MYSQLメモ

**InnoDB前提のメモ**

# ストレージエンジンの確認

`SHOW TABLE STATUS`でも良いけどカラム絞りが出来ないのでこんな感じ

```
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE from information_schema.TABLES WHERE TABLE_SCHEMA = 'データベース名';
```


# ログローテート

query.logが増え続けてしまうので、logrotateの設定を忘れ無いように設定する。

`/var/log/mysql/`以下の.logファイルをローテートする。

```:/etc/logrotate.d/mysql
/var/log/mysql/*.log {
    dateext
    ifempty
    missingok
    compress
    weekly
    rotate 12
    create 644 mysql mysql
}
```

| 設定値 |  |
| ---- | ---- |
| dateext | 拡張子の後に-yyyymmdd |
| ifempty | 対象ファイルが空でもローテーション（歯抜けにしない） |
| missingok | 対象ファイルがなくてもエラーを出さない |
| compress | gz圧縮する |
| weekly | ローテートタイミング(毎日:daily・毎週:weekly・毎月:monthly) |
| rotate 12 | 残す世代数（dailyで90の場合は直近90日まで） |
| create 644 mysql mysql | 新規ファイルのパーミッション設定 |

* 設定確認（dryrun エラーが出なければOK）

```
logrotate -dv /etc/logrotate.conf
```

* 強制実行（ファイルが作成されてればOK）

```
logrotate -fv /etc/logrotate.conf
```

# VARCHARの括弧

`VARCHAR(255)`はバイトの癖で255等を設定している？

255→1バイトで2の8乗まで
65535→2バイトで2の16乗まで

`mysql`は文字数なのでいいかげんでも良さそう？

`65535/CHARACTERのバイト数`までしかVARCHARの括弧には設定できない様子

```:utf8
Column length too big for column 'column' (max = 21845); use BLOB or TEXT instead
```

```:utf8mb4
Column length too big for column 'title' (max = 16383); use BLOB or TEXT instead
```

# `auto_increment`は重複エラー時にもカウントアップされる

https://stackoverflow.com/questions/23516958/on-duplicate-key-auto-increment-issue-mysql#answer-23517191

```sql
insert into `section` (`name`) values ('test1');
insert into `section` (`name`) values ('test1');
insert into `section` (`name`) values ('test2');
```

```
Query OK, 1 row affected (0.01 sec)
ERROR 1062 (23000): Duplicate entry 'test1' for key 'name_UNIQUE'
Query OK, 1 row affected (0.01 sec)
```

```
| id | name  |
|  1 | test1 |
|  3 | test2 |
```

# UPDATEのテクニック

現在値のチェック後にはそのチェック値をUPDATE文のWHEREに入れることで割り込みで他の値に書き換わってた場合は更新しないという事ができます。
（affected rowsを使用して0の場合はExceptionにする等で失敗したことにすることで対応）

トランザクションでFOR UPDATEを使わないで更新ができるのでロック不要なので便利です。

# SELECTした値で更新する

```sql
UPDATE
  `target_table` AS A,
  (SELECT 
    C.name, D.create_user_id
    FROM
      `user` AS C,
      (SELECT 
        `id` FROM `item` WHERE `create_user_id` IN (SELECT `id` FROM `user` WHERE `mail` IN ("test1@mail", "test2@mail"))
      ) AS D
      WHERE
        C.id = D.create_user_id
   ) AS B
SET
  A.user_name = B.name
WHERE 
  A.create_user_id = B.create_user_id;
```

# NULLをSELECTで対象にする

**NULLのレコードは != ではヒットしないのでIS (NOT) NULLを使用する。**

例)columnAが1でcolumnBが1ではない(columnBがNULLも対象)レコードを取得する場合は

```
WHERE columnA = 1 AND columnB != 1
```

ではなく

```
WHERE columnA = 1 AND (columnB != 1 OR columnB IS NULL)
```

columnBが1かNULLしか入らないのであれば

```
WHERE columnA = 1 AND columnB IS NULL
```

でも可

# AUTO_INCREMENTを更新する

* Auto_incrementの値を確認する

```sql
SHOW TABLE STATUS WHERE `name` = "table_name" \G
```

* Auto_incrementの値を更新する

```sql
ALTER TABLE `table_name` AUTO_INCREMENT = 10000000;
```

* 既存のIDを更新する

```sql
UPDATE `table_name` SET `id` = `id` + 10000000;
```

