Поиск узких мест в запросе (производительностьd MySQL)

[yaapelsinko]

Значит так. Сделал базу, запросы, все дела. Проиндексировал основные поля (все внутренние и внешние ключи, а также даты, так как по ним делаю выборки).

Один из больших запросов у меня ускорился с 250 до 1 секунды.
Второй тоже стал побыстрее, но 500 секунд, даже по сравнению с тем, что было...
Проблемный запрос в следующем сообщении, тут не лезет - большой.

Я грешу на то, что у меня используются подзапросы, в которых mysql не может использовать индекс:

SELECT SUM(SUM * (TKO.MAX / 100)) 
  FROM PAYOUT_TEMP
 WHERE PAYMENT_ID IN (SELECT PAYMENT_ID
			FROM TAKEOUT_PAYMENT
		   WHERE TAKEOUT_ID = TKO.ID)

Эксплаин говорит что-то в том же роде:

1	PRIMARY	<derived12>	system					1	
1	PRIMARY	PYT	ALL	PAYMENT_ID				4911	Using where
1	PRIMARY	PMT	eq_ref	PRIMARY,ID,PERSON_ID,PAYMENT_TYPE_ID	PRIMARY	108	SOCIAL_OPN.PYT.PAYMENT_ID	1	Using where
1	PRIMARY	PER	eq_ref	PRIMARY,ID,CLOSED,NUMBER	PRIMARY	108	SOCIAL_OPN.PMT.PERSON_ID	1	Using where
1	PRIMARY	TPT	ref	TAKEOUT_ID,PAYMENT_ID	PAYMENT_ID	109	SOCIAL_OPN.PMT.ID	1	Using where
1	PRIMARY	TKO	eq_ref	PRIMARY,ID,STARTDATE	PRIMARY	108	SOCIAL_OPN.TPT.TAKEOUT_ID	1	Using where
14	DEPENDENT SUBQUERY	PAYOUT	ref	TAKEOUT_ID	TAKEOUT_ID	109	SOCIAL_OPN.TKO.ID	1	Using where
13	DEPENDENT SUBQUERY	P	ref	TAKEOUT_ID	TAKEOUT_ID	109	SOCIAL_OPN.TKO.ID	1	Using where
12	DERIVED								No tables used
10	DEPENDENT SUBQUERY	PAYOUT_TEMP	ALL					4911	Using where
11	DEPENDENT SUBQUERY	TAKEOUT_PAYMENT	ref	TAKEOUT_ID,PAYMENT_ID	TAKEOUT_ID	109	SOCIAL_OPN.TKO.ID	1	Using where
9	DEPENDENT SUBQUERY	PAYOUT	ref	TAKEOUT_ID	TAKEOUT_ID	109	SOCIAL_OPN.TKO.ID	1	Using where
8	DEPENDENT SUBQUERY	PAYOUT	ref	TAKEOUT_ID	TAKEOUT_ID	109	SOCIAL_OPN.TKO.ID	1	Using where
6	DEPENDENT SUBQUERY	PAYOUT_TEMP	ALL					4911	Using where
7	DEPENDENT SUBQUERY	TAKEOUT_PAYMENT	ref	TAKEOUT_ID,PAYMENT_ID	TAKEOUT_ID	109	SOCIAL_OPN.TKO.ID	1	Using where
4	DEPENDENT SUBQUERY	PAYOUT_TEMP	ALL					4911	Using where
5	DEPENDENT SUBQUERY	TAKEOUT_PAYMENT	ref	TAKEOUT_ID,PAYMENT_ID	TAKEOUT_ID	109	SOCIAL_OPN.TKO.ID	1	Using where
2	DEPENDENT SUBQUERY	PAYOUT_TEMP	ALL					4911	Using where
3	DEPENDENT SUBQUERY	TAKEOUT_PAYMENT	ref	TAKEOUT_ID,PAYMENT_ID	TAKEOUT_ID	109	SOCIAL_OPN.TKO.ID	1	Using where

