【数据库笔记】高性能MySQL：chapter 5 创建高性能的索引

---

前置知识

• 持久化（Persistence）
  即把数据（如内存中的对象）保存到可永久保存的存储设备中（如磁盘）。持久化的主要应用是将内存中的数据存储在关系型的数据库中，当然也可以存储在磁盘文件中、XML数据文件中等等。

---

• 持久层（Persistence Layer)
  即专注于实现数据持久化应用领域的某个特定系统的一个逻辑层面，将数据使用者和数据实体相关联。

---

• 对象关系映射（Object—Relational Mapping，简称ORM）
  是一种为了解决面向对象与面向关系数据库存在的互不匹配的现象的技术；
  简单的说，ORM 是通过使用描述对象和数据库之间映射的元数据，将java 程序中的对象自动持久化到关系数据库中；
  本质上就是将数据从一种形式转换到另外一种形式。
  这样，我们在具体的操作数据库的时候，就不需要再去和复杂的SQL语句打交道，只要像平时操作对象一样操作它就可以了 。

---

5.0 引言

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

索引对于良好的性能非常关键。数据量越大时，索引对性能的影响也越重要，好的索引可以将查询性能提高几个数量级，但是在数据量很大时，糟糕的索引也会使MySQL的性能急剧的下降。

除非特别说明，本章假设使用的都是传统的硬盘驱动器。固态硬盘驱动器有着完全不同的性能特性，本书将对此进行详细的描述。然而即使是固态硬盘，索引的原则依然成立，只是那些需要尽量避免的糟糕索引，对于固态硬盘的影响没有传统硬盘那么糟糕。

索引优化应该是对查询性能优化最有效的手段！能够轻易将性能提高几个数量级，“最优”的索引有时比一个“好的”的索引要好两个数量级。

创建一个真正的“最优索引”，经常需要重写查询。

5.1 索引基础

最简单的理解：看一本书的“索引”部分，想在书里找某个特定主题，一般会先看“索引”，找到对应的页码。

在MySQL中，存储引擎也用类似的方法使用索引，先在索引中找到对应的值，然后根据这个索引记录找到对应的数据行。

select first_name 
from sakila.actor 
where actor_id=5

如果在actor_id上建有索引，则MySQL将以actor_id为目录，使用该索引找到actor_id=5的行（主题）。

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引最左前缀列。

创建一个包含两个列的索引，与创建两个只包含一列的索引，二者是大不相同的。

如果使用的是ORM，是否还需关心索引？
是的。
ORM工具在多数时候能够生产符合逻辑的、合法的查询。
无论是多么复杂的ORM工具，在精妙和复杂的“索引”面前都是浮云。

1. 索引的类型

索引有很多类型，可以为不同场景提供更好的性能。在MySQL中，索引是在存储引擎层实现。

因此，并没有统一的索引标准：不同引擎的索引工作方式并不一样，即使是多个存储引擎支持同一种索引，其底层实现也可能不同。

---

B-Tree 索引

【数据库笔记】B树、B-树、B+树、B*树详解

如果没有特别指明类型，那多半说的是B-Tree 索引，它使用 B-Tree 数据结构来存储数据（实际上很多存储引擎使用的是B+Tree ，即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历）。

大多数 MySQL 引擎都支持这种索引。Archive 引擎是一个例外：5.1 之前 Archive 不支持任何索引，直到 5.1才开始支持单个自增列（AUTO_INCREENT）的索引。

我们使用术语 “B-Tree”，是因为 MySQL 在 CREATE TABLE
和其他语句中也使用该关键字。不过，底层的存储引擎也可能使用不同的存储结构，例如，NDB 集群存储引擎内部实际上使用了 T-Tree
结构存储这种索引，即使其名字是 BTREE；InnoDB 则使用的是
B+Tree，各种数据结构和算法的变种不在本书的讨论范围之内。

存储引擎以不同的方式使用 B-Tree 索引，性能也各有不同，各有优劣。

• MyISAM 使用前缀压缩技术使得索引更小，但 InnoDB 则按照原数据格式进行存储；
• MyISAM 索引通过数据的物理位置引用被索引的行，而 InnoDB 则根据主键引用被索引的行。

B-Tree 通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。图 5-1 展示了 B-Tree 索引的抽象表示，大致反映了 InnoDB 索引是如何工作的。MyISAM 使用的结构有所不同，但基本思想是类似的。

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

根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进人下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

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

B-Tree 对索引列是顺序组织存储的，所以很适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像 “找出所有以Ⅰ到 K 开头的名字” 这样的查找效率会非常高。

