【MySQL系列6】详解一条查询select语句和一条更新update语句的执行流程

这个结果是通过一系列复杂的运算得到的，包括每个表或者索引的页面个数，索引的基数，索引和数据行的长度，索引分布的情况。

优化器在评估成本的时候，不会考虑任何缓存的作用，而是假设读取任何数据都需要经过一次IO操作。

优化器可以做哪些优化

优化器可以替我们做很多优化，下面列举一些常用的优化：

• 重新定义关联的顺序。优化器并不一定按照我们写的查询关联语句中的关联顺序，而是会按照优化后的顺序进行查询。

• 将外连接转为为内连接。

• 使用等价转换原则。比如a<b and a=5会被转换为a=5 and b>5

• 优化COUNT(),MIN()和MAX()

• 预估并转化为常数表达式

• 覆盖索引扫描。想要详细了解覆盖索引的可以点击这里。

• 子查询优化。

• 提前终止查询。比如我们使用了一个不成立的条件，则会立刻返回空。

• 等值传播。

• 优化IN()语句。在其他很多数据库中in等同于or语句，但是MySQL中会讲in中的值先进行排序，然后按照二分查找的方法来确定是否满足条件。

实际当中优化器能做的优化远远比上面列举的更多，所以有时候我们不要觉得比优化器更聪明，所以大部分情况下我们都可以让优化器做出优化就可以了，如果有些我们确定优化器没有选择最优的查询方案，我们也可以在查询中通过添加hint提示告知到优化器，比如通过force index强制使用索引或者straight_join语句强制优化器按我们想要的表顺序进行关联。

优化器并不是万能的

MySQL优化器也并不是万能的，并不是总能把我们写的糟糕的sql语句优化成一个高效的查询语句，而且也有很多种原因会导致优化器做出错误的选择：

• 统计信息不准确。MySQL评估成本依赖于存储引擎提供的的统计信息，然而存储引擎提供的统计信息有时候会有较大偏差。

• 执行计划的成本估算不等于实际的执行成本。比如估算成本的时候不考虑缓存，而实际执行有些数据在缓存中。

• 优化器认为的最优可能并不是我们需要的最优。比如有时候我们想要时间最短，但是优化器

• 优化器从不考虑其他并发的查询。

• 优化器并不总是基本成本的优化。有时候也会基于规则，比如当存在全文索引，查询时使用了match()子句时，即使选择其他索引更优，优化器仍然会选择全文索引。

• 优化器不将不受其控制的操作计算为成本。如执行存储过程或者用户自定义函数的成本。

• 优化器有时候无法估算所有的执行计划，所以也有可能错过最优执行计划。

优化器如何得到查询计划

优化器听起来比较抽象，给人一种看不见摸不着的感觉，但是实际上我们也可以通过参数打开优化器追踪，优化器追踪默认是关闭的，因为开启后会影响性能，所以建议是在需要定位问题的时候开启，并及时关闭。

SHOW VARIABLES LIKE ‘optimizer_trace’;

set optimizer_trace=‘enabled=on’;

接下来执行一句查询语句：

SELECT t1.name AS name1,t2.name AS name2 FROM test t1 INNER JOIN test2 t2 ON t1.id=t2.id

这时候优化器的分析过程已经被记录下来了，可以通过下面语句查询：

SELECT * FROM information_s