MySQL查询索引分析

原创

kevindang

修改于 2018-06-17 17:09:32

2.2K0

修改于 2018-06-17 17:09:32

场景：

前一段时间修改数据表时，给一个表添加一个datetime字段，当时遇到了一个问题：我是否需要给该datetime字段上加索引呢？如果不给该字段加索引，当where语句中使用该字段时，会不会扫全表呢？如果给其加了索引，那么势必会带来一些开销，假如这个索引用不到的话，给其加了索引岂不是画蛇添足了呢？

为了弄清楚上述问题的原因，以及当where条件很多时，Mysql如何选择索引进行查找，查阅了Mysql官方文档第8章optimization的相关内容。

首先，想要知道你的一条复杂的SQL语句到底是如何执行的，第一步我们可以执行

show index from table_name

来查看你的表中都有哪些索引，例如：

+------------+------------+-------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

| Table      | Non_unique | Key_name          | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+------------+------------+-------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

| acct_order |          0 | PRIMARY           |            1 | id             | A         |        2287 |     NULL | NULL   |      | BTREE      |         |

| acct_order |          1 | game_id           |            1 | game_id        | A         |          51 |     NULL | NULL   |      | BTREE      |         |

| acct_order |          1 | user_id           |            1 | user_id        | A         |         114 |     NULL | NULL   | YES  | BTREE      |         |

| acct_order |          1 | game_work_id      |            1 | game_work_id   | A         |          78 |     NULL | NULL   |      | BTREE      |         |

| acct_order |          1 | weixin_user_id    |            1 | weixin_user_id | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |

+------------+------------+-------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+

字段很多，其中比较重要的几个：

Non_unique：表明是否是唯一键
Key_name：索引的名字
Column_name：索引包含的column名字
Cardinality：大概意思就是此列中存储唯一值的个数，该值是Mysql优化器是否选择使用该索引的重要判断依据，但是改指并不准确，不会自动跟新，我们可以通过analyze table等语句来更新表
Index_type：表明该索引的类型，一般为BTREE

索引那么多，Mysql使用索引的目的是什么呢？

从where条件中快速定位到我们要找的行
从条件中消除行，如果有多个index可供选择，mysql通常会使用那些能够找出最少行数的索引
为了找出join表的行数据
在某些索引查询中已经包含所需的数据时，不需要再读取完整的记录（Mysql一般会先从索引文件中读取要找的记录，然后根据索引再从数据表中读取真正的记录）
其他

在了解了自己表结构以及索引结构之后，通常可以使用explain语句来查看Mysql的查询执行计划，例如下所示：

mysql> explain select * from acct_order where user_id=39 and weixin_user_id=61 and add_time > "2014-10-01";G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: acct_order

         type: index_merge

possible_keys: user_id,weixin_user_id

          key: weixin_user_id,user_id

      key_len: 5,5

          ref: NULL

         rows: 4

        Extra: Using intersect(weixin_user_id,user_id); Using where

explain的结果就是该次查询的执行计划，几个比较重要的字段为：

select_type：就是select类型，常用的有如下几种:

  - SIMPLE（简单select查询，不包含union或子查询等）    
  - PRIMARY（主键查询）     
  - SUBQUERY     
  - UNION     
  - UNION RESULT

type字段：显示了连接使用了哪种类别,有无使用索引.这列很重要，往往能够表明你的SQL语句执行的速度，好坏等其中从好到坏的取值依次有：system、const（最多只有一行满足条件）、eq_ref、ref、fulltext、ref_or_null、index_merge（该联接类型表示使用了索引合并优化方法）、unique_subquery、index_subquery、range（索引的范围查询）、index（扫描整个索引树）、ALL（扫描全表）
possible_keys字段：该列指出Mysql可能会选择使用的索引
key字段：Mysql在执行该条查询语句时，真正选择使用的索引
rows字段：显示MySQL认为它执行查询时必须检查的行数，不是最后得出的结果的真实行数
Extra字段：显示Mysql解析查询时的详细信息，例如使用了哪一种索引合并优化算法、查询是否使用了临时表、是否使用了filesort、等等，通过该字段你可以判断出Mysql执行查询计划是否跟你的预期一致，来决定是否要对SQL语句进行优化，从而获取更优的执行计划

在执行查询语句时，Mysql对select语句进行了很多优化，例如：

where 语句优化：去掉无用的where条件等等，详见官方文档
range 优化
index merge算法优化
判空优化
order by语句优化
group by语句优化
等等

这里不得不提的是range optimization和index merge Optimization，我在写SQL语句时，所用到的select语句大多数情况下，Mysql进行的都是这两种优化：

index merge Optimization：

该种优化是基于三种算法来进行的：The Index Merge Intersection Access Algorithm、The Index Merge Union Access Algorithm以及The Index Merge Sort-Union Access Algorithm

首先我们说说 The Index Merge Intersection Access Algorithm，上述SQL语句的执行就是使用了该种算法，该算法执行的前提条件为：

where 条件里，所有的condition都用AND相连
每一个condition必须符合以下规则：
- 主键范围查询
- 对于多列索引，where条件中需要有多列索引的每一部分： key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

例如：

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;

SELECT * FROM tbl_name WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

该算法操作流程如下：

同时扫描多个索引，然后取索引扫描结果的交集
如果需要的columns都在刚刚的index 结果中，则不需要再去数据表中取数据（例如：SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;）
然后根据索引的交集去数据表中取出所需要的真实数据记录

那么针对我们上述SQL语句的案例，执行过程就是：

先从user_id索引树中找user_id=39的记录，同时从weixin_user_id索引树中找weixin_user_id=61的记录
取两次索引结果的并集（这里，每个索引中都会包含主键的）
拿着最终索引结果去数据表中取真实记录

同理：The Index Merge Union Access Algorithm和Intersection相似，区别在于把AND条件换成了OR。

range optimization

对于range optimization，TNT存储引擎大神总结过一篇blog

所有SQL的where条件均可归纳为3大类：

Index Key (First Key & Last Key)： - 用于确定SQL查询在索引中的连续范围（起始+终止）
Index Filter： - 在完成Index Key的提取之后，根据where条件固定了索引的查询范围，但是此范围中的项，并不都是满足查询条件的项，需要过滤index，具体提取规则查看该blog
Table Filter： - 所有不属于索引列的查询条件，均归为Table Filter之中（Mysql会先根据索引从数据表中读取出真实记录，然后根据table filter中的条件来过滤结果）

那么其实我们再回头来看之前那条SQL语句的执行时，已经很清晰的知道其进行查询时的一个大概过程：