当前位置:首页 > mysql > 正文内容

mysql中优化器是如何选择索引的

phpmianshi4年前 (2017-04-17)mysql342

一:概念

  - 在 索引建立之后,一条语句可能会命中多个索引,这时,索引的选择,就会交由 优化器 来选择合适的索引

  - 优化器选择索引的目的,是找到一个最优的执行方案,并用最小的代价去执行语句。

 

二:优化器选择索引的原则?

  - 在数据库里面,扫描行数是影响执行代价的因素之一

  - 扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。

  - 当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断

 

三:优化器是如何判断扫描行数的?

  - MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条,而只能根据统计信息来估算记录数

  - 这个统计信息就是索引的“区分度”。

    - 显然,一个索引上不同的值越多,这个索引的区分度就越好。

    - 而一个索引上不同的值的个数,我们称之为“基数”(cardinality)。

    - 也就是说,这个基数越大,索引的区分度越好。

    - 可以使用 show index 方法,看到一个索引的基数。

  - 在使用普通索引,因为都要回表到主键索引上查出整行数据,这个代价优化器也要算进去的。 
 
 
四: MySQL 是怎样得到统计信息的呢?
 
   - 使用  采样统计

   - 原理

     - 采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。

     - 而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。 

 

   - 为什么需要使用 采样统计?

     - 因为把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太高了,所以只能选择“采样统计”。

 

      - 在 MySQL 中,有两种存储索引统计的方式,可以通过设置参数 innodb_stats_persistent 的值来选择:

        - 设置为 on 的时候,表示统计信息会持久化存储。这时,默认的 N 是 20,M 是 10。

        - 设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。

        - 由于是采样统计,所以不管 N 是 20 还是 8,这个基数都是很容易不准的。 

 

五:索引选择异常的问题可以有哪几种处理方式?

  - 重新统计索引信息

    -  既然是统计信息不对,那就修正。analyze table t 命令,可以用来重新统计索引信息。

 

  - 指定使用索引

    - select * from table force index(`index_name`);


版权声明:本文由PHP面试资料网发布,如需转载请注明出处。
分享给朋友:

相关文章

mysql中Join的实现原理及优化思路

mysql中Join的实现原理及优化思路

实现原理在MySQL中,只有一种 Join 算法,就是大名鼎鼎的 Nested Loop Join,他没有其他很多数据库所提供的 Hash Join,也没有 Sort Merge Join。顾名思义,...

mysql中index_merge索引合并优化

前言深入理解 index merge 是使用索引进行优化的重要基础之一。理解了 index merge 技术,我们才知道应该如何在表上建立索引。MySQL在分析执行计划时发现走单个索引的过滤效果都不是...

mysql中distinct的实现与优化

概念DISTINCT 实际上和 GROUP BY的操作非常相似,只不过是在 GROUP BY 之后的每组中只取出一条记录而已。所以,DISTINCT 的实现和 GROUP BY 的实现也基本差不多,没...

mysql中Query语句对系统性能的影响

背景我们重点分析实现同样功能的不同 SQL语句在性能方面会产生较大的差异的根本原因,并通过一个较为典型的示例来对我们的分析做出相应的验证。为什么返回完全相同结果集的不同SQL语句,在执行性能方面存在差...

mysql中order by的实现与优化

mysql中order by的实现与优化

概念在 MySQL 中,ORDER BY 的实现有如下两种类型: ◆ 一种是通过有序索引而直接取得有序的数据,这样不用进行任何排序操作即可得到满足客户端 要求的有序数据返回给客户端; ◆ 另外一种则需...

mysql中性能分析Profiling

mysql中性能分析Profiling

概念Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于Sql调优的测量。示例1、先查看是否开启了此功能,默认情况下,参数处于关闭状态,为OFF状态 ...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。