MySQL 8.0 の統計が不正確である理由

序文

Oracle であれ MySQL であれ、新バージョンで導入された新機能は、一方では製品の機能性、パフォーマンス、ユーザーエクスペリエンスなどを向上させますが、他方では、コードのバグ、顧客の誤った使用方法によって生じる問題など、いくつかの問題ももたらす可能性があります。

ケーススタディ

MySQL 5.7 シナリオ

（１）まず、２つのテーブルを作成し、データを挿入する

mysql> バージョンを選択します();
+------------+
| バージョン() |
+------------+
| 5.7.30 ログ |
+------------+
セット内の 1 行 (0.00 秒)

mysql> show テーブル作成 test\G
************************** 1. 行 ****************************
    表: テスト
テーブルの作成: CREATE TABLE `test` (
 `id` int(10) 符号なし NOT NULL AUTO_INCREMENT,
 `k` int(10) unsigned NOT NULL DEFAULT '0',
 `c` char(120) NOT NULL デフォルト ''
 `pad` char(60) NOT NULL DEFAULT ''
 主キー (`id`)、
 キー `k_1` (`k`)
) エンジン=InnoDB AUTO_INCREMENT=101 デフォルト文字セット=utf8mb4 MAX_ROWS=1000000
セット内の 1 行 (0.00 秒)

mysql> show テーブル sbtest1\G を作成します
************************** 1. 行 ****************************
    テーブル: sbtest1
テーブルの作成: CREATE TABLE `sbtest1` (
 `id` int(10) 符号なし NOT NULL AUTO_INCREMENT,
 `k` int(10) unsigned NOT NULL DEFAULT '0',
 `c` char(120) NOT NULL デフォルト ''
 `pad` char(60) NOT NULL DEFAULT ''
 主キー (`id`)、
 キー `k_1` (`k`)
) エンジン=InnoDB AUTO_INCREMENT=1000001 デフォルト文字セット=utf8mb4 MAX_ROWS=1000000
セット内の 1 行 (0.00 秒)

mysql> テストから count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 100 |
+----------+
セット内の 1 行 (0.00 秒)

mysql> sbtest1 から count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 1000000 |
+----------+
セット内1列（0.14秒）

（２）２つの表の統計情報を確認すると、どちらも比較的正確である。

mysql> table_name='test' のテーブルから table_schema、table_name、table_rows を選択します。
+--------------+------------+-------------+
| テーブルスキーマ | テーブル名 | テーブル行 |
+--------------+------------+-------------+
| テスト | テスト | 100 |
+--------------+------------+-------------+
セット内の 1 行 (0.00 秒)

mysql> table_name='sbtest1' のテーブルから table_schema、table_name、table_rows を選択します。
+--------------+------------+-------------+
| テーブルスキーマ | テーブル名 | テーブル行 |
+--------------+------------+-------------+
| テスト | sbtest1 | 947263 |
+--------------+------------+-------------+
セット内の 1 行 (0.00 秒)

（3）テストテーブルに1000万件のレコードを挿入し続け、統計を再度確認します。デフォルトでは、データの変更が10％を超えると統計が更新されるため、統計はまだ比較的正確です。

mysql> テストから count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 10000100 |
+----------+
セット1列（1.50秒）

mysql> table_name='test' のテーブルから table_schema、table_name、table_rows を選択します。
+--------------+------------+-------------+
| テーブルスキーマ | テーブル名 | テーブル行 |
+--------------+------------+-------------+
| テスト | テスト | 9749036 |
+--------------+------------+-------------+
セット内の 1 行 (0.00 秒)

MySQL 8.0 シナリオ

（１）次に、8.0の状況を見てみましょう。同様に、2つのテーブルを作成し、同じレコードを挿入します。

mysql> バージョンを選択します();
+-----------+
| バージョン() |
+-----------+
| 8.0.20 |
+-----------+
セット内の 1 行 (0.00 秒)

mysql> show テーブル作成 test\G
************************** 1. 行 ****************************
    表: テスト
