索引——MySQL技术内幕 InnoDB存储引擎

InnoDB支持以下索引

1. B+树索引
2. 散列索引
3. 全文索引

其中，散列索引是由InnoDB自动创建的自适应哈希索引，人工无法干预。

我们最常见到的，也是大部分数据库系统都在用的索引类型就是B+树索引。

索引并不是越多越好，想要提升查询性能就要在更新性能上做一定的让步，我们需要在这两种性能之间寻找一个平衡点，而不是一味的创建索引。

B+树索引

不会过多介绍什么是B+树以及什么是聚集索引，非聚集索引（辅助索引）。

聚集索引

我们通过一个例子看看InnoDB如何存储聚集索引，当前页大小为16KB，我们人为让一个页只能存储两行数据。

CREATE TABLE test_cidx(
  a INT PRIMARY KEY,
  b VARCHAR(8000),
  c INT NOT NULL,
  KEY idx_c (c)
);

INSERT INTO test_cidx SELECT 1, REPEAT('a', 7000), -1;
INSERT INTO test_cidx SELECT 2, REPEAT('a', 7000), -2;
INSERT INTO test_cidx SELECT 3, REPEAT('a', 7000), -3;
INSERT INTO test_cidx SELECT 4, REPEAT('a', 7000), -4;

查看现在该表空间的页情况

这里有了5个B+树索引页，并且通过观察prev和next，我们能得知6，7，8是叶子节点。而4和5都是非叶子节点。我通过观察发现4这个页才是聚集索引的B+树的根节点，而关于5，那是我们在列c上创建的辅助索引。

对于数据页(叶子节点的B+树页)，上一篇笔记已经分析过了，我们只看索引页(非叶子节点的B+树页)，也就是第4个页。

通过计算得知，第四个页的偏移量在00010000，通过hexdump工具将二进制文件转换为文本并找到00010000这一行。

我们关心索引页具体是如何存储的，而不关心它的其他结构，如FILE_HEADER......所以我们需要找到infimum和supremum，这两个虚拟行中间就是索引页中存储的B+树记录。

这个69 6e 66 69 6d 75 6d代表的就是infimum，它前面的五个字节中的最后两个就是相对于infimum记录的起始位置，下一条记录的偏移量。也就是00 1b。

也就是说，00010063 + 0000001b = 00010079，就是实际的B+树中第一条记录的位置。也就是这个80开始的位置。

80 00 00 01最后的01，代表主键列的值为1(32位有符号整数，第一位符号位)，后面的00 00 00 06代表具有该索引值的记录在第6页中。按此方式寻找，还能找到几个另外的记录以及所在的页。

主键id     所在页
1         6
2         7
4         8

所以我们可以推测，主键id为3的记录也在页2里，也就是说页2保存了两行数据，其它的每一个数据页分别保存一行数据。

需要注意，很多数据库书籍上给人的印象就是聚集索引按主键顺序对记录进行存储，但在实现中，维护这个顺序过于复杂，所以，很多时候只是使用这种双向链表来维护一个聚集索引的逻辑顺序。

聚集索引对主键的排序查找和范围查找非常快，因为它逻辑有序，所以根本不用进行实际的外部或内部归并排序。

辅助索引

辅助索引有很多名字，比如，非聚集索引，二级索引，它们说的都是一个东西。即按照某一列（几列）作为索引码构建B+树，但它的叶子节点不保存整行数据，而是保存一个书签(bookmark)，用于找到与索引对应的行数据。

InnoDB中，这个书签就是主键列的值，因为InnoDB会保证每张表都有一个基于主键的聚集索引，查询非聚集索引时只需要拿到这个书签，再去查询聚集索引即可获得对应的行记录。

还是刚刚的表空间，分析页5，也就是建立在列c上的辅助索引。第五页的起始位置是00014000。

还是找69 6e 66 69 6d 75 6d infimum，它在00014063的位置。

01 00 02 00 41        ## 00 41为偏移量
69 6e 66 69 6d 75 6d  ## infimum

所以第一行在：00014063 + 00000041 = 000140A4

第一行数据是7f ff ff fc 80 00 00 04，格式化一下应该会看的清楚一点

7f ff ff fc    ## -4
80 00 00 04    ## 4

