删除索引命令_【总结】MySQL技术内幕五：InnoDB的索引与算法

最新推荐文章于 2023-03-10 10:34:55 发布

weixin_39947908

最新推荐文章于 2023-03-10 10:34:55 发布

阅读量183

点赞数

文章标签：删除索引命令

本文链接：https://blog.csdn.net/weixin_39947908/article/details/111695384

版权

索引是应用程序设计和开发中一个重要技术。索引太多或太少，对应用程序的性能都会产生影响。找到合适的平衡点，对应用程序的性能至关重要。

5.1 InnoDB索引概述

InnoDB支持以下常见的索引：

B+树索引
全文索引
哈希索引

InnoDB支持的哈希索引是自适应的，根据表的使用情况自动为表生成哈希索引，即自适应哈希（AHI）。
B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引。
B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。
B+树索引是最为常见、使用最为频繁的一种索引。了解B+树之前必须掌握二叉查找树、平衡二叉树、二分查找法等数据结构和算法，本文不展开讨论。

5.2 B+树

B+树由B树和索引顺序访问方法（ISAM，也是MyISAM引擎最初参考的数据结构）演化而来，但在现实使用过程中几乎已经没有使用B树了。
B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。它所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。
下图演示了一个B+树，其高度为2，每页可存放4条记录，扇出（fan out）为5。

图 5-1

5.2.1 B+树的插入操作

B+树的插入必须保证插入后叶子节点中的记录依然排序。向B+树插入数据时会有下表中三种情况，分别对应不同的插入算法。

下面实例演示B+树的插入过程。1. Leaf Page没满且Index Page没满
向前面的B+树中插入键值28，可直接得到如下B+树：

图 5-2

2. Leaf Page满且Index Page没满
再向该B+树插入键值70，此时需要根据中间值60拆分叶子节点，得到如下B+树：

图 5-3

3. Leaf Page满且Index Page满
最后向该B+树插入键值95，叶子节点和索引节点都需要拆分，得到如下B+树：

图 5-4

为了保持平衡对于新插入的键值可能需要做大量的拆分页（split）操作。因为B+树结构主要用于磁盘，页的拆分意味着磁盘的操作，所以应该在可能的情况下尽量减少页的拆分操作。因此，B+树同样提供了类似于平衡二叉树的旋转（Rotation）功能。4. B+树旋转
旋转发生在Leaf Page已经满，但是其的左右兄弟节点没有满的情况下。这时B+树不会急于去做拆分页的操作，而是将记录移到所在页的兄弟节点上。在通常情况下，左兄弟会被首先检查用来做旋转操作（注：从源码来看，检查且只检查右兄弟节点来做旋转操作。我对源码理解错了？）。
我们再来看前面插入键值70的情况，B+树并不会急于去拆分叶子节点，而是去做旋转操作，得到如下B+树，将部分记录移到了左兄弟节点上，并且更新Index Page的键值。

图 5-5

采用旋转操作使B+树减少了一次页的拆分操作，B+树的高度保持不变。
5. 源码

索引节点拆分和插入函数：btr_page_split_and_insert()
旋转操作函数：btr_insert_into_right_sibling()

5.3.2 B+树的删除操作

B+树使用填充因子（fill factor）来控制树的删除变化，50%是填充因子可设的最小值。B+树的删除操作同样必须保证删除后叶子节点中的记录依然排序。B+树的删除操作需要考虑下表中的三种情况：

对前面“图 5-4”的B+树进行删除操作，假设填充因子为50%。
先删除键值70，符合表中第一种情况，得到如下B+树：

图 5-6

再删除键值60的。删除Leaf Page中键值为60的记录后，填充因子小于50%，这时需要做合并操作，同样，在删除Index Page中相关记录后需要做Index Page的合并操作，与插入时的旋转操作一样，最后得到如下B+树：

图 5-7

源码

删除页中行记录函数：page_cur_delete_rec()
节点合并函数：btr_compress()

5.3 B+树索引

B+树索引在数据库中有一个特点是高扇出性，因此，B+树的高度一般都在2～4层，就是说查找某一键值的行记录时最多只需要2到4次IO。一般的机械磁盘每秒至少可以做100次IO，2～4次的IO意味着查询时间0.02～0.04秒。
B+树索引分为聚集索引（clustered inex）和辅助索引（secondary index），但不管是索引，其内部都是B+树的。不同的是，聚集索引的叶子节点存放的就是行记录数据，而辅助索引的叶子节点存放的是键值和一个书签（行数据的聚集索引键），需要再次查询才能找到行记录数据。对高扇出性的理解
扇入：是指直接调用该模块的上级模块的个数。扇入高表示模块的复用程序高。
扇出：是指该抄模块直接调用的下级模块的个数。扇出高表示模块的复杂度高，需要控制和协调过多的下级模块。
根据上面扇出的定义，这里说的B+树具有高扇区性，应该是指B+树的子节点较多。

