MySQL 数据库索引

索引

1. 索引介绍

索引（index）是存储引擎用于快速找到记录的一种数据结构，在MySQL 中也叫做键（key）。索引是在存储引擎层而不是服务层实现的，所以没有统一的标准：不同存储引擎的索引工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。

index	InnoDB	MyISAM	Memory
B+树索引	√	√	√
哈希索引	√	×	√
空间索引	×	√	×
全文索引	√（5.6版本）	√	×
聚簇索引	√	×	×

索引的优点：

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

索引的缺点：

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

2. 索引类型

2.1 B+树索引

B-Tree 索引是最常见的索引类型，大多数 MySQL 引擎都支持，但使用方式、性能各有不同，InnoDB 引擎默认使用的就是 B-Tree 索引（准确的说，应该是B+Tree）。B-Tree 对索引列是顺序组织存储的，所以很适合查找范围数据，还可以用于查询中的 order by 排序操作。

只包含一个字段的索引叫做单列索引，包含两个或以上字段的索引叫做复合索引（组合索引）。下图是 B-Tree 单列索引的抽象表示。其中叶子节点比较特别，指针指向的是被索引的数据，而不是其它的节点页。

下图是 B-Tree 复合索引的抽象表示。索引中包含了姓（last_name）、名（first_name）、出生日期（dob）列的值，排序顺序依据 create table 语句中定义的索引列顺序，比如当两个人的姓和名都一样时，按照出生日期进行排序。

建立复合索引时，字段的顺序极其重要，因为键前缀查找只适用于最左前缀查找。

• 查询必须从索引的最左边的列开始，否则无法使用索引。例如，你不能利用索引查找在某一天出生的人。
• 不能跳过某一索引列。例如，你不能利用索引查找姓为 Smith 且出生于某一天的人。
• 存储引擎不能使用索引中范围条件右边的列。例如，查询语句 where last_name=“Smith” and first_name like ‘J%’ and dob=‘1976-12-23’，则该查询会使用复合索引，但只会使用索引中的前两列，因为 like 是范围查询。

2.2 哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎会对所有的索引列计算一个哈希码，并将所有哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。如果多个哈希码相同（哈希冲突），索引会以链表的方式存放多个记录指针到同一个哈希条目中。

哈希索引是 Memory 引擎的默认索引类型，而 InnoDB 引擎有自适应哈希索引功能，当某个索引值被频繁使用时，会在内存中基于 B-Tree 索引之上再创建一个哈希索引，这样就让 B-Tree 索引也具有哈希索引的一些优点。

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

• 哈希索引值包含哈希值和行指针，不存储字段值，所以不能避免读取数据行；
• 哈希索引不是按照索引值顺序存储的，所以无法用于排序；
• 哈希索引使用索引列的全部内容来计算哈希值，所以不支持部分索引列匹配查找，也不支持任何范围查找，只支持等值比较查找，包括 =、in、<=>（注意 <> 和 <=> 是不同的操作）。
• 如果哈希冲突很多，存储引擎必须遍历链表中所有的行指针，导致查询变慢和维护代价变高。

2.3 空间索引

空间索引（R-Tree）用作地理数据存储，它无须前缀查找，会从所有维度来索引数据。

2.4 全文索引

全文索引是一种特殊类型的索引，更类似于搜索引擎做的事情，它查找的是文本中的关键词，而不是直接比较索引中的值。它适用于 match against 操作，而不是普通的 where 条件操作。全文索引通常使用倒排索引（inverted index）来实现，倒排索引同 B+ 树索引一样，也是一种索引结构，它存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式：

• inverted file index，其表现形式为 {单词，单词所在文档的 ID}
• full inverted index，其表现形式为 {单词，{单词所在文档的 ID，在具体文档中的位置}}

相比之下，full inverted index 占用更多空间，但是能更好地定位数据，并扩充了一些其它搜索特性。InnoDB 存储引擎采用的就是这种方式。

3. 高效索引

3.1 前缀索引

当索引是很长的字符列时，会让索引变得大且慢。一种策略是模拟哈希索引，比如使用 CRC32 函数对 URL 做哈希，建立 URL 与 哈希值的对应关系；另一种策略是可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。对于 blob、text 或者很长的 varchar 类型的列，必须使用前缀索引。

