索引失效解析

                                                                             MYSQL索引失效解析

参考博客：https://blog.csdn.net/u014421556/article/details/52063904

前提学习：EXPLAIN列的解释

explain select id,name,age,phone from student where age not in(4,5);

id：SELECT 查询的标识符，每个 SELECT 都会自动分配一个唯一的标识符。

select_type：SELECT 查询的类型。

SIMPLE, 表示此查询不包含 UNION 查询或子查询
PRIMARY, 表示此查询是最外层的查询
UNION, 表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT, UNION 的结果
SUBQUERY, 子查询中的第一个 SELECT
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果

table：查询的是哪个表

type：显示连接使用了何种类型

通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

possible_keys：显示可能应用在这张表中的索引

key： 实际使用的索引。如果为NULL，则没有使用索引。

rows：MYSQL认为必须检查的用来返回请求数据的行数，MySQL 查询优化器根据统计信息， 估算 SQL 要查找到结果集需要扫描读取的数据行数，这个值非常直观显示 SQL 的效率好坏，原则上 rows 越少越好。

Extra：关于MYSQL如何解析查询的额外信息。

Using filesort
当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大。
Using temporary
查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化。
Using index
"覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错。

查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

一、准备工作：创建student表

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `name` varchar(64) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(3) unsigned NOT NULL DEFAULT '1' COMMENT '年龄',
  `phone` varchar(11) NOT NULL DEFAULT '' COMMENT '手机号码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8  COMMENT='学生索引分析表';

INSERT INTO `student` (`id`, `name`, `age`, `phone`) VALUES ('1', '小明', '20', '15019030000');
INSERT INTO `student` (`id`, `name`, `age`, `phone`) VALUES ('2', '小红', '21', '15019030001');
INSERT INTO `student` (`id`, `name`, `age`, `phone`) VALUES ('3', '小光', '22', '15019030002');
INSERT INTO `student` (`id`, `name`, `age`, `phone`) VALUES ('4', '小松', '23', '15019030003');
INSERT INTO `student` (`id`, `name`, `age`, `phone`) VALUES ('5', '小飞', '24', '15019030004');
INSERT INTO `student` (`id`, `name`, `age`, `phone`) VALUES ('6', '小胖', '25', '15019030005');
INSERT INTO `student` (`id`, `name`, `age`, `phone`) VALUES ('7', '小方', '26', '15019030006');
INSERT INTO `student` (`id`, `name`, `age`, `phone`) VALUES ('8', '小玉', '27', '15019030007');
INSERT INTO `student` (`id`, `name`, `age`, `phone`) VALUES ('9', '小斋', '28', '15019030008');
INSERT INTO `student` (`id`, `name`, `age`, `phone`) VALUES ('10', '小凯', '29', '15019030009');

创建复合索引、和单索引

create index idx_age on student(age);
create index idx_name_phone on student(name,phone);

查看索引

show index from student;

二、索引失效原因

全值匹配我最爱(索引使用恰当情况)

explain select id,name,age,phone from student where name = '小明' and phone = '15019030000';

1、最佳左前缀法则，带头大哥不能死，中间兄弟不能少(复合索引)

explain SELECT id,name,age,phone from student where phone = '15019030000';

2、不要在索引列上做任何操作(函数操作)

explain select id,name,age,phone from student where left(name,1) = '小' and phone = '15019030000';

3、对索引列进行运算导致索引失效,对索引列进行运算包括(+，-，*，/，! 等)

explain SELECT id,name,age,phone from student where age-1=9;

索引成功例子

explain SELECT id,name,age,phone from student where age=10;

4、尽量使用覆盖索引减少使用select *

覆盖索引：如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。

explain select * from student where name = '小';

explain select name from student where name = '小';

5、使用不等于（！= 或者<>）不能使用索引

explain select id,name,age,phone from student where age != 5;

6、使用 is null 或者 is not null 也不能使用索引

explain select id,name,age,phone from student where name is not null;

7、like 已通配符开头（%abc）导致索引失效 （解决方法：使用覆盖索引）

explain select id,name,age,phone from student where name like '%张%';

想用的话解决方法，使用覆盖索引

explain select name from student where name like '%张%';

8、少用or，用它来连接索引会失效

explain select id,name,age,phone from student where name = '张' or age = 2;

9、in 或者 not in 尽量少使用，也会导致索引失效

explain select id,name,age,phone from student where age not in(4,5);