mysql为什么16个索引_第十六节、MYSQL为什么有时候会选错索引

优化器的逻辑

选择索引是优化器的工作&＃xff0c;而优化器选择索引的目的&＃xff0c;是找到一个最优的执行方案&＃xff0c;并用最小的代价去执行语句。在数据库里面&＃xff0c;扫描行数是影响执行代价的因素之一。扫描的行数越少&＃xff0c;意味着访问磁盘数据的次数越少&＃xff0c;消耗的CPU资源越少。当然&＃xff0c;扫描行数并不是唯一的判断标准&＃xff0c;优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

扫描行数是怎么判断的&＃xff1f;

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

这个统计信息就是索引的“区分度”。显然&＃xff0c;一个索引上不同的值越多&＃xff0c;这个索引的区分度就越好。而一个索引上不同的值的个数&＃xff0c;我们称之为“基数”。也就是说&＃xff0c;这个基数越大&＃xff0c;索引的区分度越好。(使用(show index from 表名)  方法可以看到一个索引的基数)。

MySQL是怎么得到索引的基数&＃xff1f;

mysql是选择“采样统计”&＃xff0c;采样统计的时候&＃xff0c;InnoDB默认会选择N个数据页&＃xff0c;统计这些页面上的不同值&＃xff0c;得到一个平均值&＃xff0c;然后乘以这个索引的页面数&＃xff0c;就得到了这个索引的基数。而数据表是非持续更新的&＃xff0c;索引统计信息也不会固定不变。所以&＃xff0c;但变更的数据行数超过1/M的时候&＃xff0c;会出发重新做一次索引统计。在mysql中&＃xff0c;有两种存储索引统计的方式&＃xff0c;可以通过设置参数innodb_stats_persistent的值来选择&＃xff1a;

1、设置为on的时候&＃xff0c;表示统计信息会持久化存储。这时&＃xff0c;默认的N是20&＃xff0c;M是10.

2、设置为off的时候&＃xff0c;表示统计信息只存储在内存中&＃xff0c;这时&＃xff0c;默认的N是8&＃xff0c;M是16。

由于是采样统计&＃xff0c;所以不管N是20还是8&＃xff0c;这个基数都是很容易不准的&＃xff0c;但&＃xff0c;这还不是全部。索引统计只是一个输入&＃xff0c;对于一个具体的语句来说&＃xff0c;优化器还要判断&＃xff0c;执行这个语句本身要扫描多少行。

统计信息不对&＃xff0c;如何修正&＃xff1f;

既然是统计信息不对&＃xff0c;那就修正。analyze table命令&＃xff0c;可以用来重新统计索引信息。

索引选择异常如何处理&＃xff1f;

大多数时候优化器都能找到正确的索引&＃xff0c;但偶尔还是会出现以下两种情况&＃xff1a;

1、扫描行数的估计值不准确&＃xff1b;

2、mysql选错索引

处理方法&＃xff1a;

1、采用force index强行选择一个索引。mysql会根据词法解析的结果分析出可以使用的索引作为候选项&＃xff0c;然后再候选列表中依次判断每个索引需要扫描多少行。如果force index制定的索引在候选列表中&＃xff0c;就直接选择这个索引&＃xff0c;不再评估其他索引的执行代价。

2、修改语句&＃xff0c;引导mysql使用我们期望的索引。例如“order by b limit 1”改成”order by b,a limit 1“。当然这并不是通用的方法&＃xff0c;只有在有限的数据下才能提高性能。

3、在有些场景下&＃xff0c;可以新建一个更合适的索引&＃xff0c;来提供给优化器做选择&＃xff0c;或删掉误用的索引。

总结&＃xff1a;

1、对于索引统计信息不准确导致的问题&＃xff0c;可以使用analyze table 来解决&＃xff1b;

2、对于其他优化器误判的情况&＃xff0c;可以在应用端用force index 来强行指定索引&＃xff0c;也可以通过修改语句来引导优化器&＃xff0c;还可以通过增加或者删除索引来绕过这个问题。