テーブルの作成: CREATE TABLE `test` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `k` int unsigned NOT NULL デフォルト '0',
 `c` char(120) NOT NULL デフォルト ''
 `pad` char(60) NOT NULL DEFAULT ''
 主キー (`id`)、
 キー `k_1` (`k`)
) エンジン=InnoDB AUTO_INCREMENT=101 デフォルト文字セット=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
セット内の 1 行 (0.00 秒)

mysql> show テーブル sbtest1\G を作成します
************************** 1. 行 ****************************
    テーブル: sbtest1
テーブルの作成: CREATE TABLE `sbtest1` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `k` int unsigned NOT NULL デフォルト '0',
 `c` char(120) NOT NULL デフォルト ''
 `pad` char(60) NOT NULL DEFAULT ''
 主キー (`id`)、
 キー `k_1` (`k`)
) エンジン=InnoDB AUTO_INCREMENT=1000001 デフォルト文字セット=utf8mb4 COLLATE=utf8mb4_0900_ai_ci MAX_ROWS=1000000
セット内の 1 行 (0.00 秒)

mysql> テストから count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 100 |
+----------+
セット内の 1 行 (0.00 秒)

mysql> sbtest1 から count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 1000000 |
+----------+
セット内の1行（0.02秒）

（２）２つの表の統計情報を確認すると、どちらも比較的正確である。

mysql> table_name='test' のテーブルから table_schema、table_name、table_rows を選択します。
+--------------+------------+-------------+
| テーブル スキーマ | テーブル名 | テーブル行 |
+--------------+------------+-------------+
| テスト | テスト | 100 |
+--------------+------------+-------------+
セット内の 1 行 (0.00 秒)

mysql> table_name='sbtest1' のテーブルから table_schema、table_name、table_rows を選択します。
+--------------+------------+-------------+
| テーブル スキーマ | テーブル名 | テーブル行 |
+--------------+------------+-------------+
| テスト | sbtest1 | 947468 |
+--------------+------------+-------------+
セット内の1行（0.01秒）

(3) 同様に、テストテーブルに1000万件のレコードを挿入し、統計を再度確認します。table_rowsには依然として100件のレコードが表示されており、これは大きな偏差です。

mysql> テストから count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 10000100 |
+----------+
セット内1列（0.33秒）

mysql> table_name='test' のテーブルから table_schema、table_name、table_rows を選択します。
+--------------+------------+-------------+
| テーブル スキーマ | テーブル名 | テーブル行 |
+--------------+------------+-------------+
| テスト | テスト | 100 |
+--------------+------------+-------------+
セット内の 1 行 (0.00 秒)

原因分析

では、不正確な統計の原因は何でしょうか?実際、MySQL 8.0 では、information_schema のクエリ効率を向上させるために、ビューテーブルと統計に統計情報をキャッシュします。キャッシュの有効期限は、パラメータ information_schema_stats_expiry によって決定され、デフォルトでは 86400 秒です。最新の統計情報を取得するには、次の 2 つの方法を使用できます。

（１）表を分析する

（２）information_schema_stats_expiry=0を設定する

探索を続ける

では、不正確な統計情報はどのような結果をもたらすのでしょうか?実行計画に影響しますか?次に、再度テストします

テスト 1: テーブル test のレコード数は 100 で、テーブル sbtest1 のレコード数は 100 万です。

以下のSQLを実行して実行プランを確認します。NLJを使用しています。駆動テーブルとして小さいテーブルtestを使用し(フルテーブルスキャン)、被駆動テーブルとして大きいテーブルsbtest1を使用します(主キー関連付け)。実行効率は非常に高速です。

mysql> テストから count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 100 |
+----------+
セット内の 1 行 (0.00 秒)

mysql> sbtest1 から count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 1000000 |
+----------+
セット内の1行（0.02秒）

mysql> table_name='test' のテーブルから table_schema、table_name、table_rows を選択します。
+--------------+------------+-------------+
| テーブル スキーマ | テーブル名 | テーブル行 |
+--------------+------------+-------------+
| テスト | テスト | 100 |
+--------------+------------+-------------+
セット内の 1 行 (0.00 秒)

mysql> table_name='sbtest1' のテーブルから table_schema、table_name、table_rows を選択します。
+--------------+------------+-------------+
| テーブル スキーマ | テーブル名 | テーブル行 |
+--------------+------------+-------------+
| テスト | sbtest1 | 947468 |
+--------------+------------+-------------+
セット内の1行（0.01秒）

