最新版本的mysql的innodb类型,支持全文索引吗?

发布网友 发布时间：2022-04-30 20:32

我来回答

共3个回答

懂视网 时间：2022-05-01 00:53

可以在information_schema架构下的表INNODB_FT_INDEX_TABLE得到表fts_a中的分词信息。

对于InnoDB存储引擎而言，其总是在事务提交时将分词写入到FTS Index Cache,然后通过批量写入到磁盘。虽然InnoDB存储引擎通过一种延时的、批量的写入方式来提高数据库的性能，但是上述操作仅在事务提交时发生。

当数据库关闭时，在FTS Index Cache中的数据库会同步到磁盘上的Auxiliary Table中。如果当数据库发生宕机时，一些FTS Index Cache中的数据可能未同步到磁盘上，那么下次重启数据库时，当用户对表进行全文检索(查询、插入)时,InnoDB存储引擎会自动读取未完成的文档，然后进行分词操作，再将分词结果放到FTS Index Cache

为了支持全文检索，必须有一个列与word进行映射。在InnoDB中这个列被命名成FTS_DOC_ID，其类型为BIGINT UNSIGNED NOT NULL，并且InnoDB存储引擎自动会在该列加上一个名为FTS_DOC_ID_INDEX的Unique Index.这些操作由存储引擎自己完成，用户也可以在建表时自动添加FTS_DOC_ID,以及对应的Unique Index。由于列名FTS_DOC_ID聚友特殊意义，因此在创建时必须注意相应的类型，否则会报错

可以看到，由于用户手动定义FTS_DOC_ID为INT，而非BIGINT因此在创建时候会抛出异常，应该将此处修改成对应的BIGINT即可

文档中的分词的插入操作是在事务提交时完成，但是对于删除操作，其在事务提交时，不删除磁盘Auxiliary Table的记录，而只是删除FTS Cache Index记录，对于Auxiliary Table中被删除的记录，存储引擎会记录其FTS DOCUMENT ID ,并将其保存在DELETE auxiliary table中，在设置参数innodb_ft_aux_table后，用户可以访问information_schema架构下的表INNODB_FT_DELETED来观察删除的FTS Document ID

由于文档的DML操作实际并不删除索引中的数据，相反还会在对应的DELETED表中插入记录，因此随着应用程序的允许，索引会变得越来越大，即使索引中的有些数据已经被删除，查询也不会选择这类记录，为此，InnoDB提供了一种方式，允许用户手工将已删除的记录从索引中彻底删除，这就是OPTIMIZE TABLE。因为OPTIMIZE TABLE还会进行一些其他的操作。如Cardinality重新统计，若用户希望对倒排索引进行操作，可以通过innodb_optimize_fulltext_only设置

SET GLOBAL innodb_optimize_fulltext_only=1;

OPTIMIZE TABLE fts_a;

若被删除的文档很多，那么OPTIMIZE TABLE操作可能占用非常多的时间，会影响到程序并发性，并极大的降低用户的响应时间，用户可以通过参数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)
);


INSERT INTO fts_a SELECT NULL,‘pease porridge in the post‘;
INSERT INTO fts_a SELECT NULL,‘pease porridge hot,pease porridge cold‘;
INSERT INTO fts_a SELECT NULL,‘Nine days old‘;
INSERT INTO fts_a SELECT NULL,‘Some like it hot,some like it cold‘;
INSERT INTO fts_a SELECT NULL,‘Some like it the pot‘;
INSERT INTO fts_a SELECT NULL,‘Nine days old‘;
INSERT INTO fts_a SELECT NULL,‘I like code days‘;

CREATE FULLTEXT INDEX idx_fts ON fts_a(body);

查看数据

mysql> select * from fts_a;
+------------+----------------------------------------+
| FTS_DOC_ID | body     |
+------------+----------------------------------------+
|  1 | pease porridge in the post  |
|  2 | pease porridge hot,pease porridge cold |
|  3 | Nine days old    |
|  4 | Some like it hot,some like it cold |
|  5 | Some like it the pot   |
|  6 | Nine days old    |
|  7 | I like code days   |
+------------+----------------------------------------+
7 rows in set (0.00 sec)

