MySQL-InnoDB索引简介

《高性能MySQL》

创建高性能索引

索引（在MySQL中也叫做“键key”）是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能。

总结一句话：用好了非常好，用不好非常不好。

索引基础

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

索引的类型

MySQL索引是在存储引擎层实现的，没有统一标准，不同存储引擎的索引的工作方式并不一样。

支持的索引类型

B-Tree索引

InnoDB使用B+Tree。

存储引擎以不同的方式使用B-Tree索引，性能各有不同。MyISAM使用前缀压缩技术使索引更小，InnoDB按照原数据格式进行存储。

B-Tree索引能够加快访问数据的速度，存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。

B-Tree对索引列是按顺序组织存储的，所以很适合查找范围数据。

可以使用B-Tree索引的查询类型。B-Tree索引适用于全键值，键值范围或前缀查找。前缀查找只适用于根据最左前缀的查找。对如下类型的查询有效。

全值匹配

和索引中的所有列进行匹配。可用于查询姓名为Cuba Allen、出生于1960-01-01的人

匹配最左前缀

查找所有姓为Allen的人，只用了索引的第一列。

匹配列前缀

只匹配某一列的值的开头部分。例如查找所有以J开头的姓的人。

匹配范围值

某一范围值

精确匹配某一列并范围匹配另外一列

查找姓为Allen，名字是字母K开头。第一列全匹配，第二列范围匹配。

只访问索引的查询

只需要访问索引，无需访问数据行，称为覆盖索引。

下面是一些关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列。
• 如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查找。

索引的优点

索引可以让服务器快速地定位到表的指定位置。

最常见的B-Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY 和GROUP BY操作。因数据有序，所以会将相关的列值都存储在一起。索引存储了实际的值，所以某些查询只使用索引就能够完成全部查询（覆盖索引）。总结以下优点：

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器免排序和临时表。
3. 索引可以将随机I/O变成顺序I/O。

索引不是最好的工具，只有当索引帮助存储引擎快速找到记录带来的好处大于其带来的额外工作时，索引才是有效的。

高性能的索引策略

独立的列

索引列不能是表达式的一部分，也不能是函数的参数。

前缀索引和索引选择性

前缀索引：索引列开始的部分字符。大大节省索引空间，提高索引效率。但会降低索引选择性。

BLOB、TEXT或很长的varchar类型的列，必须使用前缀索引。

前缀索引是一种能使索引更小，更快的有效方法，缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY, 也无法使用前缀索引做覆盖扫描。

多列索引

在多个列上建立单独的索引大部分情况下并不能提高MySQL的查询性能，索引合并策略一定程度上可以使用表上的多个单列索引来定位指定的行。

索引合并算法有三种：OR条件的联合(union),AND条件的相交(intersection)，组合前两种情况的联合及相交。

索引合并策略有时候是一种优化的结果，更多是说明了表上的索引建的很糟糕：

• 多个索引做相交操作时，通常需要一个包含所有相关列的多列索引，而不是单列索引。
• 多个索引做联合时，通常消耗大量CPU和内存资源在算法的缓存，排序和合并操作上。
• 更重要的是优化器不会把这些计算到查询成本中

选择合适的索引列顺序

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列。

聚簇索引

聚簇索引是一种数据存储方式，数据存放在索引的叶子页。

因无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

优点：

• 把相关数据保存在一起
• 数据访问更快
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

主键索引（聚簇索引）与非主键索引（非聚簇索引）

CREATE TABLE layout_ test (
col1 int NOT NULL,
col2 int NOT NULL,
PRIMARY KEY(col1),
KEY(col2)
);

该图显示了整个表，而不只是索引。

还有一点和MyISAM的不同是，InnoDB的二级索引和聚簇索引很不相同。InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB 在移动行时无须更新二级索引中的这个“指针”。

二级索引访问需要两次索引查找，因存储的是“主键值”要进行回表操作。

图5-9是描述InnoDB和MyISAM如何存放表的抽象图。从图5-9中可以很容易看出InnoDB和MyISAM保存数据和索引的区别。

https://juejin.im/post/5c9f1707e51d45467a0e9ce4

聚簇索引指的是数据行和相邻的键值紧凑的存储在一起。也就是数据行实际上是存储在索引的叶子页中。

聚簇索引（主键索引）

数据按照id的大小排序，对应的索引会包含该索引的整行数据。

非聚簇索引（非主键索引）

索引以age排序，和主键索引不同的是，年龄索引对应的是id。

如果我建表的时候没有指定主键的话，索引结构又是如何的呢？其实在InnoDB中，如果没有定义主键，那么他会选择一个唯一的非空索引代替。如果没有这样的索引，那么他会隐式的定义一个主键来作为聚簇索引。所以无论你是否设置主键，InnoDB还是会帮你满足以上图的形式来索引数据

查询

select * from person where ID = 6,因为直接使用的是主键ID查询，所以就会用主键索引，由于主键索引直接关联了整行所有数据，所以，引擎只要执行一次就能查询出结果。

select * from person where age = 18

上述语句会走age的普通索引，索引先根据age搜索等于18的索引记录，找到ID=10的记录，然后再到主键索引搜索一次，然后拿出需要查询的数据。

从普通索引查出主键索引，然后查询出数据的过程叫做回表。由于回表需要多执行一次查询，这也是为什么主键索引要比普通索引要快的原因

优点：

• 把相关数据保存在一起
• 数据访问更快
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点：

• 插入速度严重依赖插入顺序。
• 更新聚簇索引代价很高。
• 插入新行，或移动行的时候，会面临“页分裂”问题
• 可能导致全表扫描变慢。
• 二级索引（非聚簇索引）可能更大，二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找。一次是找到主键，一次根据主键索引查找。

在InnoDB表中按主键顺序插入行

如果没有数据需要聚集，可以定义一个代理主键，设置为AUTO_INCREMENT自增。

避免随机的聚簇索引，使用UUID作为聚簇索引就很糟糕。UUID主键行插入数据花费时间更长，索引占用空间更大。

覆盖索引

如果一个索引包含（或覆盖）所需要查询的字段的值，称为覆盖索引。能极大提高性能。

查询只需要扫描索引无需回表，好处：

• 极大减少数据访问量
• 索引按照列值顺序存储，I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

大多数存储引擎中，覆盖索引只能覆盖访问索引中部分列的查询。

索引扫描排序

排序：通过排序操作；按索引顺序扫描

EXPLAIN中type为“index”，表示用索引扫描排序。

只有当索引的列顺序和ORDER BY字句的顺序完全一致，所有列的排序方向（倒叙或正序）都一样时，MySQL才能够使用索引来对结果做排序。如果查询需要关联多张表，只有ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。