Mysql Explain でフィルターされた列の値が常に 100% 拡張されるのはなぜですか_PHP チュートリアル

Mysql Explain Extended のフィルターされた列の値が常に 100% になる理由

1. 問題

Mysql Explain Extended を実行した出力には、単純な Explain よりも 1 つ多くのフィルターされた列が含まれます (MySQL5.7 はデフォルトでフィルターされた出力になります)。読み取る必要がある行のうち、結果を返す行の割合 (行列の値) を指します。結合操作の場合、前のテーブルの結果セットのサイズがループの数に直接影響するため、filtered は非常に便利な値であると言われています。しかし、私の環境でテストした結果、filtered の値は常に 100% となり、意味が失われています。

以下の MySQL 5.6 コードを参照してください。フィルターされた値は、インデックスとすべてのスキャンに対してのみ有効です (これは理解できます。他の状況では、行の値は通常、推定された結果セット サイズと等しくなります)。
sql/opt_explain.cc

1. bool Explain_join::explain_rows_and_filtered()
   
2. {
   
3. if (table->pos_in_table_list->schema_table)
   
4. return false;
   
5. 
   
6. 二重検査行;
   
7. if (選択 && 選択->クイック)
   
8. examined_rows= rows2double(選択->クイック->レコード);
   
9. else if (タブ->タイプ == JT_INDEX_SCAN || タブ->タイプ == JT_ALL)
   
10. {
    
11. if (タブ->制限)
    
12. examined_rows= rows2double(タブ->制限);
    
13. else
    
14. {
    
15. table->pos_in_table_list->fetch_number_of_rows ();
    
16. selected_rows= rows2double(table->file->stats.records);
    
17. }
    
18. }
    
19. else
    
20. examined_rows= tab->position->records_read;
    
21. 
    
22. fmt- >エントリー()->col_rows.set(static_cast(examined_rows));
    
23. 
    
24. /* 「フィルターされた」フィールドを追加 */
    
25. if (describe(DESCRIBE_EXTENDED))
    
26. {
    
27. float f= 0.0;
    
28. if (examined_rows)
    
29. f= 100.0 * tab->position->records_read / inspired_rows;
    
30. fmt->entry()->col_filtered.set(f);
    
31. }
    
32. return false;
    
33. }

しかし、フルテーブルスキャンを構築した後、フィルタリングされた結果は依然として 100% であり、期待していた値は 0.1% でした。

1. mysql> desc tb2;
   +-------+--------------+------+-----+---- --+------+
   | デフォルト |
   +------+---------- --+------+-----+------+-----+
   | 0 | |
   | int(11) |
   | varchar(100) | ----+------+-----+-----------+----------+
   セット内の 3 行 (0.00 秒)
   
   mysql> Explain拡張選択 * from tb2 where c1+----+-------------+-------+------+---- -----------+------+--------+------+--------+----- -----+----------+ | 選択可能なキーの行 |
   +-------------+----------+------+--------+---- --+----------+------+----------+----------+---------- ---+
   | tb2 | 996355 | を使用します。 -------+------+------+------+-----------+-- --------+----------+----------+---------------+
   セット内 1 行、警告 1 つ (10 分) 29.96 秒)
   
   mysql> select count(*) from tb2 where c1+----------+
   count(*) |+---------- -+
   | 1001 |
   +----------+
   セット内の 1 行 (1.99 秒)
   
   

gdb の追跡を通じて、コードによって取られた分岐が正しいことがわかりました。しかし、次の値に問題があります。

（gdb）p table-> file->statords

$ 18 =996355
1. 
   
（gdb）p tab-> position->records_read
2. 
   
$ 19 = 996355 Position->records_read は、返される推定行数である必要があります。正しい値は、テーブル全体のサイズ 996355 ではなく、約 1001 である必要があります。
3. 
   
2. 理由
4. なぜ上記のような状況が起こるのでしょうか？その後、MySQLが収集する統計情報を確認して理解しました。
MySQL は、他の主流データベースと同様に、より適切な実行計画を生成するために統計情報を自動的に収集する必要があります。収集された統計情報は、mysql.innodb_table_stats および mysql.innodb_index_stats に保存されます。

参考: http://dev.mysql.com/doc/refman/5.6/en/innodb-persistent-stats.html#innodb-persistent-stats-tables
しかし、重要なのは、これらを確認することです。 2 表から、MySQL が収集する統計情報はほとんどないことがわかります。

