作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有一个查询正在读取大约 240 万行数据。
查询本身运行良好,但 ORDER BY 子句导致性能问题。如果我删除 ORDER BY,则查询需要 0.03 秒才能执行。使用 ORDER BY 可能需要 4.5 到 5 秒。
我是否可以进一步优化这个查询?已添加索引,因此这不是解决方案。
编辑 1 -该查询是一个更大的 PDO 查询的缩短版本,因此我认为联接是必要的。您可以在本文底部看到主要查询。
SELECT t.processing_time, t.paymentType, t.status, t.merchantTransactionId, t.paymentBrand, t.amount, t.currency, t.code, t.holder, t.bin, t.last4Digits, t.recurringType, m.name AS merchant, c.name AS channel, concat(UPPER(SUBSTRING(trim(sp.status_description),1,1)), lower(SUBSTRING(trim(sp.status_description),2))) as status_description
FROM transactionsV2 t
JOIN channels c
ON t.entityId = c.uuid
JOIN merchants m
ON m.uuid = c.sender
JOIN status_payments sp
ON t.code = sp.status_code
JOIN (
SELECT t.id, t.processing_time FROM transactionsV2 t
JOIN channels c ON t.entityId = c.uuid
JOIN merchants m ON m.uuid = c.sender
WHERE (t.processing_time >= "2018-11-08 00:00:00")
AND (t.processing_time <= "2018-11-12 23:59:59")
ORDER BY t.processing_time DESC
LIMIT 1000
) t2
ON t.id = t2.id
WHERE t.status = 1
$transactions = DB::connection('mysql2')->select(DB::raw("SELECT t.processing_time, t.paymentType, t.status, t.merchantTransactionId, t.paymentBrand, t.amount, t.currency, t.code, t.holder, t.bin, t.last4Digits, t.recurringType, m.name AS merchant, c.name AS channel, concat(UPPER(SUBSTRING(trim(sp.status_description),1,1)), lower(SUBSTRING(trim(sp.status_description),2))) as status_description
FROM transactionsV2 t
JOIN channels c
ON t.entityId = c.uuid
JOIN merchants m
ON m.uuid = c.sender
JOIN status_payments sp
ON t.code = sp.status_code
JOIN (
SELECT t.id, t.processing_time FROM transactionsV2 t
JOIN channels c ON t.entityId = c.uuid
JOIN merchants m ON m.uuid = c.sender
WHERE (t.processing_time >= :insTs1)
AND (t.processing_time <= :insTs2)
AND (:merchant1 IS NULL OR m.name LIKE :merchant2)
AND (:channel1 IS NULL OR c.name LIKE :channel2)
ORDER BY t.processing_time DESC
LIMIT 1000
) t2
ON t.id = t2.id
WHERE (:status1 IS NULL OR t.status = :status2)
AND (:holder1 IS NULL OR holder LIKE :holder2)
AND (:paymentType1 IS NULL OR t.paymentType IN (".$paymentType."))
AND (:merchantTransactionId1 IS NULL OR merchantTransactionId LIKE :merchantTransactionId2)
AND (:paymentBrand1 IS NULL OR paymentBrand LIKE :paymentBrand2)
AND (:amount1 IS NULL OR amount = :amount2)
AND (:recurringType1 IS NULL OR t.recurringType = :recurringType2)"),
['status1' => $search->searchCriteria['status'],
'status2' => $search->searchCriteria['status'],
'holder1' => $search->searchCriteria['holder'],
'holder2' => '%'.$search->searchCriteria['holder'].'%',
'paymentType1' => $paymentType,
'merchantTransactionId1' => $search->searchCriteria['merchantTransactionId'],
'merchantTransactionId2' => '%'.$search->searchCriteria['merchantTransactionId'].'%',
'paymentBrand1' => $search->searchCriteria['paymentBrand'],
'paymentBrand2' => '%'.$search->searchCriteria['paymentBrand'].'%',
'amount1' => $search->searchCriteria['amount'],
'amount2' => $search->searchCriteria['amount'],
'recurringType1' => $search->searchCriteria['recurringType'],
'recurringType2' => $search->searchCriteria['recurringType'],
'merchant1' => $search->searchCriteria['merchant'],
'merchant2' => '%'.$search->searchCriteria['merchant'].'%',
'channel1' => $search->searchCriteria['channel'],
'channel2' => '%'.$search->searchCriteria['channel'].'%',
'insTs1' => $search->searchCriteria['fromDate'] . ' 00:00:00',
'insTs2' => $search->searchCriteria['toDate'] . ' 23:59:59']);
最佳答案
也许我遗漏了一些东西,但我没有看到子查询需要join
。这够了吗?
SELECT t.id, t.processing_time
FROM transactionsV2 t
WHERE t.processing_time >= '2018-11-08' AND
t.processing_time <= '2018-11-13'
ORDER BY t.processing_time DESC
LIMIT 1000
如果是这样,transactionsV2(processing_time)
上的索引会有所帮助(假设它不是 View )。
关于mysql - 如何使用 ORDER BY 子句提高性能,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/53262164/
我是一名优秀的程序员,十分优秀!