我不知道InnoDB的整数使用什么规范，反正好像不是补码，但是第一位也是标志位。唉，计算机原理学的不好。。

总之，这一行数据证明了辅助索引列c为-4的那一条记录的主键值为4，然后InnoDB就会通过主键值去聚集索引里再次查找这行数据。

对于其它的几个辅助索引，也能看到

7f ff ff ff    ## -1
80 00 00 01    ##  1

====

7f ff ff fe   ## -2
80 00 00 02   ##  2

====

7f ff ff fd   ## -3
80 00 00 02   ##  3

InnoDB中的B+树节点分裂

最简单的方法就是从中间分裂开，但当插入是顺序的时候，如果从中间分裂节点，会导致节点利用率变低，因为分裂后，左边的节点永远不会插入新数据。

上一篇笔记中的InnoDB页中的Page Header有这样几个字段，当时还想，存这几个字段有啥用呢。

这几个字段就是用来保存最近插入的方向已经已经连续多少次同方向插入了。通过这几个字段就可以判断数据是连续的还是随机插入的。然后InnoDB会选择一个最合适的分裂位置进行分裂。

InnoDB B+树索引管理

创建删除索引

ALTER TABLE tablename
  ADD | DROP {INDEX | KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...


CREATE [UNIQUE] INDEX index_name
[index_type]
ON tablename (index_col_name,...)

DROP INDEX index_name ON tablename

查看索引信息

SHOW INDEX FROM tablename\G;

比较关键的是Cardinality，这个代表表中这一个索引列大概有多少不重复数据，如果它和表的行数差距太大的话，可以考虑删除这个索引了。SQL优化器也会考虑这个值，如果这个索引太垃圾了，优化器就考虑不走这个索引。这个值不是实时更新的，所以只是一个估计值。

可以使用ANALYZE TABLE命令对表进行分析，从而更新这个值。

Fast Index Creation

MySQL 5.5 之前如果想后期对表的索引进行改动，会执行如下操作

1. 建立与原表具有新结构的临时表
2. 把原表中的数据导入到临时表
3. 删除原表
4. 把临时表重命名为原表的名字

这样做会消耗很长时间，并且修改时不能处理有关该表的其他事务。

InnoDB 1.0.x支持了FIC，即快速索引创建。就是当创建辅助索引时先对表添加一个S锁，然后不用重建表，即可在原表中对表结构进行改动。对于需要获取X锁的写事务，同样无法处理，创建和删除主键同样需要重建表。

Online Schema Change

一种在线执行DDL的方式，允许执行DDL时对表进行读写操作。不过这是在数据库外部实现的。

Facebook的OSC实现也采用了表复制的办法。

Online DDL

MySQL 5.6开始支持Online DDL。在数据库层面允许创建辅助索引的同时执行其他DML操作，除了创建辅助索引，还支持在线执行以下DDL操作：

1. 辅助索引创建与删除
2. 改变自增长值
3. 添加或删除外键约束
4. 列的重命名

可以在ALTER TABLE语句中添加一个ALGORITHM参数，用于启用Online DDL。它的可选值有DEFAULT | INPLACE | COPY，COPY即原始的复制方式，INPLACE即无需复制的方式，DEFALUT根据参数old_alter_table来判断使用什么算法。

OFF说明默认使用INPLACE。

ALTER TABLE还增加了一个LOCK参数，用于指定执行DDL时表的锁定状态。可以选择NONE | SHARE | EXCLUSIVE | DEFAULT。分别是不上锁，共享锁，排他锁和默认。

默认情况下，数据库会判断当前DDL操作能否使用NONE，如果不能再判断SHARE，如果还不能再判断EXCLUSIVE。

InnoDB通过在执行DDL时对DML语句的操作日志保存到缓存中，等待DDL执行完成再将重做应用到表上。缓存的大小由innodb_online_alter_log_max_size控制。如果缓存不够大，DDL执行不成功。

Cardinality值

在介绍数据库系统概念的书中，在索引一章都会提到索引的选择性。选择性就是索引列不重复的行数/所有行数，这个值应该无限接近于1，如果这个值非常小，那么建立索引的意义不大。索引的意义在于从大量数据中快速检索出其中的一小部分（或一条）。

在MySQL中使用SHOW INDEX命令得到的Cardinality字段代表索引列在表中不重复的行数的估计值，InnoDB是如何产生这个估计值的呢？

InnoDB的Cardinality统计

在一个访问量不小的生产系统中，不可能每次产生索引更新操作就统计Cardinality，一是要统计的表可能非常大，二是太浪费系统的计算能力。

InnoDB通过两个条件触发Cardinality的更新

1. 表中1/16的数据已经发生变化
2. stat_modified_counter > 2 000 000 000

第二个主要适用于更新频繁的发生在一些固定的行内时。

上面是何时更新，下面是怎样更新，InnoDB和大部分数据库一样，通过采样的方式更新这个值，所以说这是个预估值。

InnoDB随机抽取8个叶子节点，统计其中不同记录的个数，分别记作\(P_1, P_2, ... ,P_8\)，然后\(Cardinality=(P_1+P2+...+P_8) * A / 8\)（A为表中所有叶子节点的数量）。

B+树索引的使用

联合索引

如下是使用多个列进行联合索引的B+树，可以看到B+树中的排序依据是使用两个列一起比较，也可以理解为先按第一个列排序，若第一个列相等再按第二个列排序。

假如上图中的第一个列名为a，第二个列名为b，那么下面的语句可以用到索引

# 语句1 因为a已经有序
SELECT * FROM t WHERE a = 'xxx';

# 语句2 因为所有a相等的项目，它们按照b来排序
SELECT * FROM t WHERE a = 'xxx' and b='xxx';

下面的语句不能使用索引

# 语句1 因为没有条件限定a的话，b是无序的
SELECT * FROM t WHERE b = 'xxx';

联合索引可以规避一些排序操作，比如获取用户最近3次的购买记录。

CREATE TABLE buy_log(
  user_id INT UNSIGNED NOT NULL,
  buy_date DATE
);

INSERT INTO buy_log VALUES (1, '2009-01-01');
INSERT INTO buy_log VALUES (2, '2009-01-01');
INSERT INTO buy_log VALUES (3, '2009-01-01');
INSERT INTO buy_log VALUES (1, '2009-02-01');
INSERT INTO buy_log VALUES (2, '2009-02-01');
INSERT INTO buy_log VALUES (3, '2009-02-01');
INSERT INTO buy_log VALUES (1, '2009-03-01');
INSERT INTO buy_log VALUES (1, '2009-04-01');
INSERT INTO buy_log VALUES (3, '2009-04-01');

仅创建单独索引

ALTER TABLE buy_log ADD KEY (user_id);

获取最近三次购买记录

分析一下该语句执行过程，使用了外部文件排序。因为取出了user_id=1的购买记录，但它们并未按照buy_date排序，所以要进行一次文件排序。文件排序是一个极其耗时的操作，需要很多次IO操作，尤其是在大表中应该尽量规避。

创建联合索引

ALTER TABLE buy_log ADD KEY (user_id, buy_date);

这次可选的索引变成了两个，而且使用了第二个索引，并且没有进行文件排序。

对于独立索引和联合索引共同存在的情况，如果你的查询条件只用一个独立索引就可以完成，MySQL会优先选择独立索引，因为这样一个B+树数据页中可能包含更多符合条件的数据。

BUT WHY???? 我的MySQL大脑缺氧了吗？

覆盖索引

因为辅助索引中保存的是(索引列1, 索引列2, 主键列1, 主键列2)，如果通过查找辅助索引，并且SELECT需要展示的结果列都在这个辅助索引里，那么就不用再去查询聚集索引了。

比如：

SELECT 主键列1 FROM t WHERE 索引列1 = 'xxx';

覆盖索引的另一个好处是，MySQL优化器遇到count(*)操作会去统计辅助索引，因为相比统计聚集索引（统计整个表），它更小，需要读取的磁盘块更少。

一般情况下，对于辅助索引(a,b)，条件中只查询b时，优化器不会考虑使用辅助索引，因为没什么意义。但当进行统计时，也会用到，同样的原因，因为这个索引更小嘛~

优化器选择不使用索引的情况

比如这条语句，优化器可能选择不使用索引，就算你已经建立了orderid上的辅助索引和(orderid, productid)上的联合辅助索引。

原因是查询要返回所有字段，覆盖索引并不能生效，所以辅助索引还需要一次读取书签并再次查找聚集索引的操作，这会把原本顺序的磁盘操作打乱成一些离散的磁盘操作，即从读辅助索引跳到读聚集索引，再跳回辅助索引，再跳回聚集索引，这会徒增磁盘搜索时间，所以当你要查询相当大一批数据（一般是表的20%）时，MySQL会选择直接选择全表扫描，而放弃使用辅助索引。

现在的服务器大都是SSD，最垃圾的服务器也上SSD了，所以完全没有磁盘搜索时间，这时可以考虑使用如下语句强制使用索引。

索引提示

如果MySQL不能很好的选择索引（一般情况下不存在），或者选择索引所消耗的时间已经大于查询时间（一般在分析范围查询时），可以考虑使用索引提示，告诉MySQL该使用什么索引。

1. USE INDEX(x) ：告诉MySQL可以使用索引x，但具体使用哪个还是MySQL来决定
2. FORCE INDEX(x) ：强制使用索引x

Multi-Range-Read MRR优化

Multi-Range-Read主要用于优化上面所说的优化器不使用索引的情况。MySQL5.6开始支持。

不使用索引的原因很明了了，因为会InnoDB的辅助索引特性会导致产生随机的磁盘访问。

MRR优化对于这种情况做了如下优化：

1. 先将读取到的辅助索引键值放到缓存中，这时缓存中的数据按照辅助索引排序
2. 将缓存中的数据按照Rowid排序
3. 根据Rowid排序的顺序访问聚集索引

MRR优化还会将联合辅助索引拆分查询。比如有如下索引(key_part1, key_part2)

之前，SQL优化器会使用索引，将key_part1在指定范围内的都取出，而不管key_part2是否满足，这样就会有一些无用数据被取出。MRR会将查询条件拆分为(1000, 10000), (1001, 10000)...(1999, 10000)这样的键值对，然后根据这些条件进行查询。

Index Condition Pushdown ICP优化

MySQL 5.6开始支持。即把进行索引搜索时，将where条件的过滤下放到存储引擎层（如果可以的话），这样存储引擎与SQL服务器之间的数据传输和转换就会减少了。

比如表中有联合所有(zip_code, last_name, first_name)

如果不支持ICP的时候，可以使用联合索引提取出所有zipcode=95054的记录，但是对于后面两个模糊查询，索引无能为力，需要在SQL层处理，这无形之间增加了SQL服务器层面的压力以及引擎层向SQL服务器层递交数据(需要转换)的压力。

开启了ICP后，会在引擎层面过滤这些数据，即取到zipcode=95054的索引项时就对另外两个条件进行测试，如果不满足就放弃它。前提是只能对索引覆盖到的列进行测试。

自适应哈希索引

略。

全文索引

以前一直没学过全文索引，先看看全文索引是啥东西。

全文索引用于从大段文字中匹配特定字符串。在B+树索引情况下，如果你想进行这样的匹配是可以的

SELECT * FROM t WHERE c LIKE 'abc%';

B+树索引的有序性能够迅速找到所有c列以abc开头的记录，但是这样的匹配是不行的

SELECT * FROM t WHERE c LIKE '%abc';

也是由于B+树的特性，我们无法查找以什么结尾，或者在字符串中间包含什么的记录。那这种情况下，如果没有全文索引就只能来个全表扫描。

InnoDB最初不支持全文索引，如果有的表需要使用全文索引功能，则必须在MyISAM下建立，MySQL5.6时，InnoDB才添加了全文索引功能。

倒排索引

全文索引的一种实现方式就是倒排索引(Inverted Index)，这个翻译很差，不如翻译成反向索引。它的原理就是对于一大段文档，先进行拆分，拆分成小的单词，然后利用一张辅助表(Auxiliary Table)保存这些小单词所在的文档和位置，当用户利用某些单词进行搜索时，就可以利用倒排索引的辅助表进行搜索。

如上是InnoDB的辅助表，它将文档保存成单词，然后记录了单词所在的文档id，位置以及该单词在其他文档中出现的情况。

使用全文索引

创建表，并在body字段上使用全文索引。WITH PARSER ngram指定了分词器，ngram是一个MySQL 5.7.6版本中内置的支持亚洲语种的分词器。

CREATE TABLE test_fts1(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  body TEXT,
  FULLTEXT (body) WITH PARSER ngram
);

插入数据：

INSERT INTO test_fts1 (`body`) VALUES ('Nine days old');
INSERT INTO test_fts1 (`body`) VALUES ('Some like it hot, some like it cold');
INSERT INTO test_fts1 (`body`) VALUES ('Some like it in the pot');
INSERT INTO test_fts1 (`body`) VALUES ('Nine days old');
INSERT INTO test_fts1 (`body`) VALUES ('I like code days');
INSERT INTO test_fts1 (`body`) VALUES ('I like code days');
INSERT INTO test_fts1 (`body`) VALUES ('如果不支持ICP的时候，可以使用联合索引提取出所有`zipcode=95054`的记录，但是对于后面两个模糊查询，索引无能为力，需要在SQL层处理，这无形之间增加了SQL服务器层面的压力以及引擎层向SQL服务器层递交数据(需要转换)的压力。');

查询数据：

SELECT * FROM test_fts1 
WHERE MATCH(body) AGAINST ('联合索引');

查看这条语句的执行计划，确实使用了全文索引。

InnoDB全文索引原理

之前说到了InnoDB使用辅助表来保存倒排索引的单词。InnoDB对这个表进行了分区操作，一共有6个文件。

同时，InnoDB还使用了FTS Index Cache(全文检索索引缓存，以下简称FIC)结构在内存中，提高全文索引的性能。它是一个红黑树结构，使用(word, doc_id, position)存储。

有了缓存就意味着对全文索引列的更新可能并不能及时反映到分词表上，可能还在FIC中，当对全文索引进行查询时，首先将FIC中对应的word合并到辅助表中，然后再进行查询，除此之外，InnoDB还会定时批量合并FIC数据到辅助表，当数据库关闭时会合并所有数据到辅助表。

可以通过以下语句查看辅助表

SET GLOBAL innodb_ft_aux_table='test_db/test_fts1';

SELECT * FROM information_schema.INNODB_FT_INDEX_CACHE;

FTS Document ID

前面一直说倒排索引是通过记录单词所在的文档id实现的，那么文档ID到底是啥？首先明确，我们所说的文档，只不过是一个行中的某些大文本字段。

当你创建一个全文索引时，InnoDB会自动创建一个名为FST_DOC_ID的列，这个列用于保存每一个文档所对应的ID，并且该列还会被自动加上名为FST_DOC_ID_INDEX的唯一索引。这个列和索引对用户不可见，不过用户可以在建表之初自行创建这个列，注意这个列是BIGINT类型，如果指定其他类型会报错。

删除文档

删除文档时首先删除FIC中的缓存数据，而非删除辅助表中数据。当FIC中的一个记录被删除，InnoDB会把它记录在INNODB_FT_DELETED表中，可以查看它。

DELETE FROM test_fts1 WHERE id=6;

SELECT * FROM information_schema.INNODB_FT_DELETED;

注意，所有这些INNODB_FT什么的表，和全文索引有关的，都需要先设置innodb_ft_aux_table字段才能查看。

相关性

全文检索的返回根据相关性排序，相关性由如下条件决定：

1. 单词在文档中出现的次数
2. 单词在索引列中的数量
3. 多少个文档包含该单词

  SELECT body, 
      MATCH(body) AGAINST ('cold') AS Relevance
  FROM test_fts1;

我们在查询时的结果总是相关性越高的越靠前。

全文检索模式

MySQL支持两种全文检索模式，一种是NATURAL，也就是默认的模式，它的功能很简单，查询带有指定单词的文档。

第二种是BOOLEAN模式，允许使用一个表达式来进行全文检索。

SELECT * FROM test_fts1
WHERE MATCH(body) AGAINST ('+cold' IN BOOLEAN MODE)\G;

上面的语句会返回文档中必须包含cold的行

SELECT * FROM test_fts1 WHERE MATCH(body) AGAINST ('+days -old' IN BO
OLEAN MODE)\G;

如上的语句返回文档中必须包含days且必须不包含old的行

SELECT * FROM test_fts1 WHERE
MATCH(body) AGAINST ('"code days"' IN BOOLEAN MODE)\G;

如上的语句返回文档中必须包含code days这一短语的行

Stopword

有一些无意义的单词，比如的，the...

MySQL提供StopWord，在其中的单词不会增加相关性。

可以通过如下语句查看默认的Stopword

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;