《高性能MySQL》---- MySQL索引

一、索引的优缺点

优点

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

缺点

1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
2. 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
3. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
4. 对于非常小的表，大部分情况下简单的全表扫描更高效；

二、索引类型（逻辑结构划分）

• 普通索引：仅加速查询

• 唯一索引：加速查询 + 列值唯一（可以有null）

• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

• 全文索引：对文本的内容进行分词，进行搜索

三、索引类型（数据结构划分）

1、B-Tree索引

当人们谈论索引的时候，如果没有特别指明类型，那多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从素引的根节点(图示并未画出)开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值，可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页(不同引·擎的“指针”类型不同)，图5-1中仅绘制了一个节点和其对应的叶子节点，其实在根节点和叶子节点之间可能有很多层节点页。树的深度和表的大小直接相关。

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。可以使用B-Tree索引的查询类型。B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

B-Tree索引的限制

• 如果不是按照索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列
• 如果查询中有某列的范围查询，则其右边所有列都无法使用索引优化查询。例如有查询WHERE last_name=‘Smith’ AND first_name LIKE ‘J%’ AND dob =‘197612-23’，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件。

这些限制都和素引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

B-Tree

B-Tree又叫做多叉平衡查找树。平衡二叉树分支最多有2个，但是这里的B树分支可以有n个。分支多的好处就会形成一个矮胖矮胖的平衡二叉树。每个节点里面含有许多个键-值对。这就是B树。 B树中的每个结点根据实际情况可以包含大量的关键字信息和分支；这样树的深度降低了，这就意味着查找一个元素只要很少结点从外存磁盘中读入内存，很快访问到要查找的数据。

B+Tree(InnoDB的实现)

B+Tree是B树的变体。B+Tree的内部结点并没有指向关键字具体信息的指针，只有在叶子节点才会有每个关键字具体信息，且每个节点内部含有键值的冗余。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

B树和B+树的区别：

1. B+树中只有叶子节点会带有指向记录的指针（ROWID），而B树则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。
2. B+树中所有叶子节点都是通过指针连接在一起，而B树不会。

B+树的优点：

1. 非叶子节点不会带上ROWID，这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。
2. 叶子节点之间通过指针来连接，范围扫描将十分简单，而对于B树来说，则需要在叶子节点和内部节点不停的往返移动。

B树的优点：

1. 对于在内部节点的数据，可直接得到，不必根据叶子节点来定位。

2、哈希索引

哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效生。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code)，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制:

• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
• 哈希索引只支持等值比较查询,包括=、IN()、<=> (注意<>和<=>是不同的操作)。也不支持任何范围查询，例如WHERE price > 100。
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

因为这些限制，哈希索引只适用于某些特定的场合。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引(adaptive hash index)"。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree素引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要，完全可以关闭该功能。

3、全文索引（FULLTEXT索引）

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

4、R-Tree索引

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找。

四、索引策略（物理存储划分）

1、聚簇索引

聚簇索引，并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引(不过,覆盖索引可以模拟多个聚簇索引的情况，本章后面将详细介绍)

InnoDB 的表必须要有聚集索引：如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚集的数据的优点:

• 可以把相关数据保存在一起。
• 数据访问更快。聚簇索引将素引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点:

• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

最后一点可能让人有些疑惑，为什么二级索引需要两次索引查找?

答案在于二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。

这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。这里做了重复的工作：两次B-Tree查找而不是一次进。对于InnoDB，自适应哈希索引能够减少这样的重复工作。

2、普通索引（非聚簇索引）

普通索引也叫二级索引，除聚簇索引外的索引，即非聚簇索引。

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC（多版本控制）的回滚指针以及所有的剩余列，如果主键是一个列前缀索引， InnoDB也会包含完整的主键列和剩下的其他列。

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

五、索引策略的应用

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

最简单的方法是使用AUTO-INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。

最好避免随机的(不连续且值的分布范围非常大)聚簇索引，特别是对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

2、覆盖索引

通常大家都会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是WHERE条件部分。

索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢? 如果一个索引包含(或者说覆盖)所有需要查询的字段的值，我们就称之为“覆盖素引。

覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MysQL只能使用B-Tree素引做覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

3、请看如下示例：

示列引用自：【MySQL】覆盖索引和回表

CREATE TABLE IF NOT EXISTS `user`(
    -> `id` INT UNSIGNED AUTO_INCREMENT,
    -> `name` VARCHAR(60),
    -> `age` TINYINT(4),
    -> PRIMARY KEY (id),
    -> INDEX idx_age (age)
    -> )ENGINE=innodb charset=utf8mb4;
# id 字段是聚簇索引，age 字段是普通索引（二级索引）

insert into user(name,age) values('张三',30);
insert into user(name,age) values('李四',20);
insert into user(name,age) values('王五',40);
insert into user(name,age) values('刘八',10);

mysql> select * from user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张三 |  30 |
|  2 | 李四 |  20 |
|  3 | 王五 |  40 |
|  4 | 刘八 |  10 |
+----+------+-----+
4 rows in set (0.06 sec)

id 是主键，所以是聚簇索引，其叶子节点存储的是对应行记录的数据

age 是普通索引（二级索引），非聚簇索引，其叶子节点存储的是聚簇索引的的值

如果查询条件为主键（聚簇索引），则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。
如：select * from user where id = 1;

如果查询条件为普通索引（非聚簇索引），需要扫描两次B+树，第一次扫描通过普通索引定位到聚簇索引的值，然后第二次扫描通过聚簇索引的值定位到要查找的行记录数据。
如：select * from user where age = 30;

1》先通过普通索引【age=30】定位到主键值 【id=1】

2》再通过聚集索引【id=1】定位到行记录数据

先通过普通索引的值定位到聚簇索引值，在通过聚簇索引的值定位到行记录数据，要通过扫描两次索引B+树，它的性能较扫描一次较低。

如果SQL中涉及的字段，都包含在索引中，那么只需在一颗索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。
例如：select id,age from user where age = 10; 因为age索引的页子节点，包含了id,age两个字段，不用再回表查询。