技術めも

MariaDB10.4でhistgramの収集がデフォルトでONになりました。

（もちろんANALYZE TABLEを実行したときですけど）

10.3では、

select @@use_stat_tables;
+-------------------+
| @@use_stat_tables |
+-------------------+
| NEVER             |
+-------------------+

でしたが、

10.4では、

select @@use_stat_tables;
+------------------------+
| @@use_stat_tables      |
+------------------------+
| PREFERABLY_FOR_QUERIES |
+------------------------+

となり、ANALYZE TABLE 時に、特定のクエリーを加えると、histgramの収集が行われます。

ANALYZE TABLE 時にhistgramを収集するための書き方としては、

analyze table ${tableName} persistent for all;

のようになります。詳しくは公式を参考ください。

https://mariadb.com/kb/en/library/engine-independent-table-statistics/#examples-of-statistics-collection

で、実際にやってみました。

---

まずは、以下のようなテーブルに大量にデータを突っ込んでおきます。

MariaDB [test]> create table user (
   id         integer not null auto_increment,
   name       varchar(64),
   money_free integer,
   money_paid integer,
   primary key (id)
 );

MariaDB [test]>  select count() from user;
+----------+
| count() |
+----------+
| 16777214 |
+----------+

最小値と最大値をとっておきます。

MariaDB [test]> select min(money_free), max(money_free)  from user;
+-----------------+-----------------+
| min(money_free) | max(money_free) |
+-----------------+-----------------+
|               1 |             100 |
+-----------------+-----------------+

histgramを収集していない状態で、money_freeを条件として、フィルターされる行数を見てみます。

MariaDB [test]> explain extended select * from user where money_free between 30 and 31;
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
|    1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 16305730 |   100.00 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+

わかりにくいですが、filteredの値が100となっており、全くフィルターされない状態となっています。

次に、ANALYZE TABLE を実行してみます。

MariaDB [test]> analyze table user persistent for all;
+-----------+---------+----------+-----------------------------------------+
| Table     | Op      | Msg_type | Msg_text                                |
+-----------+---------+----------+-----------------------------------------+
| test.user | analyze | status   | Engine-independent statistics collected |
| test.user | analyze | status   | OK                                      |
+-----------+---------+----------+-----------------------------------------+
2 rows in set (36.498 sec)

結構時間かかりましたね。フルスキャンなので、公式にも記載があるように、slaveのみで実施するなどにして、サービスに影響が無いよう配慮する必要があります。

なお、バイナリログに記載されるようなので、masterで実施すると、slaveにも伝播してしまいますので、お気をつけ下さい。（flush localで回避できる模様）

そして、再度同じクエリーで、explainを実行してみます。

MariaDB [test]> explain extended select * from user where money_free between 30 and 31;
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+
|    1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 16777214 |     1.56 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+----------+----------+-------------+

おおー、filteredが激減しましたね。

なお、ヒストグラムの情報は以下のテーブルに書かれるようです。

• mysql.table_stats
• mysql.column_stats
• mysql.index_stats

MySQL8のように、ヒストグラムを削除するコマンドはないようなので、直接DELETEしちまってもいいような。。

削除したら、flush tablesを打っておくとよいのかな。。。（削除に限らず作成／更新したら？）

このようにヒストグラムを更新することで、INDEXがないカラムに対しても、JOIN時に評価対象行が減るので、非常にパフォーマンスがアップすることが期待されます。

もちろん、join_bufferを増やしたり、optimizer_switchを調整したりといったことも合わせて必要ですが、OLAP向けにもInnoDBでもこれまで以上に頑張れそうですね。

以上