从入门到精通详解SQL查询与索引优化的实战指南

一、为什么要做 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 索引失效的常见场景

2.5 索引设计最佳实践

1. 优先建联合索引，少建单列索引：一个联合索引往往能顶多个单列索引
2. 区分度高的列放前面：性别、状态这种低基数字段不适合单独建索引
3. 字符串建前缀索引：index idx_email(email(20))，节省索引空间
4. 避免冗余索引：有 (a,b) 就不需要 (a)
5. 控制索引数量：单表索引建议不超过 5 个，写多读少的表更少
6. 主键用自增 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）：

• 驱动表（小表）逐行取出，去被驱动表（大表）查匹配
• 被驱动表的关联字段必须建索引

优化规则：

1. 小表驱动大表：mysql 优化器会自动选，但复杂查询可能选错
2. 关联字段必须建索引：被驱动表的关联列一定要有索引
3. 尽量减少 join 次数：超过 3 张表的 join 要谨慎评估
4. 避免 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

4.2 常见设计误区

1. 用 varchar 存数字：排序会按字符串排，占空间更大
2. 时间用字符串存：无法用日期函数，索引效率低，用 datetime 或 timestamp
3. null 字段太多：null 值会占用额外空间，索引统计更复杂，建议设默认值
4. text/blob 滥用：大字段单独拆表，避免影响主表查询性能
5. 过度冗余字段：空间换时间要适度，冗余字段一致性维护成本高

4.3 范式与反范式

• 第三范式（3nf）：属性不依赖其他非主属性，数据不冗余
• 反范式：适当冗余字段，减少 join，提升查询性能
• 平衡策略：读多写少的场景适度反范式，写多读少保持范式

五、执行计划（explain）深度解读

5.1 explain 输出字段详解

explain select * from order where user_id = 123;

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 排查步骤

1. 发现慢 sql：慢查询日志 / 监控告警 / 业务反馈
2. explain 看执行计划：重点看 type、key、rows、extra
3. show index 看索引情况：确认索引是否存在、区分度如何
4. 分析数据分布：count(*) 看数据量，distinct 看区分度
5. 加索引 / 改写 sql：优先加索引，不行再改写法
6. 验证效果：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 优化不是一蹴而就的，而是一个持续迭代的过程。记住几个核心原则：

1. 索引是银弹，但不是万能的：写多读少的表索引多了反而慢
2. 先测量再优化：用 explain 和实际数据说话，不要凭感觉
3. 业务优化优先于技术优化：很多时候改一下业务逻辑比加 10 个索引都管用
4. 没有最好的方案，只有最合适的方案：根据业务场景权衡读写比例、数据量、一致性要求

掌握这些知识，足以应对 90% 以上的 sql 性能问题。剩下的 10% 需要结合具体业务场景和数据分布，具体问题具体分析。

以上就是从入门到精通详解sql查询与索引优化的实战指南的详细内容，更多关于sql性能优化的资料请关注代码网其它相关文章！