索引优化实战

1.索引优化实战

1.1 索引下推

覆盖索引

假设有一条SQL语句

select * from employees where name > 'a';

此时需要查询出在满足查询条件的所有字段

假设MySQL使用二级索引(name,age,position)来查询，此时MySQL会找到满足查询条件的结果集，然后通过二级索引的id到聚簇索引中回表查询数据，结果就是付出的代价比全表扫描要高。此时如果我们使用了覆盖索引，所谓覆盖索引，就是指我们查询的字段和我们索引的字段相匹配（可以少，但不能多，一多就会回表）时，就可以直接查询出想要的结果，而不用回表查询。

1.2 trace查询用法

假设有条一条SQL语句

select * from employees where name > 'zzz' ;

乍一看和上面的SQL语句差不多，但此时MySQL却使用了索引，这就很奇怪了，因此我们使用MySQL自带的trace工具来一探究竟

下面介绍一下Trace工具的开启方式

set session optimizer_trace="enabled=on",end_markers_in_json=on;

我们以上面的SQL语句为例，看一下trace的查询结果

select * from employees where name > 'zzz' ;

select * from information_schema.OPTIMIZER_TRACE;

会得到很多数据，但是我们只看关键的部分

"rows_estimation字段"

"rows_estimation": [ --预估表的访问成本
  {
    "table": "`employees`",
    "range_analysis": {
      "table_scan": { --全表扫描情况
        "rows": 97650, --扫描行数
        "cost": 19885 --查询成本
      } 
]

"analyzing_range_alternatives"

"analyzing_range_alternatives": {  --分析各个索引使用成本
"range_scan_alternatives": [
  {
    "index": "idx_name_age_position",
    "ranges": [
      "zzz < name"
    ] /* ranges */,
    "index_dives_for_eq_ranges": true,
    "rowid_ordered": false,
    "using_mrr": false,
    "index_only": false,
    "rows": 1, --索引扫描行数
    "cost": 2.21, --索引使用成本
    "chosen": true --是否选择该索引
  }
}

会发现全表扫描的行数（97650） >> 索引扫描行数（1），而总体成本来看使用索引的coast更低，因此mySQL使用了索引。但是也有可能出现索引的coast成本大于全表扫描的coast，并且与扫描行数无关，并不是说扫描行数越多所花费的coast就越高，因为coast的最终数值由mySQL内部的一系列参数决定的（具体要参考源码的实现，这里不进行深入探讨），所以coast值是最能直观体现出二者效率的，因此mySQL会优先选择coast较低的进行查询。

1.3 常见的SQL深入优化

在这之前，我们先讲一下MySQL的最左前缀原则

假设一张表有三个字段a，b，c并且建立了以a，b，c为字段的二级索引

因为索引本质是B+树，所以我们用画图来研究，图中省略了B+树的非叶子节点，主要集中于叶子节点

该图中，叶子节点从上到下分别表示a、b、c字段（省略了ID字段）

我们可以发现，在A节点的叶子节点下，当a都相同的情况下，MySQL就会去比较b字段，对b字段进行排序；在B节点的叶子节点下，a字段都不相同，则直接按a字段进行排序，不会去排序b和c字段；在C节点的叶子节点下，a字段和b字段相同，则会去排序c字段。

也就是说当一个字段相同，则去排序下一个字段，若该字段还相同，则继续排序下一个字段。

若一个字段不相同，则直接进行排序，而不会排序在自己之后的字段。这就是最左前缀原则

1.4 Order By与Group By的优化

实例1

SELECT * FROM employees WHERE name = "LiLei" and position = "dev" ORDER BY age

此时该语句可以使用索引，因为在name字段和position字段相同的情况下，age字段自然会按照顺序进行排列，因此可以直接使用索引排序，通过EXPLAIN关键字，我们会在 Extra中看到 ”Using index condition“的结果

实例2

SELECT * FROM employees WHERE name = "LiLei" ORDER BY position

我们会发现，该查询字段使用了索引，但是用了position进行排序，跳过了age字段，直接使用position进行排序。根据mySQL的最左前缀原则，mySQL会优先对name进行排序，在name相同的情况下，再对age进行排序，在age也相同的情况下再对positon进行排序。

而上述SQL直接跳过了age，直接去排序二级索引中最后一个字段position，那么此时的age必然就不是有序的，不遵循最左前缀原则，因此不会使用我们定义的索引进行排序，而是使用了文件排序的方式，降低了效率。

结论：遵循最左前缀原则，不要跳字段排序

实例3

SELECT * FROM employees WHERE name = "LiLei" ORDER BY age,position

我们会发现此次查询使用了索引，并且使用了索引排序

而如果我们调换以下age和position的位置

SELECT * FROM employees WHERE name = "LiLei" ORDER BY position,age

我们会发现，此次查询使用了文件排序

因为此次查询ORDER BY的排序顺序与二级索引字段的顺序不一致，也就是说此次查询是先对position进行排序，再对age进行排序，这种排序并不是我们创建的索引所拥有的，因此需要对结果集进行排序，并且由于结果集较大，MySQL使用了文件排序，大大降低了效率。

结论：排序的字段要和索引的字段顺序一致

实例4

SELECT * FROM employees WHERE name = "LiLei" ORDER BY age ASC,position DESC

排序的顺序age使用升序，position使用降序，通过我们创建的索引不能直接得到结果集，因此要进行文件排序，效率很低。

实例5

SELECT * FROM employees WHERE name IN ("LiLei","HanMeiMei") ORDER BY age,position

IN条件相当于也是范围查询，ORDER BY后的字段排序默认会失效，因为根据最左前缀原则，要在第一个字段相同的情况下，才会去排序第二个字段，而name为第一个字段，此查询语句中name=Lilei或者name=HanMeiMei，两个字段不相同，因此第二个字段age也就不能进行排序，所以age和position的排序是无效的。

优化总结

mySQL有两种排序方式filesort和index，一个通过文件排序，一个通过扫描索引本身完成排序
尽量使用覆盖索引，查询中少使用像”SELECT * "，尽量将查询字段和索引字段相匹配
如果ORDER BY 的条件不在索引列上，就会产生 filesort，效率降低，详见实例5
ORDER BY满足两种情况会使用Index Sort
- ORDER BY语句满足最左前缀原则
- WHERE条件与ORDER BY满足最左前缀原则
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

MySQL索引优化实战（一）