前缀选择应该足够长，以使得前缀”索引的选择性“接近于索引整个列，但同时又不能太长，以便节约空间。索引的选择性指的是：不重复的索引值（基数）和数据表的记录总数（T）的比值，范围从 1/T 到 1 之间，索引的选择性越高区分度越好，查询效率越高。

3.2 聚簇索引

聚簇索引也叫聚集索引，它不是一种索引类型，而是一种数据存储方式。”聚簇“表示数据行和相邻的键值紧凑地存储在一起，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

InnoDB 默认使用主键作为聚簇索引，如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。下图是聚簇索引的抽象表示，注意节点页只包含索引列，而叶子页包含了行的全部数据。

聚簇索引的优点：

1. 可以把相关数据保存在一起，减少读取磁盘的次数，加快数据访问。
2. 数据访问更快，因为聚簇索引将索引和数据保存在同一个B-Tree中。
3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点：

1. 更新聚簇索引的代价很高，因为会强制 InnoDB 将每个被更新的行移动到新的位置。
2. 插入新行可能导致页分裂。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多磁盘空间。

1. 聚簇索引与非聚簇索引、主键索引与二级索引的对比

• MyISAM

  MyISAM 按照数据插入的顺序存储在磁盘上，如下图所示。左边是行号，从 0 开始递增，因为行是定长的，所以 MyISAM 可以从表的开头跳过所需的字节找到需要的行。

  

  MyISAM 不支持聚簇索引，且主键索引与二级索引没有什么区别，主键索引仅仅是一个名为 primary 的唯一非空索引。如下图所示，索引中的每个叶子节点仅包含行号。

• InnoDB

  InnoDB 支持且默认使用主键作为聚簇索引，如下图所示，每一个叶子节点都包含了主键值、事务ID、用于事务和 MVCC 的回滚指针以及剩余列。在 InnoDB 中，聚簇索引”就是表“，所以不像 MyISAM 那样需要独立的行存储。

InnoDB 的二级索引与聚簇索引不同，二级索引访问需要两次索引查找，而不是一次，如下图所示。叶子节点中存储的不是行指针，而是主键值，并以此作为行的指针。这样做的好处是减少了移动数据或者数据页分裂时二级索引的维护开销。

在 InnoDB 表中，如果设置了主键，则聚簇索引就是主键索引，一个表有且只有一个聚簇索引，而所有普通索引（非聚簇索引）都是二级索引。最后给出 InnoDB 和 MyISAM 如何存放表的抽象图。

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

使用 InnoDB 时应该尽可能地按自增的主键顺序插入数据，这样可以保证数据行是按顺序写入的。

如果主键的值是顺序的，InnoDB 会把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中，此时主键页近似于被顺序的记录填满。

如果主键的值不是顺序的，而是无规律数据，比如字符串，InnoDB 无法简单地把新行插入到索引的最后，而是需要为新行寻找合适的位置（通常是已有数据的中间位置），并且分配空间。这就会导致大量的随机 I/O，页分裂，最终数据也会有碎片。

3.3 覆盖索引

如果一个索引包含（覆盖）所有需要查询的字段的值，就称为覆盖索引。即从辅助索引中就可以得到查询的记录，而不需要回表查询聚集索引中的记录。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆盖索引。为了实现索引覆盖，通常将被查询的字段建立到复合索引中。

覆盖索引的优点：

1. 索引条目通常远小于数据行大小，所以如果只需要读取索引，可以极大地减少数据访问量。
2. 由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 表特别有用。而由于 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此访问数据需要一次系统调用，可能导致严重的性能问题。

3.4 索引排序

MySQL 可以利用同一索引同时进行查找和排序操作，只有当索引的顺序与 order by 子句的顺序完全相同，且所有的列是同一方向（全部升序或者全部降序）时，才可以使用索引来排序。如果查询需要关联多个表，则只有当 order by 子句引用的字段全部是第一个表时，才能使用索引进行排序。

和查询的限制一样，索引排序需要满足索引的最左前缀要求。有一种情况例外，那就是前导列为常量时，即 where 子句或者 join 子句中对前导列指定了常量。其它情况都会使用 filesort 文件排序。

