LoginSignup
3
3

More than 3 years have passed since last update.

@horikeso

MYSQLメモ

Last updated at Posted at 2017-11-04

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は重複エラー時にもカウントアップされる

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した値で更新する

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の値を確認する
SHOW TABLE STATUS WHERE `name` = "table_name" \G
  • Auto_incrementの値を更新する
ALTER TABLE `table_name` AUTO_INCREMENT = 10000000;
  • 既存のIDを更新する
UPDATE `table_name` SET `id` = `id` + 10000000;

ロック周辺

  • トランザクション内ではinsert update deleteには排他ロックがかかる。

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

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

  • DDLにはトランザクションは効かない。（トランザクションの途中でDDLを実行するとコミットされる。）

  • ユニーク制約のあるテーブルでのバルクインサートはデッドロックの可能性があるので注意が必要。

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

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

Aクエリ

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

Bクエリ

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;

カラム追加

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を入れるとユニーク制約が無視されるので削除できる。

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 のインデックスの基礎知識

複合インデックスのメモ

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

初期値 151

wait_timeout

初期値 28800seconds(8時間)

innodb_lock_wait_timeout

初期値 50seconds

lock_wait_timeout

初期値 1year

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

INSERT or UPDATE

ON DUPLICATE KEY UPDATE

(DUPLICATE時はupdate)

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)

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
3