高性能MySQL-创建高性能索引

索引是存储引擎中用于快速找到记录的一种数据结构。索引优化应该是对查询性能优化最有效的手段。

1 索引基础

索引可以包含一个或多个列（联合索引）的值。如果索引包含多个列，其列的顺序非常重要，因为MySql只能高效地使用索引的最左前缀列。

1.1 索引的类型

Mysql中的索引实现是在存储引擎层中实现的，而不是服务层，因此没有统一的索引标准（不同的存储引擎的工作方式不一样）

1.1.1 B-Tree索引

此处的B-Tree是泛指，例如InnoDB实际使用的B+Tree；NDB实际使用的T-Tree。 大多数MySql引擎都使用这种索引。

B-Tree索引的其特征如下：

• 所有的值都是按顺序存储的。
• 每个叶子页到根的距离相同。

下图为B-Tree索引的抽象表示：

其中，每个节点表示一个页，对于InnoDB存储引擎的逻辑页大小为16k。

示例：假设有一个联合索引的表，如下：

create table People (
    `id` int unsigned not null auto_increment comment '主键id',
    `last_name` varchar(50) not null default '' comment '姓氏',
    `first_name` varchar(50) not null default '' comment '名称',
    `dob` date not null comment '生日',
    `gender` enum('m','f') not null comment '性别',
    primary key (`id`),
    key `idx_name_dob`(`last_name`, `first_name`, `dob`)
) engine=InnoDB default charset=utf8mb4 comment='用户信息表'

对于表中的普通联合索引，其组织数据的存储结构如下：

注：

• ①、索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。

• ②、图中最后两个条目的人名都一样，其排序是使用出生日期进行排序的。

B-Tree适应哪些查询类型？

• 全键值查询
• 键值范围查询
• 键前缀查询（只适用于最左前缀查询）

联合索引能适用于哪些查询类型呢？

• 全值匹配：和索引中的所有列进行匹配；
• 匹配最左前缀：例如，使用索引中的第一列，或者第一及第二列；
• 匹配列前缀：匹配某一列的值的开头部分，如查找示例所有姓为J开头的人；
• 匹配范围值：如查找示例中姓氏在Allen和Barrymore之间的人；
• 精确匹配某一列并范围匹配另一列：例如示例中，查询所有姓氏为Allen，并且名字是字母K开头的人，即第一列全值匹配，第二列范围匹配；
• 只访问索引的查询：B-Tree通常可以支持只访问索引，而无需访问数据行。

注：由于B-Tree索引树中的节点是有序的，因此该索引还可以用于查询ORDER BY操作（排序）。

B-Tree索引有哪些限制（缺点）？

• ①、如果不是按照索引的最左列开始查找，则无法使用索引。
• ②、不能跳过索引中的列。
• ③、如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找（即全表扫描）。

为什么MySQL数据库大部分索引结构要使用B+Tree？

1.1.2 哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算出一个哈希码，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

Mysql中只有Memory引擎显式支持且默认支持哈希索引。

示例：

# 建表
create table `testhash` (
    `fname` varchar(50) not null,
    `lname` varchar(50) not null,
    key using hash(fname)
)engine=memory;

# 添加数据
insert into `testhash` (`fname`,`lname`) 
values 
    ('Arjen','Lentz'),
    ('Baron', 'Schwartz'),
    ('Peter', 'Zartsev'),
    ('Vadim', 'Tkachenko');

哈希索引的优点：

• ①、索引自身只需存储对应的哈希值，因此索引结构紧凑。
• ②、查找速度非常快。

哈希索引的限制(缺点)：

• ①、不能使用索引中的值来直接读取行（哈希索引只包含哈希值和行指针，而不存储字段值）
• ②、哈希索引数据不是按照索引值顺序存储的，因此无法用于排序。
• ③、哈希索引不支持部分索引匹配查找。（哈希索引使用是使用索引列的全部内容计算哈希值的）
• ④、哈希索引只支持等值比较查询（=，IN(), <=>），也不支持任何的范围查询。
• ⑤、当有很多哈希冲突时，会导致访问速度变慢，且维护成本代价也会很高。

哈希索引比树型索引更快，为什么MySQL的索引结构要设计成B-Tree结构？

• 对于哈希索引，其增删改查的平均时间复杂度为O(1)；

• 而Tree型索引，其增删改查的平均时间复杂度为O(lg(n))；