5.3.1 聚集索引

聚集索引是按照每张表的主键构造一棵B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，左右兄弟节点的数据页都通过一个双向链表来进行链接。
聚集索引对于主键的排序查找和范围查找速度非常快。
聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

5.3.2 辅助索引

辅助索引的叶子节点除了包含键值以外，还包含了一个书签（bookmark）。该书签就是相应行数据的聚集索引键。

通过辅助索引查找数据时，InnoDB会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。辅助索引要比聚集索引多一倍的IO访问次数。

5.3.3 B+树索引的分裂

Page Header中有以下几个部分用来保存插入的顺序信息：

□ PAGE_LAST_INSERT
□ PAGE_DIRECTION
□ PAGE_N_DIRECTION

（这部分没太明白，应该是btr_page_split_and_insert()函数中的一些逻辑，前面介绍的“B+树的插入操作”也是分裂的一部分。）

5.3.4 B+树索引的管理

1. 索引管理
看如下例子：

create table t1(
  a int NOT NULL, 
  b int, 
  c int NOT NULL,
  unique key(c),
  unique key(a),
  key idx_a_c (a,b)
);

命令SHOW INDEX结果中每列的含义：

Table：索引所在的表名。
Non_unique：非唯一的索引，可以看到primary key是0，因为必须是唯一的。
Key_name：索引的名字，用户可以通过这个名字来执行DROP INDEX。
Seq_in_index：索引中该列的位置，如果看联合索引idx_a_c就比较直观了。
Column_name：索引列的名称。
Collation：列以什么方式存储在索引中。可以是A或NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL了。因为Hash根据Hash桶存放索引数据，而不是对数据进行排序。
Cardinality：非常关键的值，表示索引中唯一值的数目的估计值。Cardinality表的行数应尽可能接近1，如果非常小，那么用户需要考虑是否可以删除此索引。
Sub_part：是否是列的部分被索引。如果显示100，表示只对列的前100字符进行索引。如果索引整个列，则该字段为NULL。
Packed：关键字如何被压缩。如果没有被压缩，则为NULL。
Null：是否索引的列含有NULL值。
Index_type：索引的类型。InnoDB存储引擎只支持B+树索引，所以这里显示的都是BTREE。
Comment：注释。

Cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引。这个值不是实时更新的，因为代价太大了。因此这个值是不太准确的，是个大概的值。后面不会详细介绍。
Cardinality为NULL，在某些情况下可能会发生索引建立了却没有用到的情况。建议在一个非高峰时间，对核心表做ANALYZETABLE操作，这能使优化器和索引更好地为你工作。2. Fast Index Creation
添加或者删除索引的DDL操作，MySQL的操作过程为：

首先创建一张新的临时表，表结构为通过命令ALTER TABLE新定义的结构。
然后把原表中数据导入到临时表。
接着删除原表。
最后把临时表重名为原来的表名。

若对于一张大表进行索引的DDL操作，那么需要很长的时间，会导致服务不可用。
InnoDB支持Fast IndexCreation（快速索引创建）的索引创建方式——简称FIC。FIC方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表。
对于辅助索引的创建，InnoDB对创建索引的表加上一个S锁。在创建的过程中，不需要重建表，因此速度较之前提高很多，并且数据库的可用性也得到了提高。删除辅助索引操作更简单，只需更新内部视图，并将辅助索引的空间标记为可用，同时删除MySQL数据库内部视图上对该表的索引定义即可。3. Online Schema Change
Online Schema Change（在线架构改变，简称OSC），由Facebook实现的一种在线执行DDL的方式，并广泛地应用于Facebook的MySQL数据库。所谓“在线”是指在事务的创建过程中，可以有读写事务对表进行操作，这提高了原有MySQL数据库在DDL操作时的并发性。OSC采用PHP脚本来现实，有一定的局限性。（实现原理这里不做详细介绍）4. Online DDL
Online DDL（在线数据定义）操作，允许辅助索引创建的同时，还允许其他诸如INSERT、UPDATE、DELETE这类DML操作，这极大地提高了MySQL在生产环境中的可用性。
以下这几类DDL操作都可以通过“在线”的方式进行操作：

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

