MySQL笔记(5)---索引与算法

1.前言

　　本章记录MySQL中的索引机制，了解索引可以让数据库更快。索引太多会造成性能损耗，索引太少肯定查询效率不高。

2.InnoDB存储引擎所有概述

　　InnoDB中常见的索引有：

　　　　B+树索引

　　　　全文索引

　　　　哈希索引

　　哈希索引是自适应的，无法人为干预是否在一张表中生成hash索引。

　　B+树不能找到一个给定键值的具体行，B+树索引能找到的只是被查找数据所在的页，通过将页读取到内存，再定位数据。

3.数据结构与算法

3.1 二分查找法

　　二分查找也就是折半查找，前提是有序，该算法算是一个基础算法了，思想并不复杂。网上相关介绍很多，这里推荐一篇：这里。

　　二分查找在1946年就出现了，但是完全正确的二分查找到1962年才出现，所以虽然简单，要写对不是一件容易的事情。

　　每页的Page Directory中的槽是按照主键的顺序存放的，对于某一条具体记录的查询是通过Page Directory进行二分查找找到的。

3.2 B+树

　　B+树是由二叉查找树->平衡二叉树，再演化而来。

　　二叉查找树是一种经典的数据结构，相关定义可以看这篇文章：这里。

　　平衡二叉树又称AVL树，用于解决二叉查找树的缺陷，与此同时增加了维护平衡二叉树的开销。相关定义看这篇文章：这里。

　　B+树由B树和索引顺序访问方法（ISAM）演化而来，但是实际中B树几乎没有使用的情况了。B+树是为磁盘或者其他存储辅助设备设计的一种平衡查找树。B+树比较复杂，这里也推荐一篇文章慢慢研究：这里。

　　B+树中所有非终端节点都被视为索引节点，而且B+树没有指向关键字的具体指针。这就是前面说的要加载到内存中，进行二分查找的原因，页的设计也就是索引到具体的页。这样导致的结果就是B+树更小，磁盘IO次数更少，一次性读取的关键字数据更多。另外不使用B树使用B+树的根本原因在于B+树更稳定。只需要遍历叶子节点就能实现遍历整个表，这个操作就是全表查询了，B树要实现代价太高，需要遍历所有的节点。

　　B+树插入有3种情况：

　　　　　Leaf Page没满，Index Page没满，直接将记录插入叶子节点

　　　　　Leaf Page满，Index Page没满，需要拆分叶子节点，将中间的节点添加到索引节点，小于中间的节点记录放左边，大于或等于的放右边。

　　　　　Leaf Page满，Index Page满，拆分Leaf Page，小于中间节点的放左边，大于等于的放右边，拆分Index Page,小于中间的放左边，大于中间的放右边，中间节点放入上一次的IndexPage。

　　为了保持B+树的平衡会可能会进行大量的拆分操作，所以尽可能的减少页的拆分操作，B+树同样提供了类似平衡二叉树的旋转功能。旋转发生在Leaf节点已满，但是其左右兄弟节点没有满的情况。这个时候不会急着去拆页，左兄弟会被先用来检查旋转操作，最终填补空缺，减少一次拆页操作。

　　B+树的删除操作：

　　　　根据填充因子来控制数的删除变化，50%是填充因子可设的最小值。

　　　　叶子节点大于等于填充因子，索引大于等于填充因子，直接删除，如果该节点是Index Page的节点，用右节点代替

　　　　叶子节点小于填充因子，索引大于等于填充因子，合并叶子节点和其兄弟节点，同时更新Index Page

　　　　叶子节点小于填充因子，索引小于填充因子，合并叶子节点和其兄弟节点，更新IndexPage，合并IndexPage和其兄弟节点。

4.B+树索引

　　B+树在数据库中有一个特性是高扇出性，所以高度一般都在2~4层。也就是说查询某一键值的行记录最多只需要2到4次IO。一般的机械磁盘每秒至少100次IO，查询只要0.02~0.04秒。

