mysql - 计算多个表名出现在一列字符串中的次数

Question

我正在尝试计算多个表名在 mysql.general_log 表中出现的次数。

例如 mysql.general_log 表，参数列的值如下:

+------------------------------------------------------------------------------+
| argument                                                                     |
+------------------------------------------------------------------------------+
| SET GLOBAL general_log = 'ON'                                                |
| SELECT * FROM TABLEB WHERE val = 1 LIMIT 0, 1000                             |
| SHOW INDEX FROM 'DB1'.'TABLEB'                                               |
| DELETE FROM TABLEC WHERE val = 1                                             |
| UPDATE TABLEC SET val2 = 5 where val = 8                                     |
| DELETE FROM TABLEA WHERE val = 1                                             |
| DELETE FROM TABLED WHERE val = 1                                             |
| SELECT COUNT(*) FROM TABLED WHERE val = 1 LIMIT 0, 1000                      |
| DELETE FROM TABLEB WHERE val = 1                                             |
| SELECT COUNT(*) FROM TABLEB LIMIT 0, 1000                                    |
| UPDATE TABLEB SET city = 'NYC' WHERE val IN (2,6)                            |
| SELECT name FROM TABLEA WHERE val IN (2,3) LIMIT 0, 1000                     |
| SHOW INDEX FROM ''DB1.'TABLEA'                                               |
| INSERT INTO TABLEB VALUES(34)                                                |
| INSERT INTO TABLEA VALUES(34)                                                |
| INSERT INTO TABLEA VALUES(34)                                                |
| COMMIT                                                                       |
| SET GLOBAL general_log = 'OFF'                                               |
+------------------------------------------------------------------------------+

然后目标是让表格显示每个使用的表格的计数

|TABLE NAME| COUNTS 
| TABLEA   |  5
| TABLEB   |  5
| TABLEC   |  2
| TABLED   |  1

到目前为止，这是我能想到的最佳解决方案:

SELECT argument, COUNT (*) FROM mysql.general_log
    WHERE  argument LIKE '%TABLEA%'   OR
           argument LIKE '%TABLEB%'   OR
           argument LIKE '%TABLEC%'   OR
           argument LIKE '%TABLED%'
    GROUP BY argument ORDER BY TIMES ASC;

我还考虑过可能使用 information_schema.TABLES 并选择 TABLE_SCHEMA = 'DB1'; 所在的所有行来获取表名(如果这提供了任何线索)到一个解决方案。

Answer 1

缓慢而肮脏的版本(非常接近您自己的建议)

SELECT t.table_name, COUNT(l.argument)  
FROM information_schema.tables t 
LEFT JOIN mysql.general_log l 
  ON (l.argument REGEXP CONCAT('[[:<:]]',t.table_name,'[[:>:]]') = 1)
WHERE t.table_schema='DB1' 
GROUP BY t.table_name;

例如

SELECT t.table_name, COUNT(l.argument)  
FROM information_schema.tables t 
LEFT JOIN mysql.general_log l 
  ON (l.argument REGEXP CONCAT('[[:<:]]',t.table_name,'[[:>:]]') = 1)
WHERE t.table_schema='onemillion' 
GROUP BY t.table_name;
+---------------+-------------------+
| table_name    | COUNT(l.argument) |
+---------------+-------------------+
| citest        |                10 |
| invoice       |                 1 |
| invoice_box   |                 0 |
| invoice_myaes |                 1 |
| item          |                 1 |
| milltest      |                 2 |
| sbtest1       |                 3 |
| sbtest1_myaes |                 3 |
| sbtest2       |                 0 |
| sbtest2_myaes |                 0 |
| users         |                 6 |
| users_myaes   |                 3 |
+---------------+-------------------+
12 rows in set (0.23 sec)

如前所述，这只会为您提供日志记录，其中包含与您的表名匹配的其他标识符。它不会为您提供有关正在运行的语句的性质的任何线索，因此您不知道这些日志行是否表示 UPDATE、INSERT、SELECT 等，它们将全部混在一起。根据您的要求和可能足以进行粗略估计的表名。

如果您有 5.6 或更高版本，也许更好的方法是使用 performance_schema 并以适当的时间间隔查询 events_statements_history_long 表。

例如

SELECT t.table_name, event_name, COUNT(e.digest_text) 
FROM information_schema.tables t
LEFT JOIN performance_schema.events_statements_history_long e 
 ON (e.digest_text LIKE CONCAT('%`',t.table_name,'`%'))
WHERE t.table_schema = 'DB1' 
GROUP BY table_name, event_name;  

除了能够按语句类型分组外，这里的优点是 mysql 将规范化查询并在表名周围使用反引号，因此您可以确定标识符上的精确匹配。默认情况下，MySQL 不启用对该表的收集，因此您可能需要在 setup_instruments 中启用它，并使用 --performance-schema-events 调整要在服务器启动时收集的历史记录量-statements-history-long-size=a_suitably_large_number.

Full details on performance_schema