# ロック周辺

* トランザクション内ではinsert update deleteには排他ロックがかかる。
* トランザクション内でselect for updateを使うとselectに排他ロックがかかる。

    whereがインデックスではない値の場合はテーブルロック、whereがインデックスの値の場合は行ロック（存在しないレコードでもロックがかかる ギャプロック）

* DDLにはトランザクションは効かない。（トランザクションの途中でDDLを実行するとコミットされる。）
* **ユニーク制約**のあるテーブルでの**バルクインサート**は**デッドロックの可能性**があるので注意が必要。

バルクインサートは1つのクエリだが、順番にinsertをするので順番にロックがかかっていく。

ここで下記2つのクエリを同時に流すと

Aクエリ

```sql
INSERT INTO table_name (unique_field) VALUES ("unique1"), ("unique2"), ("unique3"), ("unique4"), ("unique5"), ("unique6");
```

Bクエリ

```sql
INSERT INTO table_name (unique_field) VALUES ("unique6"), ("unique5"), ("unique4"), ("unique3"), ("unique2"), ("unique1");
```

insertのすれ違いの時にデッドロックを起こす。
unique3とunique4ですれ違うとすると、Aクエリでunique3にロックがかかり、そのときBクエリでunique4にロックがかかる。
次のinsertの際にデッドロックとなる。
Aクエリでunique4はBクエリによる待ちが発生し、Bクエリではunique3はAクエリによる待ちが発生するため、AクエリBクエリ共に解決待になる。

# MYSQLを再起動した際にやっておいた方が良いこと

* 良くアクセスされるテーブルでレコードが沢山入ってるテーブルに対して公開前にキャッシュに載せること

公開前にSELECT COUNT(*)等を行うとそのテーブルのすべてがバッファプールに載るので以降同じテーブルを操作するときに早くなります。

# テーブル内容ダンプ作成

```
mysqldump --skip-lock-tables --single-transaction --flush-logs -e -c -t -u*** -p*** -h*** DB名 テーブル名 > テーブル名_`date +"%Y%m%d%H%M"`_dump.sql
```

# テーブル内容ダンプ戻し

```
mysql -u*** -p*** -h*** DB名 -Nse "TRUNCATE TABLE テーブル名;"
mysql -u*** -p*** -h*** DB名 -Ns < dump.sql
```

# 全DBダンプ作成

```
mysqldump --skip-lock-tables --single-transaction --flush-logs -u*** -p*** -h*** -A > all_`date +"%Y%m%d%H%M"`_dump.sql
```

# 全DBダンプ戻し

```
mysql -u*** -p*** -h*** -Ns < dump.sql
```

# MYSQL内でテキストからSQLを実行する方法
（※DB < filenameだとトランザクションが出来ないがこれだとできる）

```
mysql> begin;
mysql> source filename;
mysql> commit;
```

# SELECT結果をテキストに保存(-Nsはカラム名なしのタブ区切りで取得できます)

* 外から実行

```
mysql -u*** -p*** -h*** -D*** -Nse "SELECT * FROM `table_name` WHERE `id` = 1;" > select_reult.txt
```

* 中から実行

```
mysql -u*** -p*** -h*** -D*** -Ns > select_reult.sql
ここでSQL（プロンプト　mysql>は返って来ません）

quit
```

```:動的にSQLを作る例
mysql -u*** -p*** -h*** -D*** -Ns > test.sql
SELECT CONCAT('UPDATE `hoge` SET `name` = "', T.name, '" WHERE `id` = ', T.id, ';') FROM (SELECT * FROM `fuga`) AS T;
quit
```

```:test.sql
UPDATE `hoge` SET `name` = "fuga1" WHERE `id` = 1;
UPDATE `hoge` SET `name` = "fuga2" WHERE `id` = 2;
UPDATE `hoge` SET `name` = "fuga3" WHERE `id` = 3;
```

# カラム追加

```sql
ALTER TABLE user ADD `name` VARCHAR(255) NULL DEFAULT NULL AFTER `id`;
```

# ユニーク制約と論理削除

ユニーク制約のテーブルで論理削除した場合で、後から追加する新しいレコードと論理削除済みのレコードがユニーク制約の制限を受けるのを回避したい場合の対応方法。

ユニークキーをurlとignore_unique_by_null(int dafault 0)で作成
削除時にignore_unique_by_nullにユニークキーにnullを入れるとユニーク制約が無視されるので削除できる。