　　B+树索引可以分为聚集索引和辅助索引，叶子节点存放着所有数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

4.1 聚集索引

　　之前说过，InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。聚集索引就是按照每张表的主键构建一个B+树，同时叶子节点中存放的即为整张表的行记录数据，也就称之为数据页。这个特性决定了数据也是索引的一部分。每个数据页通过双向链表来进行链接。

　　由于实际的数据页只能按照一个B+树进行排序，因此每张表只有一个聚集索引，大部分情况，查询优化器都倾向于采取聚集索引，因为能够直接在页上找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快的访问针对范围值的查询。查询优化器可以快速的发现某一段范围的数据页需要扫描。

　　聚集索引并不是在物理地址上连续存储，这样代价太高，只是在逻辑上是连续的。页通过双向链表连接，按照主键顺序排列，物理地址上可以不按主键顺序存储。

　　聚集索引的另一个好处在于，它对于主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。比如查询最后10个记录，由于双向链表就可以快速逆序查找。

4.2 辅助索引

　　对于辅助索引，也称非聚集索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行还包含了一个书签。这个书签就是用来告诉InnoDB存储引擎在哪里可以找到索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

　　辅助索引不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指定主键索引的主键，然后通过主键索引来找到一个完整的行记录。举例就是：如果辅助索引高度为3，那么就需要遍历3次找到指定主键，如果聚集索引树的高度也是3，那么还需要进行3次查询，最终6次逻辑IO访问得到一个数据页。

　　对于其他的数据库，比如Microsoft SQL Server数据库，还有一种称为堆表的表类型，所有数据存储按插入顺序存放，和MyISAM存储引擎有些类似。堆表的特性决定了堆表上的索引都是非聚集的，主键与非主键的区别只是是否唯一且为空。堆表的书签可以比主键书签方式更快，并且非聚集可能在一张表中存在多个，需要对多个非聚集进行查找，为什么要使用索引组织表的方式呢？

　　大部分原因在于OLAP（在线分析处理）的应用，其次就是表中数据是否需要更新，并且更新是否影响了物理地址的变更。此外就是排序和范围查找的影响了，堆的特性决定无法像B+树一样进行范围读取。最后，非聚集索引的离散读，可以通过预读技术来避免多次的离散读操作。由需求决定采取用哪种。

4.3 B+树索引的分裂

　　这个是最难的一部分。页的分裂不总是从中间记录开始的，这样会导致页空间的浪费。比如：

　　　　1、2、3、4、5、6、7、8、9、10

　　如果从中间分裂，左边1、2、3、4、5，之后这边的空间就被全部浪费了，因为新的数据都在右边。

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

　　　　PAGE_LAST_INSERT

　　　　PAGE_DIRECTION

　　　　PAGE_N_DIRECTION

　　通过这些信息，InnoDB存储引擎可以决定是向左还是向右进行分裂，同时决定分裂点记录为哪一个。

4.4 B+树索引的管理

4.4.1 索引管理

　　创建删除的方法有两种：一种是ALTER TABLE，另一种是CREATE/DROP INDEX。

　　ALTER TABLE table_name

　　| ADD {INDEX|KEY} [index_name]

　　[index_type] (index_col_name,...) [index_option]

 

　　ALTER TABLE table_name

　　DROP PRIMARY KEY

　　| DROP {INDEX | KEY} index_name

 

　　CREATE [unique] index index_name [index_type] on table_name (index_col_name,...)

　　DROP INDEX index_name ON table_name

　　可以对整个列进行索引，也可以只索引开头多个个数据。SHOW INDEX from table_name。

　　里面有一个字段是Cardinality，这个值很重要，表示索引中唯一值的数目的估计值。其值除以总记录数应尽可能接近1，如果很小，可以考虑删除这个索引。优化器会根据这个值来决定是否使用索引。不是实时更新的，analyze table table_name可以更新。

4.4.2 Fast Index Creation

