比JOIN更快的SQL查询

Question

我有一个大表格，有超过10,000行数据，并且在不久的将来它会增长到1,000,000行。我需要运行一个查询，为每个用户的每个关键字返回一个时间值。我现在有一个查询，但是由于使用了左连接和一个子查询/关键字，所以速度相当慢。

SELECT rawdata.user, t1.Facebook_Time, t2.Outlook_Time, t3.Excel_time
FROM
rawdata left join
(SELECT user, sec_to_time(SuM(time_to_sec(EndTime-StartTime))) as 'Facebook_Time'
FROM rawdata 
WHERE MainWindowTitle LIKE '%Facebook%'
GROUP by user)t1 on rawdata.user = t1.user left join
(SELECT user, sec_to_time(SuM(time_to_sec(EndTime-StartTime))) as 'Outlook_Time'
FROM rawdata 
WHERE MainWindowTitle LIKE '%Outlook%'
GROUP by user)t2 on rawdata.user = t2.user left join
(SELECT user, sec_to_time(SuM(time_to_sec(EndTime-StartTime))) as 'Excel_Time'
FROM rawdata 
WHERE MainWindowTitle LIKE '%Excel%'
GROUP by user)t3 on rawdata.user = t3.user

表格长这样：

WindowTitle | StartTime | EndTime | User
------------|-----------|---------|---------
Form1       | DateTime  | DateTime| user1
Form2       | DateTime  | DateTime| user2
...         | ...       | ...     | ...
Form_n      | DateTime  | DateTime| user_n

输出应该如下所示：

User   | Keyword   | SUM(EndTime-StartTime)
-------|-----------|-----------------------
User1  | 'Facebook'|              00:34:12
User1  | 'Outlook' |              00:12:34
User1  | 'Excel'   |              00:43:13
User2  | 'Facebook'|              00:34:12
User2  | 'Outlook' |              00:12:34
User2  | 'Excel'   |              00:43:13
...    | ...       | ...  
User_n | ...       | ...

问题是，在MySQL中最快的方式是什么？

- Pho3nixHun

---

使用 EXPLAIN 或 EXPLAIN EXTENDED 关键字来了解你的查询正在做什么。 - Adrian Cornish

你不能在列的中间索引一个模式，比如 LIKE '%Outlook%'（全文索引除外）。只要你继续使用这个条件，查询速度就会变慢。 - DCoder

@DCoder，你能看出来我不是DBA吧 - 我不会用集合思考，你的评论现在显得很明显 :-) - Adrian Cornish

感谢您的评论，我知道由于通配符的存在它变得很慢，但是它们是不可避免的。 - Pho3nixHun

Answer 1

我认为通配符搜索可能是最拖慢速度的因素，因为这些字段无法使用索引。此外，如果您可以避免使用子查询，而只是进行直接连接，可能会有所帮助，但通配符搜索要糟糕得多。是否有任何方法可以更改表格以具有categoryName或categoryID，可以具有索引并且不需要使用通配符搜索？例如，“where categoryName ='Outlook'”。

为了优化表中的数据，请添加一个categoryID（理想情况下，这将引用一个单独的表，但让我们仅在此示例中使用任意数字）。

alter table rawData add column categoryID int not null

alter table rawData add index (categoryID)

然后为现有数据填充categoryID字段：

update rawData set categoryID=1 where name like '%Outlook%'
update rawData set categoryID=2 where name like '%Facebook%'
-- etc...

接下来，根据相同规则更改您的插入操作。

然后将您的SELECT查询更改为以下内容（将通配符更改为categoryID）：

SELECT rawdata.user, t1.Facebook_Time, t2.Outlook_Time, t3.Excel_time
FROM
rawdata left join
(SELECT user, sec_to_time(SuM(time_to_sec(EndTime-StartTime))) as 'Facebook_Time'
FROM rawdata 
WHERE categoryID = 2
GROUP by user)t1 on rawdata.user = t1.user left join
(SELECT user, sec_to_time(SuM(time_to_sec(EndTime-StartTime))) as 'Outlook_Time'
FROM rawdata 
WHERE categoryID = 1
GROUP by user)t2 on rawdata.user = t2.user left join
(SELECT user, sec_to_time(SuM(time_to_sec(EndTime-StartTime))) as 'Excel_Time'
FROM rawdata 
WHERE categoryID = 3
GROUP by user)t3 on rawdata.user = t3.user

- Adam Plocher

---

嗨，谢谢回复。通配符是必要的，因为记录存储窗口标题，而其中可能会有任何内容，我只能搜索关键词。 - Pho3nixHun

然而有一些程序不需要关键词，因为我也有进程名称，但在浏览器的情况下，我需要进行更深入的调查，例如进程名称。 - Pho3nixHun

你能在插入记录时使用通配符逻辑来确定某种类别ID吗？然后对categoryID建立索引。 - Adam Plocher

在SQL中进行插入时完成这个操作可能是最规范/高效的方式。如果您能将所有子查询的“WHERE”子句更改为在索引的categoryID上搜索，那么我认为您会注意到显着的性能提升。 - Adam Plocher

好的 - 我已更新我的答案，包括步骤。希望这有所帮助！ - Adam Plocher