MySQL 核心知识与索引优化全解析
本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介:
本文系统梳理了 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 范围拆分)。