　　MySQL 5.5版本前被人诟病的是对于索引的添加或者删除的这类DDL操作，操作过程是：

　　　　先创建一张新的临时表，表结构通过命令ALTER TABLE新定义的结构。

　　　　然后把原表中的数据导入到临时表。

　　　　接着删除原表。

　　　　最后把临时表重名为原来的表。

　　对大表的操作就爆炸了。从InnoDB1.0版本开始支持Fast Index Creation的索引创建方式。

　　对于辅助索引的创建，InnoDB存储引擎会对创建索引的表加上一个S锁，不需要重新建表，所以速度快了很多。删除就更简单了，更新内部视图，并将辅助索引的空间标记为可用，同时删除MySQL数据库内部视图上对该表的索引定义即可。

　　注意，临时表的创建路径是听过参数tmpdir进行设置的，要保证有足够空间，否则会导致创建索引失败。

　　由于加了S锁，因此创建过程只能对该表进行读操作，如果大量的事务需要对目标进行写操作，服务器一样不可用。所以，只限定用于辅助索引，对主键的创建和删除同样需要重建一张表。

4.4.3 Online Schema Change

　　在线架构改变是Facebook实现的一种在线执行DDL的方式，指在事务创建过程中，可以有读写事务对表进行操作，提高了DDL操作时的并发性。采取PHP脚本实现OSC，而不是通过修改InnoDB源码的方式。OSC的作用就是在对表结构改变的过程时，会锁住要改变的表，如果数据量大，这就很糟糕了。其就是解决这个问题。

　　OSC步骤如下：

　　　　init:初始阶段，会对创建表做一些验证工作，如检查表是否有主键，是否存在触发器或者外键。

　　　　createCopyTable：创建和原始表结构一样的新表

　　　　alterCopyTable：对创建的新表进行ALTER TABLE操作，如添加索引或列等

　　　　createDeltasTable：创建deltas表，该表的作用是为下一步创建的触发器所使用。之后对原表的DML操作都会被记录到createDeltasTable中。

　　　　createTriggers:对原表创建INSERT、UPDTAE、DELETE操作的触发器。将操作记录记录大deltas表。

　　　　startSnpshotXact：开始OSC操作的事务

　　　　selectTableIntoOutfile:将原表数据写入新表。为了减少对原表的锁定时间，这里通过分片将数据输出到多个外部文件，然后将外部文件的数据拷贝到表中。

　　　　dropNCIndexs:在导入到新表前，删除新表中所有辅助索引

　　　　loadCopyTable:将导出的分片文件导入到新表

　　　　replayChanges:将OSC过程中原表DML操作的记录应用到新表中，这些记录都保存在deltas表中

　　　　recreateNCIndexes:重新创建辅助索引

　　　　replayChanges:再次进行DML日志的回放操作，这些日志是在上述创建辅助索引过程中新产生的日志。

　　　　swapTables：将原表和新表交换名字，整个操作需要锁定两张表，不允许新的数据产生，改名是一个很快的操作，所以阻塞的时间非常短。

　　脚本很复杂，知识点很多。有局限性，允许set sql_bin_log=0，所以操作不会同步到slave服务器，导致主从不一致的情况。

4.4.4 Online DDL

　　FIC让InnoDB避免创建临时表，但是索引创建还是会阻塞表上的DML操作。OSC虽然解决了上述部分问题，但是还是有很大的局限性。MySQL5.6版本开始支持在线数据定义Online DDL操作。允许创建索引的过程中，进行DML操作。其他的例如：改变自增长值，添加或删除外键约束，列的重命名都可以进行。新的ALTER TABLE语法如下：

　　　　ALTER TABLE table_name

　　　　| ADD {index|key} [index_name]

　　　　[index_type] (index_col_name,...) [index_option] ...

　　　　ALGORITHM [=] {DEFAULT|INPLACE|COPY}

　　　　LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

　　ALGORITHM指定了创建或者删除索引的算法，copy表示按5.1版本之前的创建临时表的方式，inplace表示索引不需要创建临时表。DEFALUT根据参数old_alter_table参数来判断是使用哪种。

