SQL优化实战：提升数据库性能的核心技巧-三亩地-编程学习日记

1. SQL优化：从入门到精通的实战指南

作为一名与数据库打了十年交道的开发者，我见过太多因为SQL性能问题导致的系统崩溃。有一次凌晨三点被叫起来处理一个超时查询，发现只是因为缺少了一个简单的索引。这种经历让我深刻意识到：SQL优化不是高级技能，而是每个开发者必须掌握的基本功。

SQL优化本质上是通过调整查询语句、数据库结构和执行策略，让数据库用最少的资源完成最多的工作。它直接影响着系统的响应速度、吞吐量和稳定性。无论是初创公司的小型应用，还是日均千万级访问的大型平台，SQL优化都是保证系统高效运行的关键。

2. SQL优化核心方法论

2.1 执行计划：优化师的X光机

拿到一个慢查询时，我第一件事就是看它的执行计划。在MySQL中，只需要在查询前加上EXPLAIN关键字：

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'completed';

执行计划中最需要关注的几个指标：

type列：从最优到最差依次是：
- system > const > eq_ref > ref > range > index > ALL
- 要尽量避免出现ALL（全表扫描）
key列：显示实际使用的索引
- 如果这一列为NULL，说明没有用到索引
rows列：预估需要检查的行数
- 这个数字越小越好
Extra列：额外信息
- 出现"Using filesort"或"Using temporary"时需要特别注意

实战经验：在MySQL 8.0+版本中，使用EXPLAIN ANALYZE可以看到实际的执行时间和行数，比传统EXPLAIN更准确。

2.2 索引设计的黄金法则

索引是SQL优化的利器，但用不好反而会成为负担。我的索引设计原则是：

最左前缀原则：
- 对于联合索引(a,b,c)，能生效的查询条件包括：
  - a=?
  - a=? AND b=?
  - a=? AND b=? AND c=?
- 但b=?或者c=?单独使用不会走这个索引
避免过度索引：
- 每个额外的索引都会降低写操作性能
- 一般建议单表索引不超过5个
选择区分度高的列：
- 优先为区分度高的字段建索引
- 区分度计算公式：COUNT(DISTINCT col)/COUNT(*)
覆盖索引技巧：
- 让查询所需字段都包含在索引中
- 这样就不需要回表查数据文件

-- 不好的写法：需要回表 SELECT * FROM users WHERE age > 20; -- 好的写法：使用覆盖索引 CREATE INDEX idx_age_name ON users(age, name); SELECT age, name FROM users WHERE age > 20;

2.3 查询语句优化实战技巧

2.3.1 避免全表扫描的10个方法

**永远不要使用SELECT ***
只查询需要的列，特别是大文本字段

LIMIT分页优化
传统分页在大偏移量时很慢：

SELECT * FROM articles LIMIT 10000, 20;

优化方案：

SELECT * FROM articles WHERE id > 10000 LIMIT 20;

避免使用OR条件
OR会导致索引失效，改用UNION ALL：

-- 不好的写法 SELECT * FROM users WHERE age = 20 OR age = 30; -- 好的写法 SELECT * FROM users WHERE age = 20 UNION ALL SELECT * FROM users WHERE age = 30;

慎用NOT IN和!=
这些操作通常无法使用索引
JOIN优化
- 小表驱动大表
- 确保JOIN字段有索引
- 避免多表JOIN（超过3个表考虑拆解）

2.3.2 函数和类型转换陷阱

不要在索引列上使用函数

-- 索引失效 SELECT * FROM users WHERE DATE(create_time) = '2023-01-01'; -- 优化写法 SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02';

避免隐式类型转换

-- user_id是varchar类型，这样写会导致索引失效 SELECT * FROM users WHERE user_id = 123; -- 正确写法 SELECT * FROM users WHERE user_id = '123';

3. 高级优化策略

3.1 数据库参数调优

根据我的经验，这几个MySQL参数对性能影响最大：

# InnoDB缓冲池大小，建议设置为物理内存的50%-70% innodb_buffer_pool_size = 4G # 日志文件大小，建议设置为缓冲池的25% innodb_log_file_size = 1G # 并发连接数 max_connections = 200 # 查询缓存(MySQL 8.0已移除) query_cache_type = 0

注意：参数调整后需要重启数据库生效，生产环境要谨慎操作。

3.2 分库分表实战方案

当单表数据超过500万行时，就要考虑分库分表了。常用方案：

水平分表
按某个字段的哈希或范围将数据分散到多个表
例如：user_0, user_1,...user_9
垂直分表
将不常用的大字段拆分到单独表
例如：users表和users_detail表
分库
将不同业务模块的数据放到不同数据库实例

实现工具推荐：

ShardingSphere
MyCat
应用层自己实现路由逻辑

3.3 慢查询监控与分析

我常用的慢查询分析流程：

开启慢查询日志

slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 log_queries_not_using_indexes = 1

使用pt-query-digest分析

pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt

重点关注：

执行次数多的查询
单次执行时间长的查询
全表扫描的查询

4. 常见问题排查手册

4.1 索引失效的7种情况

使用了不等于操作符(!=或<>)
使用了LIKE以通配符开头('%abc')
对索引列进行了运算或函数处理
发生了隐式类型转换
使用了OR条件而没有优化
复合索引不符合最左前缀原则
数据库优化器认为全表扫描更快

4.2 死锁分析与解决

典型死锁场景：

-- 事务1 UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 事务2 UPDATE accounts SET balance = balance - 200 WHERE id = 2; UPDATE accounts SET balance = balance + 200 WHERE id = 1;

解决方案：

保持事务小型化
所有事务按相同顺序访问表
使用SELECT...FOR UPDATE锁定必要行
设置合理的锁等待超时时间

4.3 连接池优化配置

以HikariCP为例推荐配置：

HikariConfig config = new HikariConfig(); config.setMaximumPoolSize(20); // 不超过数据库max_connections的80% config.setMinimumIdle(5); config.setConnectionTimeout(30000); config.setIdleTimeout(600000); config.setMaxLifetime(1800000); config.setLeakDetectionThreshold(60000);

5. 真实案例复盘

5.1 电商平台订单查询优化

问题：订单列表页加载需要5秒以上

优化过程：

发现查询使用了SELECT *并JOIN了6个表
移除了不需要的列，只查询必要字段
为常用查询条件创建复合索引
将用户基础信息冗余到订单表，减少JOIN
对大文本字段(content)使用单独表存储

结果：响应时间降至200ms以内

5.2 社交平台Feed流优化

问题：首页Feed加载缓慢，高峰期超时