MySQLの複合主キーテーブル検索時の速度比較

MySQL の複合主キーテーブルのレコードを何件かピックアップして主キー検索したい機会があったので、いくつかのパターンの SQL で検索してパフォーマンス比較をしてみました。

実験の内容

  • それぞれ適当にピックアップした15レコードを1クエリで検索する
  • 実行時間は同じクエリを5回実行したときの最小と最大の時間を計測
  • レコードは適当に100,000件投入

実験環境

  • Ubuntu 13.10 (on Vagrant)
  • MySQL 5.5.37
  • InnoDB

使用したテーブル

CREATE TABLE `myTable` (
  `player_id` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `body` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`player_id`,`id`)
) ENGINE=InnoDB;

各検索方法の実行時間とインデックス使用状況

1. OR検索の場合

SQL例

SELECT * FROM `myTable`
WHERE  (`player_id` = 50 AND `id` = 4856)
  OR (`player_id` = 61 AND `id` = 4274);

※実験時は15個のORを繋げて検索しましたが長くなるのでここでは省略しています。

実行時間

1.2ms ~ 1.5ms

EXPLAIN結果

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE myTable range PRIMARY PRIMARY 8 NULL 15 Using where

2. UNION ALL検索の場合

SQL例

SELECT * FROM `myTable` WHERE (`player_id` = 50 AND `id` = 4856)
UNION ALL
SELECT * FROM `myTable` WHERE (`player_id` = 61 AND `id` = 4274);

実行時間

1.5ms ~ 2.0ms

EXPLAIN結果

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY myTable const PRIMARY PRIMARY 8 const,const 1
2 UNION myTable const PRIMARY PRIMARY 8 const,const 1
15 UNION myTable const PRIMARY PRIMARY 8 const,const 1
NULL UNION RESULT ALL NULL NULL NULL NULL NULL

3. WHERE IN の複合カラム指定による検索の場合

SQL例

SELECT * FROM `myTable`
WHERE (player_id, id) IN ((50, 4856),(61, 4274));

実行時間

40.9ms ~ 49.7ms

EXPLAIN結果

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE myTable ALL NULL NULL NULL NULL 100714 Using where

コメント

最も高速なのは普通に OR で繋げる検索でした。range スキャンになるんですね。次点で UNION ALL 検索。ちなみに ALL 無しの UNION に変えてもほぼ同じ速度でした。

そして最も遅かったのは WHERE IN 検索でした。IN 句に指定する件数が多いとオプティマイザはフルスキャンを選択するため大幅に遅い結果になりました。

シェルからCakePHPを使うとAPC書き込みに失敗する問題

ウェブからアプリケーションにアクセスしてAPCを使うと普通に使えるのに、
コマンドラインから cake すると何故か使えなくて困っていました。

解決したので方法をメモしておきます。

環境

  • MAMP 2.1.3
  • PHP 5.4.10
  • CakePHP 2.4.0

事象

PHPUnitでテストしようと思ったらこんなエラーが出ていました。

$ ./lib/Cake/Console/cake test core AllTests
PHP Warning:  _cake_core_ cache was unable to write 'cake_dev_eng' to Apc cache in /var/www/line_street/lib/Cake/Cache/Cache.php on line 325
PHP Warning:  _cake_core_ cache was unable to write 'cake_console_eng' to Apc cache in /var/www/line_street/lib/Cake/Cache/Cache.php on line 325
Warning Error: include(PHPUnit/Autoload.php): failed to open stream: No such file or directory in [/var/www/line_street/lib/Cake/TestSuite/CakeTestSuiteDispatcher.php, line 150]

2013-11-24 15:36:39 Warning: include(PHPUnit/Autoload.php): failed to open stream: No such file or directory in [/var/www/line_street/lib/Cake/TestSuite/CakeTestSuiteDispatcher.php, line 150]
Warning Error: include(): Failed opening 'PHPUnit/Autoload.php' for inclusion (include_path='.:/usr/local/lib/php') in [/var/www/line_street/lib/Cake/TestSuite/CakeTestSuiteDispatcher.php, line 150]

