mysql 小数据量表 like模糊查询慢sql排查

最新推荐文章于 2024-07-24 16:18:52 发布

诗心88

最新推荐文章于 2024-07-24 16:18:52 发布

阅读量305

点赞数 10

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_59937496/article/details/140604979

版权

之前有个需求，产品给了15w的数据，要求我们支持对title支持模糊查询+拼音模糊查询

SELECT * FROM `tbl` WHERE title LIKE '%keyword%' or pinyin LIKE '%keyword%' LIMIT 100;

在开发环境和测试环境都表现出正常的查询速度，但发布到线上出现了大量的慢sql，大都3~4s左右，偶尔出现20多秒的慢sql。

排查数据量一致，监控显示内存/cpu未满，iops跑满，但开发/测试相同sql都是较低的iops。

第一点很快定位到，我们线上的阿里云mysql配置不如开发/测试的...因为开发/测试的mysql共用了其他团队配置较好的mysql。在升级了线上mysql后，查询速度基本跟开发/测试一致，但依旧会出现偶尔的慢sql，伴随较高的iops。

重新复盘这个问题，一开始我们方向着重于mysql自身的问题，比较升级前和升级后mysql的差异，定位到innodb_buffer_pool_size这个mysql的参数配置，认为是这个参数的差异，导致mysql内存不足以支撑查询锁需要的缓存。

但后来突然想到mysql的配置不应该成为15w数据的like查询的瓶颈，这也是我们当时评估设计时没有太过计较使用了like的原因，重新审视我们的表结构。

对data的这个超大字段表示了怀疑，我在本地分别建了一张不含data的表和含data的表，写入相同的数据量，果然相同的sql展现了300倍左右的查询速度差异（10ms 对比 3~4s）。

定位到问题，接下来就很好做了，由于对mysql的执行计划了解没那么深入，所以确定了三个方案看看效果。

1.分别建id+title+pinyin的查询表tbl_query和id+data的数据表tbl_data，两张表的主键相同，在查询表查询主键id，在data表通过id查询数据。这是最差的也是最不可能再出现问题的方案。

SELECT id FROM `tbl_query` WHERE title LIKE '%keyword%' or pinyin LIKE '%keyword%' LIMIT 100;

SELECT * FROM `tbl_data` WHERE id in ("id1", "id2");

2.但发现select id 在原表查询也并不会如同select * 带来相同的性能问题，所以不必拆成两张表

SELECT id FROM `tbl` WHERE title LIKE '%keyword%' or pinyin LIKE '%keyword%' LIMIT 100;

SELECT * FROM `tbl` WHERE id in ("id1", "id2");

3.但是在改造成子查询时，却出现了跟之前慢sql一样的性能问题

select * from tbl WHERE id in (SELECT id FROM `tbl` WHERE slug LIKE '%keyword%' or pinyin LIKE '%keyword%') LIMIT 100

对方案3出现的性能问题表示很疑惑，但考虑时间成本问题没有继续深究，最终使用了方案2来解决。

关注