MYSQL【三、索引】

1、索引分类

1. 物理存储方式 ：
   1. 聚簇索引（Clustered Index）：按照索引的顺序来存储数据的索引，一个表只能有一个聚簇索引。聚簇索引的数据存储和索引存储在一起。
   2. 非聚簇索引（Non-clustered Index）：独立于数据的逻辑顺序而存在的索引，一个表可以有多个非聚簇索引。非聚簇索引的数据存储和索引存储分开。
2. 唯一性
   1. 唯一索引（Unique Index）：要求索引列的值在整个表中是唯一的，不允许有重复值。
   2. 非唯一索引：允许索引列的值重复出现。
3. 索引方式
   1. B-Tree索引：基于B-Tree数据结构实现的索引，是最常见的索引类型，用于支持范围查询和排序。
   2. B+Tree索引：基于B+Tree数据结构实现的索引，相比B-Tree索引，更适合范围查询和顺序访问，常用于数据库系统。
   3. Hash索引：基于哈希算法实现的索引，适用于等值查询。
4. 索引对象
   1. 主键索引（Primary Key Index）：用于标识表中的唯一记录，每个表只能有一个主键索引。
   2. 外键索引（Foreign Key Index）：用于建立表与表之间关系的索引。
   3. 辅助索引（Secondary Index）：除了主键索引之外的其他索引。
   4. 组合索引（Composite Index）：包含多个列的联合索引，通过同时索引多个列可以提高特定查询的性能。

---

2、索引会加快查询

传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，MySQL需要将表的数据从头到尾遍历一遍。

添加索引之后，MySQL一般通过 BTREE 算法生成一个索引文件，在查询数据库时，找到索引文件进行遍历，在比较小的索引数据里查找，然后映射到对应的数据，能大幅提升查找的效率。

和通过书的目录，去查找对应的内容，一样的道理。

---

3、创建索的注意点

索引虽然是sql性能优化的利器，但是索引的维护也是需要成本的。

1. 将索引建在频繁查询的字段上：在经常用于 WHERE 条件判断、ORDER BY 排序和 JOIN 的字段上创建索引，可以提升查询性能。
2. 控制索引的数量：索引需要占用存储空间，并在更新时需要进行索引维护操作，因此应适量创建索引，不要过多。
3. 避免对低区分度字段创建索引：对于离散度较低的字段，如性别，创建索引可能带来的扫描行数减少有限，不建议创建索引。
4. 避免将频繁更新的字段作为主键或索引：维护索引文件需要成本，并可能导致页分裂和增加 I/O 操作次数，不建议将频繁更新的字段作为主键或索引。
5. 组合索引中将区分度高的值放在前面：为了满足最左前缀匹配原则，应该将区分度高的值放在组合索引的前面。
6. 创建组合索引而不是修改单列索引：对于经常使用多个条件查询的情况，创建组合索引可以替代多个单列索引，提高查询性能。
7. 对于过长的字段，考虑使用前缀索引：当字段值较长时，创建完整索引会消耗大量空间并导致较慢的搜索速度，可以考虑使用前缀索引，只索引字段的前几个字符。
8. 不建议使用无序的值（如身份证号码、UUID）作为索引：当主键具有不确定性时，容易导致叶子节点频繁分裂，造成磁盘存储的碎片化，不建议将无序的值作为索引。

---

4、索引失效

