系列文档参考 MYSQL系列-整体架构介绍

索引类似于书的目录，主要目的是提高数据查询的效率。

索引相关的数据结构

hash

基本概念

哈希（Hash）数据结构是一种通过把关键字映射到哈希表中一个位置来访问记录的数据结构。哈希表通常是一个数组，数组的每个元素称为哈希桶，每个哈希桶中可以存放一个或多个记录。

哈希函数是将关键字映射到哈希桶的过程，它可以将任意长度的输入（例如字符串或数字）映射到固定长度的哈希值。

哈希表的特点是可以快速地插入和查找记录，时间复杂度通常为 O(1)

图片来源前端数据结构--散列表(哈希表)

hask(key)称为hash函数，哈希函数的设计需要考虑关键字的分布和哈希表的大小等因素，否则可能会导致哈希冲突率过高或者哈希桶的利用率过低。常见的散列函数如下：

直接定址法：取关键字的某个线性函数为散列地址：Hash(Key) = A*Key + B
- 优点：简单、均匀（每个值都有一个唯一位置，效率很高，每个都是一次就能找到）
- 缺点：需要事先知道关键字的分布情况
- 使用场景：适合查找数据比较小且连续的情况
除留余数法：除留余数法是将关键字除以某个数得到余数作为哈希值，即 H(key) = key % M，其中 M 是一个质数。
- 优点：使用场景广泛，不受限制
- 缺点：存在哈希冲突，需要解决哈希冲突，哈希冲突越多，效率下降越厉害。
平方取中法：平方取中法是将关键字平方后取中间几位作为哈希值，即 H(key) = 中间几位，其中关键字的平方可以使哈希函数更加散列，减少哈希冲突的概率。
- 使用场景：不知道关键字的分布，而位数又不是很大的情况。
随机数法:选择一个随机函数，取关键字的随机函数值为它的哈希地址，即 Hash(Key) = random(Key)，其中 random 为随机数函数。

优缺点

优点

快速性：哈希函数能够在常数时间内快速计算出哈希值，从而实现快速的数据插入、查询、删除等操作，时间复杂度通常为O(1)。
映射性：哈希函数能够将任意长度的输入映射为固定长度的哈希值，从而实现将大量数据映射到有限的哈希表中进行存储和处理。
冲突检测：哈希函数能够检测到哈希冲突，即多个关键字被映射到同一个哈希桶中的情况，从而能够采取相应的解决措施，如链表法、开放地址法等。
安全性：哈希函数能够保护数据的安全性，如密码哈希函数能够将密码转换为不可逆的哈希值，保护用户密码的安全性。

缺点

哈希冲突：哈希函数可能会产生哈希冲突，即多个关键字被映射到同一个哈希桶中的情况，从而影响哈希表的性能。
哈希函数选择：哈希函数的选择需要考虑多个因素，如关键字的分布、哈希表的大小等，如果选择不当，可能会导致哈希冲突率过高或哈希桶的利用率过低。
冲突解决：哈希冲突的解决需要消耗额外的时间和空间复杂度，如链表法需要额外的链表指针和内存空间。
不支持排序：哈希表不支持快速的排序操作，需要通过其他数据结构来实现排序。

应用场景

java中的hashmap

在JDK8之前，Java中的HashMap采用的是数组和链表的结构来实现
在JDK8中，HashMap的实现方式发生了较大的改变，采用了数组、链表和红黑树的结构来实现。

布隆过滤器

布隆过滤器（Bloom Filter）是一种基于哈希的快速、高效的数据结构，用于判断一个元素是否存在于一个集合中。

它的原理是利用多个哈希函数将输入的元素映射为多个位数组中的位置，并将这些位置设置为1。当需要判断一个元素是否在集合中时，将该元素通过相同的哈希函数映射为多个位数组中的位置，并检查这些位置是否都为1，如果都为1，则该元素可能在集合中，如果存在任意一个位置为0，则该元素肯定不在集合中。

其应用场景如下：
1、大数据去重
2、网页爬虫对 URL 的去重，避免爬取相同的 URL 地址
3、反垃圾邮件，从数十亿个垃圾邮件列表中判断某邮箱是否垃圾邮箱
4、缓存击穿，将已存在的缓存放到布隆中，当黑客访问不存在的缓存时迅速返回避免缓存及数据库挂掉

有序数组

基本概念

有序数组是一种数据结构，它是一个按照大小顺序排列的数组。与普通的数组相比，有序数组的主要优点是可以进行二分查找，以快速地查找特定元素，而不必遍历整个数组。

在有序数组中，元素的插入和删除操作可能比较费时，因为需要维护数组的有序性。

优缺点

适合等值查询和范围查询
不适合频繁的插入和删除操作

应用场景

二分查找
跳表

