MySQL 索引

1、什么是索引

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的 ROWID（相当于页码）快速找到表中对应的记录。索引的建立是表中比较有指向性的字段，相当于目录，比如说行政区域代码，同一个地域的行政区域代码都是相同的，那么给这一列加上索引，避免让它重复扫描，从而达到优化的目的。

索引特点

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列也是要占用空间的
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行 INSERT 、 UPDATE 、 DELETE 时，效率降低

2、索引结构

2.1 索引结构介绍

索引结构	描述
B+Tree	最常见的索引类型，大部分引擎都支持B+树索引（默认）
Hash	底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询
R-Tree(空间索引)	空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型（通常使用较少）
Full-Text(全文索引)	是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene, Solr, ES（通常使用较少）

2.2 不同存储引擎对索引支持情况!

索引	InnoDB	MyISAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-Tree索引	不支持	支持	不支持
Full-text	5.6版本后支持	支持	不支持

:::tip 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引 :::

3、数据结构介绍

3.1、二叉树

二叉树（Binary Tree）是一种特殊的树状数据结构，其中每个节点最多有两个子节点，分别称为左子节点和右子节点。二叉树的定义如下：

一个二叉树可以为空（即没有节点），或者由一个根节点和两颗分别称为左子树和右子树的二叉树组成。

二叉树的特点：

每个节点最多有两个子节点，分别为左子节点和右子节点。
左子树和右子树也是二叉树，可以为空。
二叉树的子节点没有特定的顺序，可以根据具体应用决定左右子节点的位置。

二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下层级较深，检索速度慢。

3.2、红黑树

红黑树（Red-Black Tree）是一种自平衡的二叉查找树，它在插入和删除操作后通过重新安排节点的颜色来保持平衡。红黑树的名称来源于每个节点上的颜色标记，每个节点可以是红色或黑色。

红黑树具有以下特点：

每个节点要么是红色，要么是黑色。
根节点是黑色的。
所有叶子节点（NIL节点）都是黑色的。
如果一个节点是红色的，则其两个子节点都是黑色的。
对于任意节点，从该节点到其所有后代叶子节点的简单路径上，均包含相同数目的黑色节点。

这些特点确保了红黑树的关键性质，即从根节点到任何叶子节点的最长路径不会超过最短路径的两倍，从而保持了树的平衡性。这种平衡性使得红黑树在实际应用中非常高效，常被用作集合、映射等数据结构的基础。

红黑树缺点：大数据量情况下，层级较深，检索速度慢的问题。

3.3、B-tree

B-Tree（B树）是一种用于存储和组织大量数据的自平衡搜索树结构。它被广泛应用于数据库和文件系统等领域，以提供高效的数据访问和查询性能。

B-Tree的特点包括：

多路平衡性：每个节点可以包含多个关键字和子节点，这使得B-Tree具有较好的平衡性能。通常情况下，B-Tree的所有叶子节点都位于相同的层级上。
有序性：B-Tree中的关键字按照升序排列，在进行范围查询时非常高效。
磁盘友好性：B-Tree的节点大小通常与硬盘页的大小相匹配，这样可以最大程度地减少磁盘I/O操作，提高读写性能。
自适应性：B-Tree能够动态调整自身的结构以适应数据的动态插入和删除操作，保持平衡性和性能稳定。

B-Tree的基本操作包括插入、删除和查找。在插入和删除操作时，B-Tree会通过重新分配关键字和调整节点来保持平衡。通过使用B-Tree索引，可以显著提高数据的检索效率，尤其是对于大规模的数据集。

需要注意的是，B-Tree并不仅限于二叉树的结构，每个节点可以包含多个子节点，使其适用于处理大规模数据集的情况。

3.4、B+tree

B+树（B+Tree）是一种类似于B-Tree的自平衡搜索树结构，被广泛应用于数据库和文件系统等领域。它是B-Tree的一种变体，相较于B-Tree，在存储和查询性能上有一些优化。

B+树与B-Tree相似，也具有多路平衡性、有序性和磁盘友好性的特点。但B+树在某些方面具有不同的设计：

只有叶子节点存储数据：B+树的内部节点只存储索引信息，而实际的数据记录则存储在叶子节点中，这样可以提高范围查询的效率。
叶子节点之间通过指针连接：B+树的叶子节点使用指针进行连接，形成一个有序链表，便于范围查询和顺序遍历。
顺序访问性能更好：由于叶子节点之间的指针连接和有序链表的形式，B+树在顺序访问时具有更好的性能。例如，对于范围查询或者按照关键字顺序遍历数据，B+树比B-Tree更适合。
叶子节点之间没有互相连接：B+树的叶子节点之间并没有直接的连接，需要通过内部节点进行导航，这样可以减少内部节点的空间占用。