　　LOCK是加锁的情况：

　　　　NONE，不加锁

　　　　SHARE，加一个S锁。

　　　　EXCLUSIVE，加一个X锁

　　　　DEFAULT，自动判断是否可以使用NONE方式，再判断SHARE，继续判断EXCLUSIVE。

　　实现原理是执行创建删除操作的同时，将DML操作日志写缓存，在索引创建完毕，再重做到应用表，达到数据一致性。缓存大小由参数innodb_online_after_log_max_size控制，默认128MB。如果写满，会抛出错误。

5.Cardinality值

5.1什么是Cardinality

　　不是所有的查询条件的列都需要添加索引的，一般经验是在访问表中很少一部分使用B+树索引才有意义。对于性别，只有男女，占表的50%，基本就是全表扫描了，这个时候使用索引就没有多大价值了，这种结果很少的字段称为低选择性。所以高选择性的使用B+树索引较好，比如姓名。

　　如何查看是否具有高选择性呢？通过SHOW INDEX 查看列的Cardinality，这个值表示索引中不重复记录数量的预估值。Cardinality/rows应尽可能接近1，非常小就要考虑删除这个索引了。

5.2 Cardinality统计

　　统计这个值代价是很大的，不同存储引擎的策略不同，一般都采取采用的方式完成。

　　在Innodb中，更新发生在INSERT和UPDATE操作：

　　　　表中的1/16数据已经发生变化。

　　　　stat_modified_counter > 2000000000

　　第一个指标无法监控对一条数据的反复修改，所以才有第二个指标来判断。

　　默认对8个叶子节点进行采样：

　　　　取得B+树索引中叶子节点的数量，记为A

　　　　随机取得B+树索引中的8个叶子节点，统计每个页不同记录的个数。

　　　　合计*A/8

　　参数innodb_stats_sample_pages设置采样数量，默认8

　　参数innodb_stats_method, 用来设置如何对待NULL值。如nulls_equal、nulls_unequal、nulls_ignored。

　　1.2版本有更多的参数：

　　　　innodb_stats_persistent:是否存在磁盘上计算结果。默认OFF

　　　　innodb_stats_on_metadata:是否执行SHOW XXX的时候，重新计算这个值。默认OFF

　　　　innodb_stats_persistent_sample_pages：analyze table更新值时的采样样本数量，默认20.

　　　　innodb_stats_transient_sample_pages:取代之前的innodb_stats_sample_pages。默认8

6.B+树索引的使用

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

　　数据库存在两种类型的应用：OLTP和OLAP应用。

　　OLTP中查询操作只有一小部分数据，一般可能在10条以下，甚至是一条，这种时候建立索引，就可以通过该索引取得表中少部分的数据。B+树索引才有意义。

　　OLAP的情况就比较复杂了，不过概况而言，需要访问表中的大量数据，根据这些数据来产生查询的结果，这些查询多是面向分析的查询，目的是为决策者提供支持。所以不需要对OLAP中的一些字段添加索引，因为其是宏观信息。但是索引是有必要的，因为涉及大量的表连接操作。但是如果表使用的是hash join，那么索引可能又变得不是很重要了。不过很多OLAP应用中，有使用到时间进行索引。

6.2 联合索引

　　联合索引是指对表上的多个列进行索引，前面的都是一个列。联合索引的创建和单个索引的方法一样，不同的就是有多个列。

　　联合索引也是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2。比如：

　　和单个键的情况没什么不同，键都是排序了的。所以对SQL：where a=xxx and b=xxx是显然可以的，单个a=xxx也是可以使用这个索引，但是对单个b=xx就不行了，因为对b而言是无序的。

　　联合索引的另一个好处在于可以对第二个字段进行排序。

6.3 覆盖索引

　　InnoDB支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处就是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，可以减少大量的IO操作。

　　　　select primary key1,primary key2, key2 from table where key1 = xxx

