MySQL采用B+树作为索引

MySQL 采用 B + 树 作为索引结构，是由其磁盘存储特性、查询性能需求和数据库场景特点共同决定的。以下是核心原因的深度解析：

一、磁盘 IO 优化：减少查询的 IO 次数

数据库索引存储在磁盘上，查询数据时需将磁盘块加载到内存。磁盘 IO 是数据库查询的主要性能瓶颈，因此索引结构需尽可能减少 IO 次数。

• B + 树的节点更大，树高更低：B + 树的非叶子节点不存储数据，仅存储索引键值，因此单个节点能容纳更多键值。相比 B 树（非叶子节点存数据）或二叉树（每个节点仅 1 个键值），B + 树的 “扇出”（节点子节点数）更高，树的高度（层级）更低。例如：若每个节点存 1000 个键值，3 层 B + 树可覆盖 10 亿条数据，仅需 3 次磁盘 IO。

• “局部性原理” 的利用：B + 树的叶子节点通过双向链表有序连接，磁盘预读（Read-Ahead）机制可将相邻叶子节点一并加载到内存，范围查询时无需频繁 IO。

二、查询性能稳定：所有查询都需到叶子节点

• B + 树的查询路径统一：无论查询单条数据还是范围数据，都必须从根节点遍历到叶子节点（数据仅存于叶子节点）。相比 B 树（可能在非叶子节点命中），B + 树的查询时间更稳定，避免了 “运气好” 和 “运气差” 的性能波动。

三、范围查询高效：天然支持排序和区间查找

数据库中范围查询（如 WHERE id BETWEEN 100 AND 200） 是高频场景，B + 树的结构对此做了极致优化：

• 叶子节点的双向链表：B + 树的叶子节点按索引键值有序排列，并通过指针连接成双向链表。范围查询时，只需找到起始键值，即可沿链表顺序遍历，无需回溯父节点，效率远高于 B 树或二叉树。

• 排序支持：由于叶子节点天然有序，ORDER BY、GROUP BY 等操作可直接利用索引的有序性，避免额外排序开销。

四、空间利用率高：非叶子节点不存数据

• 索引键更多，内存缓存更优：B + 树的非叶子节点仅存储索引键（如主键 ID），不存储完整行数据。因此，相同内存空间可缓存更多索引键，提高内存命中率，减少磁盘 IO。

• 全表扫描更快：遍历全表时，只需遍历叶子节点的链表，无需像 B 树那样遍历整个树（包括非叶子节点），扫描效率更高。

五、为什么不选其他数据结构？

数据结构	劣势
二叉搜索树	极端情况退化成链表，树高过高，IO 次数剧增。
AVL / 红黑树	树高仍较高（约 log₂N），且节点仅存 1 个键值，磁盘 IO 次数多。
B 树	非叶子节点存数据，扇出低，树高更高；范围查询需回溯父节点，效率低。
哈希表	仅支持等值查询，不支持范围查询、排序；哈希冲突时性能退化。

索引有效性

一、如何评估索引是否生效？

通过 MySQL 的 EXPLAIN 命令分析执行计划，是判断索引是否生效的核心方法。以下是关键步骤和解读要点：

1. 使用 EXPLAIN 查看执行计划

在 SQL 前加上 EXPLAIN，MySQL 会模拟执行该查询并返回执行计划，而非实际执行 SQL：

sql

EXPLAIN SELECT * FROM users WHERE id = 1;

2. 重点关注 EXPLAIN 输出的关键字段

字段名	作用与关键值解读
type	访问类型，性能从优到劣：system > const > eq_ref > ref > range > index > ALL⚠️ 若为 ALL，表示全表扫描，索引未生效。
key	实际使用的索引名。若为 NULL，表示未使用索引。
rows	预计扫描的行数。值越小越好，说明索引过滤效率高。
Extra	额外信息，关键提示：- Using index：覆盖索引（无需回表），性能极佳。- Using where：需通过 WHERE 过滤数据。- Using filesort：需额外排序（索引未生效）。- Using temporary：用到临时表（索引未生效）。

3. 示例：判断索引是否生效

-- 假设 id 是主键索引
EXPLAIN SELECT * FROM users WHERE id = 1;

• 若 type 为 const，key 为 PRIMARY，说明索引生效。
• 若 type 为 ALL，key 为 NULL，说明索引失效，需排查原因。

二、常见的索引失效场景

以下是导致索引失效的高频原因，需重点避免：

1. 索引列上使用函数或表达式

❌ 错误示例：

-- 对 create_time 列使用 YEAR() 函数，索引失效
SELECT * FROM users WHERE YEAR(create_time) = 2023;

✅ 正确示例：

-- 直接对索引列进行范围查询，索引生效
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

2. 隐式类型转换

若索引列是字符串类型，但查询时用数字匹配，会触发隐式类型转换，导致索引失效。

❌ 错误示例：

-- phone 是 VARCHAR 类型，但传入数字 13800138000，索引失效
SELECT * FROM users WHERE phone = 13800138000;

✅ 正确示例：

-- 传入字符串，索引生效
SELECT * FROM users WHERE phone = '13800138000';

