MySQL索引

MySQL索引种类

1. B-Tree索引：B-Tree是最常用的索引类型，适用于等值查询、范围查询和排序操作。B-Tree索引可以用于所有MySQL数据类型。
2. 哈希索引：哈希索引适用于等值查询，但不支持范围查询和排序操作。哈希索引仅适用于MySQL中的某些数据类型，如整数和字符串。
3. 全文索引：全文索引适用于文本数据，可以加速全文搜索和匹配查询。全文索引只适用于MyISAM和InnoDB存储引擎。
4. 空间索引：空间索引适用于地理信息系统（GIS）应用程序，可以加速地理位置查询。空间索引只适用于MyISAM和InnoDB存储引擎。
5. 前缀索引：前缀索引适用于字符串类型数据，可以加速字符串的模糊匹配查询。前缀索引的长度可以通过指定前缀长度来控制。
6. 组合索引：组合索引可以将多个列的值组合在一起，加速多列查询和排序操作。组合索引的列顺序非常重要，需要根据查询条件和排序要求来选择合适的顺序。

B树索引底层原理

MySQL的索引是基于B+树的数据结构，B+树是一种多路平衡查找树，可用于存储有序的数据。在B+树中，每个节点可以存储多个关键字和指针，指向下一层节点。其中叶子节点包含了索引列的值以及指向数据行的指针，非叶子节点包含了指向下一层节点的指针。当需查找要一个特定的值时，MySQL会从根节点开始遍历B+树，根据节点中的关键字值来决定搜索方向，直到找到叶子节点。如果需要检索的值不存在于索引中，则查找会返回空结果。

MySQL索引的设计目标是尽可能地减少磁盘I/O操作，因为磁盘I/O操作是数据库中最慢的操作之一。因此，MySQL索引的B+树通常被设计成非常扁平化的，使得大多数查询可以在一次或少数几次磁盘I/O操作中完成。此外，MySQL还支持多种类型的索引，例如普通索引、唯一索引、全文索引等，可以根据不同的需求选择适合的索引类型来优化查询性能。需要注意的是，虽然索引可以显著提高查询性能，但也会增加数据插入、更新和删除操作的开销。因此，需要根据实际情况进行权衡，避免过多地创建索引，以避免对数据库的性能产生负面影响。

B树与B+树的区别

B+ 树的叶子节点保存了所有数据，而 B 树的叶子节点只保存了指向数据的指针。因此，在使用 B+ 树时，只需要遍历树的叶子节点就可以找到所需的数据，而在使用 B 树时，还需要通过指针跳转到另一个节点才能访问数据，增加了IO访问的次数，降低了查询效率。B+树能够更高效地进行范围查询和顺序访问操作。

唯一索引

MySQL的唯一索引是一种用于保证表中某一列或者多列的值唯一性的数据库索引。在创建唯一索引之后，MySQL会自动在索引列上创建唯一性约束，保证任何时刻表中该列的值都是唯一的。在插入数据时，MySQL会首先检查唯一索引中是否已经存在相同的键值。如果存在，则插入失败。如果不存在，则MySQL会在唯一索引中插入一个新节点，并将指针指向相应的数据行。在更新数据时，MySQL会首先检查唯一索引中是否已经存在相同的键值。如果存在，则更新失败。如果不存在，则MySQL会更新相应的数据行。

CREATE UNIQUE INDEX index_name ON table_name(column_name);

已经存在的列添加唯一索引
ALTER TABLE table_name ADD UNIQUE (column_name);

哈希索引 

MySQL 哈希索引不是B+树而是一种通过哈希函数将索引键映射到索引值的数据结构，它可以快速地进行数据查找和插入，一般用于等值查询。MySQL 使用开放式寻址法和链式法来解决哈希冲突问题。当一个新的索引值插入到哈希表中时，MySQL 会首先使用哈希函数将索引键转化为哈希值，然后将哈希值对哈希表的大小取模，得到该索引值在哈希表中的位置。如果该位置上已经有了其他索引值，则需要使用链式法将新的索引值添加到该位置的链表中。

当使用哈希索引进行查询时，MySQL 首先计算出索引键的哈希值，并在哈希表中找到对应的桶。如果该桶中有指向数据行的指针，则说明该数据行的索引键与查询的索引键相同，MySQL 就可以直接返回该数据行；否则，说明该索引键对应的数据行不存在。

需要注意的是，哈希索引在解决哈希冲突时可能会导致性能问题，因为需要使用链表来存储具有相同哈希值的索引值。因此，在实际应用中，哈希索引通常适用于对静态数据的查询，而对于动态数据的插入和删除操作，B树索引更为常用。

CREATE HASH INDEX index_name
ON table_name (column_name1, column_name2, ...)
WITH (BUCKET_COUNT = n);

其中，index_name 是索引的名称，table_name 是要创建哈希索引的表的名称，(column_name1, column_name2, ...) 是要创建哈希索引的列的名称列表。BUCKET_COUNT 参数指定要创建的桶的数量。

组合索引

假设一个表有两个索引(A）和(A,B,C),SQL: WHERE A =1AND B >2 AND C=3，会走哪个索引？

答案：会走索引 (A,B,C)，因为它包含所有查询中用到的列。而 (A) 索引只包含 A 列，不包含 B 和 C 列，不能满足此查询条件。

组合索引是使用B+树的结构，将多个列的值组合成一个键值，并映射到对应的数据页上。

组合索引的存储方式

组合索引将多个列的值组合成一个键值，然后将这个键值映射到一个对应的数据页上。在存储时，MySQL会将组合索引存储在B+树的结构中。每个节点代表一个索引页，节点中的每个元素代表一个索引项，即包含了索引键值和指向数据页的指针。

组合索引的使用方式

当查询中包含了组合索引的前缀列时，MySQL可以利用索引的结构，快速地定位到对应的数据页，从而提高查询效率。如果查询中包含了组合索引中没有包含的列，MySQL会先通过索引定位到数据页，然后再进行筛选，这样可能会降低查询效率。所以，组合索引的顺序很重要。MySQL会根据联合索引中的第一个列来进行排序和过滤，因此应该将最常用于过滤的列放在组合索引的最前面，以便最大限度地利用索引。

组合索引的优化

为了提高组合索引的效率，可以通过以下方式进行优化：

• 确定合适的索引列

• 调整组合索引的顺序

• 限制组合索引的长度

CREATE INDEX index_name ON table_name (column1, column2, column3, ...);