如何减少此SQL查询的时间
问题描述:
有人能告诉我如何减少使用此查询的时间数量?如何减少此SQL查询的时间
这是SQL查询:
SELECT
`i`.`id`,
`i`.`local_file`,
`i`.`remote_file`,
`i`.`remote_file_big`,
`i`.`image_name`,
`i`.`description`,
IF(`i`.`prevent_sync`='1', '5', `i`.`status`) `status`,
GROUP_CONCAT(`il`.`user_id` SEPARATOR ',') AS `likes`,
COUNT(`il`.`user_id`) AS `likes_count`
FROM `images` `i`
LEFT JOIN `image_likes` `il` ON (`il`.`image_id`=`i`.`id`)
WHERE 1 AND `i`.`created` < DATE_SUB(CURDATE(), INTERVAL 48 HOUR)
GROUP BY `i`.`id`
ORDER BY `likes_count` DESC LIMIT 3 OFFSET 0;
在检查查询时间,这是结果:
# Query_time: 9.948511 Lock_time: 0.000181 Rows_sent: 3 Rows_examined: 4730490
# Rows_affected: 0
表image_likes
:
id (Primary) int(11)
local_file varchar(100)
orig_name varchar(100)
remote_file varchar(1000)
remote_file_big varchar(1000)
remote_page varchar(1000)
image_name varchar(50)
image_name_eng varchar(50)
user_idIndex int(11)
author varchar(50)
credit varchar(250)
credit_eng varchar(250)
location varchar(50)
description varchar(500)
description_eng varchar(275)
notes varchar(550)
categoryIndex int(11)
date_range varchar(50)
createdIndex datetime
license enum('1', '2', '3')
status enum('0', '1', '2', '3', '4')
locked enum('0', '1')
watch_list enum('0', '1', '2')
url_title varchar(100)
url_data varchar(8192)
rem_date datetime
rem_notes varchar(500)
original_url varchar(1000)
prevent_sync enum('0', '1')
checked_by int(11)
system_recommended enum('0', '1')
请建议。
答
这对于数据库来说是一项复杂的工作,并且您可以做的事情并不多,以便真正有效地获得结果。您可以尝试使用覆盖索引的子查询来限制IO。删除一切从你的查询,你并不需要拿到3个图像ID:
SELECT i.id
FROM images i
JOIN image_likes il ON il.image_id = i.id
WHERE i.created < DATE_SUB(CURDATE(), INTERVAL 48 HOUR)
GROUP BY i.id
ORDER BY COUNT(il.image_id) DESC
LIMIT 3 OFFSET 0
最小覆盖索引是images(created, id)
和image_likes(image_id)
。如果喜欢5M,那么两个索引一起将消耗100-200 MB的内容,并且应该很容易适应内存。临时表的大小必须根据计数进行排序,也会更小。
使用该查询作为派生表(FROM子句的子查询),并加入仅从images
表中的三行:
SELECT
`i`.`id`,
`i`.`local_file`,
`i`.`remote_file`,
`i`.`remote_file_big`,
`i`.`image_name`,
`i`.`description`,
IF(`i`.`prevent_sync`='1', '5', `i`.`status`) `status`,
GROUP_CONCAT(`il`.`user_id` SEPARATOR ',') AS `likes`,
COUNT(`il`.`user_id`) AS `likes_count`
FROM (
SELECT i.id
FROM images i
JOIN image_likes il ON il.image_id = i.id
WHERE i.created < DATE_SUB(CURDATE(), INTERVAL 48 HOUR)
GROUP BY i.id
ORDER BY COUNT(il.image_id) DESC
LIMIT 3 OFFSET 0
) sub
JOIN images i ON i.id = sub.id
JOIN image_likes il ON il.image_id = i.id
GROUP BY i.id
ORDER BY likes_count;
如果不够快,你应该缓存likes_count
使用触发器。
答
这可能患有“充气 - 放气”综合征,其经常发生在JOIN
+ GROUP BY
。而且它通常会导致不正确的聚合值。
SELECT `id`, `local_file`, `remote_file`,
`remote_file_big`, `image_name`, `description`,
IF(`prevent_sync`='1', '5', `status`) `status`,
s.likes, s.likes_count
FROM `images` AS `i`
JOIN
(SELECT GROUP_CONCAT(user_id SEPARATOR ',') AS likes,
COUNT(*) AS likes_count
FROM `image_likes`
GROUP BY image_id
ORDER BY `likes_count` DESC
LIMIT 3 OFFSET 0;
) AS s ON s.`image_id`=`i`.`id`
WHERE `created` < CURDATE() - INTERVAL 2 DAY
ORDER BY `likes_count` DESC;
此变体将排除likes_count = 0的行,但这似乎是合理的。
它假设PRIMARY KEY
的images
是id
。
image_likes
需要INDEX(user_id)
并且将对该表进行一次扫描。然后只有3个查找到images
。
原始查询必须扫描images
的所有行并重复扫描所有image_likes
。
你有没有索引?您应该在'created'上添加索引 – abeyaz
发布您的表结构和您输入的索引。 – gaganshera
除了列出您的表结构和索引外,发布查询的说明以显示它是如何执行的 –