Как ускорить запрос SELECT который выводит цену в диапазоне дат?

Question

[arsenaljek]

Подскажите, как можно ускорить запрос

SELECT DATE_FORMAT(date,'%d.%m.%Y') as date1, `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` WHERE `id_product` = 235408123 ORDER BY `date`

Запрос выполняется секунд 13
Он выводит такие данные :
"date1" "date" "prevDate" "price_product"
"04.02.2019" "2019-02-04" "2019-02-03" "2870"
"07.02.2019" "2019-02-07" "2019-02-06" "2870"
"15.02.2019" "2019-02-15" "2019-02-14" "2857"
"23.02.2019" "2019-02-23" "2019-02-22" "2856"
"07.03.2019" "2019-03-07" "2019-03-06" "3359"
"08.03.2019" "2019-03-08" "2019-03-07" "3358"
"15.03.2019" "2019-03-15" "2019-03-14" "3356"
"16.03.2019" "2019-03-16" "2019-03-15" "3034"
"24.03.2019" "2019-03-24" "2019-03-23" "3036"
"29.03.2019" "2019-03-29" "2019-03-28" "3358"
"31.03.2019" "2019-03-31" "2019-03-30" "3356"
"01.04.2019" "2019-04-01" "2019-03-31" "3354"
"12.04.2019" "2019-04-12" "2019-04-11" "3348"
"13.04.2019" "2019-04-13" "2019-04-12" "3162"
"22.04.2019" "2019-04-22" "2019-04-21" "3156"
"30.04.2019" "2019-04-30" "2019-04-29" "3159"
"07.05.2019" "2019-05-07" "2019-05-06" "3162"
"24.05.2019" "2019-05-24" "2019-05-23" "2941"
"03.06.2019" "2019-06-03" "2019-06-02" "3162"

В таблице около 12 млн записей и они растут
Индексы стоят на id_product и date

