关于MySql的索引问题

目录

序

条件对命中索引的影响

条件里写的函数对命中索引会不会有影响？

条件里写的like对命中索引会不会有影响？

写在字select里的字段会不会命中索引

---

序

我打算在这里写一些工作中遇到的，关于MySql的问题。有多短，不知道，有多长，我也不知道，写着看吧，可能是持续更新。

条件对命中索引的影响

条件里写的函数对命中索引会不会有影响？

有这么一个表，如图1。

图1，有个主键是ID

 表里大概有32万条数据。在没有索引的情况下，执行以下语句。

select count(1) as num 
from wms_business_doc 
where businesstype = '1' and 
createdate >= '2021-12-07 00:00:00' and 
createdate <= '2021-12-14 23:59:59';

执行100次的平均时间是0.791秒。

给docno加上唯一索引。

ALTER TABLE `sapwms`.`wms_business_doc` 
ADD UNIQUE INDEX `UK_DOCNO`(`docno`) USING BTREE;

执行100次的平均时间是0.693秒。

测试结果有点儿意思。如果说索引对查询语句没有影响吧，执行100次的平均时间减少了近0.1秒。速度提升了10%，但说索引对查询没有影吧，也符合逻辑，毕竟查询条件没有命中索引。见图2。

 图2

试试看把createdate字段也定义成索引，看看对这条查询语句的效率有什么样的影响。

ALTER TABLE `sapwms`.`wms_business_doc` 
ADD INDEX `ID_CREATEDATE`(`createdate`) USING BTREE;

 命中了索引之后的效率果然不一般，执行了20次，其中18次用时0.001s，2次用时0.002s，效率提升明显。

图3

以上这些都是在预想内的，改造一下这条丑陋的语句看会有什么效果。把日期给格式化一下去比较。

select count(1) as num 
from wms_business_doc 
where businesstype = '1' and 
DATE_FORMAT(createdate ,'%Y-%m-%d') >= '2021-12-07' and 
DATE_FORMAT(createdate ,'%Y-%m-%d') <= '2021-12-14';

结果还真如网上所说的，索引字段一旦放到函数里，就不会再命中索引。执行效率与增加索引前类似。

图4

条件里写的like对命中索引会不会有影响？

又做了测试，既然使用函数会影响命中索引，那么如果用like会怎么样。

有这么一张表，负责记录物料的。物料号是唯一的，已经被设置成了唯一索引。

图5

图6

图7

图8

执行结果有点儿意思，从解释语句的结果上证明了，使用like关键字，会不会命中索引，和通配符的位置还是有一定关系的。如果通配符写在字符串结尾，是可以命中索引的，但如果通配符写在字符串中间或者开头，是命中不了索引的。

写在字select里的字段会不会命中索引

图9

图10 

 图11

实战中遇到的问题

问题1：

同事说有个查询功能非常慢，查询结果只有几条，效率大概是几分钟。第一个反应就是查询条件破坏了索引，先把查询语句的条件搞出来执行一下看看。

照着日志往下抄关键信息

select *
from tr_transport transportv0_
where DELETESIGN=0 and  1=1 and (billNo like '%4080022%')
order by billNo desc limit 15

只涉及一张表，那么先看看这个表有没有索引。

表是有索引的。

 一步步做减法，看看问题出在哪。

select *
from tr_transport 
where DELETESIGN=0 and 1=1

执行时长3秒多，第一个念头是，难道是like影响了效率？但转念一想，还是看看SQL语句效率慢到什么程度。随即继续测试

select *
from tr_transport 
where DELETESIGN=0 and 1=1 and (billNo like '%4080022%')

执行时长2秒多，也是秒级，问题不在这儿。

select *
from tr_transport 
where DELETESIGN=0 and 1=1 and (billNo like '%4080022%')
order by billNo desc 

执行时长2秒多，还是秒级，难不成问题出现在数据库和Web容器之间？

在测试环境打断点跑了一下，确认问题还是出在了SQL语句上。

 分析了如图的两条语句，发现是key是不一样的，难道问题出在这儿？那么就尝试一下怎么能把带limit关键字的语句的key和不带limit关键字的语句的key弄成一致的。

思路是把索引里的字段加到order by里试试。

看来如图这么写不行，那就接着加。

这么写是可以的，执行速度也是秒级的了。但搞不懂，分析结果里的key是idx_tr_transport_DELETESIGN，当我在order by关键字里写order by DELETESIGN ,billNo的时候，并没有用到这个key啊。这是咋回事儿，问题解决了，但原理不清楚。

未完，待续。。。。