　　覆盖索引的另一个好处是对某些统计问题而言的。比如count(*)。存储引擎并不会选择聚集索引来进行统计，由于还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作。

　　此外，通常情况下（a,b)联合索引不可以选择b中所谓的查询条件，但是如果是统计操作，并且是覆盖索引的，则优化器就会进行选择。

6.4 优化器选择不使用

　　有些情况执行EXPLAIN命令进行SQL语句分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生在范围查找，JOIN链接操作情况下。

　　　　比如select * from orderdetails where orderid > 10000 and orderid < 102000；

　　这个表中有联合索引（orderid, productid），也有对单个列orderid的辅助索引，但是最终优化器并没有选择这两个，而是选择了聚集索引。这是为什么呢？联合索引很好理解，因为我们要查的数据是所有列的内容，联合索引提供不了。单个列的辅助索引为什么没有选择呢？原因在于辅助索引虽然能够定位到数据，但是要通过书签访问真实数据的读取，这个是离散的，变成了磁盘的离散读操作。访问数据小，还是会选择辅助索引，但是数据占整表很大一部分（20%左右），就会采取聚集索引查找数据，因为顺序读远远快于离散读。

6.5 索引提示

　　MySQL数据库支持索引提示，显示地告诉优化器使用哪个索引。以下两种情况可能需要用到INDEX HINT：

　　　　1.MySQL数据库的优化器错误的使用了某个索引，导致SQL运行很慢。在新版之中非常少见

　　　　2.SQL语句可以选择的索引非常多，优化器选择执行计划时间的开销可能会大于SQL语句本身。比如，优化器分析range查询本身就是比较耗时的。

　　select * from t use index(a) where a = 1 and b = 2.     use index只是告诉优化器使用这个，实际决定权还是在优化器手上，不一定会使用这个。

　　select * from t force index(a) where a = 1 and b =2   force index就是强制了。

6.6 Multi-Range Read优化

　　MySQL5.6开始支持Multi-Range Read 优化，目的就是减少磁盘的随机访问，并将随机访问的数据转化为较为顺序的数据访问。优化可用于range、ref、eq_ref类型的查询，好处有：

　　　　使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。

　　　　减少缓冲池中页被替换的次数

　　　　批量处理对键值对的查询操作。

　　对于InnoDB的MyISAM存储引擎的范围查询和JOIN查询操作，MRR的工作方式如下：

　　　　1.将查询得到的辅助索引键值存放在一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。

　　　　2.将缓存中的键值根据rowID进行排序。

　　　　3.根据rowID的排序顺序来访问实际的数据文件。

6.7 Index Condition Pushdown(ICP)优化

　　这个也是5.6版本开始支持的一个特性，一种根据索引进行查询的优化方式。

　　之前版本的MySQL是不支持Index Condition Pushdown，当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录。但是在Index Condition Pushdown之后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤，也就是将where放在了存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch)从而提升整体性能。

　　优化支持range、ref、eq_ref、ref_or_null类型的查询，当前支持MyISAM和InnoDB引擎。当优化器选择Index Condition Pushdown优化的时候，执行计划的Extra看到Using index condition提示。

　　要使用Using index condition必须索引包含过滤条件，不然就无法过滤。

7 哈希算法

7.1 哈希表

　　哈希表也叫散列表，由直接寻址表改进而来。当关键字的全局域U比较小时，直接寻址是一种简单而有效的技术。

　　哈希表比较常用，这里就不进行详细的解释，可以参考文章：这里。

7.2 InnoDB的哈希算法

　　使用哈希算法对字典进行查找，使用链表解决冲突。hash函数采用除法散列方式。

　　对缓冲池页的哈希表来说，Page页都有一个chain指针，指向相同哈希函数值得页。对于除法散列，m的取值为略大于2倍的缓冲池数量的质数。比如：innodb_buffer_pool_size大小为10M，共有640个16KB的页，对于哈希表来说，需要分配640*2=1280，取略大的质数，就是1399。

