高性能/索引类型 B-Tree

1. 索引介绍

⏰ 使用索引

1. 在MySQL中，存储引擎用如下的方法使用索引，查询时候先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。如下查询范例：

🍈
SELECT first_name FROM sakila.actor WHERE actor_id =5;

若我们在 actor_id 列上建有索引，则 MySQL 将使用该索引找到 actor_id 为 5 的行，也就是说，MySQL 先在索引上按值进行查找，然后返回所有包含该值的数据行。

2. 索引可以包含一个或者多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为 MySQL 只能高效地使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引大不相同的。

2. 索引的类型

2.1 B-Tree 索引

⏰ 介绍

1. 我们知道在 MySQL 中除非用到特殊的存储引擎特性，我们通常都使用 InnoDB 存储引擎。
2. 当建立索引的时候，若没有指明特别的类型，我们通常所使用的肯定是 B-Tree索引，在 InnoDB 存储引擎中， B-Tree索引使用的是 B+Tree 数据结构来组织数据的。

⏰ 工作原理

1. B-Tree索引通常意味者所有的值都是按照顺序存储的，并且每一个叶子页到根的距离相同。
2. 下图大致反映了 InnoDB 索引的工作方式：
   

B-Tree 能够加快访问速度，因为 ①🌖 存储引擎不再需要进行全表扫描来获取需要的数据，而是从索引的根节点开始，搜索，通过Tree的特性，比较值的大小，向下层遍历，直到叶子节点中找到对应的值。②🌖B-Tree的数据结构（B+Tree）的叶子节点是排好序的，逻辑连续的一系列值，所以很适合查找范围数据和排序。

⏰ 范例

1. 假设有如下数据表：

🍈
CREATE TABLE `people` (
  `last_name` varchar(50) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `dob` date NOT NULL,
  `gender` enum('m','f') NOT NULL,
  KEY `last_name` (`last_name`,`first_name`,`dob`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 此表的索引包含了 last_name ，first_name ， dob 列的值，下图显示了该索引是如何组织索引中数据的存储：
   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZUhMKChY-1607937604408)(https://media.ohbee.cn/mysql-20180826145427-2.jpg)]

索引对多个值进行排序的依据是定义索引时，列的顺序；查看最后2个子节点，两个人的姓和名都一样，则根据出生日期来排列顺序。

⏰ 使用特性

• B-Tree 索引适用于全键值，键值返回或键前缀查找。其中键前缀查找只适用于🌖最左前缀查找。B-Tree 索引都是由左开始。

1. 全值匹配：全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为 Cuba Allen、出生于 1960-01-01的人。
2. 匹配最左前缀：只使用第一列索引。前面提到的索引可用于查找所有姓 Allen 的人。
3. 匹配列前缀：可以只匹配某一列的值的开头部分。例如前面提到的索引可用于查找所有以 J 开头的姓的人。这里也只使用了索引的第一列。
4. 匹配范围值：使用索引的第一列匹配范围。例如前面提到的索引可以查找姓 Allen和Barrymore之间的人。
5. 精确匹配某一列并范围匹配另外一列：此索引页可以用于查找所有姓为 Allen，并且名字是字母 K 开头（比如 Kim、Karl等）的人。既第一列 last_name全匹配，第二列 first_name 范围匹配。
6. 只访问索引的查询：若查询需要访问的列都在索引中，则只需要访问索引，而无须访问数据行。这种技巧称之为覆盖索引的优化。

① 因为索引树的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的 ORDER BY 操作（按顺序查找）。② 一般来说，B-Tree可以按照某种方式查找到值，也可以按这种方式排序。③ 所以，若 ORDER BY 子句满足上述列出的几种查询类型，则排序的时候也可以使用索引。

⏰ 使用限制

1. 限制一：如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子的索引无法用于查找名字为 Bill 的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。
2. 限制二：不能跳过索引的列。也就是说，前面所述的索引无法用于查找姓为 Smith 并且在某个特定生日出生的人。如果不指定名（first_name），则MySQL只能使用索引的第一列。
3. 限制三：如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。例如：有查询WHERE last_name='Smith' AND first_name LIKE 'J%' AND dob= '1976-12-23'，这个查询只能使用索引的前两列，因为这里LIKE是一个返回条件。如果范围查询列的数量有限，那么可以通过用多个等于条件来替代范围条件。

⏰ 要点

1. 通过上述的描述，可以看出索引列的顺序是多么的重要，这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同列但顺序不同的索引来满足不同类型的查询需求。
2. 也有些限制不是 B-Tree 本身导致的，而是 MySQL 优化器和存储引擎使用索引的方式导致的，这部分限制可能在未来的版本中就不再是限制了。