为什么我加了索引，SQL执行还是这么慢（一）？

时间：2019-11-11 01:02:38 阅读：129 评论：0 收藏：0 [点我收藏+]

在MySQL中，有一些语句即使逻辑相同，执行起来的性能差异确实极大的。

先抛出一个结论：如果想使用索引树搜索功能，就不能使用数据库函数来处理索引字段值，而是在不改变索引字段值的同时，自己通过SQL语句来实现逻辑

条件字段函数操作

假设我们现在维护了一张系统交易表：

mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我们在表中插入5条测试数据：

insert into tradelog (id,tradeid,t_modified) values(1,‘a‘,‘2017-03-15‘);
insert into tradelog (id,tradeid,t_modified) values(2,‘b‘,‘2017-06-11‘);
insert into tradelog (id,tradeid,t_modified) values(3,‘c‘,‘2017-07-03‘);
insert into tradelog (id,tradeid,t_modified) values(4,‘d‘,‘2018-08-11‘);
insert into tradelog (id,tradeid,t_modified) values(5,‘e‘,‘2018-09-12‘);

技术分享图片

SQL执行结果

接下来我们需要查询2016年到2018年，所有7月份的数据。

SQL可以这样写：

select count(*) from tradelog where month(t_modified)=7;

我们可以通过explain命令，对这行语句的执行结果进行分析。

技术分享图片

SQL执行结果

即该语句使用了t_modified索引，row=5，代表了该语句进行了全索引扫描，Using index表示使用了覆盖索引。

那么为什么会这样呢？

首先执行操作用哪个索引，是优化器决定的，这里可以使用的索引是主键索引和t_modified索引。在对比索引树大小后发现，t_modified索引树更小，所以优化器选择使用t_modified索引，但是为什么还会使用全索引扫描，而不是索引树查找的方式呢？原因是innodb索引树查找的方式，是由于同级的兄弟节点具有顺序性（类似于二分查找，要求数据有序），锁索引值的顺序性被破坏，优化器就只能选择全索引扫描的方式执行语句，所以

对索引字段的函数操作，会破坏索引值的顺序性，导致优化器放弃走索引树搜索功能。

我们如果想使用索引树搜索功能，就不能使用数据库函数来处理索引字段值，而是在不改变索引字段值的同时，自己通过SQL语句来实现逻辑，上述SQL语句可做如下的改写：

select count(*) from tradelog where
(t_modified >= ‘2016-7-1‘ and t_modified<‘2016-8-1‘) or
(t_modified >= ‘2017-7-1‘ and t_modified<‘2017-8-1‘) or 
(t_modified >= ‘2018-7-1‘ and t_modified<‘2018-8-1‘);

接着我们使用explain对其进行分析：