1. 查询条件包含 OR 操作符：当查询条件中使用 OR 操作符连接多个条件时，索引可能无法被有效使用，因为 OR 操作符的存在会导致数据库无法利用索引的有序性进行快速定位。
2. 字段类型不匹配：如果索引列的数据类型与查询条件中的数据类型不匹配，例如将字符串与数字进行比较而没有进行显式的类型转换，索引可能无法生效。
3. 使用了不适合索引的操作符：某些操作符可能无法使用索引，例如使用 LIKE 操作符进行模糊查询时，如果通配符 % 在前面，索引可能无法使用。
4. 联合索引中未使用索引的列：如果使用了联合索引，但查询条件中的列不是联合索引的第一个列，索引可能无法被有效利用。
5. 在索引列上使用函数或表达式：当在索引列上使用数据库系统的内置函数或表达式时，索引可能无法被使用。因为索引是按照列值进行存储和排序的，对列值进行函数计算或表达式运算会导致无法使用索引。
6. 对索引列进行运算：如果在查询条件中对索引列进行运算，例如使用 + 、 - 、 * 、 / 等运算符，索引可能无法被使用。
7. 不等于( != 或 <> )和 NOT IN 操作符：在索引列上使用不等于操作符或 NOT IN 操作符时，索引可能无法被有效利用。
8. 使用 IS NULL 或 IS NOT NULL 操作符：在索引列上使用 IS NULL 或 IS NOT NULL 操作符时，索引可能无法被使用。
9. 关联字段编码格式不一致：在使用左连接或右连接进行关联查询时，如果关联字段的编码格式不一致，索引可能无法被使用。
10. 优化器估计全表扫描更快：数据库优化器会根据统计信息和查询成本估计选择使用索引还是全表扫描，如果优化器认为全表扫描更快，可能选择不使用索引。

---

5、索引不适合的场景

1. 数据量较少的表：当数据表中的数据量较少时，使用索引可能会增加额外的开销，因为索引需要占用存储空间并且需要维护索引结构。在这种情况下，使用索引可能不会带来显著的性能提升，并且可能会增加查询的时间成本。
2. 频繁更新的字段：如果某个字段需要频繁进行更新操作，例如记录的状态字段或计数字段，为该字段添加索引可能会导致频繁的索引更新操作，从而降低数据库性能。因为每次更新该字段时，数据库都需要更新索引结构。
3. 低基数（离散度低）的字段：基数是指字段中不同取值的个数，离散度高的字 段具有较高的基数，而离散度低的字段具有较低的基数。对于离散度低的字段， 例如性别字段或布尔字段，添加索引可能无法提供足够的选择性，导致索引失效或性能下降。
4. 范围查询频繁的字段：如果某个字段需要频繁进行范围查询，例如日期范围查询或价格范围查询，添加索引可能无法有效地支持范围查询操作，因为索引是按照值的顺序进行排序的，而范围查询需要跳跃式地访问索引。

索引并不是建得越多越好。建立过多的索引会增加存储空间占用和维护开销，降低数据库的写入性能。此外，过多的索引也会导致查询性能下降。因此，在建立索引时需要综合考虑查询需求和性能优化，选择适当的索引来提高查询效率。

---

6、索引的数据结构

MySQL的默认存储引擎是InnoDB，它使用 B+树 结构实现索引。B+树 索引的结构简洁明了，具有以下特点：

• B+树中的每个节点可以存储多个键值对，叶子节点存储实际的数据记录。
• 非叶子节点只存储键值，用于指引搜索方向。
• 叶子节点之间使用双向指针连接，形成有序链表，方便范围查询和排序操作。
• B+树的高度相对较低，可以减少磁盘I/O操作，提高查询性能。

B+树索引的结构示意图如下：

图中，每个矩形块表示一个磁盘块，每个磁盘块包含多个数据项（粉色方框）和指针（黄色/灰色箭头）。根节点存储了指向其他节点的指针，而叶子节点存储了实际的数据。叶子节点之间的双向指针连接使得范围查询成为可能，可以通过遍历叶子节点链表来获取一定范围内的数据。

B+树索引的优点是高效支持范围查询、排序和快速插入删除操作。它适用于大部分的查询场景，并且能够有效利用磁盘预读特性，提高查询性能。

需要注意的是，MySQL也支持其他类型的索引，如哈希索引、全文索引和空间索引，这些索引可能使用不同的数据结构来实现，适用于特定的查询需求和数据类型。

