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

本是古典何须时尚 2023-03-13 03:54 6阅读 0赞

> 继续放弃治疗。

0. 背景介绍1. 环境准备2. 导入数据3. 全文搜索测试4. 继续放弃治疗  
0. 背景介绍

前文[MySQL 8.0 InnoDB全文索引可用于生产环境吗][MySQL 8.0 InnoDB]中，简单介绍了MySQL 8.0中对InnoDB表进行全文搜索的性能测试，结论是不建议用于生产环境。

有同学建议调整测试模式，不对大文本/大对象列全文搜索，而是对类似标题这种短文本列进行全文搜索，代替 `like'%关键词%'` 这样的搜索方式。

本次就该建议进行测试，不过最终的结论也还是不建议使用全文搜索。

## 1. 环境准备 ##

本次测试依旧采用MySQL 8.0.19版本。几个关键参数：

ngram_token_size = 1
    ft_min_word_len = 1
    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` int unsigned NOT NULL AUTO_INCREMENT,
      `ltu` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `summary` varchar(255) NOT NULL,
      PRIMARY KEY (`id`),
      FULLTEXT KEY `k2` (`summary`) /*!50100 WITH PARSER `ngram` */
    ) ENGINE=InnoDB AUTO_INCREMENT=6449884 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. 导入数据

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

最后，共导入6438121条数据，最短9个字符21字节，最长255字符757字节。

看下表统计信息：

[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 p for information regarding other storage engines.

## 3. 全文搜索测试 ##

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

第一个关键词：**绿毒蛙**。多次反复全文搜索查询，耗时约为 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 |

首个关键词扫描行数 120473，**全文搜索胜出**。

第二个关键词：**田大仁**。

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

这个关键词的扫描行数是 1850544，是第一个关键词的 15.36 倍。

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

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

SELECT ... LIMIT 5;
    ...
    # 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 ... LIMIT 5;
    ...
    # Query_time: 543.437429  Rows_sent: 5  Rows_examined: 10

用LIKE的模糊搜索耗时7.9秒

# Query_time: 7.900584  Rows_sent: 256475  Rows_examined: 6438121
    ...
    select *,0 as score from t3 where summary like '%时间%';

4. 继续放弃治疗

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

全文完。

--------------------

*  由叶老师主讲的知数堂「MySQL优化课」课程早已升级到MySQL 8.0版本了，现在上车刚刚好，扫码开启MySQL 8.0的修行之旅吧。
    
    ![format_png][]
    
    --------------------
    
    另外，叶老师在腾讯课堂《**MySQL性能优化**》精编版第一期已完结，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。
    
    下面是自动拼团的二维码，组团价仅需78元
    
    ![format_png 1][]

[MySQL 8.0 InnoDB]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b4a5b8a4cc34d0d498b96067cde72b72d542da12610fe4435c540762458ede377b8d0c014&idx=1&mid=2653933617&scene=21&sn=0cf199a02caf7fd3c918cc669c23441c#wechat_redirect
[format_png]: /images/20230312/5dc69653940c4c14993ffdbb4d6f5f2a.png
[format_png 1]: /images/20230312/2e7f53e5936141dcb7062df5c860dfb1.png