D.Horiyama's output

MySQL 5.6 Developer試験対策 5 基本的な最適化 -- 最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する -- DMLステートメントの最適化

MySQLRDB勉強メモ

MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」

[https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882:embed:cite]

MySQL 5.6 リファレンスマニュアル

[https://dev.mysql.com/doc/refman/5.6/ja/:embed:cite]

公式: 最適化の概要

[https://dev.mysql.com/doc/refman/5.6/ja/optimize-overview.html:embed:cite]

なんだかどんどんDeveloper範囲から離れてAdministrator範囲に足を踏み入れている気がしてならない

最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する

INSERTステートメントの速度

[https://dev.mysql.com/doc/refman/5.6/ja/insert-speed.html:embed:cite]

  • 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を高速化するツリー状の特殊なキャッシュ
  • 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)

自動インクリメントロックのロックモードを「インターリーブ」モードにする

[https://dev.mysql.com/doc/refman/5.6/ja/innodb-auto-increment-configurable.html:embed:cite]

  • AUTO-INCロック

    • 自動インクリメント番号を予想可能にするための特殊なテーブルレベルロック
    • 1ステートメントでもAUTO-INCロックを取得していると、他のINSERT-likeステートメントは待たされる
  • このAUTO-INCロックの取得いかんを調整できる
  • innodbautoinclock_modeをデフォルト1から2に変更する
innodbautoinclock_mode \ 挿入の種類 一括挿入 単純挿入 混在モード挿入
0: 「従来」ロックモード 取得する 取得する 取得する
1: 「連続」ロックモード(デフォルト) 取得する しない しない
2: 「インターリーブ」ロックモード しない しない しない
  • 混在モード: 主キーを一部指定するタイプのバルクインサート
  • AUTO-INCロックを取得しない2が最高速

  • ただし、一部シナリオで安全でない

    • ステートメントベースのレプリケーション
    • バイナリログからSQLステートメント再現
  • 動的でないので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 を使用して、FTSDOCID カラムが存在しないテーブルに全文インデックスを追加するときにも、同じ警告が返されます。 CREATE TABLE の実行時に全文インデックスを作成する場合に、FTSDOCID カラムを定義しないと、 InnoDB によって警告なしで、非表示の FTSDOCID カラムが追加されます。

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テーブルの一括データロード高速化

[https://dev.mysql.com/doc/refman/5.6/ja/optimizing-myisam-bulk-data-loading.html:embed:cite]

INSERT DELAYED

  • 複数クライアントが大量の行を挿入する場合はINSERT DELAYEDで高速化

    • deprecated, 8.0で廃止

同時挿入

[https://dev.mysql.com/doc/refman/5.6/ja/concurrent-inserts.html:embed:cite]

  • SELECTステートメントの行読み込みと同時にINSERTを実行してテーブル末尾に行を追加できる

  • concurrent_insert

    • NEVER(0)
    • 同時挿入しない
    • AUTO(1)
    • テーブルのデータファイルの途中に隙間 = 削除された行のない場合のみ同時挿入する
    • ALWAYS(2)

    • 途中に隙間があっても末尾に同時挿入可能

      • 別のスレッドによって使用されている場合のみ
      • そうでなければ、普通に隙間に挿入する

インデックス更新を無効化して一括挿入

myisamchk --keys-used=0 -rq

または

ALTER TABLE tbl_name DISABLE KEYS

-- import stmts

ALTER TABLE tbl_name ENABLE KEYS

複数のINSERTステートメントを実行する際、テーブルロックして行う

  • インデックスバッファーのflushが1度で済むらしい

    • ロックしないとINSERTステートメントの数だけ発生する

keybuffersize 変数の値を増やす

[https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html#sysvarkeybuffer_size:embed:cite]

MyISAMキーキャッシュを拡張する

UPDATEステートメントの速度

  • SELECTクエリ + 書き込みの追加オーバーヘッド

  • 高速化のためには

    • SELECTクエリ同様の最適化
    • 更新を遅延して、なるべくまとめて更新する
    • 動的な行フォーマットを使用するMyISAMテーブルの場合、ときどき OPTIMIZE TABLEする
    • 分割された行が修復される
    • 【所感】デフラグみたい

DELETEステートメントの速度

  • 高速化のためには

    • キーキャッシュのサイズを大きくする
    • keybuffersize
    • そもそもDELETEを使わない

    • 全行消すならばTRUNCATE TABLE tbl_nameが高速

      • ただしtransaction-safeでない
      • 【補】TRUNCATEはDMLでなくDDLなので
    • 【補】パーティションを切ってDROP TABLEというのも聞いたことがある