mysql查询优化

优化器

开启方法：

set optimizer_trace="enabled=on";

在执行完目标语句后执行如下sql即可看到执行计划日志

select * from information_schema.OPTIMIZER_TRACE;

准备阶段：

优化阶段：条件处理

常量传递 equality_propagation

a = 1 AND b > a
-- 会被优化为如下形式
a = 1 AND b > 1

等值传递 constant_propagation
sql a = b and b = c and c = 5 -- 会被优化为如下形式 a = 5 and b = 5 and c = 5

移除无用条件 trival_condition_removal

a = 1 and 1 = 1
-- 会被优化为如下形式
a = 1

基于成本的执行方案选择

一个查询可以有不同的执行方案，可以选择某个索引进行查询，也可以选择扫描全表，查询优化器会选择其中成本最低的方案进行查询。

IO成本
innodb存储引起需要将数据和索引都存储在磁盘上，当我们想要查询表数据时，需要先把数据或者索引加载到内存中再进行操作。
CPU成本
读取以及检测记录是否满足对应的搜索条件，对结果集进行排序这些操作损耗的时间称为CPU成本。

InnoDB存储引擎规定读取一个页面花费的默认成本是1.0读取以及检测一条记录是否符合搜索条件的成本是0.2

基于成本的优化步骤

可以查看rows_estimation（在执行计划日志的json中喔）

"rows_estimation":[
    {
        "table":"",
        "range_analysis": {
            "table_scan"{//全表扫描花费
                "rows":1,
                "cost":1//这个花费是用于评估的标准
            },
            "potential_range_indexes": [//可能存在的各种索引使用情况
                {
                    "index" :"",
                    "usagble":bool,
                    "key_parts"[
                        ""//索引涉及列名
                    ]
                }
            ],
            "best_covering_index_scan":{
                "index":"",
                "cost":1,
                "chosen":false,
                "cause":"cost"
            }
        }
    }
]

上面的结构节选中只给出了全表扫描的花费，真正使用索引扫描的成本花费在"analyzing_range_alternatives"

全表扫描

cost=pageCount*1+rowCount*0.2

首先预估符合条件的数据在表上有多少页
IO：
执行如下语句：
```
show table status like 'tablename';
```
可以知道行数Rows、表大小Data_length(单位为byte)等等
这个统计数据的根据innodb_stats_persistent的值确定存在内存或者是磁盘当中，mysql5.6.6之后默认为ON，存储到磁盘中。表中变动数据超过10%会更新。统计数据更新的语句为
```
ANALYZE TABLE `table1`;
```
1. 主键索引有多少页？
  由于InnoDB页数默认为16kb,可得：
```
pageCount=data_length/16kb//（注意一下单位）
```
  虽然说只需要主键索引B+Tree的叶子节点，但是在估算过程中其实直接采用了data_length（即统计了所有节点的大小）。

条件搜索（主键索引）

根据索引找到满足条件的边界数据，例如大于某个值A，那么根据索引找到对应叶子节点，那么就可以知道其右边的叶子节点都满足条件。

可以根据两个边界条件的子节点的父节点对应的目录项记录之间间隔几条记录估算出行数和页数并得到CPU成本。

IO成本，只需要区一个区间范围内，毛估估是1……

条件搜索（辅助索引）

成本估算与条件搜索与主键索引类似，再加上回表成本。

另外：辅助索引如果出现很多key相同的情况，会把索引数据key+主键做成索引

回表成本相当于

select * from table where table.id in (查询出来的id)

当需要回表的id未超过200个（个数通过eq_range_index_dive_limit变量控制）时，会使用index dive方式查找值。

但如果超过200个的时候，就会通过索引估算。可通过语句查询估算依据

show index form 'tablename';

根据之前查出的rows，以及show index查出的Cardinality（不同数据的个数），得出平均每个数有多少行。再根据这个行数进行估算。

统计规则

获取B+树叶子节点的数据，记为A
随机获得B+树索引中8个叶子节点。统计每个页不同记录的个数，分别记为P1,P2...P8
计算cardinality = (P1+P2+...P8)A/8

所以说in里面超过200个的话其实估算成本就有很大偏差了……

计算成本小结

全表扫描计算成本
索引扫描计算成本
1. index_dive:根据最大以及最小值找到子节点，统计出范围内的行数。
2. 非index_dive：统计影响行数会变得很不准。

关于null值

有如下三种处理方式：

null代表一个未确定的值，每一个null值都是独一无二的，统计不重复列的时候算作多个。
null在业务上代表没有，所有null的意义都一样，作为统计不重复列时作为一个。
null完全没有意义，不统计。

可以通过变量 ''%innodb_status_method%''控制。

该变量有三个值：

nulls_equal
nulls_unequal
nulls_ignored

最好就不要在索引列中存放null值不然很头大……

join的原理

select t1.col,t2.col from t1 join t2 on 联表条件 where t1.col>xx and t2.col>xx;

执行过程大致如下：

查询优化器会根据一定规则选择一张驱动表，假定是t1。
那么就会先
```
select t1.col from t1.col>xx;
```
得到t1中符合条件的结果集A，并且放置在join buffer中，根据joinbuffer的大小可能会放置一到多跳。
需要注意的是 join buffer 中存储的是驱动表上需要查询到的所有数据，所以此时select * 和select colname 就会产生差距了……
查询被驱动表
```
select t2.col where t2.col>xx;
```
此时也会得到一个被驱动表结果集B。
这个结果集B会用来和join buffer中的记录按照联表条件进行匹配，找到符合条件的记录产生最后的查询结果。

外连接的优化

看了一圈之后发现说的就是试图在符合业务场景的基础上尽量把外连接优化成内连接。让查询优化器能够自主选择驱动表和被驱动表……

子查询的优化

子查询分类

按照查询结果集划分
1. 标量子查询
  只返回一个单一值的子查询。
2. 行子查询
3. 列子查询
4. 表子查询
按照与外层查询关系区分
1. 相关子查询
  即外部查询结果或者相关条件需要在子查询内部使用的。
2. 非相关子查询

在非相关子查询中，会先把子查询的结果集查询出来，作为结果再去查询外层的查询。

而在相关子查询中，步骤如下：

从外层获取一条记录R。
根据R的结果找到涉及子查询的值，执行子查询。
根据子查询的结果来检测where条件知否成立，成立则加入结果集。

in查询实现方式

那么in中去重的方式有好多种：

物化表
semi join
1. table pullout 不去重
2. duplicateweedout 临时表去重
3. first match 只取首次数据
4. loosescan 在非唯一索引作为条件时可能存在相同的值，通过索引去重。

in 子查询优化

如果in条件中的结果集过大（超过系统变量），会将其物化为一个临时表（同时根据情况去重），为其建立b+树索引。

如果结果集不是很大，则会建立一个哈希表。

这样看起来好像直接join会不会更好一些……

还省去了建立临时表或者是哈希表的过程……

针对于上述这个问题如果是把主键或者是一个唯一的索引去作为条件的话确实是这样的……

但如果条件中的值不唯一，那么在in子查询中会自动为其去重，而join是不会的……