你的sql慢可能还真是因为索引未命中

今天收到了一则sql优化的小需求

一个普普通通单表分页查询 居然能达到1s以上 （已知表中数据为百万左右、大于1s默认为慢sql

SELECT
	*
FROM
	TABLE
WHERE
	COLUMN1 = 1
AND COLUMN2 = 2
AND COLUMN3 LIKE "%123%"
ORDER BY
	id ASC
LIMIT 50 OFFSET 0

对表名和字段名做隐式了 大致就是这么个情况


这到底是为什么呢？我们先排查一下

1. Like %param%

在模糊查询普通情况下，%在前是会进行全表扫描的，假如我们把最前面的%去除会有什么样的结果呢

查询时间变成了0.017！直线下降啊！！！
但是这并不适用于我们的业务，模糊查询不能指定初始参数哇
不过已经开始摸清问题所在了，和索引仿佛有着密不可分的关系

2. 该表是否有正常使用索引

可以看到 是正常的使用着 都是B-TREE的索引方法 当然也有必要的查询联合索引
是不是各种索引并没有命中？

3.分析原sql

EXPLAIN SELECT
	*
FROM
	TABLE
WHERE
	COLUMN1 = 1
AND COLUMN2 = 2
AND COLUMN3 LIKE "%123%"
ORDER BY
	id ASC
LIMIT 50 OFFSET 0

分析一下原sql的索引场景情况 这里有两个很重要的参数暴露了根本问题

字段名称	含义
possible_keys	分析sql里含盖的所有索引 并非实际使用到的索引
key	实际使用到的索引

预想的应该是在本次查询会调用联合索引进行查询 但是实际上mysql的选择器却选择了主键
从而导致慢sql的产生

那么如何让实际使用的索引命中正确呢？

这里并不建议将 like ’%param%‘ 的前%进行删除 不满足业务需要。同时在不清楚业务具体需求下，也不建议将order by id的排序方式进行修改，避免修铁轨撞火车的情况。

• 强制命中法

SELECT
	*
FROM
	TABLE
	FORCE INDEX (IDX_COLUNM1_COULNM2_COLUM3)
WHERE
	COLUMN1 = 1
AND COLUMN2 = 2
AND COLUMN3 LIKE "%123%"
ORDER BY
	id ASC
LIMIT 50 OFFSET 0

运用FORCE INDEX(索引名称）去强制命中索引进行查询 是可以完成目前我们所遇到的慢sql问题

• 忽略索引法

SELECT
	*
FROM
	TABLE
	IGNORE INDEX (PRIMARY)
WHERE
	COLUMN1 = 1
AND COLUMN2 = 2
AND COLUMN3 LIKE "%123%"
ORDER BY
	id ASC
LIMIT 50 OFFSET 0

运用IGNORE INDEX(索引名称）去忽略mysql选择的主键 也是可以完成目前我们所遇到的慢sql问题

• 拐弯磨脚法

SELECT
	t1.*
FROM
	TABLE t1
	RIGHT JOIN 
	(SELECT id FROM TABLE WHERE
				COLUMN1 = 1
			AND COLUMN2 = 2
			AND COLUMN3 LIKE "%123%") AS t2 
	ON t1.id = t2.id		
ORDER BY t1.id ASC
LIMIT 50 OFFSET 0

索引命中的explain结果