mysql 核心内容-上

1、SQL语句执行流程

MySQL大体上可分为Server层和存储引擎层两部分。

Server层：

连接器：TCP握手后服务器来验证登陆用户身份，A用户创建连接后，管理员对A用户权限修改了也不会影响到已经创建的链接权限，必须重新登陆。

查询缓存：查询后的结果存储位置，MySQL8.0版本以后已经取消，因为查询缓存失效太频繁，得不偿失。

分析器：根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

优化器：多种执行策略可实现目标，系统自动选择最优进行执行。

执行器：判断是否有权限，将最终任务提交到存储引擎。

存储引擎层

负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎(经常用的也是这个)。

SQL执行顺序

2、BinLog、RedoLog、UndoLog

BinLog

BinLog是记录所有数据库表结构变更（例如create、alter table）以及表数据修改(insert、update、delete)的二进制日志，主从数据库同步用到的都是BinLog文件。BinLog日志文件有三种模式。

STATEMENT 模式

内容：binlog 记录可能引起数据变更的 sql 语句

优势：该模式下，因为没有记录实际的数据，所以日志量很少 IO 都消耗很低，性能是最优的

劣势：但有些 *** 作并不是确定的，比如 uuid() 函数会随机产生唯一标识，当依赖 binlog 回放时，该 *** 作生成的数据与原数据必然是不同的，此时可能造成无法预料的后果。

ROW 模式

内容：在该模式下，binlog 会记录每次 *** 作的源数据与修改后的目标数据，StreamSets就要求该模式。

优势：可以绝对精准的还原，从而保证了数据的安全与可靠，并且复制和数据恢复过程可以是并发进行的

劣势：缺点在于 binlog 体积会非常大，同时，对于修改记录多、字段长度大的 *** 作来说，记录时性能消耗会很严重。阅读的时候也需要特殊指令来进行读取数据。

MIXED 模式

内容：是对上述STATEMENT 跟 ROW 两种模式的混合使用。

细节：对于绝大部分 *** 作，都是使用 STATEMENT 来进行 binlog 没有记录，只有以下 *** 作使用 ROW 来实现：表的存储引擎为 NDB，使用了uuid() 等不确定函数，使用了 insert delay 语句，使用了临时表

主从同步流程：

1、主节点必须启用二进制日志，记录任何修改了数据库数据的事件。

2、从节点开启一个线程（I/O Thread)把自己扮演成 mysql 的客户端，通过 mysql 协议，请求主节点的二进制日志文件中的事件 。

3、主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。

4、从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置（主节点中的二进制文件会有多个）。

5、从节点启动另外一个线程（sql Thread ），把 Relay log 中的事件读取出来，并在本地再执行一次。

mysql默认的复制方式是异步的，并且复制的时候是有并行复制能力的。主库把日志发送给从库后不管了，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制

主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制

半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写 *** 作完成。

还可以延伸到由于主从配置不一样、主库大事务、从库压力过大、网络震荡等造成主备延迟，如何避免这个问题？主备切换的时候用可靠性优先原则还是可用性优先原则？如何判断主库Crash了？互为主备的情况下如何避免主备循环复制？被删库跑路了如何正确恢复？( o )… 感觉越来越扯到DBA的活儿上去了。

RedoLog

可以先通过下面demo理解：

饭点记账可以把账单写在账本上也可以写在粉板上。有人赊账或者还账的话，一般有两种做法：

1、直接把账本翻出来，把这次赊的账加上去或者扣除掉。

2、先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。

生意忙时选后者，因为前者太麻烦了。得在密密麻麻的记录中找到这个人的赊账总额信息，找到之后再拿出算盘计算，最后再将结果写回到账本上。

同样在MySQL中如果每一次的更新 *** 作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。而粉板和账本配合的整个过程就是MySQL用到的是Write-Ahead Logging 技术，它的关键点就是先写日志，再写磁盘。此时账本 = BinLog，粉板 = RedoLog。

1、 记录更新时，InnoDB引擎就会先把记录写到RedoLog（粉板）里面，并更新内存。同时，InnoDB引擎会在空闲时将这个 *** 作记录更新到磁盘里面。

2、 如果更新太多RedoLog处理不了的时候，需先将RedoLog部分数据写到磁盘，然后擦除RedoLog部分数据。RedoLog类似转盘。

