##
一个咨询单引发的思考：诚实回答客户的提问
1、概述
这是一个做咨询的单子，具体的工作内容就是回答客户的问题，今天突然给我一个截图，在查询information_schema中一张表，但是没有返回结果集。于是给我发消息询问为什么。使用MySQL 8.0已经有一段时间了，不确定这张表为什么没有结果集返回，于是就给客户说不知道，需要查下资料再回复，并记录了数据库的基本信息。客户也没有多说话，只是等待我的回复，事实上这不是我第一次给客户说我不知道某个问题的具体答案。在客户使用数据库的过程中，会有非常多的问题，有些我甚至从来没有听说过，但这并不影响我和客户的合作，在我长期合作的项目中，咨询项目占了三分之一，一句老话，知之为知之，不知为不知，是知也。最近网络有句话，真诚才是必杀技，我想也是适用的。
2、案例重现以及解决方法
[root@mydb01 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-48-log Percona Server (GPL), Release 48, Revision 497f936a373
Copyright (c) 2009-2023 Percona LLC and/or its affiliates
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from information_schema.TABLE_STATISTICS;
Empty set (0.00 sec)
mysql>
mysql> set global userstat=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from information_schema.TABLE_STATISTICS;
Empty set (0.00 sec)
mysql> \q
Bye
[root@mydb01 ~]# sysbench --db-driver=mysql --time=10 --threads=20 --report-interval=5 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=5 --table_size=10000 oltp_read_write --db-ps-mode=disable run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 20
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 20 tps: 409.15 qps: 8233.58 (r/w/o: 5770.28/1641.00/822.30) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 20 tps: 423.08 qps: 8459.55 (r/w/o: 5922.09/1691.31/846.16) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 58548
write: 16728
other: 8364
total: 83640
transactions: 4182 (416.80 per sec.)
queries: 83640 (8335.96 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0319s
total number of events: 4182
Latency (ms):
min: 4.58
avg: 47.87
max: 295.50
95th percentile: 70.55
sum: 200185.92
Threads fairness:
events (avg/stddev): 209.1000/7.36
execution time (avg/stddev): 10.0093/0.01
[root@mydb01 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.44-48-log Percona Server (GPL), Release 48, Revision 497f936a373
Copyright (c) 2009-2023 Percona LLC and/or its affiliates
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from information_schema.TABLE_STATISTICS;
+--------------+------------+-----------+--------------+------------------------+
| TABLE_SCHEMA | TABLE_NAME | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+--------------+------------+-----------+--------------+------------------------+
| test_db | sbtest1 | 349972 | 3292 | 6584 |
| test_db | sbtest4 | 345458 | 3420 | 6840 |
| test_db | sbtest3 | 345751 | 3354 | 6708 |
| test_db | sbtest2 | 355026 | 3402 | 6804 |
| test_db | sbtest5 | 344866 | 3260 | 6520 |
+--------------+------------+-----------+--------------+------------------------+
5 rows in set (0.00 sec)
mysql>
3、原因
This feature adds several INFORMATION_SCHEMA tables, several commands, and the userstat variable. The tables and commands can be used to understand the server activity better and identify the source of the load.
The functionality is disabled by default, and must be enabled by setting userstat to ON. It works by keeping several hash tables in memory. To avoid contention over global mutexes, each connection has its own local statistics, which are occasionally merged into the global statistics, and the local statistics are then reset to 0.
4、总结
多看官网文档，多实验。有时候官网的一句话就是一个问题的答案。
