MySQL(パフォーマンスチューニング編)

0.概要

MySQL の稼働において、パフォーマンスチューニングに関係する事項を記録する。

1.パフォーマンスチューニングに関わる基本事項

ステータス変数

MySQL では稼働状況を確認するためにステータス変数が用意されている。
各種ステータス変数の変化を確認することで稼働状況を確認できる。
以下コマンドで確認できる。LIKE句では、「％」を０文字以上の文字列、「_」を１文字としてワイルドカード表現できる

確認方法

# グローバルレベル
SHOW GLOBAL STATUS LIKE "%XXXX%";

# セッションレベル
SHOW SESSION STATUS LIKE "%XXXX%";
SHOW STATUS LIKE "%XXXX%";

システム変数

MySQL の各システム変数はmy.cnfに設定されている。システム変数をチューニングすることで、キャッシュに割り当てるメモリを増やすなどしてDBチューニングが可能。mysql プロンプトでそれを確認する方法。

確認方法

# グローバルレベル
SHOW GLOBAL VARIABLES LIKE "%XXXX%";
SELECT @@global.xxxxxxx;

# セッションレベル
SHOW SESSION VARIABLES LIKE "%XXXX%";
SHOW VARIABLES LIKE "%XXXX%";
SELECT @@session.xxxxxxx;

また動的変更可能なパラメータについては、セッションレベルで変更可能である。

セッションレベルの動的変更

SET xxxxx=xxxxx;

2.スレッドバッファのチューニング

MySQL の各スレッドごとに割り当てられるスレッドバッファの上限値。

• read_buffer_size

テーブルフルスキャンをする際に使われるバッファサイズ。
そもそもテーブルフルスキャンをするようなクエリをあまり発行すべきではないので基本小さ目でよいと考える。

• read_rnd_buffer_size

ソート後にレコードを読むときに使われるメモリ領域としてのバッファ。

• join_buffer_size

インデックスを利用しない結合処理で作業領域として使われるメモリ領域としてのバッファ。

• sort_buffer_size

sort_buffer_size はソート用のメモリー領域を指定するパラメータ。ソート処理実行時にこのメモリー領域サイズを超えるソート処理はディスク上のファイルを利用して実行、つまり、ディスクIOが発生する。そのためパフォーマンスの低下に繋がる。
そのため、ファイルソートが発生している場合は、sort_buffer_sizeを増やす必要がある可能性あり。

ファイルソートの発生有無は、Sort_merge_passesが発生しているで確認できる

ファイルソート有無確認方法

root@localhost [db01] 13:09: > show global status like 'Sort%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Sort_merge_passes | 6157    |
| Sort_range        | 0       |
| Sort_rows         | 8388608 |
| Sort_scan         | 2       |
+-------------------+---------+
4 rows in set (0.00 sec)

• 参考

http://nippondanji.blogspot.com/2009/02/mysql10.html
https://www.percona.com/blog/2010/10/25/impact-of-the-sort-buffer-size-in-mysql/

3.内部一時テーブルに関するメモリチューニング

MySQL にはクエリの最中に内部的に一時テーブルを作成する動作がある。
内部一時テーブルが最初にインメモリーテーブルとして作成されたが、これが大きくなりすぎた場合、MySQL はこれを自動的にディスク上のテーブルに変換する。インメモリー一時テーブルの最大サイズは、tmp_table_size と max_heap_table_size の最小値となり、これは、CREATE TABLE によって明示的に作成された MEMORY テーブルと異なる。

一時内部テーブルの仕組みは以下のため、以下２種類の値を比較することで、どの程度の割合でディスクIOが発生しているか分かる。

• サーバーは内部一時テーブル (メモリー内またはディスク上のいずれか) を作成すると、Created_tmp_tables ステータス変数を増分する
• サーバーはディスク上にテーブルを作成する (内部で、またはインメモリーテーブルを変換して) 場合、Created_tmp_disk_tables ステータス変数を増分する

一時内部テーブルの作成割合

root@localhost [db01] 13:39: > show global status like 'Created_tmp%tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_tables      | 50    |
+-------------------------+-------+
2 rows in set (0.00 sec)

8.4.4 MySQL が内部一時テーブルを使用する仕組み
https://dev.mysql.com/doc/refman/5.6/ja/internal-temporary-tables.html

4.バッファプール用メモリのチューニング

