MySQLの最適化と統計情報と実行中のスレッド情報

MySQLの最適化と統計情報と実行中のスレッド情報の取得

統計情報

テーブルの統計情報を用いると断片化の度合いなどを確認できます

テーブルの統計情報を最新に更新

テーブルの統計情報を最新に更新するには
ANALYZE TABLE テーブル名;

テーブルの統計情報を取得

テーブルの統計情報を取得するには
SELECT * FROM information_schema.tables WHERE table_schema='データベース名' AND table_name='テーブル名';

（例）統計情報からデータベース「db_cms」の「t_post」テーブルの未使用領域、データ領域、インデックス領域の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 テーブル名(カラム名 [,カラム名・・・])

create index idx_t_post_1 on t_post(sdate,edate,counter);