　　在hash表里面找到下一个页也很简单，通过space_id左移20位加上space_id+offset，再使用hash算法来进行散列。

7.3 自适应哈希索引

　　自适应哈希是数据库自身创建的，不能对其进行干预。通过哈希函数映射到一个表中，查询单个非常快，对范围查找就无能为力了。

　　可以配置innodb_adaptive_hash_index来禁用或启动此特性，默认开启。

8 全文检索

8.1 概述

　　B+树索引的特点前面已经很清楚了，可以通过索引字段的前缀进行查找，比如like xxx%，但是更多时候可能查找中间的字段：

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

　　根据B+树索引的特性，上述SQL语句即便添加了B+树索引也是需要进行索引的扫描来得到结果。这类查找不是B+树索引能够很好的完成的工作。

　　全文检索是将存储于数据库的整本书或者整篇文章中的任意内容信息检索的技术，从InnoDB1.2.x版本开始，支持全文索引。

8.2 倒排索引

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

　　1.inverted file index，其表现形式为{单词，单词所在文档的ID}

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

8.3 InnoDB的全文检索

　　采取full inverted index的方式。将（DocumentId, Position)视为一个“ilist"。所以全文检索中有两个列，一个word字段，一个ilist字段。在word上面设置了索引，由于ilist字段存放了Position信息，所以可以进行Proximity Search，而MyISAM存储引擎不支持该特性。

　　倒排需要将word存放在一张表中，这个表称为Auxiliary Table 辅助表。为了提供并行性能，共有6张这个表，根据word的latin编码进行分区。

　　Auxiliary Table是持久的表，存放在磁盘上。还有一个FTS index cach（全文检索索引缓存）。其用来提高全文检索的性能。FTS Index Cache是一个红黑树结构，其根据(word, ilist)进行排序。这意味着插入数据已经更新了对应的表，但是对全文索引的更新可能在分词操作后还在FTS Index Cache中，Auxiliary Table可能还没有更新。InnoDB会批量对Auxiliary Table进行更新，而不是每次插入后更新一次。查询的时候，自然先将FTS Index Cache合并到Auxiliary Table，然后再查询了。

　　InnoDB允许用户查看指定倒排序索引的Auxiliary Table中分词的信息，可以设置参数innodb_ft_aux_table来观察。SET GLOBAL innodb_ft_aux_table="test/fts_a"。就能通过information schema架构下的表INNODB_FT_INDEX_TABLE得到表fts_a的分词信息。

　　数据库关闭时，Cache中的数据会同步到磁盘的表上，如果宕机没有来得及同步，会在重启时，对全文检索时，自动读取未完成的文档，进行分词操作，再放入Cache中。参数innodb_ft_cache_size用来控制FTS Index Cache的大小，默认32M，缓存满的时候就会将信息同步到磁盘。

　　FTS Document ID是另一个重要的概念。为了支持全文索引，必须有一个列与word进行映射，这个列就被成为FTS_DOC_ID，类型必须是BIGINT UNSIGNED NOT NULL，InnoDB存储引擎会自动加上一个FTS_DOC_ID_INDEX的unique index。

　　文档中分词的插入操作是在事务提交时完成，然而对于删除操作，其在事务提交时，不删除Table中的数据，只是删除FTS Cache Index中的记录。被删除的数据会记录下FTS DOCUMENT ID，保存在DELETED auxiliary table中。设置参数innodb_ft_aux_table后，用户也能访问这个表观察被删除的id。

　　由于不删除数据，反而会加一条删除记录，所以索引会变得非常大，InnoDB提供了一种方式，允许用户手动删除已经删除了的记录，就是OPTIMIZE TABLE。这个命令还会做一些其他操作，比如Cardinality的重新统计，如果只希望对倒排序索引进行操作，可以设置innodb_optimize_fulltext_only属性。如果被删除的记录很多，这个命令就会影响性能，可以设置innodb_ft_num_word_optimize限制每次实际删除的分词数量。默认值是2000.

