повышение производительности MySQL запросов

Question

повышение производительности MySQL запросов

У меня проблема с производительностью в запросе ниже:

SELECT t.local_branch_revenue, t.total_payment,

(SELECT SUM(IF(cpo.real_account_type = 'HQ', 0, cpo.payment_amount)) AS cpo_payment_amount
FROM customer_payment_options cpo
WHERE tran_id=t.id
AND cpo.payment_type != 'WALLET' AND cpo.payment_type != 'REWARD_CREDIT'
GROUP BY cpo.tran_id)
as cpo_payment_amount,

b.ben_firstname, b.ben_lastname
FROM transaction t
LEFT JOIN beneficiary b
ON b.id=t.ben_id
WHERE t.local_branch_id='31'
AND DATE(t.date_added) < '2016-04-07'
AND source_country_id='40'
AND t.transaction_status != 'CANCELLED'

EXPLAIN

 +----+--------------------+-------+--------+----------------------------------------+----------------------------------------+---------+-----------------+------+-------------+
| id | select_type        | table | type   | possible_keys                          | key                                    | key_len | ref             | rows | Extra       |
+----+--------------------+-------+--------+----------------------------------------+----------------------------------------+---------+-----------------+------+-------------+
| 1  | PRIMARY            | t     | ref    | local_branch_id,source_country_id      | local_branch_id                        | 5       | const           | 2    | Using where |
+----+--------------------+-------+--------+----------------------------------------+----------------------------------------+---------+-----------------+------+-------------+
| 1  | PRIMARY            | b     | eq_ref | PRIMARY                                | PRIMARY                                | 8       | mtesdb.t.ben_id | 1    |             |
+----+--------------------+-------+--------+----------------------------------------+----------------------------------------+---------+-----------------+------+-------------+
| 2  | DEPENDENT SUBQUERY | cpo   | ref    | tran_id_payment_type_real_account_type | tran_id_payment_type_real_account_type | 9       | mtesdb.t.id     | 1    | Using where |
+----+--------------------+-------+--------+----------------------------------------+----------------------------------------+---------+-----------------+------+-------------+

Как видите, он использует индексы из возможных ключей. Но все равно запрос занимает около 13 сек.

У меня тоже есть индекс transaction Таблица: (ben_id, company_id, source_country_id, date_added, tran_owner), Но это даже не входит в раздел возможных ключей.

Дайте мне знать, если вам нужно table схемы.

Что мне здесь не хватает?

1

aggregate-functions mysql performance php query-performance

Решение

Другие решения

WHERE t.local_branch_id='31'
AND DATE(t.date_added) < '2016-04-07'
AND source_country_id='40'

Измените этот тест даты просто t.date_added < '2016-04-07' ! В противном случае следующие предложения индекса не будут работать.

Что за стол source_country_id в?? Если это в tтогда вам нужно INDEX(local_branch_id, source_country_id, date_added), Если это не в t, затем INDEX(local_branch_id, date_added),

Пожалуйста предоставьте SHOW CREATE TABLE если вам нужно дальнейшее обсуждение.

0

Источник

Accepted Answer

Зависимые подзапросы не очень хорошо работают в MySQL … планировщик запросов не преобразует их эффективно в подзапросы JOINed. (Они в порядке в Oracle и SQL Server, но у кого есть на это деньги?) Итак, вам стоит реорганизовать запрос, чтобы исключить зависимый подзапрос.

Вот ваш подзапрос. Давайте сделаем рефакторинг как самостоятельный подзапрос. Мы избавимся от WHERE tran_id=t.id и переместить его позже ON пункт.

             SELECT tran_id,
SUM(IF(real_account_type = 'HQ',
0,
payment_amount)) AS cpo_payment_amount
FROM customer_payment_options
WHERE payment_type != 'WALLET'
AND payment_type != 'REWARD_CREDIT'
GROUP BY tran_id

Обратите внимание, что вы можете упростить это следующим образом — ваш IF() предложение исключает строки с real_account_type = 'HQ', Вы можете сделать это в WHERE пункт вместо.

             SELECT tran_id,
SUM(payment_amount) AS cpo_payment_amount
FROM customer_payment_options
WHERE payment_type != 'WALLET'
AND payment_type != 'REWARD_CREDIT'
AND real_account_type != 'HQ'
GROUP BY tran_id

Составной индекс по (tran_id, payment_type, real_account_type, payment_amount) может помочь этому подзапросу работать быстрее. Но присутствие этих трех != пункты гарантируют полное сканирование индекса; нет никакого способа произвольного доступа к любому индексу для тех.

Это создает виртуальную таблицу, содержащую одну строку на tran_id с суммой, которая вам нужна.

Далее нам нужно включить это в ваш основной запрос.

SELECT t.local_branch_revenue,
t.total_payment,
IFNULL(cposum.cpo_payment_amount,0) cpo_payment_amount,
b.ben_firstname, b.ben_lastname
FROM transaction t
LEFT JOIN beneficiary b ON b.id=t.ben_id
LEFT JOIN (
SELECT tran_id,
SUM(payment_amount) AS cpo_payment_amount
FROM customer_payment_options
WHERE payment_type != 'WALLET'
AND payment_type != 'REWARD_CREDIT'
AND real_account_type != 'HQ'
GROUP BY tran_id
) cposum ON t.id = cposum.tran_id
WHERE t.local_branch_id='31'
AND DATE(t.date_added) < '2016-04-07'
AND source_country_id='40'
AND t.transaction_status != 'CANCELLED'

Вы видите, как мы изменили зависимый сводный подзапрос в его собственную виртуальную таблицу? Это позволяет планировщику запросов выполнять этот запрос только один раз, а не один раз для каждой строки в основном запросе. Это очень помогает.

IFNULL() получает числовое значение для cpo_payment_amount, а не NULL, для transaction строки, не соответствующие customer_payment_options строк.

Составной индекс на transaction стол на (local_branch_id, source_country_id, date_added) поможет этот запрос; механизм запросов может получить произвольный доступ к local_branch_id а также source_country_id значения, а затем диапазон сканирования date_added значение.

Как ты научишься делать это самостоятельно? http://use-the-index-luke.com/ хорошее начало

2