• 对于单行数据的读写，哈希索引均比Tree型索引块，但是对于某些特殊的查询SQL，例如分组、排序、比较等情况，哈希索引的时间复杂度会退化为O(n)，而Tree索引因为其有序的特性，能依旧保持O(lg(n))的高效率。

注：InnoDB存储引擎不支持哈希索引。

哈希索引的适用场景举例？

在数据仓库的应用中有一种经典的“星型”schema，需要关联很多查找表，哈希索引就非常适合查找表的需求。

1.1.2.1 InnoDB的自适应哈希索引

InnoDB会将某些使用非常频繁的索引在内存中基于B-Tree索引之上再创建一个哈希索引。这是一个完全自动、内部的行为，用于无法控制或配置，如有必要，可关闭该功能。

1.1.2.2 创建自定义哈希索引

思路：在B-Tree基础上创建一个伪哈希索引。需要做的就是在查询的WHERE字句中手动指定使用的哈希函数。（实例见高性能MySQL P156），为避免冲突问题，必须在WHERE条件中带入哈希值和对应的列值。

示例：需要存储大量的URL，并根据URL进行搜索查找，若使用B-Tree来存储URL，存储的内容就会很大，因为URL本身很长。因此可以新增一个被索引的url_crc列，使用crc32做哈希，然后查询时使用以下方式查询：

select id from url where url="http://www/mysql.com" and url_crc=crc32("http://www/mysql.com");

这样做的优点是性能非常高，但是需要维护哈希值（维护方式可以手动维护，即程序控制，还可以通过触发器维护（触发器示例看参考资料1P149））。

1.1.3 空间数据索引（R-Tree）

MyISAM引擎支持空间索引，可以样做地理数据存储。该索引可以从所有维度来查询数据。必须使用MySQL的GIS相关函数来维护数据。MySQL对GIS支持不完善，不推荐使用此特性。

开源关系数据库系统对GIS的解决方案做的比较好的是：PostgreSQL的PostGIS

1.1.4 全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。

2 索引的优点

索引可以让服务器快速定位指定位置，根据创建索引的数据结构不同，索引还有一些其他的附加作用。例如B-Tree索引，按照顺序存储数据，索引可以用于ORDER BY 和GROUP BY操作。

应用索引有哪些优点？

• 减少服务器需要扫描的数据量
• 帮助服务器避免排序和临时表
• 将随机I/O变为顺序I/O

3 高性能的索引策略

3.1 独立的列

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

示例：

# 下面的这个查询无法使用actor_id列的索引。
select actor_id from sakila.actor where actor_id + 1 = 5;

# 优化后的查询
select actor_id from sakila.actor where actor_id = 4;

3.2 前缀索引和索引选择性

当索引很长字符列时，会让索引变得大且慢，解决方法：

• ①、模拟哈希索引
• ②、前缀索引+索引的选择

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

索引的选择性：指不重复的索引值和数据表的记录总数的比值。索引的选择性索高则查询的效率越高。

示例：创建前缀索引

alter table sakila.city_demo add key (city(7))

前缀索引的优缺点分别是什么？

• 优点：使索引能更小、更快。
• 缺点：Mysql无法使用前缀索引做ORDERY BY 和GROUP BY，也无法使用前缀索引做覆盖扫描。

前缀索引常见的应用场景：针对很长的十六进制唯一ID使用前缀索引。

既然有前缀索引，那么有没有后缀索引呢？

其实是有后缀索引的，但MySQL原生不支持反向索引，但可以把字符串反转后存储，并基于此建立前缀索引。

3.3 多列索引

3.4 选择合适的索引顺序

正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等字句的查询。

通常来说，当不需要考虑排序或分组时，将选择性高的列放到索引最前列是很好的。

3.5 聚簇索引

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

聚簇索引的数据行实际存放在索引的叶子页（leaf page），术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。（一个表只会有一个聚簇索引）

聚簇索引中的记录存放示例：

对于InnoDB，通过主键聚集数据，若没有主键，InnoDB会选择一个唯一的非空索引代替，若没有唯一非空索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引的优点？

• 可以将数据保存在一起。例如试下电子邮箱时，可以根据用户ID来聚集数据，这样只需要从此磁盘读取少数的数据页就能获取某个用户的全部邮件，若没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
• 数据访问更快；
• 使用覆盖索引扫描的查询可以直接使用页结点中的主键值。