假设有如下数据表：

CREATE TABLE People (
    last_name   varchar(32)     not null,
    first_name  varchar(32)     not null,
    dob         date            not null,
    gender      enum ('m', 'f') not null,
    key(last_name, first_name, dob)
);

对于表中的每一行数据，索引中包含了 last_name、first_name 和 dob 列的值，图 5-2
显示了该索引是如何组织数据的存储的。

请注意，索引对多个值进行排序的依据是 CREATE TABLE
语句中定义索引时列的顺序。看一下最后两个条目，两个人的姓和名都一样，则根据他们的出生日期来排列顺序。

---

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

• 全值匹配：指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为 Cuba Allen、出生于 1960-01-01 的人。

• 匹配最左前缀：前面提到的索引可用于查找所有姓为 Allen 的人，即只使用索引的第一列。

• 匹配列前缀：也可以只匹配某一列的值的开头部分。例如前面提到的索引可用于查找所有以 J 开头的姓的人。这里也只使用了索引的第一列。

• 匹配范围值：例如前面提到的索引可用于查找姓在 Allen 和 Barrymore 之间的人。这里也只使用了索引的第一列。

• 精确匹配某一列并范围匹配另外一列：前面提到的索引也可用于查找所有姓为 Allen，并且名字是字母 K 开头（比如 Kim、Karl 等）的人。即第一列 last_name 全匹配，第二列 first_name 范围匹配。

• 只访问索引的查询：B-Tree 通常可以支持 “只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。后面我们将单独讨论这种 “覆盖索引” 的优化。

---

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的 ORDER BY 操作（按顺序查找)。一般来说，如果 B-Tree 可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果 ORDER BY 子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

---

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

• 如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为 Bill 的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。
• 不能跳过索引中的列。也就是说，前面所述的索引无法用于查找姓为 Smith 并且在某个特定日期出生的人。如果不指定名(first_name)，则 MySQL 只能使用索引的第一列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询 WHERE last_name='Smith’ AND first_name LIKE ‘J%’ AND dob = ‘1976-12-23’，这个查询只能使用索引的前两列，因为这里 LIKE是一个范围条件（但是服务器可以把其余列用于其他目的)。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。在本章的索引案例学习部分，我们将演示一个详细的案例。

到这里读者应该可以明白，前面提到的索引列的顺序是多么的重要：这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

也有些限制并不是 B-Tree 本身导致的，而是MySQL
优化器和存储引擎使用索引的方式导致的，这部分限制在未来的版本中可能就不再是限制了。

---

哈希索引

哈希索引（hash index）：基于哈希表实现，只有精确匹配索引所有列的查询才有效。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在 MySQL 中，只有Memory引擎显式支持哈希索引。这也是 Memory 引擎表的默认索引类型，Memory 引擎同时也支持 B-Tree 索引。值得一提的是，Memory 引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

---

假如有如下表：

CREATE TABLE testhash (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    KEY USING HASH(fname)
)ENGINE=MEMORY;

表中包含如下数据：

mysql> SELECT * FROM testhash;
+-------+-----------+
| fname | lname     |
+-------+-----------+
| Arjen | Lentz     |
| Baron | Schwartz  |
| Peter | Zaitsev   |
| Vadim | Tkachenko |
+-------+-----------+

假设索引使用假想的哈希函数 f()，它返回下面的值（都是示例数据，非真实数据)：

f('Arjen')= 2323
f('Baron')= 7437
f('Peter')= 8784
f('vadim')= 2458

则哈希索引的数据结构如下：

槽(Slot)	值(Value)
2323	指向第1行的指针
2458	指向第4行的指针
7437	指向第2行的指针
8784	指向第3行的指针

注意每个槽的编号是顺序的，但是数据行不是。

SELECT lname FROM testhash WHERE fname='Peter';

分析：MySQL 先计算 ‘Peter’ 的哈希值，并使用该值寻找对应的记录指针。因为 f(‘Peter’)=8784，所以 MySQL 在索引中查找 8784，可以找到指向第 3 行的指针，最后一步是比较第三行的值是否为 ‘Peter’，以确保就是要査找的行。

---

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

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配査找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列（A,B）上建立哈希索引，如果查询只有数据列 A，则无法使用该索引。
• 哈希索引只支持等值比较査询，包括 =、IN()、<=> （注意 <> 和 <=> 是不同的操作）。也不支持任何范围査询，例如 WHERE price > 100。
• 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

