「テーブルの最適化と統計情報」
テーブルやインデックスを最適化して、統計情報を最新に更新して、最適化されたかどうか未使用領域を確認するにはどうすればよいか
回答
テーブルの統計情報を最新に更新するには
analyze table テーブル名;
統計情報からテーブルの未使用領域のバイト数等を確認
select
data_free/1024/1024 as FreeMB, -- 割り当てられているが、使用されていないバイト数(MB)
from information_schema.tables
where table_schema='データベース名' and table_name='テーブル名';
テーブルを最適化する（InnoDB）
ALTER TABLE テーブル名 ENGINE=INNODB;
テーブルを最適化する（MyISAM）
optimize table テーブル名;
以下、例を示します。
ソース
■テーブルの統計情報を最新に更新する
analyze table テーブル名;
（例）
mysql> analyze table t_post;
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| db_cms.t_post | analyze | status | OK |
+---------------+---------+----------+----------+
1 row in set (0.00 sec)
■統計情報からテーブルの未使用領域のバイト数等を確認
select
table_schema,
table_name,
data_free/1024/1024 as FreeMB, -- 割り当てられているが、使用されていないバイト数(MB)
data_length/1024/1024 as DataMB, -- データファイルの長さ(MB)
index_length/1024/1024 as IndexMB -- インデックスファイルの長さ(MB)
from information_schema.tables
where table_schema='データベース名' and table_name='テーブル名';
（例）
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;
・MyISAMの場合
optimize table テーブル名;
※最適化しても、これ以上最適化できない時は「information_schema.tables.data_free」はゼロになりません。
（例１）
mysql> ALTER TABLE t_post ENGINE=INNODB;
Query OK, 11 rows affected (0.09 sec)
Records: 11 Duplicates: 0 Warnings: 0
（例２）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のキャッシュ機構によりテーブルのデータがメモリキャッシュされるそうです。
■実行されているスレッドを表示する
SHOW FULL PROCESSLIST;
Process権限（rootユーザーなど）のあるユーザーで実行すると全てのスレッドを表示できます。
（例）
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 テーブル名(カラム名 [,カラム名・・・])
（例）
create index idx_t_post_1 on t_post(sdate,edate,counter);