はじめに

• この資料は、社内向け勉強会用に作られたものです。
• ２年ほど前にまとめてましたが、これから学ぶ人の参考にでもなればと思ったので、Qiitaへも投稿
• MySQL5.7の全ては説明しません。
  • 理由は、新機能や変更点が多々あり、全部は紹介できないので。。。
• 個人的にこれはいい！と思ったものを紹介します。

使ってみたい新機能

ざっくりまとめるとこんなものがあります

• マルチソースレプリケーション
• fulltext indexの改善
• JSONデータ型の誕生
• 「生成カラム」とかいうやつ(制約と連携すると便利)
• mysql'p'umpの登場(mysqldumpと似たやつ)

マルチソースレプリケーション

• Master : Slave = N : 1の構成が可能。設定も簡単
  • Masterが複数台って不思議

log-bin=mysql-bin
server-id=1001 # MasterとするサーバのIDは重複不可

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'XXXXXX';

マルチソースレプリケーション

• 後はSlaveの設定

server-id=1003
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = ON

CHANGE MASTER TO
  MASTER_HOST='mysql5.7-master1',
  MASTER_USER='repl',
  MASTER_PASSWORD='XXXXX',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000005',
  MASTER_LOG_POS=309,
  MASTER_CONNECT_RETRY=10
  for channel 'master1'; # Channelという項目が追加されてるので注意

fulltext indexの改善

• 5.6の段階では特に日本語環境だと正直使えなかった
  • パフォーマンスも微妙すぎて不採用でした
• NGRAMがデフォルトであり、MeCabはプラグインインストールで利用可能

JSONデータ型

• データ型としてJSON型を指定可能。
• ストレージエンジン上の扱いはバイナリのデータ型BLOBと同じ。
• 順序がないので、INSERT時とSELECT時に表示形式が変わることがある
• 古い phpmyadmin を使ってる場合、そのテーブルの画面が開かなくなったりします・・・

生成カラム

• JSONデータは、インデックス作成に不向きなため追加された機能
• あるカラム値を別のカラム値から自動的に計算するという機能
  • JSONでもそうでなくても汎用的に使える
• CREATE TABLE or ALTER TABLEコマンドで追加が可能。

詳しい使用例

<生成カラムの作成>

• CREATE TABLEコマンドでテーブル定義と同時に作成することも可能だが、あとからALTER TABLEコマンドで追加も可能。
• 見た目は通常のカラムっぽく見えるが、直接値を挿入したり更新することはできない。
• さらに、生成カラムの値を使って、別の生成カラムを作成することも可能。
• 注意点として、式が決定性でなければならず、下記のような非決定性の値を生成する関数は使えない。ほかにもサブクエリやユーザ変数等も利用不可。
  • UUID()
  • CONNECTION_ID()
  • NOW()
  • RAND()

mysql> CREATE TABLE gcdate (y INT, m INT, d INT, gdate DATE AS (CAST(CONCAT(y,'-',m,'-',d) AS DATE)) VIRTUAL NOT NULL);
mysql> INSERT INTO gcdate (y,m,d) VALUES (2016,2,29);
mysql> SELECT * FROM gcdate;
+---------------------------------+
| y    | m  | d  | gdate          |
+---------------------------------+
| 2016 | 2  | 29 | 2016-02-29     |
+---------------------------------+

• JSONデータの操作をして、生成カラムを創る場合は、JSON_UNQUOTE関数を利用しないとダブルクォートが入ってしまうため、注意。

mysql'p'umpの登場

• mysqldumpにはない特徴を持つbackup コマンド
• 主な特徴は下記。
  • 並列ダンプ
  • テーブル、ビュー、ストアドプログラムを個別にフィルタリング
  • インデックス作成をデータをリストア後に実行
  • 出力の圧縮
  • バックアップの進捗報告

はまりそうな変更点

これもまとめると下記かなと。他にあるんでしょうか？

• パスワード有効期限設定
  • かの有名な5.7.10までであった罠ですｗ
• GROUP BYで起きるエラー
  • ERROR 1055 (42000)

パスワード期限の設定

• 指定日数で自動的に期限切れにすることが可能
• 期限を過ぎたアカウントは、パスワードを変更するまで他の操作が不可

mysql> SHOW DATABASES;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

• ユーザアカウントに期限を設定するコマンドは下記。

mysql> ALTER USER myuser@localhost PASSWORD EXPIRE INTERVAL 200 DAY;
Query OK, 0 rows affected (0.00 sec)

GROUP BYで起きるエラー

• 詳細はここ
  • sql_mode = ONLY_FULL_GROUP_BYがデフォルトとなるために発生
  • GROUP BY句には、一意になるcolumnをきちんと明示的に書く
• うちのDBはsql_mode = NO_ENGINE_SUBSTITUTION だったのでセーフ

個人的に気になった機能

• バッファプールのオンラインリサイズ(サイズ変更)
  • SET GLOBAL innodb_buffer_pool_size = 100 * 1024 * 1024 * 1024;クエリ実行で変更可能
  • リサイズが即完了ではなく、バックグラウンドで実行
    • 状況確認：SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_resize_status';
  • 注意点として、innodb_buffer_pool_chunk_sizeも一緒に調整が必要

個人的に気になった機能

• 一般テーブルスペースの追加
  • 複数テーブルを１つのテーブルスペースで管理可能
  • パーティショニングにも対応
  • 特定テーブルを高速なストレージに配置する運用が可能

個人的に気になった機能

• ALTERによるコピーをしないVARCHARサイズの変更
  • before: 完全なデータコピーが必要
  • after: サイズ増加だけならデータコピー不要
  • オンライン変更時、アルゴリズムの選択可能
    • ALTER TABLE tbl_name ALGORITHM=INPLACE
    • ALTER TABLE tbl_name ALGORITHM=COPY

個人的に気になった機能

• ALTERによるコピーをしないインデックス名変更
  • before: インデックスの再作成が必要
  • after: RENAME INDEX構文があり、気軽に変更可

個人的に気になった機能

• サーバサイド・クエリ書き換えフレームワーク
  • 他のRDBMSからアプリの移行時に、固有の方言を含むクエリの置換用途で作成
  • 独自にクエリ書き換えプラグインの開発が可能になった。種類は下記の２つ
    • PREPARSE(構文解析前)　SQL文そのものにを書き換え
      • plugins/rewrite_exampleにサンプルがある
    • POSTPARSE(構文解析後)　抽象構文木(AST)に対する書き換え
      • plugins/rewriterにサンプルがある

あるシステムへ導入しての感想

• 5.5からアップデートして利用したが、全然問題なくて、驚き
• MySQLで良くある「 1213 Deadlock found when ~ 」も減少した
  • 登録、更新の爆発時期でないからかもだが・・・
• 運用中のサービスのDBリプレイスを体験
  • DNSフェイルオーバーやら、学ぶ機会になった

まとめ

• 5.7 で大きく機能改善してきた印象
  • マルチマスターとかはAWS Auroraにもありますね
• はまりそうな変更点は修正や発信がされてるので、もう少ないのでは？
• JSON型や生成カラムはうまく使うと便利そう
• 今回のことで、MySQLについて、深く学ぶ機会になった