Mysql expain 及优化记录（索引）

某次优化记录：

操作记录 operation_alarm 2000万条数据

CREATE TABLE `operation_alarm` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `shop_id` bigint(20) NOT NULL,
  `shop_name` varchar(501) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL,
  `modify_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=2914 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作记录表';

需要检索shop_id 4260, 某个时间段的记录

select count(*) from operation_alarm where create_time >= date("2020-02-23") and create_time <= date("2020-03-23") and shop_id=4260;

explain看一下

type：ALL  全表扫描。给create_time加索引

alter table operation_alarm add index create_time_idx(create_time);

重新select

select count(*) from operation_alarm where create_time >= date("2020-02-23") and create_time <= date("2020-03-23") and shop_id=4260;

没啥变化，重新explain

还是全表扫描，去掉shop_id测试一下

explain  select count(*) from operation_alarm where create_time >= date("2020-02-23") and create_time <= date("2020-03-23");

命中索引了，改Sql语句

explain  select * from operation_alarm where create_time >= date("2020-02-23") and create_time <= date("2020-03-23");

又全表扫描，这里不知道为什么，先放下。把Sql改一下，缩短时间

explain  select shop_id from operation_alarm where create_time >= date("2020-03-22") and create_time <= date("2020-03-23");

又命中索引了。网上搜索了一下，找到2个，可能是因为：

1.优化器优化成全表扫描取决与使用最好索引查出来的数据是否超过表的30%的数据。

2.在查询数据条数约占总条数五分之一以下时能够使用到索引，但超过20%时，则使用全表扫描了。

给shop_id加索引

alter table operation_alarm add index create_time_idx(create_time);

查看表索引命令：show index from operation_alarm;

重新select

select count(*) from operation_alarm where create_time >= date("2020-02-23") and create_time <= date("2020-03-23") and shop_id=4260;

索引命中了，这里type是ref，索引性能排序(从低到高）ALL，index，range，ref，eq_ref，const，system，NULL

TODO：这里是否应该用联合索引？

告一段落

 

参考资料：

后端程序员必备：书写高质量SQL的30条建议

MySQL Explain详解

MySQL索引优化看这篇文章就够了！

MySQL 索引及优化实战(转载自蒲苇 CSDN技术头条)