一般的なデータベースでは、バッファプールというテーブルデータとインデックスを乗せるメモリー領域が存在する。
MySQLでは innodb_buffer_pool_size で設定する。
理想的にはデータとインデックスの総容量のサイズにするのがベストだがそこまで大量のメモリを搭載することは通常できないので、頻繁に使うアクセスするデータに関してはバッファプール上に載せられるような設計にするのが良い。

バッファプールヒット率が高い値がなるべく 100% になるようにする。
バッファプールヒット率 = 1-(Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)x100(%)

バッファプールの使用状況

root@localhost [db01] 14:07: > show global status like 'innodb_buffer%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 200418 13:58:04 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 52188                                            |
| Innodb_buffer_pool_bytes_data         | 855048192                                        |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 36                                               |
| Innodb_buffer_pool_pages_free         | 78868                                            |
| Innodb_buffer_pool_pages_misc         | 0                                                |
| Innodb_buffer_pool_pages_total        | 131056                                           |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 567                                              |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 3004874                                          |
| Innodb_buffer_pool_reads              | 51588                                            |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 325                                              |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.00 sec)

◼︎ バッファプールの現在の状態

• Innodb_buffer_pool_pages_total
  バッファプールに割り振られているページの合計

• Innodb_buffer_pool_pages_data
  バッファプールにキャッシュされているデータのページの合計

• Innodb_buffer_pool_pages_dirty
  バッファプール内で変更されているページの合計（ダーティページ）

• Innodb_buffer_pool_pages_free
  バッファプールの空きリストのページの合計

• Innodb_buffer_pool_pages_misc
  バッファプールのadaptive hash indexなどで確保されたその他のページの合計

◼︎ バッファプールへのアクセス情報

• Innodb_buffer_pool_pages_flushed
  バッファプールからディスクへフラッシュした総数

• Innodb_buffer_pool_write_requests
  バッファプールに書いたページ総数

• Innodb_buffer_pool_read_requests
  バッファプールから読んだページ総数

• Innodb_buffer_pool_reads
  ディスクから読んでバッファプールにロードしたページ総数

• Innodb_pages_created
  バッファプールに作成されたページの総数

• Innodb_pages_written
  バッファプールからディスクに書き込まれたページの総数

バッファプールヒット率

show engine innodb status\G

### セクション内のBuffer pool hit rateを確認。前回出力からバッファプールから読み出していない場合は、「No buffer pool page gets since the last printout」と出る
----------------------
BUFFER POOL AND MEMORY
----------------------
***略***
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

参考
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0101?page=2

#5.コネクションに関わるチューニング

MySQL ではクライアントがサーバーに接続すると、コネクションスレッドが生成される。その後、接続が終了するとコネクションスレッドが破棄される。コネクションスレッドの生成と破棄にはそれなりに負荷がかかるので、MySQL ではスレッドキャッシュというコネクションを破棄せず使い回す方法がある。スレッドキャッシュで何本非アクティブなコネクションスレッドを残すかを、thread_cache_size で指定できる。クライアントが接続時にコネクションスレッドが生成されるとステータス変数Thread_createdがインクリメントされるので、時間と共にこの値が増加傾向にあるならば、thread_cache_sizeを増加させるべき。

検証してみたところ、thread_cache_size はサーバー起動時にスレッドを事前起動する訳ではなく、使ったコネクションを切断するときにいくつまで残しておくかを決めるシステム変数。

理想的には最大接続数 max_connection(最大同時接続数) で指定できるので同値にしたいところだが、常時必要なものではないのでメモリのリソース消費量を考えると普段の平均的な接続数あたりにしておくのも良いのではないだろうか。

コネクションスレッド生成状況

root@localhost [db01] 15:33: > show global status like 'Threads_created';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_created | 3     |
+-----------------+-------+
1 row in set (0.00 sec)

6.ログファイル

Innodbはトランザクション型のDBであり、RDBMS界隈で一般的なオンラインログが存在する。
Oracle のREDOログと似ているがMySQLでも該当するものがあり、以下のパラメータで制御する。

innodb_log_file_size
REDO ログ 1つあたりのサイズだ。
REDO ログは、書き込みの高速化と耐久性の確保、クラッシュリカバリのために使われる。クラッシュリカバリのパフォーマンスが MySQL 5.5で大きく向上したので、大きめのサイズをとっても問題ない。大きめのサイズを取ることでDB本体への書き込み頻度を減らしてパフォーマンスを向上できる。REDO ログが満杯になると、DB 本体に溜まった更新が書き込まれるイメージ