InnoDB实现Online DDL的原理是在执行创建或者删除操作的同时，将INSERT、UPDATE、DELETE这类DML操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上，以此达到数据的一致性。

5.4 Cardinality值

并不是在所有的查询条件中列都需要添加索引。什么时候需要添加B+树索引，一般的经验是，在访问表中很少一部分时使用索引才有意义。对于性别字段、地区字段、类型字段，它们可取值的范围很小，称为低选择性，就完全没有必要的索引。如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则最适合添加索引。
可以通过SHOW INDEX结果中的列Cardinality来观察查看索引是否是高选择性的。Cardinality值非常关键，表示索引中不重复记录数量的预估值，注意是一个预估值。
在实际应用中，Cardinality/n_rows_in_table应尽可能地接近1。
数据库是怎样来统计Cardinality的呢？因为MySQL中有各种不同的存储引擎，而每种存储引擎对于B+树索引的实现又各不相同，所以对Cardinality的统计是放在存储引擎层进行的。
在生产环境中，索引的更新操作可能非常频繁的，以及表的数据量通常比较大，可见实时统计Cardinality是不能接受的。因此，数据库对于Cardinality的统计都是通过随机采样法来完成的。
在INSERT和UPDATE操作时，都会更新Cardinality统计信息，其策略为：

自上次统计Cardinality信息后，表中1/16的数据已发生过变化。
stat_modified_counter>2000 000000，这是针对有频繁更新操作，但表中的数据并没有增加的情况。stat_modified_counter是一个计数器，用来表示发生变化的次数。

默认InnoDB对8个叶子节点（Leaf Page）进行采样，采样的过程如下：

取得B+树索引中叶子节点的数量，记为A。
随机取得B+树索引中的8个叶子节点。统计每个页不同记录的个数，即为P1，P2，…，P8。
根据采样信息给出Cardinality的预估值：Cardinality=（P1+P2+…+P8）*A/8。

因为是随机取8个叶子节点，所以会存在每次得到的Cardinality值可能不同的现象。
相关设置参数如下表：

5.5 B+树索引的使用

5.5.1 不同应用中B+树索引的使用

在OLTP应用中，查询操作只从数据库中取得一小部分数据，如根据主键值来取得用户信息，根据订单号取得订单的详细信息，这都是典型OLTP应用的查询语句。在这种情况下，建立B+树索引才是有意义的，否则即使建立了，优化器也可能选择不使用索引。
在OLAP应用中，都需要访问表中大量的数据来分析出一个宏观结果，目的是为决策者提供支持。如这个月每个用户的消费情况，销售额同比、环比增长的情况等等。但是对于OLAP中的复杂查询，要涉及多张表之间的联接操作，也可能需要根据时间维度筛选数据，因此索引的添加依然是有意义的。但是，如果联接操作使用的是HashJoin，那么索引可能又变得不是非常重要了，所以这需要DBA或开发人员认真并仔细地研究自己的应用。

5.5.2 联合索引

联合索引是指对表上的多个列进行索引。如下例子创建了一个idx_a_b的联合索引。

create table t1(
  a int NOT NULL, 
  b int,
  key idx_a_b (a,b)
);

从本质上来说，联合索引也是一棵B+树，键值也都是排序的，第二个键值也进行了排序处理。不同的是联合索引的键值的数量不是1，而是大于等于2。下图是多个键值的B+树：

查找规则：最左前缀原则。各查找情况分析如下：

全列匹配：当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。需要注意的是，理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引。
最左前缀匹配：当查询条件精确匹配索引的左边连续一个或几个列时，所以可以被用到，但是只能用到一部分，即条件所组成的最左前缀。
查询条件未指定索引中间的某个条件：最左连续的索引会用到，但未使用之后的索引列无法使用。
查询条件没有指定索引第一列：不是最左前缀，索引失效。
匹配某列的前缀字符串：如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀
范围查询：范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。
查询条件中含有函数或表达式：如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引（虽然某些在数学意义上可以使用）

5.5.3 覆盖索引

覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
其包含了主键信息，因此其叶子节点存放的数据类似于（primary key1，primary key2，…，key1，key2，…）。即查询语句只需要主键信息时，仅使用一次辅助联合索引来查询就能得到结果。
另外，对于select count(*) from t1的统计查询，InnoDB会选择辅助索引来统计，因为辅助索引远小于聚集索引，可以减少IO操作。

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