mysql> t.id = t1.idのTest t -15161106334-50535565977 'および1106334-50535565977 ';
+----+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+
| id | k | c | パッド |
+----+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+
| 1 | 501885 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
+----+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+
セット内の 1 行 (0.00 秒)

mysql> explain select t.* from test t inner join sbtest1 t1 on t.id=t1.id where tc='08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977' and t1.c='08566691963-88624912351-16662227201-4664
+----+-------------+---------+-----------+----------+---------------+----------+-----------+-----------+------------+-------------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+---------+-----------+----------+---------------+----------+-----------+-----------+------------+-------------+-------------+
| 1 | SIMPLE | t | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100 | 10.00 | where の使用 |
| 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t.id | 1 | 10.00 | where の使用 |
+----+-------------+---------+-----------+----------+---------------+----------+-----------+-----------+------------+-------------+-------------+
セットに 2 行、警告 1 件 (0.00 秒)

テスト2: テーブル test には約 1,000 万件のレコードがあり、テーブル sbtest1 には 100 万件のレコードがあります。

再度SQLを実行し、実行プランを確認します。これもNLJに従っています。小さいテーブルsbtest1が駆動テーブルとして使用され、大きいテーブルtestが被駆動テーブルとして使用されます。これも正しい実行プランです。

mysql> テストから count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 10000100 |
+----------+
セット内1列（0.33秒）

mysql> sbtest1 から count(*) を選択します。
+----------+
| カウント(*) |
+----------+
| 1000000 |
+----------+
セット内の1行（0.02秒）

mysql> table_name='test' のテーブルから table_schema、table_name、table_rows を選択します。
+--------------+------------+-------------+
| テーブル スキーマ | テーブル名 | テーブル行 |
+--------------+------------+-------------+
| テスト | テスト | 100 |
+--------------+------------+-------------+
セット内の 1 行 (0.00 秒)

mysql> table_name='sbtest1' のテーブルから table_schema、table_name、table_rows を選択します。
+--------------+------------+-------------+
| テーブル スキーマ | テーブル名 | テーブル行 |
+--------------+------------+-------------+
| テスト | sbtest1 | 947468 |
+--------------+------------+-------------+
セット内の1行（0.01秒）

mysql> t.id = t1.idのTest t -15161106334-50535565977 'および1106334-50535565977 ';
+----+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+
| id | k | c | パッド |
+----+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+
| 1 | 501885 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
+----+--------+----------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+
セット内1列（0.37秒）

mysql> explain select t.* from test t inner join sbtest1 t1 on t.id=t1.id where tc='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 | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+---------+------------+----------+---------------+----------+-----------+-----------+------------+-------------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 947468 | 10.00 | where の使用 |
| 1 | SIMPLE | t | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.id | 1 | 10.00 | where の使用 |
+----+-------------+---------+------------+----------+---------------+----------+-----------+-----------+------------+-------------+-------------+
セットに 2 行、警告 1 件 (0.01 秒)

オプティマイザーが間違った実行プランを選択しなかったのはなぜですか?前回の記事で述べたように、MySQL 8.0 ではメタデータ情報が mysql ライブラリの下のデータディクショナリテーブルに保存されます。information_schema ライブラリは、ユーザーがクエリを実行するのに比較的便利なビューのみを提供します。そのため、オプティマイザが実行プランを選択するときに、データディクショナリテーブルから統計情報を取得し、正しい実行プランを生成します。

要約する

information_schema のクエリ効率を向上させるために、MySQL 8.0 では統計情報をビューテーブルと統計にキャッシュします。キャッシュの有効期限は、パラメータ information_schema_stats_expiry によって決定されます (パラメータ値を 0 に設定することをお勧めします)。これにより、ユーザーは対応するビューをクエリするときに最新かつ正確な統計情報を取得できなくなる可能性がありますが、実行プランの選択には影響しません。

上記は、MySQL 8.0 の統計が不正確である理由の詳細です。MySQL 8.0 の統計の不正確さの詳細については、123WORDPRESS.COM の他の関連記事に注目してください。

以下もご興味があるかもしれません: