mysql查询优化器为什么可能会选择错误的执行

mysql查询优化器为什么可能会选择错误的执行

有可能导致mysql优化器选择错误的执行计划的原因如下：
A：统计信息不准确，mysql依赖存储引擎为其提供的统计信息来评估成本，然而有的存储引擎提供的信息是准确的，有的引擎提供的可能就偏差很大，如：innodb因为其MVCC的架构，并不能维护一个数据表的行数的精确统计。
B：在执行计划中的成本估算不等同于实际执行的成本，即使统计信息精准，优化器给出的执行计划也可能不是最优的，如：有时候某个执行计划虽然需要读取更多的页，但它的实际执行成本却更小，因为如果这些页面都是顺序或者这些页面都在内存中，那么它的访问成本将小很多。mysql层面并不知道哪些页面在内存中，哪些在磁盘上，所以查询实际执行过程中到底需要多少次物理IO是无法预估的。
C：mysql的最优可能和你觉得最优的不一样，你可能希望执行时间尽可能地短，但是mysql只是基于成本模型选择最优执行计划，而有些时候这并不是最快的执行计划（因为mysql的成本估算主要基于扫描行数，而如果这些行是顺序的或者是在内存中，那么扫描速度就会很快，相反，如果这些行是在磁盘上且是无序的，就会产生随机读取，那么就算扫描更少的行，可能执行时间更长，而优化器在评估成本的时候并不考虑任何层面的缓存，它假设读取任何数据都需要一次磁盘IO）。
D：mysql从不考虑其他并发执行的查询，这可能影响到当前查询的速度
E：mysql也并不是任何时候都是基于成本优化，有时候也会基于一些固定的规则，如：如果存在全文搜索的match()子句，则在存在全文索引的时候就使用全文索引，即使有时候使用别的索引和where条件可以远比这种方式快，mysql也仍然使用对应的全文索引。
F：mysql不会考虑不受控制的操作成本，如：执行存储过程或者用户自定义函数的成本
G：优化器有时候无法去估算所有可能的执行计划，所以它可能错过实际上最优的执行计划。

注：
mysql架构由多个层次组成，在服务器层有查询优化器，却没有保存数据和索引的统计信息，统计信息由存储引擎层实现，不同存储引擎可能会存储不同的统计信息，某些引擎，如archive引擎，则根本没有任何统计信息。因为服务器层没有任何统计信息，所以mysql查询优化器在生成查询的执行计划的时候，需要向存储引擎获取相应的统计信息，存储引擎则提供给优化器对应的统计信息，包括：每个表或索引有多少个页面，每个表的每个索引的基数是多少，数据行和索引长度，索引的分布信息等，优化器根据这些信息来选择一个最优的执行计划。2017-11-28