SQL—— 优化

---

简介

优化慢SQL。
逻辑查询处理步骤

🍊 🌰（举例）

举例说明

🌰 1 ：in / not in

# 场景描述：查询A表.key1不包含B.key1以外的数据
SELECT A.key1, A.key2, A.key3 FROM A WHERE A.key1 NOT IN (SELECT key1 FROM B)
# 以上方式是不会走索引的
# 改为 NOT EXISTS
SELECT A.key1, A.key2, A.key3 FROM A WHERE NOT EXISTS (SELECT key1 FROM B WHERE B.key1 = A.key1)

# 场景描述：查询A表，key2字段包含 1,2,3的数据
SELECT key1, key2, key3 FROM A WHERE key2 IN (1, 2, 3)
# 解决1，通过BETWEEN AND提高
SELECT key1, key2, key3 FROM A WHERE key2 BETWEEN 1 AND 3
1
2
3
4
5
6
7
8
9
10

🌰 2：inner join、left join、right join

# 场景描述1: join查询时，查询速度慢
select A.key1, A.key2, A.key3 FROM A LEFT JOIN B ON A.key3 = B.key3
# 解决: ON 相关的字段需要添加索引
# 场景描述2：两表JOIN的字段都有索引，在关联查询时，通过查询计划没有使用索引，为什么
# 解决：两表的字段字符集可能不一致，校验字符集，保持一致
# 注：超过三张表禁止使用join（阿里规约）

1
2
3
4
5
6
7

🌰 3：like

# 场景描述1: like '%VAL%' 速度慢
SELECT * FROM A WHERE A.kye1 like '%VAL%';
# 解决1: 'VAL%'会使用索引， '%VAL%'不使用索引
SELECT * FROM A WHERE A.key1 like 'VAL%';
# 解决2: 非要使用 '%VAL%'的场景，通过索引全扫描代替全扫描
SELECT * FROM A WHERE EXISTS ( SELECT key1 FROM B WHERE B.key1 like '%VAL%')
1
2
3
4
5
6

🌰 4：group by

# Group By 字段要符合最左匹配原则
# 例如：索引创建（key1, key2, key3）
# 符合索引1 
SELECT key1, key2, key3 FROM A GROUP BY key1, key2, key3
# 符合索引2 （最左匹配之间存在缺失key2，但是key2='VAL'会填充缺失，使最左匹配生效）
SELECT key1, key2, key3 FROM A WHERE key2='VAL' GROUP BY key1, key3
# 符合索引3 （不满足最左匹配，但是条件key1='VAL'会使最左匹配生效）
SELECT key1, key2, key3 FROM A WHERE key1='VAL' GROUP BY key2, key3
1
2
3
4
5
6
7
8

🌰 5：时间范围检索

# 当范围区间过大，不会使用索引（下面示例，即便TIME加索引，也不会走索引）
SELECT key1, key2, key3 FROM A WHERE A.TIME > '1970-01-01 00:00:00:' AND A.TIME < NOW()
# 解决：减少时间范围区间，更精确搜索
1
2
3

🌰 6：LIMIT

# 利用表覆盖索引加速分野查询
# 解决1:使用id>=
SELECT ... FROM A WHERE A.ID >= (SELECT ID FROM A LIMIT 90000, 1) LIMIT 20
# 解决2:JOIN
SELECT ... FROM A a JOIN (SELECT ID FROM A LIMIT 90000, 1) b ON a.ID = b.ID
1
2
3
4
5

总结

• IN / NOT IN 使用 EXISTS / NOT EXISTS替代，连续数字状态判断可以通过BETWEEN AND 替代IN
• 关联查询，ON 对应的字段要添加索引
• LIKE '%VAL%'不走索引，使用 ‘VAL%’，或通过 … EXISTS （…LIKE ‘%VAL%’）方式，索引全扫描替代全扫描
• GROUP BY 要符合最左匹配原则
• 减少时间范围区间
• 通过id>=和JOIN优化limit查询