MySQL5.7の頃より、GA後であっても機能追加が行われるようになってきました。
MySQL8.0になりリリース方式がContinuous Delivery Modelになったという話もあり、マイナーバージョンでも新機能が積極的に追加されているようです。
この記事では、2019/12/04時点の最新リリースである8.0.18で実装された新機能の一部についてご紹介します。
8.0.18新機能の完全なリストは以下をご確認ください。
Changes in MySQL 8.0.18 (2019-10-14, General Availability)
RANDOM_PASSWORD
CREATE USER/ALTER USER/SET PASSWORDを実行する際のパスワード指定にランダム文字列が使用できるようになりました。
|
1 2 3 4 5 6 |
mysql> create user test identified by RANDOM PASSWORD; +------+------+----------------------+ | user | host | generated password | +------+------+----------------------+ | test | % | ToeGzjFNn5kL6qMR{@8( | +------+------+----------------------+ |
ふとValidate Password Componentに対応しているのだろうかと疑問に思いましたが、
ドキュメントに以下の記載が確認できました。
If the validate_password component is installed, the policy that it implements has no effect on generated passwords. (The purpose of password validation is to help humans create better passwords.)
validate_password componentは人間が設定した際のアシスト機能であるため、自動生成されたパスワードに影響させない方針であるということです。
実際にパスワード長を64文字以上と設定を行った上でRANDOM PASSWORDを試した結果は以下となります
|
1 2 3 |
mysql> INSTALL COMPONENT 'file://component_validate_password'; mysql> set global validate_password.length = 64; mysql> exit; |
やはり、文字列長等に影響はありませんでした。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> create user test@localhost identified by RANDOM PASSWORD; +------+-----------+----------------------+ | user | host | generated password | +------+-----------+----------------------+ | test | localhost | {9iu{/HH)AWUJJvLPBkv | +------+-----------+----------------------+ 1 row in set (0.01 sec) mysql> create user test2 identified by '{9iu{/HH)AWUJJvLPBkv'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements |
RANDOM PASSWORDはValidate password componentの影響を受けませんが、ユーザは正常に作成されます。
|
1 2 |
$ mysql -utest -p'{9iu{/HH)AWUJJvLPBkv' -e 'exit' 2> /dev/null && echo Success Success |
パスワードポリシに従わないケースがあるという点にご注意ください。
なお、ランダムパスワード長についてはgenerated_random_password_lengthで指定する事ができます。
HASH JOIN & EXPLAIN ANALYZE
MySQLの結合プランにHASH JOINが追加されました。
Oracle DatabaseやPostgreSQLには以前から実装されていた機能であった事もあり、ユーザ待望の機能かと思います。
HASH JOINは結合が等価条件、かつインデックスが作成されていない列を使用する場合に選択されるプランです。
WL#2241で実装の詳細が確認できます。
早速ドキュメントの通り試してみます
|
1 2 3 4 5 |
mysql> CREATE TABLE t1 (c1 INT, c2 INT); mysql> CREATE TABLE t2 (c1 INT, c2 INT); mysql> INSERT INTO t1 VALUES (1,2); mysql> INSERT INTO t1 SELECT * FROM t1; -- ある程度の行数となるまで繰り返し mysql> INSERT INTO t2 SELECT * FROM t1; |
HASH JOINは FORMAT=TREE , もしくは EXPLAIN ANALYZE により使用されていることが確認できるという事がドキュメント上に書かれています。
FORMAT=TREEは8.0.16よりEXPLAINに追加されたフォーマットです。
To see whether hash joins are being used for a given join, you must use EXPLAIN with the FORMAT=TREE option. EXPLAIN ANALYZE also displays information about hash joins used.
FORMAT=TREEで実行した結果は以下です。
Inner hash join(t1.c1 = t2.c1)という内容が確認できます。
|
1 2 3 4 5 |
mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1\G EXPLAIN: -> Inner hash join (t1.c1 = t2.c1) (cost=5065534.22 rows=5065114) -> Table scan on t1 (cost=0.03 rows=12366) -> Hash -> Table scan on t2 (cost=412.35 rows=4096) |
従来型のフォーマットは以下です。
|
1 2 3 4 5 6 7 |
mysql> EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1; +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 65685 | 100.00 | NULL | | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 196602 | 10.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+ |
従来型のフォーマットでは、実際にHASH JOINが行なわれているにも関わらず、Block Nested Loop(BNL)として表示されてしまっているようです。
EXPLAIN ANALYZEは8.0.18から追加された方式であり、実際にSQLを実行してその際に得た情報から実行計画とコスト情報を表示するコマンドです。
EXPLAIN ANALYZEでは常にFORMAT=TREEで表示されます。
通常のEXPLAINに比較して、以下の情報が追加されています。
- actual time : 左は最初の行を返すまでの経過時間、右は全行を返すまでの経過時間です
- rows : iterator(Inner hash join等)が実際に返す行数
- loops : iteratorのループ回数
|
1 2 3 4 5 6 |
mysql> EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1\G EXPLAIN: -> Inner hash join (t1.c1 = t2.c1) (cost=314677.78 rows=314573) (actual time=2.842..810.420 rows=1048576 loops=1) -> Table scan on t1 (cost=0.03 rows=3072) (actual time=0.037..5.766 rows=3072 loops=1) -> Hash -> Table scan on t2 (cost=102.65 rows=1024) (actual time=0.128..1.683 rows=1024 loops=1) |
各テーブルに単純な数値のデータを格納し、BNLとの速度を比較します。
NO_HASH_JOINヒントを付与することでHASH JOINを回避しています。
JOINに関わるデータ量としては、前述のEXPLAINの結果より500万行程度です。
|
1 2 3 4 5 6 7 8 9 |
mysql> pager cat > /dev/null mysql> SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1; 16777216 rows in set (5.07 sec) mysql> SELECT /*+ NO_HASH_JOIN(t1,t2) */ * FROM t1 JOIN t2 ON t1.c1=t2.c1; 16777216 rows in set (9.66 sec) mysql> nopager |
HASH JOINの結合のほうが倍の速度が出ているという結果となりました。
インデックスが存在している場合もHASH JOINは選択されるでしょうか。
|
1 2 3 4 5 6 7 8 9 |
mysql> alter table t1 add key (c1); mysql> EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1\G EXPLAIN: -> Nested loop inner join (cost=1777871.55 rows=16883712) -> Filter: (t2.c1 is not null) (cost=412.35 rows=4096) -> Table scan on t2 (cost=412.35 rows=4096) -> Index lookup on t1 using c1 (c1=t2.c1) (cost=21.85 rows=4122) mysql [localhost:8018] {msandbox} (test) > SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1\G 1 row in set (1 min 17.87 sec) |
インデックスが存在している場合はHASH JOINは選択される事はありませんが、インデックスのほぼすべての行が結合の対象になるようなクエリではNested loopはBNLよりも低速という結果になりました。
なおHASH_JOINヒントを付与しても、HASH JOINは使用される事がありませんでした。
|
1 2 3 4 5 6 |
mysql> EXPLAIN FORMAT=TREE SELECT /*+ HASH_JOIN(t1,t2) */ * FROM t1 JOIN t2 ON t1.c1=t2.c1\G EXPLAIN: -> Nested loop inner join (cost=1777871.55 rows=16883712) -> Filter: (t2.c1 is not null) (cost=412.35 rows=4096) -> Table scan on t2 (cost=412.35 rows=4096) -> Index lookup on t1 using c1 (c1=t2.c1) (cost=21.85 rows=4122) |
等価条件かつインデックスが使用できない場合は、デフォルトでは常にHASH JOINが選択されるようになりましたので、該当するクエリをご使用の環境ではパフォーマンスに大きな違いがある点を理解しておく必要があります。
Group Replicationにおける終了アクションにOFFLINE_MODEが追加
MySQL 8.0.17までのGroup Replicationでは、障害によりでクラスタから除外されたノードは、group_replication_exit_state_actionで2つの動作を指定する事ができました。
| 値 | 説明 |
|---|---|
| READ_ONLY | 8.0.12からのデフォルトです。 除外されたノードはsuper_read_onlyになります。 すべてのユーザで読み取りのみが可能となります。レプリケーションは継続されます。 |
| ABORT_SERVER | 除外されたノードはSHUTDOWNされます。 |
READ_ONLYでは、DBノード間の接続に問題があるがアプリケーション(or MySQL Router)からの接続には問題がない場合に、レプリケーションが中断された状態が継続し古いデータが見えることになる危険性がありました。
ABORT_SERVERでは上記の問題は発生しませんが、一度停止されてしまうことから問題の解析が難しくなるという懸念がありました。
新しく導入されたOFFLINE_MODEは、問題のあるノードでoffline_modeシステム変数を有効化するという動作をします。
オーソドックスなシングルプライマリモードのGroup Replicationで試してみます。
|
1 2 3 4 5 6 7 8 |
mysql> select member_host, member_state, member_role from replication_group_members order by 1; +-------------+--------------+-------------+ | member_host | member_state | member_role | +-------------+--------------+-------------+ | mysql1 | ONLINE | PRIMARY | | mysql2 | ONLINE | SECONDARY | | mysql3 | ONLINE | SECONDARY | +-------------+--------------+-------------+ |
全てのノードでgroup_replication_exit_state_actionをOFFLINE_MODEに設定します。
|
1 |
mysql> set persist group_replication_exit_state_action = OFFLINE_MODE; |
当然ですが全てのサーバでoffline_mode変数は0となっています。
|
1 2 3 4 5 6 |
mysql> select @@offline_mode; +----------------+ | @@offline_mode | +----------------+ | 0 | +----------------+ |
mysql3のeth0を落とし、通信を遮断します。
|
1 |
$ sudo ifdown eth0 |
mysql1, mysql2から状態を見ると、mysql3は一度UNREACHEBLEとなり、最終的に除外されます。
|
1 2 3 4 5 6 7 8 |
mysql> select member_host, member_state, member_role from performance_schema.replication_group_members order by 1; +-------------+--------------+-------------+ | member_host | member_state | member_role | +-------------+--------------+-------------+ | mysql1 | ONLINE | PRIMARY | | mysql2 | ONLINE | SECONDARY | +-------------+--------------+-------------+ |
排除された直後はステータスの変更はありませんが、再びifupを行いクラスタとの通信が回復すると、その時点でmysql3は自身をgroup_replication_exit_state_actionに従い処理します。
|
1 2 3 4 5 6 7 |
$ sudo ifup eth0 $ mysql -t -e "select @@offline_mode" +----------------+ | @@offline_mode | +----------------+ | 1 | +----------------+ |
offline_modeになると、SUPER権限を持つユーザ、及びレプリケーションスレーブ接続以外の接続は切断されます。
SHUTDOWNせずにノードをサービスアウトさせる事ができ、また問題の解析も行う事ができるというモードであり、
現在のデフォルトではありませんが、今後推奨される動作となることでしょう。
まとめ
駆け足となりましたが、MySQL 8.0.18の新機能についてご紹介させていただきました。
8.0.18の目玉機能はHASH JOINだと個人的には感じました。
ユーザからは透過的に、今までBNLとなっていた処理のパフォーマンスを2倍高速化したインパクトは大きいでしょう。
気になる機能がありましたら、ぜひ実際にお試し頂ければ幸いです。