因为这些限制，哈希索引只适用于某些特定的场合。一旦适合哈希索引，则它带来的性能提升将非常显著。举个例子，在数据仓库应用中有一种经典的 “星型” schema，需要关联很多查找表，哈希索引就非常适合查找表的需求。

除了 Memory 引擎外，NDB 集群引擎也支持唯一哈希索引，且在 NDB 集群引擎中作用非常特殊，但这不属于本书的范围。

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

---

空间数据索引（R-Tree）

MyISAM 表支持空间索引，可以用作地理数据存储。和 B-TREE 索引不同，这类索引无需前缀查询。空间索引会从所有维度来索引数据。

查询时，可以有效地使用任意维度来组合查询。必须使用 MySQL 的 GIS 相关函数如 MBRCONTAINS() 等来维护数据。

MySQL 的 GIS 支持并不完善，所以大部分人都不会使用这个特性。开源关系数据库系统中对 GIS 的解决方案做得比较好的是PostgreSQL 的 PostGIS。

---

全文索引

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

全文搜索和其他几类索引的匹配方式完全不同，它有许多需要注意的细节，如停用词、词干、布尔搜索等等。全文索引更类似于搜索引擎做的事情，而不是简单的 Where 条件匹配。

在相同的列上同时创建全文索引和基于值的 B-Tree 索引不会有冲突，全文索引适用于 MATCH AGAINST 操作，而不是普通的 WHERE 条件操作。

我们将在第 7 章讨论更多的全文索引的细节。

5.2 索引基础

索引可以让服务器快速地定位到表的指定位置。但是这并不是索引的唯一作用，根据创建索引的数据结构不同，索引也有一些其他的附加作用。

例如，最常见的 B-Tree 索引：

• 按照顺序存储数据，所以 MySQL 可以用来做 ORDER BY 和 GROUP BY 操作；
• 因为数据是有序的，所以 B-Tree 也就会将相关的列值都存储在一起；
• 因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。

据此特性，总结下来索引有如下三个优点：

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

5.3 高性能的索引策略

正确地创建和使用索引是实现高性能查询的基础。

前文介绍了各种类型的索引及其优缺点，以下内容介绍如何发挥这些优势。

1. 独立的列

如果查询中的列不是独立的，则MySQL不会使用索引。

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

select actor_id
from sakila.actor
where actor_id+1=5;

肉眼可以看出where actor_id+1=5;等价于where actor_id=4;，MySQL无法自动解析这个方程式，这完全是用户行为。我们应该养成简化where，始终将索引单独放在比较符号的一侧。

2. 前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。

一个策略是前面提过的哈希索引，有时这样做还不够：通常还可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性。

索引的选择性：不重复的索引值（也称为基数，cardinality）和数据表的记录总数（# T）的比值，范围从1/# T~1之间。

索引的选择性 越高，则查询效率越高，因为选择性高的索引能让MySQL过滤掉更多的行。

唯一索引的选择性为1。这是最好的索引选择性，性能也是最好的。

一般情况下，某个前缀的选择性也是足够高的，足以满足查询性能。对BLOB，TEXT或很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀，以保证较高的选择性（使前缀索引的选择性接近于索引整个列），同时又不能太长（以便节约空间）。

为了决定前缀的合适长度，需要找到最常见值的列表，然后和最常见的前缀列表进行比较：

select count(*) as cnt,city
from sakila.city_demo
group by city
order by cnt desc
limit 10;

上面每个值都出现了45~65次，现在查找到最频繁出现的城市前缀，先从3个前缀字母开始：

select count(*) as cnt,left(city,3) as pref
from sakila.city_demo
group by pref
order by cnt desc
limit 10

每个前缀都比原来的城市出现的次数更多，因此唯一前缀比唯一城市要少得多。然后我们增加前缀长度，直到这个前缀的选择性接近完整列的选择性。经过试验后发现前缀长度为7时比较合适：

select count(*) as cnt,left(city,7) as pref
from sakila.city_demo
group by pref
order by cnt desc
limit 10;

观察二者的出现次数。

计算合适前缀长度的另一个方法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。完整列的选择性计算如下：

select count(distinct city)/count(*)
from sakila.city_demo;

通常来说，这个例子中如果前缀的选择性能够接近0.031，基本上就可用了，可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用：

select count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4,
count(distinct left(city,5))/count(*) as sel5,
count(distinct left(city,6))/count(*) as sel6,
count(distinct left(city,7))/count(*) as sel7
from sakila.city_demo;

上述结果显示，当前缀长度达到7时，再增加前缀长度，选择性提升的幅度就已经很小了。

上面示例已经找到了合适的前缀长度（7），下面创建前缀索引：

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

前缀索引优缺点
优点：使索引更小、更快的有效办法
缺点：MySQL无法使用前缀索引做ORDER BY 和GROUP BY，以及覆盖扫描

后缀索引（suffix index）
应用场景：找到某个域名的所有电子邮件地址。
MySQL不支持反向索引，但可以把字符串反转后存储，并给予此建立前缀索引。可以通过触发器维护。

3. 多列索引

不理解造成的常见错误：

• 错误1：为每个列创建独立的索引；
• 错误2：按照错误的顺序创建多列索引。

错误1：为每个列创建独立的索引

该错误常见于创建表：

create table t(
	c1 int,
	c2 int,
	c3 int,
	key(c1),
	key(c2),
	key(c3)
);

MySQL5.0和更新版本引入了“索引合并”（index merge），一定程度上可以使用表上的多个单列索引来定位指定的行。

例 ：老版本MySQL中的应用——表film_actor在字段film_id和actor_id上各有一个单列索引。但对于下面这个查询where条件，这两个单列索引都不是好的选择，老版本MySQL会对以下查询进行全表扫描：

select film_id,actor_id
from sakila.film_actor
where actor_id=1 or film_id=1;

除非改写成：

select film_id,actor_id from sakila.film_actor where actor_id=1
union all
select film_id,actor_id from sakila.film_actor where film_id=1 and actor_id<>1;

MySQL5.0和更新版本中，查询能够同时使用film_id和actor_id两个单列索引进行扫描，并将结果合并。这种算法有3个变种：

• OR条件的联合（union）
• AND条件的相交（intersection）
• 组合OR,AND的联合及相交

explain select film_id,actor_id
from sakila.film_actor
where actor_id=1 or film_id=1\G

MySQL中\g和\G的作用
\g的作用和MySQL中的分号”;"是一样；
\G的作用是讲查找到的内容结构旋转90度，变成纵向结构；
Navicat中不支持\G、\g

上述结果的Extra列可以看到，用了两个索引扫描的联合。

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

• 如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表结构，是不是已经最优。
• 也可以通过参数optimizer_switch来关闭索引合并功能。mysql 5.1中开始引入optimizer_switch,控制mysql优化器行为。他有一些结果集，通过on和off控制开启和关闭优化器行为。

select @@optimizer_switch;

+------------------------------------------------------------------------------------------------------------------------+
| @@optimizer_switch                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on | 
+------------------------------------------------------------------------------------------------------------------------+

业务需要或滥建索引，数据表上会建很多索引。针对这个查询，可以通过修改optimizer_switch来优化优化器行为：

set global optimizer_switch="index_merge=off"    

• 也可以使用 IGNORE INDEX提示让优化器忽略掉某些索引。

详见：【数据库笔记】MySQL中的force index和ignore index

错误2：按照错误的顺序创建多列索引

下文中对比了不同多列索引的查询效率，一般应尝试优化，确定最优顺序。

详见：【数据库笔记】MySQL中的force index和ignore index

4. 选择适合的索引顺序

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

列的顺序决定了一个索引能否成为一个真正的三星索引。

经验法则： 将选择性最高的列放到索引的最前列（这不一定适用于所有的场景）。在不考虑排序和分组时，中和中做法通常是很好的。此时，索引的作用只是用于优化where条件的查找，能够最快过滤所需的行。

然而，性能不只是依赖索引列的选择性（整体基数），也和查询条件的具体值（值的分布）有关。可能需要那些运行频率最高的查询来调整索引列的顺序。

比如下面的查询：

、select *
from payment
where staff_id=2 and customer_id=584;

问题：此时应该创建(staff_id,customer_id)索引 还(customer_id,staff_id)索引？

解决方案：可以先跑一些查询来确定值的分布情况，并确定哪个列的选择性更高。

先用下面的查询预测一下（此过程称为sarg,searchable argument,可搜索参数），看看各个where条件的分支对应的数据基数有多大：

select sum(staff_id=2),sum(customer_id=584) 
from payment\G

**************1.row**************
sum(staff_id=2):7992
sum(customer_id=584):30

根据前面的经验法则，应该将customer_id放到前面，因为对应条件值的customer_id数量更小。

我们再来看看，对于这个customer_id的条件值，对应的staff_id列的选择性如何：

select sum(staff_id=2) 
from payment 
where customer_id=584\G

**************1.row**************
sum(staff_id=2):17

注意：这样做查询的结果非常依赖与选定的具体值，如果按上述办法进行优化对一些其他的条件值是不公平的！服务器的整体性能可能会变得更糟或不如预期！

如果没有类似的具体查询来运行，最好按经验法则来做，因为经验法则兼顾全局基数(count(distinct staff_id))和值的分布（count(*)），而不是某个具体查询：

select count(distinct staff_id)/count(*) as staff_id_selectivity,
	   count(distinct customer_id)/count(*) as customer_id_selectivity,
	   count(*)
from payment\G

**************1.row**************
   staff_id_selectivity:0.0001
customer_id_selectivity:0.0373
			   count(*):16049

因此，customer_id的选择性更高，应该作为索引的第一列。

最后，尽管关于经验法则和推论在多数情况是有用的，但也不要忘了where子句中的排序、分组和范围条件等其他因素，这些因素可能会对查询性能造成非常大的影响。

详见：MySQL查询where条件的顺序对查询效率的影响
结论：MySQL遵循最左前缀原理，当查询条件匹配联合索引的前面几列时，可以使用联合索引；否则，不会使用联合索引。其他情况，顺序影响不大。

5. 聚簇索引

聚簇索引：并不是一种单独的索引类型，而是一种数据存储方式。“聚簇”表示数据行和相邻的键值紧凑地存储在一起。

• InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
• 一个表只能有一个聚簇索引。因为无法同时把数据行放在两各不同的地方。
• 并不是所有的存储引擎都支持聚簇索引，此处关注InnoDB。
  当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）。

6. 覆盖索引

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

通常，大家会根据查询的where条件来创建合适的索引，这只是索引优化的一方面。设计优秀的索引应该考虑到整个查询，而不单单是where条件部分。
索引确实是一种查找数据的高效方式，但是MySQL也可以用索引直接来获取列的数据，即叶子节点中已经包含了要的数据，还有什么必要读数据行？

覆盖索引的优点：

• 索引条目通常远小于数据行大小。这极大减少了数据访问了。
• 因为索引是按照列值顺序存储的（至少在单页内如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
• 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则能避免对主键索引的二次查询。

• MySQL只能用B-Tree索引做覆盖索引。
• 不是所有引擎都支持覆盖索引，如Memory存储引擎。

当发起一个索引覆盖查询时，在explain的Extra列可以看到Using index。

例：表sakila.inventory有一个多列索引(store_id,film_id)。MySQL如果只访问这两列，就可以使用这个索引做覆盖索引。

explain select store_id,film_id from sakila.inventory\G

MySQL查询优化器会在执行查询前判断是否有一个索引能覆盖。假设索引覆盖了where条件中的字段，但不是整个查询涉及的字段。MySQL5.5和更老的版本还是会回表获取数据行。

explain select * from products where actors='SEAN CARREY' and title like '%APOLLP%'\G

这里，索引无法覆盖该查询，原因有二：

• 没有任何索引能覆盖这个查询。因为select *选择了所有列，而没有索引覆盖所有列。理论上，MySQL还有一个捷径可用：where条件中有索引可覆盖，因此MySQL可以使用该索引找到对应的actor并检查title是否匹配，过滤之后再读取需要的数据行。
• MySQL不能再索引中执行like操作。MySQL5.5和更老的版本只允许在索引中进行简单操作（=，<>，>）。只能做最左前缀匹配的like比较，但是如果是通配符%开头就不行了。此种情况，只能提取数据行的值而不是比较索引值。
  

7. 使用索引来做排序

MySQL有两种方式可以生成有序的结果：

• 通过排序操作；
• 按索引顺序扫描。如果explain出来的type为‘index’，说明MySQL用了索引扫描来做排序，不要与extra列的using index混淆。

MySQL可以使用同一个索引既满足排序，又用于查找行。因此，设计索引应尽可能同时满足这两种任务。

只有当索引的列顺序与order by子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。

如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。

order by 子句和查找型查询的限制相同：需要满足最左前缀的要求。有一种例外情况可以不满足最左前缀要求，就是前导列为常量的时候。在where子句或join子句中对前导列指定常量，就还能用上索引。详见下面的例子：

这张表建立了索引（rental_date,inventory_id,customer_id）

create table rental(
	...
	primary key (rental_id),
	unique key rental_date (rental_date,inventory_id,customer_id),
	key idx_fk_inventory_id (inventory_id),
	key idx_fk_customer_id (customer_id),
	key idx_fk_staff_id (staff_id),
	...
);

