从繁忙的表运行报告并避免锁定这些表

Question

我有许多报告涉及大型数据集上的联接。这些表每秒被写入多次。我的cronjobs在影响最小的时间运行查询，但我仍然担心用它们锁定表会损害性能。

这里有一个简单的例子，他们今天要求一次性使用。它显示了RIIA报告的播放时间：

SELECT  
    date_format(p.`played`, '%Y-%m') as `month`,
    SUM(TIME_TO_SEC(s.`length`))/3600 as `playtime`
INTO OUTFILE "/tmp/120313_playtime.csv"
FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n'
FROM 
    `plays` p,
    `songs` s
GROUP BY `month`

我该如何构建它，以避免在查询运行时给广播应用程序写入播放表带来问题？我应该创建临时表并复制活动表吗？

//根据请求编辑EXPLAIN输出

+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
|  1 | SIMPLE      | s     | ALL  | NULL          | NULL | NULL    | NULL |    3909 | Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ALL  | NULL          | NULL | NULL    | NULL | 4040933 | Using join buffer               |
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
CREATE TABLE `plays` (
  `play_id` int(11) NOT NULL auto_increment,
  `song_id` int(11) NOT NULL,
  `played` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`play_id`)
) ENGINE=MyISAM AUTO_INCREMENT=4040992 DEFAULT CHARSET=latin1 COMMENT='play counts for songs' AUTO_INCREMENT=4040992 ;

CREATE TABLE `songs` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `artist_id` int(11) NOT NULL,
  `length` time NOT NULL,
  `album_id` int(11) NOT NULL,
  `active` tinyint(4) NOT NULL,
  `tracknum` varchar(16) NOT NULL,
  `bitrate` varchar(32) NOT NULL,
  `date_created` datetime NOT NULL,
  `date_modified` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4136 DEFAULT CHARSET=latin1 AUTO_INCREMENT=4136 ;

Answer 1

脑海中浮现的只有两件事。。。第一，戏剧和歌曲之间没有"JOIN"，这将导致笛卡尔乘积。其次，添加WHERE子句，我希望"播放"列是日期/时间，这样您就可以查询所有记录<NOW（），所以如果在查询运行时添加了任何，它们将被排除在外。由于看起来你是按月进行的，你甚至可以创建一个单独的表，只不过是按月份和年份分组的每个"时间段"的运行总数，这样你就不必担心超长的查询了。然后，你可以在有问题的当月跑步。。。仍然小于NOW（）。