● 一棵B+树能存储多少条数据

B+树的存储能力与其深度和页的大小有关。在 MySQL InnoDB 存储引擎中，页的大小通常为16KB，假如主键类型是 bigint，那么主键的长度为8字节，加上指针大小 （在InnoDB源码中设置为6字节），总共为14字节。

假设以一个页为单位进行存储，计算一下在不同深度下B+树可以存储的数据条数：

• 当树的深度为 1 时，只有一个叶子节点，能存放的数据条数为：(16KB / 14B) = 1170条数据。
• 当树的深度为 2 时，有1170个叶子节点，每个叶子节点能存放1170条数据，总共能存放的数据条数为：(1170 * 1170 * 16) = 21902400条数据。

结论：

• 当B+树的深度为 2 时，可以存储大约21902400条数据
• 当B+树的深度为 3 时，可以存储更多的数据
• 通常情况下，InnoDB的B+树深度一般为1-3层，因此可以满足千万级别的数据存储需求

需要注意的是，这个计算是基于理论的情况下，实际存储的数据条数还受到其他因素的影响，如页的利用率、索引的维护等。此外，MySQL InnoDB存储引擎也支持多级索引和压缩等技术，可以进一步提高存储效率和容量。

● 为什么用 B+ 树

◎ 为什么不用普通二叉树

普通二叉树存在退化的情况，如果它退化成链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

◎ 为什么不用平衡二叉树

读取数据的时候，是从磁盘读到内存。如果用平衡二叉树这种数据结构作为索引，那每查找一次数据就需要从磁盘中读取一个节点，也就是一个磁盘块，但是平衡二叉树每个节点只存储一个键值和数据，如果是 B+ 树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来了，查询效率就快。

◎ 为什么用 B+ 树而不用 B 树

• 继承了B树的优点：B+树是B树的变种，能够解决B树能解决的所有问题。同时，B+树也继承了B树解决两大问题的优点，即每个节点存储更多关键字和路数更多。
• 更高的存储容量：B+树的内部节点只存储关键字，而B树的内部节点需要同时存储关键字和数据。由于B+树内部节点只存储关键字，可以容纳更多的关键字， 从而提高了存储容量。
• 更适应范围查询 ：B+树的叶子节点形成一个有序链表，使得范围查询操作更加高效。而B树的叶子节点不一定是有序的，需要进行额外的操作才能进行范围查询。
• 更适应大规模数据存储：B+树相比B树具有更低的树高度，因为B+树的索引只存在于叶子节点上，而B树的索引分布在各级节点上。树的高度越低，查询时需要的磁盘访问次数越少，对于大规模数据存储，B+树更加高效。
• 更适应顺序访问：B+树的叶子节点形成一个有序链表，支持顺序访问。这对于需要按照顺序扫描数据的操作（例如范围查询和排序）非常有利，而B树则没有这样的特性。
• 效率更加稳定：B+树永远是在叶子节点拿到数据，所以磁盘IO次数是稳定的。而B树可能需要在非叶子节点上进行多次磁盘IO，导致效率不稳定。

---

7、Hash 索引和 B+ 树索引 区别

7.1、实现原理

• Hash索引使用哈希函数将关键字映射到一个固定大小的桶中，每个桶中保存了对应关键字的指针或数据。通过哈希函数的映射，可以快速定位到具体的桶，然后直接访问桶中的数据。因此，Hash索引适用于等值查询，即给定一个关键字， 可以直接计算哈希值并找到对应的数据。
• B+树索引是一种多层次的树结构，由根节点、内部节点和叶子节点组成。每个节点中包含了一组关键字和对应的指针或数据。B+树通过比较关键字大小来进行导航，从根节点开始逐级向下查找，最终找到叶子节点，叶子节点上存储了完整的数据。B+树索引适用于范围查询和顺序访问，因为它的叶子节点形成了一个有序链表。

7.2、适用场景