　　　　create table fts_a (

　　　　　　FTS_DOC_ID　　BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,

　　　　　　body TEXT,

　　　　　　PRIMARY KEY(FTS_DOC_ID)　　

　　　　);

　　上面就创建了一个用于全文检索的保存记录的表了。

　　　　SET GLOBAL innodb_ft_aux_table = "test/fts_a"

　　　　select * from information_schema.INNODB_FT_INDEX_TABLE;

　　上面可以看见分词结果，倒排序索引了。

　　删除一条记录，执行select * from innodb_ft_deleted，就能够查看删除的全文检索文档了。

　　OPTIMIZE TABLE test.fts_a就能够真正删除记录了。

　　stopword列表是最后一个概念，表示该列表中的word不需要进行索引分词操作。比如the这个单词，其没有具体意义，被视为stopword。可以查看information_schema架构下的INNODB_FT_DEFAULT_STOPWORD查看，默认有36个。通过设置参数innodb_ft_server_stopword_table指定自定义的stopword表。

　　此外，全文检索还有以下限制：

　　　　每张表只能有一个全文检索的索引

　　　　由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则。

　　　　不支持没有单词界定符的语言，比如中文、日语、韩语等。

8.4 全文检索

　　语法：

　　　　MATCH （col1,col2,...） AGAINST (expr [search_modifier])

　　　　search_modifier:

　　　　{

　　　　　　IN NATURAL LANGUAGE MODE

　　　　　　| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

　　　　　　| IN BOOLEAN MODE

　　　　　　| WITH QUERY EXPANSION

　　　　}

 　　通过match against语法支持全文检索的查询，match指定了需要查询的列，against指定了使用什么方法进行查询。

8.4.1 Natural Language

　　默认采取的就是这种模式，表示查询带有指定word的文档。

　　select * from fts_a where match(body) against ('xxx' in natural language mode)；

　　这种是默认的模式，可以省略in natural language mode。查询的结果是根据相关性倒序排列的，相关性最高的放在最上面。计算依据是以下四个条件：

　　　　word是否在文档中出现

　　　　word在文档中出现的次数

　　　　word在索引列中的数量

　　　　多少个文档包含word

　　查看相关性，可以在写成 select fts_doc_id, body,  match(body) against ('xxx' in natural language mode) as relevance from fts_a；

　　此外还需要考虑以下因素：

　　　　查询的word在stopword列中，忽略该字符串的查询。

　　　　查询的word字符长度是否在区间[innodb_ft_min_token_size, innodb_ft_max_token_size]内。默认3，84。不在这个范围内的词会被忽略。

8.4.2 Boolean

　　这种模式查询键有特性含义：

　　　　select * from fts_a where match(body) against ('+xxx -yyy' in boolean mode)；

　　+代表必须出现，-代表不能存在

　　(no operator) 表示word是可选的，出现了相关性会更高

　　@distance 表示查询的多个单词之间的距离是否在distance之内，单位是字节。如'"Pease pot"@30'，意味着 Pease和pot要在30个字节内

　　> 表示出现该单词时增加相关性

　　<相反的含义

　　~表示允许出现单词，但是出现时相关性为负

　　* 表示以单词开头的单词，比如lik*

　　" 表示短语

8.4.3 Query Expansion

　　支持全文检索的扩展查询，通常在关键词太短，用户需要implied knowledge（隐含知识）时进行。如查询database时，用户希望包含 MySQL、Oracle等信息。

　　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;

　　这个例子没有显示创建FTS_DOC_ID，所以会自动创建，并添加唯一索引。此外全文检索是根据title和body进行的联合索引。

　　　　select * from articles where match(title, body) against ('database'  with query expansion)

　　这个时候查询出来的结果就不一定包含database了，可能会包含MySQL等与database相关的信息也会被查出来。

　　所以这种模式可能会带来很多非相关性的查询，需要谨慎。