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 兼顾均匀分布和扩容友好。但分库分表是最后手段,优先考虑优化索引、读写分离、数据归档等方案。