溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

MySQL 8.0統(tǒng)計信息不準(zhǔn)確的原因

發(fā)布時間：2020-09-15 23:05:51 來源：腳本之家 閱讀：215 作者：Bright@DBA 欄目：開發(fā)技術(shù)

前言

不管是Oracle還是MySQL，新版本推出的新特性，一方面給產(chǎn)品帶來功能、性能、用戶體驗等方面的提升，另一方面也可能會帶來一些問題，如代碼bug、客戶使用方法不正確引發(fā)問題等等。

案例分享

MySQL 5.7下的場景

（1）首先，創(chuàng)建兩張表，并插入數(shù)據(jù)

mysql> select version();
+------------+
| version() |
+------------+
| 5.7.30-log |
+------------+
1 row in set (0.00 sec)

mysql> show create table test\G
*************************** 1. row ***************************
    Table: test
Create Table: CREATE TABLE `test` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `k` int(10) unsigned NOT NULL DEFAULT '0',
 `c` char(120) NOT NULL DEFAULT '',
 `pad` char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000
1 row in set (0.00 sec)

mysql> show create table sbtest1\G
*************************** 1. row ***************************
    Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `k` int(10) unsigned NOT NULL DEFAULT '0',
 `c` char(120) NOT NULL DEFAULT '',
 `pad` char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 MAX_ROWS=1000000
1 row in set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|   100 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.14 sec)

（2）查看兩張表的統(tǒng)計信息，均比較準(zhǔn)確

mysql> select table_schema,table_name,table_rows from tables where table_name='test';
+--------------+------------+------------+
| table_schema | table_name | table_rows |
+--------------+------------+------------+
| test     | test    |    100 |
+--------------+------------+------------+
1 row in set (0.00 sec)

mysql> select table_schema,table_name,table_rows from tables where table_name='sbtest1';
+--------------+------------+------------+
| table_schema | table_name | table_rows |
+--------------+------------+------------+
| test     | sbtest1  |   947263 |
+--------------+------------+------------+
1 row in set (0.00 sec)

（3）我們持續(xù)往test表插入1000w條記錄，并再次查看統(tǒng)計信息，還是相對準(zhǔn)確的，因為在默認(rèn)情況下，數(shù)據(jù)變化量超過10%，就會觸發(fā)統(tǒng)計信息更新

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 10000100 |
+----------+
1 row in set (1.50 sec)

mysql> select table_schema,table_name,table_rows from tables where table_name='test';
+--------------+------------+------------+
| table_schema | table_name | table_rows |
+--------------+------------+------------+
| test     | test    |  9749036 |
+--------------+------------+------------+
1 row in set (0.00 sec)

MySQL 8.0下的場景

（1）接下來我們看看8.0下的情況吧，同樣地，我們創(chuàng)建兩張表，并插入相同記錄

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.20  |
+-----------+
1 row in set (0.00 sec)

mysql> show create table test\G
*************************** 1. row ***************************
    Table: test
Create Table: CREATE TABLE `test` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `k` int unsigned NOT NULL DEFAULT '0',
 `c` char(120) NOT NULL DEFAULT '',
 `pad` char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
1 row in set (0.00 sec)

mysql> show create table sbtest1\G
*************************** 1. row ***************************
    Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `k` int unsigned NOT NULL DEFAULT '0',
 `c` char(120) NOT NULL DEFAULT '',
 `pad` char(60) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
1 row in set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|   100 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.02 sec)

（2）查看兩張表的統(tǒng)計信息，均比較準(zhǔn)確

mysql> select table_schema,table_name,table_rows from tables where table_name='test';
+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS |
+--------------+------------+------------+
| test     | test    |    100 |
+--------------+------------+------------+
1 row in set (0.00 sec)

mysql> select table_schema,table_name,table_rows from tables where table_name='sbtest1';
+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS |
+--------------+------------+------------+
| test     | sbtest1  |   947468 |
+--------------+------------+------------+
1 row in set (0.01 sec)

（3）同樣地，我們持續(xù)往test表插入1000w條記錄，并再次查看統(tǒng)計信息，發(fā)現(xiàn)table_rows顯示還是100條，出現(xiàn)了較大偏差

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 10000100 |
+----------+
1 row in set (0.33 sec)

mysql> select table_schema,table_name,table_rows from tables where table_name='test';
+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS |
+--------------+------------+------------+
| test     | test    |    100 |
+--------------+------------+------------+
1 row in set (0.00 sec)

原因剖析

那么導(dǎo)致統(tǒng)計信息不準(zhǔn)確的原因是什么呢？其實是MySQL 8.0為了提高information_schema的查詢效率，將視圖tables和statistics里面的統(tǒng)計信息緩存起來，緩存過期時間由參數(shù)information_schema_stats_expiry決定，默認(rèn)為86400s；如果想獲取最新的統(tǒng)計信息，可以通過如下兩種方式：