mysql> select * from mysql.innodb_table_stats where table_name='tb2';
+---------------+------------+- --------------------+--------+---------------------- --+--------------------------+
|データベース名 |テーブル名 |最後の更新 | n_行 |クラスター化インデックスのサイズ | sum_of_other_index_sizes |
+---------------+------------+----------------- ----+--------+-----------+--------------- -------------+
|テスト | TB2 | 2015-12-02 06:26:54 | 996355 | 3877 | 0 |
+---------------+-----------+----------------- ----+--------+-----------+--------------- -------------+
セット内の 1 行 (0.00 秒)

mysql> select * from mysql.innodb_index_stats where table_name='tb2';
+-------- -------+-----------+---------------+----- ----------+--------------+-----------+---------------+-- ---------------------------------+
|データベース名 |テーブル名 |インデックス名 |最後の更新 |統計名 |統計値 |サンプルサイズ | stat_description |
+------+------------+------------+---- ----------+--------------+-----------+------ --------+-----------------------------------+
|テスト | TB2 |プライマリ | 2015-12-02 06:26:54 | n_diff_pfx01 | 996355 | 20 | ID |
|テスト | TB2 |プライマリ | 2015-12-02 06:26:54 | n_leaf_pages | 3841 | NULL |インデックス内のリーフ ページの数 |
|テスト | TB2 |プライマリ | 2015-12-02 06:26:54 |サイズ | 3877 | NULL |インデックス内のページ数 |
+--------------+---------------+------------ +---------------------+--------------+--------------- +-------------+------------------------------------- +
セット内の 3 行 (0.00 秒)
重要な情報は 2 つあり、1 つはテーブルの総承認数 (n_rows)、2 つはインデックス内の列の唯一の数 (n_diff_pfx01) です。 MySQL はインデックス以外の値情報を処理できません。前述の例では、c1 にインデックスが付けられていないため、MySQL は「c1

3. 引申請

後面私は MySQL 不足の统计情報会議に関係しています、何後の結果ですか？

mysql> tb1,tb2 からの拡張選択カウント (*) について説明します。ここで、tb1.c1=tb2.c1 および tb2.c2='xx';
1. +----+------ -------+------+------+---------------------+------+---- ----------+------+----------+----------+------ -----------------------------------+
   | ID |選択タイプ |テーブル |タイプ |可能なキー |キー |キー長 |参照 |行 |フィルタリング済み |おまけ |
   +------+---------------+-------+------+------------ ---+------+--------+------+----------+----------+-- -------------------------------------------------- +
   | 1 |シンプル | tb1 |すべて | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL |
   | 1 |シンプル | TB2 |すべて | NULL | NULL | NULL | NULL | 996355 | 100.00 | where を使用する;結合バッファーの使用 (ブロックネストループ) |
   +----+-------------+----------+------+----- ----------+------+----------+------+----------+------ ----+------------------------------------------ -------+
   セット内 2 行、警告 1 件 (0.00 秒)
   

虽然t1表時間小表、tb2表は大表、ただしtb2上加上tb2.c2='xx'の条件制限後の結果のコレクションは 0 になるため、先に描画された tb2 テーブルの方がパフォーマンスが良い選択になります。

postgres=# Explain select count(*) from tb1,tb2 where tb1.c1=tb2.c1 and tb2.c2='xx';

1. クエリプラン
   
2. -------- -------------------------------------------------- --------
   
3. 集計 (コスト=20865.50..20865.51 行=1 幅=0)
   
4. -> ネストされたループ (コスト=0.00..20865.50 行=1 幅=0)
   
5. 結合フィルター: (tb1.c1 = tb2.c1)
   
6. -> tb2 のシーケンススキャン (コスト=0.00..20834.00 行=1 幅=4)
   
7. フィルター: ((c2)::text = 'xx '::text)
   
8. -> tb1 での Seq Scan (コスト = 0.00..19.00 行 = 1000 幅 = 4)
   
9. (6 行)
   
10. 以下の実行時間を見る。

MySQL花了0.34s


mysql> select count(*) from tb1,tb2 where tb1.c1=tb2.c1 and tb2.c2='xx';

1. +---------- +
   
2. |カウント(*) |
   
3. +----------+
   
4. | 0 |
   
5. +----------+
   
6. セット内の1行(0.34秒)
   
7. PostgreSQL花了0.139s

1. postgres=# select count(*) from tb1,tb2 where tb1.c1=tb2.c1 and tb2.c2='xx';
   
2. count
   
3. -----
   
4. 0
   
5. (1 row)
   
6. 
   