3. 使用 OR 连接条件，且部分列无索引

若 OR 前后的条件列中，有一列未建索引，整个索引会失效。

❌ 错误示例：

-- 假设 name 有索引，age 无索引，OR 导致索引失效
SELECT * FROM users WHERE name = '张三' OR age = 20;

✅ 正确示例：

-- 给 age 也加索引，或拆分为两条 SQL 用 UNION 连接
SELECT * FROM users WHERE name = '张三'
UNION ALL
SELECT * FROM users WHERE age = 20;

4. 模糊查询 LIKE 以通配符开头

❌ 错误示例：

-- % 在开头，索引失效
SELECT * FROM users WHERE name LIKE '%张';

✅ 正确示例：

-- % 在结尾，索引生效（前缀匹配）
SELECT * FROM users WHERE name LIKE '张%';

5. 联合索引不遵循最左前缀原则

联合索引 (a, b, c) 中，查询需从左到右依次使用索引列，跳过中间列会导致后续索引失效。

❌ 错误示例：

-- 跳过 a，直接查 b 和 c，索引失效
SELECT * FROM users WHERE b = 1 AND c = 2;

✅ 正确示例：

-- 遵循最左前缀，索引生效
SELECT * FROM users WHERE a = 1 AND b = 2;

6. 范围查询（>、<、BETWEEN）之后的列

联合索引中，若某列使用了范围查询，其右侧的列索引会失效。

❌ 错误示例：

-- 联合索引 (a, b)，a 用了范围查询，b 的索引失效
SELECT * FROM users WHERE a > 1 AND b = 2;

✅ 优化建议：

-- 调整联合索引顺序，将范围查询列放最后 (b, a)
SELECT * FROM users WHERE b = 2 AND a > 1;

7. 使用 NOT、!=、<> 等否定条件

否定条件可能导致优化器放弃索引，选择全表扫描（尤其是 InnoDB 引擎）。

❌ 错误示例：

-- != 可能导致索引失效
SELECT * FROM users WHERE id != 10;

✅ 优化建议：

-- 改用范围查询替代
SELECT * FROM users WHERE id < 10 OR id > 10;

8. 索引列参与运算

❌ 错误示例：

-- id 列参与运算，索引失效
SELECT * FROM users WHERE id + 1 = 10;

✅ 正确示例：

-- 直接对索引列赋值，索引生效
SELECT * FROM users WHERE id = 9;

9. 数据量过小，优化器认为全表扫描更快

若表中数据量极少（如仅几百行），MySQL 优化器可能认为全表扫描比走索引更高效，主动放弃索引。

10. 使用 IS NULL 或 IS NOT NULL

若索引列中 NULL 值占比过高，IS NULL 或 IS NOT NULL 可能导致索引失效（需结合实际数据分布判断）。

三、总结：索引优化最佳实践

1. 用 EXPLAIN 定期分析：上线前必查执行计划，重点关注 type、key、rows、Extra。
2. 避免索引列 “被操作”：函数、类型转换、运算都会导致索引失效。
3. 遵循最左前缀原则：联合索引中，优先使用左侧列查询。
4. 范围查询列放最后：联合索引中，将需范围查询的列放在索引顺序的末尾。

监控并优化慢SQL

监控和优化慢 SQL 是一个 “发现 -> 分析 -> 优化 -> 验证” 的闭环过程。

一、如何监控并发现慢 SQL？

1. 开启 MySQL 慢查询日志（Slow Query Log）

慢查询日志是记录执行时间超过阈值的 SQL 的核心工具。

配置步骤：

-- 1. 查看当前配置（默认可能关闭）
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 2. 开启慢查询日志（临时生效，重启后失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/lib/mysql/your-slow.log'; -- 日志路径
SET GLOBAL long_query_time = 1; -- 阈值：执行时间超过1秒的SQL被记录
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- 记录未使用索引的SQL（即使很快）

-- 3. 永久生效（需修改 my.cnf 或 my.ini 配置文件）
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/your-slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

2. 分析慢查询日志工具

慢查询日志生成后，需用工具解析和统计，避免人工查看海量日志。

工具	用途与优势
mysqldumpslow	MySQL 原生工具，可按执行时间、扫描行数等维度聚合统计。示例：mysqldumpslow -s t -t 10 /var/lib/mysql/your-slow.log（按时间排序，取 Top 10）
pt-query-digest	Percona 工具，功能更强大，可生成详细报告（执行频率、扫描行数、锁等待等）。示例：pt-query-digest /var/lib/mysql/your-slow.log
云数据库控制台	阿里云 RDS、腾讯云等提供可视化慢 SQL 分析界面，无需手动操作日志。

3. 实时监控：查看正在执行的 SQL

若需实时发现慢 SQL，可通过以下命令查看当前线程：

-- 查看正在执行的SQL（需 PROCESS 权限）
SHOW PROCESSLIST;

-- 或查询 information_schema（更详细）
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND = 'Query' AND TIME > 1;

4. Performance Schema（进阶监控）

MySQL 5.5+ 内置的性能监控库，可细粒度分析 SQL 的执行阶段、锁等待、IO 消耗等。

