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

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

1. 問題

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

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

bool Explain_join::explain_rows_and_filtered()
{
if (table->pos_in_table_list->schema_table)
return false;
二重検査行;
if (選択 && 選択->クイック)
examined_rows= rows2double(選択->クイック->レコード);
else if (タブ->タイプ == JT_INDEX_SCAN || タブ->タイプ == JT_ALL)
{
if (タブ->制限)
examined_rows= rows2double(タブ->制限);
else
{
table->pos_in_table_list->fetch_number_of_rows ();
selected_rows= rows2double(table->file->stats.records);
}
}
else
examined_rows= tab->position->records_read;
fmt- >エントリー()->col_rows.set(static_cast(examined_rows));
/* 「フィルターされた」フィールドを追加 */
if (describe(DESCRIBE_EXTENDED))
{
float f= 0.0;
if (examined_rows)
f= 100.0 * tab->position->records_read / inspired_rows;
fmt->entry()->col_filtered.set(f);
}
return false;
}

しかし、フルテーブルスキャンを構築した後、フィルタリングされた結果は依然として 100% であり、期待していた値は 0.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

なぜ上記のような状況が起こるのでしょうか？その後、MySQLが収集する統計情報を確認して理解しました。

参考: 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 不足の统计情報会議に関係しています、何後の結果ですか？

+----+------ -------+------+------+---------------------+------+---- ----------+------+----------+----------+------ -----------------------------------+
| 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';

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

MySQL花了0.34s

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

+---------- +
|カウント(*) |
+----------+
| 0 |
+----------+
セット内の1行(0.34秒)
PostgreSQL花了0.139s

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

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

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;

クエリプラン
------------------------------------- ------- ----------------------------------
集計 (コスト=23502.34. .23502.35 rows=1 width=0)
-> ハッシュ結合 (cost=31.50..23474.97 rows=10947 width=0)
Hash Cond: (tb2.c1 = tb1.c1)
-> TB2 での Seq スキャン (コスト=0.00..18334.00 rows=1000000 width=4)
-> ハッシュ (cost=19.00..19.00 rows=1000 width=4)
-> tb1 での Seq Scan (cost= 0.00..19.00 rows=1000 width=4 )
(6 rows)
Time: 0.690 ms
postgres=# select count(*) from tb1,tb2 where tb1.c1=tb2。 c1;
count
- ------
10068
(1 row)
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

)