innodb_log_files_in_group
REDO ログをいくつ作るかどうかの設定。

innodb_flush_log_at_trx_commit
デフォルト値 (1)で、ACID特性に沿った形になり、コミットしたトランザクションが REDO ログに間違いなくディスクにフラッシュしてはかれる設定。基本的には、デフォルト値でよい。

innodb_flush_method
InnoDB ファイル操作に対して OS のファイルシステムキャッシュを経由しないようにするように、基本的に O_DIRECT を指定するべきと考える。

• OS でのキャッシュと InnoDB でのキャッシュの両方を利用するのは非効率なのでどちらかに統一。

innodb_log_buffer_size

コミットされていないトランザクションのためのバッファのサイズ。
デフォルト値(1MB)は多くの場合で適量かもしれないが、大きなblobやテキストのカラムを扱うトランザクションを使っている場合、すぐにバッファはいっぱいになり、余計なI/Oを引き起こすことになる。SHOW ENGINE INNODB STATUS の Innodb_log_waits を確認してそれが 0 でなければ、innodb_log_buffer_sizeを増やすこと。

• Innodb_log_waits = ログバッファーが小さすぎるため、一度領域をディスクに対してフラッシュするために待機が必要だった回数。

• 参考

[*] InnoDB ログと binlog の違い
https://www.ipride.co.jp/blog/3660

[*] 各パラメータ説明
http://nippondanji.blogspot.com/2009/01/innodb.html

7.SQLチューニング

(1) スロークエリログ

実行時間が長く処理が長期化するSQLを検知するための仕組み。long_query_timeで設定した秒数を超えたSQLをログファイル内に記録する。
試しに以下のSQLでlong_query_time以上の時間の間、SQLを実行してみると記録されることがわかる。

select sleep(xx)

以下の管理系 SQL 文もスロークエリログに記録するには，log_slow_admin_statementsをONに設定する必要があり。

• ALTER TABLE
• ANALYZE TABLE
• CHECK TABLE
• CREATE INDEX
• DROP INDEX
• OPTIMIZE TABLE
• REPAIR TABLE

インデックスを使用していないSQL文の記録もスロークエリログに記録ができる。
log_queries_not_using_indexesをONに設定すると，long_query_timeを超えていないSQL文でも，インデックスを使用せずフルテーブルスキャンをしたものがログに記録される。フルテーブルスキャンは多くのディスクIOを発生させサーバ全体のSQLのレイテンシを増加させている要因になり得るので、インデックスの使用していないという事実を明らかにする上で有用だろう。

ただし，レコード件数が小さなテーブルの場合，インデックスを走査してから実際のレコードを取得するよりも，始めからテーブルスキャンをして直接レコードを取得したほうが効率が良いこともありえる。その類のSQL文が多数実行されることが想定される場合は，min_examined_row_limitにてフルテーブルスキャンしたSQL文が最低何行アクセスしたらログに記録するかを設定することでスロークエリログへの出力を調整できる。

また、事前にフルテーブルスキャンするSQL文が多くなることが想定されている場合は，フルテーブルスキャンしたSQL文を1分間にいくつまで記録するかをlog_throttle_queries_not_using_indexes にて設定可能です。

*なお，min_examined_row_limitとlog_throttle_queries_not_using_indexesはMySQL 5.6から利用可能となった設定

(2) 実行中SQL文の稼働チェック

実行中のSQLの稼働状況をチェックするためには、SHOW FULL PROCESSLISTが利用できる。
この構文をmysql プロンプト上で実行することで、実行中SQLのID、ユーザ名、実行ホスト、SQL実態を表示することができる。
SUPERUSER権限を持ったユーザで、IDをKILLすれば実行中のSQLを強制停止することも可能である。

実行中SQL稼働確認

root@localhost [db01] 06:02: > SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
     Id: 2
   User: repl
   Host: db2.xxxxxxxxxxxxxxxxxxxxxx:47466
     db: NULL
Command: Binlog Dump GTID
   Time: 4597
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 2. row ***************************
     Id: 8
   User: root
   Host: localhost
     db: db01
Command: Query
   Time: -1
  State: starting
   Info: SHOW FULL PROCESSLIST
*************************** 3. row ***************************
     Id: 9
   User: root
   Host: localhost
     db: db01
Command: Query
   Time: 8
  State: User sleep
   Info: select sleep(100)
3 rows in set (0.00 sec)

SQL強制停止　

