MySQL で SELECT COUNT(DISTINCT) VS SELECT COUNT(*) FROM (SELECT .. GROUP BY ..) AS t

Masahiro Nagano (2012年4月 9日 17:34) | コメント( )

とあるMySQLのslowlogに残っていたところから見つけたクエリの書き換え。

サービスのどこで使われているものかまで詳しくみていないんだけど

CREATE TABLE `category2item` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned NOT NULL,
  `subcategory_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subcategory_id` (`subcategory_id`,`item_id`),
  KEY `picture_id` (`item_id`),
  KEY `category_id` (`category_id`,`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=42651972 DEFAULT CHARSET=utf8

カテゴリーとアイテムを結びつけるテーブルがあって、そこに対して、ユニークなアイテム数をカウントするクエリを実行する。

mysql> SELECT COUNT(DISTINCT item_id) FROM category2item WHERE category_id = '2';
+----------------------------+
| COUNT(DISTINCT picture_id) |
+----------------------------+
|                    2388652 |
+----------------------------+
1 row in set (3.02 sec)

件数がそれなり多いので、3秒以上掛かる。

slowlogにも多く残っていたので書き換えをしてみた。それがこれ。

mysql> SELECT COUNT(*) FROM (SELECT item_id FROM category2item WHERE category_id = '2' GROUP BY item_id) AS t;
+-------------------------+
| COUNT(DISTINCT item_id) |
+-------------------------+
|                 2388652 |
+-------------------------+
1 row in set (1.26 sec)

大体1/2の時間になった。このクエリではユニークな件数取得のために DISTINCT ではなく、GROUP BY してユニークなIDリストを取得して、その結果を COUNT(*) している。

なんでこれが速くなるのかよくわからなかったんだけど、EXPLAIN をしてみたところ

mysql> EXPLAIN SELECT COUNT(*) FROM (SELECT item_id FROM category2item WHERE category_id = '2' GROUP BY item_id) AS t;
+----+-------------+---------------+------+---------------+-------------+---------+------+---------+------------------------------+
| id | select_type | table         | type | possible_keys | key         | key_len | ref  | rows    | Extra                        |
+----+-------------+---------------+------+---------------+-------------+---------+------+---------+------------------------------+
|  1 | PRIMARY     | NULL          | NULL | NULL          | NULL        | NULL    | NULL |    NULL | Select tables optimized away |
|  2 | DERIVED     | category2item | ref  | category_id   | category_id | 4       |      | 4409520 | Using where; Using index     |
+----+-------------+---------------+------+---------------+-------------+---------+------+---------+------------------------------+
2 rows in set (1.25 sec)

Extra に「Select tables optimized away」が付いていたので、 MyISAM のクエリ最適化が行われたと予想。

そこでプロファイリングを有効にしてクエリを実行。プロファイリングについてはnippondanjiさんのblogを参考にした

漢(オトコ)のコンピュータ道: プロファイリングで快適MySQLチューニング生活

mysql> SET profiling=1;
mysql> SELECT COUNT(*) ..
mysql> SHOW PROFILE SOURCE;
+---------------------------+----------+---------+---------------+-------------+
| Status                    | Duration | Source  | Source_file   | Source_line |
+---------------------------+----------+---------+---------------+-------------+
| starting                  | 0.000041 | NULL    | NULL          |        NULL |
| Opening tables            | 0.000007 | unknown | sql_base.cc   |        4519 |
| System lock               | 0.000002 | unknown | lock.cc       |         258 |
| Table lock                | 0.000031 | unknown | lock.cc       |         269 |
| optimizing                | 0.000007 | unknown | sql_select.cc |         833 |
| statistics                | 0.000041 | unknown | sql_select.cc |        1024 |
| preparing                 | 0.000014 | unknown | sql_select.cc |        1046 |
| executing                 | 0.000004 | unknown | sql_select.cc |        1780 |
| Sorting result            | 0.000002 | unknown | sql_select.cc |        2205 |
| Sending data              | 0.510723 | unknown | sql_select.cc |        2338 |
| converting HEAP to MyISAM | 0.106450 | unknown | sql_select.cc |       10984 |
| Sending data              | 0.641679 | unknown | sql_select.cc |       11049 |
| init                      | 0.000011 | unknown | sql_select.cc |        2528 |
| optimizing                | 0.000005 | unknown | sql_select.cc |         833 |
| executing                 | 0.000011 | unknown | sql_select.cc |        1780 |
| end                       | 0.000002 | unknown | sql_select.cc |        2574 |
| query end                 | 0.000001 | unknown | sql_parse.cc  |        5082 |
| freeing items             | 0.000014 | unknown | sql_parse.cc  |        6106 |
| removing tmp table        | 0.003672 | unknown | sql_select.cc |       10916 |
| closing tables            | 0.000003 | unknown | sql_select.cc |       10941 |
| logging slow query        | 0.000002 | unknown | sql_parse.cc  |        1723 |
| cleaning up               | 0.000002 | unknown | sql_parse.cc  |        1691 |
+---------------------------+----------+---------+---------------+-------------+

22 rows in set (0.00 sec)

「converting HEAP to MyISAM」が入っているのでやはり MyISAM が使われた模様。

試しに元の COUNT(DISTINCT item_id) のプロファイルをとってみると

mysql> SHOW PROFILE SOURCE;
+--------------------+----------+------------------+---------------+-------------+
| Status             | Duration | Source_function  | Source_file   | Source_line |
+--------------------+----------+------------------+---------------+-------------+
| starting           | 0.000032 | NULL             | NULL          |        NULL |
| Opening tables     | 0.000006 | unknown function | sql_base.cc   |        4519 |
| System lock        | 0.000003 | unknown function | lock.cc       |         258 |
| Table lock         | 0.000003 | unknown function | lock.cc       |         269 |
| init               | 0.000010 | unknown function | sql_select.cc |        2528 |
| optimizing         | 0.000006 | unknown function | sql_select.cc |         833 |
| statistics         | 0.000038 | unknown function | sql_select.cc |        1024 |
| preparing          | 0.000009 | unknown function | sql_select.cc |        1046 |
| executing          | 0.000022 | unknown function | sql_select.cc |        1780 |
| Sending data       | 3.018044 | unknown function | sql_select.cc |        2338 |
| end                | 0.001905 | unknown function | sql_select.cc |        2574 |
| removing tmp table | 0.000006 | unknown function | sql_select.cc |       10916 |
| end                | 0.000003 | unknown function | sql_select.cc |       10941 |
| query end          | 0.000002 | unknown function | sql_parse.cc  |        5082 |
| freeing items      | 0.000017 | unknown function | sql_parse.cc  |        6106 |
| logging slow query | 0.000002 | unknown function | sql_parse.cc  |        1723 |
| logging slow query | 0.000030 | unknown function | sql_parse.cc  |        1733 |
| cleaning up        | 0.000003 | unknown function | sql_parse.cc  |        1691 |
+--------------------+----------+------------------+---------------+-------------+
18 rows in set (0.00 sec)

こんな感じ。「Sending data」の時間が大きくなっているところから、ユニークなIDを割り出す為のデータ走査に時間が取られたのかなと予想。

では、MyISAMに変換されなかったらどうなるかと思って、tmp_table_size を 64MB (元16MB) まであげてみると、

mysql> SELECT COUNT(*) FROM (SELECT picture_id FROM category2picture WHERE category_id = '2' GROUP BY picture_id) AS t;
+----------+
| COUNT(*) |
+----------+
|  2388710 |
+----------+
1 row in set (1.10 sec)
mysql> SHOW PROFILE SOURCE;
+--------------------+----------+------------------+---------------+-------------+
| Status             | Duration | Source_function  | Source_file   | Source_line |
+--------------------+----------+------------------+---------------+-------------+
| starting           | 0.000041 | NULL             | NULL          |        NULL |
| Opening tables     | 0.000006 | unknown function | sql_base.cc   |        4519 |
| System lock        | 0.000002 | unknown function | lock.cc       |         258 |
| Table lock         | 0.000029 | unknown function | lock.cc       |         269 |
| optimizing         | 0.000006 | unknown function | sql_select.cc |         833 |
| statistics         | 0.000037 | unknown function | sql_select.cc |        1024 |
| preparing          | 0.000014 | unknown function | sql_select.cc |        1046 |
| executing          | 0.000004 | unknown function | sql_select.cc |        1780 |
| Sorting result     | 0.000002 | unknown function | sql_select.cc |        2205 |
| Sending data       | 1.099120 | unknown function | sql_select.cc |        2338 |
| init               | 0.000012 | unknown function | sql_select.cc |        2528 |
| optimizing         | 0.000004 | unknown function | sql_select.cc |         833 |
| executing          | 0.000010 | unknown function | sql_select.cc |        1780 |
| end                | 0.000002 | unknown function | sql_select.cc |        2574 |
| query end          | 0.000002 | unknown function | sql_parse.cc  |        5082 |
| freeing items      | 0.000013 | unknown function | sql_parse.cc  |        6106 |
| removing tmp table | 0.000015 | unknown function | sql_select.cc |       10916 |
| closing tables     | 0.000002 | unknown function | sql_select.cc |       10941 |
| logging slow query | 0.000001 | unknown function | sql_parse.cc  |        1723 |
| cleaning up        | 0.001948 | unknown function | sql_parse.cc  |        1691 |
+--------------------+----------+------------------+---------------+-------------+
20 rows in set (0.00 sec)

「converting HEAP to MyISAM」がなくなって「Sending data」が1回にまとまった分、MyISAMが使われるより若干早い感じ。HEAP(MEMORY)の場合もCOUNT(*)が最適化されるんですね。8.3.1.2. WHERE Clause Optimizationにも書いてあった

まぁ、これでも1秒以上クエリに掛かるので、Webアプリケーションの中から使うのは躊躇われるところ。どこで使われているかアプリケーションのソースコード読むかー。

Amazon.co.jp ウィジェット

とあるMySQLのslowlogに残っていたところから見つけたクエリの書き換え。\n\nサービスのどこで使われているものかまで詳しくみていないんだけど\n\n CREATE TABLE `category2item` (\n `id` int(10) unsigned NOT NULL AUTO_INCREMENT,\n `category_id` int(10) unsigned NOT NULL,\n `subcategory_id` int(10) unsigned NOT NULL,\n `item_id` int(10) unsigned NOT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `subcategory_id` (`subcategory_id`,`item_id`),\n KEY `picture_id` (`item_id`),\n KEY `category_id` (`category_id`,`item_id`)\n ) ENGINE=InnoDB AUTO_INCREMENT=42651972 DEFAULT CHARSET=utf8\n\nカテゴリーとアイテムを結びつけるテーブルがあって、そこに対して、ユニークなアイテム数をカウントするクエリを実行する。\n\n mysql> SELECT COUNT(DISTINCT item_id) FROM category2item WHERE category_id = '2';\n +----------------------------+\n | COUNT(DISTINCT picture_id) |\n +----------------------------+\n | 2388652 |\n +----------------------------+\n 1 row in set (3.02 sec)\n\n件数がそれなり多いので、3秒以上掛かる。\n\nslowlogにも多く残っていたので書き換えをしてみた。それがこれ。\n\n mysql> SELECT COUNT(*) FROM (SELECT item_id FROM category2item WHERE category_id = '2' GROUP BY item_id) AS t;\n +-------------------------+\n | COUNT(DISTINCT item_id) |\n +-------------------------+\n | 2388652 |\n +-------------------------+\n 1 row in set (1.26 sec)\n\n大体1/2の時間になった。このクエリではユニークな件数取得のために DISTINCT ではなく、GROUP BY してユニークなIDリストを取得して、その結果を COUNT(*) している。\n\nなんでこれが速くなるのかよくわからなかったんだけど、EXPLAIN をしてみたところ\n\n mysql> EXPLAIN SELECT COUNT(*) FROM (SELECT item_id FROM category2item WHERE category_id = '2' GROUP BY item_id) AS t;\n +----+-------------+---------------+------+---------------+-------------+---------+------+---------+------------------------------+\n | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |\n +----+-------------+---------------+------+---------------+-------------+---------+------+---------+------------------------------+\n | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |\n | 2 | DERIVED | category2item | ref | category_id | category_id | 4 | | 4409520 | Using where; Using index |\n +----+-------------+---------------+------+---------------+-------------+---------+------+---------+------------------------------+\n 2 rows in set (1.25 sec)\n\nExtra に「Select tables optimized away」が付いていたので、 MyISAM のクエリ最適化が行われたと予想。\n\nそこでプロファイリングを有効にしてクエリを実行。プロファイリングについてはnippondanjiさんのblogを参考にした\n\n[漢(オトコ)のコンピュータ道: プロファイリングで快適MySQLチューニング生活](http://nippondanji.blogspot.jp/2009/02/mysql.html)\n\n mysql> SET profiling=1;\n mysql> SELECT COUNT(*) ..\n mysql> SHOW PROFILE SOURCE;\n +---------------------------+----------+---------+---------------+-------------+\n | Status | Duration | Source | Source_file | Source_line |\n +---------------------------+----------+---------+---------------+-------------+\n | starting | 0.000041 | NULL | NULL | NULL |\n | Opening tables | 0.000007 | unknown | sql_base.cc | 4519 |\n | System lock | 0.000002 | unknown | lock.cc | 258 |\n | Table lock | 0.000031 | unknown | lock.cc | 269 |\n | optimizing | 0.000007 | unknown | sql_select.cc | 833 |\n | statistics | 0.000041 | unknown | sql_select.cc | 1024 |\n | preparing | 0.000014 | unknown | sql_select.cc | 1046 |\n | executing | 0.000004 | unknown | sql_select.cc | 1780 |\n | Sorting result | 0.000002 | unknown | sql_select.cc | 2205 |\n | Sending data | 0.510723 | unknown | sql_select.cc | 2338 |\n | converting HEAP to MyISAM | 0.106450 | unknown | sql_select.cc | 10984 |\n | Sending data | 0.641679 | unknown | sql_select.cc | 11049 |\n | init | 0.000011 | unknown | sql_select.cc | 2528 |\n | optimizing | 0.000005 | unknown | sql_select.cc | 833 |\n | executing | 0.000011 | unknown | sql_select.cc | 1780 |\n | end | 0.000002 | unknown | sql_select.cc | 2574 |\n | query end | 0.000001 | unknown | sql_parse.cc | 5082 |\n | freeing items | 0.000014 | unknown | sql_parse.cc | 6106 |\n | removing tmp table | 0.003672 | unknown | sql_select.cc | 10916 |\n | closing tables | 0.000003 | unknown | sql_select.cc | 10941 |\n | logging slow query | 0.000002 | unknown | sql_parse.cc | 1723 |\n | cleaning up | 0.000002 | unknown | sql_parse.cc | 1691 |\n +---------------------------+----------+---------+---------------+-------------+\n 22 rows in set (0.00 sec)\n\n「converting HEAP to MyISAM」が入っているのでやはり MyISAM が使われた模様。\n\n試しに元の COUNT(DISTINCT item\\_id) のプロファイルをとってみると\n\n mysql> SHOW PROFILE SOURCE;\n +--------------------+----------+------------------+---------------+-------------+\n | Status | Duration | Source_function | Source_file | Source_line |\n +--------------------+----------+------------------+---------------+-------------+\n | starting | 0.000032 | NULL | NULL | NULL |\n | Opening tables | 0.000006 | unknown function | sql_base.cc | 4519 |\n | System lock | 0.000003 | unknown function | lock.cc | 258 |\n | Table lock | 0.000003 | unknown function | lock.cc | 269 |\n | init | 0.000010 | unknown function | sql_select.cc | 2528 |\n | optimizing | 0.000006 | unknown function | sql_select.cc | 833 |\n | statistics | 0.000038 | unknown function | sql_select.cc | 1024 |\n | preparing | 0.000009 | unknown function | sql_select.cc | 1046 |\n | executing | 0.000022 | unknown function | sql_select.cc | 1780 |\n | Sending data | 3.018044 | unknown function | sql_select.cc | 2338 |\n | end | 0.001905 | unknown function | sql_select.cc | 2574 |\n | removing tmp table | 0.000006 | unknown function | sql_select.cc | 10916 |\n | end | 0.000003 | unknown function | sql_select.cc | 10941 |\n | query end | 0.000002 | unknown function | sql_parse.cc | 5082 |\n | freeing items | 0.000017 | unknown function | sql_parse.cc | 6106 |\n | logging slow query | 0.000002 | unknown function | sql_parse.cc | 1723 |\n | logging slow query | 0.000030 | unknown function | sql_parse.cc | 1733 |\n | cleaning up | 0.000003 | unknown function | sql_parse.cc | 1691 |\n +--------------------+----------+------------------+---------------+-------------+\n 18 rows in set (0.00 sec)\n\nこんな感じ。「Sending data」の時間が大きくなっているところから、ユニークなIDを割り出す為のデータ走査に時間が取られたのかなと予想。\n\nでは、MyISAMに変換されなかったらどうなるかと思って、tmp\\_table\\_size を 64MB (元16MB) まであげてみると、\n\n mysql> SELECT COUNT(*) FROM (SELECT picture_id FROM category2picture WHERE category_id = '2' GROUP BY picture_id) AS t;\n +----------+\n | COUNT(*) |\n +----------+\n | 2388710 |\n +----------+\n 1 row in set (1.10 sec)\n mysql> SHOW PROFILE SOURCE;\n +--------------------+----------+------------------+---------------+-------------+\n | Status | Duration | Source_function | Source_file | Source_line |\n +--------------------+----------+------------------+---------------+-------------+\n | starting | 0.000041 | NULL | NULL | NULL |\n | Opening tables | 0.000006 | unknown function | sql_base.cc | 4519 |\n | System lock | 0.000002 | unknown function | lock.cc | 258 |\n | Table lock | 0.000029 | unknown function | lock.cc | 269 |\n | optimizing | 0.000006 | unknown function | sql_select.cc | 833 |\n | statistics | 0.000037 | unknown function | sql_select.cc | 1024 |\n | preparing | 0.000014 | unknown function | sql_select.cc | 1046 |\n | executing | 0.000004 | unknown function | sql_select.cc | 1780 |\n | Sorting result | 0.000002 | unknown function | sql_select.cc | 2205 |\n | Sending data | 1.099120 | unknown function | sql_select.cc | 2338 |\n | init | 0.000012 | unknown function | sql_select.cc | 2528 |\n | optimizing | 0.000004 | unknown function | sql_select.cc | 833 |\n | executing | 0.000010 | unknown function | sql_select.cc | 1780 |\n | end | 0.000002 | unknown function | sql_select.cc | 2574 |\n | query end | 0.000002 | unknown function | sql_parse.cc | 5082 |\n | freeing items | 0.000013 | unknown function | sql_parse.cc | 6106 |\n | removing tmp table | 0.000015 | unknown function | sql_select.cc | 10916 |\n | closing tables | 0.000002 | unknown function | sql_select.cc | 10941 |\n | logging slow query | 0.000001 | unknown function | sql_parse.cc | 1723 |\n | cleaning up | 0.001948 | unknown function | sql_parse.cc | 1691 |\n +--------------------+----------+------------------+---------------+-------------+\n 20 rows in set (0.00 sec)\n\n「converting HEAP to MyISAM」がなくなって「Sending data」が1回にまとまった分、MyISAMが使われるより若干早い感じ。HEAP(MEMORY)の場合もCOUNT(*)が最適化されるんですね。[8.3.1.2. WHERE Clause Optimization](http://dev.mysql.com/doc/refman/5.1/en/where-optimizations.html)にも書いてあった\n\n\nまぁ、これでも1秒以上クエリに掛かるので、Webアプリケーションの中から使うのは躊躇われるところ。どこで使われているかアプリケーションのソースコード読むかー。

comments powered by Disqus.

このページは、Masahiro Naganoが2012年4月 9日 17:34に書いたブログ記事です。

ひとつ前のブログ記事は「「サーバ/インフラエンジニア養成読本管理/監視編」に寄稿しました」です。

次のブログ記事は「CloudForecast で Redis の監視」です。

最近のコンテンツはインデックスページで見られます。過去に書かれたものはアーカイブのページで見られます。

MySQL で SELECT COUNT(DISTINCT) VS SELECT COUNT(*) FROM (SELECT .. GROUP BY ..) AS t

検索

このブログ記事について

月別アーカイブ

ウェブページ

MySQL で SELECT COUNT(DISTINCT) VS SELECT COUNT(*) FROM (SELECT .. GROUP BY ..) AS t

検索

このブログ記事について

月別 アーカイブ

ウェブページ

月別アーカイブ