聚簇索引的缺点？

• 聚簇索引最大限度地提高了I/O密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没有那么重要了，聚簇索引也就没有什么优势了。
• 插入的速度严重依赖于插入的顺序。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个更新的行移动到新的位置。
• 基于聚簇索引的表再插入新行，或者主键被更新告知需要移动行的时候，可能面临“页分裂”的问题。
• 可能导致全表扫描变慢，尤其是行比较稀疏或者由于页分裂告知数据存储不连续的时候。
• 二级索引可能比想象的要更大，因为二级索引的叶子节点包含了引用行的主键列。
• 二级索引访问需要两次索引查询，而不是一次。

InnoDB和MyISAM保存数据和索引的区别？

在InnoDB表时应该尽可能地按主键顺序插入数据，并且尽可能使用单调增加的聚簇键的值来插入新行。

那么顺序的主键有不适用的时候吗？是什么时候呢？

对于高并发负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”，因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是auto_increment锁机制。

3.6 覆盖索引

定义：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称为覆盖索引。

优点：

• ①、索引条目远小于数据行大小，此时Mysql会极大的减少数据访问量。
• ②、索引按照列值顺序存储（至少单页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行的数据的I/O要少得多。
• ③、一些存储索引（如：MyISAM）在内存中值缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要系统调用。这可能会导致严重的性能问题，尤其是那桐调用占了数据访问中最大开销的场景。
• ④、InnoDB是聚簇索引，覆盖索引对InnoDB表特别有用，使用覆盖索引可以避免对主键索引的二次查询。

（注：不是所有类型的索引都可以成为覆盖索引，覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引）

如何知道一个查询被索引覆盖？

在EXPLAIN的EXtra列可以看到“Using index”的信息。

3.7 使用索引扫描来做排序

MySQL生成有序结果的方式有哪些？

• ①、排序操作。
• ②、索引顺序扫描。

为什么按索引顺序读取数据的速度通常要比顺序地全表扫描慢？

扫描索引本身是很快的，但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本上都是随机I/O，因此会比顺序地全表扫描慢，尤其是在I/O密集型工作负载时。

MySQL可以使用同一个索引既满足排序，又用于查找行，因此，设计索引时应该尽可能的同事满足索引和排序这两种任务。只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（正序或倒序）都一样时，MySQL才能使用索引来对接过做排序。

（注：若多表关联查询，只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。）

特殊情况：有一种情况ORDER BY子句不满足索引的最左前缀的要求，就是前导列为常量的时候，如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以“弥补”索引的不足。

