db4free.net で MySQL 8.0
こんにちは、みなさん MySQL チューニングしてますか！ nobuh です。
今回は MySQL 8.0 DMR（開発マイルストーン版）がリリースされことを記念しまして、早速 8.0 を触ってみようというお話です。
DMR版の MySQL を入れる方法はいろいろあります。VirtualBox に立てたサーバーに yum/apt で入れたり、Vagrant, Docker を使ったりなど、みなさんも日常的に行われていると思います。今回はちょっと趣向を変えまして db4free.net というテスト向けに最新の MySQL を無料でホスティングしているサービスがありますので、そちらを使ってみました。
まずは登録
db4free.net ですが、2つサービスがあります。
- db4free.net – GA版 MySQL 5.7 の最新をホスティング
- mysql8.db4free.net – 出たばかりの 8.0 DMR をホスティング
安定最新版の MySQL 5.7 もまだまだ学ぶべきものが一杯ありますが、今回は旬の 8.0 を使いたいので mysql8.db4free.net を使います。トップページに「無料でアカウント作成」のリンクがありますのでクリックします。
登録ページもシンプルです。
必要なものは
- MySQLデーターベース名
- MySQLユーザー名
- MySQLパスワード
- 登録Eメールアドレス
だけです。同意に ✔ をつけたら 「サインアップ」 ボタンを押せば完了！
MySQL のサービスなので MySQLのユーザー／パスワードがそのままこのサービスのアカウントになっています。直球で無駄が無いですね！
すぐ「db4free.netにデータベースが作成されました」メールが来ますので、そのメール文中のリンクをクリックして登録を有効にします。
プレーンなメールでリンクが多く意外に見つけるのが難しいので抜粋しました。目が 「～翻訳を手伝って～」 まで行くと行き過ぎです！
さっそく phpMyAdmin を使ってみる
トップページの左サイド下に phpMyAdmin へのログイン画面がありますので、そこからログインします。
ログインすると、そこはいつもの phpMyAdmin の世界が広がっていますｗ 操作可能なデーターベースは自分の作成したデータベースだけですが、状態／モニタ によるグラフ機能なども通常通り利用可能です。
MySQL 8.0 の世界へ
phpMyAdmin 使えただけで満足感一杯ですが、ここで止まらずに本題の MySQL 8.0 に挑戦しましょう。まずは mysql クライアントでアクセスします。とりあえず手元のマシンが MySQL 5.7 のクライアントが入っていましたのでそれを使います。
YOU ~]$ mysql --version mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
接続先は db4free.net の port 3307 になりますので mysql クライアントから直接アクセスしてみると… 8.0.0-dmr の文字が！
YOU ~]$ mysql -h db4free.net -P 3307 -uYOURNAME Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 45022 Server version: 8.0.0-dmr MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
早速何か MySQL 8 っぽい機能を使ってみましょう。
不可視インデックスを使ってみる
MySQL 8.0 からデータとしては保存されて存在しているものの、SELECT のための実行計画ではぜったいに選ばれないという不可視インデックスの機能が追加されました。インデックスを削除するときに、一度不可視インデックスの状態に置くことで、インデックスが無くなって SELECT 性能がどうなるか十分に確認することができます。そして最後にもうＯＫとなってから実際にインデックスを削除することが出来ます。
インデックス削除後にやっぱり作成し直すとなると、システムに与える負荷は大きくなりますが、不可視インデックスへの変更あるいはそれを元に戻すのもメタデータの変更のみで軽そうです。より安全にインデックスを削除出来るようになりますので、不可視インデックスは期待できそうな機能です。それではテストしてみましょう。
まずは Auto Inc のプライマリキーと文字カラム、追加インデックスは一つだけのテーブルを作成します。
mysql> CREATE TABLE t (p int auto_increment primary key, c char(80), key k (c));
1行目を空でインサート
mysql> insert into t () values ();
2行目以降は insert … select を使います。
mysql> insert into t (c) select c from t;
あとは↑キーで戻ってこのクエリを繰り返し叩けば、2 の n 乗のスピードで行数が増えます。とりあえず1024行用意しました。
mysql> select count(*) from t; +----------+ | count(*) | +----------+ | 1024 | +----------+
このままだと c のカラムは全部 NULL のままでデータらしくないので加工します。英数4文字をランダムに決定するには RAND と MD5 を使ってこのように書くことができます。
mysql> SELECT SUBSTRING(MD5(RAND()), 1, 4); +------------------------------+ | SUBSTRING(MD5(RAND()), 1, 4) | +------------------------------+ | 54d4 | +------------------------------+
この方法を使って全行をランダムに更新します。
mysql> update t set c = SUBSTRING(MD5(RAND()), 1, 4); Query OK, 1024 rows affected (0.90 sec) Rows matched: 1024 Changed: 1024 Warnings: 0
真ん中あたりの3行見てみます
mysql> select * from t limit 500,3; +------+------+ | p | c | +------+------+ | 1088 | 7f2b | | 1020 | 7f4f | | 70 | 7f74 | +------+------+
え、この値の並びは。。。。。 無条件 select でプライマリキーではなくインデックスが使われていることにやや驚きつつも、とりあえずサンプルとして c = ‘7f4f’ を選びました。そして念には念を入れ間違いなくインデックスが使われるように FORCE をつけて、検索するときの explain を見ると。。。
mysql> explain select * from t force index (k) where c = '7f4f'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | t | NULL | ref | k | k | 321 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.28 sec)
たしかにインデックスが使われて1行ピンポイントに SELECT しました。そして、この状態からインデックス k を不可視化します。
mysql> ALTER TABLE t ALTER INDEX k INVISIBLE; Query OK, 0 rows affected (0.40 sec) Records: 0 Duplicates: 0 Warnings: 0
この状態で再度同じクエリの explain を見ると。。。
mysql> explain select * from t force index (k) where c = '7f4f'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 1024 | 10.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
今はインデックスが無いカラム c を条件に select していますので、想定通りフルテーブルスキャンになりました。
不可視化されたインデックスを無理やり FORCE 指定していてもエラーにはならず、possible_keys に載って来ていなくて単に選ばれていないだけの挙動になることがわかります。
この状態で show index してみるとインデックスはちゃんと存在していて、ただし Visible カラムが NO になっていることがわかります。
mysql> mysql> show index from t; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+ | t | 0 | PRIMARY | 1 | p | A | NULL | NULL | NULL | | BTREE | | | YES | | t | 1 | k | 1 | c | A | NULL | NULL | NULL | YES | BTREE | | | NO | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
information_schema の統計情報テーブルには is_visible というカラムがありますのでリストアップしてみます
mysql> SELECT * FROM information_schema.statistics WHERE is_visible='NO'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: ###YOU###
TABLE_NAME: t
NON_UNIQUE: 1
INDEX_SCHEMA: ###YOU###
INDEX_NAME: k
SEQ_IN_INDEX: 1
COLUMN_NAME: c
COLLATION: A
CARDINALITY: NULL
SUB_PART: NULL
PACKED: NULL
NULLABLE: YES
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
IS_VISIBLE: NO
そして戻すときは VISIBLE です。
mysql> ALTER TABLE t ALTER INDEX k VISIBLE; Query OK, 0 rows affected (0.51 sec) Records: 0 Duplicates: 0 Warnings: 0
これだけでインデックスが復活です！
いかがでしたでしょうか。db4free.net を使うことで、MySQL 8.0 をお手軽に体験することが出来ました。皆様もこの機会にぜひ db4free.net や MySQL 8.0 を体験してみてください！
弊社では MySQL が大好きなエンジニアを募集していますので、ご興味ある方のご応募をお待ちしています！！ →採用情報