mysql> set global innodb_ft_aux_table=‘iot2/fts_a‘;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM information_schema.`INNODB_FT_INDEX_TABLE`;
+----------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+----------+--------------+-------------+-----------+--------+----------+
| code | 7 | 7 | 1 | 7 | 7 |
| cold | 2 | 4 | 2 | 2 | 34 |
| cold | 2 | 4 | 2 | 4 | 30 |
| days | 3 | 7 | 3 | 3 | 5 |
| days | 3 | 7 | 3 | 6 | 5 |
| days | 3 | 7 | 3 | 7 | 12 |
| hot | 2 | 4 | 2 | 2 | 15 |
| hot | 2 | 4 | 2 | 4 | 13 |
| like | 4 | 7 | 3 | 4 | 5 |
| like | 4 | 7 | 3 | 4 | 17 |
| like | 4 | 7 | 3 | 5 | 5 |
| like | 4 | 7 | 3 | 7 | 2 |
| nine | 3 | 6 | 2 | 3 | 0 |
| nine | 3 | 6 | 2 | 6 | 0 |
| old | 3 | 6 | 2 | 3 | 10 |
| old | 3 | 6 | 2 | 6 | 10 |
| pease | 1 | 2 | 2 | 1 | 0 |
| pease | 1 | 2 | 2 | 2 | 0 |
| pease | 1 | 2 | 2 | 2 | 19 |
| porridge | 1 | 2 | 2 | 1 | 6 |
| porridge | 1 | 2 | 2 | 2 | 6 |
| porridge | 1 | 2 | 2 | 2 | 19 |
| post | 1 | 1 | 1 | 1 | 22 |
| pot | 5 | 5 | 1 | 5 | 17 |
| some | 4 | 5 | 2 | 4 | 0 |
| some | 4 | 5 | 2 | 4 | 17 |
| some | 4 | 5 | 2 | 5 | 0 |
+----------+--------------+-------------+-----------+--------+----------+
27 rows in set (0.00 sec)

可以看到每个word对应一个DOC_ID和POSITION。此外，还记录了FIRST_DOC_ID、LAST_DOC_ID、DOC_COUNT分别代表该word第一次出现文档的ID,最后一次出现的文档ID，以及该word在多少个文档中存在。

若此时执行下面的SQL语句，会删除FTS_DOC_ID为7的文档

DELETE FROM fts_a WHERE FTS_DOC_ID=7;

InnoDB存储引擎并不会直接删除索引中对应的记录，而是将删除的文档ID插入到DELETED表

SELECT * FROM information_schema.`INNODB_FT_DELETED`;

如果用户想要彻底删除倒排索引中该文档的分词信息，可以

mysql> SET GLOBAL innodb_optimize_fulltext_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> OPTIMIZE TABLE fts_a;
+------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+----------+
| iot2.fts_a | optimize | status | OK |
+------------+----------+----------+----------+
1 row in set (0.08 sec)

mysql> SELECT * FROM information_schema.`INNODB_FT_DELETED`;
+--------+
| DOC_ID |
+--------+
| 7 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.`INNODB_FT_BEING_DELETED`;
+--------+
| DOC_ID |
+--------+
| 7 |
+--------+
1 row in set (0.00 sec)

运行OPTIMIZE TABLE 可以将记录彻底删除，并且彻底删除的文档ID会记录到INNODB_FT_BEGIN_DELETED中。此外，由于7这个文档一倍删除，因此不允许在插入这个文档ID，否则会抛出异常

mysql> INSERT INTO fts_a SELECT 7,‘I like this days‘;
ERROR 182 (HY000): Invalid InnoDB FTS Doc ID

stopword列表(stopword list)是本节最后阐述的一个概念，其表示该列表中的word不需要对其进行索引分词操作。例如，对于the这个单词，由于其不具有具体的意义，因此将其视为stopword，InnoDB存储引擎有一张默认的stopword列表，在information_schema架构下，表名为INNODB_FT_DEFAULT_STOPWORD，默认为36个stopword可以通过参数innodb_ft_server_stopword_table来定义stopword列表，如

mysql> CREATE TABLE innodb_ft_bug (
 -> value VARCHAR(18) NOT NULL DEFAULT ‘‘
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8; #此处必须为utf8不然会碰到bug
Query OK, 0 rows affected (0.07 sec)

mysql> SET GLOBAL innodb_ft_server_stopword_table=‘iot2/innodb_ft_bug‘;
Query OK, 0 rows affected (0.00 sec)

遇到bug的情形

mysql> CREATE TABLE user_stopword(VALUE VARCHAR(30))ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)
mysql> SET GLOBAL innodb_ft_server_stopword_table=‘iot2/user_stopword‘;
ERROR 1231 (42000): Variable ‘innodb_ft_server_stopword_table‘ can‘t be set to the value of ‘iot2/user_stopword‘

观察错误日志提示

InnoDB: invalid column name for stopword table iot2/user_stopword. Its first column must be named as ‘value‘.

使用全文检索还有以下限制

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

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

不支持没有单词界定符delimiter的语言，如中文 日文汉语等

MySQL中InnoDB全文检索

标签：

热心网友 时间：2022-04-30 22:01

追答mysql5.6我还没有做过测试，这个需要你去造点数据试试了

热心网友 时间：2022-04-30 23:19