2018-09-18

MySQL 8.0は何が優れていて、どこに注意すべきか。データベース専門家が新機能を徹底解説

MySQLの最新版「MySQL 8.0」正式版が2018年4月にリリースされました。数多くの機能や設定が追加・変更されているMySQL 8.0の「知っておきたい便利な機能」や「危険なハマりどころ」などを、My SQLの専門家に教えてもらいました。

2018年4月、世界中のエンジニアが待ちに待ったMySQL 8.0の正式版がリリースされました。本リリースに伴い、数多くの機能や設定が追加・変更されており、MySQLがより便利なものへと進化しています。

MySQL 8.0で積極的に利用すべき目玉機能や、知っておかなければ危険なハマりどころなど重要な変更点を、MySQLの保守サポートやコンサルティングなどを専門とする株式会社スマートスタイルの中野真也さんと成田優隆さんに解説してもらいました。

中野真也（なかの・しんや）（写真右）: 株式会社スマートスタイルデータベース＆クラウド事業部技術部部長。MySQLを中心にMariaDB、Percona Serverといった大規模なシステムを持つエンタープライズユーザのデータベース設計・構築・コンサルティングなどを手掛ける。

成田優隆（なりた・まさたか）（写真左）: Oracle DatabaseやMySQLを中心としたデータベースのサポート・運用業務を経て、現在はMySQLやMariaDBといったデータベースの設計・構築・コンサルティング業務を手掛けている。

CTEでSQLの可読性を向上
SET構文のデメリットを解消するSET PERSIST
MySQLクライアントからサーバーの起動・停止などが可能に
ウィンドウ関数もついに実装！おすすめはRANK関数
JSON関連の機能が充実
ROLE機能を使えば権限管理もラクに
リソースグループで接続スレッドの処理優先度を変更
デフォルトのユーザー認証プラグイン変更に注意
クエリーキャッシュを無効にしてからアップグレードを
log_bin変数のデフォルト値が変更

CTEでSQLの可読性を向上

――ではさっそく、MySQL 8.0で登場した便利な機能について教えてください。

成田：目玉機能として、まずはCTE（Common Table Expressions：共通テーブル式）が挙げられます。これは、あるSQLを関数のような形で名前（別名）を付けて定義しておき、他SQLからその名前を用いて呼び出せるというものです。

実際に使ったSQLのサンプルは「MySQL 8.0 の共通テーブル式（CTE）を使ってみよう」（スマートスタイル TECH BLOG）に掲載されています

これまで複雑な条件のSQLを書く場合、どうしても文のネストが深くなっていました。例えば、SELECT文のFROM句の部分にさらにSELECT文を付けて、その結果を用いてさらにSELECT文を書いて……という冗長なクエリを書いた経験のある方もいらっしゃると思います。そういったSQLは可読性が非常に低くなってしまいますよね。

それを解消するための手段がCTEです。この機能はOracle DatabaseやPostgreSQLなどには既にあった機能で、いよいよMySQLにも導入されました。

――CTEを使う上での注意点はありますか？

成田：CTEとして定義するSQLは、可能な限り一文一文の内容をシンプルに保ってください。理解しやすいSQLを書くためにCTEを使うのに、時間をかけて内容を読み解かなければいけないようでは元も子もなくなってしまいます。シンプルな内容のCTEをいくつか定義し、それらを組み合わせて使用するのがいいと思います。

SET構文のデメリットを解消するSET PERSIST

成田優隆さん

成田：他には、SET PERSISTという便利な構文が導入されました。MySQLではこれまで、コマンドラインから設定変更をする場合にSET構文が用いられてきました。しかし、SET構文で変更された設定はメモリ上にしか反映されないため、MySQLを再起動するとmy.cnfファイルが持つパラメータにより初期化されてしまいます。

ですが、SET PERSIST構文で設定した内容はメモリ上にも反映されますし、MySQL再起動後も永続的に残るんです。

――それは便利ですね！運用の負担がかなり軽減されそうです。

成田：また、類似した構文としてSET PERSIST_ONLYもあります。これは、コマンドを打った時点では設定が反映されず、MySQLを再起動したときに設定が読み込まれるというものです。用途に応じて使い分けてください。

中野：SET PERSISTやSET PERSIST_ONLYなどの構文は確かに便利なのですが、使う上で気を付けてほしいことがあります。これらの構文によって変更されたパラメータはmy.cnfファイルではなく、データディレクトリ内のmysqld-auto.cnfファイルに格納されるんです。どのような設定がされているかを確認する際は、my.cnfファイルとmysqld-auto.cnfファイルの両方を見るようにしてください。

MySQLクライアントからサーバーの起動・停止などが可能に

――他に、運用が楽になる機能はありますか？

