为何COUNT很慢却不写SLOW LOG

MySQL对COUNT(*)一直在优化。

1. 问题描述

某日,群友反馈问题对大表COUNT(*)很慢,但却不会记录到slow log中,这是为什么呢?
我自己根据他提供的信息,复现了这个问题:

# MySQL版本是8.0.20
[root@yejr.run]>\s
...
Server version:     8.0.20 MySQL Community Server - GPL
...

# 确认 long_query_time
[root@yejr.run]>select @@global.long_query_time, @@session.long_query_time;
+--------------------------+---------------------------+
| @@global.long_query_time | @@session.long_query_time |
+--------------------------+---------------------------+
|                 0.010000 |                  0.010000 |
+--------------------------+---------------------------+

# 执行 COUNT(*),耗时超过 0.01,但slow log没有记录
[root@yejr.run]>select count(*) from t1;
+----------+
| count(*) |
+----------+
|   799994 |
+----------+
1 row in set (0.27 sec)

这到底是为什么呢?

2. 问题排查

我们先检查所有和slow log相关的参数:

[root@yejr.run]>show global variables;
...
| log_slow_admin_statements              | OFF      |
| log_slow_extra                         | ON       |
| log_slow_slave_statements              | OFF      |
| long_query_time                        | 0.010000 |
| slow_query_log                         | ON       |
| slow_query_log_file                    | slow.log |
| log_output                             | FILE     |
| min_examined_row_limit                 | 100      |
| log_queries_not_using_indexes          | 1        |
| log_throttle_queries_not_using_indexes | 60       |
...

上面几个参数中,比较可疑有下面几个:

| min_examined_row_limit                 | 100      |
| log_queries_not_using_indexes          | 1        |
| log_throttle_queries_not_using_indexes | 60       |

先说 log_queries_not_using_indexes,这表示把没有使用索引的SQL也当成slow query记录下来,但在本例中,是有走索引的:

[root@yejr.run]>desc select count(*) from t1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 42760
     filtered: 100.00
        Extra: Using index

由此也顺便排除了参数 log_throttle_queries_not_using_indexes 的嫌疑。
那么,只剩下参数 min_examined_row_limit 的嫌疑,它表示当扫描行数少于设定值时,这个SQL也不会被当做slow query记录下来。
那么,本例中的COUNT(*)是否符合这种情况呢?
我们先把参数 min_examined_row_limit 值设置为 0,也就是默认值。

[root@yejr.run]>set global min_examined_row_limit=0;
[root@yejr.run]>set session min_examined_row_limit=0;
[root@yejr.run]>select @@global.min_examined_row_limit, @@session.min_examined_row_limit;
+---------------------------------+----------------------------------+
| @@global.min_examined_row_limit | @@session.min_examined_row_limit |
+---------------------------------+----------------------------------+
|                               0 |                                0 |
+---------------------------------+----------------------------------+

再执行一次 COUNT(*) 查询

[root@yejr.run]>select count(*) from t1;
+----------+
| count(*) |
+----------+
|    43462 |
+----------+
1 row in set (0.02 sec)

果然,这次被记录到slow log中了

# Query_time: 0.026083  Lock_time: 0.000110 Rows_sent: 1  Rows_examined: 0
...
select count(*) from t1;

注意到 Rows_examined 的值是 0,嗯,好像不太科学?

到这里,原因查明了,参数 min_examined_row_limit 的值设置大于 0 了,而本例中的 COUNT(*) 操作因为 Rows_examined=0,所以不会被记录到slow log中。

3. 问题解释

虽然知道问题原因了,但 Rows_examined 表示什么意思呢,文档中的解释如下:

• Rows_examined: 
The number of rows examined by the server layer (not counting any processing internal to storage engines).

可能字面意思上看起来不太好理解,换个思路,其实就是我们执行完一个SQL后,查看状态变量中名为 Handler_read_% 的几个指标即可,例如:

[root@yejr.run]> flush status;
[root@yejr.run]> select count(*) from t1;
...
[root@yejr.run]> show status like 'handler%read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

可以看到以上几个值均为 0,因此 Rows_examined 也为 0,就不会被记录到slow log中了。

