MySQL統(tǒng)計信息查詢慢問題分析

起因

在MySQL服務(wù)器運(yùn)行mysqld_exporter后，發(fā)現(xiàn)數(shù)據(jù)庫的中活躍連接數(shù)暴增，而且都是來自于mysqld_exporter的慢查詢，語句如下：

SELECT
            TABLE_SCHEMA,
            TABLE_NAME,
            TABLE_TYPE,
            ifnull(ENGINE, 'NONE') as ENGINE,
            ifnull(VERSION, '0') as VERSION,
            ifnull(ROW_FORMAT, 'NONE') as ROW_FORMAT,
            ifnull(TABLE_ROWS, '0') as TABLE_ROWS,
            ifnull(DATA_LENGTH, '0') as DATA_LENGTH,
            ifnull(INDEX_LENGTH, '0') as INDEX_LENGTH,
            ifnull(DATA_FREE, '0') as DATA_FREE,
            ifnull(CREATE_OPTIONS, 'NONE') as CREATE_OPTIONS
          FROM information_schema.tables
          WHERE TABLE_SCHEMA = 'xxx';

分析

1.在該數(shù)據(jù)庫執(zhí)行該語句，執(zhí)行時間非常慢

102 rows in set (6.35 sec)

2.在該數(shù)據(jù)庫的從庫執(zhí)行，結(jié)果卻完全不一樣

102 rows in set (0.01 sec)

3.這個時候就可以確定應(yīng)該跟MySQL統(tǒng)計信息有關(guān)。

查看MySQL統(tǒng)計信息相關(guān)介紹：https://blog.csdn.net/n88Lpo/article/details/79144495

4.查看主從數(shù)據(jù)庫的參數(shù)，發(fā)現(xiàn)差異

##主庫
mysql> show variables like 'innodb_stats_on_metadata';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | ON    |
+--------------------------+-------+
1 row in set (0.00 sec)

##從庫
mysql> show variables like 'innodb_stats_on_metadata';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_stats_on_metadata | OFF   |
+--------------------------+-------+
1 row in set (0.00 sec)

5.確定原因為每次查詢時都會對統(tǒng)計信息進(jìn)行更新。

查看MySQL官方文檔 https://dev.mysql.com/doc/refman/5.7/en/innodb-statistics-estimation.html

解決

修改參數(shù)innodb_stats_on_metadata

mysql> set global innodb_stats_on_metadata=0;
Query OK, 0 rows affected (0.00 sec)