MySQL_explain关键字分析查询语句
版权声明：本⽂为博主原创⽂章，转载请注明出处。
通过对查询语句的分析，可以了解查询语句的执⾏情况。MySQL中，可以使⽤EXPLAIN语句和DESCRIBE语句来分析查询语句。 EXPLAIN语句的基本语法如下:（DESCRIBE语法⼀致，DESCRIBE可简写为DESC）
EXPLAIN Select 语句；
eg：explain SELECT * FROM `user` where name = 'name6'; 结果如下：
explain结果值及其含义：
参数值含义
id表⽰SELECT语句的编号；
select_type 表⽰SELECT语句的类型。
该参数有⼏个常⽤的取值：
SIMPLE ：表⽰简单查询，其中不包括连接查询和⼦查询; PRIMARY：表⽰主查询，或者是最外层的查询语句; UNION ：表⽰连接查询的第⼆个或后⾯的查询语句;
table表⽰查询的表；
type 表⽰表的连接类型。该参数有⼏个常⽤的取值：
const ：表⽰表中有多条记录，但只从表中查询⼀条记录;
eq_ref ：表⽰多表连接时，后⾯的表使⽤了UNIQUE或者PRIMARY KEY;
ref ：表⽰多表查询时，后⾯的表使⽤了普通索引;
unique_ subquery：表⽰⼦查询中使⽤了UNIQUE或者PRIMARY KEY;
index_ subquery：表⽰⼦查询中使⽤了普通索引; range ：表⽰查询语句中给出了查询范围; index ：表⽰对表中的索引进⾏了完整的扫描;
all ：表⽰此次查询进⾏了全表扫描; ----------- 该条SQL需要优化；
possible_keys 表⽰查询中可能使⽤的索引；
如果备选的数量⼤于3那说明已经太多了，因为太多会导致选择索引⽽损耗性能，所以建表时字段最好精简，同时也要建⽴联合索引，避免⽆效的单列索引；
key表⽰查询使⽤到的索引；
简单的mysql语句key_len表⽰索引字段的⼀长度;
ref表⽰使⽤哪个列或常数与索引⼀起来查询记录;
rows 表⽰查询的⾏数;
试图分析所有存在于累计结果集中的⾏数，虽然只是⼀个估值，却也⾜以反映出SQL执⾏所需要扫描的⾏数，因此这个值越⼩越好；
Extra表⽰查询过程的附件信息。
通过explain可以得到如下结论：
①使⽤索引⽐未使⽤索引，扫描的⾏数更少查询速度更快；
②在查询语句中使⽤LIKE关键字进⾏查询时，如果匹配字符串的第⼀个字符为“%”时，索引不会被使⽤。如果“%”不是在第⼀个位置，索引就会被使⽤。
③使⽤多列索引时，只有查询条件中使⽤了该索引中的第⼀个索引字段时，索引才会被使⽤。
注：create index index_age_sex on user(age,sex); age为第⼀个索引；
④查询语句只有OR关键字时，如果OR前后的两个条件列都是索引时，查询中将使⽤索引。只要OR前后有⼀个条件的列不是索引，那么查询中将不使⽤索引。
注： 1：where 语句⾥⾯如果带有or条件, myisam表能⽤到索引，innodb不⾏；2：必须所有的or条件都必须是独⽴索引；
⑤经过普通运算或函数运算后的索引字段不能使⽤索引。
但是，经过函数运算字段的字段要使⽤可以使⽤函数索引，这种需求建议与DBA沟通。
以上结论来⾃如下测试：
user表：独⽴索引：id、name 联合索引：age && sex user_noindex表：⽆任何索引列；
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(30) NOT NULL, `age` int(11) NOT NULL, `sex` tinyint(4) NOT NULL, `isDeleted` tinyint(4) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id_unidx` (`id`) USING BTREE, UNIQUE KEY `name_unidx` (`name`) USING BTREE, KEY `index_age_sex` (`age`,`sex`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;CREATE TABLE `user_noindex` ( `id` int(11) NOT NULL, `name` varchar(30) NOT NULL, `age` int(11) DEFAULT NULL, `sex` tinyint(4) DEFAULT NULL, `isDeleted` tinyint(4) DEFAULT NULL )
ENGINE=InnoDB DEFAULT CHARSET=utf8;
【1】索引对查询的影响-----加索引和不加索引的对⽐-----使⽤索引扫描的更少查询更快
语句1：explain SELECT * FROM `user` where name = 'name6';
语句2：explain SELECT * FROM `user_noindex` where name = 'name6';
结果集id select_type table type possible_keys key key_len ref rows Extra
语句11SIMPLE user constname_unidx name_unidx32const1null
语句21SIMPLE user_noindex ALL null null null null10Using where
【2】索引对查询的影响-----加索引----使⽤和未使⽤索引的对⽐-----在查询语句中使⽤LIKE关键字进⾏查询时，如果匹配字符串的第⼀个字符为“%”时，索引不会被使⽤。如果“%”不是在第⼀个位置，索引就会被使⽤。
语句1：explain SELECT * FROM `user` where name like '%name6';
语句2：explain SELECT * FROM `user` where name like '%name6%';
语句3：explain SELECT * FROM `user` where name like 'name6%';
结果集id select_type table type possible_keys key key_len ref rows Extra
语句11SIMPLE user ALL null null null null10Using where
语句21SIMPLE user ALL null null null null10Using where
语句31SIMPLE user range name_unidx name_unidx32const1null
【3】索引对查询的影响-----加索引----使⽤和未使⽤索引的对⽐-----多列索引是在表的多个字段创建
⼀个索引。只有查询条件中使⽤了这个字段中的第⼀个字段时，索引才会被使⽤。
语句1：explain SELECT * FROM `user` where age = '19';
语句2：explain SELECT * FROM `user` where sex = '1';
语句3：explain SELECT * FROM `user` where sex = '1' and age = '19';
结果集id select_type table type possible_keys key key_len ref rows Extra
语句11SIMPLE user ref index_age_sex index_age_sex4const1null
语句21SIMPLE user ALL null null null null10Using where
语句31SIMPLE user ref index_age_sex index_age_sex5const,const1null
【4】索引对查询的影响-----加索引----使⽤和未使⽤索引的对⽐-----查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引时，查询中将使⽤索引。如果OR前后有⼀个条件的列不是索引，那么查询中将不使⽤索引。
语句1：explain SELECT * FROM `user` where (age = '19' OR isDeleted = '0');
语句2：explain SELECT * FROM `user` where (sex = '1' OR age = '19'); -- 联合索引
语句3：explain SELECT * FROM `user` where (name = 'name1' OR id = '1'); -- 独⽴索引
-- alter table user engine = innodb;
结果集id select_type table type possible_keys key key_len ref rows Extra
语句11SIMPLE user ALL index_age_sex null null null10Using where
语句21SIMPLE user ALL index_age_sex null null null10Using where
语句31SIMPLE user ref PRIMARY,id_unidx,name_unidx index_age_sex null null10Using where
-- alter table user engine = myisam;
结果集id select_type table type possible_keys key key_len ref rows Extra
语句11SIMPLE user ALL index_age_sex null null null10Using where 语句21SIMPLE user ALL index_age_sex null null null10Using where
语句31SIMPLE user index_merge PRIMARY,
id_unidx,
name_unidx
name_unidx,
PRIMARY
32,4null2
Using union
(name_unidx,PRIMARY);
Using where
很多查询中需要使⽤⼦查询。⼦查询可以使查询语句很灵活，但⼦查询的执⾏效率不⾼。⼦查询时，MySQL需要为内层查询语句的查询结果建⽴⼀个临时表。然后外层查询语句在临时表中查询记录。查询完毕后，MySQL需要撤销这些临时表。因此，⼦查询的速度会受到⼀定的影响。如果查询的数据量⽐较⼤，这种影响就会随之增⼤。在MySQL中可以使⽤连接查询来替代⼦查询。连接查询不需要建⽴临时表，其速度⽐⼦查询要快。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论