如何使用JOIN优化MySQL查询？
如何使用JOIN优化MySQL查询？
提问于 2022-01-30 12:27:30
我有两个表:显示(横幅显示)，点击(横幅点击)。
CREATE TABLE `shows` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`data` text NOT NULL,
`created_at` date NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `clicks` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`show_id` int(11) unsigned NOT NULL,
`data` text NOT NULL,
`created_at` date NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `created_at` (`created_at`),
KEY `show_constaraint_idx` (`show_id`),
CONSTRAINT `show_constaraint` FOREIGN KEY (`show_id`) REFERENCES `shows` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;每张表格都有40000份记录。
我在表中增加了40000条记录：
drop procedure if exists doWhile;
DELIMITER //
CREATE PROCEDURE doWhile()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= 40000000) DO
INSERT INTO `shows` (data, created_at) values (
CONCAT(MD5(RAND()), MD5(RAND())),
FROM_UNIXTIME(unix_timestamp('2021-01-01') + floor(rand() * (unix_timestamp('2021-12-31') - unix_timestamp('2021-01-01') + 1)))
);
SET i = i + 1;
END WHILE;
END;
//
CALL doWhile();我在单击表中添加了40000条记录：
drop procedure if exists doWhile;
DELIMITER //
CREATE PROCEDURE doWhile()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= 40000000) DO
INSERT INTO `clicks` (show_id, data, created_at) values (
(FLOOR(1 + RAND() * 40000000)),
CONCAT(MD5(RAND()), MD5(RAND())),
FROM_UNIXTIME(unix_timestamp('2021-01-01') + floor(rand() * (unix_timestamp('2021-12-31') - unix_timestamp('2021-01-01') + 1)))
);
SET i = i + 1;
END WHILE;
END;
//
CALL doWhile();SQL查询执行时间为10秒：
SELECT shows.id,
shows.data,
clicks.id,
clicks.data
FROM clicks
INNER JOIN shows ON shows.id = clicks.show_id
WHERE shows.created_at = '2021-03-03'
AND clicks.created_at >= '2021-03-03'
AND clicks.created_at <= '2021-03-06'我试图优化SQL查询，但SQL查询执行时间是10秒：
SELECT sub_shows.id,
sub_shows.data,
sub_clicks.id,
sub_clicks.data
FROM (
SELECT clicks.id,
clicks.show_id,
clicks.data
FROM clicks
WHERE clicks.created_at >= '2021-03-03' AND clicks.created_at <= '2021-03-06'
) as sub_clicks
INNER JOIN (
SELECT shows.id,
shows.data
FROM shows
WHERE shows.created_at = '2021-03-03'
) as sub_shows ON sub_shows.id = sub_clicks.show_id对这两种查询解释相同：
如何优化查询？
答案:将innodb_buffer_pool_size改为12G (Rick )
回答 1
Stack Overflow用户
发布于 2022-01-30 14:09:33
最好有一个复合索引，涵盖连接标准的两个部分和个人。
Suggest Indexes as listed
Shows ( created_at, id )
Clicks ( show_id, created_at )这两个键提供帮助的原因是索引使用了这两个部分，而不必转到原始数据页来限定记录。只有在限定之后，才需要检索结果集中的其他列。
我还会调整并将查询反转到最小的细粒度项..。在主查询位置的单一日期上显示表。
SELECT
shows.id,
shows.data,
c.id,
c.data
FROM
shows s
JOIN clicks c
ON s.id = c.show_id
AND c.created_at >= '2021-03-03'
AND c.created_at <= '2021-03-06'
WHERE
s.created_at = '2021-03-03'页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/70914627
复制相关文章
相似问题
腾讯云开发者