-- 假设key idx_rental(rental_date, inventory_id, customer_id)
-- 1.能使用索引排序
where rental_date = '2005-05-25' order by inventory_id, customer_id;  -- 第一列被指定为常数
-- 2.不能使用索引排序
where rental_date = '2005-05-25' order by inventory_id desc, customer_id asc; -- 排序不一致
where rental_date = '2005-05-25' order by customer_id;	-- 不符合最左前缀
where rental_date > '2005-05-25' order by inventory_id, customer_id;	-- 第一列是范围查找
where rental_date = '2005-05-25' and inventory_id in (1,2) order by customer_id;	-- 对于排序来说，多个相等条件也是范围查找

3.5 索引失效

1. 索引列不能是表达式的一部分，也不能是函数的参数。
2. like 模糊匹配不要以通配符 % 或 _ 开头，即头部模糊匹配。
3. 尽量不要使用 not in、<>、!= 操作。
4. 用 or 分割开的条件， 如果 or 前的条件中的列有索引， 而后面的列中没有索引， 那么涉及到的索引都不会被用到。
5. 如果列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来。否则即便这个列上有索引，MySQL 也不会用到的，因为 MySQL 默认把输入的常量值进行转换以后才进行检索。
6. 尽量使用覆盖索引，用具体的字段列表代替 select *。

4. 查询优化

4.1 show

通过 show [session|global] status 命令可以查看服务器状态信息，session 显示当前连接的计结果，global 显示自数据库上次启动至今的统计结果，默认使用 session。

-- 统计所有存储引擎执行增删改查的次数
show status like 'Com_______';
-- 只统计InnoDB存储引擎执行增删改查的次数
show status like 'Innodb_rows_%';
-- 统计慢查询的次数
show status like 'Slow_queries';
-- 统计试图连接MySQL服务器的次数
show status like 'Connections';

4.2 慢查询日志

可以通过以下两种方式定位执行效率较低的 SQL 语句：

• 慢查询日志：它会记录运行时间超过设定阈值的所有 SQL 语句，阈值默认 10 秒；分析慢查询日志需要使用 mysqldumpslow 命令
• show processlist：慢查询日志在查询结束后才记录，使用 show processlist 命令可查看当前 MySQL 正在进行的线程，包括线程的状态、是否锁表等，实时查看 SQL 的执行情况，同时对一些锁表操作进行优化。

4.3 explain

使用 explain SQL语句 命令可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。

字段	含义
id	表示查询的序列号，是一组数字，表示的是查询中执行 select 子句或者是操作表的顺序。id 相同，执行顺序从上到下；id 不同，值越大优先级越高，越先被执行。
select_type	表示查询类型，常见的取值有：SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	表示这一行的数据是关于哪张表的
type	表示表的连接类型，性能由好到差的连接类型为：NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref。
possible_keys	表示查询时，可能使用的索引，一个或多个，但不一定被查询实际使用
key	表示实际使用的索引，如果为 NULL，则没有使用索引
key_len	表示索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
ref	表示索引的哪一列被使用了，如果可能的话，是一个常数
rows	根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数
extra	包含不适合在其他列中显示但十分重要的额外信息，常见的取值有：using filesort（数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为“文件排序”，效率低）、using temporary（使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，效率低）、using index（使用了覆盖索引，避免访问表的数据行，效率不错）

type	含义
NULL	MySQL 不访问任何表，索引，直接返回结果
system	表只有一行记录（等于系统表），这是 const 类型的特例，一般不会出现
const	表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快，如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量
eq_ref	类似 ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作
index	index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比 ALL 快， ALL 是遍历数据文件
all	将遍历全表以找到匹配的行

4.4 profiling

在做 SQL 优化时，show profiles 和 show profile 命令能帮助我们了解耗费的时间，默认情况下，profiling 是关闭的，可使用 select @@profiling 查看是否开启 profiling；使用 set profiling=1 来开启 profiling。

首先执行一系列 SQL 命令，然后执行 show profiles 命令来查看 SQL 语句执行的耗时：

然后通过 show profile for query query_id 语句查看该 SQL 执行过程中每个线程的状态和消耗的时间。其中，Sending data 状态表示 MySQL 线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在 Sending data 状态下，MySQL 线程往往需要做大量的磁盘读取操作，所以经常是各查询中耗时最长的。

参考

1. 《高性能 MySQL》
2. 《MySQL 技术内幕 - InnoDB 存储引擎》
3. MySQL 官网
4. MySQL 三万字精华总结
5. MySQL 索引总结
6. MySQL InnoDB 聚簇索引 非聚簇索引 二级索引 普通索引定义