EXPLAIN

建表

CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;
CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

设置参数 log_bin_trust_function_creators

创建函数，假如报错，需开启如下命令：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。
创建函数

DELIMITER //
CREATE FUNCTION rand_string1(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

创建存储过程

创建往s1表中插入数据的存储过程：

DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
创建往s2表中插入数据的存储过程：

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s2 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
调用存储过程
s1表数据的添加：加入1万条记录：

CALL insert_s1(10001,10000);
s2表数据的添加：加入1万条记录：

CALL insert_s2(10001,10000);
explain是sql分析的一个重要的工具，可以帮助我们分析sql具体的执行方式，用的什么索引等。使用EXPLAIN并不会真正的执行sql语句。首先我们看一下explain的一些列的介绍。

id
对于连接查询来说，一个SELECT关键字后边的FROM字句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的，比如：

EXPLAIN SELECT * FROM s1 INNER JOIN s2;
可以看到，上述连接查询中参与连接的s1和s2表分别对应一条记录，但是这两条记录对应的id都是1。出现在前边的表表示驱动表，出现在后面的表表示被驱动表。所以从上边的EXPLAIN输出中我们可以看到，查询优化器准备让s1表作为驱动表，让s2表作为被驱动表来执行查询。

对于包含子查询的查询语句来说，就可能涉及多个SELECT关键字，所以在包含子查询的查询语句的执行计划中，每个SELECT关键字都会对应一个唯一的id值，比如这样：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
小结:

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
2.table

不论我们的查询语句有多复杂，里边包含了多少个表 ，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

EXPLAIN SELECT * FROM s1;
这个查询语句只涉及对s1表的单表查询，所以 EXPLAIN 输出中只有一条记录，其中的table列的值为s1，表明这条记录是用来说明对s1表的单表访问方法的。

下边我们看一个连接查询的执行计划

EXPLAIN SELECT * FROM s1 INNER JOIN s2;
这个就有两条记录。

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
select_type
一条大的查询里面可以包含若干个SELECT关键字，每一个SELECT关键字代表着一个小的查询。MYSQL为每一个SELECT 关键字代表的小查询都定义了一个称为select_type的属性。select_type有以下属性值。

具体分析如下：

SIMPLE
查询语句中不包含UNION、UNION ALL或者子查询的查询都算作是SIMPLE类型，比方说下边这个单表查询select_type的值就是SIMPLE:
EXPLAIN SELECT * FROM s1;
当然，连接查询也算是 SIMPLE 类型，比如：

EXPLAIN SELECT * FROM s1 INNER JOIN s2;
PRIMARY
对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type的值就是PRIMARY,或者是一个子查询，那么外面的是PRIMARY。比方说：
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

从结果中可以看到，最左边的小查询SELECT * FROM s1对应的是执行计划中的第一条记录，它的select_type的值就是PRIMARY。

UNION
对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询意外，其余的小查询的select_type值就是UNION。
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

UNION RESULT
MySQL 选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT。
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

SUBQUERY
如果该子查询不是相关子查询，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY，比如下边这个查询：
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

DEPENDENT SUBQUERY
如果该子查询是相关子查询。子查询使用了外面的表。该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY。
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key1 = s2.key2) OR key3 = 'a';

DEPENDENT UNION
使用union或unionall查询中,如果子查询依赖于外层查询 除了第一个子查询 其余的子查询都是dependent union
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key1 = s2.key2 UNION SELECT key1 FROM s1 WHERE key1 = 'b');

partitions (可略)
代表分区表中的命中情况，非分区表，该项为NULL。一般情况下我们的查询语句的执行计划的partitions列的值为NULL。
type ☆
执行计划的一条记录就代表着MySQL对某个表的 执行查询时的访问方法 , 又称“访问类型”，其中的 type 列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对s1表的查询。

访问方法如下： system ， const ， eq_ref ， ref ， ref_or_null ， index_merge ， range ， index ， ALL 。

我们详细解释一下：

system
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system
CREATE TABLE t(i int) Engine=MyISAM;
INSERT INTO t VALUES(1);
然后我们看一下查询这个表的执行计划：
EXPLAIN SELECT * FROM t;
可以看到type列的值就是system了，如果我们再添加一条数据，再看看
EXPLAIN SELECT * FROM t;
DROP TABLE t;
CREATE TABLE t(i int) Engine=InnoDB;

const
当我们根据主键或者唯一索引列与常数进行等值匹配时，访问方法就是const, 比如：
EXPLAIN SELECT * FROM s1 WHERE id = 10005;

eq_ref
在连接查询时，如果被驱动表是通过主键或者唯一索引列等值匹配的方式进行访问的。则对该被驱动表的访问方法就是eq_ref，比方说：
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
从执行计划的结果中可以看出，MySQL打算将s1作为驱动表，s2作为被驱动表，重点关注s2的访问 方法是 eq_ref ，表明在访问s2表的时候可以 通过主键的等值匹配 来进行访问。

ref
当通过普通的索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref，比方说下边这个查询：
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

ref_or_null
当对普通索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null，比如说：
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

