数据库SQL优化实战：从索引策略到查询优化

一、优化目标：明确方向，精准发力

1. 减少IO次数（优先级别最高）

• IO操作占数据库操作90%以上的时间
• 是性能瓶颈的主要来源
• 优化的效果最为显著

2. 降低CPU计算

• 在IO优化达到一定程度后，成为主要优化目标
• order by、group by、distinct等操作是CPU消耗大户
• 这些操作主要处理内存中的数据比较运算

二、优化方法：改变SQL执行计划

1. 使用EXPLAIN分析执行计划

EXPLAIN SELECT * FROM users WHERE age > 25;

关键字段解读：

• type：连接类型，从最优到最差：system > const > eq_ref > ref > range > index > ALL
• possible_keys：可能使用的索引
• key：实际使用的索引
• rows：扫描的行数
• Extra：额外信息，如Using where、Using index等

2. SHOW命令家族

-- 显示表的索引信息
SHOW INDEX FROM table_name;

-- 显示当前正在执行的查询
SHOW PROCESSLIST;

-- 显示表的状态信息
SHOW TABLE STATUS LIKE 'table_name';

-- 显示创建表的语句
SHOW CREATE TABLE table_name;

三、查询缓存优化

适用场景

• 表不经常变更
• 服务器收到大量相同查询
• 查询语句完全一致（逐字节相同）

缓存失效场景

1. 包含不确定函数：NOW()、CURRENT_DATE()、RAND()等
2. 使用用户自定义函数（UDFs）
3. 引用自定义变量
4. 特定语法场景：

   SELECT ... FOR UPDATE
   SELECT ... INTO OUTFILE
   SELECT ... LOCK IN SHARE MODE
   

5. 临时表操作
6. 用户有列级别权限

查询缓存注意事项

• 不要设置过大的缓存空间，避免全局锁竞争
• 频繁更新的表不适合使用查询缓存
• InnoDB事务特性会影响缓存命中率

四、表设计优化策略

1. 定长与变长类型选择

CHAR类型：

• 固定长度，M取值0-255
• 存储时自动用空格填充
• 检索时删除尾部空格
• 索引效率高，适合长度固定的数据

VARCHAR类型：

• 可变长度，最大65535字节
• 存储实际数据长度+1/2字节（记录长度）
• 保留尾部空格
• 存储效率高，适合长度不固定的数据

选择建议：

• 固定长度或长度变化很小的字段用CHAR
• 长度变化较大的字段用VARCHAR
• 权衡点：空间效率 vs 查询效率

2. 文本类型选择

类型	最大长度	特点
TINYTEXT	255字节	最小
TEXT	65,535字节	常用
MEDIUMTEXT	16,777,215字节	中等
LONGTEXT	4,294,967,295字节	最大

使用建议：

• 普通文本用VARCHAR，超过长度用TEXT
• 二进制数据用BLOB系列
• 避免在WHERE子句中对TEXT/BLOB建立索引
• 优先使用VARCHAR，必要时使用TEXT

3. 静态表优势

• 读写速度快
• 便于缓存和修复
• 但可能浪费存储空间
• 动态表节省空间但容易产生碎片

五、SQL编写基本原则

1. IN vs EXISTS

-- 适合外表大、内表小
SELECT * FROM A WHERE id IN (SELECT id FROM B);

-- 适合外表小、内表大  
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);

2. JOIN优化

• MySQL擅长简单查询，复杂多表JOIN性能有限
• 单表查询性能优秀
• 能用单表查询解决的，尽量不用JOIN

3. 排序优化

• 排序消耗大量CPU资源
• 优化方法：
  • 利用索引避免排序
  • 减少排序数据量
  • 非必要不排序

4. 避免SELECT *

• 增加IO负担
• 影响覆盖索引使用
• 网络传输数据量增加

5. JOIN代替子查询

-- 避免子查询
SELECT * FROM A WHERE id IN (SELECT id FROM B);

-- 使用JOIN代替
SELECT A.* FROM A JOIN B ON A.id = B.id;

6. OR优化

-- 性能较差
SELECT * FROM users WHERE age < 18 OR age > 60;