RedoLog有write pos 跟checkpoint

write pos ：是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。

check point：是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos和check point之间的是粉板上还空着的部分，可以用来记录新的 *** 作。如果write pos追上checkpoint，表示粉板满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。 redolog两阶段提交：为了让binlog跟redolog两份日志之间的逻辑一致。提交流程大致如下：

1 prepare阶段 -->2 写binlog -->3 commit

当在2之前崩溃时，重启恢复后发现没有commit，回滚。备份恢复：没有binlog 。一致

当在3之前崩溃时，重启恢复发现虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

binlog跟redolog区别：

redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。

redo log是物理日志，记录的是在某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如给ID=2这一行的c字段加1。

redo log是循环写的，空间固定会用完；binlog是可以追加写入的。追加写是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

UndoLog

UndoLog 一般是逻辑日志，主要分为两种：

insert undo log

代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃

update undo log

事务在进行update或delete时产生的undo log不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

3、MySQL中的索引

索引的常见模型有哈希表、有序数组和搜索树。

哈希表：一种以KV存储数据的结构，只适合等值查询，不适合范围查询。

有序数组：只适用于静态存储引擎，涉及到插入的时候比较麻烦。可以参考Java中的ArrayList。

搜索树：按照数据结构中的二叉树来存储数据，不过此时是N叉树(B+树)。广泛应用在存储引擎层中。

B+树比B树优势在于：

B+ 树非叶子节点存储的只是索引，可以存储的更多。B+树比B树更加矮胖，IO次数更少。

B+ 树叶子节点前后管理，更加方便范围查询。同时结果都在叶子节点，查询效率稳定。

B+树中更有利于对数据扫描，可以避免B树的回溯扫描。

索引的优点：

1、唯一索引可以保证每一行数据的唯一性

2、提高查询速度

3、加速表与表的连接

4、显著的减少查询中分组和排序的时间

5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

1、创建跟维护都需要耗时

2、创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据 *** 作

3、 索引需要磁盘的空间进行存储，磁盘占用也很快。

4、当对表中的数据进行CRUD的时，也会触发索引的维护，而维护索引需要时间，可能会降低数据 *** 作性能

索引设计的原则不应该：

1、索引不是越多越好。索引太多，维护索引需要时间跟空间。

2、 频繁更新的数据，不宜建索引。

3、数据量小的表没必要建立索引。

应该：

1、重复率小的列建议生成索引。因为重复数据少，索引树查询更有效率，等价基数越大越好。

2、数据具有唯一性，建议生成唯一性索引。在数据库的层面，保证数据正确性

3、频繁group by、order by的列建议生成索引。可以大幅提高分组和排序效率

4、经常用于查询条件的字段建议生成索引。通过索引查询，速度更快

索引失效的场景

1、模糊搜索：左模糊或全模糊都会导致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。

2、隐式类型转换：比如select * from t where name = xxx , name是字符串类型，但是没有加引号，所以是由MySQL隐式转换的，所以会让索引失效 3、当语句中带有or的时候：比如select * from t where name=‘sw’ or age=14

4、不符合联合索引的最左前缀匹配：(A,B,C)的联合索引，你只where了C或B或只有B,C

关于索引的知识点：

主键索引：主键索引的叶子节点存的是整行数据信息。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。主键自增是无法保证完全自增的哦，遇到唯一键冲突、事务回滚等都可能导致不连续。

唯一索引：以唯一列生成的索引，该列不允许有重复值，但允许有空值(NULL)

普通索引跟唯一索引查询性能：InnoDB的数据是按数据页为单位来读写的，默认每页16KB，因此这两种索引查询数据性能差别微乎其微。

change buffer：普通索引用在更新过程的加速，更新的字段如果在缓存中，如果是普通索引则直接更新即可。如果是唯一索引需要将所有数据读入内存来确保不违背唯一性，所以尽量用普通索引。

非主键索引：非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）

回表：先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。

联合索引：相对单列索引，组合索引是用多个列组合构建的索引，一次性最多联合16个。

最左前缀原则：对多个字段同时建立的组合索引(有顺序，ABC，ACB是完全不同的两种联合索引) 以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。另外组合索引实际还是一个索引，并非真的创建了多个索引，只是产生的效果等价于产生多个索引。

索引下推：MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

索引维护：B+树为了维护索引有序性涉及到页分裂跟页合并。增删数据时需考虑页空间利用率。

