为何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的修行之旅吧。

MySQL企业版之Firewall(SQL防火墙)

利用Friewall阻挡恶意SQL请求。

1. 关于Firewall插件

Friewall是MySQL企业版非常不错的功能插件之一,启用Firewall功能后,SQL的执行流程见下图示意:

2. Firewall插件的工作方式

Firewall插件的工作机制大概是这样的:
0.将某个账号Register(注册)到Firewall插件中,未注册的账号将不会被Firewall插件保护。
1.先将Firewall插件设置 recording(记录)模式,将各种SQL格式化/模式化之后,形成各种不同的SQL fingerprint(指纹)。例如下面的两条SQL,都会被格式化成一条:

# 原始SQL
a) SELECT * FROM t1 WHERE c1 = 1;
b) SELECT * FROM t1 WEHRE c1 = 1024;

# 格式化之后的SQL
SELECT * FROM t1 WHERE c1 = ?;

备注:在这个过程中,如果总有超长SQL的话,需要加大参数 max_digest_length 的设置,其默认值是1024。
2.Firewall插件会学习上述SQL,形成一个白名单。
3.经过一段时间的训练后,可以将Firewall插件工作模式切换为 protecting(保护)模式,开始工作。这时候就能自动判断有哪些SQL可能是恶意的,会被自动拒绝,并且记录到日志中,如果启用参数 mysql_firewall_trace的话。
4.如果还发生个别SQL被拒绝的情况,则可以将插件切换回 recording(记录)模式,继续学习训练一段时间再切换到工作状态。
5.此外,还有一种工作模式是detecting(探测),在这个模式下,符合白名单的会被放过执行,而其他SQL则会被记录到日志中,但并不会被拒绝执行,这就相当于正式开始工作前的灰度测试模式了。

简言之,就是在业务账号对外正式开放前,先自行模拟各种正常业务请求,使之完成前期必要的学习,正式上线后再开启保护模式。因为外网生产环境中坏人太多,任意时候都有可能有坏蛋提交各种恶意破坏的请求。

3. Firewall插件测试

接下来我们做个简单的测试场景。
首先,先尝试将一个新账号直接设置为 protecting 模式,这时候该账号还未学习任何规则,因此所有的SQL应该都会被拒绝才对。

[root@yejr.run]>CALL mysql.sp_set_firewall_mode('yejr@%', 'PROTECTING');
+-------------------------------------------------------------------------+
| result                                                                  |
+-------------------------------------------------------------------------+
| ERROR: PROTECTING mode requested for yejr@% but the whitelist is empty. |
+-------------------------------------------------------------------------+

嗯,看来这个插件还挺聪明的,发现规则是空的,直接不让设置了,要不然可能会害的DBA直接下岗走人了吧,哈哈。

还是先设置为 recording 模式吧。

[root@yejr.run]>CALL mysql.sp_set_firewall_mode('yejr@%', 'recording');
+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0
Imported rules: 0
          |
+-----------------------------------------------+
1 row in set (0.01 sec)

# 手动查询 mysql.firewall_users 表确认
[root@yejr.run]>select * from mysql.firewall_users;
+----------+-----------+
| USERHOST | MODE      |
+----------+-----------+
| yejr@%   | RECORDING |
+----------+-----------+
1 row in set (0.00 sec)

设置成功。

然后用这个新账号连接MySQL,执行一些合规的SQL操作后,再查看白名单规则表:

[root@yejr.run]>select * from mysql.firewall_whitelist;
+----------+--------------------------------------------------------+----+
| USERHOST | RULE                                                   | ID |
+----------+--------------------------------------------------------+----+
| yejr@%   | SHOW CREATE TABLE `t1`                                 |  8 |
| yejr@%   | SELECT * FROM `t1`                                     |  9 |
| yejr@%   | SELECT * FROM `t1` WHERE `id` >= ?                     | 10 |
| yejr@%   | SELECT * FROM `t1` WHERE `id` = `rand` ( ) * ?         | 11 |
| yejr@%   | SELECT * FROM `t1` WHERE `id` = `rand` ( ) * ? LIMIT ? | 12 |
| yejr@%   | SELECT * FROM `t1` WHERE `c1` >= ?                     | 13 |
+----------+--------------------------------------------------------+----+

再将该账号修改为 PROTECTING 模式:

[root@yejr.run]>CALL mysql.sp_set_firewall_mode('yejr@%', 'PROTECTING');
Query OK, 6 rows affected (0.01 sec)

执行一些不合规的SQL后,看怎么报错的。

[yejr@yejr.run] [test]>select * from t1 order by rand() limit 1;
ERROR 1045 (28000): Statement was blocked by Firewall

error log中也记录了相应的行为:

2020-06-09T09:50:38.286878Z 26 [Note] [MY-011192] [Server] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for 'yejr@%'. Reason: No match in whitelist. Statement: SELECT * FROM `t1` ORDER BY `rand` ( ) LIMIT ?'

再查看几个相关的全局状态参数:

[root@yejr.run] [mysql]>show global status like '%firewall%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Firewall_access_denied     | 10    | #拒绝次数
| Firewall_access_granted    | 11    | #通过次数
| Firewall_access_suspicious | 9     | #在DETECTING模式下不匹配白名单的次数
| Firewall_cached_entries    | 6     | #在cache中的白名单数量
+----------------------------+-------+

想要关闭该账号的Firewall规则,执行下面的指令即可:

[root@yejr.run] [mysql]>call mysql.sp_set_firewall_mode('yejr@%', 'RESET');

或者只是简单地设置为 OFF 也可以:

[root@yejr.run] [mysql]>call mysql.sp_set_firewall_mode('yejr@%', 'OFF');

二者的区别在于,设置为 RESET 时,除了关闭Firewall保护,同时也会将该账号之前训练学习的白名单全部清空,这样下次再想采用Firewall保护就需要重头开始了,除非再也不用了,否则不建议这么做。

当已经对一个账号启用Firewall保护后,此时有新增业务SQL不在白名单中,除了将模式改回 RECORDINGDETECTING 之外,其实还可以手动往 mysql.firewall_whitelist 表中插入格式化之后的SQL,再刷新使之生效即可,例如:

# 先确认一条SQL的格式化写法
[root@yejr.run]>SELECT normalize_statement('SELect *,sleep(1) from t1 where 1');
+----------------------------------------------------------+
| normalize_statement('SELect *,sleep(1) from t1 where 1') |
+----------------------------------------------------------+
| SELECT * , `sleep` (?) FROM `t1` WHERE ?                 |
+----------------------------------------------------------+

# 手动插入新规则
[root@yejr.run]>insert into mysql.firewall_whitelist select 'yejr@%', 'SELECT * , `sleep` (?) FROM `t1` WHERE ?', 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

# 查询内存中的规则,此时为空
[root@yejr.run] [mysql]>SELECT * FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST;
Empty set (0.00 sec)

# reload加载规则,使之生效
[root@yejr.run] [mysql]>CALL mysql.sp_reload_firewall_rules('yejr@%');
+--------+
| Result |
+--------+
| OK     |
+--------+
1 row in set (0.00 sec)

+--------------------------------------+
| Result                               |
+--------------------------------------+
| Imported users: 0
Imported rules: 1
 |
+--------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

# 再次查询,此时就可以看到规则加载到内存中了,已生效
[root@yejr.run]>SELECT * FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST;
+----------+------------------------------------------+
| USERHOST | RULE                                     |
+----------+------------------------------------------+
| yejr@%   | SELECT * , `sleep` (?) FROM `t1` WHERE ? |
+----------+------------------------------------------+
1 row in set (0.00 sec)

# 最后记得启用保护模式
[root@yejr.run]>call mysql.sp_set_firewall_mode('yejr@%', 'PROTECTING');
Query OK, 3 rows affected (0.00 sec)

4. 总结

简单测试下来,我认为Firewall插件至少有几个地方可以更完善:
1.对SQL进行格式化时,不支持正则匹配模式,建议增加。
2.设置RECORDING模式测试期间,我执行一个SQL后,又手动执行CTRL+C终止,结果记录了一条KILL QUERY ? 的规则,这个规则就不建议记录了。
3.存储过程 mysql.sp_reload_firewall_rules() 可以增加一个参数表示重载规则后,是否要顺便设置账号的规则,例如 CALL mysql.sp_reload_firewall_rules('yejr@%', 'PROTECTING') 表示重载完规则后,顺便将该账号设置为 PROTECTING 模式。
总的来说,Firewall插件工作方式还是比较简单的,直观感觉就是对规则的学习比较初级,真正在线上生产环境启用的话,可能需要记录大量的规则,这也势必会造成性能的下降,以后再做个性能测试吧。
更多关于Firewall插件资料请查看官方手册。

延伸阅读

Enjoy MySQL 8.0 :)

全文完。


扫码加入叶老师的「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企业版之数据脱敏功能

利用企业级特性给敏感数据打码。

MySQL企业版从8.0.13开始,新增一个插件叫做Data Masking and De-Identification,我将其简称为数据打码插件,其主要功能有:
– 将部分敏感数据转换成无害数据。这就可以在查询数据库中的敏感数据时以”***”或”XXX”等方式打码显示,避免泄漏。
– 生成随机随机数据,例如邮箱或银行卡号。例如在做功能测试时,我们可能需要生成一些随机数据,就用得上了。

1. 插件安装

数据打码插件名为 data_masking,安装成功后,会生成一系列UDF,部分UDF需要授予 SUPER 权限才行。
执行下面的命令即可快速安装插件:

INSTALL PLUGIN data_masking SONAME 'data_masking.so';
CREATE FUNCTION gen_blacklist RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_dictionary RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_dictionary_drop RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_dictionary_load RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_range RETURNS INTEGER SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_email RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_pan RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_ssn RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION gen_rnd_us_phone RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_inner RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_outer RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_pan RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_pan_relaxed RETURNS STRING SONAME 'data_masking.so';
CREATE FUNCTION mask_ssn RETURNS STRING SONAME 'data_masking.so';