```sql
CREATE TABLE `test` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  `ignore_unique_by_null` TINYINT(1) DEFAULT 0,
  `create_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  `delete_datetime` DATETIME NULL DEFAULT NULL,
  `delete_flag` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC, `ignore_unique_by_null` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
```

```
>insert into `test` (`name`) values ("test");
Query OK, 1 row affected (0.00 sec)

>insert into `test` (`name`) values ("test");
ERROR 1062 (23000): Duplicate entry 'test-0' for key 'name_UNIQUE'

>insert into `test` (`name`) values (null);
Query OK, 1 row affected (0.00 sec)

>insert into `test` (`name`) values (null);
Query OK, 1 row affected (0.00 sec)

>update `test` set `ignore_unique_by_null` = null, `update_datetime` = NOW(), `delete_datetime` = NOW(), `delete_flag` = 1 where `name` = "test";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

>insert into `test` (`name`) values ("test");
Query OK, 1 row affected (0.00 sec)

>select * from test;
+----+------+-----------------------+---------------------+---------------------+---------------------+-------------+
| id | name | ignore_unique_by_null | create_datetime     | update_datetime     | delete_datetime     | delete_flag |
+----+------+-----------------------+---------------------+---------------------+---------------------+-------------+
|  1 | test |                  NULL | 2018-01-09 17:52:24 | 2018-01-09 17:57:06 | 2018-01-09 17:57:06 |           1 |
|  3 | NULL |                     0 | 2018-01-09 17:54:08 | 2018-01-09 17:54:08 | NULL                |           0 |
|  4 | NULL |                     0 | 2018-01-09 17:54:29 | 2018-01-09 17:54:29 | NULL                |           0 |
|  5 | test |                     0 | 2018-01-09 17:57:59 | 2018-01-09 17:57:59 | NULL                |           0 |
+----+------+-----------------------+---------------------+---------------------+---------------------+-------------+
4 rows in set (0.00 sec)
```

# InnoDBのインデックス構造

* クラスタ化インデックス

    * PKに対してレコードが紐づいている
    * PK指定→クラスタ化インデックスを見てレコードを特定→対象レコード
* セカンダリインデックス

    * インデックスに対して対象のインデックスカラムの値とPKのセットが紐づいている
    * インデックス指定→セカンダリインデックスを見てPKを特定→クラスタ化インデックスを見てレコードを特定→対象レコード

    select対象がインデックスカラムとPKの場合はクラスタ化インデックスを見る必要なく取得可能（カバリングインデックス）
    インデックス指定→セカンダリインデックスを見てPKとインデックスカラムの値が特定

    Extra : Using index condition（インデックス）
    Extra : Using index（カバリングインデックス）


| EXPLAINのtype ||
|:---|:---|
| ALL | フルスキャン（全件走査） |
| index | フルインデックススキャン（インデックス全体走査） |
| const | PKまたはユニークインデックス |
| eq_ref | JOINでconstの場合 |
| ref | ユニーク以外のインデックス |
| range | インデックス範囲検索 |

**typeがALLとindexは遅くなる可能性が高いので注意する**

[MySQL with InnoDB のインデックスの基礎知識](https://techlife.cookpad.com/entry/2017/04/18/092524)

# 複合インデックスのメモ

col1, col2, col3 の順で複合インデックスが作られているとする。

**複合インデックスの動作は昔試したので今は変わっているかもしれません**

```
WHERE col1 = 'A'	col1のインデックスのみを使用して検索（type ref/ref const）
WHERE col2 = 'B'	ALL検索（type ALL/ref NULL）
WHERE col3 = 'C'	ALL検索（type ALL/ref NULL）
```

```
WHERE col1 = 'A' and col2 = 'B'	col1のインデックスのみを使用して検索（type ref/ref const）
WHERE col2 = 'B' and col1 = 'A'	col1のインデックスのみを使用して検索（type ref/ref const）
WHERE col2 = 'B' and col3 = 'C'	ALL検索（type ALL/ref NULL）
```

```
WHERE col1 = 'A' and col2 = 'B' and col3 = 'C'	複合インデックスを使用して検索（type ref/ref const,const,const）
WHERE col2 = 'B' and col1 = 'A' and col3 = 'C'	複合インデックスを使用して検索（type ref/ref const,const,const）
```

* 複合インデックス作成時に使用した全てのカラムをwhere区に入れないと使えない（select順は関係なし）
* 複合インデックス作成時に最初に指定したカラムは通常のインデックスとして機能する（最初以外のものは機能しない）

# TEXT型にUNIQUE

```
BLOB/TEXT column 'text' used in key specification without a key length
```

255文字までしか許容できないそうなので、ユニークインデックス用のカラムを作る必要がある。
ユニークインデックス用のカラムはVARCHAR(255)等で作りTEXTのデータをハッシュ化したものを入れる。

# 絵文字を使う

## CHARSET

`utf8mb4`

## COLLATE 

普通は`utf8mb4_general_ci`（大文字小文字を区別しない）で良いがどうしても絵文字を検索する必要がある場合は`utf8mb4_bin`（完全一致）にする必要がある

`utf8mb4_general_ci`だと違う絵文字でも同じ絵文字に認識されるケースがある

大文字小文字を区別しないで絵文字を区別するのは無いのでトレードオフ

## my.conf

以下も設定しておく

```
[mysqld]
character-set-server=utf8mb4

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4
```

# 全文検索（日本語は5.7以上）

`like`だとインデックスが効かないのでこっちの方が早い。

MeCabパーサーのプラグインを入れれば`WITH PARSER mecab`も使うことができる

```
CREATE TABLE IF NOT EXISTS `test` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `search` MEDIUMTEXT NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT INDEX `search` (`search` ASC) WITH PARSER ngram)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;
```

```
INSERT INTO `test` (`search`) VALUES ('テ'), ('テス'), ('テスト'), ('テストト'), ('トステ'), ('テストテスト'), ('トテストテスト');
```

```
SELECT * FROM `test`;
+----+-----------------------+
| id | search                |
+----+-----------------------+
|  1 | テ                    |
|  2 | テス                  |
|  3 | テスト                |
|  4 | テストト              |
|  5 | トステ                |
|  6 | テストテスト          |
|  7 | トテストテスト        |
+----+-----------------------+
```

```
EXPLAIN SELECT * FROM `test` WHERE id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
```

```
mysql> EXPLAIN SELECT * FROM `test` WHERE search = 'テスト';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | search        | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
```

```
mysql> EXPLAIN SELECT * FROM `test` WHERE MATCH(search) AGAINST('テスト');
+----+-------------+-------+------------+----------+---------------+--------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type     | possible_keys | key    | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+-------+------------+----------+---------------+--------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | test  | NULL       | fulltext | search        | search | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+-------+------------+----------+---------------+--------+---------+-------+------+----------+-------------------------------+
```

```
mysql> SELECT * FROM `test` WHERE MATCH(search) AGAINST('テスト');
+----+-----------------------+
| id | search                |
+----+-----------------------+
|  6 | テストテスト          |
|  7 | トテストテスト        |
|  3 | テスト                |
|  4 | テストト              |
|  2 | テス                  |
+----+-----------------------+
```

ngramは効いている様子

```
mysql> SELECT * FROM `test` WHERE MATCH(search) AGAINST('テスト' in boolean mode);
+----+-----------------------+
| id | search                |
+----+-----------------------+
|  6 | テストテスト          |
|  7 | トテストテスト        |
|  3 | テスト                |
|  4 | テストト              |
+----+-----------------------+
```

# 接続周辺

[max_connections](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_connections)

初期値 151

[wait_timeout](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout)

初期値 28800seconds(8時間)

[innodb_lock_wait_timeout](https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout)

初期値 50seconds

[lock_wait_timeout](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_lock_wait_timeout)

初期値 1year

```
wait_timeout系は応答が無い状態が設定値の間続くと接続が切れる
wait_timeout系を小さくする際は
バッチ処理等で長時間アクセスしない接続を行う場合は注意が必要
（バッチでも頻繁にアクセスするものだと問題さそう）
```

# INSERT or UPDATE

## ON DUPLICATE KEY UPDATE

(DUPLICATE時はupdate)

```sql
INSERT INTO `table` (`name`, `description`, `value`) VALUES ('名前1', '概要1', 1), ('名前2', '概要2', 2), ('名前3', '概要3', 3) ON DUPLICATE KEY UPDATE `description` = VALUES(`description`), `value` = VALUES(`value`);
```

## REPLACE

(DUPLICATE時はdelete and insert)

```sql
REPLACE INTO `table` (`name`, `description`, `value`) VALUES ('名前1', '概要1', 1), ('名前2', '概要2', 2), ('名前3', '概要3', 3);
```