-- 1. 开启 Performance Schema（默认开启）
SHOW VARIABLES LIKE 'performance_schema';

-- 2. 查询慢SQL的执行统计
SELECT 
  DIGEST_TEXT, -- SQL 模板（去掉具体值）
  COUNT_STAR, -- 执行次数
  AVG_TIMER_WAIT/1000000000 AS avg_time_sec, -- 平均执行时间（秒）
  SUM_ROWS_EXAMINED -- 总扫描行数
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

二、如何分析慢 SQL？

拿到慢 SQL 后，需通过以下步骤定位根因：

1. 用 EXPLAIN 分析执行计划

EXPLAIN 是分析 SQL 的核心工具，需重点关注以下字段：

EXPLAIN SELECT * FROM users WHERE name = '张三' AND age > 20;

字段名	关键解读
type	访问类型，性能从优到劣：system > const > eq_ref > ref > range > index > ALL⚠️ 若为 ALL，需优化索引或 SQL。
key	实际使用的索引。若为 NULL，说明未用索引。
rows	预计扫描的行数。值越大，性能越差。
Extra	关键提示：- Using filesort：需额外排序（慢）。- Using temporary：用到临时表（慢）。- Using index：覆盖索引（优）。

2. 用 EXPLAIN ANALYZE（MySQL 8.0+）

比 EXPLAIN 更强大，会实际执行 SQL并返回真实耗时（注意：生产环境慎用！）：

EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

3. 查看表结构和索引

-- 查看表结构
DESC users;

-- 查看索引
SHOW INDEX FROM users;

检查点：

• 是否有合适的索引（联合索引顺序是否正确？）。
• 索引是否失效（如隐式类型转换、函数操作等）。
• 数据量是否过大（需分库分表？）。

三、如何优化慢 SQL？

1. 索引优化

• 添加缺失的索引：针对 WHERE、ORDER BY、GROUP BY 字段添加索引。示例：ALTER TABLE users ADD INDEX idx_name_age (name, age);（联合索引遵循最左前缀）。
• 优化联合索引顺序：将区分度高的列放前面（如 name 比 gender 区分度高）。
• 使用覆盖索引：让索引包含查询所需的所有字段，避免 “回表”。示例：SELECT id, name FROM users WHERE name = '张三';（若 idx_name 包含 id 和 name，则为覆盖索引）。
• 删除冗余索引：若已有 idx(a, b)，则 idx(a) 通常是冗余的，可删除以减少写入开销。

2. SQL 语句优化

• 避免 SELECT *：只查询需要的字段，减少数据传输和 IO。❌ SELECT * FROM users; → ✅ SELECT id, name FROM users;。
• 用 UNION ALL 代替 UNION：UNION 会去重（慢），若业务允许重复，用 UNION ALL。
• 避免在 WHERE 子句中使用函数或表达式：❌ WHERE YEAR(create_time) = 2023 → ✅ WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'。
• 合理使用 LIMIT 分页：深分页优化：SELECT * FROM users WHERE id > 10000 LIMIT 10;（代替 LIMIT 10000, 10）。
• 避免隐式类型转换：❌ WHERE phone = 13800138000（phone 是字符串）→ ✅ WHERE phone = '13800138000'。

3. 表结构优化

• 选择合适的数据类型：用 INT 代替 BIGINT（若不需要大整数），用 VARCHAR(20) 代替 VARCHAR(255)。
• 避免 NULL 值：NULL 值会占用额外空间，且索引统计复杂，建议用 0 或空字符串代替。
• 拆分大表：
  • 垂直拆分：将大字段（如 TEXT、BLOB）拆分到单独的表。
  • 水平拆分：按时间、ID 范围等拆分表（如 users_2023、users_2024）。

4. 数据库配置优化

• innodb_buffer_pool_size：建议设置为物理内存的 50%-75%，让更多数据缓存在内存中，减少磁盘 IO。
• sync_binlog 和 innodb_flush_log_at_trx_commit：若业务允许少量数据丢失（如日志系统），可设置为 0 或 2，提升写入性能。
• sort_buffer_size 和 join_buffer_size：适当调大（但不宜过大，避免内存浪费），减少 Using filesort 和 Using temporary。

5. 架构优化

• 读写分离：主库负责写入，从库负责查询，分担读压力。
• 引入缓存：用 Redis 缓存热点数据（如用户信息、商品详情），减少数据库查询。
• 分库分表：数据量超千万时，用 ShardingSphere、MyCat 等中间件进行分库分表。

四、验证优化效果

优化后需通过以下方式验证：

1. 再次用 EXPLAIN 查看执行计划：确认 type、key、rows 是否改善。
2. 实际执行 SQL：对比优化前后的执行时间（SELECT SQL_NO_CACHE ... 避免缓存干扰）。
3. 监控慢查询日志：确认该 SQL 不再出现在慢日志中。

总结

1. 开启慢查询日志 → 收集慢 SQL。
2. 用 EXPLAIN/pt-query-digest 分析 → 定位根因（索引？SQL？表结构？）。
3. 针对性优化 → 优先索引和 SQL，再考虑配置和架构。
4. 验证效果 → 确保优化生效。