ここから本文です

MySQLで、データが4億5000万件入っているのですが、COUNTがやたらと遅いです。 全...

spa********さん

2017/11/2211:27:14

MySQLで、データが4億5000万件入っているのですが、COUNTがやたらと遅いです。
全体の件数を数えるだけで20分以上かかってしまいます。

そこで、全体件数をTRIGGERを使って別のテーブルに記録しておき、全体件数についてはそちらを参照することにしました。

しかし、検索条件を指定して検索したときの問題がまだ解決できていません。

結果件数によってはCOUNTの結果が遅くなります。
500件程度なら0.00秒で取得できますが、150万件を超えると取得に3秒かかります。

MySQLのCOUNTがそもそも遅いというのはわかったのですが、条件検索の結果が150万件を超えるような場合も件数を0秒で取得できるようにする工夫の方法はないでしょうか?


#テーブルはこんな感じです。
CREATE TABLE `test_table` (
`col1` int(10) unsigned NOT NULL,
`col2` int(10) unsigned NOT NULL,
PRIMARY KEY (`col1`,`col2`),
KEY `bug_id` (`col2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


#全体件数は取得に20分かかります
mysql> SELECT count(*) FROM test_table;
+-----------+
| count(*) |
+-----------+
| 455809821 |
+-----------+
1 row in set (20 min 3.62 sec)

#WHERE句で条件指定した場合、ヒット件数が少なければ0.00秒で結果を取得できます
mysql> select count(*) from test_table where col1 = 10000;
+----------+
| count(*) |
+----------+
| 356 |
+----------+
1 row in set (0.00 sec)

#ヒット件数が多いと結果の取得に3秒以上かかって困ってます
mysql> select count(*) from test_table where col1 = 20000;
+----------+
| count(*) |
+----------+
| 1717959 |
+----------+
1 row in set (3.52 sec)

#explainすると次の結果になります
mysql> explain select count(*) from test_table where col1 = 20000;
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+---------+----------+-------------+
| 1 | SIMPLE | test_table | NULL | ref | PRIMARY | PRIMARY | 4 | const | 3243214 | 100.00 | Using index |
+----+-------------+--------------+------------+------+---------------+---------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.06 sec)

閲覧数:
3,324
回答数:
3
お礼:
500枚

違反報告

ベストアンサーに選ばれた回答

プロフィール画像

カテゴリマスター

nor********さん

2017/11/2220:08:54

150万件の件数取得で3秒といのは遅すぎるとは普通言わないと思います。
インメモリDBでもないと1秒を切るのは困難では。
一応確認ですが、搭載している物理メモリはどれくらい、使っているデータエンジンがinnodbならinnodb_bufferのサイズはどれくらい、検索中のCPU使用率がどれほどで物理IOは発生しているのでしょうか。

  • 質問者

    spa********さん

    2017/11/2411:36:54

    回答ありがとうございます。

    物理メモリ 8173532 kB
    innodb_bufferサイズ 128MB
    その後、このサイズをあげてみましたが、特に変化なしでした。

    >検索中のCPU使用率がどれほどで物理IOは発生
    すみません、サーバはubuntuですが、調べ方がわかりませんでした。

    検索がメインとなるサービスなので高速検索を実現したいです。

    150万件で3秒くらいならまだ我慢できるのですが、検索結果が3000万件を超えてくると1分では取得できなくなるので、検索方法を変えられないかと思っています。今のところ、COUNT以外については不自由はないので、例えば、COUNTの結果だけをmemcachedなどに保存しておき、件数はそちらを参照するとか。

    >インメモリDB

    なるほど。調べてみます。

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

この回答は投票によってベストアンサーに選ばれました!

ベストアンサー以外の回答

1〜2件/2件中

並び替え:回答日時の
新しい順
|古い順

gor********さん

2017/11/2907:11:47

通常、大規模なテーブルを扱う場合、パーティション化するのが一般的です。
(以下参考)
https://sys-guard.com/post-12138/

また、数千万~数億件という規模のレコードを1つのテーブルに格納するという設計自体がそもそも間違っているのでは?

パーティション化するだけでもパフォーマンスは改善出来ると思いますが、もし可能であれば日時の項目を追加し、時系列でパーティション化し、古いレンジから段階的に退避していくとか、何らかの要素で単純にテーブルを分け、ロジックで制御するべきだと思います。

nii********さん

2017/11/2314:09:32

試してみないとわかりませんが、
MySQLのテーブル情報から取得してみたらどうでしょう

select table_name, table_rows from information_schema.TABLES
where table_schema = 'DB名' and table_name = 'テーブル名'
;


http://blog.goo.ne.jp/dak-ikd/e/24399c40f6ba81e266ed4c6650632336

あわせて知りたい

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる