← 返回学习路线

SYSTEM

慢查询优化

八股1.6 慢查询优化system
src/content/notes/system/慢查询优化.md

1.6 慢查询优化

索引失效场景

-- 1. 对索引字段做函数运算
SELECT * FROM t WHERE DATE(create_time) = '2026-01-01';  -- 失效
-- 优化:改为范围查询
SELECT * FROM t WHERE create_time >= '2026-01-01' AND create_time < '2026-01-02';

-- 2. 隐式类型转换
SELECT * FROM t WHERE phone = 13800138000;  -- phone 是 varchar,传入数字导致失效
-- 优化:加引号
SELECT * FROM t WHERE phone = '13800138000';

-- 3. LIKE 前缀通配符
SELECT * FROM t WHERE name LIKE '%张';  -- 失效
SELECT * FROM t WHERE name LIKE '张%';  -- 能走索引

-- 4. OR 连接(一侧无索引则全部失效)
SELECT * FROM t WHERE a = 1 OR b = 2;  -- 若 b 无索引则全表扫描

-- 5. != / NOT IN / NOT EXISTS
SELECT * FROM t WHERE status != 1;  -- 通常失效,优化器选择全表扫描
-- 优化:改写为 IN
SELECT * FROM t WHERE status IN (0, 2, 3);

-- 6. 联合索引违反最左前缀
SELECT * FROM t WHERE b = 2;  -- idx(a,b) 失效

force index

当优化器因为统计信息不准而选择错误索引时,可以用 FORCE INDEX 强制指定索引:

-- 优化器可能选择全表扫描而不是走索引
SELECT * FROM order FORCE INDEX (idx_user_id) WHERE user_id = 10086;

面试题

Q:索引什么时候会失效?

A:常见失效场景有六类。一是对索引字段做函数运算或运算(WHERE age + 1 = 20),索引列被破坏无法走索引。二是隐式类型转换,如 varchar 列传入数字。三是 LIKE 以 % 开头。四是 OR 连接的条件中有一侧没有索引,会导致整个查询放弃索引。五是 !=、NOT IN、NOT EXISTS 通常走全表扫描。六是联合索引违反最左前缀原则。此外如果优化器评估走索引比全表扫描代价更大(比如筛选结果占表的大比例),也会放弃索引走全表扫描,这时可用 FORCE INDEX 指定索引。


1.7 主从复制

binlog 格式

格式 说明 优点 缺点
statement 记录 SQL 语句 日志量小 函数(NOW()、UUID())可能导致主从不一致
row 记录每行数据的变更 数据一致性好 日志量大(尤其批量操作)
mixed 自动选择 statement 或 row 兼顾两者 逻辑复杂

异步复制 vs 半同步复制

  • 异步复制:主库提交事务后不等从库确认,直接返回客户端。性能高但主库宕机可能丢失未同步的事务。
  • 半同步复制:主库提交事务后至少等待一个从库确认收到 binlog 才返回客户端。降低数据丢失风险,增加少量延迟。

GTID

GTID(Global Transaction Identifier)格式为 server_uuid:transaction_id,每个事务全局唯一标识。相比传统的基于 binlog 文件名 + position 的复制,GTID 让从库自动知道需要从哪个事务开始复制,简化了主从切换和故障恢复。

-- 查看主库状态(传统方式)
SHOW MASTER STATUS;
-- 查看从库状态
SHOW SLAVE STATUS\G

-- GTID 方式下配置从库
CHANGE MASTER TO 
  MASTER_HOST='192.168.1.100',
  MASTER_AUTO_POSITION=1;  -- 自动定位 GTID

面试题

Q:MySQL 主从复制的原理?

A:主从复制基于 binlog 实现,分三个步骤:主库执行事务后将变更写入 binlog;从库的 IO 线程连接主库,请求读取 binlog,主库的 Dump 线程将 binlog 发送给从库,从库 IO 线程将日志写入 relay log(中继日志);从库的 SQL 线程读取 relay log 回放事务,实现数据同步。binlog 有三种格式:statement 记录 SQL 语句但函数可能导致不一致;row 记录每行变更一致性最好但日志量大;mixed 自动选择。复制方式有异步复制(性能高可能丢数据)和半同步复制(至少等一个从库 ACK)。现代 MySQL 推荐用 GTID 代替传统的 file+position 方式,自动定位复制位点,简化故障切换。


1.8 分库分表

垂直拆分 vs 水平拆分

垂直拆分 水平拆分
拆分维度 按业务/字段拆 按数据行拆
典型场景 大字段拆到扩展表;不同业务拆到不同库 单表数据量过大(千万级以上)
特点 表结构变化,数据量不变 表结构不变,数据分散到多表/多库
难点 跨库 JOIN、分布式事务 路由、跨库分页、聚合查询

分片策略

  • hash 分片:对分片键取模,数据均匀分布,但扩容需要 rehash 迁移大量数据。
  • range 分片:按范围(如按时间、ID 段)分片,适合范围查询,但容易热点。
  • 一致性 hash:新增节点只影响相邻区间的数据,减少迁移量,但实现复杂。
// hash 分片示例
int shardCount = 16;
int shardIndex = userId.hashCode() % shardCount;
String table = "order_" + shardIndex;

ShardingSphere

Apache ShardingSphere 提供两种模式:

  • ShardingSphere-JDBC:以 jar 包形式嵌入应用,在 JDBC 层做 SQL 解析、路由、改写,轻量级无额外进程,适合 Java 技术栈。
  • ShardingSphere-Proxy:独立部署的数据库代理,应用连接 Proxy 像连接真实数据库一样,对语言透明,但增加一层网络开销。

面试题

Q:什么时候需要分库分表?

A:一般经验值是单表数据量超过 500 万~1000 万行,或单表数据文件超过 10GB,B+Tree 层级增加导致查询性能下降。分库分表解决的是存储容量和单机性能瓶颈。垂直拆分先做,把大字段拆到扩展表、把不同业务拆到不同库;水平拆分后做,把同一张表的数据分散到多个表或多个库。难点在于跨库 JOIN、分布式事务、全局唯一 ID 和分页查询(如”查看第3页”需要查所有分片再合并)。中间件用 ShardingSphere,分片策略常用一致性 hash 兼顾均匀分布和扩容友好。但分库分表是最后手段,优先考虑优化索引、读写分离、数据归档等方案。