MySQL可以用unique key rental_date 索引为下面的查询做排序，从explain可以看出没有出现文件排序（filesort）操作：

explain select rental_id,staff_id 
from sakila.rental
where rental_date='2005-05-25'
order by inventory_id,customer_id\G

即使order by子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为索引的第一列被指定为一个常数！

同理，衍生出一些其他示例：

1. 索引的第一列指定常量条件，而使用第二列进行排序，将两列组合成为索引的最左前缀：
   疑问：这里索引的第二列inventory_id 需要asc才能用么？
   解答：详见 动手实践

...where rental_date='2005-05-25' order by inventory_id desc;

1. order by使用的前两列就是索引的最左前缀：

...where rental_date>'2005-05-25' order by rental_date,inventory_id;

动手实践

下面是自己的数据库表 employees 的试验：

1.首先为表employees 建立多列索引ix_empno_gender(emp_no,gender)：

alter table employees add index ix_empno_gender(emp_no,gender);
show keys from employees;

2.当order by emp_no desc,gender asc两个索引的顺序相反时，果然不能用索引。只有当索引的列顺序(emp_no,gender)与order by emp_no ,gender子句的顺序完全一致（都是(emp_no,gender)），并且所有列的排序方向（倒序或正序）都一样时（均为asc或desc），MySQL才能够使用索引来对结果做排序。

explain select emp_no,birth_date,first_name,last_name
from employees force index(ix_empno_gender) 
order by emp_no desc,gender asc --所有列的排序方向不同

--或

explain select emp_no,birth_date,first_name,last_name
from employees force index(ix_empno_gender) 
order by emp_no asc,gender desc--所有列的排序方向不同

--或

explain select emp_no,birth_date,first_name,last_name
from employees force index(ix_empno_gender) 
order by gender,emp_no --索引的列顺序与`order by`子句的顺序不同

explain select emp_no,birth_date,first_name,last_name
from employees force index(ix_empno_gender) 
order by emp_no,gender

--或

explain select emp_no,birth_date,first_name,last_name
from employees force index(ix_empno_gender) 
order by emp_no desc,gender desc

--解答跳转处的疑问:desc和asc都可以，同样符合上述规律（order by后的索引全部是desc或asc）
explain select emp_no,birth_date,first_name,last_name
from employees
where emp_no=10001
order by gender desc

--或

explain select emp_no,birth_date,first_name,last_name
from employees
where emp_no=10001
order by gender asc

在select中查询索引之外的列，MySQL就不会用索引，需要用force index(ix_empno_gender) 来强制使用索引！！！
详见：mysql order by为什么没有走索引排序？

8. 压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大提高性能。

默认只压缩字符串，但通过参数设置也可以对整数做压缩。

9. 冗余和重复索引

重复索引：在相同列上按照相同顺序创建的相同类型的索引，发现后应该立即移除。
如果索引的类型不同，不算重复索引！比如KEY(col)和FULLTEXT KEY(col).

MySQL允许在相同列上创建多个索引，无论是有意还是无意的。MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

例：如下所示，想创建一个主键，先加上了唯一限制，再加上索引以供查询。事实上MySQL的唯一限制和主键限制都是通过索引实现的。因此，下面的例子实际上在ID列上创建了3个重复的索引。除非有不同的查询需求，不然没啥必要。

create table test(
	ID INT NOT NULL PRIMARY KEY,
	A  INT NOT NULL,
	B  INT NOT NULL,
	UNIQUE(ID),
	INDEX(ID)
) ENGINE=InnoDB;

冗余索引：如果创建了索引（A,B），那么再创建索引（A）就是冗余索引。在大多情况下都不需要冗余索引，应该扩展已有的索引而不是创建新的索引！！

• 索引（A,B）也可以当做索引（A）使用。但如果再创建索引（B,A）不算是冗余索引，因为B不是索引（A,B）的前缀列！
• 还有一种情况是，将一个索引拓展成（A,ID）,其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中了。
  
  有两个索引的缺点：索引的成本更高，插入数据耗费更长的时间。一般来说，增加索引会导致INSERT, UPDATE, DELETE等操作的速度变慢！

解决冗余索引和重复索引的方法：找到它们并删除它们！！

• 可使用Shlomi Noach的common_shema中的一些视图定位。比自己编写查询要快且简单。
• 可使用Percona Toolkit中的pt-duplicate-key-checker,该工具通过分析表结构来找出冗余和重复的索引。
• 在决定删除哪些索引时，建议使用Percona工具箱中pt-upgrade工具仔细检查计划中的索引变更。

