MySQL 核心知识与索引优化全解析

一、MySQL 基础概念解析

1. char 与 varchar 的区别两者都是存储字符串的类型，但核心差异体现在存储方式和性能上：

• char是定长字符串，无论实际存储的字符串长度如何，都会占用固定长度的存储空间。例如定义char(10)，即使只存 “abc” 3 个字符，也会占用 10 个字符的空间。这种特性让 char 的操作速度更快，但可能造成磁盘空间浪费。
• varchar是变长字符串，占用的空间与实际存储的字符串长度相关。例如varchar(10)存储 “abc” 时，仅占用 3 个字符的空间（额外加 1-2 字节记录长度）。它的操作速度略慢，但能节省空间。

2. 实际使用中，固定长度的字段（如手机号char(11)、身份证号char(18)）适合用 char；长度不固定的字段（如用户名varchar(50)、备注varchar(200)）适合用 varchar。
   
3. 事务及四大特性事务是一组不可分割的数据库操作，要么全部成功，要么全部失败，以此保证数据的正确性和完整性。其四大特性（ACID）如下：

• 原子性：事务中的操作是最小单元，无法拆分，要么全部执行成功，要么全部回滚。
• 一致性：事务执行前后，数据必须处于合法状态（如转账后两人总金额不变）。
• 隔离性：多个事务并发执行时，彼此不受干扰，仿佛在独立环境中运行。
• 持久性：事务提交或回滚后，对数据库的修改永久生效，不会因系统故障丢失。

4. 并发事务问题及解决：
   并发事务可能引发三类问题：
   

• 脏读：事务 A 读取到事务 B 未提交的数据（若 B 回滚，A 读到的数据无效）。
• 不可重复读：事务 A 两次读取同一记录，期间事务 B 修改并提交，导致两次结果不同。
• 幻读：事务 A 按条件查询无数据，事务 B 插入符合条件的数据后，A 插入时发现数据已存在。

5. 数据库通过隔离级别解决这些问题：

1. MySQL 默认隔离级别是REPEATABLE READ，它平衡了安全性和性能。隔离级别并非越高越好，例如 SERIALIZABLE 虽解决所有问题，但会导致并发性能急剧下降。

二、索引基础

1. MySQL 索引的数据结构InnoDB 存储引擎默认使用B+tree作为索引结构，其核心特点如下：

• 多路平衡搜索树：一个节点可存储多个 key 和指针（而非二叉树的 2 个），数据量越大，树的高度越低（通常 2-3 层），检索效率更高。
• 数据集中存储：所有数据仅存于叶子节点，非叶子节点仅作为索引，确保检索效率稳定（无论查询哪条数据，都需遍历到叶子节点）。
• 区间查询友好：叶子节点形成双向链表，可快速定位区间范围（如查询 “id>100 and id<200”）。

2. 聚簇索引与二级索引

• 聚簇索引：数据与索引存储在一起，B+tree 的叶子节点保存整行数据。一张表有且仅有一个聚簇索引，默认是主键索引。若表无主键，MySQL 会选择第一个非空唯一索引作为聚簇索引；若仍无，则自动生成隐藏的 rowID 作为聚簇索引。
• 二级索引（非聚簇索引）：数据与索引分离存储，B+tree 的叶子节点仅保存主键（或聚簇索引值）。一张表可创建多个二级索引，查询时需通过主键关联聚簇索引获取完整数据。

3. 例如，表user(id, name, age)中，id是聚簇索引（叶子节点存id+name+age），name是二级索引（叶子节点存name+id）。
   
4. 回表查询基于二级索引查询时，需先通过二级索引获取主键，再到聚簇索引中查询完整数据，这个过程称为回表查询（需扫描两次索引，性能较低）。

• 示例：通过name查询用户信息时，先在name二级索引中找到id=10，再到聚簇索引中查id=10对应的name和age。
• 优化方式：

• 直接使用主键查询（避免二级索引）。
• 创建联合索引（如(name, age)），实现 “索引覆盖”（二级索引已包含所需字段，无需回表）。
• 启用索引下推（MySQL 5.6+）：在二级索引层提前过滤数据，减少回表次数。

5. B+tree 作为索引结构的原因对比其他结构（如二叉树、B 树），B+tree 的优势如下：

• 树高更低：非叶子节点仅存 key 和指针（不存数据），一页（16KB）可存储更多 key（如主键为 bigint (8 字节)+ 指针 (6 字节)，一页约存 1170 个 key），3 层树可容纳约 2190 万数据（1170×1170×16）。
• 效率稳定：所有数据存于叶子节点，查询耗时固定。
• 区间查询高效：叶子节点的双向链表支持快速范围扫描。

三、索引优化

1. 索引创建原则

• 为数据量大（单表超 10 万条）、查询频繁的表创建索引。
• 优先为查询条件（where）、排序（order by）、分组（group by）字段创建索引。
• 选择区分度高的列（如手机号，而非性别），唯一索引效率更高。
• varchar 字段建议创建前缀索引（如name(10)），平衡索引大小和区分度。
• 优先创建联合索引，将区分度高的字段放前面（如(name, age)比(age, name)更优），减少单列索引（联合索引可覆盖更多查询场景）。
• 索引不宜过多（维护成本高，会降低增删改效率）。

2. 索引失效的常见场景

• 违反最左前缀法则（联合索引需从最左列开始查询，如(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 '张%'则生效）。

3. 可通过explain命令分析 SQL 执行计划，重点关注key（是否使用索引）、type（性能等级，避免all全表扫描）、extra（如Using filesort表示文件排序，需优化）。
   

四、SQL 性能分析及优化

1. 定位慢 SQL

• 工具监控：通过 SkyWalking 等链路追踪工具，查看接口耗时分布，定位慢 SQL。
• 慢查询日志：开启 MySQL 慢查询日志（slow_query_log=1），设置阈值（如long_query_time=1秒），记录执行超 1 秒的 SQL。

2. 分析 SQL 执行性能使用explain查看执行计划，核心字段如下：

• key：实际使用的索引（为NULL表示未使用索引）。
• type：查询类型（性能从优到差：NULL > system > const > eq_ref > ref > range > index > all）。
• extra：额外信息（如Using index表示索引覆盖，Using where; Using index表示索引下推生效）。

3. SQL 优化经验

• 表设计：选择合适类型（如tinyint存性别，date存生日），主键用递增类型（如自增 ID，避免页分裂）。
• 索引优化：合理创建索引，避免失效场景，利用联合索引覆盖查询。
• 架构优化：高并发场景采用读写分离（主库写，从库读）；数据量超千万时，考虑分库分表（如按 ID 范围拆分）。