MySQL学习_数据库索引

MySQL 相关的数据库索引这一部分内容单独提出来，然后整理一下相关内容。

参考：

大纲

底层数据结构选型
索引为什么使用 B+ 树
常用的索引类型有哪些
覆盖索引和联合索引
最左前缀匹配原则
索引下推
正确使用索引的一些建议

1、底层数据结构选型

可以看一下这个： MySQL索引详解 | JavaGuide

索引的数据结构选型一般选择方向有： Hash 表、二叉查找树（BST)、AVL 树、红黑树、B 树 & B+ 树。

考虑到一些原因，最终 MySQL 的索引选用的数据结构是 B+ 树。

Hash 表

如果采用 Hash 表作为底层数据结构的话，优点是能够快速检索（哈希表能够通过键值对快速定位数据，检索效率接近 O(1)）；但缺点也比较明显，

一个是哈希冲突
- 不同的键可能会产生相同的哈希值，导致冲突；虽然链地址法等技术可以解决，但会增加复杂度；
还有一个比较重要的是它不支持顺序和范围查询
- Hash 索引是根据 hash 算法来定位的（基于哈希值而非实际数据顺序）；因此它不适用于那些依赖于数据顺序的操作，如排序；同样因为这个原因 Hash 索引无法快速定位到特定范围的数据

二叉查找树 BST

最坏情况（退化斜树）

AVL 树

AVL 树是计算机科学中最早被发明的自平衡二叉查找树，它的名称来自于发明者 G.M. Adelson-Velsky 和 E.M. Landis 的名字缩写。AVL 树的特点是保证任何节点的左右子树高度之差不超过 1，因此也被称为高度平衡二叉树，它的查找、插入和删除在平均和最坏情况下的时间复杂度都是 O(logn)。

他有一个比较明显的缺点：由于 AVL 树需要频繁地进行旋转操作来保持平衡，因此会有较大的计算开销进而降低了查询性能。

红黑树

红黑树的应用是比较广泛的，TreeMap、TreeSet 以及 JDK1.8 的 HashMap 底层都用到了红黑树。对于数据在内存中的这种情况来说，红黑树的表现是非常优异

但因为树太高，不太适合在磁盘中使用

B 树 & B+ 树

？？？

参考：

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键值（id）顺序存放的，每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都指向下一个叶子节点，形成一个链表，便于范围查询。

示例：

假设有一张商品表，表里有这些数据

聚簇索引的 B+Tree 如图所示：

假设，执行了 select * from t_product where id = 5 查询语句，该查询语句的条件是找到 id（主键）为 5 的这条记录。因为 B+Tree 是一个有序的数据结构，所以可以通过二分查找算法快速定位到这条记录，这也就是我们常说的索引查询，具体过程如下：

从根节点开始，将 5 与根节点的索引数据 (1，10，20) 比较，5 在 1 和 10 之间，根据二分查找算法，找到第二层的索引数据 (1，4，7)；
在第二层的索引数据 (1，4，7)中进行查找，因为 5 在 4 和 7 之间，根据二分查找算法，找到第三层的索引数据（4，5，6）；
在叶子节点的索引数据（4，5，6）中进行查找，然后我们找到了索引值为 5 的这条记录。

二级索引的叶子节点存放的是主键值，不是实际数据

2、索引为什么使用 B+ 树

在实际使用中，找到一个平衡点，索引使用 B+ 树能够减少 IO 次数，查询范围更加高效。

MySQL学习_基础内容02#索引(B+树)

3、常用的索引类型有哪些

按照数据结构维度划分：

BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree，但二者实现方式不一样（前面已经介绍了）。
哈希索引：类似键值对的形式，一次即可定位。
RTree 索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

按照底层存储方式角度划分：

聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

按照应用维度划分：

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
普通索引：仅加速查询。
唯一索引：加速查询 + 列值唯一（可以有 NULL）。
覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

MySQL 8.x 中实现的索引新特性：

隐藏索引：也称为不可见索引，不会被优化器使用，但是仍然需要维护，通常会软删除和灰度发布的场景中使用。主键不能设置为隐藏（包括显式设置或隐式设置）。
降序索引：之前的版本就支持通过 desc 来指定索引为降序，但实际上创建的仍然是常规的升序索引。直到 MySQL 8.x 版本才开始真正支持降序索引。另外，在 MySQL 8.x 版本中，不再对 GROUP BY 语句进行隐式排序。
函数索引：从 MySQL 8.0.13 版本开始支持在索引中使用函数或者表达式的值，也就是在索引中可以包含函数或者表达式

主键索引 Primary Key

数据表的主键列使用的就是主键索引。

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。

