mysql - select count(1) 统计太慢，怎么优化？

Question

我有一张表400w+数据，单表查询都很慢，当关联时就更慢了。 请问还有什么可以优化的方法吗？

mysql> select count(1) from test;
count(1)
+----------+
4429754

1 row in set (1.39 sec)

单表统计需要这么长时间。按时间段查询，也一样很慢。

mysql> select count(1) from test f where f.createdDate >= '2015-01-01 00:00:00' and f.createdDate <= '2015-12-15 23:59:59';
count(1)
+----------+
2584387

1 row in set (1.08 sec)

createdDate是datetime类型，我按年份分区过。
ALTER TABLE test PARTITION BY RANGE(to_days(createdDate))(
PARTITION p20120101 VALUES LESS THAN (to_days('2012-01-01 00:00:00')),
PARTITION p20130101 VALUES LESS THAN (to_days('2013-01-01 00:00:00')),
PARTITION p20140101 VALUES LESS THAN (to_days('2014-01-01 00:00:00')),
PARTITION p20150101 VALUES LESS THAN (to_days('2015-01-01 00:00:00')),
PARTITION p20160101 VALUES LESS THAN (to_days('2016-01-01 00:00:00')),
PARTITION p20170101 VALUES LESS THAN (to_days('2017-01-01 00:00:00'))
);

请问还有什么可以优化的吗？

Answer 1

Your table is partitioned by year, and the query time condition is also a whole year, so although the partitioning is done, there is still a lot of data in this partition.
Does your query business need to have such a large span? Yes Optimize the partitioning method according to your actual query span?
If there is indeed a large amount of data and it is existing data, you can consider directly putting some dimension statistics in the database, and later query the results directly without dynamic statistics.