范围查找、JOIN链接操作等情况，优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是扫描全表来得到数据。
原因在于要选取的数据是整行信息，而辅助索引不能覆盖要查询的信息，还需要一次书签访问来查找整行数据的信息，而且书签查找的数据是无序的，变为了磁盘上的离散读操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整表数据的比例较大时（一般是20%左右），优化器会选择通过聚集索引来查找数据。因为顺序读要远远快于离散读。
如果使用了固态硬盘，随机读操作非常快，同时有足够的自信来确认使用辅助索引可以带来更好的性能，那么可以使用关键字FORCE INDEX来强制使用某个索引。

5.5.5 索引提示

持索引提示（INDEX HINT），显式地告诉优化器使用哪个索引。个人总结以下两种情况可能需要用到INDEX HINT：

MySQL优化器错误地选择了某个索引，导致SQL语句运行的很慢。这种情况在最新版本中非常非常的少见。优化器在绝大部分情况下工作得都非常有效和正确。
某SQL语句可以选择的索引非常多，这时优化器选择执行计划时间的开销可能会大于SQL语句本身。此时需要分析最优的索引选择，通过Index Hint来强制使优化器不进行各个执行路径的成本分析，直接选择指定的索引来完成查询。

USE INDEX只是告诉优化器可以选择该索引，实际上优化器还是会再根据自己的判断进行选择。

5.5.6 Multi-Range Read优化

Multi-Range Read（MRR）优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound（IO密集）类型的SQL查询语句可带来性能极大的提升。MRR优化可适用于range（索引的范围查询）、ref（非唯一索引）、eq_ref（主键或者唯一索引）类型的查询。
对于InnoDB和MyISAM的范围查询和JOIN查询操作，MRR的工作方式如下：

将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
将缓存中的键值根据RowID进行排序。
根据RowID的排序顺序来访问实际的数据文件。

MRR优化有以下好处：

MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
减少缓冲池中页被替换的次数。
批量处理对键值的查询操作。

此外，MRR还可以将某些范围查询拆分为键值对，以此来进行批量的数据查询。这样做的好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据。

5.5.7 Index Condition Pushdown 优化

通常情况下，当进行索引查询时，首先根据索引来查找记录，然后再根据WHERE条件来过滤记录。
在支持Index Condition Pushdown（ICP）后，MySQL会在取出索引的同时，判断是否可以进行WHERE条件的过滤，也就是将WHERE的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库的整体性能。
ICP优化支持range、ref、eq_ref、ref_or_null类型的查询，当前支持MyISAM和InnoDB存储引擎。

5.6 哈希算法

哈希算法是一种常见算法，时间复杂度为O(1)。
哈希表（Hash Table）也称散列表，由直接寻址表改进而来。通过下面两个图来简单比较下直接寻址和哈希表，相关资料很多，这里不做详细介绍。
直接寻址表：

哈希表：

5.6.1 InnoDB中的哈希算法

InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。对于缓冲池页的哈希表来说，在缓冲池中的Page页都有一个chain指针，它指向相同哈希函数值的页。而对于除法散列，m的取值为略大于2倍的缓冲池页数量的质数。
InnoDB的缓冲池对页的查找过程：

表空间都有一个space_id；
要查询的应该是某个表空间的某个连续16KB的页，即偏移量offset；
将space_id左移20位，然后加上这个space_id和offset，即关键字 K = space_id << 20 + space_id + offset；
然后通过除法散列到各个槽中去。

（源码中哈希表的查找过程应该对应的是宏HASH_SEARCH，哈希函数应该是hash_calc_hash()，似乎跟上面的介绍不一样）

5.6.2 自适应哈希索引

自适应哈希索引就是用哈希表实现的，查找非常快速。由数据库内部创建并使用的，DBA和开发人员不能对其进行干预。哈希索引只能用来搜索等值的查询，对于范围查找无能为力。

5.7 全文检索

在很多应用场景，经常需要查询字段中包含某个单词，如：

select * from blog where content like '%xxx%';

根据B+树索引的特性，上述SQL语句，即便添加了索引也是需要进行索引的扫描来得到结果。类似这样的需求在互联网应用中还有很多，典型的就是搜索引擎和电商系统的商品查询。
全文检索（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。
InnoDB 从1.2.x版本开始支持全文检索。

5.7.1 倒排索引

全文检索通常使用倒排索引（inverted index）来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表（auxiliary table）中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。
通常利用关联数组实现，其拥有两种表现形式：

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

下面看个例子。
全文检索表t：

