首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >mysql -按索引列分组+按索引列分组导致速度下降

问mysql -按索引列分组+按索引列分组导致速度下降
EN

Stack Overflow用户
提问于 2012-01-08 14:08:45
回答 3查看 623关注 0票数 1

我有带有下一个结构的表statistics:

代码语言:javascript
复制
+-------------------+----------------------+------+-----+---------+----------------+
| Field             | Type                 | Null | Key | Default | Extra          |
+-------------------+----------------------+------+-----+---------+----------------+
| id                | int(11)              | NO   | PRI | NULL    | auto_increment |
| created_at        | datetime             | YES  | MUL | NULL    |                |
| year_in_tz        | smallint(5) unsigned | YES  | MUL | NULL    |                |
| month_in_tz       | tinyint(3) unsigned  | YES  | MUL | NULL    |                |
+-------------------+----------------------+------+-----+---------+----------------+

created_at、year_in_tz、month_in_tz和on (year_in_tz,month_in_tz)上的键:

代码语言:javascript
复制
 ALTER TABLE `statistics` ADD INDEX created_at (created_at);
 alter table statistics add index year_in_tz (year_in_tz);
 alter table statistics add index month_in_tz (month_in_tz);
 alter table statistics add index year_month_in_tz(year_in_tz,month_in_tz);

一些疑问--例如..。

代码语言:javascript
复制
mysql> SELECT COUNT(*) AS count_all, year_in_tz, month_in_tz 
       FROM `statistics` 
       GROUP BY year_in_tz, month_in_tz;
+-----------+------------+-------------+
| count_all | year_in_tz | month_in_tz |
+-----------+------------+-------------+
|    467890 |       2011 |          11 |
|   7339389 |       2011 |          12 |
+-----------+------------+-------------+
2 rows in set (5.04 sec)  

mysql> describe SELECT COUNT(*) AS count_all, year_in_tz, month_in_tz FROM `statistics` GROUP BY year_in_tz, month_in_tz;
 +----+-------------+--------------------+-------+---------------+------------------+---------+------+---------+-------------+
 | id | select_type | table              | type  | possible_keys | key              | key_len | ref  | rows    | Extra       |
 +----+-------------+--------------------+-------+---------------+------------------+---------+------+---------+-------------+
 |  1 | SIMPLE      | statistics         | index | NULL          | year_month_in_tz | 5       | NULL | 7797984 | Using index |
 +----+-------------+--------------------+-------+---------------+------------------+---------+------+---------+-------------+
 1 row in set (0.01 sec)

 mysql> SELECT COUNT(*) AS count_all, year_in_tz, month_in_tz 
        FROM `statistics` 
        WHERE (created_at BETWEEN '2011-10-31 20:00:00' AND '2011-12-31 19:59:59') 
        GROUP BY year_in_tz, month_in_tz;
 +-----------+------------+-------------+
 | count_all | year_in_tz | month_in_tz |
 +-----------+------------+-------------+
 |    467890 |       2011 |          11 |
 |   7339389 |       2011 |          12 |
 +-----------+------------+-------------+
 2 rows in set (1 min 33.46 sec)

 mysql> describe SELECT COUNT(*) AS count_all, year_in_tz, month_in_tz FROM `statistics` WHERE (created_at BETWEEN '2011-10-31 20:00:00' AND '2011-12-31 19:59:59') GROUP BY year_in_tz, month_in_tz;
 +----+-------------+--------------------+-------+---------------+------------------+---------+------+---------+-------------+
 | id | select_type | table              | type  | possible_keys | key              | key_len | ref  | rows    | Extra       |
 +----+-------------+--------------------+-------+---------------+------------------+---------+------+---------+-------------+
 |  1 | SIMPLE      | statistics         | index | created_at    | year_month_in_tz | 5       | NULL | 7797984 | Using where |
 +----+-------------+--------------------+-------+---------------+------------------+---------+------+---------+-------------+
 1 row in set (0.07 sec)

因此,如果我在索引列+按索引列分组上使用where语句with子句,则速度非常低。也许有人知道如何改进上一次查询,使其更快。

P.S.在使用索引之后,我发现新的索引(created_at、year_in_tz、month_in_tz)使查询运行得更快,但我希望每次查询的时间为0到1秒,而不是10秒:

代码语言:javascript
复制
alter table lending_statistics add index created_at_with_year_and_month_in_tz (created_at,year_in_tz,month_in_tz);

mysql> describe SELECT COUNT(*) AS count_all, year_in_tz, month_in_tz FROM `statistics`        WHERE (created_at BETWEEN '2011-10-31 20:00:00' AND '2011-12-31 19:59:59') GROUP BY year_in_tz, month_in_tz;
+----+-------------+--------------------+-------+-------------------------------------------------+--------------------------------------+---------+------+---------+-----------------------------------------------------------+
| id | select_type | table              | type  | possible_keys                                   | key                                  | key_len | ref  | rows    | Extra                                                     |
+----+-------------+--------------------+-------+-------------------------------------------------+--------------------------------------+---------+------+---------+-----------------------------------------------------------+
|  1 | SIMPLE      | statistics         | range | created_at,created_at_with_year_and_month_in_tz | created_at_with_year_and_month_in_tz | 9       | NULL | 3612208 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------------------+-------+-------------------------------------------------+--------------------------------------+---------+------+---------+-----------------------------------------------------------+

一排设置(0.05秒)

代码语言:javascript
复制
mysql> SELECT COUNT(*) AS count_all,        year_in_tz, month_in_tz        FROM `lending_statistics`        WHERE (created_at BETWEEN '2011-10-31 20:00:00' AND '2011-12-31 19:59:59')        GROUP BY year_in_tz, month_in_tz;
+-----------+------------+-------------+
| count_all | year_in_tz | month_in_tz |
+-----------+------------+-------------+
|    467890 |       2011 |          11 |
|   7339389 |       2011 |          12 |
+-----------+------------+-------------+
2 rows in set (10.62 sec)
EN

回答 3

Stack Overflow用户

发布于 2012-01-10 03:44:12

将字段ID添加到索引created_at_with_year_and_month_in_tz,然后将select语句更改为使用

代码语言:javascript
复制
select count(id) ....

在MySQL 5.6中,国际比较方案的特性在这种情况下可能有所帮助,因为访问的所有字段都是索引的一部分。我相信当您指定count(*)时,MySQL可能会读取实际的数据记录,因此它需要读取索引文件以及数据文件。

票数 1
EN

Stack Overflow用户

发布于 2012-01-08 14:19:27

尝试一下,有一个带有日期时间索引的已知的MySQL问题

代码语言:javascript
复制
    WHERE
        created_at BETWEEN 
               CAST('2011-10-31 20:00:00' AS datetime) AND 
               CAST('2011-12-31 19:59:59'  AS datetime)
票数 0
EN

Stack Overflow用户

发布于 2012-01-08 15:36:53

缓慢的COUNT(*)查询常常是MySQL & PostgreSQL (和其他关系数据库管理系统)的麻烦,因为后续表扫描是在查询执行过程中执行的。尝试考虑将聚合数据缓存到其他地方:梅卡奇、雷迪斯等。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/8778210

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档