一、SQL优化基础原则

1.1 避免全表扫描陷阱

全表扫描是性能杀手，尤其在数据量超过百万级时。优化核心原则是让查询尽可能使用索引：

禁止使用SELECT *：明确指定所需字段，减少I/O压力。例如将SELECT * FROM users改为SELECT id,name,email FROM users
慎用OR条件：WHERE age=20 OR gender='M'可能导致索引失效，可改写为WHERE age=20 UNION SELECT * FROM users WHERE gender='M'
合理使用函数：避免在索引列上使用函数，如WHERE DATE(create_time)='2023-01-01'应改为WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59'

1.2 数据类型选择黄金法则

字段类型直接影响存储效率和计算性能：

整数类型：根据数值范围选择TINYINT(-128~127)、SMALLINT(-32768~32767)等
字符串类型：定长用CHAR，变长用VARCHAR（最大长度建议不超过2000字节）
时间类型：优先使用DATETIME（精确到秒）或TIMESTAMP（带时区转换）
枚举类型：对于固定值集合（如状态字段），ENUM比VARCHAR节省存储空间

二、索引优化实战

2.1 索引设计五要素

选择性原则：高区分度字段优先建索引（如用户ID比性别更适合建索引）
复合索引顺序：遵循最左前缀原则，将高选择性字段放在左侧
覆盖索引：确保查询所需字段全部包含在索引中，避免回表操作
索引长度优化：对字符串字段可截取前缀建索引，如ALTER TABLE products ADD INDEX idx_name(product_name(20))
避免过度索引：每个额外索引都会增加写入开销，建议单表索引不超过5个

2.2 索引失效典型场景

-- 场景1：隐式类型转换
EXPLAIN SELECT * FROM users WHERE phone='13800138000'; -- phone是varchar类型但用数字查询
-- 场景2：使用NOT、!=、<>操作符
EXPLAIN SELECT * FROM orders WHERE status != 'completed';
-- 场景3：LIKE以通配符开头
EXPLAIN SELECT * FROM products WHERE name LIKE '%apple%';

2.3 索引监控与维护

慢查询日志：通过long_query_time参数设置阈值（建议1秒）
索引使用统计：使用SHOW INDEX FROM table_name查看索引基数和碎片情况
定期重建索引：对频繁更新的表，每月执行ALTER TABLE table_name ENGINE=InnoDB重建索引

三、查询语句深度优化

3.1 JOIN操作优化策略

小表驱动大表：将数据量小的表放在JOIN左侧
STRAIGHT_JOIN强制顺序：当优化器选择错误执行计划时，可使用SELECT /*+ STRAIGHT_JOIN */ * FROM a JOIN b ON a.id=b.a_id
子查询改写：将SELECT * FROM a WHERE id IN (SELECT a_id FROM b)改写为SELECT a.* FROM a JOIN b ON a.id=b.a_id

3.2 分页查询优化方案

传统LIMIT分页在深度分页时性能极差：

-- 低效方式（数据量越大越慢）
SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;
-- 高效方式（使用索引覆盖）
SELECT * FROM orders WHERE id > (SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 1) ORDER BY create_time DESC LIMIT 20;

3.3 批量操作优化技巧

批量插入：使用INSERT INTO table VALUES (...),(...),(...)替代单条插入
事务控制：将多个操作合并到一个事务中，减少日志写入次数
临时表优化：对复杂查询可先创建临时表存储中间结果

四、数据库架构级优化

4.1 分库分表实施路径

垂直拆分：按业务维度拆分（如用户表拆分为用户基础表、用户扩展表）
水平拆分：按哈希或范围拆分（如订单表按用户ID哈希拆分到16个库）
分片中间件：可使用行业常见技术方案实现透明分片，支持跨库JOIN和分布式事务

4.2 读写分离实践

主从架构：配置一主多从，写操作走主库，读操作走从库
负载均衡：使用代理层（如MySQL Router）实现读写请求的自动路由
数据一致性：对于强一致性要求的场景，可采用半同步复制或GTID复制

4.3 缓存策略设计

应用层缓存：使用内存数据库缓存热点数据，设置合理的过期时间
数据库缓存：调整query_cache_size参数（MySQL 8.0已移除该功能）
多级缓存：构建本地缓存+分布式缓存+数据库的三级缓存体系

五、性能监控与调优

5.1 关键指标监控

QPS/TPS：每秒查询/事务数，反映系统整体负载
连接数：监控Threads_connected和max_connections参数
缓存命中率：计算Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests

5.2 执行计划分析

-- 获取执行计划
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id=100 AND status='paid' ORDER BY create_time DESC LIMIT 10;
-- 关键字段解读
/*
{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "orders",
      "access_type": "ref",  -- 访问类型（const/eq_ref/ref/range/index/ALL）
      "key": "idx_user_status",  -- 使用的索引
      "rows": 5,  -- 预估扫描行数
      "filtered": 80.00  -- 条件过滤比例
    }
  }
}
*/

5.3 参数调优建议

参数	推荐值	作用说明
innodb_buffer_pool_size	物理内存的60-70%	InnoDB缓存池大小
innodb_log_file_size	256M-2G	重做日志文件大小
tmp_table_size	64M-256M	内存临时表大小阈值
join_buffer_size	256K-4M	JOIN操作使用的缓冲区大小

六、典型优化案例解析

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

问题现象：订单列表查询响应时间超过3秒，CPU使用率90%+
优化措施：

为user_id+status+create_time创建复合索引
将ORDER BY create_time DESC改为ORDER BY id DESC（利用主键索引）
限制返回字段数量，移除不必要的JSON字段解析
优化效果：查询时间降至80ms，CPU使用率降至30%

案例2：报表系统慢查询治理

问题现象：某日报统计查询执行超时（默认30秒）
优化措施：

将5个子查询改写为JOIN操作
对日期字段建立函数索引（需数据库支持）
添加/*+ SET_VAR(optimizer_switch='condition_fanout_filter=on') */提示
优化效果：查询时间从超时降至12秒

七、持续优化方法论

建立基线：记录优化前的各项性能指标
分阶段实施：从最影响业务的查询开始优化
A/B测试：对比优化前后的执行计划和实际性能
自动化监控：部署监控告警系统，及时发现性能退化
定期复盘：每月分析慢查询日志，更新优化策略

通过系统化的SQL优化方法，可使数据库查询效率提升5-10倍，同时降低30%-50%的服务器资源消耗。建议开发者建立完整的优化知识体系，结合具体业务场景灵活应用各类优化技术。

SQL优化全攻略：从基础到进阶的实践指南