Mysql优化器-mysql详解（六）

上篇文章说了，mysql的访问效率有几大类别，const，ref,Ref_null,rang，index，all，以及连接查询走索引，驱动表和被驱动表的查询效率。

Mysql优化器

前面说了很多mysql优化器会选择成本低的来执行，那么怎么判断成本低呢？

I/O成本：当我们需要把磁盘上的数据刷新到内存上的时候，这个过程就是I/O成本。

CPU成本：读取和监测数据是否满足条件，对结果进行排序分组等操作则是CPU成本。

Mysql规定一个页在磁盘与内存交互的成本是1.0，读取或者监测数据是否存在成本是0.2。

Mysql优化器的工作步奏大概是：1、计算全表扫描的代价。2、计算各个索引扫描的代价。3、对比各个成本，选择低成本去执行sql。

Possible keys：Mysql在计算之前，会看看可能使用的索引是哪些。

全表扫描计算主要看聚簇索引占用多少data_length，我们知道每个页是16kb，所以可以通过这个参数算出多少页，那么如何看data_length呢，这是属于统计数据，通过show table status语句来查看。

还有一个rows也可以看到，值得注意的是innoDB这个是一个估算值，比如1万条真实数据可能只显示9900多条，但是myISAM是真实的值。

为什么innodb是大致计算的呢，因为mysql计算会随机选取几个叶子节点页面，计算每个叶子的值，然后平均一下，还为我们准备了一个参数设置

innoDB_states_persisentent_sample_page的系统变量来统计，如果设置的越大，越计算的，当然耗时也就更长。

innoDB_states_transient_sample_page控制着非性统计页数量。

如果连接查询如何计算成本呢，我们前面说了连接查询有驱动表和被驱动表，被驱动表查询次数取决于驱动表查询的数据，这里称为 扇出值。

上面那些数据是怎么来的呢？Mysql在5.6.6版本之前innodb_stats_persistent是默认off的，也就是默认存在内存中的，在5.6.6版本之后是默认on的，会存在磁盘上。顾名思义，存在内存中，系统重启就会丢失，存在磁盘上就属于性持久化。他们分别存储在两个表里，一个是innodb_table_stats和innodb_index_stats。

在计算null值得时候，有个参数可以决定null怎么计算，innodb_stats_method

当参数等于nulls_Equal：表示所有null相等，也是当前参数默认值。

Nulls_unequal：表示每个null都不相同。

Nulls_ignored：直接忽略null。

前面说过外连接和内连接的区别就是，内连接的on语句没满足则直接舍弃，外连接如果被驱动表没满足查询条件，则会返回null到结果集。所以如果where语句里面直接过滤null的话，其实内连接外连接查询的结果集是一致的，这时候会直接优化为内连接查询。

子查询：当我们的子查询在from后面的时候，子查询本质上就是一个表，这时候我们叫他 派生表。（派生表会尝试先与外层结合查询，不行的话就会物化）

还可以把子查询放在where或者on后面的时候。

子查询分为很多种，

标量子查询：表示只有特定值查询。

行子查询：表示查询一行数据的子查询。（可以在查询语句后面加个limit1）

列子查询：表示子查询查询的数据是一列。

表子查询：表示子查询查询的是多列多行，如同表一样。

当我们外部查询需要依赖内部查询值得时候，这时候又分为相关子查询，反之则是不相关子查询。

当在子查询里使用布尔符号比如<>,>,<等这时候子查询必须是行或者标量子查询。

那么子查询是如何执行的呢？

当执行不相关 标量查询或者行查询的时候，mysql则会当做两个表来查询，先查询内层子查询，用结果再查询外层。

执行相关子查询，则是重复上述动作，一次一次把查询出来的参数带入外层查询。

但如果有in语句，则与上面就不同。

Materialized_table:

当in里面的值比较少的时候，则是直接子查询，当in里面的值很多，这时候就会把子查询里面的值写入临时表，并且写入临时表的数据会用建去重--物化表。这个物化的临时表则是memory存储引擎，会建立哈希索引。当子查询数据再大到一定值，tmp_table_Size,

max_heap_table_size的值时候，这时候会把临时表建立成磁盘存储，索引也会变为b+树。

In的子查询不仅仅限于此，发现物化之后可以转内连接，因为有两张表，但是有更好的选择，mysql还有semi-join，直接放弃物化，直接半连接，半连接与内连接不同的是，内连接返回的是两个表的数据，并且on满足几条就返回几条，而半连接不关系被驱动表满足几条数据，只要一条满足，就返回驱动表的数据。并且半连接有四种算法，这里就不一一举例了。

那么满足semi-join的条件：

1、子查询必须in连用，并且在外层的where或者on后面。（不能在from，那就是派生查询）

2、外层查询也可以有其他搜索条件，不过和in子查询的条件用and连接起来。

3、子查询不能包含group by 和having等。

...等等