mysql COUNT（）num行太慢了 [英] mysql COUNT() num rows too slow

问题描述

我正在尝试从 22m 行的表中获取用户的订阅者数量。

i am trying to get the number of subscribers of a user from a table which has 22m rows.

我的sql是以下：

SELECT
    COUNT(id)
FROM
    subscribers
WHERE
    suid=541839243781

需要 12.6020 s 来加载

但是下面相同的查询（获取用户订阅）只需要 0.0036 s 来加载（似乎没问题）

however the following same query(gets subscriptions of a user) needs only 0.0036 s to load(seems ok)

SELECT
    COUNT(uid)
FROM
    subscribers
WHERE
    uid=541839243781

我的EXPLAIN：

    id  select_type     table            type   possible_keys    key    key_len     ref     rows                 Extra
     1     SIMPLE    dvx_subscribers    index        4            4       16        NULL    22041275    Using where; Using index

SHOW CREATE TABLE：

SHOW CREATE TABLE:

    CREATE TABLE `subscribers` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `sid` bigint(20) unsigned NOT NULL,
 `uid` bigint(20) unsigned NOT NULL,
 `suid` bigint(20) unsigned NOT NULL,
 `date` datetime NOT NULL,
 KEY `id` (`id`),
 KEY `2` (`uid`,`suid`),
 KEY `4` (`suid`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=23226599 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

INDEXES：

Table   Non_unique  Key_name    Seq_in_index    Column_name     Collation   Cardinality     Sub_part    Packed  Null    Index_type  Comment
subscribers     1   id  1   id  A   NULL    NULL    NULL        BTREE   
subscribers     1   2   1   uid     A   449821  NULL    NULL        BTREE   
subscribers     1   2   2   suid    A   459193  NULL    NULL        BTREE   
subscribers     1   4   1   suid    A   6115    NULL    NULL        BTREE   
subscribers     1   4   2   id  A   22041275    NULL    NULL        BTREE   

怎么能我索引它或优化它以尽可能快地加载？因为12secs对于这个太多了......

how can i index it or optimize it to load as fast as possible? cause 12secs is hell too much for this...

推荐答案

第二个查询针对索引运行（ uid），所以它几乎是瞬间完成的。但是，第一个需要扫描表，因为没有合适的索引可供使用。在（suid，id）上创建索引以解决此问题。

The second query runs against an index (uid), so it completes nearly instantaneously. The first one, however, needs to scan the table, because there is no suitable index to use. Create an index on (suid, id) to fix this problem.

这篇关于mysql COUNT（）num行太慢了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！