MySQL 进阶教程 第一章第二章

MySQL 进阶教程 第一章&第二章

说明：本教程面向有MySQL基础的学习者，通过电商系统的真实案例，深入理解索引与锁机制。第一章聚焦索引核心原理与优化实践（数据结构、执行计划、索引失效场景、设计原则）；第二章深入InnoDB锁机制与并发控制（事务隔离级别、MVCC、各类锁详解、死锁排查）。通过“订单系统性能优化”和“高并发秒杀”两个实战串联知识点。

第一部分：第一章 索引核心原理与优化实践

一、背景故事：电商订单系统查询慢

小张的电商订单表数据量已经突破5000万行，运营人员查询用户订单时需要等待几十秒。老板要求他彻底解决订单查询的性能问题。小张决定从索引原理入手，掌握优化的本质。

二、索引本质与B+树原理

2.1 索引到底是什么

索引就像一本书的目录：先通过目录找到页码（索引值），再翻到对应页码快速定位内容。MySQL官方定义：索引（Index）是帮助MySQL高效获取数据的排好序的数据结构。

对比测试：一张5000万行的订单表，SELECT * FROM orders WHERE user_id = 123456 无索引耗时 45秒，建立 idx_user_id 索引后仅需 0.02秒，性能提升 2250倍。

2.2 为什么是B+树

MySQL InnoDB默认使用 B+树 作为索引结构，理由如下：

· 树高极低：千万级数据B+树高度仅3-4层，意味着查询只需3-4次磁盘I/O。
· 范围查询友好：叶子节点通过双向链表相连，天然支持 BETWEEN、ORDER BY。
· 扇出高：非叶子节点只存储索引键，一个页（16KB）可存储约1200个键值。

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

· 哈希索引：仅支持等值查询，不支持范围、排序、模糊匹配。
· 二叉树/AVL树：层高过高，查询需要的磁盘I/O次数多。

三、索引分类体系

3.1 按物理存储划分

索引类型 存储内容 特点
聚簇索引 叶子节点存储整行完整数据 InnoDB独有，每表仅一个；数据物理存储顺序与索引逻辑顺序一致
非聚簇索引 叶子节点只存储主键值 查询需要回表：先查二级索引拿到主键，再查聚簇索引获取完整数据

3.2 按业务用途划分

-- 主键索引（同时是聚簇索引）ALTERTABLEordersADDPRIMARYKEY(id);-- 唯一索引（不可重复，可有一个NULL）ALTERTABLEordersADDUNIQUEINDEXidx_order_no(order_no);-- 普通索引（仅加速查询）ALTERTABLEordersADDINDEXidx_user_id(user_id);-- 联合索引（多字段）ALTERTABLEordersADDINDEXidx_user_date(user_id,order_date);-- 覆盖索引：查询的所有字段都在索引中，无需回表SELECTuser_id,order_dateFROMordersWHEREuser_id=1001;-- 如果 idx_user_date 包含这两个字段，Extra列会显示 Using index[reference:10]

四、索引设计的黄金法则

4.1 最左前缀原则

联合索引 (a, b, c) 可以被用于 a、(a,b)、(a,b,c) 的查询，但无法直接用于 b 或 c 的查询。

-- idx_user_date (user_id, order_date) 索引SELECT*FROMordersWHEREuser_id=123;-- ✅ 可用SELECT*FROMordersWHEREuser_id=123ANDorder_date>'2024-01-01';-- ✅ 可用SELECT*FROMordersWHEREuser_id=123ANDamount>100;-- ⚠️ 只用到user_id部分SELECT*FROMordersWHEREorder_date>'2024-01-01';-- ❌ 不可用