mysql - 如何使用子查询、WHERE IN 和 varchar 比较字段优化查询？

Question

我正在开展一个抓取项目，以根据不同的时间表抓取项目及其观看次数。时间表是用户定义的脚本运行时间段(日期)。

表结构如下:

CREATE TABLE IF NOT EXISTS `stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `schedule_id` smallint(11) NOT NULL,
  `type` smallint(11) NOT NULL,
  `name` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `views` int(11) NOT NULL,
  `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

所有数据都存储在表统计信息中，稍后将对其进行分析以查看 View 的类型增长情况。

数据会是这样的:

样本集

抓取是分阶段进行的，每个时间表预计会有大约 20,000 个条目。可以每天或每周制定时间表，因此数据将在 5-6 年内增长到 2-3 百万左右个月。

我需要根据这些数据执行查询，以汇总出现在所选计划范围内的相同名称。

例如:

我需要聚合跨越多个时间表的相同项目(名称)。如果选择了计划 1 和 2，则只会选择同时属于这两个计划的项目。因此这里将是 ItemA 和 ItemB。

应在此处计算按类型分类的总和。

因此对于附表 1:(已更新)

SELECT COUNT(t.`type`) AS count, SUM(t.views) AS view_count 
FROM `stats` t 
INNER JOIN 
( 
   SELECT name,COUNT(name) as c FROM `stats` WHERE `schedule_id` IN (1,2) GROUP BY name HAVING c=2
) t2 ON t2.`name` = t.`name` 
WHERE `schedule_id`=2 GROUP BY type

这是我的预期结果。

但我读过使用子查询，其中 varchar 比较字段对优化查询没有帮助。如何优化它以获得更好的性能。

相同类型聚合器的规则如下:

1.在一个schedule id下，可以有相同的names和不同的type值。schedule_id,name和type的组合不会重复。

2.Type wise aggregator - 对每种类型下的值进行求和。

我正在用 Python -MySQL 做这个项目，目的是抓取数据，用 PHP 做结果列表。我想知道如何正确组织这个表以及查询以获得更好的性能。请指教。

Answer 1

VARCHAR 列

如评论中所述，将 varchars 存储在字典表中是一种很好的做法。为什么？它们比例如 int4 需要更多空间，因此越来越大的表只会占用更多空间，而每个名称可以在另一个表中存储一次。

查询性能

WHERE IN 实际上意味着规划器确实将 schedule_id 与转换为 integer[] 的 ANY'{1,2}' 进行比较 您可以在下面注意到的类型。

子查询

如果您需要聚合数据，有时您无法避免子查询。考虑到这一点，请记住并非所有查询都包含 1 个 SELECT 语句。实际上，它们很少这样做(除非您的应用程序只有一小部分与数据库相连，例如简单的游戏，您只需要存储包含用户和点数的信息)

查询

您对给定示例数据的查询计划:

select count(type), sum(views) from tmp_test8 a join (select name,count(1) from tmp_test8 where schedule_id in (1,2) group by 1 having count(1) = 2) b
on a.name = b.name where schedule_id = 1;

                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Aggregate  (cost=23.59..23.60 rows=1 width=8)
   ->  Nested Loop  (cost=11.77..23.59 rows=1 width=8)
         Join Filter: ((a.name)::text = (tmp_test8.name)::text)
         ->  Seq Scan on tmp_test8 a  (cost=0.00..11.75 rows=1 width=524)
               Filter: (schedule_id = 1)
         ->  HashAggregate  (cost=11.77..11.79 rows=2 width=516)
               Filter: (count(1) = 2)
               ->  Seq Scan on tmp_test8  (cost=0.00..11.75 rows=2 width=516)
                     Filter: (schedule_id = ANY ('{1,2}'::integer[]))

不过，您的查询可以在没有连接的情况下重写，因此它只会扫描表一次。这是我的建议:

select count, sum(view_count) 
from( 
    select name, count(1) count, sum(case when schedule_id = 1 then views end) view_count 
    from tmp_test8 
    where schedule_id in (1,2) 
    group by 1 
    having count(1) = 2 
    ) foo 
group by 1
                               QUERY PLAN                               
------------------------------------------------------------------------
 HashAggregate  (cost=11.83..11.85 rows=2 width=16)
   ->  HashAggregate  (cost=11.78..11.80 rows=2 width=524)
         Filter: (count(1) = 2)
         ->  Seq Scan on tmp_test8  (cost=0.00..11.75 rows=2 width=524)
               Filter: (schedule_id = ANY ('{1,2}'::integer[]))

两个查询会产生相同的结果。