检查唯一非空索引
- InnoDB首先检查表中是否存在唯一索引且该索引的所有列都不允许为null。如果找到这样的索引，InnoDB会将其作为主键。
自动创建隐藏的自增主键
- 如果上述条件不满足，即表中没有合适的唯一非空索引，InnoDB会自动创建一个隐藏的、自增的、6字节大小的主键。
- 这个隐藏的主键对用户是不可见的，但在InnoDB内部用于唯一地标识每一行。
- 这个主键是自增的，意味着每插入一行新数据，主键的值都会自动增加。

二级索引

二级索引的作用就是通过索引快速找到主键索引（定位主键的位置）

二级索引（Secondary Index）又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

唯一索引(Unique Key):唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
普通索引(Index):普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
前缀索引(Prefix):前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，
因为只取前几个字符。
全文索引(Full Text):全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

聚簇索引与非聚簇索引

聚簇索引（聚集索引）

聚簇索引（Clustered Index）即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。

在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

非聚簇索引（非聚集索引）

非聚簇索引(Non-Clustered Index)即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

聚簇索引与非聚簇索引

面试题: 非聚簇索引一定回表查询吗(覆盖索引) ？

在MySQL中，使用非聚簇索引（也称为二级索引或辅助索引）时，并不一定总是需要进行回表查询。是否需要回表查询取决于查询是否为覆盖索引查询。

回表查询:

当一个查询使用了非聚簇索引但是需要获取的数据不完全在索引中时，就会发生回表查询。
在这种情况下，数据库首先使用非聚簇索引找到对应的主键，然后再使用这个主键去聚簇索引（即数据表）中检索完整的行数据。

覆盖索引（Covering Index）:

如果查询所需的所有数据都包含在非聚簇索引中，这种情况称为覆盖索引。
当发生覆盖索引查询时，不需要进行回表查询，因为索引已经包含了所有需要的信息。
覆盖索引可以显著提高查询效率，因为它避免了额外的I/O操作去读取数据表。

例如，如果你有一个包含id（主键），name，和age列的表，且对name列有一个非聚簇索引。如果你的查询只是检索name列（比如SELECT name FROM table WHERE name = 'Alice'），那么这个查询就是一个覆盖索引查询，不需要回表查询。但是如果你需要检索age列的信息（如SELECT age FROM table WHERE name = 'Alice'），就需要回表查询了。

4、覆盖索引和联合索引

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引（Covering Index） 。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次，这样就会比较慢。而覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

以 score 和 name 两个字段建立联合索引：

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

联合索引的 B+Tree 是先按 score 进行排序，然后再 score 相同的情况再按 name 字段排序。

最左前缀匹配原则

最左前缀匹配原则指的是，

在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，
- 如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，
- 或者在执行过程中遇到范围查询（如 >、< ）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。
所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据

联合索引的最左匹配原则，在遇到范围查询（如 >、< ）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段后面的字段无法用到联合索引。但是，对于 >=、<=、BETWEEN、like 前缀匹配这四种范围查询，并不会停止匹配

复习看一下这篇文章： https://mp.weixin.qq.com/s/8qemhRg5MgXs1So5YCv0fQ

看一下这个例子：

-- 示例: 创建一个包含联合索引的表
CREATE TABLE example_table (
    col1 INT,
    col2 INT,
    col3 INT,
    INDEX idx_col1_col2_col3 (col1, col2, col3)
);

只有在 col1 相同的情况才，col2 才是有序的

联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。

Q1: select * from t_table where a > 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

由于联合索引（二级索引）是先按照 a 字段的值排序的，所以符合 a > 1 条件的二级索引记录肯定是相邻的，于是在进行索引扫描的时候，可以定位到符合 a > 1 条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 a > 1 条件位置。所以 a 字段可以在联合索引的 B+Tree 中进行索引查询。

但是在符合 a > 1 条件的二级索引记录的范围里，b 字段的值是无序的。

联合索引的最左匹配原则在遇到 a 字段的范围查询（ >）后就停止匹配了，因此 b 字段并没有使用到联合索引。

Q2: select * from t_table where a >= 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

由于联合索引（二级索引）是先按照 a 字段的值排序的，所以符合 >= 1 条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合 >= 1 条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 a>= 1 条件位置。所以 a 字段可以在联合索引的 B+Tree 中进行索引查询。

虽然在符合 a>= 1 条件的二级索引记录的范围里，b 字段的值是「无序」的，但是对于符合 a = 1 的二级索引记录的范围里，b 字段的值是「有序」的（因为对于联合索引，是先按照 a 字段的值排序，然后在 a 字段的值相同的情况下，再按照 b 字段的值进行排序）。

于是，在确定需要扫描的二级索引的范围时，当二级索引记录的 a 字段值为 1 时，可以通过 b = 2 条件减少需要扫描的二级索引记录范围（b 字段可以利用联合索引进行索引查询的意思）。也就是说，从符合 a = 1 and b = 2 条件的第一条记录开始扫描，而不需要从第一个 a 字段值为 1 的记录开始扫描。

所以，Q2 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

Q3: SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