10. 未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不使用的索引，这样的索引完全是累赘，建议考虑删除，有两个工具可以帮助定位未使用的索引：

• 在percona server或者mariadb中先打开userstat=ON服务器变量，默认是关闭的，然后让服务器运行一段时间，再通过查询information_schema.index_statistics就能查到每个索引的使用频率。
• 使用percona toolkit中的pt-index-usage工具，该工具可以读取查询日志，并对日志中的每个查询进行explain操作，然后打印出关于索引和查询的报告，这个工具不仅可以找出哪些索引是未使用的，还可以了解查询的执行计划，如：在某些情况下有些类似的查询的执行方式不一样，这可以帮助定位到那些偶尔服务器质量差的查询，该工具也可以将结果写入到MySQL的表中，方便查询结果。

11. 索引和锁

索引和锁可以让查询锁定更少的行。如果你的查询从不访问那些不需要访问的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外的开销，其次，锁定超过需要的行会增加锁竞争，并减少并发性。

InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但只有当InnoDB在存储引擎能够过滤掉不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句。这时候，已经无法避免锁定行了：InnoDB可以在服务器端过滤掉行后就释放锁，但是在早期的MySQL版本中，InnoDB只有在事务提交后才能释放锁。

通过下面的例子，很好的解释了这些情况

SELECT actor_id 
FROM actor 
WHERE actor_id < 5 AND actor_id<>1 FOR UPDATE;

这些表仅仅会返回2-4之间的行，但是实际上获取了1-4之间行的排他锁，InnoDB会锁住第一行，这是因为MySQL为该查询选择执行计划是索引范围扫描；换句话说，底层存储引擎操作的是“从索引的开头开始获取满足条件的actor_id<5的记录”服务器并没有告诉InnoDB可以过滤掉第一行的where条件。注意到explain的Extra列出现了“Using Where” 这表示MySQL服务器将存储引擎返回行以后再应用where过滤条件。

下面的第二个查询就可以证明第一行确实已经被锁定，尽管第一个查询的结果中并没有这个第一行。保持第一个连接打开，然后开启第二个连接并执行如下语句：

SELECT actor_id 
FROM actor 
WHERE actor_id = 1 FOR UPDATE.

这个查询就会被挂起，直到第一个事务释放第一行的锁。这个行为对于基于语句的复制的正常运行来说是必要的。

就像这个例子显示的，即使使用了索引，InnoDB可能也会锁住一些不需要的数据。如果不能使用索引查找和锁定行的话问题可能会很糟糕，MySQL会做全表扫描并锁定所有的行，而不管是不是需要。

关于InnoDB，索引和锁有一些很少有人知道的细节： InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写），这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE 比LOCK IN SHARE MODE 或非锁定查询要慢得多。

5.4 案例学习

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

如何设计索引，以满足上述复杂需求？
考虑需要使用索引来排序，还是先检索数据在排序？

1. 支持多种过滤条件

首先，我们要看哪些列具有很多不同的值，哪些列在where子句中出现得最频繁。在有更多不同值的列上创建索引效果会更好。一般这样做都是对的，因为可以让MySQL更有效地过滤掉不需要的行。

country列的选择性不高，但是可能很多查询都会用到。sex列的选择性很低，但是很多查询也会用到。
所以考虑到使用的频率，还是建议创建不同组合索引的时候将（sex，country）列作为前缀。理由：

• 几乎所有的查询都会用到sex列，如果某个查询不限制性别，我们可以通过新增and sex in （‘m’,’f’）来让MySQL选择索引，虽然这样写没有过滤任何行和不加时候返回结果一样，但是加上去后，MySQL才能够匹配索引的最左前缀。这个诀窍在这类场景中非常有效，但是仅限于IN（）列表比较短。

原则1：考虑表上的所有选项。当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化，如果发现某些查询需要创建新的索引，但是这个索引又会降低另一些查询的效率，那么应该想一下是否能优化原来的查询。应该同时优化查询和索引找到最佳平衡！

接下来考虑哪些where条件的组合，并需要了解哪些组合在没有合适索引的情况下会变得很慢。（sex，country，age）上的索引就是一个明显的选择，还有可能是（sex，country，region，age）和（sex，country，region，city，age）这样的组合索引。可以使用IN（）技巧来避免同时需要不同的索引。而且我们把age索引放到最后一列是有原因的，我们总是尽可能让MySQL使用更多的索引列，因为查询只能使用索引的最左前缀，直到遇到第一个范围查询。Age多半是范围查询，between 18 and 25.