inverted file index的关联数组：

full inverted index的关联数组：

5.7.2 InnoDB全文检索的实现

InnoDB 从1.2.x版本开始支持全文检索，采用fullinverted index的方式。
InnoDB将（DocumentId，Position）视为一个“ilist”，在全文检索的表中，这个表称为Auxiliary Table（辅助表），有word字段和ilist字段两列，并且在word字段上有设有索引。
为了提高全文检索的并行性能，共有6张辅助表，目前每张表根据word的Latin编码进行分区。辅助表是持久的表，存放于磁盘上。
当前全文检索还存在以下的限制：

每张表只能有一个全文检索的索引。
由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则。
不支持没有单词界定符（delimiter）的语言，如中文、日语、韩语等。

FTS Index Cache
InnoDB的全文索引使用了FTS Index Cache（全文检索索引缓存），用来提高全文检索的性能。FTS Index Cache是一个红黑树结构，其根据（word，ilist）进行排序。
插入数据时，先将分词写入到FTS Index Cache中，但不会马上更新辅助表，而是批量更新辅助表。
查询时，辅助表会先将在FTS Index Cache中对应的word字段合并到辅助表中，然后再进行查询。由于其根据红黑树排序后进行批量插入，其产生的辅助表相对较小。
InnoDB允许用户查看指定辅助表中分词的信息，可以通过设置参数innodb_ft_aux_table来观察倒排索引的辅助表。
当数据库关闭时，在FTS Index Cache中的数据库会同步到磁盘上的辅助表中。当数据库突然宕机时，可能会导致FTS Index Cache中的数据未同步到辅助表中。数据库重启时，当用户对表进行全文检索时，InnoDB会自动读取未完成的文档，然后进行分词操作，再保存到FTS Index Cache中。
参数innodb_ft_cache_size用来控制FTS Index Cache的大小，默认值为32M。当该缓存满时，会同步到磁盘的辅助中。增大缓存大小可以提高性能，但是在宕机时，恢复时间也更长。FTS Document ID
为了支持全文检索，必须有一列与word进行映射。该列被称为FTS_DOC_ID，其类型必须是BIGINT UNSIGNED NOT NULL，并且自动会在该列上加入名为FTS_DOC_ID_INDEX的Unique Index。这些操作都由InnoDB存储引擎自己完成。由于FTS_DOC_ID的列具有特殊意义，创建时必须注意相应的类型，否则会报错。stopword列表
stopword列表（stopword list）表示该列表中的word不需要对其进行索引分词操作，如the、is、what等。
InnoDB有一张默认的stopword列表，默认有36个stopword，位于information_schema架构的INNODB_FT_DEFAULT_STOPWORD表中。此外也可以通过参数innodb_ft_server_stopword_table来自定义stopword列表。索引的删除
由于文档的DML操作实际并不删除索引中的数据，相反还会在对应的DELETED表中插入记录，因此索引会变得非常大，即使索引中的有些数据已经被删除，查询也不会选择这类记录。为此，InnoDB支持手工地将已经删除的记录从索引中彻底删除，该命令就是OPTIMIZE TABLE。因为OPTIMIZE TABLE还会进行如Cardinality的重新统计等其他操作，如果只希望仅对倒排索引进行操作，可以通过参数innodb_optimize_fulltext_only进行设置。
若被删除的文档非常多，那么OPTIMIZE TABLE操作可能需要占用非常多的时间，这会影响应用程序的并发性，并极大地降低用户的响应时间。用户可以通过参数innodb_ft_num_word_optimize来限制每次实际删除的分词数量。该参数的默认值为2000。示例
表：

create table t_fts(
  FTS_DOC_ID bigint unsigned auto_increment not null,
  content text,
  primary key(FTS_DOC_ID)
);

create fulltext index idx_fts on t_fts(content);

-- 设置查看分词信息
set global innodb_ft_aux_table = 'deep_demo/t_fts';

-- 查看分词信息（我测试的版本有时在这个表里 information_schema.INNODB_FT_INDEX_CACHE）
SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE;

数据：

insert into t_fts select NULL, "Hello world";
insert into t_fts select NULL, "Are you OK?";
insert into t_fts select NULL, "To learn about what’s new read the MariaDB release notes.";
insert into t_fts select NULL, "Getting started";
insert into t_fts select NULL, "Introduction to relational databases";
insert into t_fts select NULL, "A 10-minute MariaDB primer";
insert into t_fts select NULL, "List of SQL statements";
insert into t_fts select NULL, "Useful MariaDB queries";
insert into t_fts select NULL, "Presentation and videos";

