mysql 8 生产_MySQL 8.0 InnoDB全文索引可用于生产环境吗（续）

innodb_ft_min_token_size= 1

innodb_ft_cache_size= 80000000

innodb_ft_total_cache_size= 1600000000

innodb_buffer_pool_size= 10737418240

新的测试表：

[root@yejr.run]> CREATE TABLE `t3`(

`id`intunsigned NOT NULL AUTO_INCREMENT,

`ltu`datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

`summary`varchar( 255) NOT NULL,

PRIMARY KEY ( `id`),

FULLTEXT KEY `k2`( `summary`) /*! 50100WITH PARSER `ngram`* /

) ENGINE=InnoDB AUTO_INCREMENT=6449884 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. 导入数据

还是从小说网站上下载的数据进行切分，每次不超过200字节，一个大文件被切分成很多分导入。

看下表统计信息：

[root@yejr.run]> show table status like 't3'G

*************************** 1. row ***************************

Name: t3

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 6434441

Avg_row_length: 319

Data_length: 2053111808

Max_data_length: 0

Index_length: 114999296

Data_free: 5242880

Auto_increment: 6449884

Create_time: 2020-05-10 19:23:12

Update_time: NULL

Check_time: NULL

Collation: utf8mb4_0900_ai_ci

Checksum: NULL

Create_options: stats_sample_pages=100

Comment:

#表空间文件有2.1GB

[root@yejr.run] # ls -l test/t3.ibd

-rw-r----- 1 mysql mysql 2197815296 May 9 14:37 test/t3.ibd

#索引文件加起来2.3GB

[root@yejr data01] # du -sch test/fts_0000000000000571_*

209M test/fts_0000000000000571_0000000000000231_index_1.ibd

80K test/fts_0000000000000571_0000000000000231_index_2.ibd

80K test/fts_0000000000000571_0000000000000231_index_3.ibd

80K test/fts_0000000000000571_0000000000000231_index_4.ibd

80K test/fts_0000000000000571_0000000000000231_index_5.ibd

2.1G test/fts_0000000000000571_0000000000000231_index_6.ibd

80K test/fts_0000000000000571_being_deleted_cache.ibd

80K test/fts_0000000000000571_being_deleted.ibd

80K test/fts_0000000000000571_config.ibd

80K test/fts_0000000000000571_deleted_cache.ibd

608K test/fts_0000000000000571_deleted.ibd

2.3G total

注意，上面输出的table status中 Index_length不为 0，按理说这个表非聚集的二级索引，这里的值应该是 0 才对，后面再去确认什么原因，有知道的读者也请留言告知，谢谢。

文档中关于 Index_length的解释：

For MyISAM, Index_length is the length of the index file, in bytes.

For InnoDB, Index_length is the approximate amount of space allocated for non-clustered indexes, in bytes. Specifically, it is the sum of non-clustered index sizes, in pages, multiplied by the InnoDB page size.

Refer to the notes at the end of this section for information regarding other storage engines.

For MyISAM, Index_length is the length of the index file, in bytes.

For InnoDB, Index_length is the approximate amount of space allocated for non-clustered indexes, in bytes. Specifically, it is the sum of non-clustered index sizes, in pages, multiplied by the InnoDB page size.

Refer to the notes at the end of this section for information regarding other storage engines.

本次我根据导入的文章，随机找几个关键词进行测试。

第一个关键词： 绿毒蛙。多次反复全文搜索查询，耗时约为 1.67 ~ 1.75秒 之间。

# Query_time: 1.691762 Rows_sent: 13 Rows_examined: 120473

利用profiling查看耗时最久的两个阶段

| FULLTEXT initialization |1.384282|

| executing | 0.324287 |

改成 like'%绿毒蛙%'模糊搜索，耗时基本稳定在 7.5 ~ 8.0秒 之间

# Query_time: 7.511337 Rows_sent: 13 Rows_examined: 6438121

profiling的结果

| executing |7.511123|

第二个关键词： 田大仁。

这次的全文搜索耗时则慢了很多

# Query_time: 72.822958 Rows_sent: 2334 Rows_examined: 1850544

...

| FULLTEXT initialization |67.449839|

| executing | 5.083728 |

LIKE模糊搜索耗时还是和第一个关键词差不多，7.5 ~ 8.0秒 之间。

此外，全文搜索时如果加上 LIMIT N对性能提升并没有实质性帮助，而且在 slow query log中记录的 Rows_examined值也不准确

SELECT... LIMIT5;

...

# Query_time: 67.928363 Rows_sent: 5 Rows_examined: 211

从 profiling的结果来看，在 FULLTEXT initialization阶段的耗时占总耗时的 98%，约 66.59秒，只是在 executing阶段提升了，加上 LIMIT5之后，耗时从 5秒 降低到 0.7秒。

第三个关键词我选择了比较常见的"时间"。这次的全文搜索则要更久，总耗时 553.69秒

# Query_time: 553.693291 Rows_sent: 256491 Rows_examined: 1838688

即便加上 LIMIT 5也需要543秒

SELECT... LIMIT5;

...

# Query_time: 543.437429 Rows_sent: 5 Rows_examined: 10

用LIKE的模糊搜索耗时7.9秒

# Query_time: 7.900584 Rows_sent: 256475 Rows_examined: 6438121

...

select*, 0asscore fromt3 wheresummary like'%时间%';

4. 继续放弃治疗

从上面的几个简单测试中能看出来InnoDB的全文索引效率还不是太可靠，如果没有靠谱的分词库的话，还是不建议在生产环境上使用，个人愚见，欢迎批评指正 ：)