有一个 ?
遇到这样一个疑问:当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 命令的输出内容为一个表格形式,表的每一个字段含义如下:
列名 | 解释 |
---|---|
id | SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符 |
select_type | SELECT 查询的类型 |
table | 查询的是哪个表 |
partitions | 匹配的分区 |
type | join 类型 |
possible_keys | 此次查询中可能选用的索引 |
key | 此次查询中确切使用到的索引 |
ref | 哪个字段或常数与 key 一起被使用;与索引比较的列 |
rows | 显示此查询一共扫描了多少行, 这个是一个估计值 |
filtered | 表示此查询条件所过滤的数据的百分比 |
extra | 额外的信息 |
select_type
查询类型 | 解释 |
---|---|
SIMPLE | 表示此查询不包含 UNION 查询或子查询 |
PRIMARY | 表示此查询是最外层的查询 |
UNION | 表示此查询是 UNION 的第二或随后的查询 |
DEPENDENT UNION | UNION 中的第二个或后面的查询语句, 取决于外面的查询 |
UNION RESULT | UNION 的结果 |
SUBQUERY | 子查询中的第一个 SELECT |
DEPENDENT SUBQUERY | 子查询中的第一个 SELECT,取决于外面的查询。子查询依赖于外层查询的结果 |
MATERIALIZED | Materialized subquery |
table
表示查询涉及的表或衍生表 。 这也可以是以下值之一:
:该行指的是具有和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: 这种连接类型类似于
ref
,但是除了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://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain