发现问题:内网系统,随着数据量增多,页面一个统计功能特别慢,跟踪定位分析,发现统计sql有问题,有个关键的索引未生效。千万级的数据,全表扫描.......最后缩减到最简单的一句,select * from table where zjhm like 'xxxx%',还是没用到索引,什么情况?百度N篇索引失效,各种试,然后发现了个关于索引失效有趣的问题。
试验过程:
内网oracle,外网Mysql,数据量一千七百万,条件:证件号码,普通索引;
Sql:select * from table t where t.zjhm like 'xxxx%';
数据分布:天津旅馆业数据(别纳闷怎么只有这么点数据,这新系统上线才八个月)。
试验结果表:
编号 | like条件(总18位) | 数量(单位:万) | 比例(总1700万) | Mysql(5.7) | Oracle(11g) |
1 | 12 | 330 | 19.41% | 否 | 否 |
2 | 120103 | 24.7 | 1.45% | 是 | 否 |
3 | 12010319 | 24.2 | 1.42% | 是 | 否 |
4 | 65 | 10 | 0.59% | 是 | 否 |
5 | 120103199 | 5.6 | 0.33% | 是 | 是 |
6 | 653 | 0.96 | 0.056% | 是 | 否 |
7 | 6531 | 0.56 | 0.033% | 是 | 是 |
8 | 8 | 0.24 | 0.014% | 是 | 是 |
sql里解释计划的图我就不发了,测验结果绝对没问题,好奇的是有两组数据。第5,6组数据,六组比五组少很多数据,但奇怪的是五组用到索引了,六组没用到。难道这和like中条件的所占位数还有关系么???
我的结论:like 'xxxx%' 时是否使用索引,与该条件数量和数据总量比例有关,具体多少不清楚,虽然知道大量重复数据会导致不走索引,like 时应该和这个同理,但百度了N篇,没一篇有提到过这现象。另怀疑还有可能和like字符长度位数占总位数比例有关。我这只是实践得出,具体是否正确,有待证实!
有深入研究过Mysql,Oracle索引机制的大神们,欢迎点评,谢谢!