2013-11-24 15:36:39 Warning: include(): Failed opening 'PHPUnit/Autoload.php' for inclusion (include_path='.:/usr/local/lib/php') in [/var/www/line_street/lib/Cake/TestSuite/CakeTestSuiteDispatcher.php, line 150]
Error: Please install PHPUnit framework (http://www.phpunit.de)
#0 /var/www/line_street/lib/Cake/Console/ShellDispatcher.php(208): TestShell->initialize()
#1 /var/www/line_street/lib/Cake/Console/ShellDispatcher.php(68): ShellDispatcher->dispatch()
#2 /var/www/line_street/lib/Cake/Console/cake.php(51): ShellDispatcher::run(Array)
#3 {main}
Warning Error: _cake_core_ cache was unable to write 'file_map' to Apc cache in [/var/www/line_street/lib/Cake/Cache/Cache.php, line 325]

2013-11-24 15:36:39 Warning: _cake_core_ cache was unable to write 'file_map' to Apc cache in [/var/www/line_street/lib/Cake/Cache/Cache.php, line 325]

ここで言われていることは二つ。

APC の書き込みが出来ないということと、PHPUnit をインストールしてくださいということです。

コマンドラインからの APC 有効化

app/Config/core.php のキャッシュエンジン設定はこうなってます。

$engine = 'Apc';

一応 php のパスを確認。

$ which php
/Applications/MAMP/bin/php/php5.4.10/bin/php

ちゃんと MAMP の PHP を読み込んでいるので大丈夫そうです。

次に apc_sma_info を実行してみて APC の動作確認。
今回は直接 php コマンドの -r オプションで実行して確認してみます。

$ /Applications/MAMP/bin/php/php5.4.10/bin/php -r 'var_dump(apc_sma_info());'
Warning: apc_sma_info(): No APC SMA info available.  Perhaps APC is disabled via apc.enabled? in Command line code on line 1

Call Stack:
    0.0005     226808   1. {main}() Command line code:0
    0.0005     227416   2. apc_sma_info() Command line code:1

bool(false)

false が返ってきていて、APC が無効だと言われています。
「php.ini の apc.enabled が有効になっていますか」という旨のことを言われているので確認します。

$ php -r 'phpinfo();' | grep apc.enable
apc.enable_cli => Off => Off
apc.enabled => On => On

apc.enabled はちゃんと有効でしたが、apc.enable_cliが無効になっています。
apc.enable_cli が無効だと、コマンドラインから APC を利用することが出来ません。

php.ini を編集します。

$ vi /Applications/MAMP/bin/php/php5.4.10/conf/php.ini

一番下に以下の設定を追記しました。

apc.enable_cli = 1

Apache を再起動して再度確認します。

$ php -r 'phpinfo();' | grep apc.enable
apc.enable_cli => On => On
apc.enabled => On => On

有効になりました!

PHPUnit のインストール

ついでにさっき怒られていた PHPUnit がインストールされていない件も対応します。

pear コマンドで PHPUnit のインストール先を確認しておきます。

$ /Applications/MAMP/bin/php/php5.4.10/bin/pear config-get php_dir
/Applications/MAMP/bin/php/php5.4.10/lib/php

インストールします。

$ /Applications/MAMP/bin/php/php5.4.10/lib/php upgrade-all
$ /Applications/MAMP/bin/php/php5.4.10/lib/php config-set auto_discover 1
$ /Applications/MAMP/bin/php/php5.4.10/lib/php install pear.phpunit.de/PHPUnit

エラーが出なければ OK です。
これで無事に CakePHP で PHPUnit が使えるようになりました。
今回は MAMP 環境でしたが、XAMPP でも似たような感じだと思います。

参考ドキュメント

MySQLでパーティショニング機能を試す

MySQL 5.5 から強化されたパーティショニング機能を試してみたのですが、パーティションに関する制約が多くて苦戦したので、やり方をメモしておきます。

今回やりたいこと – ログデータの肥大化を防ぐ(ログローテート)

日々溜まる膨大なログテーブルのレコードをパーティショニングして、古くなったログを削除してみます。

Cron で毎日新しいパーティションを作り、古くなったパーティション(ログ)は削除するといった感じの運用です。

レコードの削除は DELETE FROM でも出来ますが、パーティショニングを活用すると高速にレコードを削除できます!内部的には DROP TABLE と似たような動作で高速にレコードを削除しているようです。

まずはパーティションの追加から削除まで、ひと通り試してみます。

パーティション確認用のテーブルを生成

まずはログを保存する logs というテーブルを作成します。

CREATE TABLE `logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `log` varchar(255) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`,`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

このとき、パーティショニングのキーになる項目は全てユニークキーの一部になっている必要があります。そうしておかないとパーティションを作れません。

なので今回は id と created カラムを主キーにしました。

created カラムがレコード追加日を格納するカラムになります。
このカラムのデータを使ってパーティショニングしていきます。

まずは初期パーティションを作成

pmax という名前のパーティションを作ります。

ALTER TABLE logs PARTITION BY RANGE COLUMNS (`created`) (
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

パーティション振り分け条件として LESS THAN MAXVALUE を指定していますので、このパーティションに全てのレコードが追加されることになります。これだけだと意味がないですが、何かしら一つパーティションを作っておかないとパーティションの追加が出来ませんので何も考えずに作っておきます。

パーティションを追加

パーティションを再編成する REORGANIZE PARTITION 文を使ってパーティションを追加します。

先ほど作成した pmax パーティションを、 p20131114 と pmax パーティションの二つに分割してパーティションを追加します。

ALTER TABLE logs REORGANIZE PARTITION pmax INTO (
  PARTITION p20131114 VALUES LESS THAN ('2013-11-14') ENGINE = InnoDB,
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

これを毎日 Cron で実行して追加していく感じになります。

パーティション分割なんかせずに ADD PARTITION でパーティションを追加すればいいじゃんと思うかもしれないですが、既存のパーティションの後ろにしか追加できない仕様になってるので pmax パーティションがある以上は追加できないです。

なので、pmax パーティションを分割する形でパーティションを追加していきます。

試しにログレコードを挿入してパーティションごとに振り分けられるか確認してみる

この状態で試しにログデータを三件追加します。

INSERT INTO `logs` (`id`, `log`, `created`) VALUES(null, 'sample log message', '2013-11-13 00:00:00');
INSERT INTO `logs` (`id`, `log`, `created`) VALUES(null, 'sample log message', '2013-11-14 00:00:00');
INSERT INTO `logs` (`id`, `log`, `created`) VALUES(null, 'sample log message', '2013-11-15 00:00:00');

データを挿入したら各パーティションに何件のデータが格納されているか確認します。確認したいテーブル名が logs の場合 WHERE TABLE_NAME = ‘logs’ とします。

SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'logs';

上記 SQL の結果です。

TABLE_SCHEMA TABLE_NAME PARTITION_NAME PARTITION_ORDINAL_POSITION TABLE_ROWS
test_partition logs p20131114 1 1
test_partition logs pmax 2 2

p20131114 パーティションに 1レコード(2013-11-13 のレコード)、
pmax パーティションに 2レコード(2013-11-14 と 2013-11-15のレコード)が属していることが分かりました。

パーティション操作をしていて状態が分からなくなったりしたら見てみるといいと思います。

パーティションとそれに属するレコードを削除

ALTER TABLE logs DROP PARTITION p20131114;

これで created が 2013-11-14 のレコードが削除されます。こちらも Cron で毎日実行することになります。

MySQL パーティショニングのハマりどころ

  • パーティショニングキーのカラムはユニークキーの一部になっている必要がある
  • パーティションに属するレコードしか存在できない(INSERT できない)
  • パーティションを追加する場合、既にあるパーティションの後ろにしか追加できない
  • パーティションが一つもない場合 ADD PARTITION できない

主な制約としてこのような制約があります。知らない苦戦すると思います(しましたw)。なのでパーティショニングを使った運用する場合はテーブル設計の段階からよく考えて行うべきだと思います。

参考ドキュメント

MySQL のドキュメントはここらへんが参考になりました。