SQL 性能优化：从 10 秒到 0.1 秒的 8 个技巧

引言：性能优化，从 10 秒到 0.1 秒

想象一下，你的查询从 10 秒变成 0.1 秒，用户体验提升 100 倍！

SQL 性能优化不是玄学，而是有章可循的技术。今天这篇教程将带你掌握 8 个实战优化技巧，让你也能写出毫秒级的 SQL 查询。

第一章：性能优化基础

1.1 性能瓶颈分析

-- 性能问题的常见原因

缺少索引
索引使用不当
查询语句复杂
表结构设计不合理
缺少连接条件
子查询效率低
数据类型不匹配
服务器配置不当

1.2 分析工具

-- MySQL: 使用 EXPLAIN 分析查询计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 关键指标：
-- - type: 连接类型（ALL > index > range > ref > eq_ref > const）
-- - rows: 扫描行数
-- - Extra: 额外信息（Using index, Using filesort 等）

第二章：8 个优化技巧实战

技巧 1：添加合适的索引

优化前（10 秒）：

-- 缺少索引
SELECT * FROM orders 
WHERE customer_id = 123 
AND status = 'completed'
AND created_at > '2024-01-01';

-- EXPLAIN 结果
type: ALL       -- 全表扫描
rows: 1000000   -- 扫描 100 万行

优化后（0.05 秒）：

-- 添加复合索引
CREATE INDEX idx_orders_customer_status_created 
ON orders(customer_id, status, created_at);

-- 或者分别创建
CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);

-- EXPLAIN 结果
type: range     -- 范围扫描
rows: 1000      -- 扫描 1000 行

索引最佳实践：

-- 1. 最左前缀原则
CREATE INDEX idx_name_age ON users(name, age);
-- 索引可用于：name、name+age，但单独 age 无法使用索引

-- 2. 区分度高的列优先
-- 错误：id_card（每个人不同）放在 name（重复率高）前面
CREATE INDEX idx_card_name ON users(id_card, name);
-- 正确：name 在前
CREATE INDEX idx_name_card ON users(name, id_card);

-- 3. 覆盖索引优化
-- 避免 SELECT *，只查询需要的列
SELECT id, name, email FROM users WHERE status = 1;
-- 如果这些列都在索引中，无需回表

-- 4. 避免索引失效
-- ✅ 正确：索引列不参与计算
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- ❌ 错误：索引列参与计算
SELECT * FROM users WHERE created_at >= '2024-01-01' 
                         AND created_at < '2025-01-01';

-- ✅ 正确写法
SELECT * FROM users 
WHERE created_at >= '2024-01-01' 
AND created_at < '2025-01-01';

技巧 2：优化 WHERE 条件

优化前（8 秒）：

-- 错误：使用函数包裹索引列
SELECT * FROM orders 
WHERE DATE(created_at) = '2024-01-01';

-- 优化后（0.1 秒）：
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' 
AND created_at < '2024-01-02';

优化前（6 秒）：

-- 错误：OR 条件导致索引失效
SELECT * FROM users 
WHERE name = 'John' 
OR email = 'john@example.com';

-- 优化后（0.2 秒）：使用 UNION
SELECT * FROM users WHERE name = 'John'
UNION
SELECT * FROM users WHERE email = 'john@example.com';

优化前（5 秒）：

-- 错误：!= 和 NOT 导致全表扫描
SELECT * FROM users 
WHERE status != 'inactive';

-- 优化后（0.3 秒）：
SELECT * FROM users 
WHERE status = 'active'
OR status = 'pending';

技巧 3：使用 JOIN 优化

优化前（12 秒）：

-- 错误的子查询方式
SELECT * FROM orders 
WHERE customer_id IN (
    SELECT id FROM customers 
    WHERE country = 'USA'
);

-- EXPLAIN 显示：依赖子查询，无法使用索引

优化后（0.1 秒）：

-- 使用 JOIN 优化
SELECT o.* 
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'USA';

-- EXPLAIN 显示：使用索引连接

JOIN 优化最佳实践：

-- 1. 小表驱动大表
-- 优化器会自动选择，但可以手动控制
SELECT * FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'USA';  -- 小表先行

-- 2. 使用 ON 过滤，而非 WHERE
-- 错误：在 WHERE 中过滤导致笛卡尔积
SELECT * FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'active';

-- 正确：在 ON 中过滤
SELECT * FROM orders o
INNER JOIN customers c ON o.customer_id = c.id 
                     AND c.status = 'active';

-- 3. 避免多表 JOIN 超过 5 个
-- 超过 5 个表的 JOIN 性能急剧下降
SELECT a.*, b.*, c.*, d.*, e.*, f.*
FROM table_a a
JOIN table_b b ON ...
JOIN table_c c ON ...
JOIN table_d d ON ...
JOIN table_e e ON ...
JOIN table_f f ON ...;

