MySQL 8.0統(tǒng)計信息不準確的原因
更新時間：2020年08月27日 11:52:59 作者：Bright@DBA
這篇文章主要介紹了MySQL 8.0統(tǒng)計信息不準確的原因，幫助大家更好的理解和學習MySQL8.0的相關(guān)內(nèi)容，感興趣的朋友可以了解下
前言
不管是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)計信息，均比較準確
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)計信息，還是相對準確的，因為在默認情況下，數(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)計信息，均比較準確
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)
原因剖析
那么導致統(tǒng)計信息不準確的原因是什么呢？其實是MySQL 8.0為了提高information_schema的查詢效率，將視圖tables和statistics里面的統(tǒng)計信息緩存起來，緩存過期時間由參數(shù)information_schema_stats_expiry決定，默認為86400s；如果想獲取最新的統(tǒng)計信息，可以通過如下兩種方式：
（1）analyze table進行表分析
（2）設置information_schema_stats_expiry=0
繼續(xù)探索
那么統(tǒng)計信息不準確，會帶來哪些影響呢？是否會影響執(zhí)行計劃呢？接下來我們再次進行測試
測試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ù)值為0)；這可能會導致用戶查詢相應視圖時，無法獲取最新、準確的統(tǒng)計信息，但并不會影響執(zhí)行計劃的選擇。
以上就是MySQL 8.0統(tǒng)計信息不準確的原因的詳細內(nèi)容，更多關(guān)于MySQL 8.0統(tǒng)計信息不準確的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
MySQL中使用PROFILING來查看SQL執(zhí)行流程的實現(xiàn)步驟
在MySQL中,PROFILING功能提供了一種方式來分析SQL語句的執(zhí)行時間,包括查詢執(zhí)行的各個階段,如發(fā)送、解析、優(yōu)化、執(zhí)行等,這對于診斷性能問題非常有用,本文給大家介紹了MySQL中使用PROFILING來查看SQL執(zhí)行流程的實現(xiàn)步驟,需要的朋友可以參考下2024-07-07
這篇文章主要介紹了MySQL刪除表的三種方式(小結(jié))，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-09-09
文章介紹了在MySQL 5.5.40版本中,創(chuàng)建帶有外鍵約束的`stu`和`grade`表時遇到的問題,發(fā)現(xiàn)`grade`表的`id`字段沒有隨著`student`表的`id`字段更新,并且沒有刪除的限制,問題的原因是使用了不支持外鍵的`MyISAM`引擎,而使用了支持外鍵的`InnoDB`引擎即可解決2025-01-01
解決創(chuàng)建主鍵報錯:Incorrect column specifier for
這篇文章主要介紹了解決創(chuàng)建主鍵報錯:Incorrect column specifier for column‘id‘問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-08-08
這篇文章主要給大家介紹了關(guān)于MySQL排序原理和案例詳析的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-12-12
這篇文章主要給大家介紹了關(guān)于MySQL對JSON數(shù)據(jù)進行查詢的相關(guān)資料,MySQL支持使用JSON類型存儲數(shù)據(jù),并提供了多種查詢JSON數(shù)據(jù)的方法,需要的朋友可以參考下2023-07-07
這篇文章主要介紹了mysql視圖原理與用法,結(jié)合實例形式詳細分析了mysql視圖的概念、原理、使用方法及操作注意事項,需要的朋友可以參考下2020-04-04
Prometheus 監(jiān)控MySQL使用grafana展示
這篇文章主要介紹prometheus通過mysql exporter+node exporter監(jiān)控mysql，并使用grafana進行圖表展示的相關(guān)內(nèi)容，感興趣的效果版可以參考下文2021-08-08
Mysql調(diào)優(yōu)Explain工具詳解及實戰(zhàn)演練(推薦)
這篇文章主要介紹了Mysql調(diào)優(yōu)Explain工具詳解及實戰(zhàn)演練,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-03-03
mysql unsigned 用法及相減出現(xiàn)補數(shù)溢出解決方法
unsigned表示無符號的意思，也就是非負數(shù)，只用于整型并且unsigned 是MYSQL自定義的類型，非標準SQL。用途1是紅苕說的起到約束數(shù)值的作用，2是可以增加數(shù)值范圍（相當于把負數(shù)那部分加到正數(shù)上）。不過少用，不方便移植2012-04-04