Отсюда вопрос - можно ли что-то сделать, чтобы оно таки стало работать быстрее?
Единственный способ обрезать эту таблицу - это выборка по полю PAYMENT_ID, у которого пеймента связь многие ко многим с TAKEOUT_ID. То есть, пока я не вижу возможности не использовать IN.

[yaapelsinko]

Сам запрос:

SELECT UUID() as `ID`,			/* ид добавляемых записей */
	   PYT.PAYMENT_ID as `PAYMENT_ID`,	/* ид назначенного платежа, к которому расчитывается удержание */
	   TKO.ID as `TAKEOUT_ID`,		/* ид назначенного удержания, чтобы не перепутывались */

	   CASE
	   /* когда сумма удержаний за этот месяц больше, чем вся сумма к удержанию */
	   WHEN
	   (SELECT SUM(SUM * (TKO.MAX / 100)) 
		  FROM PAYOUT_TEMP
		 WHERE PAYMENT_ID IN (SELECT PAYMENT_ID
					FROM TAKEOUT_PAYMENT
					   WHERE TAKEOUT_ID = TKO.ID)) > TKO.SUM
	   /* тогда сумма текущего удержания умножается на (всю сумму к удержанию) / (сумму удержания за текущий месяц) */
	   THEN
	   PYT.SUM * (TKO.MAX / -100) * 
	   TKO.SUM /
	   (SELECT SUM(SUM * (TKO.MAX / 100)) 
		  FROM PAYOUT_TEMP
		 WHERE PAYMENT_ID IN (SELECT PAYMENT_ID
					FROM TAKEOUT_PAYMENT
					   WHERE TAKEOUT_ID = TKO.ID))

	   /* когда сумма удержаний за этот месяц больше, чем разница между уже удержанным и всей суммой к удержанию */
	   WHEN 
	   (SELECT ABS(SUM(SUM * (TKO.MAX / 100)))
		  FROM PAYOUT_TEMP
		 WHERE PAYMENT_ID IN (SELECT PAYMENT_ID
					FROM TAKEOUT_PAYMENT
					   WHERE TAKEOUT_ID = TKO.ID)) > 
	   ((SELECT ABS(SUM(SUM)) FROM PAYOUT WHERE DELETED IS NULL AND TAKEOUT_ID = TKO.ID) - TKO.SUM)
	   /* тогда сумма текущего удержания умножается на (разность между удержанным и всей суммой удержания) / (сумму удержания за текущий месяц) */
	   THEN
	   PYT.SUM * (TKO.MAX / -100) * 
	   ((SELECT ABS(SUM(SUM)) FROM PAYOUT WHERE DELETED IS NULL AND TAKEOUT_ID = TKO.ID) - TKO.SUM) /
	   (SELECT SUM(SUM * (TKO.MAX / 100)) 
		  FROM PAYOUT_TEMP
		 WHERE PAYMENT_ID IN (SELECT PAYMENT_ID
					FROM TAKEOUT_PAYMENT
					   WHERE TAKEOUT_ID = TKO.ID))
	   /* обычная сумма удержания */
	   ELSE PYT.SUM * (TKO.MAX / -100) 
	   END as `SUM`, 

	   PYT.DELIVERY_DATE as `DELIVERY_DATE`,	/* дата доставки того платежа, с которого удерживаем */
	   NULL as `DELIVERY`,			/* тут доставка вовсе никогда не проставляется */
	   NOW() as `UPDATED`,			/* время добавления записи */
	   NULL as `DELETED`			/* пока запись не удалена */