Q3 查询条件中 a BETWEEN 2 AND 8 的意思是查询 a 字段的值在 2 和 8 之间的记录。

不同的数据库对 BETWEEN ... AND 处理方式是有差异的。在 MySQL 中，BETWEEN 包含了 value1 和 value2 边界值，类似于 >= and =<。而有的数据库则不包含 value1 和 value2 边界值（类似于 > and <）。

这里我们只讨论 MySQL。由于 MySQL 的 BETWEEN 包含 value1 和 value2 边界值，

所以类似于 Q2 查询语句，因此 Q3 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

Q4: SELECT * FROM t_user WHERE name like 'j%' and age = 22，联合索引（name, age）哪一个字段用到了联合索引的 B+Tree？

由于联合索引（二级索引）是先按照 name 字段的值排序的，所以前缀为 ‘j’ 的 name 字段的二级索引记录都是相邻的，于是在进行索引扫描的时候，可以定位到符合前缀为 ‘j’ 的 name 字段的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录的 name 前缀不为 ‘j’ 为止。

所以 a 字段可以在联合索引的 B+Tree 中进行索引查询，形成的扫描区间是['j','k')。注意， j 是闭区间。

所以，Q4 这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

5、索引下推

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

没有索引下推的情况:
- 在传统的索引使用中，数据库首先使用索引来找到符合索引部分条件的记录。然后，对于每个找到的索引条目，数据库都会进行“回表”操作，即访问主数据表来获取完整的行数据。
- 一旦获取了完整的行数据，数据库才能对WHERE子句中的其他条件进行评估。这意味着即使某些行最终不满足所有查询条件，它们仍然会被从磁盘加载到内存中，导致不必要的I/O开销。
有索引下推的情况:
- 使用索引下推时，数据库在索引遍历过程中就可以对索引中包含的字段进行更多的判断。这意味着，如果一个索引包含了多个列，数据库可以在访问主数据表之前，利用索引中的这些列来过滤掉更多不符合条件的记录。
- 例如，如果有一个索引是在first_name和age两个字段上，当查询条件是first_name = 'Alice' AND age > 25时，数据库可以在索引层面就利用这两个条件进行筛选，而不仅仅是first_name字段。这减少了回表的次数，因为只有同时满足这两个条件的记录才会被实际加载全行数据进行进一步处理。

看上面这个对比区别，感觉是在使用 > 这种范围查询进行了优化。

6、正确使用索引的一些建议

选择合适的字段创建索引
被频繁更新的字段应该慎重建立索引
限制每张表上的索引数量（建议单张表索引不超过 5 个）
尽可能的考虑建立联合索引而不是单列索引
注意避免冗余索引
字符串类型的字段使用前缀索引代替普通索引
避免索引失效
删除长期未使用的索引
知道如何分析语句是否走索引查询 EXPLAIN
...

避免索引失效

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：

SELECT * 不会直接导致索引失效（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖;
创建了组合索引，但查询条件未准守最左匹配原则;
在索引列上进行计算、函数、类型转换等操作;
以 % 开头的 LIKE 查询比如 LIKE '%abc';;
查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同);
发生隐式转换
……

索引失效场景参考： https://mp.weixin.qq.com/s/mwME3qukHBFul57WQLkOYg

to be contined....

DB_MySQL

MySQL调优操作

Java技术栈_数据库SQL

NoSQL_基础概念

DB MongoDB

ElasticSearch

ES的服务安装

MySQL学习_数据库索引

1、底层数据结构选型

2、索引为什么使用 B+ 树

3、常用的索引类型有哪些

主键索引 Primary Key

二级索引

聚簇索引与非聚簇索引

聚簇索引（聚集索引）

非聚簇索引（非聚集索引）

4、覆盖索引和联合索引

覆盖索引

联合索引

最左前缀匹配原则

5、索引下推

6、正确使用索引的一些建议

MySQL学习_数据库索引

MySQL调优操作

DB MongoDB

ES的服务安装

MySQL学习_数据库索引

1、底层数据结构选型 ​

2、索引为什么使用 B+ 树 ​

3、常用的索引类型有哪些 ​

主键索引 Primary Key ​

二级索引 ​

聚簇索引与非聚簇索引 ​

聚簇索引（聚集索引） ​

非聚簇索引（非聚集索引） ​

4、覆盖索引和联合索引 ​

覆盖索引 ​

联合索引 ​

最左前缀匹配原则 ​

5、索引下推 ​

6、正确使用索引的一些建议 ​

1、底层数据结构选型

2、索引为什么使用 B+ 树

3、常用的索引类型有哪些

主键索引 Primary Key

二级索引

聚簇索引与非聚簇索引

聚簇索引（聚集索引）

非聚簇索引（非聚集索引）

4、覆盖索引和联合索引

覆盖索引

联合索引

最左前缀匹配原则

5、索引下推

6、正确使用索引的一些建议