【Mysql】查询优化器

基于成本

Mysql执行一个查询可以有不同的执行方案,它会选择其中成本最低的方案.
主要计算的就是:

• 全表扫描成本
• 针对每个索引计算不同的访问方式的成本

成本其中包括 I/O成本和CPU成本
InnoDB存储引擎规定 读取一个页面花费的成本默认是1.0(I/O成本),读取以及检测一条记录是否符合搜素条件的成本为0.2(CPU成本).

如果是计算辅助索引成本 还会加上 回表成本(因为辅助索引的叶子结点存的是主键而不是真实数据).

I/O成本

InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。

CPU成本

读取以及检测记录是否满足对应的搜索条件 对结果集进行排序等这些操作损耗的时间为CPU成本

join

连接原理

连接的大致原理:

• 选取驱动表,使用与驱动表相关的过滤条件,选取代价最低的访问形式来执行对驱动表的单表查询
• 对上一步骤中查询驱动表得到的结果集中的每一条记录,都分别到被驱动表中查找匹配的记录

对应的伪代码:

for each row in t1{ //遍历满足对t1单表查询结果集中的每一条记录
	for each row in t2{ //对某条t1表的记录来说,遍历满足对t2单表查询结果集中的每一条记录
	}
}

嵌套循环连接

上面的过程是 驱动表只访问一次,但被驱动表可能被访问多次 这种就是嵌套循环连接

例子:

mysql> select * from t1 join t2 on t1.a = t2.a where t1.b in (1,2);

先会执行:

mysql> select * from t1 where t1.b in (1,2);

得到结果 比如有3行 a 分别是1,2 ,5
那么就会分别执行:

mysql> select * from t2 where t2.a = 1;
mysql> select * from t2 where t2.a = 2;
mysql> select * from t2 where t2.a = 5;

缺点:
如果驱动表的结果很多,那对每个结果都要去被驱动查一次,读这么多次这个表,即驱动表的结果集中有多少个记录,就得把被驱动表从磁盘上加载到内存多少次,I/O代价就会很大.

基于块的嵌套连接

为了减少访问被驱动表的次数, 我们可以用join buffer

join buffer就是执行连接查询前申请的一块固定大小的内存,先把若干个驱动表结果集中的记录装在join buffer中,然后扫描被驱动表,每一条被驱动表的记录一次性和join buffer中多条驱动表记录做匹配

这个join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144字节（也就是256KB），最小可以设置为128字节。当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。

外连接

1. left join 和right join 统称为外连接,

2. 外连接消除:
   优化办法 就是 把他优化成内连接.因为内连接的驱动表和被驱动表的位置可以相互转换，而左连接和右连接的驱动表和被驱动表是固定的。

3. 外连接和内连接的本质区别就是：
   对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充；如对于左连接 是左边全部要 这时候另一个表没有的话 那就写成null
   而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录会被舍弃**
   

4. 所以我们消除外连接 可以在被驱动表对应属性加 not null 条件
   因为外连接被驱动表没有的话 就全是null, 我们加了not null 那就变成了内连接.
   

子查询优化

mysql 会尽量把子查询优化成join

子查询的两种:

• 写在from 后面

  mysql> select * from  (select * from t1) as t;
  

• 写在where 后面

  mysql> select * from t1 where a in (select a from t2);
  

按返回的结果区分子查询:

• 标量子查询
  只返回一个单一值

  mysql> select * from t1 where a in (select max(a) from t2);
  

• 行子查询
  返回一条记录的子查询,且这条记录包含多个列

  mysql> select * from t1 where (a,b) = (select a , b from t2 limit1);
  

• 列子查询
  返回一个列的数据的子查询,包含多条记录

  mysql> select * from t1 where a in (select a from t2 );
  

• 表子查询
  子查询的结果既包含很多条记录,又包含很多个列

  mysql> select * from t1 where (a,b) in (select a,b from t2);
  

按与外层查询关系来区分子查询

• 相关子查询
  子查询的执行需要依赖于外层查询的值

  mysql> select * from t1 where a = (select a from t2 limit 1);
  

  执行步骤是：
  • 先从外层查询中获取一条记录，本例中也就是先从t1表中获取一条记录。
  • 然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从t1表中获取的那条记录中找出t1.a列的值，然后执行子查询。
  • 最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
  • 再次执行第一步，获取第二条外层查询中的记录，依次类推。。。
• 不相关子查询
  子查询可以单独运行出结果,不依赖外层查询的值

  mysql> select * from t1 where a = (select a from t2 limit 1);
  

  执行步骤:
  • 执行select a from t2 limit 1这个子查询。
  • 然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询select * from t1 where a = ...;

in子查询优化

   mysql> select * from t1 where a in (select a from t2);

1. 子查询结果太多导致的问题:
   对于不相关的IN子查询来说，如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率还是蛮高的，但是如果单独执行子查询后的结果集太多的话，就会导致这些问题：

   • 结果集太多，可能内存中都放不下
   • 对于外层查询来说，如果子查询的结果集太多，那就意味着IN子句中的参数特别多，这会导致：
     • 无法有效的使用索引，只能对外层查询进行全表扫描。
     • 在对外层查询执行全表扫描时，由于IN子句中的参数太多，这会导致检测一条记录是否符合和IN子句中的参数匹配花费的时间太长。

2. Mysql优化in子查询的方式：

   • 物化表
   • semi-join

物化表

在mysql中，不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程是这样的：

• 该临时表的列就是子查询结果集中的列。
• 写入临时表的记录会被去重。
  IN语句是判断某个操作数在不在某个集合中，集合中的值重不重复对整个IN语句的结果并不影响，所以我们在将结果集写入临时表时对记录进行去重可以让临时表变得更小。临时表也是个表，只要为表中记录的所有列建立主键或者唯一索引就可以进行去重。
• 一般情况下子查询结果集不会特别大，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。IN语句的本质就是判断某个操作数在不在某个集合里，如果集合中的数据建立了哈希索引，那么这个匹配的过程就是很快的。
• 如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用**基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引**。

这个将子查询结果集中的记录保存到临时表的过程称之为物化（Materialize）。那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能

• 在使用了物化表之后，也就是说其实上边的查询就相当于表s1和子查询物化表materialized_table进行内连接：

       select * from t1 inner join materialized_table on t1.a = m_val

semi-join

semi-join 是Mysql内部采用的一种执行子查询的方式，主要就是为了去重。
他有以下几种：

Table pullout（子查询中的表上拉）

也就是子查询的查询列表处只有主键，这样不会有重复的，所以就直接可以将in转成inner join

DuplicateWeedout execution strategy（重复值消除）

当查询的字段为普通字段，查询结果就可能出现重复的，这时候可以建一个临时表来去重

FirstMatch execution strategy首次匹配

两个表之间直接去找，只找一次，这样也可以去重

LooseScan 松散索引扫描

利用索引是排好序的来去重