FROM PAYOUT_TEMP PYT									/* удержания происходят с уже начисленных сумм (можно вместо таблицы использовать запрос расчёта выплат */
JOIN PAYMENT PMT ON PMT.ID = PYT.PAYMENT_ID AND PMT.DELETED IS NULL			/* присоединяем сюда таблицу с назначенными выплатами, кроме удалённых (с уже удалённых не удерживаем) */
JOIN PERSON PER ON PER.ID = PMT.PERSON_ID AND PER.DELETED IS NULL AND PER.CLOSED = 0	/* присоединяем дела к назначенным выплатам, кроме удалённых и закрытых */
JOIN TAKEOUT_PAYMENT TPT ON TPT.PAYMENT_ID = PYT.PAYMENT_ID AND TPT.DELETED IS NULL	/* присоединяем таблицу привязки выплата-удержание, кроме удалённых связей */
JOIN TAKEOUT TKO ON TKO.ID = TPT.TAKEOUT_ID AND TKO.DELETED IS NULL			/* присоединяем наконец таблицу с самими удержаниями, кроме удалённых */
JOIN (SELECT '%' as `type`,
	 '%' as `person`,
	 STR_TO_DATE('01.03.2013', '%d.%m.%Y') as `date`) input
WHERE PYT.DELETED IS NULL								/* не расчитываем удержания с удалённых выплат */
  AND ('%' = input.person OR PER.NUMBER = input.person)						/* фильтр дел - все или одно конкретное */
  AND ('%' = input.type OR PMT.PAYMENT_TYPE_ID = input.type)					/* фильтр типов платежей - все или один конкретный */
  AND EXTRACT(YEAR_MONTH FROM PYT.DELIVERY_DATE) = EXTRACT(YEAR_MONTH FROM input.date)	/* взяли только те платежи, которые начислены за указанный месяц */
  AND PYT.SUM > 0										/* взяли только платежи, а не удержания  */
  AND input.date >= TKO.STARTDATE								/* смотрим, чтобы текущая дата была не ранее, чем дата начала действия удержания */

  /* и не существаует уже расчитанных удержаний по этому платежу за этот месяц */
  AND NOT EXISTS (SELECT P.ID
		FROM PAYOUT P
		   WHERE DELETED IS NULL
		 AND TAKEOUT_ID = TKO.ID
		 AND EXTRACT(YEAR_MONTH FROM P.DELIVERY_DATE) = EXTRACT(YEAR_MONTH FROM input.date))

  /* и уже удержанное по этому удержанию не превышает общую сумму удержания */
  AND TKO.SUM > COALESCE((SELECT ABS(SUM(SUM)) FROM PAYOUT WHERE TAKEOUT_ID = TKO.ID), 0)

[yaapelsinko]

Ну в общем как обычно на свои темы отвечаю сам.

Грешил я на те подзапросы правильно, в них вся проблема.
Используйте джоины вместо инов, посоны.

Подзапросы вида

SELECT SUM(SUM * (TKO.MAX / 100))
FROM PAYOUT_TEMP P
JOIN TAKEOUT_PAYMENT T ON T.PAYMENT_ID = P.PAYMENT_ID
WHERE T.TAKEOUT_ID = TKO.ID)

ускорили весь запрос в целом ещё раз в 200.

[Аватар]

Для начала опробовать вместо IN такой вариант

SELECT SUM(PAYOUT_TEMP.SUM * (TKO.MAX / 100))
  FROM PAYOUT_TEMP,TAKEOUT_PAYMENT
  WHERE PAYOUT_TEMP.PAYMENT_ID=TAKEOUT_PAYMENT.PAYMENT_ID AND
        TAKEOUT_PAYMENT.TAKEOUT_ID=TKO.ID

ADD

ускорили весь запрос в целом ещё раз в 200.

Пока отвечат ТС сам додумался

[yaapelsinko]

Ладно, тогда другой вопрос, что-то я сам не додумался.

SELECT PER.NUMBER, PTY.NAME, POT.SUM, POT.DELIVERY_DATE 
FROM PAYOUT POT
LEFT JOIN PAYMENT PMT ON PMT.ID = POT.PAYMENT_ID
LEFT JOIN PAYMENT_TYPE PTY ON PTY.ID = PMT.PAYMENT_TYPE_ID
LEFT JOIN PERSON PER ON PER.ID = PMT.PERSON_ID
WHERE PER.DELETED IS NULL
AND (PER.NUMBER = '1' OR '%' = '1')

