リード開発メモ

大阪のソフトウェア会社です。 技術的な事柄についてのメモとしてブログを始めます。

MySQL

MySQL で検索が遅い

MySQL でスロークエリログを出力するようにしたところ、いくつかのクエリが遅いようだった。
中でも次の非常に単純なクエリが遅いのが気になった。
しかし、クエリはこれ以上改良しようがない。
mysql> select TradingDay from test_db.table1
    -> group by TradingDay order by TradingDay desc limit 10;
+---------------------+
| TradingDay          |
+---------------------+
| 2013-06-25 00:00:00 |
| 2013-06-07 00:00:00 |
| 2013-06-06 00:00:00 |
| 2013-06-05 00:00:00 |
| 2013-06-04 00:00:00 |
| 2013-06-03 00:00:00 |
| 2013-05-31 00:00:00 |
| 2013-05-30 00:00:00 |
| 2013-05-29 00:00:00 |
| 2013-05-28 00:00:00 |
+---------------------+
10 rows in set (0.45 sec)
テーブルの定義は以下のようなもので、InnoDB を使っている。

CREATE TABLE `table1` (
  `TradingDay` datetime NOT NULL,
  `BrandCode` varchar(10) NOT NULL COMMENT '銘柄コード',
  `BrandName` varchar(45) DEFAULT NULL COMMENT '銘柄名',
  `MarketCode` varchar(4) DEFAULT NULL COMMENT '市場コード',
  `Characteristics` varchar(200) DEFAULT NULL COMMENT '特色',
  `CreateTime` datetime DEFAULT NULL COMMENT '作成日時',
  `ModifiedTime` datetime DEFAULT NULL COMMENT '更新日時',
  PRIMARY KEY (`BrandCode`,`TradingDay`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='銘柄テーブル'$$

レコード数は42万件。けっして少なくはないが。。。
mysql> SELECT count(*) FROM test_db.table1;
+----------+
| count(*) |
+----------+
|   425496 |
+----------+
1 row in set (0.17 sec)
遅い理由については以下のページに詳しい解説があった。
結論を言えば「MySQL の主キーのスキャンはテーブルスキャンなので遅い。セカンダリーインデックスを設定すればインデックスのみのスキャンになるので速くなる。」ということだ。
http://nippondanji.blogspot.jp/2010/03/innodbcount.html

また、MySQL の explain コマンドについても同サイトで詳しく解説されている。
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html

セカンダリーインデックスを設定する前。
key のところを見ると PRIMARY となっているのが分かる。

mysql> explain
    -> select TradingDay from test_db.table1
    -> group by TradingDay order by TradingDay desc limit 10;
+----+-------------+--------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows   | Extra                                        |
+----+-------------+--------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | table1 | index | NULL          | PRIMARY | 40      | NULL | 418139 | Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)

セカンダリーインデックスを設定する。

mysql> ALTER TABLE test_db.table1 ADD INDEX (TradingDay);
Query OK, 0 rows affected (7.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

セカンダリーインデックスを設定した後。
key のところが設定したばかりのセカンダリーインデックスに変わった。
mysql> explain
    -> select TradingDay from test_db.table1
    -> group by TradingDay order by TradingDay desc limit 10;
+----+-------------+--------+-------+---------------+------------+---------+------+--------+-------------+
| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows   | Extra       |
+----+-------------+--------+-------+---------------+------------+---------+------+--------+-------------+
|  1 | SIMPLE      | table1 | index | NULL          | TradingDay | 8       | NULL | 418139 | Using index |
+----+-------------+--------+-------+---------------+------------+---------+------+--------+-------------+
1 row in set (0.00 sec)

そして問題のクエリの実行時間は10分の1程度になった。
mysql> select TradingDay from test_db.table1
    -> group by TradingDay order by TradingDay desc limit 10;
+---------------------+
| TradingDay          |
+---------------------+
| 2013-06-25 00:00:00 |
| 2013-06-07 00:00:00 |
| 2013-06-06 00:00:00 |
| 2013-06-05 00:00:00 |
| 2013-06-04 00:00:00 |
| 2013-06-03 00:00:00 |
| 2013-05-31 00:00:00 |
| 2013-05-30 00:00:00 |
| 2013-05-29 00:00:00 |
| 2013-05-28 00:00:00 |
+---------------------+
10 rows in set (0.03 sec)
以上。

MySQL スロークエリログ

MySQL では時間のかかるクエリを実行したときにログ出力をおこなうようにすることができる。
MySQL 5.1 以降でのその設定。

設定ファイル C:\pleiades\xampp\mysql\bin\my.ini の mysqld セクションに以下を追加する。
[mysqld]
slow_query_log = 1
log_output = FILE
slow_query_log_file = "C:/pleiades/xampp/mysql/logs/slow_query.log"
long_query_time = 1

slow_query_log はスロークエリ出力の有効無効を 0/1 または OFF/ON で設定する。
log_output はログの出力先。TABLE、FILE、またはその両方を指定する。
long_query_time で設定した秒数より時間がかかったクエリが出力対象となる。

MySQL 5.0 以前では次のようにする。
[mysqld]
log-slow-queries = "C:/pleiades/xampp/mysql/logs/slow_query.log"
long_query_time = 1

MySQL バージョンの確認方法

MySQL のバージョンを確認する方法はいくつかある。

1. mysqladmin コマンド
C:\work>mysqladmin -u root -p version
Enter password: *****
mysqladmin Ver 8.42 Distrib 5.5.25a, for Win32 on x86
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version 5.5.25a-log
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 15 min 53 sec

Threads: 1 Questions: 8 Slow queries: 0 Opens: 33 Flush tables: 1 Open tabl
es: 26 Queries per second avg: 0.008

2. mysql コマンド
C:\work>mysql --version
mysql Ver 14.14 Distrib 5.5.25a, for Win32 (x86)

3. status コマンド
mysql> status
--------------
mysql Ver 14.14 Distrib 5.5.25a, for Win32 (x86)

Connection id: 9
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.5.25a-log MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 22 min 40 sec

Threads: 1 Questions: 12 Slow queries: 0 Opens: 33 Flush tables: 1 Open tab
les: 26 Queries per second avg: 0.008
--------------

4. version関数
mysql> select version();
+-------------+
| version() |
+-------------+
| 5.5.25a-log |
+-------------+
1 row in set (0.00 sec)


以上。

MySQL の OPTIMIZE TABLE

MySQL に何度も大量のデータを登録する必要があったので、テーブルのメンテについて調べた。

http://nippondanji.blogspot.jp/2010/09/innodb.html

少し話がそれてしまったが、OPTIMIZEが必要になるのはどういう時だろうか?簡単にいうと、「行をDELETEのして無駄な領域がたくさん生じたとき」だけである。これによって、利用されていない領域が回収されることになる。そして、フラグメンテーションが解消するわけである。

実は、InnoDBにはOPTIMIZE TABLEに相当する機能は実装されておらず、代わりにALTER TABLEが実行される。OPTIMIZE TABLE t1は次のコマンドを実行するのと同じなのである。

モノホンのOPTIMIZE TABLEだと実行中は参照も更新もブロックしてしまうことになるが、ALTER TABLEであればメンテナンス中も参照は可能なのである。

mysqldump ・・・ 論理バックアップをオンラインで取得する。他の方法に比べると速度は遅め。

MySQL をテーブルごとのデータファイルにする

MySQL をテーブルごとのデータファイルにする方法がある。
設定ファイル C:\pleiades\xampp\mysql\bin\mysql.ini に以下を追加する。

[mysqld]
innodb_file_per_table

アクセスカウンター
  • 今日:
  • 昨日:
  • 累計:

livedoor 天気