3.8 压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能，默认只压缩字符串，但通过参数设置也可以对整数做压缩。`

MyISAM压缩每个索引块的方法是怎么样的？

先完全保存索引块中的第一个值，然后将其他值和第一个值比较，得到相同前缀的字节数和剩余不同后缀部分，把这部分存储起来即可。

示例：索引块中第一个值是“perform”，第二个值为“performance”，那么第二个指的前缀压缩后存储的值类似于“7，ance”这样的形式。

注：MyISAM对行指针也采用前缀索引方式。

压缩（前缀压缩）索引的优缺点？

• 优点：使用更少的空间；
• 缺点：某些操作可能更慢，比如，每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而是只能从头开始扫描。倒序扫描也支持得不是很好。

在CREATE TABLE语句中使用PACK_KEYS参数来控制索引压缩的方式。

3.9 冗余和重复索引

MySQL允许在相同列上创建多个索引，无论有意还是无意。

重复索引：指在相同的列上按照相同的顺序创建相同类型的索引。应该避免这样创建重复索引！

冗余索引：冗余索引与重复索引有些许不同，比如，已有联合索引（A，B），若在创建索引A，则算是冗余索引。

3.10 未使用的索引

怎样检查那些索引未使用到？

• ①、在Percona Server或者MariaDB中先打开userstates服务器变量（默认是关闭的），然后让服务器正常运行一段时间，再通过查询INFORMATION_SCHEMA.INDEX_STATISTICS就能查到每个索引的使用频率。
• ②、使用Percona Toolkit中pt-index-usage，该工具可以读取查询日志，并对日志中的每条查询进行EXPLAIN操作，然后打印出关于索引和查询的报告。

3.11 索引和锁

索引可以让查询锁定更少的行。InnoDB只有在访问行的时候才会对其加锁，而索引能够减少innoDB访问的行数，从而减少锁的数量。（仅在InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。）

InnoDB在二级索引上使用共享锁，但访问主键索引需要排他锁。

4 索引案例

案例描述：

假设要涉及一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色等等。网站必须支持上面这些特征的各种组合来搜索用户，还必须允许更具用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。

第一件要考虑的事情是：需要使用索引来排序，还是先检索数据再排序。

4.1 支持多种过滤条件

分析哪些列拥有很多不同的取值，哪些列在WHERE子句中出现的最频繁。通常在有更多不同的值的创建索引的选择性会更好。

根据实际业务场景，country列的选择性通常不高，sex列的选择性肯定很低，但是他们在很多查询中都会用到。因此建议在创建不同组合索引的时候，将（sex, country）列作为前缀，其原因如下：

①、几乎所有查询都会用到sex列（若对sex列没有限制，可以通过一些技巧绕过最左前缀限制）。

技巧：若某个查询不限制性别，那么可以通过在查询条件中新增AND SEX IN(‘m’,’f’)来让MySQL选择该索引。

②、考虑常见的where条件的组合，了解哪些组合在没有合适的索引情况下会很慢。(sex,country,age)上建立索引。

对于生僻的搜索条件，及时他们选择性高，使用不频繁，可以忽略他们。

4.2 避免多个范围查询

对于范围查询，MySQL无法使用联合查询后面的索引。

假设有一个last_online列，并希望通过以下的查询显示在过去几周上线过的用户：

where eye_color in ('brown','blue','hazel')
    and hair_color in ('black', 'red', 'blonde', 'brown')
    and sex in ('M','F')
    and last_online > DATE_SUB(NOW(), INTERVAL 7 DAY)
    and age between 18 and 25

这个查询中，他有两个范围查询条件，last_online和age列，MySQL可以使用last_online列索引或者age列索引，但无法同时使用他们。

4.3 优化排序

使用文件排序对小数据集是很快的，但如果一个查询匹配的结果有上白万行会怎样？

示例：高效地利用(sex,rating)索引进行排序和分页？

利用延迟关联策略，通过覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。

select <cols> from profiles inner join (
    select <primary key cols> from profiles
    where x.sex='M' order by rating limt 1000000, 10
) as x using(<primary key cols>);

5 维护索引和表

维护表的目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

5.1 找到并修复损坏的表

通过以下命令可以检查表是否发生了损坏：

check table

通过以下命令可以修复损坏的表：

repair table

注：以上两个命令并不是所有存储引擎都支持。

若存储引擎不支持，可以通过一个不做任何操作的alter操作来重建表，如下示例所示：

alter table innodb_tb1 engine=innodb;

通常来说InnoDB引擎的表不容易损坏，若发生损坏，一般要么是数据库的硬件问题，例如内存或磁盘问题，要么是由于数据库管理员的错误，例如，在MySQL外部操作了数据文件（有可能）或者InnoDB本身的缺陷（不太可能）。

5.2 更新索引的统计信息

MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息：

• record_in_range()：通过向存储引擎传入两个边界值获取这个范围大概有多少条记录。
• info()：返回各种类型的数据，包括索引的基数（每个键值有多少条记录）

若存储引擎向优化器提供的扫描行数信息不准确或者执行计划本身太复杂，以至于无法准确地获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。

MySQL优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。

通过以下命令来生成统计信息解决表没有统计信息或者统计信息不准确的情况。

analyze table

通过以下命令查看索引的基数：

show index from <table_name>

5.3 减少索引和数据的碎片

见参考资料1 P191

6 总结

针对B-Tree索引，在选择索引和编写利用这些索引的查询时，需要记住以下三个原则：

• ①、单行访问是很慢的。特别是在机械硬盘存储中。最好读取的块中能尽可能多的包含所需的行。
• ②、按顺序访问范围数据是很快的，其原因是：第一，顺序I/O比随机I/O快很多。第二，服务器按顺序读取数据就不需要额外的排序操作。
• ③、覆盖索引是很快。覆盖索引避免了回表操作。

如何判断一个系统创建的索引时合理的呢？

• ①、按响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器最大压力的查询。
• ②、检查这些查询的schema、SQL和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用了随机I/O访问数据，或者有太多徽标查询那些不在索引中的列的操作。

---

参考资料：

1 高性能MySQL_第3版 P177