（1）analyze table進(jìn)行表分析

（2）設(shè)置information_schema_stats_expiry=0

繼續(xù)探索

那么統(tǒng)計信息不準(zhǔn)確，會帶來哪些影響呢？是否會影響執(zhí)行計劃呢？接下來我們再次進(jìn)行測試

測試1：表test記錄數(shù)100，表sbtest1記錄數(shù)100w

執(zhí)行如下SQL，查看執(zhí)行計劃，走的是NLJ，小表test作為驅(qū)動表(全表掃描)，大表sbtest1作為被驅(qū)動表(主鍵關(guān)聯(lián))，執(zhí)行效率很快

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|   100 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.02 sec)

mysql> select table_schema,table_name,table_rows from tables where table_name='test';
+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS |
+--------------+------------+------------+
| test     | test    |    100 |
+--------------+------------+------------+
1 row in set (0.00 sec)

mysql> select table_schema,table_name,table_rows from tables where table_name='sbtest1';
+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS |
+--------------+------------+------------+
| test     | sbtest1  |   947468 |
+--------------+------------+------------+
1 row in set (0.01 sec)

mysql> select t.* from test t inner join sbtest1 t1 on t.id=t1.id where t.c='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' and t1.c='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977';
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k   | c                                                            | pad                             |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 501885 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select t.* from test t inner join sbtest1 t1 on t.id=t1.id where t.c='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' and t1.c='08566691963-88624912351-16662227201-4664
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref    | rows | filtered | Extra    |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE   | t   | NULL    | ALL  | PRIMARY    | NULL  | NULL  | NULL   | 100 |  10.00 | Using where |
| 1 | SIMPLE   | t1  | NULL    | eq_ref | PRIMARY    | PRIMARY | 4    | test.t.id |  1 |  10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

測試2：表test記錄數(shù)1000w左右，表sbtest1記錄數(shù)100w

再次執(zhí)行SQL，查看執(zhí)行計劃，走的也是NLJ，相對小表sbtest1作為驅(qū)動表，大表test作為被驅(qū)動表，也是正確的執(zhí)行計劃

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 10000100 |
+----------+
1 row in set (0.33 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.02 sec)

mysql> select table_schema,table_name,table_rows from tables where table_name='test';
+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS |
+--------------+------------+------------+
| test     | test    |    100 |
+--------------+------------+------------+
1 row in set (0.00 sec)

mysql> select table_schema,table_name,table_rows from tables where table_name='sbtest1';
+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS |
+--------------+------------+------------+
| test     | sbtest1  |   947468 |
+--------------+------------+------------+
1 row in set (0.01 sec)

mysql> select t.* from test t inner join sbtest1 t1 on t.id=t1.id where t.c='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' and t1.c='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977';
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k   | c                                                            | pad                             |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 501885 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
+----+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.37 sec)

mysql> explain select t.* from test t inner join sbtest1 t1 on t.id=t1.id where t.c='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' and t1.c='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977';
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref    | rows  | filtered | Extra    |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------------+
| 1 | SIMPLE   | t1  | NULL    | ALL  | PRIMARY    | NULL  | NULL  | NULL    | 947468 |  10.00 | Using where |
| 1 | SIMPLE   | t   | NULL    | eq_ref | PRIMARY    | PRIMARY | 4    | test.t1.id |   1 |  10.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+--------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

為什么優(yōu)化器沒有選擇錯誤的執(zhí)行計劃呢？之前文章也提過，MySQL 8.0是將元數(shù)據(jù)信息存放在mysql庫下的數(shù)據(jù)字典表里，information_schema庫只是提供相對方便的視圖供用戶查詢，所以優(yōu)化器在選擇執(zhí)行計劃時，會從數(shù)據(jù)字典表中獲取統(tǒng)計信息，生成正確的執(zhí)行計劃。

總結(jié)

MySQL 8.0為了提高information_schema的查詢效率，會將視圖tables和statistics里面的統(tǒng)計信息緩存起來，緩存過期時間由參數(shù)information_schema_stats_expiry決定(建議設(shè)置該參數(shù)值為0)；這可能會導(dǎo)致用戶查詢相應(yīng)視圖時，無法獲取最新、準(zhǔn)確的統(tǒng)計信息，但并不會影響執(zhí)行計劃的選擇。

以上就是MySQL 8.0統(tǒng)計信息不準(zhǔn)確的原因的詳細(xì)內(nèi)容，更多關(guān)于MySQL 8.0統(tǒng)計信息不準(zhǔn)確的資料請關(guān)注億速云其它相關(guān)文章！

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手