前言
通过使用Explain分析,可以帮助选择更好的索引和写出更优化的查询语句。
Explain列解释
id:select的查询序列号,表示查询的顺序
select_type:显示对应的查询是简单还是复杂SELECT
table:显示这一行数据是关于哪张表的
type:显示连接使用了何种类型
possible_keys:可能应用再这张表中的索引,一个或多个,但是不一定被查询实际使用。
key:实际使用的索引,如果为null,表示没有使用索引,查询中若是用来覆盖索引,则该索引仅出现在key列表中
key_len:表示索引中 使用的字节数,可通过该列计算查询中使用的索引长度,在不损失精确性的情况下,长度越短越好。
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。
rows:根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数。
Extra:包含不适合在其他列显示但十分重要的额外信息。在这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢。
SQL脚本
CREATE TABLE `test03` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` char(10) DEFAULT NULL,
`c2` char(10) DEFAULT NULL,
`c3` char(10) DEFAULT NULL,
`c4` char(10) DEFAULT NULL,
`c5` char(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `test03` VALUES ('1', 'a1', 'a2', 'a3', 'a4', 'a5');
INSERT INTO `test03` VALUES ('2', 'b1', 'b2', 'b3', 'b4', 'b5');
INSERT INTO `test03` VALUES ('3', 'c1', 'c2', 'c3', 'c4', 'c5');
INSERT INTO `test03` VALUES ('4', 'd1', 'd2', 'd3', 'd4', 'd5');
INSERT INTO `test03` VALUES ('5', 'e1', 'e2', 'e3', 'e4', 'e5');
创建复合索引
CREATE index idx_test03_c1234 on test03(c1,c2,c3,c4);
查看索引使用情况
1.explain select * from test03 where c1='a1';
2.explain select * from test03 where c1='a1'and c2='c2';
3.explain select * from test03 where c1='a1'and c2='a2' and c3='a3';
4.explain select * from test03 where c1='a1'and c2='a2' and c3='a3' and c4='a4';
ref里的值个数表示用到了几个值的索引
5.explain select * from test03 where c1='a1'and c2='a2' and c4='a4'and c3='a3';
6.explain select * from test03 where c4='a4'and c3='a3'and c2='a2' and c1='a1';
4、5、6这三个语句使用的索引个数都是4个,全部使用的时候和顺序无关,最好按照索引的创建顺序使用
7.explain select * from test03 where c1='a1' and c2='a2'and c3>'a3'and c4='a4';
8.explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
9.explain select * from test03 where c1='a1' and c2='a2' and c4='a4' ORDER BY c3;
索引两大功能:排序和查找,c3在这里作用排序,没有统计到里面
10.explain select * from test03 where c1='a1' and c2='a2' ORDER BY c3;
11.explain select * from test03 where c1='a1' and c2='a2' ORDER BY c4;
12.explain select * from test03 where c1='a1' and c5='a5' ORDER BY c2,c3;
只有c1一个字段索引,c2,c3用于排序,无filesort
13.explain select * from test03 where c1='a1' and c5='a5' ORDER BY c3,c2;
出现filesort,我们建的索引是1234没有按照顺序来,3 2颠倒了
14.explain select * from test03 where c1='a1' and c2='a2' ORDER BY c2,c3;
15.explain select * from test03 where c1='a1' and c2='a2' and c5='a5' ORDER BY c2,c3;
用c1、c2两个字段索引,但c2、c3用于排序无filesort
16.explain select * from test03 where c1='a1' and c2='a2' and c5='a5' ORDER BY c3,c2;
已有常量c2,c2,c3用于排序无filesort
17.explain select * from test03 where c1='a1' and c5='a5' ORDER BY c3,c2;
18.explain select * from test03 where c1='a1' and c4='a4' GROUP BY c2,c3;
19.explain select * from test03 where c1='a1' and c4='a4' GROUP BY c3,c2;
优化口诀
全职匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
LIKE百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用;
VAR引号不可丢,SQL高级也不难!