MySQL 查询优化利刃 - EXPLAIN

有一个 ?

遇到这样一个疑问: 当 where 查询中 In 一个索引字段作为条件, 那么在查询中还会使用到索引吗?

SELECT * FROM table_name WHERE column_index in (expr)

上面的 sql 语句检索会使用到索引吗? 带着这个问题, 在网上查找了很多文章, 但是有的说 in 会导致放弃索引, 全表扫描; 有的说 Mysql5.5 之前的版本不会走, 之后的 innodb 版本会走索引...

越看越迷糊, 那答案到底是怎样的呢?

唯有实践是检验真理的唯一方式!

拿出我们的利刃 --EXPLAIN, 去剖析 SELECT 语句, 一探究竟!

EXPLAIN 的用法

在 SELECT 语句前加上 EXPLAIN 就可以了 , 例如:

EXPLAIN SELECT * FROM table_name [WHERE Clause]

EXPLAIN 的输出

EXPLAIN 命令的输出内容为一个表格形式, 表的每一个字段含义如下:

select_type

table

表示查询涉及的表或衍生表 . 这也可以是以下值之一:

<unionM,N>: 该行指的是具有和 id 值的行 的 M 并集 N.

: 该行是指用于与该行的派生表结果 id 的值 N. 派生表可能来自 FROM 子句中的子查询 .

: 该行是指该行的物化子查询的结果, 其 id 值为 N.

partitions

查询将匹配记录的分区. 该值适用 NULL 于未分区的表.

type

联接类型. 提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是全表扫描还是索引扫描等. 从最佳类型到最差类型:

system: 该表只有一行(= 系统表). 这是 const 联接类型的特例 .

const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可 .

SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

ref : 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询. ref 可以用于使用 = 或<=> 运算符进行比较的索引列.

SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

ref_or_null: 这种连接类型类似于 , 但是除了 MySQL 会额外搜索包含 NULL 值的行. 此联接类型优化最常用于解析子查询.

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

unique_subquery: 只是一个索引查找函数, 它完全替代了子查询以提高效率.

value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery: 此连接类型类似于 unique_subquery. 它代替 IN 子查询, 但适用于以下形式的子查询中的非唯一索引.

range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =,<>,>,>=,<,<=,IS NULL,<=>,BETWEEN,IN() 操作中.

当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个 .

SELECT * FROM tbl_name
  WHERE key_column = 10;
SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index: 表示全索引扫描 (full index scan) 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.

index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index

ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一.

我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免 .

possible_keys

表示 MySQL 在查询时, 能够使用到的索引.

即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定.

key

是 MySQL 在当前查询时所真正使用到的索引.

key_len

表示查询优化器使用了索引的字节数.

这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到. key_len 的计算规则如下:

字符串

char(n): n 字节长度

varchar(n): 如果是 utf8 编码, 则是 3n + 2 字节; 如果是 utf8mb4 编码, 则是 4n + 2 字节

数值类型

TINYINT: 1 字节

SMALLINT: 2 字节

MEDIUMINT: 3 字节

INT: 4 字节

BIGINT: 8 字节

时间类型

DATE: 3 字节

TIMESTAMP: 4 字节

DATETIME: 8 字节

字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性

rows

查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数. 这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

这个 rows 就是 MySQL 认为必须要逐行去检查和判断的记录的条数. 举个例子来说, 假如有一个语句 select * from t where column_a = 1 and column_b = 2; 全表假设有 100 条记录, column_a 字段有索引(非联合索引),column_b 没有索引. column_a = 1 的记录有 20 条, column_a = 1 and column_b = 2 的记录有 5 条.

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

Using filesort: 当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

Using index:"覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错

Using temporary: 查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化

Using where: WHERE 子句用于限制哪些行与下一个表匹配或发送给客户端 .

得出结论

说到最后, 那 WHERE column_index in (expr) 到底走不走索引呢? 答案是不确定的.

走不走索引是由 expr 来决定的, 不是一概而论走还是不走.

SELECT * FROM a WHERE id in (1,23,456,7,8)

-- id 是主键, 查询是走索引的. type = range,key = PRIMARY

SELECT * FROM a WHERE id in (SELECT b.a_id FROM b WHERE some_expr)

-- id 是主键, 如果 some_expr 是一个索引查询, 那么 select a 将走索引;

-- some_expr 不是索引查询, 那么 select a 将全表扫描;

上面是两个通用案例, 但到底对不对了, 还是自己去实践最好了, 拿起 EXPLAIN 去剖析吧~

参考文章:

来源: https://www.cnblogs.com/sayook/p/13066193.html