3.1 关于聚集索引并行读

说到这里,我还要隆重介绍MySQL 8.0的另一个新特性。
从8.0.14版本起,新增参数 innodb_parallel_read_threads,支持对聚集索引的并行扫描,需要满足以下几个条件:
– 参数 innodb_parallel_read_threads 值 > 0
– 只支持聚集索引
– 只支持无锁查询
– 不是INSERT…SELECT查询

主要用于加速以下两种场景:
– CHECK TABLE操作
– 不带WHERE条件的全表COUNT(*)

因此,COUNT(*)也是可以并行读聚集索引的,从error log中可以看到类似下面的信息:

[Note] [MY-011825] [InnoDB] Parallel scan: 4
[Note] [MY-011825] [InnoDB] ranges: 130 max_threads: 4 split: 128 depth: 1
[Note] [MY-011825] [InnoDB] n: 20914
[Note] [MY-011825] [InnoDB] n: 18066
[Note] [MY-011825] [InnoDB] n: 4482

从上述日志能看出来几点:
1. 设置了最高4个并行线程
2. 实际并行3个线程,实际并行数从1~4,不一定每次都跑最高并行
3. 分别扫描行数是 20914、18066、4482,即 COUNT() 结果总数是 43462

对t1表加上一个辅助索引后,再来看下面这个COUNT(*)

# 看起来这个查询是走辅助索引
[root@yejr.run]>desc select count(*) from t1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: NULL
          key: k1
      key_len: 5
          ref: NULL
         rows: 42760
     filtered: 100.00
        Extra: Using index

# 实际执行一把
[root@yejr.run]>select count(*) from t1;
+----------+
| count(*) |
+----------+
|    43462 |
+----------+
1 row in set (0.01 sec)

# 发现 Handler_read_% 的值还是 0
[root@yejr.run]>show status like 'handler%read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+        

且此时error log中依然有并行扫描的记录

[Note] [MY-011825] [InnoDB] Parallel scan: 4
[Note] [MY-011825] [InnoDB] ranges: 91 max_threads: 4 split: 88 depth: 1
[Note] [MY-011825] [InnoDB] n: 21493
[Note] [MY-011825] [InnoDB] n: 21486
[Note] [MY-011825] [InnoDB] n: 483

看到了么,实际上还是用到了聚集索引的并行扫描特性来加速。

提醒:上述error log中记录并行扫描聚集索引信息的功能在8.0.20中又被去掉了,上面之所以能看到这段日志是因为我排查到后面又回退到8.0.19版本了,有点费劲。。。
看下8.0.20 Release Notes

InnoDB: Unnecessary messages about parallel scans were printed to the error log. (Bug #30330448)

其实留着不挺好的嘛,搞不懂为毛要去掉的说。。。

提醒:MySQL 5.6版本里,无论基于主键还是辅助索引的全表无WHERE条件的COUNT(*),Rows_examined记录的是总行数,而不是像8.0那样值为0。

延伸阅读

Enjoy MySQL :)

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。

活久见,为什么SHOW TABLE STATUS总是不更新

踩坑了吗?

1. 问题描述

前几天,QQ群里在讨论一个关于MySQL表统计信息迟迟不更新的问题。
这个问题我复现了,下面是详细过程:

# 创建一个空表
[root@yejr.run]>create table ttxx like t1;

# 第一次执行 show table status,看到 Rows = 0,没问题
[root@yejr.run] [test]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2020-06-04 16:17:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

# 写入将近80万条数据
[root@yejr.run]>insert into ttxx select id,name,c1 from t1;
Query OK, 799994 rows affected (8.25 sec)
Records: 799994  Duplicates: 0  Warnings: 0

# 再次执行 show table status,发现 Rows 值还是 0,并且过了几秒钟后多执行几次,结果依然如此
[root@yejr.run] [test]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2020-06-04 16:17:54
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

简言之,就是执行 SHOW TABLE STATUS无法及时查看到该表的最新统计信息

但与此同时,直接查看 mysql.innodb_table_statsmysql.innodb_index_stats 两个表,却又可以看到该表的统计信息已经更新了:

[root@yejr.run] [test]>select * from mysql.innodb_table_stats where database_name ='test' and table_name ='ttxx';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | ttxx       | 2020-06-04 16:18:24 | 795064 |                 2788 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+

[root@yejr.run] [test]>select * from mysql.innodb_index_stats where database_name ='test' and table_name ='ttxx';
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+
| test          | ttxx       | PRIMARY    | 2020-06-04 16:18:24 | n_diff_pfx01 |     795064 |          20 | aid                               |
| test          | ttxx       | PRIMARY    | 2020-06-04 16:18:24 | n_leaf_pages |       2764 |        NULL | Number of leaf pages in the index |
| test          | ttxx       | PRIMARY    | 2020-06-04 16:18:24 | size         |       2788 |        NULL | Number of pages in the index      |
+---------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+

尝试跑一个SQL观察执行计划,看起来也是正确的:

[root@yejr.run] [test]>desc select count(*) from ttxx;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | ttxx  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 795064 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

此时再执行 SHOW TABLE STATUS 依然无法看到信息更新。

到底肿么回事呢?

2. 问题探究

作为老司机(踩坑大户),首先想到的就是检查官方手册。
MySQL官方手册的描述中,有这么一段内容:

• Rows

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

The Rows value is NULL for INFORMATION_SCHEMA tables.

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

简言之,就是说MyISAM表的Rows是精确值,但InnoDB表则只是大概值,甚至有可能只是真实值的40% ~ 50% 之间。

另外,这个信息是从 INFORMATION_SCHEMA.TABLES (下面简称IFS.TABLES)里获取的:

Table information is also available from the INFORMATION_SCHEMA TABLES table. See Section 25.36, “The INFORMATION_SCHEMA TABLES Table”.

那我们再看看文档中关于 IFS.TABLES 的描述吧:

25.36 The INFORMATION_SCHEMA TABLES Table

The TABLES table provides information about tables in databases.

Columns in TABLES that represent table statistics hold cached values. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To update cached values at any time for a given table, use ANALYZE TABLE. To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry to 0. For more information, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.

看到这里,真相基本上呼之欲出了。
IFS.TABLES表中看到的数据是有cache的,默认cache时长是 86400秒(即1天),修改参数 information_schema_stats_expiry 即可调整时长。也就是说,除非cache过期了,或者手动执行 ANALYZE TABLE 更新统计信息,否则不会主动更新。
这个参数(功能)是MySQL 8.0后新增的,所以这个问题在8.0之前的版本不存在。
参数 information_schema_stats_expiry 还影响其 IFS.STATISTICS 表。
此外,该参数还可以在session级动态修改。
我们尝试修改session级配置:

[root@yejr.run]>set session information_schema_stats_expiry = 0;

# 修改完后就可以看到Rows数据变了
[root@yejr.run]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 795064
 Avg_row_length: 57
...

[root@yejr.run]>set session information_schema_stats_expiry = 86400;
# 把session配置改回默认值,尴尬的发现Rows值又恢复成0了
[root@yejr.run] [test]>show table status like 'ttxx'\G
*************************** 1. row ***************************
           Name: ttxx
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
...

看来,如果应用程序中有需要读取 table status 概要信息的时候,最好还是先手动执行 ANALYZE TABLE 或者修改参数值,也可以用下面这样的SQL:

select /* set_var(information_schema_stats_expiry = 1) */ * from information_schema.tables where table_schema='test' and table_name = 'ttxx'\G

这是MySQL 8.0后新增的HINT语法。

另外,文档中还有一段注释:

If the innodb_read_only system variable is enabled, ANALYZE TABLE may fail because it cannot update statistics tables in the data dictionary, which use InnoDB. For ANALYZE TABLE operations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is a MyISAM table). To obtain the updated distribution statistics, set information_schema_stats_expiry=0.

意思是,当启用参数 innodb_read_only 后再执行 ANALYZE TABLE 就会失败,哪怕要更新统计信息的表是MyISAM引擎,因为所有InnoDB表都被设置为只读,更新统计信息后无法回写到对应的InnoDB字典表里了。