KILL CONNECTION <PROCESSLISTで確認できるID>

(3) 実行計画

MySQLサーバのオプティマイザが，どのインデックスを利用するかやJOINの順序をどのように決めるかなどの実行計画に関してEXPLAIN文で確認できる。
また、MySQL 5.6.3以降は、insert、update、delete 文といった更新DMLもEXPLAINで実行計画を表示可能となった。

例：実行計画

root@localhost [db01] 06:37: > explain select * from item;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | item  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 8376062 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

root@localhost [db01] 06:37: > explain select count(1) from item;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | item  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 8376062 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

各カラムの大まかな意味

カラム	意味
id	SELECT 識別子
select_type	SELECT 型
table	出力行のテーブル
partitions	一致するパーティション
type	結合型
possible_keys	選択可能なインデックス
key	実際に選択されたインデックス
ref	インデックスと比較されるカラム
rows	調査される行の見積もり
filtered	テーブル条件によってフィルタ処理される行の割合
Extra	追加情報

*各カラムの値に関しては多種多様なので以下を確認しよう。
https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html

##(4) インデックスヒント
オプティマイザに対して特定のインデックスの使用有無を明示的に指示することができる。
MySQL 5.7 では以下のインデックスヒントが利用できる。

• USE INDEX
  特定のインデックスを使用するようにオプティマイザに指示。（指定されたインデックスが使用できたとしても、オプティマイザがテーブルスキャンの方が効率的と判断するとインデックスが使われない）

• FORCE INDEX
  特定のインデックスを使用するようにオプティマイザに指示。（指定されたインデックスが使用できる場合、オプティマイザは必ず当該インデックスを使用）

• IGNORE INDEX
  特定のインデックスを使用しないようにオプティマイザに指示。

例

root@localhost [db01] 07:03: > explain select * from aaa FORCE INDEX(IND1) where id = 9999;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | aaa   | NULL       | ref  | ind1          | ind1 | 5       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [db01] 07:03: > explain select * from aaa IGNORE INDEX(IND1) where id = 9999;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | aaa   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Tips: 特定のテーブルに貼られているインデックス一覧
SHOW INDEX FROM <テーブル名>

##(5) STRAIGHT_JOIN ヒント
STRAIGHT_JOIN ヒント句を使うことで結合の順番を強制的に指定することができる。
具体的には以下の構文で指定するが、左のテーブルを駆動表、右のテーブルを内部表として処理するように強制可能。
オプティマイザにより、想定の結合順序が指定されない場合に有用である。

駆動表にするテーブル STRAIGHT_JOIN 内部表にするテーブル名 

##(6) NJL (ネステッドループ結合)の例
ここで、MySQLで可能な結合方式 NJL の例を見ておきたい。以下の２つのテーブルを ID 列をキーに結合する。

• ext 表。3行しかない小規模表
• item99 表。10000000行ある超大規模表。ID 表がプライマリキーになっている。

NJL では基本的に以下の条件を満たすことで検索行を最小限に抑えることができ、処理の高速化が可能である。

• 駆動表は小規模な表にする
• 内部表のキー列にはインデックスを貼る。

以下EXPLAINの例である。

mysql> explain select * from item99 join ext on (item99.id=ext.id) ;
+----+-------------+--------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table  | partitions | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+--------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | ext    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL        |    3 |   100.00 | Using where |
|  1 | SIMPLE      | item99 | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | db01.ext.id |    1 |   100.00 | NULL        |
+----+-------------+--------+------------+--------+---------------+---------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

特出すべき点としては以下があり。

• idフィールドはSELECTを構成するJOINにそれぞれつけられる。上記のクエリは2つのテーブルをJOINしているので、SELECTの構成単位であるJOINはひとつしかない。そのため全てのテーブルで共通のidである「1」が割り当てられている。
• EXPLAINで表示されている順序がJOINの順序を表している。すなわち、ここではextテーブルが駆動表、item99テーブルが内部表
• possible_keysフィールドは使用可能なインデックスの一覧。ref フィールドが実際に使われたインデックス。（プライマリーキー）内部表へのアクセスはこのプライマリーキーが選択されたことが分かる。
• type フィールドはテーブルへの以下のようなテーブルへのアクセスタイプを示す。extテーブルはテーブルスキャンがされており、item99テーブルはプライマリーキーによる行のフェッチが行われている。