成田：MySQLのプロセスを起動・停止する場合は、サーバーにログインしてsystemctlやserviceなどのコマンドを叩く方法が一般的でした。ですがMySQL 8.0からは、ユーザーに権限さえ付与されていれば、MySQLクライアントからプロセスを起動・停止させることも可能になったんです。例えば、再起動させたい場合には「RESTART」というコマンドを叩くだけです。

――運用の汎用性が格段に上がりますね。

中野：MySQLが大規模サービスで利用されるケースが増えるにつれ「サーバー台数が多いので、なるべく各サーバーにログインしなくて済むような仕組みがほしい」というニーズが多くのユーザーから挙がっていました。この変更は、そうした声に応えたものだと思います。

――この機能のハマりどころはありますか？

成田：あります。RESTARTコマンドは、再起動でしか変更できないパラメータのためSET PERSIST_ONLYコマンドと組み合わせて使用すると非常に便利なのですが、SET PERSIST_ONLYコマンドはパラメータの型が正しいか程度しかチェックしません。

そのため、キャッシュ領域の容量を設定するinnodb_buffer_pool_sizeパラメータに対して、実際はサーバーのメモリが10GBなのに、間違って200GBなどの値を設定してしまうと、RESTART後に予期せずOutOfMemoryエラーが発生したりします。また、他の文字列型の値に設定上存在しない文字列を指定すると、そもそもMySQLが起動しないといったトラブルも発生する可能性があります。このような組み合わせで使うような場合には注意が必要です。

――恐ろしい……。簡単に設定できるけれど、「適切な設定になっているかどうか」には気を配っておく必要があるんですね。

ウィンドウ関数もついに実装！おすすめはRANK関数

――PostgreSQLやOracle Databaseにあったウィンドウ関数（部分的に切り出した結果セットに集約関数を適用できる機能）も、いよいよMySQLに実装されたそうですね。

成田：はい。実装されたばかりなので他のデータベースと比べるとバリエーションは少ないですが、よく使われるような主要なウィンドウ関数はひと通り実装されています。

――ウィンドウ関数にもさまざまな機能があると思いますが、「特にこれは実務で役立つ」という機能はありますか？

成田：おそらく、多くの方に幅広く使われているのはRANK関数だと思います。これは、特定の結果セットに対して順位付けができる関数です。

例えば、テーブルの特定のカラムに0から100までの点数が格納されているとします。このテーブルに格納されているデータをなんらかの条件でグルーピングした上で「○○さんはグループ全体のうち、点数の順位が△△位」といった情報を集計できるんです。

また、RANK関数の親戚のような機能としてDENSE_RANK関数というものもあります。前者は同率1位が2つある場合に「1, 1, 3, 4, ……」という結果になるんですが、後者は「1, 1, 2, 3, ……」という結果になります。用途に応じて使い分けてください。

JSON関連の機能が充実

成田：JSON（JavaScript Object Notation）を扱うための機能がMySQL 5.7時代から登場していましたが、MySQL 8.0でさらなる改善が行われました。

――具体的に、どんな機能が登場したんですか？

成田：JSONを扱うSQLがより簡潔に書けるようになりました。例えばMySQL 5.7では、JSON型のカラムからデータを取得する場合、以下のような記法を用いていました。

SELECT 
    JSON_EXTRACT(doc, '$.firstname') AS firstname,
    JSON_EXTRACT(doc, '$.lastname') AS lastname,
    JSON_EXTRACT(doc, '$.score') AS score
FROM
    students
WHERE
    JSON_EXTRACT(doc, '$.score') > 80;

成田：JSON_EXTRACTは、使用されるケースが多いため、 "->" を使用したショートカット記法がありました。

SELECT 
    doc -> '$.firstname' AS firstname,
    doc -> '$.lastname' AS lastname,
    doc -> '$.score' AS score
FROM
    students
WHERE
    doc->'$.score' > 80;

成田：ただし、このままでは「文字列のダブルクォーテーションが結果に出力されてしまう」という不都合があります。そのため、回避策としてJSON_EXTRACTした値をさらにJSON_UNQUOTEに通す方法がこれまで用いられてきました。ただ、あまりにも変換処理をかけるケースが多いため、今回からダブルクォーテーションを外すための "->>" というショートカット記法が新しく導入されたんです。

SELECT 
    doc ->> '$.firstname' AS firstname,
    doc ->> '$.lastname' AS lastname,
    doc -> '$.score' AS score
FROM
    students
WHERE
    doc->'$.score' > 80;

成田：さらに、JSONを扱うための新機能として、JSON_TABLEという関数が追加されました。これはJSON文字列を擬似的なテーブルとして扱えるようになるものです。

SELECT 
  t.firstname,
  t.lastname,
  t.score,
  t.job,
  t.address
FROM students, 
JSON_TABLE( doc , "$" COLUMNS ( 
  firstname   varchar(100)   PATH   "$.firstname",
  lastname    varchar(100)   PATH   "$.lastname",
  score       int            PATH   "$.score",
  job         varchar(100)   PATH   "$.job",
  address     varchar(100)   PATH   "$.address"
)) as t

