mysql--索引

一、简介

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响。要找到一个合适的平衡点，这对应用程序的性能至关重要。

二、InnoDB常见索引

1.哈希索引

前面的文章已经提到过，InnoDB 会监控对 B+树索引页 的查询访问情况，如果发现某个 索引前缀组合 被频繁以“等值查询”方式访问（如 WHERE a = 1 AND b = 2），并且该模式可以命中同一个索引页范围，InnoDB 就会在内存中为这个“索引前缀”建立一个 哈希表

MySQL 5.7+ 默认开启（innodb_adaptive_hash_index=ON）
局限性：

问题	说明
❌ 效果不稳定	依赖访问模式，不一定总生效
❌ 锁竞争	哈希表全局锁，在高并发下可能成为瓶颈
❌ 内存开销	占用 Buffer Pool 内存空间
❌ 不适用于 OLAP	复杂查询、范围扫描无效
❌ 可能拖慢性能	某些 OLTP 场景实测反而降低 QPS

📌 生产建议

• 高并发 OLTP 系统中可尝试关闭测试性能影响

• 如果是读远多于写的小表或热点表建议开启

• OLTP 系统中存在大量“简单键值查询”

2.单列索引

普通索引：MySQL中最基本的索引类型，没有什么限制，允许定义索引的列中插入重复值和空值，纯粹就是为了查询数据更快一些；

唯一索引：索引列中的值必须是唯一的，但是允许空值；

主键索引：一种特殊的唯一索引，不允许有空值。

3.组合索引

在表中的多个字段组合上创建的索引，遵循最左前缀原则。

最左前缀原则：使用组合索引时在查询条件中使用的所有字段中从左开始有索引的字段，索引才会被使用，如果找到没有索引的字段那么后面所有字段上的索引也会失效。

回表：如果 select 所需获得列中有大量的非索引列，索引就需要到表中找到相应的列的信息。后面会详讲

4.全文索引

SELECT * FROM t_demo WHERE col_name LIKE '%aaa%';

这种查询在工作中会经常用到

全文索引，在MyISAM中早已支持，但是现在基本上大家用的都是InnoDB，而InnoDB对于FULLTEXT索引的支持是从MySQL5.6新引入的特性。

在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。但是从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中、日、韩文的分词。

三、B+树索引详解（转载https://www.cnblogs.com/nullzx/p/8729425.html）

B+ 树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引。B+ 树索引的构造类似于二叉树，根据键值（Key Value）快速找到数据。B+树索引还分为了聚集索引和辅助索引

1.数据结构：

B+树是应文件系统所需而产生的一种B树的变形树，B+树需要保证以下特性：

（1）文件的目录一级一级索引，只有最底层的叶子节点（文件）保存数据，非叶子节点只保存索引，不保存实际的数据，全部数据都保存在叶子节点中

（2）构建前需要先确定阶数m，任意一个节点最多存储m-1个数据，最多有m个子树，如下图所示，该b+树为4阶2层，

（3）构建好的B+树观察可发现所有叶节点从左到右刚好是顺序顺序

（4）对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。

2.B+树的插入操作

插入分为以下三种情况

下面是一颗5阶B树的插入过程，5阶B数的结点最少2个key，最多4个key。

a）空树中插入5

---

b）依次插入8，10，15

---

c）插入16

插入16后超过了关键字的个数限制，所以要进行分裂。在叶子结点分裂时，分裂出来的左结点2个记录，右边3个记录，中间key成为索引结点中的key，分裂后当前结点指向了父结点（根结点）。结果如下图所示。

当然我们还有另一种分裂方式，给左结点3个记录，右结点2个记录，此时索引结点中的key就变为15。

---

d）插入17

---

e）插入18，插入后如下图所示

当前结点的关键字个数大于5，进行分裂。分裂成两个结点，左结点2个记录，右结点3个记录，关键字16进位到父结点（索引类型）中，将当前结点的指针指向父结点。

当前结点的关键字个数满足条件，插入结束。

---

f）插入若干数据后

---

g）在上图中插入7，结果如下图所示

当前结点的关键字个数超过4，需要分裂。左结点2个记录，右结点3个记录。分裂后关键字7进入到父结点中，将当前结点的指针指向父结点，结果如下图所示。

