MySQL 二轮学习笔记·进阶篇·(二) 索引

索引

索引是MySQL从慢查询到秒级响应的关键，但是滥用索引会导致更新变慢（删改数据需要维护索引数据结构），浪费空间（索引是独立的数据结构，需要占用磁盘空间）

1.索引结构

二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢

红黑树缺点：大数据量下，层级较深，检索速度慢

B树（也叫B-树）（多路平衡查找树）：以最大度数为5的b-tree为例，每个节点最多存储4个key，5个指针，一旦某个节点的key到5个，就会分裂，中间的key成为分裂后两侧节点的父节点

B+树：以4阶b+树为例，叶子节点中除了原来添加的数据（参考b树），还会储存非叶子节点中的所有数据，并且叶子节点形成了一个单向链表，非叶子节点虽然可能存一些数据，但只起到索引作用

总结B+树和B树的区别：①所有的数据都会出现在叶子节点，②叶子结点形成一个单向链表

MySQL对B+树进行了优化：在b+树基础上，叶子结点增加指针指向相邻节点，形成双向循环链表

Hash索引：优点：查询效率高，只需要检索一次（若无哈希冲突）

缺点：①hash索引只能用于对等比较，不支持范围查询（between，>，<，...）②无法利用索引进行排序

在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的

2.索引分类

聚簇索引：索引即数据，查聚簇索引（主键）时，在叶子结点找到索引就拿到了一行全部数据

二级索引（非聚簇索引）：索引即指针，查非聚簇索引时，先查到主键，再拿着主键查聚簇索引，最后找到数据，这个过程叫回表查询，效率比聚簇索引低

3.操作索引

创建索引

普通索引：create index 索引名 on 表名(字段名);

唯一索引：create unique index 索引名 on 表名(字段名);

联合索引：create index 索引名 on 表名(字段1， 字段2);

也可以创建表时直接定义索引：

create table user (
    id int primary key,-- 主键会自动创建聚簇索引
    age int,
    name varchar(20),
    phone varchar(11) unique, -- 唯一索引不会自动创建
    index idx_age (age) -- 指定创建普通索引
);

查看索引

show index from 表名;

删除索引

drop index 索引名 on 表名;

4.性能分析-验证索引效率

查看执行频次

show gobal status like 'Com_______'

慢查询日志

记录执行时间超过阈值（默认1秒）的查询

开启慢查询日志（临时生效）：

SET GLOBAL slow_query_log = ON; -- 开启
SET GLOBAL long_query_time = 0.5; -- 阈值设为0.5秒（超过则记录）

查看慢查询日志路径：

SHOW VARIABLES LIKE 'slow_query_log_file';

Explain-分析SQL执行计划

在sql前加explain，会模拟执行sql的过程，返回一个表格，字段有id（执行顺序）、type（查询类型）、key（实际使用的索引）、rows（预估扫描的行数）、Extra（额外信息）等

5.索引使用规则

(1)最左前缀法则

联合索引（如idx_age_name）的生效规则：查询条件必须包含 “索引的最左列”，且不能跳过中间列，否则索引失效。

例：联合索引(age, name)，不同查询条件的生效情况：

避坑技巧：创建联合索引时，将 “过滤性强（重复值少）、查询频率高” 的列放在最左边。

最左前缀法则核心要点：

1. 必须从最左边开始：不能跳过索引的前面的列
2. 不能中间断档：可以不用所有列，但不能跳过中间的列
3. 范围查询右边的列失效：WHERE name='A' AND age>20 AND city='B'，city用不到索引

(2)常见的索引失效场景

以下情况会导致索引失效，写 SQL 时必须避开：

1. 索引列用函数 / 计算：如where SUBSTR(name,1,1)='张'（name 列有索引，但用了函数，索引失效）。

   优化：尽量在应用层处理，或用 “前缀索引”（后续讲）。

2. 索引列用不等于（!=、<>）、Not in、Is not null：如where age!=20，索引失效（会触发全表扫描）。

   优化：能用between代替则代替（如age<20 or age>20改为age not between 20 and 20，部分场景可生效）。

3. 模糊查询以 % 开头：如where name like '%三'（索引失效），而where name like '张%'（索引生效）。

   原因：% 开头无法通过索引的有序性定位，只能全表扫描。

4. OR 连接的条件中，有一列无索引：如where age=20 or name='张三'（若 name 无索引，整个 SQL 索引失效）。

   优化：确保 OR 连接的所有列都有索引，或拆分为两个 SQL 用 UNION。

5. 隐式类型转换：如索引列是 varchar（phone='13800138000'），但查询用where phone=13800138000（数字类型），会触发隐式转换，索引失效。

   避坑：查询条件的类型必须与索引列类型一致。

(3)覆盖索引-避免回表，提升效率

覆盖索引是指 “查询的所有字段，都在索引中存在”，无需回表查询聚簇索引，效率极高（对应 Explain 的Extra: Using index）。

例：联合索引idx_age_name（包含 age、name 列），查询：

-- 覆盖索引生效：查询的age、name都在索引中，无需回表
EXPLAIN SELECT age, name FROM user WHERE age=20;

-- 覆盖索引失效：查询了id（主键，在聚簇索引）、age、name，需回表，一共2次操作，先查age和phone再查id，效率较低
EXPLAIN SELECT id, age, name FROM user WHERE age=20;

优化技巧：写 SQL 时，“按需查询字段”，避免用SELECT *，尽量让查询字段都在索引中，触发覆盖索引。

(4)前缀索引-优化字符串索引

对于长字符串（如varchar(255)的地址、邮箱），创建全列索引会占用大量空间，此时可创建 “前缀索引”—— 只对字符串的前 N 个字符建立索引，平衡 “空间” 与 “查询效率”。

创建语法：

CREATE INDEX idx_address_prefix ON user(address(10));

关键：确定 N 的长度 —— 确保 “前 N 个字符的区分度足够高”（重复值少），可通过以下 SQL 判断：

SELECT COUNT(DISTINCT LEFT(address,10)) / COUNT(*) FROM user;

(5)单列索引和联合索引怎么选

高频查询是 “单条件”（如where age=20），选单列索引idx_age。

高频查询是 “多条件”（如where age=20 and name='张三'），优先选联合索引idx_age_name，而非两个单列索引（idx_age+idx_name）。

6.索引设计原则

1. 高频查询优先：优先给高频查询的列建索引

   考虑索引覆盖：让高频查询的字段都包含在索引里，避免回表查询

2. 过滤性优先：优先给过滤性强的列建索引

   例：“性别” 列（只有男 / 女，重复值极高）过滤性差，建索引意义不大；“身份证号” 列（唯一，重复值为 0）过滤性极强，建索引效率极高。

   联合索引设计时，也需将过滤性强的列放在左侧（符合最左前缀法则，进一步缩小查询范围）

3. 避免过度索引

   避免重复索引

4. 避免给频繁更新的列建索引：更新操作会同步修改索引结构，频繁更新会导致索引 “频繁重构”，降低写入效率。

   例：“订单状态” 列（每秒更新数百次）不建议建索引；“订单创建时间” 列（一旦创建不再修改）适合建索引