中野：何が便利かというと、JSON型の列と他のテーブルとをJOINできるようになるんですよ。リレーショナルデータベースのテーブルとJSONそれぞれのデータ構造の違いを意識せずに開発・運用が可能になりました。

中野真也さん

ROLE機能を使えば権限管理もラクに

――権限の管理もかなり便利になったとか。

成田：いくつかの権限をセットにしたROLEという機能が追加されました。例えば「管理者以外のユーザーにはSELECT、INSERT、UPDATEを許可するけれどDELETEを許可しない」という場合、これまでは各ユーザーに対して権限を操作するオペレーションを行う必要がありました。けれど、ROLE機能ができたことで、「SELECT、INSERT、UPDATEができるROLE」を事前に作っておき、そのROLEを付与するだけでよくなったんです。

ROLE機能の詳細については「MySQL 8.0から追加されたロール機能について」（スマートスタイル TECH BLOG）に掲載されています

――ユーザーの種類が増えるほど権限管理は面倒になりますから、その負担を軽減してくれる機能は嬉しいですね。

リソースグループで接続スレッドの処理優先度を変更

成田：さらに、リソースグループという機能が追加されました。これは、MySQLのスレッドを特定のCPUに割り当てられる機能です。現代のサーバーは、CPUのコアが複数あるものが主流になっています。それらのコアを、「何番目のコアはこのスレッドの処理に使う」という形で割り当てることで、各スレッドの処理優先度を変更できるんです。

用途としては、例えばバッチ処理などに使用することが考えられます。一般的にバッチプログラムは大量のデータを扱うことが多く、処理すべきデータの量が多いとデータベースへの負荷が大きくなり同じデータベースに接続している他のサービスに影響が出てしまうこともあります。ですが、リソースグループの機能を使うことで優先度を下げれば、他サービスへの影響を軽減できるんです。

構文
CREATE RESOURCE GROUP group_name
    TYPE = {SYSTEM|USER}
    [VCPU [=] vcpu_spec [, vcpu_spec] ...]
    [THREAD_PRIORITY [=] N]
    [ENABLE|DISABLE]

作成
CREATE RESOURCE GROUP HIGH_PRIO_RG
TYPE = USER
VCPU = 0
THREAD_PRIORITY = 0;

適用
SET RESOURCE GROUP HIGH_PRIO_RG;

デフォルトのユーザー認証プラグイン変更に注意

――他に「ここはハマりそうだな」と思う変更点はありますか？

成田：一番ハマるだろうと思うのは、デフォルトのユーザー認証プラグインが変更になったことです。これまではmysql_native_passwordがデフォルトだったんですが、MySQL 8.0からはcaching_sha2_passwordがデフォルトになりました。

これは、一度認証したユーザー情報をメモリ上にキャッシュしておくことで認証処理を高速化できる便利なプラグインです。しかし、クライアント側がこの形式をサポートしていなければ、ログイン時に接続できなくなってしまいます。

――これは結構ハマりそうですね。

成田：もちろん対応しているクライアントを使えばこの事象は回避できますが、それが難しいようであればデフォルトの認証プラグインをmysql_native_passwordに戻すのも手段のひとつです。ですが、caching_sha2_passwordの方がパフォーマンスが良いので、なるべくこちらを使っていただくことをおすすめします。

デフォルトの認証プラグインを変更する場合の設定方法については「MySQL 8.0新機能 (caching_sha2_password 認証プラグイン)」（スマートスタイル TECH BLOG）に掲載されています

クエリーキャッシュを無効にしてからアップグレードを

成田：それから注意すべきこととして、前のバージョンまではクエリーキャッシュという機能があったんですがMySQL 8.0から廃止になりました。これは、実行したSQL文と結果のセットをメモリ上にキャッシュとして格納しておき、同じSQLが実行された場合にキャッシュを返すことで処理が高速化する仕組みです。

――便利な機能のように思えますが、なぜ廃止になったのでしょうか？

成田：この機能は広範囲のロックを取るため、複数セッションから同時にSQLが投げられた場合にロックの競合が起こり、それほど高速化しないという問題があったんです。そのため、MySQL 5.6からはデフォルトで無効化され、MySQL 5.7では非推奨になっていました。

ですが、セッション並列数がそれほど多くない環境の場合はパフォーマンスが良くなるため、利用していらっしゃる方もまだ一定の割合でいらっしゃいます。それが問題で。旧バージョンからMySQL 8.0にアップグレードした際にこの設定が入っていると、データベースが起動しなくなってしまうんです。

――恐ろしい話ですね……。

成田：はい。なので、アップグレードする場合にはクエリーキャッシュを使用していないかを事前に確認していただきたいです。