-- 优化：分步查询或使用临时表

技巧 4：优化 COUNT 查询

优化前（15 秒）：

-- 全表计数
SELECT COUNT(*) FROM orders;

-- 扫描所有行，性能差

优化后（0.01 秒）：

-- 使用索引计数
SELECT COUNT(*) FROM orders 
WHERE status = 'active';  -- 如果 status 有索引

-- 或使用覆盖索引
SELECT COUNT(id) FROM orders;  -- id 是主键，自动使用索引

-- 或使用近似值（大数据场景）
SELECT COUNT(*) FROM orders USE INDEX (idx_status);

优化前（10 秒）：

-- 分页获取总数
SELECT COUNT(*) FROM orders;

-- 优化后（0.2 秒）：使用索引
SELECT COUNT(*) FROM orders 
WHERE created_at >= '2024-01-01'
AND created_at < '2024-02-01';

技巧 5：优化 LIMIT 和 OFFSET

优化前（20 秒）：

-- 深分页性能极差
SELECT * FROM orders 
LIMIT 1000000, 10;

-- 需要扫描 100 万行，然后丢弃前 999,990 行

优化后（0.3 秒）：

-- 使用延迟关联优化
SELECT o.* 
FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    LIMIT 1000000, 10
) tmp ON o.id = tmp.id;

-- 只查询主键，减少回表开销

优化前（15 秒）：

-- 使用 WHERE 条件
SELECT * FROM orders 
WHERE id > 1000000
ORDER BY id
LIMIT 10;

-- 使用游标分页（推荐）
-- 上一页最后一条 ID
SELECT * FROM orders 
WHERE id > 999990
ORDER BY id
LIMIT 10;

技巧 6：使用 UNION 优化

优化前（18 秒）：

-- 多个 OR 条件
SELECT * FROM users 
WHERE country = 'USA'
   OR country = 'UK'
   OR country = 'Canada'
   OR country = 'Australia';

-- 可能导致索引失效

优化后（0.5 秒）：

-- 使用 UNION
SELECT * FROM users WHERE country = 'USA'
UNION ALL
SELECT * FROM users WHERE country = 'UK'
UNION ALL
SELECT * FROM users WHERE country = 'Canada'
UNION ALL
SELECT * FROM users WHERE country = 'Australia';

-- 或使用 IN
SELECT * FROM users 
WHERE country IN ('USA', 'UK', 'Canada', 'Australia');

技巧 7：优化 UPDATE 和 DELETE

优化前（25 秒）：

-- 批量删除，无 WHERE 条件
DELETE FROM logs WHERE 1=1;

-- 全表删除，锁表时间长

优化后（2 秒）：

-- 分批删除
DELETE FROM logs 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
LIMIT 1000;

-- 循环执行，避免长事务

优化前（30 秒）：

-- 批量更新，无 WHERE 条件
UPDATE users SET status = 'inactive';

-- 优化后（3 秒）：
UPDATE users 
SET status = 'inactive'
WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

技巧 8：表结构优化

优化前（20 秒）：

-- 大字段在前
CREATE TABLE orders (
    description TEXT,  -- 大字段
    status VARCHAR(20),
    created_at DATETIME,
    customer_id INT
);

-- 导致索引效率低

优化后（0.5 秒）：

-- 小字段在前
CREATE TABLE orders (
    status VARCHAR(20),
    created_at DATETIME,
    customer_id INT,
    description TEXT   -- 大字段放最后
);

-- 或使用单独表存储大字段
CREATE TABLE orders (
    id INT PRIMARY KEY,
    status VARCHAR(20),
    created_at DATETIME,
    customer_id INT
);

CREATE TABLE order_details (
    order_id INT,
    description TEXT
);

表优化建议：

-- 1. 选择合适的数据类型
-- ✅ 使用 INT 而非 VARCHAR 存储数字
customer_id INT  -- 4 字节
-- ❌ 避免使用 VARCHAR 存储数字
customer_id VARCHAR(10)  -- 浪费空间，比较慢

-- 2. 使用 ENUM 存储固定值
-- ✅ 适合
status ENUM('pending', 'active', 'completed')
-- ❌ 不适合
status VARCHAR(50)

-- 3. 避免 NULL 值
-- ✅ 使用默认值
status VARCHAR(20) NOT NULL DEFAULT 'pending'
-- ❌ 避免
status VARCHAR(20) NULL

-- 4. 垂直分表
-- 分离大字段
CREATE TABLE orders (
    id INT PRIMARY KEY,
    amount DECIMAL(10,2),
    created_at DATETIME
);

CREATE TABLE order_details (
    order_id INT,
    description TEXT,
    notes TEXT
);

第三章：实战案例

案例 1：电商订单查询优化

优化前（12 秒）：

-- 复杂子查询
SELECT * FROM orders 
WHERE customer_id IN (
    SELECT id FROM customers 
    WHERE country = 'USA' 
    AND registration_date < '2023-01-01'
)
AND status IN ('pending', 'processing')
AND total_amount > 1000;

