分组和分组concat，不使用主pk来优化mysql查询 [英] Group by and group concat , optimization mysql query without using main pk
本文介绍了分组和分组concat，不使用主pk来优化mysql查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的例子在 MYSQL VERSION是 5.6.34-log
my example is on MYSQL VERSION is 5.6.34-log
问题摘要，以下查询需要 40秒， ORDER_ITEM 表
Problem summary the below query takes 40 seconds, ORDER_ITEM table
-
有 758423 条记录
和付款表
有 177272 条记录
和 submission_entry 表
-
有 2165698 条记录
作为整个表计数.
详细信息如下:
DETAILS HERE: BELOW:
-
我有此查询，请参考 [1]
我添加了 SQL_NO_CACHE ，用于在重新测试
时测试重复的测试
查询.
I Have added SQL_NO_CACHE for testing repeated tests when re
query.
我有优化的索引，请参考 [2] ，但没有明显意义
改善.
I Have Optimized indexes Refer to [2], but no significant
improvement.
在此处查找表结构 [3]
[1]
SELECT SQL_NO_CACHE
`payment`.`id` AS id,
`order_item`.`order_id` AS order_id,
GROUP_CONCAT(DISTINCT (CASE WHEN submission_entry.text = '' OR submission_entry.text IS NULL
THEN ' '
ELSE submission_entry.text END) ORDER BY question.var DESC SEPARATOR 0x1D) AS buyer,
event.name AS event,
COUNT(DISTINCT CASE WHEN (`order_item`.status > 0 OR (
`order_item`.status != -1 AND `order_item`.status >= -2 AND `payment`.payment_type_id != 8 AND
payment.make_order_free = 1))
THEN `order_item`.id
ELSE NULL END) AS qty,
payment.currency AS `currency`,
(SELECT SUM(order_item.sub_total)
FROM order_item
WHERE payment_id =
payment.id) AS sub_total,
CASE WHEN payment.make_order_free = 1
THEN ROUND(payment.total + COALESCE(refunds_total, 0), 2)
ELSE ROUND(payment.total, 2) END AS 'total',
`payment_type`.`name` AS payment_type,
payment_status.name AS status,
`payment_status`.`id` AS status_id,
DATE_FORMAT(CONVERT_TZ(order_item.`created`, '+0:00', '-8:00'),
'%Y-%m-%d %H:%i') AS 'created',
`user`.`name` AS 'agent',
event.id AS event_id,
payment.checked,
DATE_FORMAT(CONVERT_TZ(payment.checked_date, '+0:00', '-8:00'),
'%Y-%m-%d %H:%i') AS checked_date,
DATE_FORMAT(CONVERT_TZ(`payment`.`complete_date`, '+0:00', '-8:00'),
'%Y-%m-%d %H:%i') AS `complete date`,
`payment`.`delivery_status` AS `delivered`
FROM `order_item`
INNER JOIN `payment`
ON payment.id = `order_item`.`payment_id` AND (payment.status > 0.0 OR payment.status = -3.0)
LEFT JOIN (SELECT
sum(`payment_refund`.total) AS `refunds_total`,
payment_refunds.payment_id AS `payment_id`
FROM payment
INNER JOIN `payment_refunds` ON payment_refunds.payment_id = payment.id
INNER JOIN `payment` AS `payment_refund`
ON `payment_refund`.id = `payment_refunds`.payment_id_refund
GROUP BY `payment_refunds`.payment_id) AS `refunds` ON `refunds`.payment_id = payment.id
# INNER JOIN event_date_product ON event_date_product.id = order_item.event_date_product_id
# INNER JOIN event_date ON event_date.id = event_date_product.event_date_id
INNER JOIN event ON event.id = order_item.event_id
INNER JOIN payment_status ON payment_status.id = payment.status
INNER JOIN payment_type ON payment_type.id = payment.payment_type_id
LEFT JOIN user ON user.id = payment.completed_by
LEFT JOIN submission_entry ON submission_entry.form_submission_id = `payment`.`form_submission_id`
LEFT JOIN question ON question.id = submission_entry.question_id AND question.var IN ('name', 'email')
WHERE 1 = '1' AND (order_item.status > 0.0 OR order_item.status = -2.0)
GROUP BY `order_item`.`order_id`
HAVING 1 = '1'
ORDER BY `order_item`.`order_id` DESC
LIMIT 10
[2]
CREATE INDEX order_id
ON order_item (order_id);
CREATE INDEX payment_id
ON order_item (payment_id);
CREATE INDEX status
ON order_item (status);
第二张表
CREATE INDEX payment_type_id
ON payment (payment_type_id);
CREATE INDEX status
ON payment (status);
[3]
CREATE TABLE order_item
(
id INT AUTO_INCREMENT
PRIMARY KEY,
order_id INT NOT NULL,
form_submission_id INT NULL,
status DOUBLE DEFAULT '0' NULL,
payment_id INT DEFAULT '0' NULL
);
第二张表
CREATE TABLE payment
(
id INT AUTO_INCREMENT,
payment_type_id INT NOT NULL,
status DOUBLE NOT NULL,
form_submission_id INT NOT NULL,
PRIMARY KEY (id, payment_type_id)
);
[4] 运行代码段以查看 EXPLAIN 的HTML格式表格
[4] Run the snippet to see the table of EXPLAIN in HTML format
<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body>
<table border="1" style="border-collapse:collapse">
<tr><th>id</th><th>select_type</th><th>table</th><th>type</th><th>possible_keys</th><th>key</th><th>key_len</th><th>ref</th><th>rows</th><th>Extra</th></tr>
<tr><td>1</td><td>PRIMARY</td><td>payment_status</td><td>range</td><td>PRIMARY</td><td>PRIMARY</td><td>8</td><td>NULL</td><td>4</td><td>Using where; Using temporary; Using filesort</td></tr>
<tr><td>1</td><td>PRIMARY</td><td>payment</td><td>ref</td><td>PRIMARY,payment_type_id,status</td><td>status</td><td>8</td><td>exp_live_18092017.payment_status.id</td><td>17357</td><td></td></tr>
<tr><td>1</td><td>PRIMARY</td><td>payment_type</td><td>eq_ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.payment.payment_type_id</td><td>1</td><td></td></tr>
<tr><td>1</td><td>PRIMARY</td><td>user</td><td>eq_ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.payment.completed_by</td><td>1</td><td></td></tr>
<tr><td>1</td><td>PRIMARY</td><td>submission_entry</td><td>ref</td><td>form_submission_id,idx_submission_entry_1</td><td>form_submission_id</td><td>4</td><td>exp_live_18092017.payment.form_submission_id</td><td>2</td><td></td></tr>
<tr><td>1</td><td>PRIMARY</td><td>question</td><td>eq_ref</td><td>PRIMARY,var</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.submission_entry.question_id</td><td>1</td><td>Using where</td></tr>
<tr><td>1</td><td>PRIMARY</td><td>order_item</td><td>ref</td><td>status,payment_id</td><td>payment_id</td><td>5</td><td>exp_live_18092017.payment.id</td><td>3</td><td>Using where</td></tr>
<tr><td>1</td><td>PRIMARY</td><td>event</td><td>eq_ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.order_item.event_id</td><td>1</td><td></td></tr>
<tr><td>1</td><td>PRIMARY</td><td><derived3></td><td>ref</td><td>key0</td><td>key0</td><td>5</td><td>exp_live_18092017.payment.id</td><td>10</td><td>Using where</td></tr>
<tr><td>3</td><td>DERIVED</td><td>payment_refunds</td><td>index</td><td>payment_id,payment_id_refund</td><td>payment_id</td><td>4</td><td>NULL</td><td>1110</td><td></td></tr>
<tr><td>3</td><td>DERIVED</td><td>payment</td><td>ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.payment_refunds.payment_id</td><td>1</td><td>Using index</td></tr>
<tr><td>3</td><td>DERIVED</td><td>payment_refund</td><td>ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.payment_refunds.payment_id_refund</td><td>1</td><td></td></tr>
<tr><td>2</td><td>DEPENDENT SUBQUERY</td><td>order_item</td><td>ref</td><td>payment_id</td><td>payment_id</td><td>5</td><td>func</td><td>3</td><td></td></tr></table>
</body>
</html>
期望的Restul
必须少于5秒而不是40秒
重要 更新
1)对评论1的答复:在这两个表上根本没有外键.
1) Reply to comment 1: there is no foreign key at all on those two tables.
UPDATE-1: 在本地上，原始查询需要 40秒 如果我仅删除了 ，则它会变为 25秒，从而节省 15秒
UPDATE-1: On local the original query takes 40 seconds if i removed only the following it becomes 25 seconds saves 15 seconds
GROUP_CONCAT(DISTINCT (CASE WHEN submission_entry.text = '' OR submission_entry.text IS NULL
THEN ' '
ELSE submission_entry.text END) ORDER BY question.var DESC SEPARATOR 0x1D) AS buyer
如果我在 40秒的同一时间删除了仅，没有保存！
if I removed only its the same time around 40 seconds no save!
COUNT(DISTINCT CASE WHEN (`order_item`.status > 0 OR (
`order_item`.status != -1 AND `order_item`.status >= -2 AND `payment`.payment_type_id != 8 AND
payment.make_order_free = 1))
THEN `order_item`.id
ELSE NULL END) AS qty,
如果我仅删除了 ，则大约需要 36秒，可以节省 4秒
if I removed only it takes around 36 seconds saves 4 seconds
(SELECT SUM(order_item.sub_total)
FROM order_item
WHERE payment_id =
payment.id) AS sub_total,
CASE WHEN payment.make_order_free = 1
THEN ROUND(payment.total + COALESCE(refunds_total, 0), 2)
ELSE ROUND(payment.total, 2) END AS 'total',
推荐答案
由以下策略指导
- 将汇总预先评估到临时表上
- 将
payment放在顶部-因为这似乎是最具确定性的 - 分组联接-强制查询优化器建立表关系
- pre-evaluating agregations onto temporary tables
- placing
paymentat the top - since this seems to be the most deterministic - grouping joins - enforcing to the query optimizer the tables relationship
我提出了您的查询的修订版本:
i present a revised version of your query:
-- -----------------------------------------------------------------------------
-- Summarization of order_item
-- -----------------------------------------------------------------------------
drop temporary table if exists _ord_itm_sub_tot;
create temporary table _ord_itm_sub_tot(
primary key (payment_id)
)
SELECT
payment_id,
--
COUNT(
DISTINCT
CASE
WHEN(
`order_item`.status > 0 OR
(
`order_item`.status != -1 AND
`order_item`.status >= -2 AND
`payment`.payment_type_id != 8 AND
payment.make_order_free = 1
)
) THEN `order_item`.id
ELSE NULL
END
) AS qty,
--
SUM(order_item.sub_total) sub_total
FROM
order_item
inner join payment
on payment.id = order_item.payment_id
where order_item.status > 0.0 OR order_item.status = -2.0
group by payment_id;
-- -----------------------------------------------------------------------------
-- Summarization of payment_refunds
-- -----------------------------------------------------------------------------
drop temporary table if exists _pay_ref_tot;
create temporary table _pay_ref_tot(
primary key(payment_id)
)
SELECT
payment_refunds.payment_id AS `payment_id`,
sum(`payment_refund`.total) AS `refunds_total`
FROM
`payment_refunds`
INNER JOIN `payment` AS `payment_refund`
ON `payment_refund`.id = `payment_refunds`.payment_id_refund
GROUP BY `payment_refunds`.payment_id;
-- -----------------------------------------------------------------------------
-- Summarization of submission_entry
-- -----------------------------------------------------------------------------
drop temporary table if exists _sub_ent;
create temporary table _sub_ent(
primary key(form_submission_id)
)
select
submission_entry.form_submission_id,
GROUP_CONCAT(
DISTINCT (
CASE WHEN coalesce(submission_entry.text, '') THEN ' '
ELSE submission_entry.text
END
)
ORDER BY question.var
DESC SEPARATOR 0x1D
) AS buyer
from
submission_entry
LEFT JOIN question
ON(
question.id = submission_entry.question_id
AND question.var IN ('name', 'email')
)
group by submission_entry.form_submission_id;
-- -----------------------------------------------------------------------------
-- The result
-- -----------------------------------------------------------------------------
SELECT SQL_NO_CACHE
`payment`.`id` AS id,
`order_item`.`order_id` AS order_id,
--
_sub_ent.buyer,
--
event.name AS event,
--
_ord_itm_sub_tot.qty,
--
payment.currency AS `currency`,
--
_ord_itm_sub_tot.sub_total,
--
CASE
WHEN payment.make_order_free = 1 THEN ROUND(payment.total + COALESCE(refunds_total, 0), 2)
ELSE ROUND(payment.total, 2)
END AS 'total',
--
`payment_type`.`name` AS payment_type,
`payment_status`.`name` AS status,
`payment_status`.`id` AS status_id,
--
DATE_FORMAT(
CONVERT_TZ(order_item.`created`, '+0:00', '-8:00'),
'%Y-%m-%d %H:%i'
) AS 'created',
--
`user`.`name` AS 'agent',
event.id AS event_id,
payment.checked,
--
DATE_FORMAT(CONVERT_TZ(payment.checked_date, '+0:00', '-8:00'), '%Y-%m-%d %H:%i') AS checked_date,
DATE_FORMAT(CONVERT_TZ(payment.complete_date, '+0:00', '-8:00'), '%Y-%m-%d %H:%i') AS `complete date`,
--
`payment`.`delivery_status` AS `delivered`
FROM
`payment`
INNER JOIN(
`order_item`
INNER JOIN event
ON event.id = order_item.event_id
)
ON `order_item`.`payment_id` = payment.id
--
inner join _ord_itm_sub_tot
on _ord_itm_sub_tot.payment_id = payment.id
--
LEFT JOIN _pay_ref_tot
on _pay_ref_tot.payment_id = `payment`.id
--
INNER JOIN payment_status ON payment_status.id = payment.status
INNER JOIN payment_type ON payment_type.id = payment.payment_type_id
LEFT JOIN user ON user.id = payment.completed_by
--
LEFT JOIN _sub_ent
on _sub_ent.form_submission_id = `payment`.`form_submission_id`
WHERE
1 = 1
AND (payment.status > 0.0 OR payment.status = -3.0)
AND (order_item.status > 0.0 OR order_item.status = -2.0)
ORDER BY `order_item`.`order_id` DESC
LIMIT 10
您问题中的查询显示的聚合函数没有显式分组……这很尴尬，在我的解决方案中，我尝试设计出有意义的"聚合.
The query from your question present aggregated functions without explicit groupings... this is pretty awkward and in my solution I try to devise aggregations that 'make sense'.
请运行此版本并告诉我们您的发现.
Please, run this version and tell us your findings.
请注意，不仅要注意运行统计信息，还要注意汇总结果.
Be, please, very careful not just on the running statistics, but also on the summarization results.
这篇关于分组和分组concat，不使用主pk来优化mysql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
