MySQL 核心知识与索引优化全解析
本文涉及的产品
RDS MySQL DuckDB 分析主实例，基础系列 4核8GB
简介：
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分，阐述了 char 与 varchar 在存储方式和性能上的差异，以及事务的 ACID 特性、并发事务问题及对应的隔离级别（MySQL 默认 REPEATABLE READ）。索引基础部分，详解了 InnoDB 默认的 B+tree 索引结构（多路平衡树、叶子节点存数据、双向链表支持区间查询），区分了聚簇索引（数据与索引共存，唯一）和二级索引（数据与索引分离，多个），解释了回表查询的概念及优化方法，并分析了 B+tree 作为索引结构的优势（树高低、效率稳、支持区间查询）。索引优化部分，列出了索引创建的六大原则
一、MySQL 基础概念解析
- char 与 varchar 的区别两者都是存储字符串的类型，但核心差异体现在存储方式和性能上：
- char是定长字符串，无论实际存储的字符串长度如何，都会占用固定长度的存储空间。例如定义
char(10)，即使只存 “abc” 3 个字符，也会占用 10 个字符的空间。这种特性让 char 的操作速度更快，但可能造成磁盘空间浪费。
- varchar是变长字符串，占用的空间与实际存储的字符串长度相关。例如
varchar(10)存储 “abc” 时，仅占用 3 个字符的空间（额外加 1-2 字节记录长度）。它的操作速度略慢，但能节省空间。
- 实际使用中，固定长度的字段（如手机号
char(11)、身份证号char(18)）适合用 char；长度不固定的字段（如用户名varchar(50)、备注varchar(200)）适合用 varchar。
- 事务及四大特性事务是一组不可分割的数据库操作，要么全部成功，要么全部失败，以此保证数据的正确性和完整性。其四大特性（ACID）如下：
- 原子性：事务中的操作是最小单元，无法拆分，要么全部执行成功，要么全部回滚。
- 一致性：事务执行前后，数据必须处于合法状态（如转账后两人总金额不变）。
- 隔离性：多个事务并发执行时，彼此不受干扰，仿佛在独立环境中运行。
- 持久性：事务提交或回滚后，对数据库的修改永久生效，不会因系统故障丢失。
- 并发事务问题及解决：
并发事务可能引发三类问题：
- 脏读：事务 A 读取到事务 B 未提交的数据（若 B 回滚，A 读到的数据无效）。
- 不可重复读：事务 A 两次读取同一记录，期间事务 B 修改并提交，导致两次结果不同。
- 幻读：事务 A 按条件查询无数据，事务 B 插入符合条件的数据后，A 插入时发现数据已存在。
- 数据库通过隔离级别解决这些问题：
- MySQL 默认隔离级别是REPEATABLE READ，它平衡了安全性和性能。隔离级别并非越高越好，例如 SERIALIZABLE 虽解决所有问题，但会导致并发性能急剧下降。
二、索引基础
- MySQL 索引的数据结构InnoDB 存储引擎默认使用B+tree作为索引结构，其核心特点如下：
- 多路平衡搜索树：一个节点可存储多个 key 和指针（而非二叉树的 2 个），数据量越大，树的高度越低（通常 2-3 层），检索效率更高。
- 数据集中存储：所有数据仅存于叶子节点，非叶子节点仅作为索引，确保检索效率稳定（无论查询哪条数据，都需遍历到叶子节点）。
- 区间查询友好：叶子节点形成双向链表，可快速定位区间范围（如查询 “id>100 and id<200”）。
- 聚簇索引与二级索引
- 聚簇索引：数据与索引存储在一起，B+tree 的叶子节点保存整行数据。一张表有且仅有一个聚簇索引，默认是主键索引。若表无主键，MySQL 会选择第一个非空唯一索引作为聚簇索引；若仍无，则自动生成隐藏的 rowID 作为聚簇索引。
- 二级索引（非聚簇索引）：数据与索引分离存储，B+tree 的叶子节点仅保存主键（或聚簇索引值）。一张表可创建多个二级索引，查询时需通过主键关联聚簇索引获取完整数据。
- 例如，表
user(id, name, age)中，id是聚簇索引（叶子节点存id+name+age），name是二级索引（叶子节点存name+id）。
- 回表查询基于二级索引查询时，需先通过二级索引获取主键，再到聚簇索引中查询完整数据，这个过程称为回表查询（需扫描两次索引，性能较低）。
- 示例：通过
name查询用户信息时，先在name二级索引中找到id=10，再到聚簇索引中查id=10对应的name和age。
- 优化方式：
- 直接使用主键查询（避免二级索引）。
- 创建联合索引（如
(name, age)），实现 “索引覆盖”（二级索引已包含所需字段，无需回表）。
- 启用索引下推（MySQL 5.6+）：在二级索引层提前过滤数据，减少回表次数。
- B+tree 作为索引结构的原因对比其他结构（如二叉树、B 树），B+tree 的优势如下：
- 树高更低：非叶子节点仅存 key 和指针（不存数据），一页（16KB）可存储更多 key（如主键为 bigint (8 字节)+ 指针 (6 字节)，一页约存 1170 个 key），3 层树可容纳约 2190 万数据（1170×1170×16）。
- 效率稳定：所有数据存于叶子节点，查询耗时固定。
- 区间查询高效：叶子节点的双向链表支持快速范围扫描。
三、索引优化
- 索引创建原则
- 为数据量大（单表超 10 万条）、查询频繁的表创建索引。
- 优先为查询条件（where）、排序（order by）、分组（group by）字段创建索引。
- 选择区分度高的列（如手机号，而非性别），唯一索引效率更高。
- varchar 字段建议创建前缀索引（如
name(10)），平衡索引大小和区分度。
- 优先创建联合索引，将区分度高的字段放前面（如
(name, age)比(age, name)更优），减少单列索引（联合索引可覆盖更多查询场景）。
- 索引不宜过多（维护成本高，会降低增删改效率）。
- 索引失效的常见场景
- 违反最左前缀法则（联合索引需从最左列开始查询，如
(name, age)索引，查询age=20会失效）。
- 范围查询（如
age>20）右侧的列无法使用索引（(name, age)中，name='张三' and age>20仅name生效）。
- 索引列上使用函数或运算（如
substring(name, 1, 3)='张'、age+1=20）。
- 隐式类型转换（如
phone=13800138000，而phone是 varchar 类型，需加引号）。
- like 以 “%” 开头（如
name like '%三'，索引失效；name like '张%'则生效）。
- 可通过
explain命令分析 SQL 执行计划，重点关注key（是否使用索引）、type（性能等级，避免all全表扫描）、extra（如Using filesort表示文件排序，需优化）。
四、SQL 性能分析及优化
- 定位慢 SQL
- 工具监控：通过 SkyWalking 等链路追踪工具，查看接口耗时分布，定位慢 SQL。
- 慢查询日志：开启 MySQL 慢查询日志（
slow_query_log=1），设置阈值（如long_query_time=1秒），记录执行超 1 秒的 SQL。
- 分析 SQL 执行性能使用
explain查看执行计划，核心字段如下：
key：实际使用的索引（为NULL表示未使用索引）。type：查询类型（性能从优到差：NULL > system > const > eq_ref > ref > range > index > all）。extra：额外信息（如Using index表示索引覆盖，Using where; Using index表示索引下推生效）。
- SQL 优化经验
- 表设计：选择合适类型（如
tinyint存性别，date存生日），主键用递增类型（如自增 ID，避免页分裂）。
- 索引优化：合理创建索引，避免失效场景，利用联合索引覆盖查询。
- 架构优化：高并发场景采用读写分离（主库写，从库读）；数据量超千万时，考虑分库分表（如按 ID 范围拆分）。
