MySQL 索引 - YHT's Note Repo

%% # 纲要 > 主干纲要、Hint/线索/路标 # Q&A #### 已明确 #### 待明确 > 当下仍存有的疑惑 **❓ 有什么问题？** %% # MySQL 索引分类 MySQL 中索引类型，可按不同维度如下划分： | 分类标准 | 类型 | | ---- | ------------------------------------------------------ | | 数据结构 | **B+树索引**、**Hash 索引**、**倒排索引** | | 物理存储 | **聚簇索引**、**二级索引** | | 字段性质 | **主键索引**、**唯一索引**、**普通索引**、**Full-Text 全文索引**、**前缀索引** | | 字段数量 | **单列索引**、**联合索引** | ![[Excalidraw/Excalidraw-Solution/MySQL 索引.excalidraw.md#^group=ny4J_cVeLcZ3XkCyliYj5|630]] > [!NOTE] 不同引擎在 "索引" 上支持的数据结构 > ![[_attachment/02-开发笔记/08-数据库/MySQL 相关/MySQL 索引.assets/IMG-MySQL 索引-366C6E7F8CB28DA48AE42F8A00D4ABEF.png|544]] # 「聚簇索引」与「二级索引」 - "**聚簇索引**" 也称 "**主键索引**"； - "**二级索引**" 也称 "**辅助索引**"，包括 **普通索引**、**唯一索引**、**前缀索引** 这几种具体类型。 ![[02-开发笔记/08-数据库/MySQL 相关/MySQL InnoDB 引擎#^5kf4d8]] 参见 [[02-开发笔记/08-数据库/MySQL 相关/MySQL InnoDB 引擎#InnoDB 中的 B+树索引|MySQL InnoDB 引擎#B+树索引]] # 主键索引也即 "**聚簇索引**"，建立在**主键**字段上的索引，只能在 **==建表时创建==**。 ![[02-开发笔记/08-数据库/MySQL 相关/MySQL InnoDB 引擎#^8luk98]] # 唯一索引（Unique Index）建立在 **`UNIQUE` 字段**上的索引，一张表可以有**多个**唯一索引。 > [!example] 创建唯一索引 > > ```sql > -- 建表时创建 > CREATE TABLE my_table ( > ... > UNIQUE KEY(col1, col2, ...) > ); > > -- 建表后创建 > CREATE UNIQUE INDEX idx_name > ON my_table(col1, col2, ...); > ``` > # 普通索引（Index）普通索引就是建立在 **普通字段** 上的索引，用作索引的字段 **既不要求为主键，也不要求为 `UNIQUE`**。 > [!example] 创建普通索引 > > ```sql > -- 建表时创建 > CREATE TABLE my_table ( > ... > INDEX(col1, col2, ...) > ); > > -- 建表后创建 > CREATE INDEX idx_name > ON my_table(col1, col2, ...); > ``` > # 前缀索引（Prefix Index）前缀索引：是指**只对字符类型字段的==前 N 个字符==建立索引**，而非对整个字段建立索引，从而减少索引存储空间，提高索引效率。前缀索引可建立在 `char`、`varchar`、`text`、`binary`、`varbinary` 类型字段上。 > [!info] **前缀索引的存储方式与普通 B+ 树索引相同**，但索引值仅存储**列的前 N 个字符** > [!example] 创建前缀索引 > > ```sql > -- 建表时创建 > CREATE TABLE users ( > id INT PRIMARY KEY, > email VARCHAR(255), > INDEX idx_email_prefix (email(10)) -- 只索引前10个字符 > ) ENGINE=InnoDB; > > -- 建表后创建 > CREATE INDEX idx_name > ON my_table(col(n)); -- 索引col列的前n个字符 > ``` > # 全文索引（Full-text Index） > 仅 InnoDB （MySQL 5.6+）与 MyISAM 引擎支持，底层通过 "**倒排索引**" 实现。适用场景：**快速==全文搜索==**（模糊匹配）——通过 `MATCH() ... AGAINST()` 语法搜索**长文本字段**，如 `CHAR`、`VARCHAR`、`TEXT`。 > [!example] 创建全文索引 > > ```sql > -- 建表时创建 > CREATE TABLE articles ( > id INT AUTO_INCREMENT PRIMARY KEY, > title VARCHAR(255), > tag VARCHAR(255), > content TEXT, > FULLTEXT INDEX idx_ft_title (title) -- 对title列创建全文索引 > FULLTEXT INDEX idx_ft_title_tag (title, tag) -- 联合全文索引 > ); > > -- 建表后创建 > -- 方式一: > ALTER TABLE articles ADD FULLTEXT INDEX idx_ft_title (title); > -- 方式二: > CREATE FULLTEXT INDEX idx_ft_title ON articles(title); > ``` ![[02-开发笔记/08-数据库/MySQL 相关/MySQL InnoDB 引擎#^l3if7m]] # 哈希索引（Hash Index） > 仅 Memory 存储引擎支持，基于**哈希表**进行查找。适用于**等值查询**，**不能用于范围查询**，如 `BETWEEN`、`LIKE`、`>`、`<` 等操作。 # 索引使用建议 ## 索引建立适用索引的情况： - `UNIQUE` 字段 - 常用于 `WHERE` 查询的字段 ——可考虑联合索引 - 常用于 `GROUP BY` 或 `ORDER BY` 的字段 ——索引表遍历不适用索引的情况： - **频繁修改**的字段——维护索引开销大 - 存在**大量重复值**的字段——例如性别。索引建立注意事项： - **主键索引**最好是**自增**的 ——数据插入时直接追加，**无页分裂**，页满时直接**追加新页**，**不涉及数据移动**。。 - 索引列最好为 **`NOT NULL`** ## 索引查询 - 尽可能触发 **覆盖索引**，避免回表。 - 遵循联合索引的 **最左匹配原则** ![[02-开发笔记/08-数据库/MySQL 相关/MySQL InnoDB 引擎#覆盖索引（Covering Index）|MySQL InnoDB 引擎]] ![[02-开发笔记/08-数据库/MySQL 相关/MySQL InnoDB 引擎#联合索引的 "最左匹配原则"|MySQL InnoDB 引擎]] ## 索引失效情况 ###### （1） - （1）**避免查询中对 "索引列" 进行==计算==、调用==函数==或隐式==类型转换==**，均会导致索引失效。 - 原因：索引保存的是**字段原始值**，无法根据计算后的值进行比对，故无法走索引 - 优化： MySQL 8.0 起，增加了函数索引，可**针对==函数计算后的值==建立索引**。 ###### （2） - （2）**避免 `OR` 条件中包含 "非索引列"** - 原因：`OR` 中包含非索引列时，索引将失效。 - 优化：为查询列建立联合索引，或者分别建索引。 > [!example] > > ```sql > -- name有索引, 而city无索引, 则将走全表扫描 > SELECT * FROM users WHERE name = 'Alice' or city = 'New York'; > ``` > ###### （3） - （3）**避免 `LIKE %xx` 或 `LIKE %xx%`**： `%` 号在前，则将进行 "**全表扫描**" (type=ALL) 或 "**==全索引扫描==**" (type=index)[^2] - 原因：B+树按索引 key 排序，只能根据前缀进行比较。 - 优化：=> 改用 **`LIKE xxx%`** 或建立 `FULLTEXT` 索引 > [!example] > > ```sql > -- '%'在前，索引失效 > SELECT * FROM users WHERE name LIKE '%Alice%'; > > -- 优化一: > SELECT * FROM users WHERE name LIKE 'Alice%'; -- 可以使用索引 > -- 可改用全文索引 > ALTER TABLE users ADD FULLTEXT INDEX idx_name (name); > SELECT * FROM users WHERE MATCH(name) AGAINST('Alice'); > ``` > > # 执行计划检查通过 `EXPLAIN` + `SQL` 语句可查看 SQL 的执行计划，判断**是否触发使用索引**。 > [!example] > > ```sql > EXPLAIN SELECT * FROM users WHERE name = 'Alice'; > ``` > > 执行计划的显示结果包括： | 字段 | 说明 | | ------------- | ----------------------------------- | | possible_keys | 可能使用的索引 | | key | **实际使用**的索引，若为 **NULL 表示未使用索引** | | key_len | **索引列的字段类型的字节数**（若允许为 NULL，则字节数需+1） | | rows | **预计扫描的数据行数** | | type | **数据扫描类型** | | Extra | **额外的查询优化信息** | ## type 字段说明 type 字段指示了**查找数据时采用的扫描方式**，包括以下类型（按**查询效率**由低到高排列）： | 字段值 | 类型 | 触发索引 | 说明 | | ------ | ------------------------------- | ---- | ------------------------------------------------------------------------- | | ALL | **全表**扫描 | ❌ | 索引未生效，遍历整个数据表。 | | index | **全索引**扫描 | ✔️ | 索引表进行全表扫描。 | | range | **索引范围**扫描 | ✔️ | 索引定位后，还需进行**范围查找** （where 中使用 `<`, `>`, `between` 时） | | ref | **非唯一索引**扫描 （或唯一索引的非唯一性前缀） | ✔️ | 索引列的**值不唯一**，即普通索引。 因此索引定位后，还需在目标值附近进行小范围检索 | | eq_ref | **唯一索引**扫描 | ✔️ | 连接查询时，通过主键或唯一索引查找。 （例如 `join` 条件为两表的某**列相等**，且**该列具有唯一索引**） | | const | **主键 or 唯一索引扫描** | ✔️ | 使用主键或唯一索引与常量值比较，只有一条查询结果 （例如 `SELECT name FROM users WHERE id = 1;`） | > [!NOTE] 区分 "**全表扫描`ALL`**" 与 "**全索引扫描`index`**" > > - ALL：遍历**完整数据表**（按聚簇索引顺序），**会读取每一行的完整数据**，磁盘 I/O 开销更大。 > - index：只遍历**特定索引表**，根据 **==特定索引列==的索引顺序**，遍历所有记录（而非原表存储顺序，即聚簇索引顺序） > > index 通常出现在下列情况： > > - **`ORDER BY` 子句**，对**索引列**排序时。 > - 例如 `SELECT age FROM users ORDER BY age;`， `age` 为索引列。 > - **覆盖索引**，索引列本身包含所需字段，**只需读取==索引列==的数据**，不访问原表完整数据行，无回表过程，磁盘 I/O 开销更小。 > - 例如 `SELECT age FROM users;`， `age` 为索引列。 > ## Extra 字段说明 Extra 字段提供了额外的查询优化信息，帮助判断： - 是否索引覆盖、是否需要回表查询； - 是否需要**排序** 或 **临时表**； | 内容 | 含义 | 使用索引 | | --------------------- | ---------------------------------------- | ---- | | Using index | **覆盖索引**（无需回表） | ✔️ | | Using index condition | **索引条件优化**，仍需回表，但**减少了回表次数** | ✔️ | | Using where | **索引过滤**，**需要回表** | ✔️ | | Using filesort | 需要**额外排序**（例如查询包含 group by，且无法利用索引完成排序时） | ❌ | | Using temporary | 需要使用**临时表**（常见于涉及 order by 或 group by 时） | ❌ | > [!NOTE] 「索引过滤」：指基于**索引**完成了数据过滤，但**仍需==回表==**取得完整数据。 > > 即 where 子句**使用了索引列**，但 **SELECT 查询数据** 超出索引字段，故需要回表的情况。 > > 例如 `SELECT * FROM users WHERE name = 'Alice';` > # Buffer ## 闪念 > sudden idea ## 候选资料 > Read it later # ♾️参考资料 - [为什么 MySQL 采用 B+ 树作为索引？ | 小林coding](https://xiaolincoding.com/mysql/index/why_index_chose_bpuls_tree.html#_3%E3%80%81%E8%8C%83%E5%9B%B4%E6%9F%A5%E8%AF%A2) - [索引失效](https://mp.weixin.qq.com/s/lEx6iRRP3MbwJ82Xwp675w) # Footnotes [^1]: [为什么 MySQL 采用 B+ 树作为索引？ | 小林coding](https://xiaolincoding.com/mysql/index/why_index_chose_bpuls_tree.html#mysql-%E4%B8%AD%E7%9A%84-b-%E6%A0%91) [^2]: [MySQL 使用 like “%x“，索引一定会失效吗？ | 小林coding](https://xiaolincoding.com/mysql/index/index_issue.html#%E9%A2%98%E7%9B%AE%E4%BA%8C)