原则2：组合索引中，把范围索引放到最后一列，因为最左匹配原则。可以再索引中加入更多列，通过IN（）方式覆盖哪些不在where句子中的列。但是不可滥用。

Where eye_color in(‘brown’，‘blue’，‘Hazel’)
And hair_colorin(‘black’,’red’,’blonde’,’brown’)
And sex in(‘F’,’M’)

对于MySQL来说，342=24种组合不是很夸张，但是组合数达到上千个需要特别小心，可能会导致MySQL无法很好地使用索引！

2.避免多个范围条件

假设我们希望通过下面的查询显示最近几周上线过的用户：

Where eye_color in(‘brown’，‘blue’，‘Hazel’)
And hair_colorin(‘black’,’red’,’blonde’,’brown’)
And sex in(‘F’,’M’)
And last_online>DATE_SUB(NOW(),INTERVAL7 DAY)
And age between 18 and 25

这个查询有个问题：存在2个范围条件last_online>DATE_SUB(NOW(),INTERVAL7 DAY)和
age between 18 and 25。MySQL只能使用last_online列索引或age列索引。

什么是范围条件？

从explain的输出难以区分MySQL是要查询范围值，还是列表值。explain的结果会在type中显示range来描述两种情况。

explain select actor_id from sakila.actor
where actor_id > 45\G

explain select actor_id from sakila.actor
where actor_id in(1,4,99)\G

Wehre actor_id>12是范围条件查询，无法再使用范围后面的其他索引列了，where actor_idin(1,4,99)是多个等值查询。从explain的输出难以区分MySQL是要查询范围值，还是列表值。

这两种访问效率是不同的，MySQL无法使用范围列后面的其他索引列了，但是对于多个等值查询则没有这个限制！！

3.优化排序

文件排序（filesort）对小数据集是很快的，但如果数据很多的术后怎么整？？

当需要排序的时候，对于那些选择性很低的列，可以增加一些特殊的索引来做排序，可以创建(sex,rating)索引用于一下查询：

Select cols 
from profiles 
where sex=’M’
order by rating 
limit 10;

这个查询同时用了order by和limit，如果没有索引可能会很慢！

即使有了索引，当用户界面上需要翻页，并且比较靠后的时候会非常慢，随着偏移量的增加，MySQL需要花费大量的时间来扫面需要丢弃的数据，反范式化、预先计算缓存可能是解决这类查询的仅有策略，有两个建议：

• 限制用户的翻页数量；
• 使用延迟关联，通过索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。如下显示如何高效地使用（sex,rating）索引进行排序和分页：

Select cols 
from profiles inner join
	(select<primary key cols> 
	from profiles 
	where x.sex=’M’ 
	order by rating 
	limit 100000,10) as x using(<primary key cols>);

5.5 维护索引和表

即使使用了正确的类型创建了表并加上了适合的索引，工作也没结束：还需要维护表和索引，确保他们正常工作。

维护表的三个目的：

• 找到并修复损坏的表，check table通常能够找出大多数表盒索引的错误。；
• 维护准确的索引统计信息；
• 减少碎片。

5.6 总结

MySQL索引是一个非常复杂的话题！

MYSQL和存储引擎访问数据的方式，加上索引的特性，使得索引成为一个影响数据访问的有力而灵活的工作。

在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住：

1. 单行访问是很慢的。特别是在机械硬盘存储中（SSD的随机I/O要快很多，不过这一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多的行。使用索引可以创建位置引用以提升效率。
2. 按顺序访问范围数据是很快的，这里有两个原因：第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）；第二，如果服务器能够按需要顺序读取数据，那么就不要在需要额外的排序操作，并且group by查询也无需再做排序和按行按组进行聚合计算了。
3. 索引覆盖查询是很快的，如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查询行。这避免了大量的单行访问。

总的来说，编写查询语句应该间可能的选择合适的索引来避免单行查询，尽可能的使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询 。

这个就是开头提出来的“三星”评价系统是一致的。

如何判断一个系统创建的索引是否合理：
建议按照响应时间来对查询进行分析，找出消耗最长时间的查询或者那些给服务器带来最大压力的查询，然后检查这些查询的 schema,SQL和索引结构，判断是否有查询扫描了太多的行是否做了太多额外的排序或者使用了临时表，是否使用随机I/O访问数据，或者返回了太多不需要的列操作。