有一个 ? 遇到这样一个疑问:当where查询中In一个索引字段作为条件,那么在查询中还会使用到索引吗? SELECT * FROM table_name WHERE column_index in (e
遇到这样一个疑问:当where查询中In一个索引字段作为条件,那么在查询中还会使用到索引吗?
SELECT * FROM table_name WHERE column_index in (expr)
上面的sql语句检索会使用到索引吗?带着这个问题,在网上查找了很多文章,但是有的说 in 会导致放弃索引,全表扫描;有的说Mysql5.5之前的版本不会走,之后的innodb版本会走索引...
越看越迷糊,那答案到底是怎样的呢?
唯有实践是检验真理的唯一方式!
拿出我们的利刃——EXPLaiN,去剖析 SELECT 语句,一探究竟!
在 SELECT 语句前加上 EXPLAIN 就可以了 ,例如:
EXPLAIN SELECT * FROM table_name [WHERE Clause]
EXPLAIN 命令的输出内容为一个表格形式,表的每一个字段含义如下:
列名 | 解释 |
---|---|
id | SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符 |
select_type | SELECT 查询的类型 |
table | 查询的是哪个表 |
partitions | 匹配的分区 |
type | join 类型 |
possible_keys | 此次查询中可能选用的索引 |
key | 此次查询中确切使用到的索引 |
ref | 哪个字段或常数与 key 一起被使用;与索引比较的列 |
rows | 显示此查询一共扫描了多少行, 这个是一个估计值 |
filtered | 表示此查询条件所过滤的数据的百分比 |
extra | 额外的信息 |
查询类型 | 解释 |
---|---|
SIMPLE | 表示此查询不包含 UNION 查询或子查询 |
PRIMARY | 表示此查询是最外层的查询 |
UNION | 表示此查询是 UNION 的第二或随后的查询 |
DEPENDENT UNION | UNION 中的第二个或后面的查询语句, 取决于外面的查询 |
UNION RESULT | UNION 的结果 |
SUBQUERY | 子查询中的第一个 SELECT |
DEPENDENT SUBQUERY | 子查询中的第一个 SELECT,取决于外面的查询。子查询依赖于外层查询的结果 |
MATERIALIZED | Materialized subquery |
表示查询涉及的表或衍生表 。 这也可以是以下值之一:
查询将匹配记录的分区。该值适用NULL
于未分区的表。
联接类型。 提供了判断查询是否高效的重要依据依据。通过 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 类型查询,那么一般来说可以对相应的字段添加索引来避免 。
表示 MySQL 在查询时,能够使用到的索引。
即使有些索引在 possible_keys 中出现,但是并不表示此索引会真正地被 MySQL 使用到。MySQL 在查询时具体使用了哪些索引,由 key 字段决定。
是 MySQL 在当前查询时所真正使用到的索引。
表示查询优化器使用了索引的字节数。
这个字段可以评估组合索引是否完全被使用,或只有最左部分字段被使用到。key_len 的计算规则如下:
查询优化器根据统计信息,估算 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 条。
EXplain 中的很多额外的信息会在 Extra 字段显示,常见的有以下几种内容:
说到最后,那 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
--结束END--
本文标题: MySQL查询优化利刃-EXPLAIN
本文链接: https://www.lsjlt.com/news/6530.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
2024-05-10
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0