查询以识别错误的索引和/或没有索引(在有大量记录的表上)需要4-6分钟来执行。

Question

查询：

SELECT Concat(t.table_schema, '.', t.table_name),
       t.table_rows,
       snu.non_unique,
       smax.cardinality,
       ( t.table_rows / Ifnull(smax.cardinality, 1) )                AS
       "medium distribution",
       t.table_rows * ( t.table_rows / Ifnull(smax.cardinality, 1) ) AS
       "replication row reads"
FROM   information_schema.tables t
       LEFT JOIN (SELECT table_schema,
                         table_name,
                         Max(cardinality) cardinality
                  FROM   information_schema.statistics
                  GROUP  BY table_schema,
                            table_name) AS smax
              ON t.table_schema = smax.table_schema
                 AND t.table_name = smax.table_name
       LEFT JOIN (SELECT table_schema,
                         table_name,
                         Min(non_unique) non_unique
                  FROM   information_schema.statistics
                  GROUP  BY table_schema,
                            table_name) AS snu
              ON t.table_schema = snu.table_schema
                 AND t.table_name = snu.table_name
WHERE  t.table_rows > 0
       AND t.table_schema <> 'information_schema'
       AND t.table_schema <> 'performance_schema'
       AND t.table_schema <> 'mysql'
       AND ( snu.non_unique IS NULL
              OR snu.non_unique = 1 )
       AND ( ( t.table_rows / Ifnull(smax.cardinality, 1) ) > 1.99 )
       AND t.table_rows * ( t.table_rows / Ifnull(smax.cardinality, 1) ) >
           100000
ORDER  BY t.table_rows * ( t.table_rows / Ifnull(smax.cardinality, 1) ) DESC; 

版本：

(none)> show variables like '%version%';
+-------------------------+---------------------------+
| Variable_name           | Value                     |
+-------------------------+---------------------------+
| innodb_version          | 5.6.36-82.1               |
| protocol_version        | 10                        |
| slave_type_conversions  |                           |
| version                 | 10.1.26-MariaDB           |
| version_comment         | Source distribution       |
| version_compile_machine | x86_64                    |
| version_compile_os      | Linux                     |
| version_malloc_library  | system                    |
| version_ssl_library     | OpenSSL 1.0.1f 6 Jan 2014 |
| wsrep_patch_version     | wsrep_25.19               |
+-------------------------+---------------------------+
10 rows in set
Time: 0.010s

解释：

+----+-------------+------------+------+---------------+--------+---------+-------------------------------------------------------------------+--------+----------+--------------------------------------------------------------------------------------+
| id | select_type | table      | type | possible_keys | key    | key_len | ref                                                               | rows   | filtered | Extra                                                                                |
+----+-------------+------------+------+---------------+--------+---------+-------------------------------------------------------------------+--------+----------+--------------------------------------------------------------------------------------+
| 1  | PRIMARY     | t          | ALL  | <null>        | <null> | <null>  | <null>                                                            | <null> | <null>   | Using where; Open_full_table; Scanned all databases; Using temporary; Using filesort |
| 1  | PRIMARY     | <derived2> | ref  | key0          | key0   | 390     | information_schema.t.TABLE_SCHEMA,information_schema.t.TABLE_NAME | 2      | 100.0    | Using where                                                                          |
| 1  | PRIMARY     | <derived3> | ref  | key0          | key0   | 390     | information_schema.t.TABLE_SCHEMA,information_schema.t.TABLE_NAME | 2      | 100.0    | Using where                                                                          |
| 3  | DERIVED     | statistics | ALL  | <null>        | <null> | <null>  | <null>                                                            | <null> | <null>   | Open_frm_only; Scanned all databases; Using temporary; Using filesort                |
| 2  | DERIVED     | statistics | ALL  | <null>        | <null> | <null>  | <null>                                                            | <null> | <null>   | Open_full_table; Scanned all databases; Using temporary; Using filesort              |
+----+-------------+------------+------+---------------+--------+---------+-------------------------------------------------------------------+--------+----------+--------------------------------------------------------------------------------------+
5 rows in set
Time: 0.022s

计数：

> select count('A') from information_schema.tables;
+------------+
| count('A') |
+------------+
| 7846       |
+------------+
1 row in set
Time: 0.069s

看起来这个无文件的Open_full_table; Scanned all databases;花了那么长时间？如何优化此查询，或者在繁忙的服务器上此持续时间是否正常？

Answer 1

MySQL 8.0从本质上解决了information_schema在InnoDB表中的所有问题。您正在运行MariaDB 10.1，它与MySQL 5.6大致相当(至少在这方面是如此)。

您所拥有的内容涉及到读取所有.frm文件--如果您有很多表，这可能会很慢(甚至几分钟)。

一个可能的解决办法..。

1. 找出你有多少张桌子。( SELECT COUNT(*) FROM information_schema.tables可能已经够近了。)
2. 将几个文件和表缓存设置为至少那么高。警告:如果这是真的很高，可能会有记忆压力，你应该避免。

你找到一些调皮的模式了吗？