二叉搜索树

基本概念

二叉搜索树（Binary Search Tree，BST）是一种基于二叉树的数据结构，它具有以下特点：

对于树中的每个节点，其左子树中的所有节点的值都小于该节点的值，而右子树中的所有节点的值都大于该节点的值。
左子树和右子树也都是二叉搜索树。

二叉搜索树的主要优点是可以快速地进行查找、插入、删除操作，时间复杂度为O(log n)，其中n为树中节点的个数。另外，二叉搜索树还可以进行中序遍历，以得到有序的节点序列。

二叉搜索树的缺点是当树的高度较大时，可能会导致查找、插入、删除操作的时间复杂度变为O(n)，因此需要进行平衡操作，保证树的高度不会过大。

B+树

基本概念

B+树是一种多路平衡查找树，具有以下几个特点：

所有数据都存储在叶子节点上，而非内部节点上。
所有叶子节点之间通过指针相连，形成一个有序链表。
内部节点不存储数据，仅用于索引和划分关键字范围。
所有叶子节点的深度相同，可以通过根节点快速定位到任意一个叶子节点。

B+树的主要优点是能够高效地支持范围查询和顺序访问，因为所有叶子节点之间都是通过指针相连的有序链表。另外，B+树还具有良好的磁盘存储性能，因为所有数据都在叶子节点上，可以通过I/O操作快速地读取一整块数据，从而减少磁盘寻道时间。

延伸，相关树概念

二叉树（Binary Tree）

每个节点至多拥有两个子节点的树结构

完美二叉树 Perfect Binary Tree

除了最底层外，其余所有层的节点都被完全填满。在完美二叉树中，叶节点的度为 0 ，其余所有节点的度都为 2 ；若树高度为 ℎ ，则节点总数为 2ℎ+1−1 ，呈现标准的指数级关系

完全二叉树 Complete Binary Tree

只有最底层的节点未被填满，且最底层节点尽量靠左填充

完满二叉树 Full Binary Tree

除了叶节点之外，其余所有节点都有两个子节点

平衡二叉树 Balanced Binary Tree

中任意节点的左子树和右子树的高度之差的绝对值不超过 1

二叉搜索树 Binary Search Tree

对于根节点，左子树中所有节点的值 < 根节点的值 < 右子树中所有节点的值
任意节点的左、右子树也是二叉搜索树，即同样满足条件 1.

AVL 树

既是二叉搜索树也是平衡二叉树，同时满足这两类二叉树的所有性质，因此也被称为「平衡二叉搜索树」
插入或者删除节点需要通过旋转保持树的平衡

红黑树（Red-Black Tree）

红黑树是每个节点都带有颜色属性的二叉查找树，颜色为红色或黑色。在二叉查找树强制一般要求以外，对于任何有效的红黑树我们增加了如下的额外要求：

节点是红色或黑色。
根是黑色。
所有叶子都是黑色（叶子是NIL节点）。
每个红色节点必须有两个黑色的子节点。（或者说从每个叶子到根的所有路径上不能有两个连续的红色节点。）（或者说不存在两个相邻的红色节点，相邻指两个节点是父子关系。）（或者说红色节点的父节点和子节点均是黑色的。）
从任一节点到其每个叶子的所有简单路径都包含相同数目的黑色节点。

B树（B-Tree）

一种多路平衡查找树，它可以在每个节点存储多个关键字，以提高查找效率。

B+树（B+ Tree）

一种基于B树的数据结构，它的所有数据都存储在叶子节点上，而非内部节点上，以方便进行范围查询和顺序访问。

参考二叉树

INNODB引擎使用的数据结构

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。
InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的
每一个索引在 InnoDB 里面对应一棵 B+ 树

为什么使用B+树

支持高效的范围查询
B+树的叶子节点都是有序的，并且通过指针相连，可以很方便地进行范围查询。例如，在一个包含100万条记录的表中，如果需要查询ID在1,000到10,000之间的所有记录，可以通过B+树索引快速地定位到起始位置和结束位置，遍历叶子节点链表即可得到结果。
支持高效的顺序访问
由于B+树的叶子节点都是有序的，并且通过指针相连，可以很方便地进行顺序访问。例如，在一个包含100万条记录的表中，如果需要按照ID从小到大的顺序访问所有记录，可以通过B+树索引快速地定位到起始位置，然后依次遍历叶子节点链表即可得到结果。
支持高效的磁盘存储
B+树的内部节点只存储索引信息，而不存储数据信息，可以减少磁盘存储空间。另外，B+树的叶子节点通过指针相连，可以通过I/O操作快速地读取一整块数据，从而减少磁盘寻道时间，提高存储性能。
支持高效的插入和删除操作
由于B+树的内部节点只存储索引信息，而不存储数据信息，可以减少更新操作的开销。另外，B+树的叶子节点通过指针相连，可以快速地进行插入和删除操作，而不需要进行树的平衡调整。