3. 总结

遇到诡异问题时,总是习惯性地先去查阅官方手册,通常都是可以得到答案的,耐心点,再耐心点。

延伸阅读

enjoy MySQL 8.0 :)

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。

MySQL 8.0 InnoDB压缩行格式性能测试

InnoDB compressed好吃吗?
不,它有点硌牙。

1. 背景信息

多年前我对InnoDB表压缩格式做了个简单的测试,得到的结论大概是:

InnoDB采用compressed行格式后,OLTP整体性能大约为原来的1/10,压缩率约为50%。

按照这个结论,压缩行格式不建议用在TPS较高的OLTP场景,如果有类似的业务需要,可以考虑用TokuDB或RocksDB引擎。
尝试过用TokuDB当做Zabbix的后端数据库,效果还不错,详情见 迁移Zabbix数据库到TokuDB
不过,TokuDB现在已经基本被Percona抛弃了,还有这类业务需求时,可以考虑改用RocksDB引擎,可以参考这篇文章 MyRocks引擎:入坑须知
随着MySQL 8.0.20的发布,我又重燃了对compressed行格式的兴趣,今日就此再做了个简单测试。

1. 测试环境

本次测试的服务器配置是腾讯云”标准型S5″型CVM主机,具体配置是:

配置项 参数
CPU 4 Core(Intel(R) Xeon(R) Platinum 8255C CPU @ 2.50GHz)
内存 16GB
数据盘 500GB SSD云硬盘(理论最大随机IOPS值 16800,实际上最高也只能跑到10000不到)

my.cnf中InnoDB相关配置参数(其余采用默认设置)

innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_size=8G
innodb_log_file_size = 2G

MySQL选用最新的8.0.20版本:

Server version:     8.0.20 MySQL Community Server - GPL

2. 进行测试

本次测试计划分为两种模式
a) 所有数据可以加载到buffer pool中
b) 数据量超过内存ibp容量
针对上述两种模式再分别对dynamic、compressed行格式的区别。

2.1 所有数据可以加载到buffer pool中

相应的sysbench参数如下:

TBLCNT=50 #共50个表
DURING=900 #一次压测900秒(5分钟)
ROWS=100000 #每个表10万行数据
MAXREQ=5000000 #每个线程执行500万次请求

2.1.1 数据压缩率

未压缩格式(KB) 压缩格式(KB) 压缩率(1-压缩格式/未压缩格式)
1638456 1218588 25.63%

2.1.2 TPS相差值

数值说明:这表示 未压缩格式 相对于 压缩格式的提升比例,例如上图中第一列的 71.11%,表示 在OLTP模式下,并发256线程压测时,未压缩行格式的TPS相对于压缩行格式增加71.11%,下同。

2.1.3 平均延迟差值 avg Latency (ms)

2.1.4 99%延迟差值 99th percentile Latency (ms)

根据测试结果的几点结论:
a) 当数据都能放在buffer pool中的时候,是否采用压缩格式对于读的业务场景影响很小。
b) 当数据都能放在buffer pool中的时候,混合OLTP业务场景或者以更新为主的业务场景中,Dynamic行格式明显要比Compressed行格式的性能更好。
综上,当数据量比较小的时候,并且读多写少的业务场景中,可以考虑使用Compressed行格式。而如果是写多读少的业务场景,则最好使用Dynamic行格式。

2.2 数据量超过内存ibp容量

sysbench参数调整ROWS,其余不变。

ROWS=5000000 #每个表500万行数据

2.2.1 数据压缩率

未压缩格式(KB) 压缩格式(KB) 压缩率(1-压缩格式/未压缩格式)
59596904 40210556 34.03%

2.2.2 TPS相差值

2.2.3 平均延迟差值 avg Latency (ms)

2.2.4 99%延迟差值 99th percentile Latency (ms)

根据测试结果的几点结论:
a) 当数据无法全部放在buffer pool中的时候,如果是读多写少的业务场景,则用Compressed行格式性能更高。
b) 当数据无法全部放在buffer pool中的时候,如果是写多读少的业务场景,则用Dynamic行格式性能更高。
综上,当数据量比较小的时候,并且读多写少的业务场景中,可以考虑使用压缩行格式。

