MySQL 5.6 Developer試験対策 5 基本的な最適化 -- 最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する -- DMLステートメントの最適化
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
公式: 最適化の概要
なんだかどんどんDeveloper範囲から離れてAdministrator範囲に足を踏み入れている気がしてならない
最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する
INSERTステートメントの速度
- N行挿入にかかる時間の割合
- 接続: 3
- クエリ送信: 2
- クエリ解析: 2
- 行挿入: N
- インデックス挿入: N (O(NlogN))
- クローズ1
- 1接続でバルクインサートが理想
- 【補】オーバヘッド計算
- (8/(8+2N)
- N=1のとき8割がたが行挿入・インデックス挿入以外にかかっている
- バルクインサートのチューニング変数
- bulk-insert-buffer-size
INSERT ... SELECT
,INSERT ... VALUES
,LOAD DATA INFILE
を高速化するツリー状の特殊なキャッシュ
- bulk-insert-buffer-size
LOAD DATA INFILE
はINSERT
ステートメントの20倍くらい速い
InnoDBテーブルの一括データロード高速化
一時的に自動コミットを切る
SET autocommit=0; -- import stmts COMMIT; SET autocommit=1;
一時的に一意性チェックを切る
SET unique_checks=0; -- import stmts SET unique_checks=1;
- 必ずしも重複キーを無視するとは限らない
ALTER TABLE tbl ADD CONSTRAINT unq_col UNIQUE KEY (col); SET unique_checks=0; INSERT INTO tbl VALUES (1),(1),(1); ERROR 1062 (23000): Duplicate entry '1' for key 'unq_col'
一時的に外部キー制約チェックを切る
SET foreign_key_checks=0; -- import stmts SET foreign_key_checks=1;
- こちらは外部キー制約を無視して挿入できてしまうので注意する
CREATE TABLE tbl2(col INT, CONSTRAINT fk_col FOREIGN KEY(col) REFERENCES tbl(col));
- 外部キー制約有効
mysql> INSERT INTO tbl VALUES (1); INSERT INTO tbl VALUES (1); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO tbl2 VALUES (99); INSERT INTO tbl2 VALUES (99); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mysql`.`tbl2`, CONSTRAINT `fk_col` FOREIGN KEY (`col`) REFERENCES `tbl` (`col`)) mysql> INSERT INTO tbl2 VALUES (1); INSERT INTO tbl2 VALUES (1); Query OK, 1 row affected (0.00 sec)
- 無効化すると不正な行を挿入できてしまう
mysql> SET foreign_key_checks=0; SET foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO tbl2 VALUES (99); INSERT INTO tbl2 VALUES (99); Query OK, 1 row affected (0.00 sec) mysql> SET foreign_key_checks=1; SET foreign_key_checks=1; Query OK, 0 rows affected (0.00 sec)
- 【補】親のdropもできちゃう
mysql> DROP TABLE tbl; DROP TABLE tbl; Query OK, 0 rows affected (0.01 sec) mysql> DROP TABLE tbl2; DROP TABLE tbl2; Query OK, 0 rows affected (0.01 sec)
自動インクリメントロックのロックモードを「インターリーブ」モードにする
- AUTO-INCロック
- このAUTO-INCロックの取得いかんを調整できる
- innodb_autoinc_lock_modeをデフォルト1から2に変更する
innodb_autoinc_lock_mode \ 挿入の種類 | 一括挿入 | 単純挿入 | 混在モード挿入 |
---|---|---|---|
0: 「従来」ロックモード | 取得する | 取得する | 取得する |
1: 「連続」ロックモード(デフォルト) | 取得する | しない | しない |
2: 「インターリーブ」ロックモード | しない | しない | しない |
- 混在モード: 主キーを一部指定するタイプのバルクインサート
- AUTO-INCロックを取得しない2が最高速
- ただし、一部シナリオで安全でない
- 動的でないのでmy.cnfの変更が必要
FULLTEXTインデックスへのデータロード時の工夫
- FULLTEXTインデックス使用時、
FTS_DOC_ID
というカラムがないと警告が出る
CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, text mediumtext NOT NULL ) ENGINE=InnoDB; CREATE FULLTEXT INDEX idx ON t1(text);
Query OK, 0 rows affected, 1 warning (0.08 sec) Records: 0 Duplicates: 0 Warnings: 1
SHOW WARNINGS; +---------+------+--------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------+ | Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID | +---------+------+--------------------------------------------------+ 1 row in set (0.00 sec)
ALTER TABLE を使用して、FTS_DOC_ID カラムが存在しないテーブルに全文インデックスを追加するときにも、同じ警告が返されます。 CREATE TABLE の実行時に全文インデックスを作成する場合に、FTS_DOC_ID カラムを定義しないと、 InnoDB によって警告なしで、非表示の FTS_DOC_ID カラムが追加されます。
CREATE TABLE t2 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, text mediumtext NOT NULL, FULLTEXT idx(text) ) ENGINE=InnoDB; DESC t2;
- 特にDESCで出てきたりはしない
DESC t2; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | text | mediumtext | NO | MUL | NULL | | +-------+------------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
- FULLTEXTインデックスへのデータロード時の工夫
FTS_DOC_ID
ユニークカラムをInnoDBに作らせるのではなく自分で作る
CREATE TABLE t3 ( FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT, title varchar(255) NOT NULL DEFAULT '', text mediumtext NOT NULL, PRIMARY KEY (`FTS_DOC_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t3(FTS_DOC_ID); -- import stmts CREATE FULLTEXT INDEX idx ON t3(text);
DESC t3; +------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+----------------+ | FTS_DOC_ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | title | varchar(255) | NO | | | | | text | mediumtext | NO | MUL | NULL | | +------------+---------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
- 自分で作った場合は見える
MyISAMテーブルの一括データロード高速化
INSERT DELAYED
- 複数クライアントが大量の行を挿入する場合は
INSERT DELAYED
で高速化- deprecated, 8.0で廃止
同時挿入
- SELECTステートメントの行読み込みと同時にINSERTを実行してテーブル末尾に行を追加できる
- concurrent_insert
- NEVER(0)
- 同時挿入しない
- AUTO(1)
- テーブルのデータファイルの途中に隙間 = 削除された行のない場合のみ同時挿入する
- ALWAYS(2)
- 途中に隙間があっても末尾に同時挿入可能
- 別のスレッドによって使用されている場合のみ
- そうでなければ、普通に隙間に挿入する
- 途中に隙間があっても末尾に同時挿入可能
- NEVER(0)
インデックス更新を無効化して一括挿入
myisamchk --keys-used=0 -rq
または
ALTER TABLE tbl_name DISABLE KEYS -- import stmts ALTER TABLE tbl_name ENABLE KEYS
複数のINSERTステートメントを実行する際、テーブルロックして行う
- インデックスバッファーのflushが1度で済むらしい
- ロックしないとINSERTステートメントの数だけ発生する
key_buffer_size 変数の値を増やす
MyISAMキーキャッシュを拡張する
UPDATEステートメントの速度
- SELECTクエリ + 書き込みの追加オーバーヘッド
- 高速化のためには
- SELECTクエリ同様の最適化
- 更新を遅延して、なるべくまとめて更新する
- 動的な行フォーマットを使用するMyISAMテーブルの場合、ときどき OPTIMIZE TABLEする
- 分割された行が修復される
- 【所感】デフラグみたい
DELETEステートメントの速度
- 高速化のためには
- キーキャッシュのサイズを大きくする
- そもそも
DELETE
を使わない