表数据：

分词信息：

删除一条记录，分词信息没表，在DELETED表记录下来了：

执行OPTIMIZE TABLE t_fts命令，已删除的记录的分词信息被彻底删除，DELETED表中的记录也被删了。

补充：根据提示，OPTIMIZE TABLE {table_name}不再支持，推荐使用alter table {table_name} engine=innodb。

5.7.3 InnoDB全文检索的查询

MySQL通过MATCH（）…AGAINST（）语法支持全文检索的查询，MATCH指定了需要被查询的列，AGAINST指定了使用何种方法去进行查询。
语法如下：

查询模式有：
1、IN NATURAL LANGUAGE MODE：expr就是要搜寻的字符串，默认。
2、IN BOOLEAN MODE：expr里有特殊字符辅助特殊的搜寻语法。
3、IN NATURAL MODE WITH QUERY EXPANSION：第一次用给定的短语搜索，第二次使用给定的短语结合第一次搜索返回结果中相关性非常高的一些行进行搜索。

1. Natural Language

全文检索通过MATCH函数进行查询，默认采用Natural Language模式，其表示查询带有指定word的文档。比如：

对应的执行计划：

WHERE条件中使用MATCH函数，查询返回的结果是根据相关性（Relevance）进行降序排序的，即相关性最高的结果放在第一位。相关性的值是一个非负的浮点数字，0表示没有任何的相关性。
相关性的计算依据以下四个条件：

word是否在文档中出现。
word在文档中出现的次数。
word在索引列中的数量。
多少个文档包含该word。

下面两条SQL语句，第二条的执行速度可能更快些。因为第一条还需要进行相关性的排序统计，而第二条不需要。

select count(*) from t_fts where match (content) against ('mariadb' IN NATURAL LANGUAGE MODE);

select count(if(match (content) against ('mariadb' IN NATURAL LANGUAGE MODE), 1, NULL)) as count from t_fts;

InnoDB的全文检索还需要考虑以下的因素：

查询的word在stopword列中，忽略该字符串的查询。
查询的word的字符长度是否在区间[innodb_ft_min_token_size，innodb_ft_max_token_size]内。

2. Boolean

使用IN BOOLEAN MODE修饰符时，查询字符串的前后字符会有特殊的含义。
支持以下几种操作符：
□ +表示该word必须存在。
□ -表示该word必须被排除。
□ （no operator）表示该word是可选的，但是如果出现，其相关性会更高。
□ @distance表示查询的多个单词之间的距离是否在distance之内，distance的单位是字节。这种全文检索的查询也称为Proximity Search。如MATCH（body）AGAINST（'"Pease pot"@30' INBOOLEAN MODE）表示字符串Pease和pot之间的距离需。
□ >表示出现该单词时增加相关性。
□ <表示出现该单词时降低相关性。
□ ～表示允许出现该单词，但是出现时相关性为负（全文检索查询允许负相关性）。
□ *表示以该单词开头的单词，如lik*，表示可以是lik、like，又或者likes。
□ "表示短语。
例如：

3. Query Expansion

全文检索还支持的扩展查询。这种查询通常在查询的关键词太短，用户需要implied knowledge（隐含知识）时进行。
例如，对于单词database的查询，可能希望查询的不仅仅是包含database的文档，可能还指那些包含MySQL、Oracle、DB2、RDBMS的单词。
通过在查询短语中添加WITH QUERY EXPANSION或IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION可以开启blind query expansion（又称为automatic relevance feedback）。该查询分为两个阶段。

第一阶段：根据搜索的单词进行全文索引查询。
第二阶段：根据第一阶段产生的分词再进行一次全文检索的查询。

示例：

CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT (title, body)
) ENGINE=InnoDB;

INSERT INTO articles (title, body) VALUES
('MySQL Tutorial', 'DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks', '1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison...'),
('MYSQL Security', 'When configured properly, MySQL...'),
('Tuning DB2', 'For IBM database ...'),
('IBM History', 'DB2 history for IBM ...');

SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('database' IN NATURAL LANGUAGE MODE); -- 3条记录
SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('database' WITH QUERY EXPANSION); -- 8条记录

（其中隐含知识是InnoDB自有的吗？不能用户构建？）

参考

《MySQL技术内幕：InnoDB存储引擎（第2版）》
https://dev.mysql.com/doc/internals/en/innodb.html
源码：MariaDB 10.4.12