Mysql面试题_索引03
文章目录
1.Mysql为什么有时候会选错索引?
这里需要注意优化器的逻辑:选择索引是优化器的工作。
优化器选择索引的目的:是寻找一个最优的执行方案,并用最小的代价去执行语句。在数据库中,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的CPU资源越少。
优化器选择索引的参考标准:
- 扫描行数
- 是否使用临时表
- 是否排序等因素
因此,Mysql选错索引的原因可能就是优化器选择索引的参考标准导致的。
2.Mysql索引扫描行数是怎么判断的?
Mysql在真正开始执行语句之前,并不能精确的知道满足条件的记录有多少条,而只能根据统计信息来估算记录数。因此为了性能考虑Mysql使用采样统计的方法来估算记录数。也就是说采用统计的精确度决定了扫描行数。
**具体的:**采样统计时,InnoDB默认会选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数据,就得到了这个索引的基数(Cardinality)。
而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。
在 MySQL 中,有两种存储索引统计的方式,可以通过设置参数 innodb_stats_persistent 的值来选择:
- 设置为 on 的时候,表示统计信息会持久化存储。这时,默认的 N 是 20,M 是 10。
- 设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。
3.Mysql索引选择异常的优化处理方案?
- 如果是Mysql索引统计不准确,可以采用
analyze table <tableName>;
命令来重新统计索引信息。 - 可以采用
force index
强行选择一个索引(不建议使用) - 可以考虑修改语句,引导Mysql使用我们期望的索引。比如,把“order by b limit 1” 改成 “order by b,a limit 1” ,语义逻辑是相同的。
- 重新建一个更合适的索引,来提供给优化器做选择或删掉误用的索引。
4.Mysql 优化器存在选错索引的可能性,如何解决呢?
MySql优化器存在选错索引的可能性的解决方案:
- 对于由于索引统计信息不准确导致的问题,你可以用
analyze table <tableName>
来解决。 - 而对于其他优化器误判的情况:
- 你可以在应用端用 force index 来强行指定索引,
- 也可以通过修改语句来引导优化器,
- 还可以通过增加或者删除索引来绕过这个问题。