index_merge
一般情况下对于某个表的查询只能使用到一个索引。我们的 where 中可能有多个条件涉及到多个字段，它们之间进行 OR。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
从执行计划的 type 列的值是 index_merge 就可以看出，MySQL 打算使用索引合并的方式来执行 对 s1 表的查询。

range
进行范围查询。
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
或者：
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
EXPLAIN SELECT * FROM s1 WHERE key1 between 'a' AND 'b';

index`
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
上述查询中的所有列表中只有key_part2 一个列，而且搜索条件中也只有 key_part3 一个列，这两个列又恰好包含在idx_key_part这个索引中，可是搜索条件key_part3不能直接使用该索引，只能扫描整个idx_key_part索引的记录，所以查询计划的type列的值就是index。

ALL
最熟悉的全表扫描，就不多说了，直接看例子：
EXPLAIN SELECT * FROM s1;

小结:

结果值从最好到最坏依次是：

system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

其中比较重要的几个提取出来（见上图中的粗体）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求）

possible_keys和key
在EXPLAIN语句输出的执行计划中，possible_keys列表示在某个查询语句中，对某个列执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。比方说下面这个查询：

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
上述执行计划的possible_keys列的值是idx_key1, idx_key3，表示该查询可能使用到idx_key1, idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定采用idx_key3。

key_len ☆
实际使用到的索引长度 (即：字节数)

帮你检查是否充分的利用了索引，值越大越好，主要针对于联合索引，有一定的参考意义。

EXPLAIN SELECT * FROM s1 WHERE id = 10005;
int 占用 4 个字节

EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
key2上有一个唯一性约束，是否为NULL占用一个字节，那么就是5个字节

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
key1 VARCHAR(100) 一个字符占3个字节，100*3，是否为NULL占用一个字节，varchar的长度信息占两个字节。

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
联合索引中可以比较，key_len=606的好于key_len=303

练习：

key_len的长度计算公式：

varchar(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)
ref

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

可以看到ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数，当然有时候更复杂一点:

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
rows ☆
预估的需要读取的记录条数，值越小越好。

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
filtered
某个表经过搜索条件过滤后剩余记录条数的百分比

如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
对于单表查询来说，这个filtered的值没有什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数 (即: rows * filtered)

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
从执行计划中可以看出来，查询优化器打算把s1作为驱动表，s2当做被驱动表。我们可以看到驱动表s1表的执行计划的rows列为9688，filtered列为10.00，这说明还要对被驱动表执行大约968次查询。

Extra ☆
顾名思义，Extra列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑选比较重要的额外信息介绍给大家。

No tables used
当查询语句没有FROM子句时将会提示该额外信息，比如：
EXPLAIN SELECT 1;

Impossible WHERE
当查询语句的WHERE子句永远为FALSE时将会提示该额外信息
EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

Using where
没有任何索引或者有除了索引外的别的字段
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';

No matching min/max row
当查询列表处有MIN或者MAX聚合函数，但是并没有符合WHERE子句中的搜索条件的记录时。
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';

Using index
当我们的查询列表以及搜索条件中只包含属于某个索引的列，在Extra列将会提示该额外信息。比方说下边这个查询中只需要用到idx_key1而不需要回表操作:
EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a';

Using index condition
有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询：
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

Using join buffer (Block Nested Loop)
在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法。
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

Not exists
当我们使用左外连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示这个信息：
EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

Using union(...)
如果出现Using union(...)提示，说明准备使用Union索引合并的方式执行查询;
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

Zero limit
当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读取任何记录，将会提示该额外信息
EXPLAIN SELECT * FROM s1 LIMIT 0;

Using filesort
有一些情况下对结果集中的记录进行排序是可以使用到索引的。
EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
需要注意的是，如果查询中需要使用filesort的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用文件排序的执行方式改为索引进行排序。

Using temporary
EXPLAIN SELECT DISTINCT common_field FROM s1;
再比如：
EXPLAIN SELECT common_field, COUNT() AS amount FROM s1 GROUP BY common_field;
执行计划中出现Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大的成本的，所以我们最好能使用索引来替代掉使用临时表，比方说下边这个包含GROUP BY子句的查询就不需要使用临时表：
EXPLAIN SELECT key1, COUNT() AS amount FROM s1 GROUP BY key1;
从 Extra 的 Using index 的提示里我们可以看出，上述查询只需要扫描 idx_key1 索引就可以搞 定了，不再需要临时表了。
2.索引失效案例
步骤1：建表
CREATE TABLE class (
id INT(11) NOT NULL AUTO_INCREMENT,
className VARCHAR(30) DEFAULT NULL,
address VARCHAR(40) DEFAULT NULL,
monitor INT NULL ,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE student (
id INT(11) NOT NULL AUTO_INCREMENT,
stuno INT NOT NULL ,
name VARCHAR(20) DEFAULT NULL,
age INT(3) DEFAULT NULL,
classId INT(11) DEFAULT NULL,
PRIMARY KEY (id)
#CONSTRAINT fk_class_id FOREIGN KEY (classId) REFERENCES t_class (id)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
步骤2：设置参数

命令开启：允许创建函数设置：
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。
步骤3：创建函数
保证每条数据都不同。
随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
假如要删除
drop function rand_string;
随机产生班级编号

用于随机产生多少到多少的编号

DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;

假如要删除

drop function rand_num;

步骤4：创建存储过程

创建往stu表中插入数据的存储过程

DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, name ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;

假如要删除

drop PROCEDURE insert_stu;

创建往class表中插入数据的存储过程

执行存储过程，往class表添加随机数据

DELIMITER //
CREATE PROCEDURE insert_class( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;

假如要删除

drop PROCEDURE insert_class;

步骤5：调用存储过程
class

执行存储过程，往class表添加1万条数据

CALL insert_class(10000);
stu

执行存储过程，往stu表添加50万条数据

CALL insert_stu(100000,500000);
步骤6：删除某表上的索引
创建存储过程
DELIMITER //
CREATE PROCEDURE proc_drop_index(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 AND index_name <>'PRIMARY' ;

每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束

DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;

若没有数据返回,程序继续,并将变量done设为2

OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index " , _index , " on " , tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END //
DELIMITER ;
执行存储过程
CALL proc_drop_index("study","student");
2.1 全值匹配我最爱
建立索引前执行：（关注执行时间）
SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';
建立索引
CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_classid ON student(age,classId);
CREATE INDEX idx_age_classid_name ON student(age,classId,name);
建立索引后执行：
SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND name = 'abcd';
2.2 最佳左前缀法则
在MySQL建立联合索引时会遵守最佳左前缀原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
举例1：
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd';
可以使用idx_age，但是不能使用idx_age_classid_name。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=1 AND student.name = 'abcd';
完全没有使用上索引。
举例2：
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.age=30 AND student.name = 'abcd';
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
结论：MySQL可以为多个字段创建索引，一个索引可以包含16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有用这些字段中第一个字段时，多列（或联合）索引不会被使用。
拓展：Alibaba《Java开发手册》
索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
2.3 计算、函数导致索引失效
举例：

student表的字段stuno上设置有索引
CREATE INDEX idx_sno ON student(stuno);
索引优化失效：
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
运行结果：
索引优化生效：
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;
再举例：
student表的字段name上设置有索引
CREATE INDEX idx_name ON student(NAME);
EXPLAIN SELECT id, stuno, name FROM student WHERE SUBSTRING(name, 1,3)='abc';
索引优化生效
EXPLAIN SELECT id, stuno, NAME FROM student WHERE NAME LIKE 'abc%';
2.4 类型转换导致索引失效
下列哪个sql语句可以用到索引。（假设name字段上设置有索引）
未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
name=123发生类型转换，索引失效。
2.5 范围条件右边的列索引失效
系统经常出现的sql如下：
DROP INDEX idx_name ON student;
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
那么索引 idx_age_classId_name 这个索引还能正常使用么？
不能，范围右边的列不能使用。比如：(<) (<=) (>) (>=) 和 between 等
将范围查询条件放置语句最后：
CREATE INDEX idx_age_name_classid ON student(age,name,classId);
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。（创建的联合索引中，务必把范围涉及到的字段写在最后）

2.6 不等于(!= 或者<>)索引失效

查看索引是否失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';
或者

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc';
2.7 is null可以使用索引，is not null无法使用索引

IS NULL: 可以触发索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
IS NOT NULL: 无法触发索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
结论：最好在设计数据库的时候就将字段设置为 NOT NULL 约束，比如你可以将 INT 类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串('')。

2.8 like以通配符%开头索引失效

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为'%'，索引就不会起作用。只有'%'不在第一个位置，索引才会起作用。

使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'ab%';
未使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab%';
拓展：Alibaba《Java开发手册》

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

2.9 OR 前后存在非索引的列，索引失效

在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。

因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此所以的条件列也会失效。

查询语句使用OR关键字的情况：

未使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
因为classId字段上没有索引，所以上述查询语句没有使用索引。

使用到索引

CREATE INDEX idx_name ON student(NAME);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';
因为age字段和name字段上都有索引，所以查询中使用了索引。你能看到这里使用到了index_merge，简单来说index_merge就是对age和name分别进行了扫描，然后将这两个结果集进行了合并。这样做的好处就是避免了全表扫描。

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 用于PostgreSQL索引维护的有用查询
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 索引的一些总结
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 【Leetcode 2474. 购买量严格增加的客户】MySQL用户变量编程解决严格递增连续子序列问题
• mysql发生连接异常Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
• 【LeetCode 2494. 合并在同一个大厅重叠的活动】MySQL用户变量编程解决区间合并问题
• 如何处理Mysql死锁
• 【LeetCode1747. 应该被禁止的 Leetflex 账户】MySQL用户变量编程；尝试维护一个multiset

索引 explain 情况 mysql索引explain情况mysql 索引 情况mysql 索引 情况mysql order 索引 常见 情况mysql 索引explain 索引mongodb explain explain mysql mysql-explain mysql_explain mysql-explain explain mysql