-- 使用UNION ALL优化
SELECT * FROM users WHERE age < 18
UNION ALL
SELECT * FROM users WHERE age > 60;

7. UNION vs UNION ALL

• UNION：去重+排序，消耗大
• UNION ALL：直接合并，效率高
• 确认无重复数据时使用UNION ALL

8. 尽早过滤

-- 优化前
SELECT * FROM A 
JOIN B ON A.id = B.a_id
WHERE A.create_time > '2024-01-01'
LIMIT 10;

-- 优化后：先过滤再JOIN
SELECT * FROM (
  SELECT * FROM A 
  WHERE create_time > '2024-01-01'
  LIMIT 10
) AS A_filtered
JOIN B ON A_filtered.id = B.a_id;

9. 避免隐式类型转换

-- 错误：索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- 正确：使用相同类型
SELECT * FROM users WHERE phone = '13800138000';

六、索引优化黄金法则

1. 索引使用要点

1. 前缀索引原则：联合索引遵循最左前缀匹配

   -- index(a, b, c) 可匹配：
   WHERE a = 1
   WHERE a = 1 AND b = 2
   WHERE a = 1 AND b = 2 AND c = 3
   
   -- 不可匹配：
   WHERE b = 2
   WHERE c = 3
   

2. 选择性原则：在区分度高的列建索引

   • 避免在性别、状态等低区分度列建索引
   • 选择性 = 不重复值数量 / 总记录数

3. 唯一性保证：在UNIQUE列上建立唯一索引

4. 避免冗余：定期清理无用索引

   -- 冗余示例
   INDEX(a)
   INDEX(a, b)  -- 冗余，可删除INDEX(a)
   

5. 长度控制：使用合适的前缀长度

   -- 对长字符串使用前缀索引
   CREATE INDEX idx_name ON users(name(10));
   

2. 索引类型选择

• BTREE索引：InnoDB默认，适合范围查询、排序
• HASH索引：Memory引擎，精确匹配快
• FULLTEXT索引：全文搜索
• SPATIAL索引：空间数据

3. 索引维护

-- 重建索引，优化存储
OPTIMIZE TABLE table_name;

-- 更新索引统计信息
ANALYZE TABLE table_name;

七、优化优先级策略

1. 高并发优先

• 高并发SQL问题影响面广
• 低频率"大SQL"影响相对有限
• 优先解决影响系统稳定性的问题

2. 全局视角

• 考虑整个系统的SQL性能
• 避免优化一个SQL影响其他SQL
• 建立性能基线，持续监控

3. 持续优化

• 对重要SQL定期进行EXPLAIN分析
• 建立SQL审核机制
• 监控慢查询日志

八、实战检查清单

索引使用检查

• 为频繁查询的WHERE条件字段建立索引
• 为JOIN条件字段建立索引
• 为ORDER BY、GROUP BY字段建立索引
• 避免在索引列上使用函数或计算
• 定期清理未使用的索引

SQL编写检查

• 避免SELECT *
• 合理使用JOIN代替子查询
• 使用UNION ALL代替UNION
• 避免隐式类型转换
• 尽早过滤数据
• 合理分页，避免深度翻页

架构设计检查

• 合理分库分表
• 读写分离
• 使用连接池
• 适当使用缓存

九、监控与维护

1. 慢查询监控

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';

-- 查看慢查询日志
SHOW VARIABLES LIKE 'long_query_time';

-- 临时开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

2. 性能分析工具

• EXPLAIN：执行计划分析
• SHOW PROFILE：详细执行过程
• PERFORMANCE_SCHEMA：性能模式
• INFORMATION_SCHEMA：元数据查询

3. 定期维护任务

1. 分析表统计信息
2. 优化表碎片
3. 清理历史数据
4. 审查和优化索引
5. 更新数据库参数配置

总结

数据库优化是一个系统工程，需要从多个维度综合考虑：

1. SQL层面：编写高效的SQL语句
2. 索引层面：建立合适的索引策略
3. 架构层面：设计合理的数据库架构
4. 运维层面：建立完善的监控维护体系

记住优化原则：先测量，后优化；先全局，后局部；先高并发，后低频率。通过持续优化，才能保证数据库系统的高性能和高可用性。