タイプ	説明
const	PRIMARY KEYまたはUNIQUEインデックスのルックアップ（等価比較）によるアクセス
eq_ref	JOINにおいて内部表から行のフェッチがPRIARY KEYまたはUNIQUE KEYにのルックアップよって行われることを示す。constと似ているがJOINで用いられるところが違う
ref	ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセスタイプ
range	インデックスを用いた範囲検索
ALL	フルテーブルスキャン
index	フルインデックススキャン

結論としては、ext 表の各行に対してitem99 表で該当する行がそれぞれ１行フェッチされており、結果的にextテーブルは3行検索、item99テーブルは10000000行あるにも関わらず実際に検索されたのは3行だけとなっている。もし、駆動表がitem99テーブルの場合はitem99テーブルの各行に関してextテーブルに条件に会う行が存在するかチェックしなければならないので少なくとも10000000行はフェッチしなければならない。またインデックスがitem99テーブルになかった場合は、両テーブル共にテーブルスキャンするため、4x10000000=40000000行のフェッチが必要。

参考
https://enterprisezine.jp/article/detail/3520?p=4

8. 統計情報

オプティマイザが実行計画を作成する際には一般的に対象テーブルやインデックスの統計情報(テーブル行数、インデックスのリーフページ数)等を元に決める。
テーブル統計とインデックス統計情報は、以下の確認方法で見ることができる。

統計情報収拾実行(手動)

ANALYZE TABLE item99;

テーブル統計/インデックス統計確認

set @v1 ='item99';
select * from mysql.innodb_table_stats where table_name=@v1;select * from mysql.innodb_index_stats where table_name=@v1;

インデックス統計情報を利用したインデックスサイズの算出

set @v1 ='item99';
SELECT SUM(stat_value) pages, index_name,
SUM(stat_value)*@@innodb_page_size size
FROM mysql.innodb_index_stats 
WHERE table_name=@v1 AND stat_name = 'size' GROUP BY index_name;

• 統計情報に関連するサーバーパラメータ

innodb_stats_persistent
統計情報を永続的なものにするための設定（永続的=再起動しても消えない）。デフォルトはON。

innodb_stats_auto_recalc
テーブルに (行の 10% を超える) 大幅な変更が加えられた場合に常に統計を自動的に再計算。デフォルトはON。

innodb_stats_persistent_sample_pages
統計を収集するために実行されるサンプリングのページ数。InnoDB は、インデックスのカーディナリティーを推定するためにテーブル上の各インデックスからランダムなページをサンプリングするのでそのページ数(これを「ランダムダイブ」と呼ぶらしい)。基本デフォルト値でよいが何か問題が発生したら調整を検討する。

参考:
https://dev.mysql.com/doc/refman/5.6/ja/innodb-persistent-stats.html
https://www.percona.com/blog/2017/09/11/updating-innodb-table-statistics-manually/

9.テーブル/インデックス再編成

InnoDB では、更新を繰り返していると、断片化（フラグメンテーション）という現象が発生する。

フラグメンテーションが発生すると、本来読み取らなくて良い場所まで無駄に読み取る形になるため、クエリ処理が遅くなる可能性がある。
例えば、極端な例でいうと10000000行あるテーブルの内、5000000行を delete した状態だと、データとしては 5000000 行しかない一方でテーブルが占有している領域としては 10000000 行使っている状態になる

以下の data_free 列が割り当てられているが、使用されていないバイト数。この値が高いほどフラグメンテーション率が高いのでデフラグの必要性がある。

フラグメンテーションの発生状況

SELECT table_schema, table_name, data_free, table_rows 
FROM information_schema.tables 
WHERE table_schema = 'db01';
...
+--------------+------------+-----------+------------+
| table_schema | table_name | data_free | table_rows |
+--------------+------------+-----------+------------+
| db01         | item       | 150994944 |    8909120 |
| db01         | item1      |         0 |          0 |
+--------------+------------+-----------+------------+

フラグメンテーションを解消するには最適化をおこなう。このコマンドだが内部的な動きが要注意なので詳しくは以下を見て確認しておく。
http://nippondanji.blogspot.com/2009/05/alter-table.html

テーブル最適化

alter table item99 engine innodb;
optimize table item99;

• MySQL 5.6.17以降では、optimize table は結局、alter table ... engine innodb にマッピングされている。一方で、MySQL 5.6.17より前では、optimize table 文はテーブルロックがかかり並列で行われるDML(Insert/Update/Delete)がロックされるので注意。