其他索引结构

MyISAM引擎只支持B+树索引和全文索引，而InnoDB引擎则支持B+树索引、哈希索引、全文索引和空间索引等多种索引类型。

哈希索引

哈希索引只能用于等值查询，适用于索引列的值分布比较均匀的情况。

mysql> CREATE TABLE user_info (
    ->     user_id INT(11),
    ->     name VARCHAR(50),
    ->     age INT(11),
    ->     INDEX idx_user_id USING HASH (user_id)
    -> );
Query OK, 0 rows affected (0.04 sec)
mysql> insert into user_info values(1,'aa',1),(2,'bb',12);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> explain select * from user_info where user_id = '2';
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_info | NULL       | ref  | idx_user_id   | idx_user_id | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

全文索引

全文索引用于对文本数据进行全文搜索，适用于需要进行文本搜索的场景。

mysql> CREATE TABLE article (
    ->     id INT(11),
    ->     title VARCHAR(255),
    ->     content TEXT,
    ->     FULLTEXT idx_content (content)
    -> );
Query OK, 0 rows affected (0.14 sec)
mysql> ALTER TABLE article CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM article WHERE MATCH (content) AGAINST ('just');

全文索引的查询性能可能会受到文本数据长度、查询关键词的数量和查询模式等因素的影响。

空间索引

空间索引用于对具有空间属性的数据进行查询，如地理位置信息、地图数据等。