B+树通常被用作数据库系统的索引结构，特别适用于支持范围查询和按顺序访问数据的场景。它的平衡性和磁盘友好性使得在大规模数据集的存储和检索过程中具有良好的性能表现。

相对于B-Tree区别

1、所有的数据都会出现在叶子节点
2、叶子节点形成一个单向链表

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。

3.5、Hash 哈希

哈希索引（Hash Index）是一种在数据库中用于快速查找数据的索引结构。它通过将关键字（Key）通过散列函数（Hash Function）转换成一个固定长度的散列值（Hash Value），然后将这个散列值与存储位置建立映射关系，从而实现高效的数据查找。

哈希索引的主要特点包括：

快速查找：哈希索引通过使用散列函数将关键字映射到存储位置，可以在常数时间内直接访问目标数据，因此具有非常高的查找效率。
相等查询优化：哈希索引适用于相等比较查询（例如WHERE column = value），对于这类查询，只需要计算散列值并进行一次查找即可，不需要遍历整个索引。
不支持范围查询和排序：由于哈希索引是基于散列值进行查找的，因此不支持范围查询（例如WHERE column > value）和排序操作。
冲突处理：由于散列函数将不同的关键字映射到相同的散列值可能性存在，这种情况称为哈希冲突。常见的解决冲突的方法包括开放地址法和链表法。

需要注意的是，哈希索引在某些场景下可能效果不如B树索引，因为它无法支持范围查询和排序操作，并且对于存在大量冲突的情况下性能可能会下降。因此，在选择索引类型时需要根据具体的业务需求和数据特点进行综合考虑。

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

Hash索引特点：

Hash索引只能用于对等比较（=、in），不支持范围查询（betwwn、>、<、…）
无法利用索引完成排序操作
查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引

存储引擎支持：

Memory
InnoDB: 具有自适应hash功能，hash索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的

4、索引分类

4.1 索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

4.2 InnoDB存储引擎索引分类

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

假设user表的id字段为聚集索引，name字段为二级索引，那么select * from user where name = 'Arm'的查询顺序如下：

会先到二级索引中查询name = Arm的数据，查询到name=Arm的id为10，然后再去聚集索引中查询id=10的数据（聚集索引中存放的是这一行的行数据）。

5、如何创建、删除、查看索引

5.1、创建索引

在执行 CREATE TABLE 语句时可以创建索引，也可以单独用 CREATE INDEX 或 ALTER TABLE 来为表增加索引。

ALTER TABLE

ALTER TABLE用来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

其中 table_name 是要增加索引的表名，column_list 指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名 index_name 可选，缺省时，MySQL 将根据第一个索引列赋一个名称。另外，ALTER TABLE 允许在单个语句中更改多个表，因此可以在同时创建多个索引。

CREATE INDEX

CREATE INDEX 可对表增加普通索引或 UNIQUE 索引。

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name 、index_name 和 column_list 具有与 ALTER TABLE 语句中相同的含义，索引名不可选。另外，不能用 CREATE INDEX 语句创建 PRIMARY KEY 索引。

CREATE TABLE

create table T(
    id int primary key, 
    k int not null, 
    name varchar(16),
    index (k)
)engine=InnoDB;

5.2、删除索引

可利用 ALTER TABLE 或 DROP INDEX 语句来删除索引。类似于 CREATE INDEX 语句，DROP INDEX 可以在 ALTER TABLE 内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP PRIMARY KEY;

5.3、查看索引

show index from table_name;

6、创建索引的注意事项

只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

MySQL 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by 中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

其中，前两条语句是等价的，删除掉 table_name 中的索引 index_name。第3条语句只在删除 PRIMARY KEY 索引时使用，因为一个表只可能有一个 PRIMARY KEY 索引，因此不需要指定索引名。如果没有创建PRIMARY KEY 索引，但表具有一个或多个 UNIQUE 索引，则 MySQL 将删除第一个 UNIQUE 索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

7、如何选择合适的列建立索引

在 where 从句，group by 从句，order by 从句，on 从句中虚线的列添加索引。
索引字段越小越好（因为数据库数据存储单位是以“页”为单位的，数据存储的越多，IO 也会越大）。
查询中与其它表关联的字段需要添加索引。
对一些经常处理的业务表应在查询允许的情况下尽量减少索引。
假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。