坑点1：翻页越往后越卡
当你用LIMIT 1000000,10查数据时，数据库就像翻一本100万页的书——明明只要第100万页的10行字，却得从第一页开始数。优化大招：记住上次看到哪儿。比如上次翻页最后一条的创建时间是"2023-01-01 12:00:00"，下页查询直接加条件WHERE create_time > '2023-01-01 12:00:00'，秒出结果。
问题案例：
-- 传统分页（偏移量越大性能越差）
SELECT * FROM logs
WHERE log_type='ERROR'
ORDER BY create_time
LIMIT 1000000, 10; -- 需遍历前100万条记录优化方案：
-- 游标分页法（基于最后时间戳）
SELECT * FROM logs
WHERE log_type='ERROR'
AND create_time > '2024-02-17 00:00:00' -- 使用上一页末尾时间
ORDER BY create_time
LIMIT 10; -- 执行时间稳定在5ms内坑点2：乱填参数害死索引
字段明明是varchar类型，程序却传了数字进来。这就好比用家门钥匙开汽车锁，数据库只能暴力拆锁全表扫描。检查方法：执行SHOW WARNINGS看到类型转换警告，赶紧让程序员改代码对齐类型。
问题案例：
-- 字段phone定义为VARCHAR(20)，但传入数值参数
SELECT * FROM users
WHERE phone = 13800138000; -- 触发类型转换，索引失效
-- 查看执行计划警告
SHOW WARNINGS;
> Warning: Cannot use ref access on index 'phone' due to type conversion 优化方案：
-- 添加引号保持类型一致
SELECT * FROM users
WHERE phone = '13800138000'; -- 正确使用索引坑点3：更新语句慢如蜗牛
用UPDATE...WHERE id IN (子查询)更新数据？MySQL会傻乎乎地循环查几千次。救星方案：改成JOIN写法，让数据库一次性关联更新。实测有个案例从7秒降到2毫秒。
问题案例：
-- 错误写法（嵌套子查询）
UPDATE orders o
SET status = 'expired'
WHERE o.id IN (
SELECT id FROM (
SELECT id FROM orders
WHERE create_time < '2023-01-01'
LIMIT 1000
) tmp
); -- 执行时间>10秒优化方案：
-- 改用JOIN重写
UPDATE orders o
JOIN (
SELECT id FROM orders
WHERE create_time < '2023-01-01'
LIMIT 1000
) tmp ON o.id = tmp.id
SET status = 'expired'; -- 执行时间<100ms坑点4：混搭排序逼疯数据库
既要按评分升序，又要按时间降序，索引直接罢工。破解术：拆成两次查询再合并。比如先查未回复的订单按时间排序，再查已回复的，最后拼起来。
问题案例：
-- 混合排序导致全表扫描
SELECT * FROM product_reviews
ORDER BY is_approved ASC, review_time DESC
LIMIT 20; -- 无法使用(is_approved,review_time)索引优化方案：
-- 拆分查询+UNION ALL
(SELECT * FROM product_reviews
WHERE is_approved=0
ORDER BY review_time DESC LIMIT 20)
UNION ALL
(SELECT * FROM product_reviews
WHERE is_approved=1
ORDER BY review_time DESC LIMIT 20)
ORDER BY is_approved ASC, review_time DESC
LIMIT 20; -- 执行效率提升80倍坑点5：乱用SELECT * 拖垮网络
动不动就SELECT *，把不要的字段也查出来。特别是text大字段，传输速度直接腰斩。保命原则：需要什么字段就写什么，别让数据库做快递小哥送垃圾。
问题案例：
-- 低效写法
SELECT * FROM employees e
WHERE EXISTS (
SELECT 1 FROM salaries s
WHERE s.emp_id = e.id
AND s.year = 2023
); -- 嵌套循环执行优化方案：
-- 改用JOIN优化
SELECT e.*
FROM employees e
INNER JOIN salaries s
ON e.id = s.emp_id
AND s.year = 2023; -- 利用索引快速定位坑点6：模糊搜索堵死CPU
LIKE '%关键字%'这种查询，数据库只能玩命扫描。应急方案：上Elasticsearch做专业搜索，或者业务上限制必须带前缀查询。
问题案例：
-- 用户搜索包含"error"的日志（无前缀模糊匹配）
SELECT * FROM server_logs
WHERE log_message LIKE '%error%';
-- 执行计划：全表扫描，耗时12秒（数据量500万行）优化方案：
-- 方案1：强制前缀匹配（利用索引）
SELECT * FROM server_logs
WHERE log_message LIKE 'error%'; -- 使用前缀索引
-- 方案2：集成Elasticsearch（专业分词检索）
PUT /logs/_search
{
"query": { "match": { "message": "error" } }
}原理说明：
B+树索引无法逆向检索，LIKE '%xx'会导致索引失效。前缀匹配可触发索引范围扫描，而全文检索场景建议使用专用引擎（如ES）的分词能力。
坑点7：乱堆OR条件让索引哭晕
WHERE a=1 OR b=2这种写法，数据库往往直接放弃治疗全表扫描。改造技巧：拆成两个查询用UNION合并，速度提升50倍。
问题案例：
-- 查询订单状态为"paid"或物流单号为"SF123"的订单
SELECT * FROM orders
WHERE status = 'paid' OR tracking_no = 'SF123';
-- 执行计划：全表扫描，耗时8秒（数据量200万行）优化方案：
-- 拆分为UNION查询（分别利用不同索引）
(SELECT * FROM orders WHERE status = 'paid')
UNION
(SELECT * FROM orders WHERE tracking_no = 'SF123');
-- 执行时间：0.2秒（提升40倍）原理说明：
OR条件导致优化器无法选择有效索引。拆分为UNION后，可分别利用status和tracking_no的单列索引，再合并结果集。
坑点8：子查询嵌套套到死
在WHERE里疯狂嵌套子查询，MySQL会被逼成单线程工人。优化真经：多用临时表或JOIN代替，给数据库留条活路。
问题案例：
-- 查询有评论的商品（嵌套子查询）
SELECT * FROM products
WHERE id IN (
SELECT product_id FROM comments
WHERE create_time > '2024-01-01'
);
-- 执行计划：DEPENDENT SUBQUERY，耗时15秒 优化方案：
-- 方案1：改用JOIN关联（触发索引嵌套循环）
SELECT p.*
FROM products p
JOIN comments c ON p.id = c.product_id
WHERE c.create_time > '2024-01-01';
-- 方案2：临时表预存子查询结果
CREATE TEMPORARY TABLE tmp_products
AS SELECT product_id FROM comments
WHERE create_time > '2024-01-01';
SELECT p.* FROM products p
JOIN tmp_products t ON p.id = t.product_id;
-- 执行时间均降至0.5秒内 原理说明：
嵌套子查询会导致循环执行，而JOIN或临时表可将多次查询优化为单次数据关联。通过执行计划分析，JOIN写法通常能触发更优的Index Nested-Loop Join算法。
看完赶紧检查下自己写的SQL，别让同事半夜打电话骂人！遇到过更奇葩SQL坑货的，欢迎留言区吐槽
才哥最近开发了一个针对Java开发人员的面试刷题小程序，计划准备面试的伙伴们可以去试试，手机在线刷题更方便
评论 (0)