-- EXPLAIN:
type: dependent subquery
rows: 500000

优化后（0.15 秒）：

-- 使用 JOIN
SELECT o.* 
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'USA' 
AND c.registration_date < '2023-01-01'
AND o.status IN ('pending', 'processing')
AND o.total_amount > 1000;

-- 添加索引
CREATE INDEX idx_orders_customer ON orders(customer_id, status, total_amount);
CREATE INDEX idx_customers_country ON customers(country, registration_date);

-- EXPLAIN:
type: ref
rows: 5000

案例 2：数据导出优化

优化前（60 秒）：

-- 导出大量数据
SELECT * FROM users 
WHERE created_at >= '2020-01-01'
AND created_at < '2024-01-01';

-- 结果：100 万行，耗时 60 秒

优化后（5 秒）：

-- 分批导出
SELECT * FROM users 
WHERE created_at >= '2020-01-01'
AND created_at < '2020-07-01'
LIMIT 100000;

SELECT * FROM users 
WHERE created_at >= '2020-07-01'
AND created_at < '2021-01-01'
LIMIT 100000;

-- 添加覆盖索引
CREATE INDEX idx_created_users ON users(created_at, id);

案例 3：报表查询优化

优化前（45 秒）：

-- 复杂统计
SELECT 
    DATE(created_at),
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales
FROM orders 
WHERE status = 'completed'
GROUP BY DATE(created_at)
HAVING COUNT(*) > 100
ORDER BY order_count DESC;

-- 全表扫描，耗时 45 秒

优化后（1.2 秒）：

-- 使用物化表或分区表
-- 创建汇总表
CREATE TABLE order_summary AS
SELECT 
    DATE(created_at) as order_date,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales
FROM orders 
WHERE status = 'completed'
GROUP BY DATE(created_at);

-- 创建索引
CREATE INDEX idx_order_date ON order_summary(order_date);

-- 查询汇总表
SELECT * FROM order_summary 
WHERE order_count > 100
ORDER BY order_count DESC;

第四章：性能监控

4.1 慢查询日志

-- MySQL 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 分析慢查询
mysqldumpslow /var/log/mysql/slow.log

-- 或使用 pt-query-digest
pt-query-digest /var/log/mysql/slow.log

4.2 性能视图

-- 查看表统计信息
SHOW TABLE STATUS LIKE 'orders';

-- 查看索引使用情况
SHOW INDEX FROM orders;

-- 查看查询缓存
SHOW STATUS LIKE 'Qcache%';

-- 查看锁信息
SHOW ENGINE INNODB STATUS;

第五章：最佳实践总结

5.1 索引优化清单

-- ✅ 为常用查询添加索引
CREATE INDEX idx_column ON table(column);

-- ✅ 使用复合索引
CREATE INDEX idx_a_b ON table(a, b, c);

-- ✅ 使用覆盖索引
SELECT id, name FROM users WHERE status = 1;

-- ❌ 避免过度索引
-- 每个索引都会降低写入性能

-- ❌ 避免在索引列上使用函数
SELECT * FROM table WHERE YEAR(created_at) = 2024;

5.2 查询优化清单

-- ✅ 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM users;

-- ✅ 避免 SELECT *
SELECT id, name, email FROM users;

-- ✅ 使用 LIMIT 限制返回行数
SELECT * FROM users LIMIT 100;

-- ✅ 避免深分页
-- 使用 id 范围查询代替 OFFSET

5.3 表设计优化清单

-- ✅ 选择合适的数据类型
customer_id INT  -- 而非 VARCHAR(50)

-- ✅ 避免 NULL 值
status VARCHAR(20) NOT NULL DEFAULT 'pending'

-- ✅ 使用 ENUM 存储固定值
status ENUM('active', 'inactive')

-- ✅ 垂直分表分离大字段

总结：性能优化是系统工程

SQL 性能优化不是单一技巧，而是综合应用：

1. 合理的索引设计：70% 的性能问题源于缺少索引
2. 优化的查询语句：减少扫描行数，避免全表扫描
3. 良好的表结构设计：选择合适的数据类型和索引
4. 持续的性能监控：使用慢查询日志分析性能瓶颈

记住：

• 先分析问题，再优化
• 使用 EXPLAIN 分析查询计划
• 先优化索引，再优化查询
• 持续监控，持续优化

掌握这些技巧，你的 SQL 查询性能提升将不是梦！🚀

---

参考资源：

• [MySQL EXPLAIN 详解](https://dev.mysql.com/doc/refman/8.0/en/explain.html)
• [索引优化指南](https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html)
• [慢查询日志分析](https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html)
• [Performance Schema](https://dev.mysql.com/doc/refman/8.0/en/performance-schema.html)
打赏赞微海报分享