Mysql索引踩坑指北

前言

老王：为什么查询这么慢？
小新：已经给表加索引了。
老王：那为什么索引没生效？
小新：……

Mysql是常用的数据库，在使用过程中为加速查询，增加索引是常规操作，但操作不当会出现索引失效让人尴尬的场景。

今天我们就简单做一个索引相关的总结说明，仅包括InnoDB存储引擎。不想看到最后的同学直接看总结。

2.总结

2.1所有索引

1. 索引字段慎用like模糊查询;
2. 索引字段慎用between&in范围查询;
3. 索引字段慎用逻辑判断or，!=;
4. 索引字段在where语句中慎用函数;
5. 索引字段类型不能发生转换;

2.2复合索引

1. 索引最左侧原则要牢记；

3.EXPLAIN说明

官方文档是我们的第一手资料：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types

4.EXPLAIN输出格式

5.要关注的列

5.1.Type

• 表示表的关联join的类型，非常重要，官方文档。
• 常见的几种，性能从好到坏由上向下

5.1.1.Const

在命中主键或唯一索引时会出现；

5.1.2.Ref

在命中普通索引时会出现；

5.1.3.Index_merge

在同时命中多个索引时会出现；

比如对两个索引字段进行or查询：

5.1.4.Range

在查询条件中对索引字段使用范围查询会出现，比如：between ... and 或 In 等操作；

比如对主键字段id进行between查询：

5.1.5.Index

对所有索引字段进行扫描，可以理解为ALL；

5.1.6.ALL

全表扫描，最不想出现的情况；

比如对索引字段进行not in操作：

5.2.Possible_keys

表示查询时，可能使用的索引；

比如表中有两个索引：

在查询条件中都指定的情况下：

5.3.Key

表示实际命中的索引；

5.4.Key_len

索引字段的长度；

5.5.Ref

列与索引的比较；

5.6.Rows

扫描出的行数(估算的行数)，InnoDB不准；

5.7.Filtered

按条件过滤的行数百分比（越多越好）；

5.8Extra

执行情况的描述的额外说明；这个还没闹明白；

6.主键索引&普通索引

小新：不是说好的加了索引就起飞。我明明配置了索引，为什么翻车了？

老王：这个嘛……有可能是姿势不太对：）

6.1.模糊查询like

1. 数值型主键使用like会让索引失效
   

2. 字符型主键like 'abc%'会生效

3. 字符型主键like '%abc'会失效
   

   6.2.范围查询between,in

4. 对索引字段使用between会让索引失效

5. 对索引字段使用in会让索引失效
   

   6.3.逻辑判断or，!=

6. 对索引字段使用or会让索引失效
   

7. 对索引字段使用!=会让索引失效

8. 对索引字段使用>,<会让索引失效
   

   6.4.列参与计算

9. 对索引字段进行计算，会让索引失效
   

   6.5.数据类型转换

10. 对索引字段进行转换类型，会让索引失效
    

7.复合索引

小新：哈哈，我对所有字段增加一个复合索引，这下不会失效了吧！

老王：……

在mysql中，复合索引要生效，必须满足最左前缀原则，即索引左侧字段优先，意思是查询条件必须精确匹配复合索引的最左边的列，才会构建对应列的复合索引树。

比如我们有a,b,c,d四个列，使用a,b,c构建一个复合索引：(a,b,c)，这个复合索引的最左侧字段为a，在查询条件中，必须优先匹配a这个字段，否则索引不会生效。

可以理解为复合索引(a,b,c)相当于三个复合索引：(a),(a,b),(a,b,c)

查询条件中，必须满足上面三个复合索引条件，才能正确命中索引。

#构建复合索引
CREATE INDEX index_user_order_amount ON test.`tb_order`(`user_id`,`order_id`,`order_amount`);

#复合索引生效---->type列:ref;'ref'列：const
explain select * from test.tb_order where user_id ='user_0'

#复合索引生效---->type列:ref;'ref'列：const,const
explain select * from test.`tb_order` where `user_id` ='user_0' and `order_id` = 'bacfbdf8-bf4d-11ed-aca9-a86daa7b49cc'

#复合索引生效---->type列:ref;'ref'列：const,const,const
explain select * from test.`tb_order` where `user_id` ='user_0' and `order_id` = 'bacfbdf8-bf4d-11ed-aca9-a86daa7b49cc' and order_amount = 27

#复合索引不生效---->type列:'ALL'
explain select * from test.`tb_order` where `order_id` = 'bacfbdf8-bf4d-11ed-aca9-a86daa7b49cc'

#复合索引不生效---->type列:'ALL'
explain select * from test.`tb_order` where `order_id` = 'bacfbdf8-bf4d-11ed-aca9-a86daa7b49cc' and order_amount = 27

#复合索引生效---->type列:ref;'ref'列:const;
explain select * from test.`tb_order` where `user_id` ='user_0' and order_amount = 27

相关脚本

#相关sql脚本  

#创建数据表-test.tb_book
drop table if exists test.tb_book;
create table test.tb_book (
    book_id varchar(50),
    book_name varchar(100),
    book_auth varchar(50) null,
    publication_date date ,
    primary key (`book_id`)
) engine = InnoDb default charset=utf8;

#创建数据表-test.tb_order
CREATE TABLE test.`tb_order`(
    `id` INT  NOT NULL AUTO_INCREMENT COMMENT '主键',
    `order_id` VARCHAR(50) DEFAULT '' COMMENT '订单id',
    `product_id` VARCHAR(50) DEFAULT '' COMMENT '产品id',
    `user_id` VARCHAR(50) DEFAULT '' COMMENT '用户id',
    `order_amount` INT DEFAULT '0' COMMENT '订单金额',
    `create_time` DATETIME DEFAULT NOW(),
    `update_time` DATETIME DEFAULT NOW(),
    PRIMARY KEY (`id`) 
)ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT='订单表'
;

#查看表的索引信息
SHOW INDEX FROM test.tb_user;
SHOW INDEX FROM test.`tb_order`;

#删除索引
DROP INDEX index_order_id ON test.`tb_order`;
DROP INDEX index_user_id ON test.`tb_order`;
DROP INDEX index_order_amount ON test.`tb_order`;
DROP INDEX index_user_order_amount ON test.`tb_order`;

#创建索引
CREATE INDEX index_order_id ON test.`tb_order`(`order_id`);
CREATE INDEX index_user_id ON test.`tb_order`(`user_id`);
CREATE INDEX index_order_amount ON test.`tb_order`(`order_amount`);
#创建索引
CREATE INDEX index_user_order_amount ON test.`tb_order`(`user_id`,`order_id`,`order_amount`);