MySQL

 

MySQLでgroup byした結果を高速にカウントする

MySQLでgroup byした結果を高速にカウントします。サブクエリや一時変数を使わず、インデックスを活用して高速にカウントします。大量にデータがあるテーブルに対するカウントは非常に遅いので工夫が必要です。

新サイト、tree-mapsを公開しました!!

tree-maps: 地図のWEB TOOLの事ならtree-mapsにお任せ!

地図に関するWEB TOOL専門サイトです!!

大画面で大量の緯度経度を一気にプロット、ジオコーディング、DMS<->DEGの相互変換等ができます!

◯ 広告
+--------------------------+--------------+------+-----+---------+-------+
| Field                    | Type         | Null | Key | Default | Extra |
+--------------------------+--------------+------+-----+---------+-------+
| pref_cd                  | char(2)      | NO   | PRI |         |       |
| pref_name                | text         | YES  |     | NULL    |       |
| city_cd                  | char(5)      | NO   | PRI |         |       |
| city_name                | text         | YES  |     | NULL    |       |
| town_street_cd           | char(12)     | NO   | PRI |         |       |
| town_street_name         | varchar(191) | YES  |     | NULL    |       |
| town_street_name_reverse | text         | YES  |     | NULL    |       |
| latitude                 | double       | YES  |     | NULL    |       |
| longitude                | double       | YES  |     | NULL    |       |
+--------------------------+--------------+------+-----+---------+-------+

city_cdをgroup byし、ユニークな市区町村コードが何件あるかをカウントします。

まずは誰もがやってしまう間違いです。

select
        count(*)
    from
        address
    group by
        city_cd
;
+----------+
| count(*) |
+----------+
|      982 |
|      957 |
|     1087 |
|      457 |
・・・略・・・
|       23 |
|        3 |
|       13 |
|        1 |
+----------+
1898 rows in set (0.38 sec)

count(*)したつもりが、集約結果をカウントしてしまうケース、よくありますね。

こうではなくて、1898件を取得したいのです。

select
        count(*)
    from
        (
            select
                    city_cd
                from
                    address
                group by
                    city_cd
        ) tmp
;

実行計画を見てみます。

+----+-------------+------------+-------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+------------+-------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL | NULL    | NULL | 255903 | NULL                                         |
|  2 | DERIVED     | address    | index | NULL          | idx1 | 767     | NULL | 255903 | Using index; Using temporary; Using filesort |
+----+-------------+------------+-------+---------------+------+---------+------+--------+----------------------------------------------+

サブクエリ内はインデックスが有効ですが、その後のcount(*)がフルスキャンです。ダメですね。

group byの件数が多い場合、CPU使用率が上がるでしょう。

set @cnt = 0;
select
        @cnt := @cnt + 1
    from
        (
            select
                    city_cd
                from
                    address
                group by
                    city_cd
        ) tmp
;
select @cnt;

実行計画を見てみます。

+----+-------------+------------+-------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+------------+-------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL | NULL    | NULL | 255903 | NULL                                         |
|  2 | DERIVED     | address    | index | NULL          | idx1 | 767     | NULL | 255903 | Using index; Using temporary; Using filesort |
+----+-------------+------------+-------+---------------+------+---------+------+--------+----------------------------------------------+

やはり、サブクエリ内はインデックスが有効ですが、その後のcount(*)がフルスキャンです。ダメですね。

select
        count(distinct city_cd)
    from
        address
;

実行計画を見てみます。

+----+-------------+---------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+---------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | address | index | NULL          | idx1 | 767     | NULL | 255903 | Using index |
+----+-------------+---------+-------+---------------+------+---------+------+--------+-------------+

これです!Using indexのみでカウント可能になりました。

前述のサブクエリの処理が完全になくなりました!

更にこのクエリはインデックスカバークエリ(カバリングインデックス)になっているのです。

インデックスカバークエリとは、select項目とwhere項目が全てインデックスでカバーされているクエリです。

その場合、メモリ上のデータのみを使って処理されるため、高速+CPU負荷が非常に低いのです。

◯ 広告