MariaDB(MySQL)の最適化と統計情報と実行中のスレッド情報の取得
MySQLやMariaDBでテーブルの最適化や統計情報の確認を行いたいとき、OPTIMIZE TABLEやANALYZE TABLEの使い方に戸惑ったことはありませんか？
このページでは、「table does not support optimize」エラーの意味や、data_freeを使った断片化の確認方法、InnoDBとMyISAMの違い、スレッドの強制終了方法まで、実例付きでわかりやすく解説します。
テーブルの統計情報で断片化の度合いを確認し、断片化している場合はテーブルを最適化すれば処理速度が向上する可能性があります。
また、実行中で終了していない（遅い）スレッド（実行中のSQL）を一覧表示して、スレッドを強制終了しなければならない場合もあります。
- テーブルの統計情報の取得
- テーブルの最適化、DBの最適化
- 実行中スレッドの表示と、スレッドの強制終了
について解説します。
統計情報
テーブルの統計情報を用いると断片化の度合いなどを確認できます
テーブルの統計情報を最新に更新
テーブルの統計情報を最新に更新するには
ANALYZE TABLE テーブル名;
テーブルの統計情報を取得
テーブルの統計情報を取得するには
SELECT * FROM information_schema.tables
WHERE table_schema='データベース名' AND table_name='テーブル名';
（例）統計情報からデータベース「db_cms」の「t_post」テーブルの未使用領域(data_free)、データ領域(data_length)、インデックス領域(index_length)のMByte数を確認
mysql> SELECT
table_schema,
table_name,
data_free/1024/1024 as FreeMB,
data_length/1024/1024 as DataMB,
index_length/1024/1024 as IndexMB
FROM information_schema.tables
WHERE table_schema='db_cms' AND table_name='t_post';
+--------------+------------+------------+------------+------------+
| table_schema | table_name | FreeMB | DataMB | IndexMB |
+--------------+------------+------------+------------+------------+
| db_cms | t_post | 8.00000000 | 1.51562500 | 0.07812500 |
+--------------+------------+------------+------------+------------+
1 row in set (0.00 sec)
※テーブルを最適化しても、これ以上最適化できない場合は「data_free」はゼロになりません。
テーブルを最適化する
InnoDBの場合
ALTER TABLE テーブル名 ENGINE=INNODB;
（例）
mysql> ALTER TABLE t_post ENGINE=INNODB; Query OK, 11 rows affected (0.09 sec) Records: 11 Duplicates: 0 Warnings: 0
MyISAMの場合
OPTIMIZE TABLE テーブル名;
（例）InnoDBなのに「optimize table」を実行した場合
mysql> optimize table t_post; +---------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------+----------+----------+-------------------------------------------------------------------+ | db_cms.t_post | optimize | note | Table does not support optimize, doing recreate + analyze instead | | db_cms.t_post | optimize | status | OK | +---------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.17 sec)
※Msg_textのメッセージ「Table does not support optimize, doing recreate + analyze instead」とは、
InnoDBのテーブルを「OPTIMIZE」しようとしたが、実際には「ALTER TABLE」でテーブル再作成とインデックス張り直しを行ったという意味です。
statusがOKなので正常に完了していることを示しています。
注意点など
テーブルを最適化することによって処理速度が速くなるはずですが、適切にインデックスが設定されていないテーブルを最適化すると遅くなる場合があります。
最適化によりキャッシュがクリアされる為ではないかと思います。
InnoDBは「innodb_buffer_pool_size」設定によりテーブルやインデックスがメモリにキャッシュされるそうです。
（確認方法）SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
（設定方法）SET GLOBAL innodb_buffer_pool_size = 変更したいサイズ; -- 処理に時間がかかるので注意
MyISAMは「key_buffer_size」設定によりインデックスがメモリにキャッシュされ、OSのキャッシュ機構によりテーブルのデータがメモリキャッシュされるそうです。
DBを最適化する
DBを最適化するには
mysqlcheck -o -u ユーザー名 -pパスワード データベース名
で可能です。
全データベースを最適化するには -A オプションを追加します。
mysqlcheck -o -u ユーザー名 -pパスワード -A
更に自動修復させるには --auto-repair オプションを追加します。
mysqlcheck -o -u ユーザー名 -pパスワード -A --auto-repair
> mysqlcheck -o -u user -ppassword --auto-repair db_test db_test.t_bu note : Table does not support optimize, doing recreate + analyze instead status : OK db_test.t_emp note : Table does not support optimize, doing recreate + analyze instead status : OK db_test.t_unit note : Table does not support optimize, doing recreate + analyze instead status : OK
実行されているスレッドを表示する
Process権限（rootユーザーなど）のあるユーザーで
SHOW FULL PROCESSLIST;
で一覧が表示できます。
mysql> SHOW FULL PROCESSLIST;
+----+-------------+-----------------+--------+---------+------+---------------+------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------------+--------+---------+------+---------------+------------------------+----------+
| 8 | root | localhost:50174 | db_cms | Query | 0 | Init | show full processlist | 0.000 |
| 57 | user | localhost:50359 | db_cms | Query | 0 | Sending data |
SELECT sc.cname,p.pid,p.cid,p.title,p.img
FROM (SELECT * FROM tm_category WHERE pcid=0) AS c
INNER JOIN (SELECT * FROM tm_category WHERE pcid>0) AS sc
ON c.cid=sc.pcid
INNER JOIN (
SELECT * FROM t_post
WHERE visible=1 AND (sdate<=NOW() AND (edate IS NULL OR edate>=NOW())) ORDER BY counter DESC LIMIT 5
) p
ON sc.cid=p.cid
ORDER BY p.counter DESC | 0.000 |
+----+-------------+-----------------+--------+---------+------+---------------+------------------------+----------+
実行されているスレッドを強制終了する
KILL プロセスID
で強制終了できます。
プロセスIDは、SHOW FULL PROCESSLISTで表示されるIdを指定します。
インデックスを作成する
CREATE [UNIQUE] INDEX インデックス名 ON テーブル名(カラム名 [,カラム名・・・])
以下は重複可能なインデックス「idx_t_post_1」を"t_post"テーブルの"sdate,edate,counter"カラムに対して作成しています。
create index idx_t_post_1 on t_post(sdate,edate,counter);