CREATE TABLE `prices` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`id_product` VARCHAR(100) NOT NULL DEFAULT '0',
	`price_product` INT(11) NULL DEFAULT NULL,
	`date` DATE NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `id_product` (`id_product`),
	INDEX `id_date` (`id_product`, `date`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT

EXPLAIN возвращает такие данные
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" "prices" "ALL" "id_product,id_date" \N \N \N "13132125" "Using where; Using filesort"

Проблема именно в этом запросе, если его убрать, то страница открывается моментально.

Comments

[Дмитрий]

От date1 смысла нет, так как можно уже через php переделать, но запрос разгрузит

[arsenaljek]

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

[Дмитрий]

arsenaljek, id_product varchar(100) тоже думаю лишнее
Первое это строка, если сделать int будет проще
Если поменяете тип. Ключи тоже надо будет перестроить

[Дмитрий]

arsenaljek, ну ещё попробуйте без второго составного ключа, или просто ключ на дату

[arsenaljek]

Дмитрий, пробовал ключ на дату только, стало еще медленнее.

[Дмитрий]

arsenaljek, ну ключ на строки совсем печалька, можно глянуть какой сейчас максимум имеет по длине ид продукта.
select max(length(id_product)) from prices

[arsenaljek]

Дмитрий, Максимум 9

[Дмитрий]

arsenaljek, можно поставить 10 и добавить Unsigned , они же всегда положительные

[Дмитрий]

arsenaljek, главное чтоб там не было приколов с нулями вначале

Answer 1

[AUser0]

Ну заставьте MySQL перманентно использовать индекс:

SELECT DATE_FORMAT(date,'%d.%m.%Y') as date1, `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` FORCE INDEX (`id_product`) WHERE `id_product` = 235408123 ORDER BY `date`

Если версия MySQL не позволит выполнить такой запрос - поменяйте FORCE INDEX на USE INDEX.

Comments

[arsenaljek]

SELECT  `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` FORCE INDEX (`id_product`) WHERE `id_product` = 235408123  ORDER BY `date`;

/* Affected rows: 0 Найденные строки: 19 Предупреждения: 0 Длительность 1 query: 11,279 sec. */

SELECT  `date`, `date` - INTERVAL 1 DAY AS `prevDate`, `price_product` FROM `prices` USE INDEX (`id_product`) WHERE `id_product` = 235408123  ORDER BY `date`;

/* Affected rows: 0 Найденные строки: 19 Предупреждения: 0 Длительность 1 query: 12,449 sec. */

[AUser0]

А какой получился EXPLAIN у первого запроса?

[arsenaljek]

AUser0,
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "SIMPLE" "prices" "ALL" "id_product" \N \N \N "12975903" "Using where; Using filesort"

[AUser0]

А можно изменить индекс `id_date`, добавив в него `price_product`, ну и поменять FORCE INDEX на `id_date`. Тогда все данные могут браться прямиком из индекса, вообще без чтения базы.

[AUser0]

\N \N \N говорит о том, что индекс не используется, совсем.

Меняйте тип `id_product` на BIGINT, явно у вас там число меньше двойки в 63-ей степени...
Или там хранятся именно строки с буквами?

[arsenaljek]

AUser0, Я пытаюсь заменить индексы и поменять тип на BIGINT но видимо это не быстро. Таблица висит уже 4 часа через ALTER TABLE

[AUser0]

До сих пор висит?

[arsenaljek]

AUser0, Сменил на BIGINT, но теперь почему-то основной запрос отказываеться выполняться. Просто висит, даже EXPLOIN сделать не хочет. Никаких ошибок, просто висит. В коде ничего не менял, просто изменил тип id_product с VARCHAR на BIGINT

[AUser0]

Сделайте исправление таблицы, может полегчает.
REPAIR TABLE `prices`;

[arsenaljek]

AUser0, Не работает. Говорит
The storage engine for the table doesn't support repair

[AUser0]

arsenaljek, тогда
CHECK TABLE `prices`;
Хотя бы узнаете, таблица работоспособна ли вообще...

[arsenaljek]

AUser0, Вроде все норм с таблицей. Ответ такой
"Table" "Op" "Msg_type" "Msg_text"
"test.prices" "check" "status" "OK"
Но запрос висит и все.....

[AUser0]

arsenaljek, так вы делаете SELECT запрос при полном отсутствии индекса, и он висит?

[arsenaljek]

AUser0, Если честно я не понимаю в чем дело.
Запрос

SELECT dtc, id_product, vendor, vendorCode, description, image_product, link_product, name, price_product,dtp
			FROM (
	  			SELECT t2.dtc, t2.id_product, n.name, prc.price_product, t2.dtp, n.vendor, n.vendorCode, n.description, n.image_product, n.link_product
	  			FROM (
	    			SELECT t1.dt AS dtc, t1.id_product, MAX(p.`date`) AS dtp
	    			FROM (	
	      				SELECT p.dt, products.id_product
	      				FROM pcalendar p, (
	        				SELECT distinct p.id_product
	        				FROM prices_test p
	        				WHERE id_product = 837623465
	        				ORDER BY id_product
	      				) products
	      					WHERE p.dt between p.dt AND CURDATE()
	    			) t1
	    				LEFT JOIN prices_test p ON (t1.dt >= p.`date`) AND (t1.id_product = p.id_product)
	    				GROUP BY dtc, id_product
	    				ORDER BY dtc, id_product
	  			) t2
	  				LEFT JOIN prices_test prc ON (t2.dtp = prc.`date`) AND (t2.id_product = prc.id_product)
	  				LEFT JOIN products n ON (t2.id_product = n.id_product)
	  				ORDER BY dtc, id_product
			) t3 
				WHERE dtc between dtp AND CURDATE()
				ORDER BY dtc DESC, id_product

До того, как я изменил тип id_product с VARCHAR на BIGINT все работало. Запрос выполнялся. Сейчас от просто висит.
Сейчас попробовал создать тестовую таблицу с 1000 строк

CREATE TABLE `prices` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `id_product` BIGINT(21) NOT NULL DEFAULT '0',
  `price_product` INT(11) NULL DEFAULT NULL,
  `date` DATE NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `id_product` (`id_product`),
  INDEX `id_date` (`id_product`, `date`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT

Все равно висит. Сейчас изменил значение `id_product` с BIGINT на VARCHAR и все опять заработало

[AUser0]

Так, сразу, даже не разбираясь, что в этом запросе зачем, вот это:
WHERE p.dt between p.dt AND CURDATE()
это как? "min <= X <= max", где min всегда равно X?

[arsenaljek]

AUser0, По сути это строка здесь и не нужна. Но ее присутствие или отсутствие никак не влияют на то, что с типом VARCHAR все работает, а INT или BIGINT сразу вводит его в ступор. Не понимаю, почему так

[arsenaljek]

AUser0, Суть этого запроса в том, что он дополняет даты. Т.к в базу пишется цена только в том случае, если она отличается от прошлой цены. Но выводить мне нужно цены каждый день. Что собственно и делает этот запрос

[AUser0]

arsenaljek, ну давайте разберём один краааасивы запрос:

SELECT p.dt, products.id_product
FROM pcalendar p,
(
	SELECT DISTINCT p.id_product
	FROM prices_test p
	WHERE id_product = 837623465
	ORDER BY id_product
) products
WHERE p.dt between p.dt AND CURDATE()

Значит из базы `prices_test` берутся все записи WHERE id_product=..., через DISTINCT дубликаты отбрасываются, получается одна запись id_product=..., это результат внутреннего запроса.

Далее из `pcalendar` через бессмысленный WHERE p.dt between p.dt AND CURDATE() берутся ВСЕ ЗАПИСИ таблицы `pcalendar`, к каждой БЕЗ каких-либо уcловий тупо добавляется id_product=..., и это результат внешнего запроса.

То есть у нас массив из ВСЕХ (даже повторяющихся) pcalendar.dt с тупо добавленным к ним id_product=... А сколько там у вас записей в `pcalendar`?
Хорошо ведь, да? А дальше... Нет, всё, я не могу, я передохну, потому что дальше - больше!

[arsenaljek]

Далее из `pcalendar` через бессмысленный WHERE p.dt between p.dt AND CURDATE() берутся ВСЕ ЗАПИСИ таблицы `pcalendar`, к каждой БЕЗ каких-либо уcловий тупо добавляется id_product=..., и это результат внешнего запроса.

Ну почему все записи? Там как раз есть условие, что берутся записи от первой даты в таблице календаря по текущую CURDATE()
Т.е в данный момент он выводит 158 строк с 2019-01-01 по 2019-06-07

[AUser0]

arsenaljek, ВСЕ записи. Потому что там условие p.dt BETWEEN p.dt AND CURDATE(), что означает `значение dt` <= `значение dt` <= CURDATE().
Но поскольку p.dt всегда равно самому себе (p.dt == p.dt), и оно всегда меньше CURDATE(), то WHERE можно заменить на WHERE 1...

Или у вас там есть p.dt из будущего? Ну, тогда да, не ВСЕ записи таблицы, а только записи с p.dt до даты CURDATE().

[arsenaljek]

AUser0, Да, в таблице календаря есть записи из будущего) до 2025г

[AUser0]

arsenaljek, а вот покажите EXPLAIN этого запроса. Любопытно, сколько он займёт времени?

[arsenaljek]

AUser0,

1	PRIMARY	<derived3>	NULL	ALL	NULL	NULL	NULL	NULL	15585	11.11	Using where; Using filesort	
1	PRIMARY	prc	NULL	ref	id_product,id_date	id_date	306	t2.id_product,t2.dtp	1	100.00	NULL	
1	PRIMARY	n	NULL	ref	id_product	id_product	303	t2.id_product	1	100.00	NULL	
3	DERIVED	<derived5>	NULL	system	NULL	NULL	NULL	NULL	1	100.00	Using temporary; Using filesort	
3	DERIVED	p	NULL	ALL	NULL	NULL	NULL	NULL	2192	11.11	Using where	
3	DERIVED	p	NULL	ref	id_product,id_date	id_date	302	const	64	100.00	Using where; Using index	
5	DERIVED	p	NULL	ref	id_product,id_date	id_product	302	const	64	100.00	Using index

Сам Select занял 0.0104 сек.)
Но все это не решает моих проблем, т.к я до сих пор не могу понять, почему с VARCHAR работает, а с цифровыми типами - нет

Answer 2

[Stalker_RED]

Очень желательно id_product заменить на int, если это возможно, или перейти на использование того, другого id.

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

Попробуйте выбирать без сортировки, если разница будет очень большой, то можно делать ее отдельно. (подзапросом вытащить данные, и только потом сортировать).

Если все равно все плохо, и данные растут очень быстро, то возможно стоит рассмотреть другие БД, что-то высокопроизводительное, вроде tarantool, clickhouse или какие-то из семейства tsdb.

Answer 3

[Immortal_pony]

1. Судя по EXPLAIN, индексы у вас не используются при выполнении запроса. Это может происходить из-за того что индексы слишком большие и не лезут в память. Измените занчение innodb_buffer_pool_size (если у вас InnoDB) или key_buffer_size (если MyISAM) на ~30%-50% от оперативной памяти
2. Уберите сортировку из запроса если она вам нужна только для итоговой выборки. Воспользуйтесь конструкцией SELECT FROM SELECT чтобы отсортировать итоговую выборку