首页 > mysql > mysql中优化器是如何选择索引的
2017
04-17

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

一:概念

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

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

 

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

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

  - 扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 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`);


本文》有 0 条评论

留下一个回复