我有以下查询，统计每周每个区域的船只数量：
SELECT zone,
DATE_FORMAT(creation_date, '%Y%u') AS date,
COUNT(DISTINCT vessel_imo) AS vessel_count
FROM vessel_position
WHERE zone IS NOT NULL
AND creation_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY zone, date;这个表有大约4000万行。这方面的执行计划是：
+----+-------------+-----------------+------------+-------+--------------------+------+---------+------+----------+----------+------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+-------+--------------------+------+---------+------+----------+----------+------------------------------------------+
| 1 | SIMPLE | vessel_position | NULL | range | creation_date,zone | zone | 5 | NULL | 21190904 | 50.00 | Using where; Using index; Using filesort |
+----+-------------+-----------------+------------+-------+--------------------+------+---------+------+----------+----------+------------------------------------------+柱 vessel_imo , zone 以及 creation_date 每个索引。主键是复合键( vessel_imo , creation_date ).
当我查看查询配置文件时，我可以看到我花了大量时间 Creating sort index .
我能做些什么来进一步改进这个查询吗？
3条答案
按热度按时间ftf50wuq1#
假设数据一旦插入，就不会更改，然后构建并维护一个摘要表。
该表将有三列：区域、周和该周的计数。在每周开始时，只构建前一周的行（每个区域一行；跳过
NULL). 然后构建一个针对该表的查询——它将非常快，因为它将获取更少的行。与此同时
INDEX(creation_date, zone, vessel_imo)作为二级索引，将使每周任务的效率合理（大约是当前查询的52倍）。ffx8fchx2#
设置@mystartdate=date\u sub（curdate（），间隔12个月）；
选择区域，日期格式（创建日期，“%y%u”）作为日期，计数（不同的容器\u imo）作为容器计数，从容器位置创建日期>=@mystartdate和区域>0按区域，日期分组；
可能提供结果在较短的时间内，请张贴您的比较时间第二次运行的每一个（旧的和建议的）
请张贴新的解释选择…以确认创建日期的索引现在使用。
除非旧的数据被允许改变，为什么你要收集12个月的历史，1个多月前的数字是不会改变的。
6vl6ewon3#
这取决于筛选条件的选择性以及表结构。过滤条件是否选择了20%的行，5%，1%，0.1%？
如果你的答案不到5%，那么以下索引可能会有所帮助：
如果您的表有许多和/或重列，则此选项可能仍然很慢，具体取决于筛选条件的选择性。
否则，您可以尝试使用更昂贵的索引，以避免使用表并执行以下操作：
这个索引的维护成本更高——读取
insert,update,delete排成一排——但对你的孩子来说会更快select.两种选择都可以尝试，并根据您的需要选择最好的。