UCO-Tech（MySQL 8.0 RCヒストグラム統計）

コメントする
このエントリーをはてなブックマークに追加

MySQL 8.0 RCの、ヒストグラムを使用したオプティマイザについて、少し調べてみました。

マニュアルはこちら。
https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html

概要

INFORMATION_SCHEMA.COLUMN_STATISTICSというテーブルが新規に導入され、
ここにヒストグラムの情報が格納されます。
ただ、何もしないとここに情報が格納されることはなく、
ANALYZE TABLEをUPDATE HISTOGRAM句付きで実行しないといけないようです。
https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html

検証

例えば、こんな感じです。

mysql8.0.3> ANALYZE TABLE world.city UPDATE HISTOGRAM ON District WITH 3 BUCKETS;
+------------+-----------+----------+-----------------------------------------------------+
| Table      | Op        | Msg_type | Msg_text                                            |
+------------+-----------+----------+-----------------------------------------------------+
| world.city | histogram | status   | Histogram statistics created for column 'District'. |
+------------+-----------+----------+-----------------------------------------------------+
1 row in set (0.01 sec)

mysql8.0.3> SELECT * FROM information_schema.column_statistics;
+-------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | HISTOGRAM                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+-------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| world       | city       | District    | {"buckets": [["base64:type254:", "base64:type254:SGVicm9u", 0.3326795783280216, 485], ["base64:type254:SGVpbG9uZ2ppYW5n", "base64:type254:UGVyYWs=", 0.6668301054179946, 458], ["base64:type254:UGVybQ==", "base64:type254:lg==", 1, 424]], "data-type": "string", "charset-id": 8, "null-values": 0, "last-updated": "2017-09-25 13:56:52.000000", "sampling-rate": 1, "histogram-type": "equi-height", "number-of-buckets-specified": 3} |
+-------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

見にくいので、HISTOGRAM列を整形するとこんな感じです。

{
	"buckets": [
		[
			"base64:type254:", "base64:type254:SGVicm9u", 0.3326795783280216, 485
		],
		[
			"base64:type254:SGVpbG9uZ2ppYW5n", "base64:type254:UGVyYWs=", 0.6668301054179946, 458
		],
		[
			"base64:type254:UGVybQ==", "base64:type254:lg==", 1, 424
		]
	],
	"data-type": "string",
	"charset-id": 8,
	"null-values": 0,
	"last-updated": "2017-09-25 13:56:52.000000",
	"sampling-rate": 1,
	"histogram-type": "equi-height",
	"number-of-buckets-specified": 3
}

この例では、値を3つのグループ（Bucket）に分割しており、
“”から”SGVicm9u”まで、”SGVpbG9uZ2ppYW5n”から”UGVyYWs=”まで、”UGVybQ==”から”lg==”までで分割しています。
そしてそれぞれ、485、458、424種の値が含まれており、33.3%まで、66.7%まで、100%までの値が含まれていることを示しています。

また、histogram-typeにはequi-heightが指定されています。
ヒストグラムのタイプには「singleton」と「equi-height」の2種類があり、
singletonは、WITH N BUCKETSで指定したBucketの数以下しか値の種類がなかった場合に採用されるタイプ、
equi-heightはそれ以外です。

singletonの場合、HISTOGRAM列の表示が少し変わります。

このようなテーブルがあるとします。

mysql8.0.3> SELECT * FROM UCO.test;
+------+
| a    |
+------+
|    1 |
|    1 |
|    1 |
|    1 |
|    2 |
|    2 |
|    3 |
|    4 |
|    4 |
|    4 |
+------+
10 rows in set (0.00 sec)

ここでヒストグラムを取得すると、このような結果となります。

mysql8.0.3> ANALYZE TABLE UCO.test UPDATE HISTOGRAM ON a WITH 4 BUCKETS;
+----------+-----------+----------+----------------------------------------------+
| Table    | Op        | Msg_type | Msg_text                                     |
+----------+-----------+----------+----------------------------------------------+
| UCO.test | histogram | status   | Histogram statistics created for column 'a'. |
+----------+-----------+----------+----------------------------------------------+
1 row in set (0.00 sec)

