mybatisPlus的@TableLogic逻辑删除注解导致联合索引失效的坑

1.问题

    假设一个mysql的一张单表叫student数据量有500w左右，如果没有选择适当的列加索引的话，相关的查询就会全表扫描，使用的是mybatisPlus作为ORM框架，并且在实体上的逻辑删除字段del(0:否,1:是)上加了@TableLogic注解，那么StudentService中的业务查询代码如下所示：

this.getOne(Wrappers.<Student>lambdaQuery().eq(Student::getName, xxxx).eq(Student::getAddress,xxxx).last("limit 1"))

    如果在student的name，address两列上建立了联合索引如下：

ALTER TABLE student ADD INDEX `index_name_address` (`name`,`address`); -- 索引默认是BTREE

    请问各位看官上面那个StudentServer的this.getOne的查询有没有走index_name_address索引?

    答案是：不会走索引，可以使用explain查根据上面业务代码的sql的执行计划

explain SELECT * FROM student WHERE name = 'xxxx' AND address = 'xxxxxx' LIMIT 1

    如果按照上面的业务代码使用眼睛翻译的sql确实会认为是根据name和address两个条件来查的，但实际上却不是这种的，这里也是有点坑的，如果你以为是这种加索引的，那么即使是加了索引也不会走索引，在那么大数据量的表中加索引还是有风险的，那如何来处理解决呢? 请听下面分析讲解。

2.原因

    首先可以将mybatisPlus的sql打印开开，然后在测试环境写一个test方法，测试下上面那个业务代码，把sql抓到，然后你就会豁然开朗了，开启mybatisPlus的sql打印日志配置如下：

mybatis-plus:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpltypeAliasesPackage: xxx.xxxx.entitymapper-locations: classpath:mapper/*.xml

    在测试环境写一个test用例方法就省略了，抓到的sql如下：

SELECT * FROM student WHERE del = 0 AND ( name = 'xxxx' AND address = 'xxxxxx') LIMIT 1

    问题的根本原因就是mybatisPlus使用了@TableLogic注解在实体中标注了逻辑删除字段，最终的sql会将逻辑删除的字符放在一个条件上，会导致联合索引失效（最左匹配的原则），如果生产表数据太大，sql索引失效导致全表扫描，在并发比较高的情况下，一个sql的慢会将mysql数据库cpu干到百分之好几百，导致业务系统不可用，验证可能直接把mysql数据库拖垮，这种就是生产级别的事故了。

3.解决方法

3.1 方法1

ALTER TABLE student ADD INDEX `index_del_name_address` (`del`,name`,`address`);

    修改原索引

3.2 方法2

ALTER TABLE student ADD INDEX `index_name` (`name`);
ALTER TABLE student ADD INDEX `index_address` (`address`);

    这种方式建议一开始就这种搞，生产单表数据量太大，不建议删除之前的索引后新增两个单列索引

4. 建索引的几大原则

4.1.最左前缀匹配原则，非常重要的原则

    mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

4.2.=和in可以乱序

    比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

4.3.尽量选择区分度高的列作为索引

    区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4.4.索引列不能参与计算，保持列“干净”

    比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

4.5.尽量的扩展索引，不要新建索引

    比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

    总之，索引不是越多越好越快，而是加的恰到好处，所以遵循上面的原则来加是比较好的。

5.好文分享

https://www.bilibili.com/video/BV1UL411z7WD/?buvid=XX3844F3338BD8CFE89E44F906B4309A8CD6E&from_spmid=main.my-favorite.0.0&is_story_h5=false&mid=3McxpjV8dqbNqf%2FOSLyb1A%3D%3D&p=1&plat_id=116&share_from=ugc&share_medium=android&share_plat=android&share_session_id=3c6b1623-f34f-4fad-8b07-62e11e04b9f8&share_source=WEIXIN&share_tag=s_i&spmid=united.player-video-detail.0.0&timestamp=1723546033&unique_k=XitSYec&up_id=3493270011251381https://www.cnblogs.com/zmc60/p/14533123.html
https://mp.weixin.qq.com/s/hcmRh493yWJUJTJl0tlZWw
https://tech.meituan.com/2014/06/30/mysql-index.html

6.总结

    当生产单表数据量上百万级别的时候，如果要修改表结构的DDL操作，加索引这种操作都还是有一定的风险的，建议在业务低峰期操作，还是要相当的小心，尽量不要去随意操作，否则一个不小心就把系统搞挂了，那个可是要背锅的，mysql在表数据量非常大的时候能力就相对来说弱了一点，所以需要寻求强悍的在大数据量下的查询分析引擎方案，mysql、redis等单一数据库撸到底（可以尝试一些新东西，然后组合搭配使用）已经在这种大数据体量下力不从心了，所以就需要大数据的架构来解决大数据量的写入查询分析等痛点问题，本次分享到此结束，希望我的分享对你有所启发和帮助，请一键三连，么么么哒！