执行下面的命令确认安装成功:

# 查询是否已启用该插件
[root@yejr.run]> show plugins;
...
| Name                            | Status   | Type               | Library         | License     |
+---------------------------------+----------+--------------------+-----------------+-------------+
| data_masking                    | ACTIVE   | UDF                | data_masking.so | PROPRIETARY |
...

# 目前共有14个UDF
[root@yejr.run]> select count(*) from mysql.func where dl = 'data_masking.so';
+----------+
| count(*) |
+----------+
|       14 |
+----------+

到这里,就表示安装成功了,可以愉快地给数据打码了。

2. 给数据打码

2.1 隐藏重要数据

这部分共有X个UDF,我们分别举例说明。

# mask_inner()函数,从第5个字符开始打码,直到最后3个字符,用"***"代替(默认用'X'打码)
[root@yejr.run] [mysql]> SELECT mask_inner("MySQL is the world's most popular open source database", 5, 3, "*") as MySQL;
+--------------------------------------------------------+
| MySQL                                                  |
+--------------------------------------------------------+
| MySQL**********************************************ase |
+--------------------------------------------------------+

# mask_outer()函数,从头开始打码,直到第5个字符结束;再从尾部开始打码,直到倒数第三个字符结束
[root@yejr.run] [mysql]> SELECT mask_outer("MySQL is the world's most popular open source database", 5, 3) AS MySQL;
+--------------------------------------------------------+
| MySQL                                                  |
+--------------------------------------------------------+
| XXXXX is the world's most popular open source databXXX |
+--------------------------------------------------------+

2.2 生成随机数据并打码

# 生成随机数字
# 并用 gen_rnd_pan() 函数打码,只保留最后4位数,例如常用于只显示电话号码最后4位
[root@yejr.run]> select gen_rnd_pan() as RAND_NUMBER, mask_pan(gen_rnd_pan()) as MASK_RAND_NUMBER;
+------------------+------------------+
| RAND_NUMBER      | MASK_RAND_NUMBER |
+------------------+------------------+
| 1936900392284608 | XXXXXXXXXXXX2155 |
+------------------+------------------+

# 也可以只打码中间部分数字
[root@yejr.run]> select gen_rnd_pan() as RAND_NUMBER, mask_pan_relaxed(gen_rnd_pan()) as MASK_RAND_NUMBER;
+------------------+------------------+
| RAND_NUMBER      | MASK_RAND_NUMBER |
+------------------+------------------+
| 9868703631627362 | 426420XXXXXX3182 |
+------------------+------------------+

其他几个生成随机数据的函数还有
– gen_rnd_ssn(),生成美式社会安全码(身份证),例如 1234-5678-0123。相应地,可以用函数 mask_ssn() 对其打码。
– gen_range(1000, 2000),在1000 – 2000之间产生一个随机数。
– gen_rnd_email(),生成一个随机邮件地址。
– gen_rnd_us_phone(),生成美式电话号码,例如 1-555-016-7135。

2.3 基于字典生成随机值

有时候,需要随机生成国家地区城市数据,就可以先造好这些字典,加载到数据库中,再从中随机抽取。
编辑城市列表字典:

[root@yejr.run]# cat cn_cities.txt
Beijing
Shanghai
Shenzhen
Guangzhou
Hangzhou
Wuhan

# 加载到MySQL中
[root@yejr.run]> SELECT gen_dictionary_load('path/cn_cities.txt', 'CN_Cities');
+--------------------------------------------------------+
| gen_dictionary_load('path/cn_cities.txt', 'CN_Cities') |
+--------------------------------------------------------+
| Dictionary load success                                |
+--------------------------------------------------------+

# 从中随机抽取城市
[root@yejr.run]> select gen_dictionary('CN_Cities') AS City;
+----------+
| City     |
+----------+
| Shenzhen |
+----------+

# 还可以配合 ELT() 函数从多个字典中随机抽取
[root@yejr.run]> select gen_dictionary(ELT(gen_range(1,3), 'DE_Cities', 'US_Cities', 'CN_Cities')) AS City;
+---------+
| City    |
+---------+
| Beijing |
+---------+

2.4 其他要注意的地方

从MySQL 8.0.19开始,部分UDF函数默认使用latin1字符集,而在此之前默认使用binary字符集,使用过程中需要注意可能因此引发隐式类型转换导致索引不可用的风险,可以用 CONV() 函数进行转码。

总结

数据打码插件是个非常不错的功能,除了上面描述的几个场景,还可以在用在 SELECT ... INTO OUTFILE 导出线上生产数据,然后再导入本地测试环境。或者直接在线上环境中,利用这些函数将数据脱敏处理后,写入专门的测试库,让测试程序直接读取,或者再利用主从复制同步到本地测试环境,可以玩出各种花样。

更多详情请参考手册内容。

最后亲切友情提醒:MySQL企业版下载后只能试用一个月,试用完毕后记得删除卸载哟,土豪的话直接无脑付费即可哟

延伸阅读

enjoy MySQL :)

全文完。


扫码加入叶老师的「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的修行之旅吧。