mysql8.0.3> SELECT * FROM information_schema.column_statistics;
+-------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | HISTOGRAM                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+-------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| world       | city       | District    | {"buckets": [["base64:type254:", "base64:type254:SGVicm9u", 0.3326795783280216, 485], ["base64:type254:SGVpbG9uZ2ppYW5n", "base64:type254:UGVyYWs=", 0.6668301054179946, 458], ["base64:type254:UGVybQ==", "base64:type254:lg==", 1, 424]], "data-type": "string", "charset-id": 8, "null-values": 0, "last-updated": "2017-09-25 13:56:52.000000", "sampling-rate": 1, "histogram-type": "equi-height", "number-of-buckets-specified": 3} |
| UCO         | test       | a           | {"buckets": [[1, 0.4], [2, 0.6000000000000001], [3, 0.7000000000000001], [4, 1]], "data-type": "int", "charset-id": 8, "null-values": 0, "last-updated": "2017-09-25 14:38:43.000000", "sampling-rate": 1, "histogram-type": "singleton", "number-of-buckets-specified": 4}                                                                                                                                                                |
+-------------+------------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

整形すると、こうなります。

{
	"buckets": 
	[
		[1, 0.4],
		[2, 0.6000000000000001],
		[3, 0.7000000000000001],
		[4, 1]
	],
	"data-type": "int",
	"charset-id": 8,
	"null-values": 0,
	"last-updated": "2017-09-25 14:38:43.000000",
	"sampling-rate": 1,
	"histogram-type": "singleton",
	"number-of-buckets-specified": 4
}

equi-heightでは、〇〇～△△が□%含まれていて値は☆個ある、という表示でしたが、
singletonでは、〇〇が□%、という表示になっています。
要は、singletonはカージナリティーが低い時に使用される形式、ということのようです。

これによって実行計画に影響が出るかどうか、確認してみます。
と言っても、実際に実行計画が変わるクエリを作るのは大変そうだったので、
EXPLAINのfilteredの値が変わることを先ほどのUCO.testテーブルで確認します。

まずはヒストグラムなしの時。

mysql8.0.3> EXPLAIN SELECT a FROM UCO.test WHERE a=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

1は4個あるので、filteredは40.00が正しいのですが、10.00、すなわち10%にまで絞り込めると判断しています。
ここでヒストグラムを取得。

mysql8.0.3> ANALYZE TABLE UCO.test UPDATE HISTOGRAM ON a WITH 4 BUCKETS;
+----------+-----------+----------+----------------------------------------------+
| Table    | Op        | Msg_type | Msg_text                                     |
+----------+-----------+----------+----------------------------------------------+
| UCO.test | histogram | status   | Histogram statistics created for column 'a'. |
+----------+-----------+----------+----------------------------------------------+
1 row in set (0.01 sec)

mysql8.0.3> EXPLAIN SELECT a FROM UCO.test WHERE a=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    40.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql8.0.3>

正しく40%に絞り込める、と判断できています。

なお、ヒストグラムは、インデックスをつけると使用されないようです。

mysql8.0.3> CREATE INDEX a_idx on UCO.test(a);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql8.0.3> EXPLAIN SELECT a FROM UCO.test WHERE a=1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | a_idx         | a_idx | 5       | const |    4 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql8.0.3>

まとめ

1. ヒストグラム統計は、2通り（singleton/equi-height）の方法で値をグループ化する。
2. ヒストグラム統計を使用するには、ANALYZE TABLEにUPDATE HISTOGRAMをつけて実行しておく必要がある。
　　（・・・ということは、定期的に明示的な実行をしないと統計はどんどん古くなる？）
3. インデックスがあるカラムには使用されない。

という感じです。
使いどころが難しそうですが、選択肢が増えるのは良いですね。

では。

このエントリーをはてなブックマークに追加

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です