自增主键：一般会建立与业务无关的自增主键，不会触发叶子节点分裂。

延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据。

InnoDB存储: * .frm文件是一份定义文件，也就是定义数据库表是一张怎么样的表。*.ibd文件则是该表的索引，数据存储文件，既该表的所有索引树，所有行记录数据都存储在该文件中。

MyISAM存储：* .frm文件是一份定义文件，也就是定义数据库表是一张怎么样的表。* .MYD文件是MyISAM存储引擎表的所有行数据的文件。* .MYI文件存放的是MyISAM存储引擎表的索引相关数据的文件。MyISAM引擎下，表数据和表索引数据是分开存储的。

MyISAM查询：在MyISAM下，主键索引和辅助键索引都属于非聚簇索引。查询不管是走主键索引，还是非主键索引，在叶子结点得到的都是目的数据的地址，还需要通过该地址，才能在数据文件中找到目的数据。

PS：InnoDB支持聚簇索引，MyISAM不支持聚簇索引

4、SQL事务隔离级别

ACID的四个特性

原子性（Atomicity）：把多个 *** 作放到一个事务中，保证这些 *** 作要么都成功，要么都不成功

一致性（Consistency）：理解成一串对数据进行 *** 作的程序执行下来，不会对数据产生不好的影响，比如凭空产生，或消失

隔离性（Isolation，又称独立性）：隔离性的意思就是多个事务之间互相不干扰，即使是并发事务的情况下，他们只是两个并发执行没有交集，互不影响的东西；当然实现中，也不一定需要这么完整隔离性，即不一定需要这么的互不干扰，有时候还是允许有部分干扰的。所以MySQL可以支持4种事务隔离性

持久性（Durability）：当某个 *** 作 *** 作完毕了，那么结果就是这样了，并且这个 *** 作会持久化到日志记录中

PS：ACID中C与CAP定理中C的区别

ACID的C着重强调单数据库事务 *** 作时，要保证数据的完整和正确性，数据不会凭空消失跟增加。CAP 理论中的C指的是对一个数据多个备份的读写一致性

事务 *** 作可能会出现的数据问题

1、脏读(dirty read)：B事务更改数据还未提交，A事务已经看到并且用了。B事务如果回滚，则A事务做错了

2、 不可重复读(non-repeatable read)：不可重复读的重点是修改: 同样的条件, 你读取过的数据, 再次读取出来发现值不一样了，只需要锁住满足条件的记录

3、 幻读(phantom read)：事务A先修改了某个表的所有纪录的状态字段为已处理，未提交；事务B也在此时新增了一条未处理的记录，并提交了；事务A随后查询记录，却发现有一条记录是未处理的造成幻读现象，幻读仅专指新插入的行。幻读会造成语义上的问题跟数据一致性问题。

4、 在可重复读RR隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在当前读下才会出现。要用间隙锁解决此问题。

在说隔离级别之前，你首先要知道，你隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。SQL标准的事务隔离级别由低到高如下： 上图从上到下的模式会导致系统的并行性能依次降低，安全性依次提高。

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。

读已提交(Oracle默认)：别人改数据的事务已经提交，我在我的事务中才能读到。

可重复读(MySQL默认)：别人改数据的事务已经提交，我在我的事务中也不去读，以此保证重复读一致性。

串行：我的事务尚未提交，别人就别想改数据。

标准跟实现：上面都是关于事务的标准，但是每一种数据库都有不同的实现，比如MySQL InnDB 默认为RR级别，但是不会出现幻读。因为当事务A更新了所有记录的某个字段，此时事务A会获得对这个表的表锁，因为事务A还没有提交，所以事务A获得的锁没有释放，此时事务B在该表插入新记录，会因为无法获得该表的锁，则导致插入 *** 作被阻塞。只有事务A提交了事务后，释放了锁，事务B才能进行接下去的 *** 作。所以可以说 MySQL的RR级别的隔离是已经实现解决了脏读，不可重复读和幻读的。

5、MySQL中的锁

无论是Java的并发编程还是数据库的并发 *** 作都会涉及到锁，研发人员引入了悲观锁跟乐观锁这样一种锁的设计思想。

悲观锁：

优点：适合在写多读少的并发环境中使用，虽然无法维持非常高的性能，但是在乐观锁无法提更好的性能前提下，可以做到数据的安全性