• Hash索引适用于等值查询，例如根据唯一标识查找数据。它在内存中查找的速度非常快，适合用于缓存、内存数据库等场景。但是，Hash索引不支持范围查询和排序操作，也不适合用于模糊查询或部分匹配。
• B+树索引适用于范围查询、排序和模糊查询等操作。它在磁盘上的查找速度相对较快，适合用于数据库系统等需要高效存储和查询的场景。B+树索引还具有良好的顺序访问性能，适合扫描大量数据或按照特定顺序访问数据的操作。

7.3、性能特点

• Hash索引在内存中查找速度非常快，平均时间复杂度为O(1)。但是，当哈希冲突较多时，查询性能可能下降，需要进行额外的处理，如使用开放地址法或链地址法来解决冲突。
• B+树索引在磁盘上的查找速度相对较快，平均时间复杂度为O(log N)，其中N为索引中的数据量。B+树索引的结构使得范围查询和排序操作更加高效，同时也适用于等值查询。

---

8、聚簇索引 和 非聚簇索引 区别

解聚簇索引不是一种新的索引，而是一种数据存储方式。 聚簇表示数据行和相邻的键值紧凑地存储在一起。我们熟悉的两种存储引擎——MyISAM采用的是聚簇索引，InnoDB采用的是非聚簇索引。

• 索引的数据结构是树，聚簇索引的索引和数据存储在一棵树上，树的叶子节点就是数据，非聚簇索引的索引和数据不在一棵树上。
• 一个表中只能拥有一个聚簇索引，而非聚簇索引一个表可以存在多个。
• 聚簇索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚簇索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
• 聚簇索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序。

---

9、回表

在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

例如：select * from user where name = ‘张三’：

---

10、覆盖索引

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用辅助索引就能够取得，不用去查主键索引，这时候使用的索引就叫做覆盖索引，避免了回表。

例如：select name from user where name = ‘张三：

---

11、左前缀原则/最左匹配原则

注意：最左前缀原则、最左匹配原则、最左前缀匹配原则 这三个都是一个概念。

最左匹配原则 是指在联合索引中，如果查询语句只使用了联合索引的一部分，那么只有从最左边的索引开始匹配，才能使用该索引进行查询，这被称为最左前缀原则。

根据最左匹配原则，创建了一个组合索引，如 (a1,a2,a3)，相当于创建了 （a1）、(a1,a2)和 (a1,a2,a3) 三个索引。

为什么不从最左开始查，就无法匹配？

比如有一个user表，我们给 name 和 age 建立了一个组合索引。

 ALTER TABLE user add INDEX comidx_name_phone (name,age);

组合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 (name 在左边，age 在右边)。

从上图可以看出来，name 是有序的，age 是无序的。当 name 相等的时候， age 才是有序的。

这个时候使用 where name= '张三' and age = 20 去查询数据的时候， B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name 相同的时候再比较age。但是如果查询条件没有 name，就不知道下一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较因子，所以就没用上索引。

---

12、索引下推优化

索引条件下推优化 （Index Condition Pushdown (ICP) ） 是MySQL5.6添加 的，用于优化数据查询。

• 不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL Server，MySQL Server进行过滤条件的判断。
• 当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL Server将这一部分判断条件 下推 给存储引擎，然后由存储引擎通过判断索引是否符合MySQL Server传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

例如一张表，建了一个联合索引（name, age），查询语句： select * from t_user where name like '张%' and age=10; ，由于 name 使用了范围查询， 根据最左匹配原则：

不使用ICP，引擎层查找到 name like '张%' 的数据，再由Server层去过滤 age=10 这个条件，这样一来，就回表了两次，浪费了联合索引的另外一个字段 age 。

但是，使用了索引下推优化，把where的条件放到了引擎层执行，直接根据 name like '张%' and age=10 的条件进行过滤，减少了回表的次数。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器 从存储引擎接收数据的次数。