我的例子在
MYSQL VERSION是
5.6.34日志
问题摘要,以下查询需要 40秒, ORDER_ITEM 表
和付款方式表
和提交_entry 表
作为整个表计数。
详细信息:下面:
询问。
改进。
[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] 运行代码段以HTML格式查看 EXPLAIN 的表
<!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的答复:在这两个表上根本没有外键。
UPDATE-1:
在本地上,原始查询花费 40秒
如果我只删除了,只有,则它变为 25秒节省了15秒
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秒的同一时间删除了,无法保存!
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秒
(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',
最佳答案
删除HAVING 1=1
;优化器可能不够聪明,无法忽略它。请提供EXPLAIN SELECT
(不在html中)以查看优化程序正在做什么。
在这种情况下拥有复合PK似乎是错误的:PRIMARY KEY (id, payment_type_id)
。请说明理由。
请解释status
的含义或对DOUBLE
的需求:status DOUBLE
需要花点时间弄清楚查询为什么这么慢。让我们从扔掉标准化部分开始,例如日期,事件名称和货币。这样会使查询减少到足以找到所需的行,而不是找到每行的详细信息。如果仍然很慢,让我们对其进行调试。如果是“快速”,则一个接一个地添加其他内容,以找出导致性能问题的原因。
只是id
是每个表的PRIMARY KEY
吗?还是还有更多异常(exception)(例如payment
)?
为question.var
指定一个值似乎是“错误的”,但是随后使用LEFT
暗示它是可选的。除非我在这个问题上弄错了,否则请将所有LEFT JOINs
更改为INNER JOINs
。
有任何表(也许是submission_entry
和event_date_product
)“多对多”映射表吗?如果是这样,请按照here的提示进行操作以提高性能。
当您回来时,请为每个表格提供SHOW CREATE TABLE
。
关于mysql - 分组和分组concat,不使用主pk来优化mysql查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/46510329/