当前结点的关键字个数超过4，需要继续分裂。左结点2个关键字，右结点2个关键字，关键字16进入到父结点中，将当前结点指向父结点，结果如下图所示。

当前结点的关键字个数满足条件，插入结束。

3.B+树删除

B+树使用填充因子（fll factor）来控制树的删除变化，50%是填充因子可设的最小值。B+ 树的删除操作同样必须保证删除后叶子节点中的记录依然排序，同插入一样， B+ 树的删除操作同样需要考虑以下表的三种情况，与插入不同的是，删除根据填充因子的变化来衡量。

4.聚集索引

，InnoDB 存储引擎表是索引组织表，即表中数据按照主键顺序存 放。而聚集索引（clustered index）就是按照每张表的主键构造—棵 B+ 树，同时叶子节 点中在放的即为整张表的行记录数据，也将聚集索弓的叶子节点称为数据页。聚集索引 的这个特性决定了索引组织表中数据也是索引的一部分。同 B+ 树数据结构一样，每个数据页都通过一个双向链表来进行链接。由于实际的数据页只能按照一棵 B+ 树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在 B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。

在InnoDb上会选择自增字段作为主键，是为了维持B+树的分裂特性，顺序添加到当前索引的后续位置，当达到最大就会分裂产生新的页，也不需要移动原有的顺序

5.辅助索引（非聚集索引）

对于辅助索引（Secondary Index，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签 （bookmark）。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。由 于InnoDB 存储引擎表是索引组织表，因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

四、回表

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

建立一个student表，表中有主键id,name,age,class四个字段,其中我们将name字段建了一个辅助索引，id为主键索引

如果查询语句是

select id,name from student where name = "sss"

那么mysql只需根据name辅助索引查找一遍即可查到id和name

但是如果sql语句是select id,name,age from student where name = "sss"

那么mysql就需要先根据name索引查找出name和主键id，在根据主键id走一遍聚集索引找到整条数据，拿到age，因为辅助索引只会存索引字段对应的主键id

解决方案：

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效，所以将name和age建立一个联合索引再查就不会有回表了

 五、最左前缀原则（联合索引的关键）

对于联合索引 (a, b, c)，以下查询能命中索引：

查询条件	是否命中？	原因
WHERE a = 1	✅	最左匹配
WHERE a = 1 AND b = 2	✅	连续匹配
WHERE a = 1 AND b = 2 AND c = 3	✅	完全匹配
WHERE b = 2	❌	跳过 a
WHERE a = 1 AND c = 3	⚠️ 部分命中（仅 a）	中断了连续性

📌 注意：遇到范围查询（>, <, BETWEEN, LIKE 'abc%'）后，后面的字段无法使用索引。

-- 假设索引是 (a, b, c)
WHERE a = 1 AND b > 2 AND c = 3;
-- 结果：a 和 b 能用索引，c 不能（因为 b 是范围查询）

六、如何创建高效的索引？（实战经验）

1. 选择合适的字段建索引

• 高频查询字段
• 区分度高的字段（如 user_id，不要给 gender 建索引）
• 避免给大字段建索引（如 TEXT）

2. 联合索引设计技巧

• 把过滤性强的字段放前面
• 经常一起出现的字段合并为联合索引
• 尽量实现“覆盖索引”

3. 控制索引数量

• 每增加一个索引，写操作（INSERT/UPDATE/DELETE）都要维护索引树
• 一般建议单表索引不超过 6 个

七、哪些情况会导致索引失效？

情况	示例	解决方案
1. 使用函数或表达式	WHERE YEAR(created_time) = 2024	改为 created_time BETWEEN '2024-01-01' AND '2024-12-31'
2. 类型转换	string_col = 123（字段是 VARCHAR）	保持类型一致
3. 左模糊匹配	LIKE '%java'	改为 LIKE 'java%' 或用全文索引
4. OR 条件未全走索引	WHERE a=1 OR b=2（只有 a 有索引）	改用 UNION
5. 最左前缀中断	见上文	调整索引顺序
6. != 或 NOT IN	WHERE status != 1	改为明确范围
7. IS NULL / IS NOT NULL	某些情况下不走索引	看执行计划

如何分析索引是否生效？

mysql--执行计划及sql优化https://blog.youkuaiyun.com/zhang09090606/article/details/120716215