Mysql索引 - 指南
InnoDB时Mysql中最常用的 存储引擎索引. 索引设计基于B+树结构,且与数据存储深度结合(索引中存储数据).
InnoDB索引的基础
- 索引的本质与作用:
索引是辅助数据库高校查询内容的数据结构. 本质是排好序的快速查询数据结构.
核心作用:
- 降低查询的时间复杂度(从全表扫描的O(n)优化为B+树的O((log2n));
- 避免全表扫描, 减少磁盘IO次数(innoDB以页为单位写数据,索引可直接定位目标页)
- 辅助排序(若查询排序字段与索引字段一致,可直接利用索引的有序性避免额外排序).
- InnoDB索引的核心特性:
InnoDB索引与其他存储引擎(如MYISAM)的区别:
- 聚簇索引: 数据物理存储顺序与索引顺序一致, 索引叶子节点直接存储完整素材行(索引即数据).
- 事务安全: 索引执行支持事务(ACID), 依赖MVCC(多版本并发控制)和锁机制保证一致性.
- 二级索引依赖聚簇索引: 所有二级索引的叶子节点存储"聚簇索引的键值"(而非物理地址), 查询需要通过"回表" 找到完整内容.
InnoDB索引的底层数据结构 B+树
基于就是innoDB所有索引均B+树建立(而非B树或哈希表), 其结构设计转为磁盘IO优化,需要明确B+树与B树的差异:
| 对比维度 | B树 | B+树 |
|---|---|---|
| 叶子节点存储 | 索引键+数据(或数据地址) | 仅存储索引键+素材(聚簇索引)/聚簇索引键(二级索引) |
| 非叶子节点 | 索引键+子节点指针 | 仅存储索引键 |
| 数据有序性 | 整体有序,但必须遍历树查找数据 | 叶子节点借助链表串联,天然有序(支持范文查询) |
| 范围查询效率 | 低(得遍历树节点) | 高(直接遍历叶子链表) |
| 磁盘IO次数 | 多(节点存储数据,单次加载少) | 少(节点仅存储键和指针,单次加载更多索引项) |
B+树优势(适配InnoDB 场景)
- 减少磁盘IO: B+树非叶子节点仅存储"索引键+指针",单个节点可存储更多索引项,树的高度更低(通常树高度为2-4层,意味着进行2-4次IO).
- 高范围查询: 叶子节点通过双向量表串联, 如查询id BETWEEN 100 AND 200, 找到100后直接遍历节点即可,无需回表.
- 数据一致性: 所有数据仅存于叶子节点,非叶子节点仅作为"索引目录",比米娜内容冗余和一致性问题.
InnoDB 的索引类型(按效果分类)
innoDB索引分为:聚簇索引和二级索引两大类,所有查询最终都依赖聚簇索引定位数据.
聚簇索引: 索引即资料
- 定义:默认创建的 “主索引”, 其叶子节点直接存储完整的行资料, 且信息的物理地址存储顺序于聚簇索引逻辑完全一致.
- 创建规则(优先级)
- 若表定义了PRIMARY KEY(主键),则主键就是聚簇索引.
- 若未定义主键,InnoDB会选择第一个NOT NULL且唯一的列作为聚簇索引.
- 如以上均无, innoDB会**隐式 创建一个6字节的自增隐藏列(_rowid)**作为聚簇索引.
- 核心特性:
- 一张表仅能存储一个聚簇索引(数据物理顺序唯一).
- 查询效率高: 凭借聚簇索引查村可直接获取完整数据, 无需回表.
- 主键顺序影响数据存储: 若主键是自增整数(如INT AUTO_INCRMENT), 数据会按主键顺序连续存储在磁盘页中,减少页分裂,若主键是随机值(如UUID),会导致页分裂,降低性能.
二级索引:依赖聚簇索引
- 定义: 二级索引(辅助索引),需要用户手动创建的索引(如INDEX 或 UNIQUE INDEX), 其叶子节点不存储完整数据, 仅存储"聚簇索引的键值"(即主键值).
- 常见索引:
- 普通索引(INDEX):最基础的二级索引,无唯一性约束. 如 CREATE INDEX idx_name ON user(name);；
- 唯一索引(UNIQUE INDEX): 索引值唯一性(允许为NULL,且NULL仅出现1次), 如: CREATE UNIQUE INDEX idx_email ON user(email);
- 联合索引(Composite index): 基于多列创建的二级索引,遵循"最左前缀原则",如CREATE INDEX idx_name_age ON user(name, age);
- 前缀索引(Prefix Index): 对字符串列的前N个字符创建索引(减少索引占用空间), 如CREATE INDEX idx_title ON article(title(10));仅对前10个字符串索引
- 核心特点:
- 一张表可创建多个二级索引.
- 查询序"回表": 通过二级索引找到聚簇索引键值后, 需再次查询聚簇索引才能获取完整数据(除非触发覆盖索引)
- 联合索引"**最左前缀匹配"**原则.
特殊索引:覆盖索引
- 定义:覆盖索引并非独立索引类型，而是一种 “查询优化场景”：当查询的所有字段（SELECT 后的列）都包含在某一索引的叶子节点中时，无需回表，直接通过该索引即可获取所有所需数据。
InnoDB索引的工作流程
排查 "就是理解索引查询流程索引失效"的关键.
- 基于聚簇索引的查询(如主键查询)
- 根据查询的主键值，从 B+ 树的根节点开始遍历，通过比较键值找到对应的子节点指针；
- 逐层向下遍历，最终在叶子节点找到对应的主键值，直接获取完整数据行；
示例：SELECT * FROM user WHERE id = 100;（仅需 2-4 次磁盘 I/O）。
- 基于二级索引的查询(普通查询/ 联合索引)
需经历"定位二级索引 - 回表找聚簇索引" 两步(若未触发覆盖索引):- 第一步：查询二级索引：根据二级索引的键值（如 name = ‘张三’），遍历二级索引的 B+ 树，在叶子节点找到对应的 “聚簇索引键值（如 id=100）”；
- 第二步：回表查询聚簇索引：用第一步得到的 id=100，遍历聚簇索引的 B+ 树，在叶子节点获取完整信息行；
示例：SELECT * FROM user WHERE name = ‘张三’;（需 4-8 次磁盘 I/O，比聚簇索引多一次回表）；
例外（覆盖索引）：若查询字段均在二级索引中（如 SELECT name, age FROM user WHERE name = ‘张三’;），则第一步后直接返回结果，无需第二步。
InnoDB索引的优化实践
- 索引设计原则(避免无效索引)
- 优先用自增整数作为主键:自增主键保证数据连续存储,减少B+树页分裂(UUID等随机主键会导致频繁页分裂,降低写入性能)
- 联合索引遵循:“最左前缀”:
- 避免过度索引: 索引会加速查询，但会降低插入 / 更新 / 删除性能（每次写操作需同步维护所有相关索引的 B+ 树）；
- 字符串用前缀匹配: 对长字符串（如 VARCHAR(255)），仅索引前 N 个字符（如 title(10)），平衡索引效率与空间占用（需确保前缀唯一性足够，避免重复率过高）；
- 用覆盖索引减少回表: 高频查询如 SELECT id, name FROM user WHERE name LIKE ‘张%’，可创建 (name, id) 联合索引（虽 id 是聚簇索引键值已在二级索引中，但显式包含更清晰）。
- 索引失效
- 违反最左前缀原则: 联合索引 (a, b, c)，查询 WHERE b=2 AND c=3（无 a）；
- 索引列参与计算/函数: WHERE SUBSTR(name, 1, 1) = ‘张’（索引列 name 被函数处理，无法启用索引）；
- 索引列用不等于（!=/<>）、NOT IN、IS NOT NULL：这类条件会破坏索引的有序性，导致全表扫描（除非数据量极小）；
- 字符串不加引号: WHERE name = 123（name 是字符串类型，MySQL 会隐式转换为 WHERE CAST(name AS INT) = 123，索引失效）；
- OR连接并非索引列: WHERE name = ‘张三’ OR gender = ‘男’（gender 无索引，导致整个查询索引失效）；
- like以%开头: WHERE name LIKE ‘%三’（无法利用索引的有序性，若需模糊查询，尽量用 % 结尾，如 LIKE ‘张%’）。
- 索引维护
- 定期分析索引使用情况：通过 sys.schema_unused_indexes 查看未使用的索引，及时删除冗余索引；
- 优化碎片化索引：频繁删除 / 更新会导致索引碎片化（B+ 树出现空洞），可借助 ALTER TABLE 表名 ENGINE = InnoDB（重建表）或 OPTIMIZE TABLE 表名（InnoDB 中会重建表）整理碎片；
- 监控索引性能：通过 EXPLAIN 分析查询计划（重点看 type 列，ref/range/eq_ref 表示索引奏效，ALL 表示全表扫描）。
索引相关面试题
InnoDB 索引的底层数据结构是什么？为什么选择这种结构？
答案：
InnoDB 索引基于 B+ 树 实现。
选择 B+ 树的核心原因是：- 高效支持磁盘 I/O：B+ 树非叶子节点仅存储索引键和子节点指针（不存信息），单个节点可容纳更多索引项，树的高度通常仅 2-4 层，查询时只需 2-4 次磁盘 I/O（远优于 B 树或链表）。
- 范围查询高效：B+ 树叶子节点通过双向链表串联，范围查询（如 BETWEEN）可直接遍历叶子节点，无需回溯树结构。
素材一致性：所有数据仅存储在叶子节点，非叶子节点作为 “索引目录”，避免数据冗余和一致性问题。
什么是覆盖索引？如何利用覆盖索引优化查询？
答案：- 覆盖索引：指查询的所有字段（SELECT 后的列）均包含在某一索引的叶子节点中，无需回表即可获取完整结果。
- 优化原理：避免回表操作（减少一次磁盘 I/O），显著提升查询性能。
示例：表 user 有联合索引 idx_name_age (name, age)，查询 SELECT name, age FROM user WHERE name = ‘张三’ 时，name 和 age 均在索引中，直接返回结果，无需回表。
实践：针对高频查询，可设计包含查询字段的联合索引（如查询 id, name 时，创建 (name, id) 索引）。
联合索引的 “最左前缀原则” 是什么？举例说明。
答案：- 最左前缀原则：联合索引 (a, b, c) 仅能匹配以最左字段 a 开头的查询条件，即支持 a、a+b、a+b+c 的查询，不协助 b、b+c、c 的单独查询。
- 示例：
有效：WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3；
无效：WHERE b=2、WHERE b=2 AND c=3（缺少 a，无法使用索引）。
原因：联合索引的 B+ 树按 a→b→c 顺序排序，缺少 a 时无法确定查询范围。
为什么不建议用 UUID 作为 InnoDB 表的主键？
答案：因为 InnoDB 聚簇索引的物理存储顺序与主键顺序一致，而 UUID 是随机字符串，会导致：- 数据存储碎片化：新插入的 UUID 主键值随机分布，无法连续存储在磁盘页中，频繁触发页分裂（拆分现有数据页以插入新数据），降低写入性能。
- 索引体积增大：UUID 是 128 位字符串，比自增 INT（32 位）或 BIGINT（64 位）占用更多存储空间，导致索引树变高，查询效率下降。
- 建议：用自增整数（INT AUTO_INCREMENT 或 BIGINT AUTO_INCREMENT）作为主键，保证数据连续存储，减少页分裂。
索引失效的常见场景有哪些？
答案：即使创建了索引，以下情况会导致索引失效，触发全表扫描：- 违反最左前缀原则：联合索引 (a, b)，查询 WHERE b=2（无 a）。
- 索引列参与函数 / 运算：WHERE SUBSTR(name, 1, 1) = ‘张’（name 被函数处理）、WHERE age + 1 = 20（age 参与运算）。
- 使用不等于（!=/<>）、NOT IN、IS NOT NULL：如 WHERE age != 18（破坏索引有序性）。
- 字符串不加引号：WHERE name = 123（name 是字符串，MySQL 隐式转换为 CAST(name AS INT)，索引失效）。
- OR 连接非索引列：WHERE a=1 OR b=2（若 b 无索引，整个查询索引失效）。
- LIKE 以 % 开头：WHERE name LIKE ‘%三’（无法利用索引有序性，LIKE ‘张%’ 则有效）。
InnoDB 的行锁与索引有什么关系？
答案：InnoDB 的行锁（如记录锁、间隙锁）依赖索引，具体表现为：- 行锁基于索引加锁：只有通过索引条件过滤的行才会加行锁，否则会退化为表级锁。
例：UPDATE user SET name=‘test’ WHERE age=20，若 age 无索引，会锁定全表；若 age 有索引，仅锁定 age=20 的行。 - 联合索引的锁范围：对联合索引 (a, b) 加锁时，会根据查询条件锁定对应的索引范围（如 WHERE a=1 会锁定所有 a=1 的行及间隙）。
- 行锁基于索引加锁：只有通过索引条件过滤的行才会加行锁，否则会退化为表级锁。
如何判断一个查询是否使用了索引？如何优化？
答案：- 判断方法：利用 EXPLAIN 分析查询计划，重点关注 type 和 key 字段：
- type：显现访问类型，ref、range、eq_ref 表示使用索引；ALL 表示全表扫描（索引未使用）。
- key：显示实际使用的索引，NULL 表示未应用索引。
- 优化方向：
- 为查询条件（WHERE、JOIN、ORDER BY）添加合适的索引；
- 避免索引失效场景（如函数管理、违反最左前缀）；
- 用覆盖索引减少回表；
- 小表驱动大表，减少索引扫描范围。
- 判断方法：利用 EXPLAIN 分析查询计划，重点关注 type 和 key 字段：
什么？就是前缀索引的适用场景和优缺点
答案：- 定义：对字符串列的前 N 个字符创建索引（如 CREATE INDEX idx_title ON article(title(10))）。
- 适用场景：长字符串列（如 VARCHAR(255)），需平衡索引效率与存储空间（全字段索引体积过大）。
- 优点：减少索引占用空间，提升索引查询和维护效率。
- 缺点：
- 无法用于覆盖索引（仅存储前缀，无法获取完整字段值）；
- 若前缀区分度低（如前 10 个字符重复率高），索引效率下降。
- 实践：通过 SELECT COUNT(DISTINCT SUBSTR(title, 1, N)) / COUNT(*) FROM article 计算区分度，选择合适的 N（区分度接近 1 最佳）。
B+树中查询数据的总过程.
- 从根节点查起,根据比较键值与节点中存储的索引键值(用二分),确定数据所在的区间.从而确定分支从上到下最终定位到具体的叶子节点.
- 叶子节点存储实际的数据行记录,但是一页由16KB,存储的数据行不止一条.
- 叶子节点中资料行以组形式划分,利用页目录结构,依据二分查询可以定位到对应的组.
- 定位到组后,利用链表遍历就可以找到对应的数据行.
总结:先根据键值对的索引 进行二分匹配,从上到下找到叶子节点,每个叶子节点由16KB.叶子节点中以组形式存储.提供组的页目录借助二分法定位到组.再遍历组找到具体数据行.
1.根节点(根据二分从上到下)找到所在叶子节点, 2 叶子节点中 一组形式存储,且存储对应的页目录(二分法找到对应的组),3.遍历组找到具体信息.
- 事务的实现
主要通过 锁, Redo log, Undo log,和MVCC来实现事务的.
首先利用锁机制(行锁,间隙锁), 采用数据并发修改的控制, 保证事务的隔离性.
Redo log(重做日志): 记录事务对数据库的所有修改,当崩溃或失败,凭借重放恢复材料.满足事务持久性
Undo log(回滚日志): 记录事务的方向操作,即保存数据的历史版本. 用于事务回滚.满足原子性和隔离性.
MVCC(多版本并发控制): 满足非锁定的并发度需求.== 建立了读已提交和可重复读==两种隔离级别.达成了隔离性. - 深度分页
- 子查询,
可以将查询 分为只查询主键进行排序作为子查询.外层应用查询所需条件 排序后返回前n条数据. - 记录id
每次返回当前的最大id,然后下次查询时,携带条件id>maxid…
仅适合 连续查询的情况.如果跳页的化就不合适. - 使用Elasticsearch
浙公网安备 33010602011771号