mysql> CREATE TABLE merchant (
    ->     id INT(11),
    ->     name VARCHAR(255),
    ->     location POINT NOT NULL,
    ->     SPATIAL idx_location (location)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql> ALTER TABLE merchant CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO merchant (id, name, location) VALUES (1, '商家1', ST_GeomFromText('POINT(121.48 31.22)'));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM merchant WHERE ST_Distance_Sphere(location, ST_GeomFromText('POINT(121.48 31.22)')) <= 5000;
+------+---------+---------------------------+
| id   | name    | location                  |
+------+---------+---------------------------+
|    1 | 商家1   |        ▒▒Q▒^^@▒▒▒Q8?@       |
+------+---------+---------------------------+
1 row in set (0.01 sec)

前缀索引

前缀索引是一种特殊的索引类型，它只对索引列的前几个字符进行索引，可以节省索引空间，提高索引效率。


mysql> CREATE TABLE product (
    ->     id INT(11),
    ->     sku_code VARCHAR(50),
    ->     name VARCHAR(255),
    ->     INDEX idx_sku_code (sku_code(6))
    -> );
Query OK, 0 rows affected (0.05 sec)

索引分类

据叶子节点的内容，索引类型分为主键索引和非主键索引

主键索引（聚簇索引）

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）

非主键索引

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

普通索引可重复，唯一索引和主键一样不能重复

唯一索引

普通索引

延伸：基于主键索引和普通索引的查询有什么区别？

基于非主键索引的查询需要多扫描一棵索引树（称之为回表）

延伸：索引维护

页分裂
页合并

自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂

有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高

延伸：索引选择最佳实践

普通索引、唯一索引在查询性能上无差别，主要考虑更新性能。所以，推荐尽量选择普通索引。若所有更新后面，都紧跟对该记录的查询，就该关闭change buffer。其它情况下，change buffer都能提升更新性能。
普通索引和change buffer的配合使用，对数据量大的表的更新优化还是明显的。

在使用机械硬盘时，change buffer收益也很大。所以，当你有“历史数据”库，且出于成本考虑用机械硬盘，应该关注这些表里的索引，尽量用普通索引，把change buffer开大，确保“历史数据”表的数据写性能。

索引优化使用

举例

mysql> create table test (
    -> id int primary key,
    -> cardId int NOT NULL DEFAULT 0,
    -> code varchar(16) NOT NULL DEFAULT '',
    -> index cardId(cardId))
    -> engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> insert into test values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
Query OK, 6 rows affected (0.01 sec)

覆盖索引

覆盖索引（Covering Index）是一种特殊的索引结构，它可以避免数据库进行额外的随机I/O操作，从而提高数据库的查询性能。在覆盖索引中，索引结构包含了所有需要查询的字段，因此数据库可以直接从索引结构中获取查询结果，而无需再次访问磁盘进行数据查找。比如

mysql> select id from test where cardid = 3;
+-----+
| id  |
+-----+
| 300 |
+-----+
1 row in set (0.00 sec)

mysql> explain select id from test where cardid = 3;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | cardId        | cardId | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *  from test where cardid = 3;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | cardId        | cardId | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

"Using index"表示查询使用了覆盖索引

最左匹配

最左匹配原则是指在使用联合索引（Composite Index）进行查询时，索引的左边列会优先匹配查询条件，而右边的列只有在左边列相同时才会匹配。具体来说，最左匹配原则要求查询条件必须包含联合索引的最左边的列，才能利用该索引进行查询优化。比如

mysql> alter table test add index code_cardid(code,cardid);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select id from test where code ='bb';
+-----+
| id  |
+-----+
| 200 |
+-----+
1 row in set (0.01 sec)

mysql> explain select id from test where code ='bb';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | code_cardid   | code_cardid | 18      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

延伸：联合索引的建立是否会影响数据的插入和更新操作？

联合索引的建立会对数据的插入和更新操作产生一定的影响，但具体影响程度取决于多个因素，例如数据的大小、索引的大小、索引的选择性等。

对于数据插入操作，联合索引的建立会增加数据插入的开销。因为每次插入数据时，需要同时更新所有的索引结构，包括联合索引和单列索引。如果索引结构过多或过大，插入操作的时间复杂度可能会增加，从而影响数据库的性能。

索引下推

索引下推（Index Condition Pushdown）是指在MySQL查询优化中，将过滤条件下推到存储引擎层，利用索引结构进行过滤操作，从而减少数据扫描和随机I/O操作，提高查询性能和效率。

具体来说，索引下推可以在执行查询语句时，将查询条件下推到存储引擎层，利用索引结构进行过滤操作，从而减少数据扫描和随机I/O操作。这样可以避免将不必要的数据加载到内存中，减少CPU和内存的开销，提高数据库的整体性能。

mysql> alter table test add column age int(4) not null default 0;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from test where cardid = 8 and age =12;
+-----+--------+------+-----+
| id  | cardId | code | age |
+-----+--------+------+-----+
| 800 |      8 | ac   |  12 |
+-----+--------+------+-----+
1 row in set (0.00 sec)

在 MySQL 5.6 之前，只能一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

详细下推可以参考: 五分钟搞懂MySQL索引下推

延伸

为什么MYSQL会选错索引

MySQL 在决定如何执行查询时，会尝试估算每个可用索引的成本，并选择成本最低的索引来执行查询。但是，在某些情况下，MySQL 可能会选错索引，导致查询性能下降。
导致 MySQL 选错索引的原因：

统计信息不准确

MySQL 通过统计信息来估算每个索引的成本，如果统计信息不准确，就可能导致 MySQL 选错索引。
为了解决这个问题，可以使用ANALYZE TABLE命令来更新统计信息。

索引选择器不准确

MySQL 的索引选择器负责选择最优的索引来执行查询，但是在某些情况下，索引选择器可能会出现错误。例如，当表中包含多个索引时，索引选择器可能会选择错误的索引。
为了解决这个问题，可以使用FORCE INDEX语句来强制 MySQL 使用指定的索引。

查询条件不符合索引特性

MySQL 的不同类型的索引适用于不同类型的查询，如果查询条件不符合索引特性，就可能导致 MySQL 选错索引。例如，如果查询条件包含一个 LIKE 子句，但是索引是一个前缀索引，MySQL 就可能无法使用该索引。
为了解决这个问题，可以调整查询条件或者创建适合查询条件的索引。

索引故障或磁盘故障

如果索引故障或磁盘故障导致索引数据不可用，MySQL 就可能无法选择正确的索引。
为了解决这个问题，可以使用CHECK TABLE命令来检查表的完整性，并使用备份数据来恢复索引数据。

怎么给字符串创建索引?

直接创建完整索引，这样可能比较占用空间；
创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

MYSQL系列-索引介绍和优化

索引相关的数据结构

hash

基本概念

优缺点

优点

缺点

应用场景

java中的hashmap

布隆过滤器

有序数组

基本概念

优缺点

应用场景

二叉搜索树

基本概念

B+树

基本概念

延伸，相关树概念

二叉树（Binary Tree）

完美二叉树 Perfect Binary Tree

完全二叉树 Complete Binary Tree

完满二叉树 Full Binary Tree

平衡二叉树 Balanced Binary Tree

二叉搜索树 Binary Search Tree

AVL 树

红黑树（Red-Black Tree）

B树（B-Tree）

B+树（B+ Tree）

INNODB引擎使用的数据结构

为什么使用B+树

其他索引结构

哈希索引

全文索引

空间索引

前缀索引

索引分类

主键索引（聚簇索引）

非主键索引

唯一索引

普通索引

延伸：基于主键索引和普通索引的查询有什么区别？

延伸：索引维护

延伸：索引选择最佳实践

索引优化使用

覆盖索引

最左匹配

延伸：联合索引的建立是否会影响数据的插入和更新操作？

索引下推

延伸

为什么MYSQL会选错索引

怎么给字符串创建索引?