Когда эксплейню запрос в таком виде, то он показывает, что сканирует сто тыщ мильёнов строк в таблице PAYOUT.
Когда убираю "OR '%' = '1'" - всё становится на место, срабатывают индексы, оптимизации, выполняется за миллисекунды. А так - несколько секунд.

Я интерпретирую это так, что все остальне таблицы ограничиваются по индексированным ключам в связках и в условии, а таблица PAYOUT как будто бы ничем напрямую не ограничивается, поэтому mysql выполняет проверку равентсва '%' = '1' для каждой строки в таблице.

Однако, во-первых, ежу понятно, что данное равенство не может быть разным для разных строк - достаточно вычислить его один раз. Во-вторых, на более простом запросе, например
SELECT *
FROM PAYOUT
WHERE (ID = '1' OR '%' = '1')
mysql вполне разбирается в ситуации и не проверяет все строки.

Соответственно, вопрос в том, есть ли какие-то директивы, чтобы указать субду, как именно надо оптимизировать запрос, либо способ иначе описать конструкцию (ID = '1' OR '%' = '1'), так, чтобы она не смущала базу данных?

[Serge_Bliznykov]

а не вариант засунуть тот запрос, который, по вашим словам корректно работает, в подзапрос (если, конечно, MySQL это позволяет!)
я имею в виду:

  SELECT PER.NUMBER, PTY.NAME, POT.SUM, POT.DELIVERY_DATE 
  FROM ( SELECT * FROM PAYOUT
               WHERE (ID = '1' OR '%' = '1') ) POT
 ....

только, хоть убейте меня, не могу понять, что означает условие '%' = '1' и для чего оно Вам нужно в запросе...


p.s. я из личного опыта давно сделал умозаключение (скорее всего, ошибочное в общем случае, но на практике часто очень полезное) - любое использование OR в запросе - это сразу провал в производительности запроса

[Аватар]

любое использование OR в запросе - это сразу провал в производительности запроса

И IN (1,5,..) тоже самое. По сути, если во WHERE такие конструкции, то это аналогично нескольким выполнениям запроса.

что означает условие '%' = '1' и для чего оно Вам нужно в запросе

Сделан запрос, который должен работать как с фильтром, так и без фильтра. Программисту лень адаптировать нормальным образом запрос к работе без фильтра, вот и придумывает такие залепухи, а потом - почему медленно работает

[yaapelsinko]

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

Если в условии с OR есть равенство с константами типа 1 = 1, то вполне очевидно, что весь блок всегда TRUE и его можно вовсе исключить из запроса. А если FALSE, то само равенство также можно исключить из запроса.
Если субда этого не умеет, значит такая замечательная субда.
В оракле никогда проблем не возникало, хотя там делал выборки из таблиц с сотнями тысяч записей и кучей джоинов и не одним таким условием вида '%' = '%'.

а не вариант засунуть тот запрос, который, по вашим словам корректно работает, в подзапрос (если, конечно, MySQL это позволяет!)
я имею в виду

Нет, этот запрос был только для иллюстрации. Таблица payout джойнится с несколькими другими по ключам, и всё это множество записей отсекается по ключу, который находится в одной из этих таблиц, а не в самой payout. Это работает нормально и быстро, пока субда не натыкается на этот OR, и mysql почему-то считает необходимым проверить условие вида ([поле НЕ из PAYOUT] = [константа1] ИЛИ [константа2] = [константа1]) на каждой записи таблицы PAYOUT. Хотя ежу понятно, что там абсолютно никакой зависимости от данных из PAYOUT быть не может.

[Аватар]

Если субда этого не умеет, значит такая замечательная субда

Если выбора нет, то какая СУБД-а, то к той и нужно приспосабливаться хоть трижды она "замечательная". Или не пользоваться

[yaapelsinko]

Главное при этом помнить, о чём идёт речь - о том, можно ли какими-либо специальными директивами или особой структурой запроса избежать этого бага (а тут явно баг оптимизатора) и чётко указать, для какой таблицы какая проверка должна выполняться.

А не о преимуществах каких-то разных вещей или подходов в программировании.