我要评论一、为什么要做 sql 优化
sql 性能问题是后端系统最常见的瓶颈之一。一条慢 sql 可能拖垮整个数据库，进而导致服务雪崩。优化的核心目标：
- 减少 io 次数：磁盘 io 是数据库最大的性能杀手
- 降低 cpu 消耗：避免全表扫描、大量排序、临时表
- 提升并发能力：锁等待减少，系统吞吐量提升
- 节省硬件成本：用更少的机器支撑更大的流量
优化原则：先优化业务逻辑，再优化 sql，最后才考虑加机器。
二、索引优化：sql 优化的重中之重
2.1 索引的底层原理
mysql 常用存储引擎 innodb 使用 b+tree 结构：
- b+tree 特点：非叶子节点只存索引键，叶子节点存完整数据（主键索引）或主键值（二级索引）
- 页大小：默认 16kb，三层 b+tree 可存千万级数据
- 聚簇索引：主键索引即数据本身，一张表只有一个
- 回表：通过二级索引找到主键，再回主键索引查完整行数据
2.2 联合索引与最左前缀原则
联合索引 (a, b, c) 实际排序规则：先按 a 排，a 相同按 b 排，b 相同按 c 排。
生效场景：
-- ✅ 走索引：a 单独、a+b、a+b+c where a = 1 where a = 1 and b = 2 where a = 1 and b = 2 and c = 3 -- ✅ 部分走索引：只有 a 生效 where a = 1 and c = 3 -- ❌ 不走索引：跳过了最左列 where b = 2 and c = 3
范围查询截断： 范围查询（> < between like）之后的列无法使用索引。
-- (a, b, c) 索引中，只有 a 和 b 生效，c 用不上 where a = 1 and b > 2 and c = 3
2.3 覆盖索引
查询的所有字段都包含在索引中，无需回表，性能极高。
-- 建立联合索引 (name, age) select name, age from user where name = '张三'; -- ✅ 覆盖索引，extra: using index
2.4 索引失效的常见场景
| 场景 | 示例 | 原因 |
|---|---|---|
| 函数操作 | where year(create_time) = 2024 | 索引列上用函数破坏有序性 |
| 隐式类型转换 | where phone = 13800138000（phone 是 varchar） | 字符串和数字比较会触发 cast |
| 模糊查询前缀通配 | where name like '%张' | 前缀不确定，无法利用 b+tree 有序性 |
| or 连接非索引列 | where a = 1 or b = 2（b 无索引） | 为了 b 必须全表扫 |
| != / <> / not in | where status != 1 | 优化器认为扫全表更快 |
| is not null | where col is not null | 多数情况下不走索引 |
2.5 索引设计最佳实践
- 优先建联合索引，少建单列索引：一个联合索引往往能顶多个单列索引
- 区分度高的列放前面：性别、状态这种低基数字段不适合单独建索引
- 字符串建前缀索引：
index idx_email(email(20))，节省索引空间 - 避免冗余索引：有
(a,b)就不需要(a) - 控制索引数量：单表索引建议不超过 5 个，写多读少的表更少
- 主键用自增 id：避免 uuid 导致的页分 裂和碎片
三、查询语句优化实战
3.1 避免 select *
-- ❌ 不好 select * from order where user_id = 123; -- ✅ 好 select id, order_no, amount from order where user_id = 123;
危害：
- 无法使用覆盖索引，必然回表
- 传输多余字段，浪费网络带宽
- 大字段（text/blob）会严重拖慢查询
3.2 分页优化
深分页问题：limit 1000000, 10 要先扫 100 万行再丢弃。
优化方案一：游标分页（推荐）
-- 用上一页最后一条的 id 作为游标
select * from order
where id < #{last_id}
order by id desc
limit 10;
优化方案二：延迟关联
select o.* from order o
inner join (
select id from order
where status = 1
order by id
limit 100000, 10
) t on o.id = t.id;
3.3 join 优化
join 执行原理（nested loop join）：
- 驱动表（小表）逐行取出，去被驱动表（大表）查匹配
- 被驱动表的关联字段必须建索引
优化规则：
- 小表驱动大表：mysql 优化器会自动选，但复杂查询可能选错
- 关联字段必须建索引：被驱动表的关联列一定要有索引
- 尽量减少 join 次数：超过 3 张表的 join 要谨慎评估
- 避免 join + order by 排序字段跨表：容易产生 using filesort
3.4 子查询优化
mysql 5.5 及以前子查询性能很差，5.6+ 优化了，但仍建议改成 join：
-- ❌ 子查询（可能执行多次） select * from order where user_id in (select id from user where status = 1); -- ✅ join 写法 select o.* from order o inner join user u on o.user_id = u.id where u.status = 1;
3.5 group by / order by 优化
核心思路：利用索引的有序性，避免额外排序。
-- 索引 (status, create_time) 可以同时满足 where 和 order by select * from order where status = 1 order by create_time desc; -- ✅ extra: using index condition（不需要 filesort）
using filesort 不一定慢：数据量小时内存排序很快，数据量大才需要优化。
group by 优化：
- 默认会排序，不需要排序时加
order by null - 尽量让分组字段走索引
3.6 union vs union all
-- union 会去重 + 排序，性能差 select a from t1 union select a from t2; -- union all 直接合并，性能好（确认无重复时用） select a from t1 union all select a from t2;
四、表结构与数据类型优化
4.1 数据类型选择原则
越小越好：能用 tinyint 不用 int，能用 int 不用 bigint
| 类型 | 字节 | 范围 | 适用场景 |
|---|---|---|---|
| tinyint | 1 | -128~127 | 状态、类型枚举 |
| int | 4 | -21亿~21亿 | 主键、数量 |
| bigint | 8 | 超大 | 分布式id |
| varchar(20) | 变长 | 短字符串 | 手机号、编码 |
| char(10) | 定长 | 固定长度 | md5、邮编 |
| decimal(10,2) | 精确 | 金额 | 财务数据 |
4.2 常见设计误区
- 用 varchar 存数字：排序会按字符串排，占空间更大
- 时间用字符串存：无法用日期函数，索引效率低，用 datetime 或 timestamp
- null 字段太多：null 值会占用额外空间，索引统计更复杂，建议设默认值
- text/blob 滥用：大字段单独拆表，避免影响主表查询性能
- 过度冗余字段：空间换时间要适度，冗余字段一致性维护成本高
4.3 范式与反范式
- 第三范式（3nf）：属性不依赖其他非主属性，数据不冗余
- 反范式：适当冗余字段，减少 join，提升查询性能
- 平衡策略：读多写少的场景适度反范式，写多读少保持范式
五、执行计划（explain）深度解读
5.1 explain 输出字段详解
explain select * from order where user_id = 123;
| 字段 | 含义 | 重点关注 |
|---|---|---|
| id | 查询编号 | id 越大越先执行 |
| select_type | 查询类型 | simple / primary / subquery / derived |
| type | 访问类型 | system > const > eq_ref > ref > range > index > all |
| possible_keys | 可能用到的索引 | 候选索引列表 |
| key | 实际用到的索引 | 为 null 表示没走索引 |
| rows | 预估扫描行数 | 越小越好 |
| extra | 额外信息 | using index / using where / using filesort / using temporary |
5.2 type 列性能等级
- system / const：常量级，主键或唯一索引等值查询，最快
- eq_ref：主键或唯一索引关联，每行只匹配一条
- ref：普通索引等值匹配
- range：索引范围扫描（between/in/> </like 前缀匹配）
- index：扫描整个索引树（比全表扫快一点，因为索引小）
- all：全表扫描，必须优化
5.3 extra 常见值解读
- using index：覆盖索引，性能最好
- using where：在存储引擎层过滤后，server 层再过滤
- using index condition：索引条件下推（icp），在索引层面就过滤
- using filesort：文件排序，数据量大时性能差
- using temporary：使用临时表，group by / distinct 常见，性能差
六、数据库配置层面优化
6.1 innodb 关键参数
# 缓冲池大小，建议设为物理内存的 50%~70% innodb_buffer_pool_size = 8g # 日志文件大小，影响写入性能和崩溃恢复时间 innodb_log_file_size = 1g # 事务刷盘策略，0/1/2 三档 # 1 = 最安全（每次提交刷盘），0/2 = 性能好但可能丢 1s 数据 innodb_flush_log_at_trx_commit = 1 # 每个表独立表空间，便于管理和回收空间 innodb_file_per_table = 1 # 脏页刷新比例 innodb_max_dirty_pages_pct = 75
6.2 连接与缓存
# 最大连接数 max_connections = 500 # 排序缓冲区，每个连接独享，不要设太大 sort_buffer_size = 2m # 临时表大小 tmp_table_size = 64m max_heap_table_size = 64m
七、慢查询定位与排查流程
7.1 开启慢查询日志
-- 临时开启 set global slow_query_log = on; set global long_query_time = 1; -- 超过 1 秒记录 set global log_queries_not_using_indexes = on; -- 没走索引的也记录
7.2 用 mysqldumpslow 分析
# 按访问次数排序取前 10 mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # 按查询时间排序 mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
7.3 排查步骤
- 发现慢 sql：慢查询日志 / 监控告警 / 业务反馈
- explain 看执行计划：重点看 type、key、rows、extra
- show index 看索引情况：确认索引是否存在、区分度如何
- 分析数据分布：
count(*)看数据量，distinct看区分度 - 加索引 / 改写 sql：优先加索引，不行再改写法
- 验证效果：
explain+ 实际执行时间对比
八、高级优化技巧
8.1 索引条件下推（icp）
mysql 5.6 引入，把 where 过滤条件下推到存储引擎层，减少回表次数。
-- 索引 (last_name, first_name) select * from people where last_name = '张' and first_name like '%三%'; -- 没有 icp：先按 last_name 查，回表，再过滤 first_name -- 有 icp：在索引里先过滤 first_name，减少回表次数
8.2 哈希索引与自适应哈希
innodb 会自动为热点页建立自适应哈希索引（ahi），无需手动干预。
8.3 读写分离
主库写、从库读，分摊读压力：
- 一主一从 / 一主多从
- 中间件：shardingsphere、mycat、proxysql
- 注意主从延迟问题
8.4 分库分表
单表数据量超过千万级考虑分表：
- 水平分表：按时间、按用户 id 取模
- 垂直分表：大字段、冷字段拆到扩展表
- 分表中间件：shardingsphere
8.5 加缓存
数据库不是万能的，热点数据放缓存：
- redis / memcached 缓存热点查询
- 本地缓存（caffeine）缓存极热点数据
- 注意缓存穿透、击穿、雪崩问题
九、常见 sql 坑与避坑指南
9.1 count 相关
-- ✅ 最快：统计行数 select count(*) from table; -- ✅ 差不多：等价于 count(*) select count(1) from table; -- ❌ 慢：需要判断字段是否为 null select count(col) from table; -- ❌ 非常慢：去重统计 select count(distinct col) from table;
myisam 的 count(*) 存了元数据所以很快，但 innodb 是事务性的，必须实时统计。
9.2 大事务问题
长事务会导致：
- 锁持有时间长，阻塞其他操作
- undo log 膨胀，占用大量空间
- 主从延迟加大
优化：
- 事务尽量短小
- 事务内不要有外部调用（http/rpc）
- 批量操作分批提交
9.3 死锁排查
-- 查看最近一次死锁 show engine innodb status; -- 查看当前锁等待 select * from information_schema.innodb_locks;
避免死锁原则：
- 不同事务按相同顺序访问资源
- 事务尽量短
- 合理使用索引，减少锁范围
- 避免 gap lock（间隙锁）：rc 隔离级别下间隙锁更少
9.4 隐式转换坑
-- phone 是 varchar，但传了数字，触发隐式转换，索引失效 select * from user where phone = 13800138000; -- ✅ 正确写法 select * from user where phone = '13800138000';
十、优化总结：一张图记住核心思路
业务层
├── 只查需要的数据（避免 select *）
├── 分页用游标代替 offset
└── 热点数据加缓存
sql 层
├── where 条件走索引
├── 避免索引失效（函数/隐式转换/前缀%）
├── 联合索引遵守最左前缀
├── 尽量用覆盖索引
└── join/order by/group by 利用索引有序性
表结构层
├── 合适的数据类型（越小越好）
├── 避免 null
└── 大字段拆表
架构层
├── 读写分离
├── 分库分表
└── 数据库参数调优
结语
sql 优化不是一蹴而就的，而是一个持续迭代的过程。记住几个核心原则：
- 索引是银弹，但不是万能的：写多读少的表索引多了反而慢
- 先测量再优化：用 explain 和实际数据说话，不要凭感觉
- 业务优化优先于技术优化：很多时候改一下业务逻辑比加 10 个索引都管用
- 没有最好的方案，只有最合适的方案：根据业务场景权衡读写比例、数据量、一致性要求
掌握这些知识，足以应对 90% 以上的 sql 性能问题。剩下的 10% 需要结合具体业务场景和数据分布，具体问题具体分析。
以上就是从入门到精通详解sql查询与索引优化的实战指南的详细内容，更多关于sql性能优化的资料请关注代码网其它相关文章！
发表评论