MySQL5.7勉強会資料

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

---

#使ってみたい新機能
ざっくりまとめるとこんなものがあります

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

---

#マルチソースレプリケーション
- Master : Slave = N : 1の構成が可能。設定も簡単
    - Masterが複数台って不思議 :rolling_eyes:

```bash:masterのmy.cnf
log-bin=mysql-bin
server-id=1001 # MasterとするサーバのIDは重複不可
```

```sql:masterにレプリ用ユーザを用意
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'XXXXXX';
```

---

#マルチソースレプリケーション
- 後はSlaveの設定

```bash:Slaveのmy.cnf
server-id=1003
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = ON
```

```sql:SlaveとMaster接続
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` を使ってる場合、そのテーブルの画面が開かなくなったりします・・・ :sweat_smile: 

---

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

### 詳しい使用例
#### <生成カラムの作成>

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

```mysql:生成カラムの例
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:期限切れエラー
mysql> SHOW DATABASES;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
```

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

```mysql:期限をセット
mysql> ALTER USER myuser@localhost PASSWORD EXPIRE INTERVAL 200 DAY;
Query OK, 0 rows affected (0.00 sec)
```

---

#GROUP BYで起きるエラー
- 詳細は[ここ](http://mazeltov7.hateblo.jp/entry/2016/01/16/003232)
    - `sql_mode = ONLY_FULL_GROUP_BY`がデフォルトとなるために発生
    - GROUP BY句には、一意になるcolumnをきちんと明示的に書く
- うちのDBは`sql_mode = NO_ENGINE_SUBSTITUTION` だったのでセーフ :yum:

---

#個人的に気になった機能
- バッファプールのオンラインリサイズ(サイズ変更)
    - `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からアップデートして利用したが、全然問題なくて、驚き :astonished:
- MySQLで良くある「 `1213 Deadlock found when ~` 」も減少した :thinking:
    - 登録、更新の爆発時期でないからかもだが・・・ :fire:
- 運用中のサービスのDBリプレイスを体験 :relaxed:
    - DNSフェイルオーバーやら、学ぶ機会になった :notebook: 

---

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