面试:谈谈MySQL数据库SQL改写技巧？

以下是 MySQL 中常见的高风险 SQL 写法及其优化方案，这些写法会导致全表扫描、索引失效、资源过载等性能问题：

---

一、索引失效类问题

1. 对索引列使用函数或计算

-- 原写法（索引失效）：
SELECT * FROM orders WHERE DATE_FORMAT(create_time,'%Y-%m') = '2023-01';

-- 优化方案：
SELECT * FROM orders 
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59';

原理：对索引列使用函数会使优化器放弃索引。

2. 隐式类型转换

-- user_id 是 VARCHAR 类型
SELECT * FROM users WHERE user_id = 10086; -- 触发隐式转换

-- 优化方案：
SELECT * FROM users WHERE user_id = '10086'; -- 保持类型一致

原理：数字与字符串比较时，MySQL 会将字符串转为数字，导致索引失效。

3. 前导通配符查询

-- 原写法（全表扫描）：
SELECT * FROM products WHERE name LIKE '%Pro Max%';

-- 优化方案：
SELECT * FROM products WHERE name LIKE 'Apple%'; -- 仅用后通配符

附加方案：

-- 全文索引方案
ALTER TABLE products ADD FULLTEXT(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('+Pro +Max' IN BOOLEAN MODE);

---

二、连接与子查询问题

4. 笛卡尔积连接

-- 原写法（产生 M×N 条数据）：
SELECT * FROM users, orders;

-- 优化方案：
SELECT * FROM users JOIN orders ON users.id = orders.user_id;

5. IN 子查询未优化

-- 原写法（逐行执行子查询）：
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE vip_level > 5);

-- 优化方案（改用 JOIN）：
SELECT orders.* 
FROM orders JOIN users ON orders.user_id = users.id
WHERE users.vip_level > 5;

6. 相关子查询

-- 原写法（外层每行执行一次子查询）：
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) 
FROM users;

-- 优化方案（LEFT JOIN + 聚合）：
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

---

三、排序与分组问题

7. 非索引列排序

-- 原写法（文件排序）：
SELECT * FROM products ORDER BY price DESC;

-- 优化方案：
ALTER TABLE products ADD INDEX idx_price(price); -- 创建索引
SELECT * FROM products ORDER BY price DESC;     -- 使用索引排序

8. 大结果集分页

-- 原写法（扫描前 100000 行）：
SELECT * FROM logs ORDER BY id LIMIT 100000, 20;

-- 优化方案（索引分页）：
SELECT * FROM logs 
WHERE id > (SELECT id FROM logs ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 20;

---

四、数据操作问题

9. 未分批的大数据删除

-- 原写法（长事务锁表）：
DELETE FROM user_logs WHERE create_time < '2020-01-01';

-- 优化方案（分批删除）：
DELETE FROM user_logs 
WHERE create_time < '2020-01-01' 
LIMIT 1000; -- 循环执行直到影响0行

10. SELECT * 滥用

-- 原写法（传输冗余数据）：
SELECT * FROM products WHERE category_id = 5;

-- 优化方案：
SELECT id, name, price -- 仅查必要字段
FROM products 
WHERE category_id = 5;

---

五、聚合函数陷阱

11. DISTINCT 滥用

-- 原写法（高开销去重）：
SELECT DISTINCT product_id FROM orders;

-- 优化方案（GROUP BY 更高效）：
SELECT product_id FROM orders GROUP BY product_id;

12. 聚合与非聚合列混用

-- 原写法（产生随机值）：
SELECT user_id, name, SUM(amount) 
FROM orders GROUP BY user_id; -- name 值不确定

-- 优化方案：
SELECT user_id, MAX(name) AS name, SUM(amount) -- 明确聚合方式
FROM orders GROUP BY user_id;

---

六、高级优化技巧

13. 覆盖索引优化

-- 原写法（回表查询）：
SELECT user_id, status FROM orders WHERE status = 'PAID';

-- 优化方案：
ALTER TABLE orders ADD INDEX idx_status_userid(status, user_id); -- 覆盖索引

14. 强制索引提示

-- 优化器错误选择索引时：
SELECT * FROM orders FORCE INDEX(idx_create_time) 
WHERE create_time > '2023-01-01';

15. 拆分复杂查询

-- 原写法（单查询过重）：
SELECT * FROM logs 
WHERE type = 'ERROR' AND create_time > NOW() - INTERVAL 1 DAY
ORDER BY id DESC 
LIMIT 100;

-- 优化方案（两步查询）：
-- 第一步：快速定位ID
SELECT id FROM logs 
WHERE type = 'ERROR' AND create_time > NOW() - INTERVAL 1 DAY
ORDER BY id DESC 
LIMIT 100;

-- 第二步：取完整数据
SELECT * FROM logs WHERE id IN (/*上一步的ID列表*/);

---

七、执行计划验证方法

对每个优化后的 SQL 使用 EXPLAIN 验证：

EXPLAIN 
SELECT * FROM products 
WHERE category_id = 5 
ORDER BY price DESC;

-- 关键指标检查：
-- 1. type: ref/range (避免 ALL)
-- 2. key: 使用了正确索引
-- 3. Extra: Using index (覆盖索引)
-- 4. rows: 扫描行数小于1000

---

性能问题SQL速查表

问题类型	危险写法特征	优化策略
索引失效	WHERE DATE(col)/LIKE '%xx'	改写条件/使用函数索引
隐式类型转换	数字与字符串比较	统一数据类型
连接效率低下	无 JOIN 条件的多表查询	显式指定 JOIN 条件
子查询性能差	WHERE col IN (SELECT ...)	改用 JOIN 或 EXISTS
文件排序	ORDER BY non_index_col	加索引/减少结果集
深分页	LIMIT 100000,20	基于索引分页
全字段返回	SELECT *	仅选择必要字段
未批处理大数据操作	一次性 DELETE/UPDATE 百万行	分批操作

通过避免这些反模式，结合 EXPLAIN 分析执行计划，可显著提升 MySQL 查询性能。关键原则：减少数据扫描量，利用索引，避免资源密集型操作。

欢迎关注我的公众号《IT小Chen》