7. Time: 139.600 ms

上記の例のパフォーマンスの差は、実際には tb2.c2='xx' の条件を取り除くとそれほど大きくありません。とても大きくなってください。
Mysql には 1 分 8 秒かかりました

1. mysql> Explain select count(*) from tb1,tb2 where tb1.c1=tb2.c1;
   +----+---------- - -+----------+-----+------+-----+--------- + ----------+----------+------------------------------------- ------------------+
   | テーブルのタイプ | キー長 |
   -----------+------+------+---------------------+------ + --------+------+----------+---------------------- - -----------------------------+
   | 1 | ヌル | | 1000 | 1 | 1 | 1 | 996355 | 結合バッファーを使用します。 -- ----+------+------+---------------------+------+----- -- --+------+----------+---------------------- ----- ---------------------+
   セット内の 2 行 (0.00 秒)
   
   mysql> select count(*) from tb1,tb2 ここでtb1.c1=tb2 .c1;
   +----------+
   | カウント(*) |
   +----------+
   | 9949 |
   +--- ----- --+
   セット内の 1 行 (1 分 8.26 秒)
   
   
   

PostgreSQL は 0.163 秒しかかかりませんでした

postgres=# Explain select count(*) from tb1,tb2 where tb1.c1= tb2.c1;

1. 
   クエリプラン
2. 
   ------------------------------------- ------- ----------------------------------
3. 
   集計 (コスト=23502.34. .23502.35 rows=1 width=0)
4. 
   -> ハッシュ結合 (cost=31.50..23474.97 rows=10947 width=0)
5. 
   Hash Cond: (tb2.c1 = tb1.c1)
6. 
   -> TB2 での Seq スキャン (コスト=0.00..18334.00 rows=1000000 width=4)
7. 
   -> ハッシュ (cost=19.00..19.00 rows=1000 width=4)
8. 
   -> tb1 での Seq Scan (cost= 0.00..19.00 rows=1000 width=4 )
9. 
   (6 rows)
10. 
    
11. 
    Time: 0.690 ms
12. 
    postgres=# select count(*) from tb1,tb2 where tb1.c1=tb2。 c1;
13. 
    count
14. 
    - ------
15. 
    10068
16. 
    (1 row)
17. 
    
18. 
    Time: 163.868 ms

ただし、このパフォーマンスの違い何も関係ないその理由は、PG が Nest Loop Join、Merge Join、Hash Join をサポートしているのに対し、MySQL は Nest Loop Join のみをサポートしているため、インデックスがないと Nest Loop Join はカメのように遅くなります。

4. 概要
1. MySQL には統計情報がほとんどなく、テーブルの行数とインデックス列の一意の値の数しかないため、MySQL オプティマイザーはデータ サイズを正しく理解できないことがよくあります。実行計画のパフォーマンスが低下します。

2. MySQL の結合操作の効率はインデックスに大きく依存します (私が MySQL の SQL ステートメントのチューニングを手伝った過去 2 回はすべてインデックスでした)。 PG の結合にインデックスが必要ないわけではありませんが、MySQL にインデックスがないことほど大きな反響ではありません。上記の例では、MySQL の実行に 1 分以上かかりましたが、インデックスを追加した後、MySQL と PG の実行時間はすぐに 10 ミリ秒未満に短縮されました。したがって、開発者はテーブルを設計するときに可能なクエリ メソッドを評価し、構築する必要があるすべてのインデックスを (それ以下でもそれ以上でも) 構築する必要があります。

3. 対照的に、PG はすべての列の値の分布をカウントするだけでなく、一意の値に加えてヒストグラム、頻度の値、その他の情報も含めて、PG オプティマイザーが正しい決定を下せるようにサポートします。この理由から、PG コミュニティは、PG のオプティマイザは十分に賢く、Oracle のようなヒント機能を PG カーネルに追加する必要はないと考えていると推測されます (ヒントは人によって悪用される可能性があり、システムの保守が困難になるためです)。ただし、実際には、これを使用したい場合は、pg_hint_plan プラグインを自分でインストールできます)。

http://www.bkjia.com/PHPjc/1080260.html

www.bkjia.comtru​​ehttp://www.bkjia.com/PHPjc/1080260.html技術記事 Mysql Explain Extended のフィルターされた列の値が常に 100% になるのはなぜですか 1. 質問: Mysql Explain Extended を実行した出力には、単純な Explain よりもフィルターされた列が 1 つ多くなります (MySQL5.7 はデフォルトで fil を出力します...

)