3. 总结

根据上面的测试结果来看,如果是下面几种业务场景,则可以考虑使用InnoDB表想要使用compressed行格式:
a) 对压缩比需求不是特别高,本案中,只压缩了 25% ~ 34% 数据量,优势不大。
b) 数据量无法全部加载到buffer pool中的时候,读多写少的业务场景。

本案中,测试条件存在几点不足:
a) 服务器配置不算高。
b) 测试持续时长不够,只有15分钟。
c) 测试表和实际业务预计相差比较大,实际业务环境中,可能文本类型列会多一些,这样压缩比也会高一些。

综合来看,类似下面的业务场景,可以考虑使用compressed格式:
a) 数据量较大,且文本数据较多。
b) 磁盘比较紧张。
c) 读多写少。
最后,最好还是自己再亲自测试下比较靠谱哈。

延伸阅读

Enjoy MySQL :)

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。

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

继续放弃治疗。​

0. 背景介绍

前文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 /mysql/data01/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.

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,全文搜索胜出。

第二个关键词:田大仁
这次的全文搜索耗时则慢了很多

# Query_time: 72.822958  Rows_sent: 2334  Rows_examined: 1850544
...

| FULLTEXT initialization        | 67.449839 |
| executing                      |  5.083728 |

这个关键词的扫描行数是 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 阶段提升了,加上 LIMIT 5 之后,耗时从 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的修行之旅吧。

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

暂不建议。

0. 背景介绍

MySQL从5.6版本开始,InnoDB也支持全文索引(fulltext),从5.7开始新增ngram插件以支持对中文的全文索引,以及用MeCab解析日文。
为了验证全文搜索的效果,我做了个简单的测试。

1. 环境准备

本次测试我采用的是MySQL 8.0.19版本。
另外,即便有了ngram这个中文分词插件,但其实分词效果还是不太理想,所以我修改了几个参数:

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我设置为10GB。

创建测试表:

[root@yejr.run]> CREATE TABLE `t_fulltxt` (
  id int unsigned NOT NULL,
  author varchar(100) NOT NULL,
  title varchar(100) NOT NULL,
  summary varchar(255) NOT NULL,
  bdata longtext,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `k1` (`title`,`summary`,`bdata`) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 导入数据

我在某小说网站上下载了一批数据,并每个文件进行切分,保证每条记录里的中文数据不超过96KB,这样一个大文件就会被切分成多次导入。
最后,共导入了约14万条数据,看下表统计信息:

[root@yejr.run]> show table status\G
           Name: t_fulltxt
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 136554
 Avg_row_length: 109696   --行平均长度107KB,太大了
    Data_length: 14574764032
Max_data_length: 0
   Index_length: 5275648
      Data_free: 5242880
 Auto_increment: NULL
    Create_time: 2020-04-29 20:27:44
    Update_time: 2020-04-30 01:25:20
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:

#表空间文件有15GB
[root@yejr.run]# ls -l test/t_fulltxt.ibd
-rw-r----- 1 mysql mysql 15145631744 Apr 30 01:28 test/t_fulltxt.ibd

#索引文件加起来比表空间文件还要大
[root@yejr data01]# ll test/fts_000000000000050b_*
-rw-r----- 1 mysql mysql  1216348160 Apr 30 01:28 test/fts_000000000000050b_00000000000001b8_index_1.ibd
-rw-r----- 1 mysql mysql      114688 Apr 29 20:27 test/fts_000000000000050b_00000000000001b8_index_2.ibd
-rw-r----- 1 mysql mysql      114688 Apr 29 20:27 test/fts_000000000000050b_00000000000001b8_index_3.ibd
-rw-r----- 1 mysql mysql      114688 Apr 29 20:27 test/fts_000000000000050b_00000000000001b8_index_4.ibd
-rw-r----- 1 mysql mysql      114688 Apr 29 20:27 test/fts_000000000000050b_00000000000001b8_index_5.ibd
-rw-r----- 1 mysql mysql 15544090624 Apr 30 01:28 test/fts_000000000000050b_00000000000001b8_index_6.ibd
-rw-r----- 1 mysql mysql      114688 Apr 29 20:27 test/fts_000000000000050b_being_deleted.ibd
-rw-r----- 1 mysql mysql      114688 Apr 29 20:27 test/fts_000000000000050b_being_deleted_cache.ibd
-rw-r----- 1 mysql mysql      114688 Apr 30 01:25 test/fts_000000000000050b_config.ibd
-rw-r----- 1 mysql mysql      114688 Apr 29 20:27 test/fts_000000000000050b_deleted.ibd
-rw-r----- 1 mysql mysql      114688 Apr 29 20:27 test/fts_000000000000050b_deleted_cache.ibd

索引文件都已经超过数据文件了,也超过了innodb buffer pool,可以预见其搜索的性能可能也不会太好。

3. 全文搜索

我拿几个关键词进行搜索测试,发现SQL执行耗时都特别大,不过搜索的相关性也还算有一定保证。
先看下SQL的执行计划

[root@yejr.run]> EXPLAIN select id,cid,left(title,10) as title,left(author,10) as author,left(summary,10) as summary,instr(bdata,'艾伦') as pos,
            match(title,summary,bdata) AGAINST('艾伦' IN BOOLEAN MODE) as score
            from t_fulltxt where MATCH(title,summary,bdata) AGAINST('艾伦' IN NATURAL LANGUAGE MODE) and
            match(title,summary,bdata) AGAINST('艾伦' IN BOOLEAN MODE) >0 order by score LIMIT 3;
*************************** 1. row ***************************            
           id: 1
  select_type: SIMPLE
        table: t_fulltxt
   partitions: NULL
         type: fulltext
possible_keys: k1
          key: k1
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Ft_hints: no_ranking; Using filesort

上面这条SQL的目的是按全文搜索相关性,从小到大排序,取最小的前三条记录。
再看这条SQL的实际耗时

# Query_time: 127.635140  Lock_time: 0.000238 Rows_sent: 3  Rows_examined: 52415

如果换成一个比较常见的词组搜索,则耗时要大很多:

# Query_time: 520.929179  Lock_time: 0.000142 Rows_sent: 3  Rows_examined: 141045
...
where MATCH(title,summary,bdata) AGAINST('时间' IN NATURAL LANGUAGE MODE)
...

另外,我们注意到,无论是在查看SQL执行计划,还是在执行搜索时,一开始都有这个状态

FULLTEXT initialization

The server is preparing to perform a natural-language full-text search.

如果是执行SQL查询,那么这个状态持续的时间会更久一些,有时甚至达到了几十上百秒。

在SQL执行期间,看了下服务器的负载数据

[root@yejr.run]# vmstat -S m 1
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  1    969    177      0   2386    0    0 155360     0 3859 5548  1  2 74 23  0
 0  1    969    173      0   2389    0    0 158420   262 4123 5873  1  2 74 23  0
 1  0    969    171      0   2400    0    0 146852  3071 4270 6085  1  4 71 24  0
 0  1    969    173      0   2398    0    0 106900  3160 3019 4765  1  2 74 24  0

物理I/O读太大了,看起来可能是因为innodb buffer pool不够,所以比较慢。
不过,关键词查询过一次后,后面的查询就会相对快很多,例如上面的两个关键词在后面的查询大概只需要耗时1.5秒。然鹅,这是在表中数据不再发生变化的前提下。
but,当表中数据发生变化后,再次执行查询的耗时又比较高了。这就像MySQL 8.0之前的Query Cache那样,让人很不爽了。

既然是因为innodb buffer pool不够导致比较慢,那么如果换成小表是不是会快一些呢。
于是从原来的表中取1万条数据,插入到新表中。新标的全文索引也只有bdata列,不再包含title, summary两个列。新表大小1.1GB,索引文件1.4GB,都没超过innodb buffer pool
再次执行全文搜索查询,这次的的耗时的确提升了很多:

# Query_time: 0.349465  Lock_time: 0.000165 Rows_sent: 3  Rows_examined: 3
...
select id,cid,left(title,10) as title,left(author,10) as author,
left(summary,10) as summary,instr(bdata,'时间') as pos,
match(bdata) AGAINST('时间' IN BOOLEAN MODE) as score
from t2 where MATCH(bdata) AGAINST('时间' IN NATURAL LANGUAGE MODE) and
match(bdata) AGAINST('时间' IN BOOLEAN MODE) >0 LIMIT 3;

这个SQL相对于之前去掉了对score排序,所以相对还是快了点。
不过,反复执行多次同样的SQL,执行耗时没办法继续下降了,基本上都维持这个值左右。
用profile查看这个SQL的耗时,发现大部分是在 FULLTEXT initialization 阶段:

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
...
| FULLTEXT initialization        | 0.343885 |
| executing                      | 0.003677 |
...

4. 放弃治疗吧

从上面的几个简单测试中也能看出来MySQL的全文索引效率还是比较低,才十几万的数据量,每次出现新关键词的第一次查询耗时几乎无法承受,虽然后面重复查询好了一些,但如果是并发度稍高的场景里,估计又要歇菜了。
即便是已经把整个全文索引都加载到innodb buffer pool中了,数据量也才一万条,查询效率也还是不如意。

测试下来,全文索引有几点不足之处:
– 虽然支持online ddl,但在DDL期间,会阻塞DML请求(此时只能只读查询)。
– 首次创建全文索引时非常慢,因为此时需要重建整张表(见下方文档中的解释)。重建过程中,生成的临时文件是原表的数倍大小。
– 按照文档中的说法,创建第二个全文索引应该不再需要重建整张表了,会快一些了。但实测依然要重建,还是慢的不能接受。
– 不能同时创建两个全文索引。

[root@yejr.run]> alter table t2 add fulltext k4(title) with parser ngram, add fulltext index k5(summary) with parser ngram;
ERROR 1795 (HY000): InnoDB presently supports one FULLTEXT index creation at a time
  • 官方文档中几乎没有关于全文搜索优化的内容。

MySQL文档中关于全文索引Online DDL的描述

Operation Instant In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Adding a FULLTEXT index No Yes* No* No No

Adding a FULLTEXT index

Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Additional FULLTEXT indexes may be added without rebuilding the table.

文档链接:https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-index-operations

最后,个人建议现在还不适合在MySQL里玩全文索引,先继续保持观望吧

2020.5.9更新
创建新表,只有summary字段有全文索引,6438121条数据

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;

summary列最长255个字符,最大757字节,最少21字节。

关键词”绿毒蛙”,多次反复全文搜索查询,耗时约为1.67 ~ 1.75秒之间,利用profiling查看耗时最久的两个阶段

# Query_time: 1.691762  Rows_sent: 13  Rows_examined: 120473
...

| FULLTEXT initialization        | 1.384282 |
| executing                      | 0.324287 |

同样是”绿毒蛙”关键词,改成 like ‘%绿毒蛙%’ 模糊搜索,耗时约为7.5 ~ 7.9秒之间,profiling的结果

# Query_time: 7.511337  Rows_sent: 13  Rows_examined: 6438121
...

| executing                      | 7.511123 |

不过,换成一个新的关键词”田大仁”后,模糊搜索耗时还是和原来相当,但全文搜索耗时慢了很多

# Query_time: 72.822958  Rows_sent: 2334  Rows_examined: 1850544
...

| FULLTEXT initialization        | 67.449839 |
| executing                      |  5.083728 |

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

SELECT ... LIMIT 3;
...
# Query_time: 67.928363  Rows_sent: 3  Rows_examined: 3

profiling 的结果来看,在 FULLTEXT initialization 阶段的耗时没有发生变化(约68秒),只是在 executing 阶段提升了,加上 LIMIT 3 之后,耗时从 5秒 降低到 0.7秒。

如果是换成更常见的词,则全文搜索要更久,例如”时间”这个关键词要 553.69秒,即便加上 LIMIT N也需要543秒。

综上,InnoDB全文搜索如果没有靠谱的分词库的话,还是不建议在生产环境上使用,个人愚见 :)

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。