缺点：加锁会增加系统开销，虽然能保证数据的安全，但数据处理吞吐量低，不适合在读书写少的场合下使用

乐观锁：

优点：在读多写少的并发场景下，可以避免数据库加锁的开销，提高DAO层的响应性能，很多情况下ORM工具都有带有乐观锁的实现，所以这些方法不一定需要我们人为的去实现。

缺点：在写多读少的并发场景下，即在写 *** 作竞争激烈的情况下，会导致CAS多次重试，冲突频率过高，导致开销比悲观锁更高。

实现：数据库层面的乐观锁其实跟CAS思想类似， 通数据版本号或者时间戳也可以实现。

数据库并发场景主要有三种：

读-读：不存在任何问题，也不需要并发控制

读-写：有隔离性问题，可能遇到脏读，幻读，不可重复读

写-写：可能存更新丢失问题，比如第一类更新丢失，第二类更新丢失

两类更新丢失问题：

第一类更新丢失：事务A的事务回滚覆盖了事务B已提交的结果 第二类更新丢失：事务A的提交覆盖了事务B已提交的结果

为了合理贯彻落实锁的思想，MySQL中引入了杂七杂八的各种锁：

锁分类

MySQL支持三种层级的锁定，分别为

表级锁定

MySQL中锁定粒度最大的一种锁，最常使用的MYISAM与INNODB都支持表级锁定。

页级锁定

是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

行级锁定

Mysql中锁定粒度最细的一种锁，表示只针对当前 *** 作的行进行加锁。行级锁能大大减少数据库 *** 作的冲突。其加锁粒度最小，但加锁的开销也最大行级锁不一定比表级锁要好：锁的粒度越细，代价越高，相比表级锁在表的头部直接加锁，行级锁还要扫描找到对应的行对其上锁，这样的代价其实是比较高的，所以表锁和行锁各有所长。

MyISAM中的锁

虽然MySQL支持表，页，行三级锁定，但MyISAM存储引擎只支持表锁。所以MyISAM的加锁相对比较开销低，但数据 *** 作的并发性能相对就不高。但如果写 *** 作都是尾插入，那还是可以支持一定程度的读写并发

从MyISAM所支持的锁中也可以看出，MyISAM是一个支持读读并发，但不支持通用读写并发，写写并发的数据库引擎，所以它更适合用于读多写少的应用场合，一般工程中也用的较少。

InnoDB中的锁

该模式下支持的锁实在是太多了，具体如下：

共享锁和排他锁 (Shared and Exclusive Locks)

意向锁（Intention Locks）

记录锁（Record Locks）

间隙锁（Gap Locks）

临键锁 （Next-Key Locks）

插入意向锁（Insert Intention Locks）

主键自增锁 (AUTO-INC Locks)

空间索引断言锁（Predicate Locks for Spatial Indexes）

举个栗子，比如行锁里的共享锁跟排它锁：lock in share modle 共享读锁：

为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据使用了 in share mode 的方式上了S 锁。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。

for update排它写锁:

为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到for update。相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。

Gap Lock间隙锁：

1、行锁只能锁住行，如果在记录之间的间隙插入数据就无法解决了，因此MySQL引入了间隙锁(Gap Lock)。间隙锁是左右开区间。间隙锁之间不会冲突。

2、间隙锁和行锁合称NextKeyLock，每个NextKeyLock是前开后闭区间。

间隙锁加锁原则(学完忘那种)：

1、加锁的基本单位是 NextKeyLock，是前开后闭区间。

2、查找过程中访问到的对象才会加锁。

3、索引上的等值查询，给唯一索引加锁的时候，NextKeyLock退化为行锁。

4、索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，NextKeyLock退化为间隙锁。

5、唯一索引上的范围查询会访问到不满足条件的第一个值为止。

InnoDB的全文索引使用反向索引的设计。反向索引存储了一个单词（word）列表，对于每个单词，都有一个文档的列表，来标识这个单词出现的地方。为了支持临近搜索（proximity search），每个单词的位置信息也以字节偏移的方式存储。

当创建了InnoDB全文索引，一系列的索引表会一同被创建，见下面的例子：

最前面的六个表包含了反向索引，它们被称作附属索引表（auxiliary index table）。当输入的表被索引（tokenized）后，每个独立的单词（亦称作“tokens”）会被携带其DOC_ID和位置信息插入到索引表中。根据单词第一个字符的字符集排序权重，在六个索引表中对单词进行完全排序和分区。

反向索引分区到六个附属索引表以支持并行的索引创建。默认有2个线程复制索引（Tokenize）、排序、插入单词和关联数据到索引表中。工作的线程的数量由 innodb_ft_sort_pll_degree 配置项控制的。对于大表的全文索引，可以考虑增加线程数量。

如果主表创建在 xx表空间，索引表存储在它们自己的表空间中。反之，索引表存储于其索引的表空间中。

前面例子展示的另外一种索引表被称作通用索引表，它们被用于全文索引的“删除处理（deletion handing）”和存储内部状态。不同于为每个全文索引都各自创建的反向索引表，这组表对特定表的所有全文索引都是共用的。

即使全文索引删掉了，通用索引（Common Index）也会被保留，当全文索引删除后，为这个索引而创建的FTS_DOC_ID列依然保留，因为移除FTS_DOC_ID列会导致重构之前被索引的表。管理FTS_DOC_ID列需要用到通用索引表。

为了防止大量并发读写附属表，InnoDB使用全文索引缓存去临时缓存最近的插入行。在存满并刷入磁盘之前，缓存的内容一直存储在内存之中，可以通过查询 INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE 表去查看最近缓存的插入行。

缓存和批处理刷新行为避免了对辅助索引表的频繁更新，频繁更新可能会在繁忙的插入和更新期间导致并发访问问题。批处理还避免了对同一个word的多次插入，最大化的减少了重复的条目。相同的word会先merge再刷入到磁盘中，而不是为每个word单独插入，这样提高了插入效率并且使得索引附属表尽可能的小。

全文索引缓存只缓存最近插入的行，查询时，已经刷入磁盘（附属索引表）的数据不会再回到索引缓存中。附属索引表中的内容是直接查询的，最终返回的结果返回前需要将附属索引表的结果和索引缓存中的结果合并。

InnoDB使用被称作DOC_ID的唯一文件描述符，将全文索引中的单词与该单词在文档中的记录映射起来。映射关系需要索引表中的 FTS_DOC_ID 列。在创建全文索引时，如果没有定义 FTS_DOC_ID 列，InnoDB会自动的加入一个隐藏的 FTS_DOC_ID 列。下面是一个例子，

CREATE FULLTEXT INDEX ft_index ON xxxxxxxx(CONTEXT)

[2021-11-12 18:14:04] [HY000][124] InnoDB rebuilding table to add column FTS_DOC_ID

重点看一下这一行： [HY000][124] InnoDB rebuilding table to add column FTS_DOC_ID ，InnoDB重新构建了这个表，并且添加了一个列 FTS_DOC_ID 。

在CREATE TABLE的过程中添加 FTS_DOC_ID 的时间成本要低于在已经有数据的表上建立全文索引。如果在表加载数据之前定义 FTS_DOC_ID 列，这个表和它的索引都不需要为了新增列而重新构建。如果你不需要考虑 CREATE FULLTEXT INDEX 的性能，可以让InnoDB为你创建 FTS_DOC_ID 列。InnoDB会新增一个隐藏的 FTS_DOC_ID 列，并且在 FTS_DOC_ID 上建立唯一索引（FTS_DOC_ID_INDEX）。如果你想自行创建 FTS_DOC_ID 列，这个列必须定义为 BIGINT UNSIGNED NOT NULL 且命名为FTS_DOC_ID（全大写），如下例子：

如果你自行定义 FTS_DOC_ID 列的话，你需要负责管理这个列，避免空值（empty）或者重复值。 FTS_DOC_ID 的值是不能被重复利用的，所以也就是说 FTS_DOC_ID 的值是需要一直增加的。

或者，你可以在 FTS_DOC_ID 列上创建所必须的唯一索引FTS_DOC_ID_INDEX（全大写）。

mysql>CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID)

如果你没有创建FTS_DOC_ID_INDEX，InnoDB会自动创建。

在MySQL 5.7.13前，允许最大FTS_DOC_ID与最新的FTS_DOC_ID之间的间隔为10000，在MySQL 5.7.13及之后的版本中，这个允许的间隔为65535。

为了避免重新构建表，FTS_DOC_ID列在删除了全文索引之后依然被保留。

删除被索引文件的一个记录，可能会在附属索引表中产生非常多的小的删除项，在并发访问时，会产生热点问题。为了避免这个问题，每当被索引表中的记录被删除时，会将被删文档的DOC_ID记录在一个特别的 FTS_*_DELETED 表中，同时全文索引中已经索引了的记录依然被保存。在返回查询结果前，使用 FTS_*_DELETED 中的信息去过滤掉已经删除掉了的DOC_ID。这种设计的优势在于删除速度快且消耗低。不好的地方在于索引的大小不能随着记录的删除而立即减少。为了删除已删除记录在全文索引中的项，需要对被索引的表执行OPTIMIZE TABLE，配合[ innodb_optimize_fulltext_only=ON ]，去重构全文索引。

细节略，有例子： https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

全文搜索只能看到已经提交了的数据。

你可以通过查询下面的INFORMATION_SCHEMA表，来监控或测试InnoDB的一些特殊文本处理。

默认的分词器不支持中文，不能检索到中文中的英文单词。

InnoDB默认的Stopwords：

select * from information_schema.INNODB_FT_DEFAULT_STOPWORD

SQL中的关键词（保留关键字）：

Shell中的关键词：for，while，echo

其他：###, ***, --,

被索引表数据量、索引表数据量

模糊匹配与严格匹配的性能差距

需要将 innodb_optimize_fulltext_only 配置为ON，这里是否需要DBA在MySQL镜像中修改？

innodb_optimize_fulltext_only 设置为ON后，对系统有何影响需要评估。

innodb_optimize_fulltext_only

执行的时间、频率。

MySQL内建的全文检索解析器使用单词之间的空白作为分隔符以标识单词的头尾，但是这里有个限制，对于表意文字，它是没有单词分隔符的。为了解决这个限制，MySQL提供了支持中文、日语、韩语的 ngram 解析器。ngram解析器支持InnoDB和MyISAM。

Ngram是内建在服务中的插件，像其他自建在服务中的插件一样，服务启动时会自动加载它。全文检索的语法参考上面（ Section 12.10, “Full-Text Search Functions” ），这里只讨论一些不同的地方。除了单词的最小、最大长度配置项（[ innodb_ft_min_token_size ]innodb_ft_max_token_size,ft_min_word_len,ft_max_word_len，全文检索依赖一些配置项都是可以使用的。

Ngram默认索引的单词（token）的大小为2（ 2bigram ）。例如，索引的大小为2，Ngram解析器解析字符串“abc def”为四个单词元素(tokens)：“ab”, “bc”, “de” and “ef”。

ngram token size is configurable using the ngram_token_size configuration option, which has a minimum value of 1 and maximum value of 10.

作为只读变量, ngram_token_size 只能在启动配置或者配置文件中指定

与默认的解析器相差不大，多了一句： xxx WITH PARSER ngram

Ngram在解析时去除空格，如

MySQL内建的默认全文检索解析器将单词与Stopword列表中的做对比，如果单词与Stopword列表中的元素相同的话，这个单词则不会被索引。对于Ngram解析器，Stopword的处理方式不同。Ngram解析器不排除与stopword列表中的条目相等的token，而是排除包含stopwords的token。例如，假设 ngram_token_size=2 ，包含“a,b”的文档将被解析为 “a,” h和“,b”。如果将逗号（“,”）定义为停止字，则 “a,”和“,b”都将不会加入索引中，因为它们包含逗号。

例子：

默认Ngram解析器使用默认的Stopword列表，这里面含有英文的Stopword。如果需要中文的Stopword，需要你自己创建。

Stopword的长度超过 ngram_token_size则会被忽略。

有两个文档，一个包含“ab”，另一个包含“abc”。对于搜索文本“abc”将转换成“ab”，“bc”。

略。

For example, The search phrase “abc” is converted to “ab bc”, which returns documents containing “abc” and “ab bc”.

The search phrase “abc def” is converted to “ab bc de ef”, which returns documents containing “abc def” and “ab bc de ef”. A document that contains “abcdef” is not returned.

使用Ngram解析器好处是支持了中文的检索

解决方案：先删除唯一索引，再在当前字段创建普通索引，参考下列说明以及SQL：

普通索引

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

唯一索引

普通索引允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。

如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

创建索引

在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

ALTER TABLE

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

---