Mysql 5.6.17 より前は、OPTIMIZE TABLE はオンライン DDL (ALGORITHM=INPLACE) を使用しません。その結果、OPTIMIZE TABLE が実行中のテーブルに対しては (つまり、そのテーブルのロック中は) 並列 DML (INSERT、UPDATE、DELETE) が許可されません。また、主キーに現れる順序でキーが挿入されるため、セカンダリインデックスはそれほど効率的に作成されません。
5.6.17 の時点では、OPTIMIZE TABLE は、InnoDB の通常のテーブルとパーティション化されたテーブルの両方に対してオンライン DDL (ALGORITHM=INPLACE) を使用します。OPTIMIZE TABLE によってトリガーされ、ALTER TABLE ... FORCE の下で実行されるテーブル再構築は現在、オンライン DDL (ALGORITHM=INPLACE) を使用して実行され、短期間しかテーブルをロックしないため、並列 DML 操作のためのダウンタイムが短縮されます。

• 尚、MySQL ではインデックスのみ再編成するコマンドはなく、上記のコマンドでインデックスも同時に再編成される。

10.ロック

###共有ロック(S)と排他ロック(X)

• 共有ロックは、共有ロックと共存できるが排他ロックとは共有できない。読み取りするときに、他のトランザクションに更新されないようにするロックと考えれば良い。(select ... lock in share mode)
• 排他ロックは、共有ロックとも排他ロックとも共存できない。書き込みするときに、他のトランザクションに参照・更新されないようにするロックと考えれば良い。(select ... for update)

参考：
https://dev.mysql.com/doc/refman/5.6/ja/innodb-lock-modes.html

###ギャップロック

InnoDBではインデックス上のレコードとレコードの間がロックされることがあり、これをギャップロックと呼ぶ。
ファントムリードを防止するには、その時点で存在しないレコードに対するロックを取得する必要があるが、これを実現する方法がギャップロック。そのため、MySQL InnoDB では REPEATABLE READ でもファントムリードが発生しない。

なお、あくまでREPEATABLE READ 時の挙動であり、READ COMMITED では発生しない。

例1 (#TX1 - REPEATABLE READ)

• TX1
  あるトランザクションで、select ... for update でi=3~7を検索。この際にまだ存在しないi=5に対しても排他ロックが取得されるのがギャップロック

[e-mail] [db01] 15:39: > SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 2 warnings (0.00 sec)
[e-mail] [db01] 15:39: > begin;
Query OK, 0 rows affected (0.01 sec)

[e-mail] [db01] 15:39: > select * from t where i >=3 and i<=7 for update;
+---+
| i |
+---+
| 3 |
| 4 |
| 6 |
| 7 |
+---+
4 rows in set (0.00 sec)

• TX2
  別のトランザクションで、i=5をInsertしようとするもロック待機となる。

[e-mail] [db01] 15:41: > begin;
Query OK, 0 rows affected (0.00 sec)

[e-mail] [db01] 15:41: > insert into t value(5);

例2 (#TX1 - READ COMMITED)

• TX1
  あるトランザクションで、select ... for update でi=3~7を検索。この際にまだ存在しないi=5に対して排他ロックは取得されない(ギャップロックが発動しない)

[e-mail] [db01] 15:43: > SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

[e-mail] [db01] 15:43: > SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| REPEATABLE-READ       | READ-COMMITTED |
+-----------------------+----------------+
1 row in set, 2 warnings (0.00 sec)

[e-mail] [db01] 15:43: > begin;
Query OK, 0 rows affected (0.00 sec)

[e-mail] [db01] 15:43: > select * from t where i >=3 and i<=7 for update;
+---+
| i |
+---+
| 3 |
| 4 |
| 6 |
| 7 |
+---+
4 rows in set (0.00 sec)

• TX2
  別のトランザクションで、i=5をInsert可能。

[e-mail] [db01] 15:43: > begin;
Query OK, 0 rows affected (0.00 sec)

[e-mail] [db01] 15:43: > insert into t value(5);
Query OK, 1 row affected (0.00 sec)

[e-mail] [db01] 15:43: > select * from t where i >=3 and i<=7 ;
+---+
| i |
+---+
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
5 rows in set (0.00 sec)

色々な応用例：
https://dbstudy.info/files/20140907/mysql_lock_r2.pdf
https://nippondanji.blogspot.com/2013/12/innodbrepeatable-readlocking-read.html
https://techblog.kayac.com/repeatable_read.html
https://github.com/ichirin2501/doc/blob/master/innodb.md