SELECT COUNT(*) FROM (SELECT 1 FROM xxxx)が、SELECT COUNT(*) FROM (SELECT item_id FROM xxxx)より速い理由

とあるサービスでslow logをみていたら、どこかの記事で読んだことのあるクエリがでてきたのだけど、もうちょっと速くできないものか、と考えてみた。

結論を言えば、

BEFORE:
SELECT sql_no_cache COUNT(*) FROM (SELECT item_id
 FROM category2item WHERE category_id = '2' group by item_id ) AS t;
1 row in set (2.06 sec)

AFTER:
SELECT sql_no_cache COUNT(*) FROM (SELECT 1 FROM category2item WHERE category_id = '2' group by item_id ) AS t;
1 row in set (1.89 sec)

という感じで、SELECT item_idではなく、SELECT 1とすると、ちょっとだけ早くなるようだ。
このクエリの実行結果はだいたい300万件ぐらい。

なぜこうすると早くなるのか、profilingを取ってみた。

profilingのとりかたは、

> set profiling=1;
> select * from xxx;
> show profile source;

のようにした。

BEFORE:
+---------------------------+----------+-------------------------+---------------+----
---------+
| Status                    | Duration | Source_function         | Source_file   | Source_line |
+---------------------------+----------+-------------------------+---------------+----
---------+
| starting                  | 0.000130 | NULL                    | NULL          |    
    NULL |
| checking permissions      | 0.000010 | check_access            | sql_parse.cc  |    
    4751 |
| Opening tables            | 0.000025 | open_tables             | sql_base.cc   |        4831 |
| System lock               | 0.000076 | mysql_lock_tables       | lock.cc       |         299 |
| optimizing                | 0.000013 | optimize                | sql_select.cc |         865 |
| statistics                | 0.000107 | optimize                | sql_select.cc |        1056 |
| preparing                 | 0.000033 | optimize                | sql_select.cc |        1078 |
| executing                 | 0.000011 | exec                    | sql_select.cc |        1836 |
| Sorting result            | 0.000005 | exec                    | sql_select.cc |        2267 |
| Sending data              | 0.740704 | exec                    | sql_select.cc |        2380 |
| converting HEAP to MyISAM | 0.144822 | create_myisam_from_heap | sql_select.cc |       11286 |
| Sending data              | 1.166885 | create_myisam_from_heap | sql_select.cc |       11352 |
| init                      | 0.000024 | mysql_select            | sql_select.cc |        2579 |
| optimizing                | 0.000008 | optimize                | sql_select.cc |         865 |
| executing                 | 0.000012 | exec                    | sql_select.cc |        1836 |
| end                       | 0.000011 | mysql_select            | sql_select.cc |        2615 |
| query end                 | 0.000009 | mysql_execute_command   | sql_parse.cc  |        4440 |
| closing tables            | 0.000003 | mysql_execute_command   | sql_parse.cc  |        4492 |
| removing tmp table        | 0.005282 | free_tmp_table          | sql_select.cc |       11219 |
| closing tables            | 0.000014 | free_tmp_table          | sql_select.cc |       11244 |
| freeing items             | 0.000026 | mysql_parse             | sql_parse.cc  |        5640 |
| logging slow query        | 0.000004 | log_slow_statement      | sql_parse.cc  |        1461 |
| cleaning up               | 0.000005 | dispatch_command        | sql_parse.cc  |        1417 |

AFTER:
+---------------------------+----------+-------------------------+---------------+----
---------+
| Status                    | Duration | Source_function         | Source_file   | Sou
rce_line |
+---------------------------+----------+-------------------------+---------------+----
---------+
| starting                  | 0.000074 | NULL                    | NULL          |    
    NULL |
| checking permissions      | 0.000008 | check_access            | sql_parse.cc  |    
    4751 |
| Opening tables            | 0.000018 | open_tables             | sql_base.cc   |        4831 |
| System lock               | 0.000059 | mysql_lock_tables       | lock.cc       |         299 |
| optimizing                | 0.000010 | optimize                | sql_select.cc |         865 |
| statistics                | 0.000088 | optimize                | sql_select.cc |        1056 |
| preparing                 | 0.000024 | optimize                | sql_select.cc |        1078 |
| executing                 | 0.000008 | exec                    | sql_select.cc |        1836 |
| Sorting result            | 0.000004 | exec                    | sql_select.cc |        2267 |
| Sending data              | 0.659646 | exec                    | sql_select.cc |        2380 |
| converting HEAP to MyISAM | 0.164216 | create_myisam_from_heap | sql_select.cc |       11286 |
| Sending data              | 1.052896 | create_myisam_from_heap | sql_select.cc |       11352 |
| init                      | 0.000025 | mysql_select            | sql_select.cc |        2579 |
| optimizing                | 0.000008 | optimize                | sql_select.cc |         865 |
| executing                 | 0.000013 | exec                    | sql_select.cc |        1836 |
| end                       | 0.000004 | mysql_select            | sql_select.cc |        2615 |
| query end                 | 0.000010 | mysql_execute_command   | sql_parse.cc  |        4440 |
| closing tables            | 0.000003 | mysql_execute_command   | sql_parse.cc  |        4492 |
| removing tmp table        | 0.005384 | free_tmp_table          | sql_select.cc |       11219 |
| closing tables            | 0.000014 | free_tmp_table          | sql_select.cc |       11244 |
| freeing items             | 0.000023 | mysql_parse             | sql_parse.cc  |        5640 |
| logging slow query        | 0.000003 | log_slow_statement      | sql_parse.cc  |        1461 |
| cleaning up               | 0.000005 | dispatch_command        | sql_parse.cc  |        1417 |

見比べてみると、Sending dataの部分の時間の差が基本的にはそのまま実行時間の差になっているようだ。

このクエリは、まずサブクエリが評価されて、tmp_tableへ書きこまれ、tmp_tableがMyISAMに変換されて、それを数えあげて結果を返す、という感じのようだ。
おそらく、SELECT 1の方のクエリは、tmp tableを作成するときに固定のデータをINSERTしつづけるのに対し、SELECT item_idの方はitem_idのフィールドをフェッチする処理が必要で、その時間の差が時間差につながるのかなと思われる。

一番時間かかっているのは2回目のSending dataのところで、1行しか結果がないのに1秒かかっているのは、データを数えあげる時間もここに含まれている、ということだろうか。ただ、MemoryストレージエンジンあるいはMyISAMのSELECT COUNT(*)は最適化で一瞬で数えあげができるはず*1なのだけど、何に時間がかかってるのだろうか?

このプロファイルを実行した環境はtmp_table_sizeの調整をさほどしていないが、tmp_table_sizeを大きくして、メモリストレージエンジンからMyISAMへの変換がなくなれば、もうちょっと早くなると思われる。

なお、MySQLのバージョンは5.5.30で確認しましたが、5.6とかだとまた違ったりするかもしれない。

追記:

ちょっと納得していなかったのだけど、Sending Dataが2回出現するのは、MyISAMへの変換が行なわれるときに、

  1. Memoryストレージエンジンへデータを入れる
  2. tmp_table_sizeが溢れそうなので、処理を中断する (1回目のSending Data)
  3. MyISAMへ今まで入れたデータをconvertする ( converting HEAP to MyISAM )
  4. MyISAMのテーブルへ引き続きINSERTする (2回目のSending Data)

ということのようだ。tmp_table_sizeを大きくしたら、変換がなくなり、Sending Dataは1回のみになった。

*1:Select tables optimized away