gpt4 book ai didi

MySQL 查询执行优化

转载 作者:行者123 更新时间:2023-11-29 19:35:09 25 4
gpt4 key购买 nike

我正在寻找一种方法来加速以下示例查询,而无需对数据进行反规范化并将task_customers列引入表任务中:

SELECT
tasks.id,
tasks.title,
COALESCE(tasksWithOnlyOneCustomer.task_customers, tasksWithMoreThanOneCustomer.task_customers) task_customers

FROM tasks

LEFT JOIN (
SELECT
tcl.task_id,
GROUP_CONCAT(CONCAT(c.first_name, ' ', c.last_name) SEPARATOR ', ') task_customers,
COUNT(tcl.customer_id) c
FROM tasks_customers_links tcl
LEFT JOIN customers c ON c.id = tcl.customer_id
WHERE c.id IS NOT NULL
GROUP BY tcl.task_id
HAVING c = 1
) tasksWithOnlyOneCustomer ON tasksWithOnlyOneCustomer.task_id = tasks.id

LEFT JOIN (
SELECT
tcl.task_id,
GROUP_CONCAT(CONCAT(c.first_name, ' ', c.last_name) SEPARATOR ', ') task_customers,
COUNT(tcl.customer_id) c
FROM tasks_customers_links tcl
LEFT JOIN customers c ON c.id = tcl.customer_id
WHERE c.id IS NOT NULL
GROUP BY tcl.task_id
HAVING c > 1
) tasksWithMoreThanOneCustomer ON tasksWithMoreThanOneCustomer.task_id = tasks.id

ORDER BY
ISNULL(tasksWithOnlyOneCustomer.task_customers) ASC, /* ORDER NULLS AT THE END */
tasksWithOnlyOneCustomer.task_customers ASC,
ISNULL(tasksWithMoreThanOneCustomer.task_customers) ASC, /* ORDER NULLS AT THE END */
tasksWithMoreThanOneCustomer.task_customers ASC

表结构如下:

CREATE TABLE tasks (
id INT NOT NULL,
title VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE customers (
id INT NOT NULL,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE tasks_customers_links (
id INT NOT NULL AUTO_INCREMENT,
task_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (task_id) REFERENCES tasks(id),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO tasks (id, title) VALUES (1, 'Wake Up!'), (2, 'Eat!'), (3, 'Sleep!');
INSERT INTO customers (id, first_name, last_name) VALUES (1, 'Homer', 'Simpson'), (2, 'Bart', 'Simpson'), (3, 'Marge', 'Simpson');
INSERT INTO tasks_customers_links (task_id, customer_id) VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2), (3, 3);

在两个表中都有超过 100k 条记录的实际情况下执行此查询时,它确实会变慢并创建临时表。因此,我正在寻找一种解决方案,以在可能的情况下加快处理速度,而无需数据非规范化。

最佳答案

select t.id,
t.title,
coalesce(group_concat(concat(c.first_name, ' ', c.last_name) separator ', '), '') as customers
from tasks t
left join tasks_customers_links l on l.task_id = t.id
left join customers c on l.customer_id = c.id
group by 1, 2
order by
count(c.id) = 0,
count(c.id)

关于MySQL 查询执行优化,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/41590753/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com