powered by simpleCommunicator - 2.0.28     © 2024 Programmizd 02
Map
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как оптимизировать выборку по диапазону?
25 сообщений из 31, страница 1 из 2
Как оптимизировать выборку по диапазону?
    #40133042
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
В таблице data_sgrc есть проиндексированное поле period типа date (всегда 1-е число месяца).
Запрос №1 выполняется ~0.5 сек для 1128204 записей:
Код: sql
1.
2.
3.
4.
5.
SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, COUNT(c.id) c2
FROM data_sgrc d
INNER JOIN contragents c ON d.id_contragent=c.id
WHERE d.period='2021-10-01'
GROUP BY p


Explain:
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdnullrefi_per|fk_contri_per4const79974100.00Using where1SIMPLEcnulleq_refPRIMARYPRIMARY4direct_contracts.d.id_contragent1100.00null

Но стоит только сделать выборку не за 1 месяц, а, к примеру, за 3:
Код: sql
1.
2.
3.
4.
5.
SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, COUNT(c.id) c2
FROM data_sgrc d
INNER JOIN contragents c ON d.id_contragent=c.id
WHERE d.period BETWEEN '2021-10-01' AND '2021-12-01'
GROUP BY p


Explain:
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdnullALLi_per|fk_contrnullnullnull110837723.04Using where; Using filesort1SIMPLEcnulleq_refPRIMARYPRIMARY4direct_contracts.d.id_contragent1100.00null
как индекс перестает использоваться и запрос №2 выполняется уже ~5 сек.
Что тут можно/нужно сделать? MySQL 8.0.27. Может CTE тут выручит? Или как-то изменив запрос/индексы? Ведь даже выполняя запрос №1 трижды, каждый раз для следующего месяца, займет всего ~1.5 сек вместо 5 сек для запроса №2.
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133051
SergiiW
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
LiYing
каждый раз для следующего месяца, займет всего ~1.5 сек

Это предположение или Вы проверяли?
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133056
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
SergiiW
LiYing
каждый раз для следующего месяца, займет всего ~1.5 сек

Это предположение или Вы проверяли?

Проверял, пожалуйста, вот лог:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, COUNT( c.id) c2
	FROM data_sgrc d
	INNER JOIN contragents c ON d.id_contragent=c.id
	WHERE d.period='2021-10-01'
	GROUP BY p
> OK
> Query Time: 0,396s


SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, COUNT( c.id) c2
	FROM data_sgrc d
	INNER JOIN contragents c ON d.id_contragent=c.id
	WHERE d.period='2021-11-01'
	GROUP BY p
> OK
> Query Time: 0,358s


SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, COUNT( c.id) c2
	FROM data_sgrc d
	INNER JOIN contragents c ON d.id_contragent=c.id
	WHERE d.period='2021-12-01'
	GROUP BY p
> OK
> Query Time: 0,349s
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133079
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
LiYing,

Сколько записей выдает каждый из запросов?

попробуйте сделать OPTIMIZE TABLE для обеих таблиц.
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133089
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
miksoft
LiYing,

Сколько записей выдает каждый из запросов?

попробуйте сделать OPTIMIZE TABLE для обеих таблиц.

Делал - "Table does not support optimize, doing recreate + analyze instead", время не изменилось.
Результаты запросов:
№1pc1c22021-10-0150741837№2pc1c22021-10-01507418372021-11-01510422112021-12-0151442516
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133119
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
LiYing, маленько офтопа
Вот интересно, учитывая, что "ON d.id_contragent=c.id", как изменится время и план выполнения, если заменить COUNT(c.id) на COUNT(d.id_contragent)? Кстати, эти поля проиндексированы? в обеих таблицах?
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133127
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
paver,
№1
Код: sql
1.
2.
3.
4.
5.
explain SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, COUNT(d.id_contragent) c2
	FROM data_sgrc d
	INNER JOIN contragents c ON d.id_contragent=c.id
	WHERE d.period='2021-10-01'
	GROUP BY p


idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdnullrefi_per|fk_contri_per4const85562100.00Using where1SIMPLEcnulleq_refPRIMARYPRIMARY4direct_contracts.d.id_contragent1100.00null
№2
Код: sql
1.
2.
3.
4.
5.
explain SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, COUNT(d.id_contragent) c2
	FROM data_sgrc d
	INNER JOIN contragents c ON d.id_contragent=c.id
	WHERE d.period BETWEEN '2021-10-01' AND '2021-12-01'
	GROUP BY p


idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdnullALLi_per|fk_contrnullnullnull110843623.04Using where; Using filesort1SIMPLEcnulleq_refPRIMARYPRIMARY4direct_contracts.d.id_contragent1100.00null
c.id (из DDL)
Код: sql
1.
PRIMARY KEY (`id`),


d.id_contragent (из DDL)
Код: sql
1.
2.
  KEY `fk_contr` (`id_contragent`),
  CONSTRAINT `fk_contr` FOREIGN KEY (`id_contragent`) REFERENCES `contragents` (`id`)



Время выполнения запросов после замены примерно то же самое.
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133267
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
В первом случае селективность индекса 79974 / 1128204 ~ 7%. В принципе и это много, где-то на границе оптимальности. Но тем не менее использование индекса всё ещё возможно.

Во втором же случае селективность , вероятно, на уровне 20%, т.е. использование индекса заведомо неэффективно. Что и видно из плана - сервер предпочитает сканирование таблицы.

PS. И вообще, где CREATE TABLE обеих таблиц? о статистике данных даже не говорю...
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133318
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Akina
PS. И вообще, где CREATE TABLE обеих таблиц? о статистике данных даже не говорю...

Про статистику не понял (что надо? всё покажу :)), а CREATE вот:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
CREATE TABLE `contragents` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`account` INT(10) UNSIGNED NOT NULL,
	`acc_kind` INT(10) NOT NULL,
	`fio` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`id_mkd` INT(10) NULL DEFAULT NULL,
	`addr_flat` VARCHAR(25) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`area` DOUBLE NULL DEFAULT NULL,
	`property` TINYINT(3) NULL DEFAULT '0',
	`phone` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`email` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`egrn_cadastral_num` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`egrn_file` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`cert_num` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`cert_file` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`pass_num` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`pass_file` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`other_num` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`other_file` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`finacc` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`finacc_file` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`comment` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `i_account` (`account`, `acc_kind`) USING BTREE,
	INDEX `i_fio` (`fio`) USING BTREE,
	INDEX `mk_idmkd` (`id_mkd`, `id`) USING BTREE,
	INDEX `i_phone` (`phone`) USING BTREE,
	CONSTRAINT `fk_mkd` FOREIGN KEY (`id_mkd`) REFERENCES `direct_contracts`.`mkd` (`id`) ON UPDATE CASCADE ON DELETE RESTRICT
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

CREATE TABLE `data_sgrc` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`id_contragent` INT(10) NULL DEFAULT NULL,
	`fio_cur` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`area_cur` DOUBLE NULL DEFAULT '0',
	`period` DATE NULL DEFAULT NULL,
	`h_deb_start` DOUBLE NULL DEFAULT '0',
	`h_cre_start` DOUBLE NULL DEFAULT '0',
	`h_sum_bill` DOUBLE NULL DEFAULT '0',
	`h_sum_get` DOUBLE NULL DEFAULT '0',
	`h_sum_change` DOUBLE NULL DEFAULT '0',
	`h_deb_end` DOUBLE NULL DEFAULT '0',
	`h_cre_end` DOUBLE NULL DEFAULT '0',
	`h_sum_debt` DOUBLE NULL DEFAULT '0',
	`h_sum_bill_y` DOUBLE NULL DEFAULT '0',
	`h_sum_get_y` DOUBLE NULL DEFAULT '0',
	`hw_deb_start` DOUBLE NULL DEFAULT '0',
	`hw_cre_start` DOUBLE NULL DEFAULT '0',
	`hw_sum_bill` DOUBLE NULL DEFAULT '0',
	`hw_sum_get` DOUBLE NULL DEFAULT '0',
	`hw_sum_change` DOUBLE NULL DEFAULT '0',
	`hw_deb_end` DOUBLE NULL DEFAULT '0',
	`hw_cre_end` DOUBLE NULL DEFAULT '0',
	`hw_sum_debt` DOUBLE NULL DEFAULT '0',
	`hw_sum_bill_y` DOUBLE NULL DEFAULT '0',
	`hw_sum_get_y` DOUBLE NULL DEFAULT '0',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `i_per` (`period`) USING BTREE,
	INDEX `fk_contr` (`id_contragent`) USING BTREE,
	CONSTRAINT `fk_contr` FOREIGN KEY (`id_contragent`) REFERENCES `direct_contracts`.`contragents` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133321
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Индексы i_per и fk_contr - ниачём. Создай вместо них один по (id_contragent, period) .
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133376
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Akina
Индексы i_per и fk_contr - ниачём. Создай вместо них один по (id_contragent, period) .

Сделано:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
CREATE TABLE `data_sgrc` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`id_contragent` INT(10) NULL DEFAULT NULL,
	`fio_cur` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`area_cur` DOUBLE NULL DEFAULT '0',
	`period` DATE NULL DEFAULT NULL,
	`h_deb_start` DOUBLE NULL DEFAULT '0',
	`h_cre_start` DOUBLE NULL DEFAULT '0',
	`h_sum_bill` DOUBLE NULL DEFAULT '0',
	`h_sum_get` DOUBLE NULL DEFAULT '0',
	`h_sum_change` DOUBLE NULL DEFAULT '0',
	`h_deb_end` DOUBLE NULL DEFAULT '0',
	`h_cre_end` DOUBLE NULL DEFAULT '0',
	`h_sum_debt` DOUBLE NULL DEFAULT '0',
	`h_sum_bill_y` DOUBLE NULL DEFAULT '0',
	`h_sum_get_y` DOUBLE NULL DEFAULT '0',
	`hw_deb_start` DOUBLE NULL DEFAULT '0',
	`hw_cre_start` DOUBLE NULL DEFAULT '0',
	`hw_sum_bill` DOUBLE NULL DEFAULT '0',
	`hw_sum_get` DOUBLE NULL DEFAULT '0',
	`hw_sum_change` DOUBLE NULL DEFAULT '0',
	`hw_deb_end` DOUBLE NULL DEFAULT '0',
	`hw_cre_end` DOUBLE NULL DEFAULT '0',
	`hw_sum_debt` DOUBLE NULL DEFAULT '0',
	`hw_sum_bill_y` DOUBLE NULL DEFAULT '0',
	`hw_sum_get_y` DOUBLE NULL DEFAULT '0',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `id_contr_per` (`id_contragent`, `period`) USING BTREE,
	CONSTRAINT `fk_contr` FOREIGN KEY (`id_contragent`) REFERENCES `contragents` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;


Хм... план изменился (и стал одинаков и для предположения paver "если заменить COUNT(c.id) на COUNT(d.id_contragent)"):
Код: sql
1.
2.
3.
4.
5.
6.
№1
explain SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, COUNT(c.id) c2
	FROM data_sgrc d
	INNER JOIN contragents c ON d.id_contragent=c.id
	WHERE d.period='2021-10-01'
	GROUP BY p


idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEcnullindexPRIMARYmk_idmkd9null48270100.00Using index1SIMPLEdnullrefid_contr_perid_contr_per9direct_contracts.c.id|const1100.00Using index
Код: sql
1.
2.
3.
4.
5.
6.
№2
explain SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, COUNT(c.id) c2
	FROM data_sgrc d
	INNER JOIN contragents c ON d.id_contragent=c.id
	WHERE d.period BETWEEN '2021-10-01' AND '2021-12-01'
	GROUP BY p


idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEcnullindexPRIMARYmk_idmkd9null48270100.00Using index; Using temporary; Using filesort1SIMPLEdnullrefid_contr_perid_contr_per5direct_contracts.c.id2611.11Using where; Using index
>№1 Query Time: 0,435s
>№2 Query Time: 2,562s
Неожиданно, но уже лучше для диапазонного запроса №2 в два раза, а для №1 время осталось примерно тем же.
Еще какие-нибудь предположения, пожалуйста!
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133388
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Akina, а конструкция "INDEX `mk_idmkd` (`id_mkd`, `id`) USING BTREE" вообще осмысленна?
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133429
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
А что с ней не так, с этой конструкцией? композитный индекс из двух полей, может, где-то для какого-то запроса ну очень нужный...
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133464
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Akina
А что с ней не так, с этой конструкцией? композитный индекс из двух полей, может, где-то для какого-то запроса ну очень нужный...

Мне казалось включение первичного ключа в составной индекс ненужным излишеством. Наверное в башке попутались понятия первичный ключ и внутренний идентификатор
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133467
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Akina
А что с ней не так, с этой конструкцией? композитный индекс из двух полей, может, где-то для какого-то запроса ну очень нужный...

Верно, очень частый запрос, например:
Код: sql
1.
2.
3.
4.
explain SELECT c.account,c.addr_flat,c.fio,c.area,d.*
FROM data_sgrc d
INNER JOIN contragents c ON d.id_contragent=c.id
WHERE c.id_mkd=1 AND d.period='2022-01-01'


idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEcnullrefPRIMARY|mk_idmkdmk_idmkd5const15100.00null1SIMPLEdnullrefid_contr_perid_contr_per9direct_contracts.c.id|const1100.00null
Этот индекс как раз для такого.

А по сути вопроса темы, можете еще что-то подсказать? Что изменить, добавить может?
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133481
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Даже такой простой запрос не использует индекс:
Код: sql
1.
2.
3.
4.
5.
explain SELECT period, SUM(h_sum_get+hw_sum_get) paid
FROM data_sgrc
WHERE period BETWEEN '2021-10-01' AND '2021-12-01'
GROUP BY period
ORDER BY period


idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdata_sgrcnullALLid_contr_pernullnullnull110843611.11Using where; Using temporary; Using filesort
> Query Time: 6,1s !!! :(
А данных-то в data_sgrc прибавляется каждый месяц по несколько десятков тысяч. И дальше время выборки будет только расти, как я понимаю.
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133499
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
LiYing

А по сути вопроса темы, можете еще что-то подсказать? Что изменить, добавить может?

Я (как любитель) еще пообследовал бы ситуацию.
- в какой момент происходит скачек прироста времени исполнения. т.е. посмотреть результаты для периода в 2 месяца.
- как быстро растет время в зависимости от периода. т.е. замерить время для 3, 6, 9 и 12 месяцев, например (вдруг не критично).
- как ведет запрос, если каунты считать отдельно

LiYing
Даже такой простой запрос не использует индекс:

Для однотабличных запросов можно попробовать покрывающий индекс. И сделать принудительным использование индексов
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133509
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
paver
LiYing

А по сути вопроса темы, можете еще что-то подсказать? Что изменить, добавить может?

Я (как любитель) еще пообследовал бы ситуацию.
- в какой момент происходит скачек прироста времени исполнения. т.е. посмотреть результаты для периода в 2 месяца.
- как быстро растет время в зависимости от периода. т.е. замерить время для 3, 6, 9 и 12 месяцев, например (вдруг не критично).
- как ведет запрос, если каунты считать отдельно

Свел замеры в табличку:
периодов в запросеQuery Time для SELECT p c1Query Time для SELECT p c1 c210.527s0.589s22.482s2.499s32.679s2.688s42.888s3.042s53.185s3.121s63.394s3.487s73.574s3.583s83.736s3.802s94.006s4.056s103.874s3.609s114.485s4.208s124.713s4.669s
Выбивающееся из общей закономерности роста времени для 9 месяцев обусловлено, видимо, общей нагрузкой на сервер в период замера.
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133526
Ролг Хупин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
LiYing
Akina
Индексы i_per и fk_contr - ниачём. Создай вместо них один по (id_contragent, period) .

Сделано:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
CREATE TABLE `data_sgrc` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`id_contragent` INT(10) NULL DEFAULT NULL,
	`fio_cur` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`area_cur` DOUBLE NULL DEFAULT '0',
	`period` DATE NULL DEFAULT NULL,
	`h_deb_start` DOUBLE NULL DEFAULT '0',
	`h_cre_start` DOUBLE NULL DEFAULT '0',
	`h_sum_bill` DOUBLE NULL DEFAULT '0',
	`h_sum_get` DOUBLE NULL DEFAULT '0',
	`h_sum_change` DOUBLE NULL DEFAULT '0',
	`h_deb_end` DOUBLE NULL DEFAULT '0',
	`h_cre_end` DOUBLE NULL DEFAULT '0',
	`h_sum_debt` DOUBLE NULL DEFAULT '0',
	`h_sum_bill_y` DOUBLE NULL DEFAULT '0',
	`h_sum_get_y` DOUBLE NULL DEFAULT '0',
	`hw_deb_start` DOUBLE NULL DEFAULT '0',
	`hw_cre_start` DOUBLE NULL DEFAULT '0',
	`hw_sum_bill` DOUBLE NULL DEFAULT '0',
	`hw_sum_get` DOUBLE NULL DEFAULT '0',
	`hw_sum_change` DOUBLE NULL DEFAULT '0',
	`hw_deb_end` DOUBLE NULL DEFAULT '0',
	`hw_cre_end` DOUBLE NULL DEFAULT '0',
	`hw_sum_debt` DOUBLE NULL DEFAULT '0',
	`hw_sum_bill_y` DOUBLE NULL DEFAULT '0',
	`hw_sum_get_y` DOUBLE NULL DEFAULT '0',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `id_contr_per` (`id_contragent`, `period`) USING BTREE,
	CONSTRAINT `fk_contr` FOREIGN KEY (`id_contragent`) REFERENCES `contragents` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;


Хм... план изменился (и стал одинаков и для предположения paver "если заменить COUNT(c.id) на COUNT(d.id_contragent)"):
Код: sql
1.
2.
3.
4.
5.
6.
№1
explain SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, COUNT(c.id) c2
	FROM data_sgrc d
	INNER JOIN contragents c ON d.id_contragent=c.id
	WHERE d.period='2021-10-01'
	GROUP BY p


idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEcnullindexPRIMARYmk_idmkd9null48270100.00Using index1SIMPLEdnullrefid_contr_perid_contr_per9direct_contracts.c.id|const1100.00Using index

Код: sql
1.
2.
3.
4.
5.
6.
№2
explain SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, COUNT(c.id) c2
	FROM data_sgrc d
	INNER JOIN contragents c ON d.id_contragent=c.id
	WHERE d.period BETWEEN '2021-10-01' AND '2021-12-01'
	GROUP BY p


idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEcnullindexPRIMARYmk_idmkd9null48270100.00Using index; Using temporary; Using filesort1SIMPLEdnullrefid_contr_perid_contr_per5direct_contracts.c.id2611.11Using where; Using index
>№1 Query Time: 0,435s
>№2 Query Time: 2,562s
Неожиданно, но уже лучше для диапазонного запроса №2 в два раза, а для №1 время осталось примерно тем же.
Еще какие-нибудь предположения, пожалуйста!

Как-то странно, количество результирующих записей разное, но измеряем же время
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133880
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
LiYing
пропущено...
Выбивающееся из общей закономерности роста времени для 9 месяцев обусловлено, видимо, общей нагрузкой на сервер в период замера.

Ну, т.е основную нагрузку дает COUNT(DISTINCT c.id_mkd)
Увеличение периода влияет некритично (с 2 до 12 - прирост менее чем в 2 раза)
Идея по оптимизации: в запросе используются индексы mk_idmkd и id_contr_per, у которых идентификатор контрагента (а это поле связи таблиц) на разных местах. Попробуй поменять порядок в mk_idmkd (id_mkd, id).
Ну и последнее: если запрос регулярный и критический, добавить поле id_mkd во вторую таблицу и считать каунты только по ней
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133889
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
paver
Попробуй поменять порядок в mk_idmkd (id_mkd, id)

Поменял и оптимизировал таблицу, теперь план для:
Код: sql
1.
2.
3.
4.
5.
explain SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, COUNT(c.id) c2
	FROM data_sgrc d
	INNER JOIN contragents c ON d.id_contragent=c.id
	WHERE d.period BETWEEN '2021-10-01' AND '2021-12-01'
	GROUP BY p


такой
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEcnullindexPRIMARYmk_idmkd9null45586100.00Using index; Using temporary; Using filesort1SIMPLEdnullrefid_contr_perid_contr_per5direct_contracts.c.id2711.11Using where; Using index
Отличие от инвертированного индекса только в чуть меньшем количестве rows (45586 < 48270). Но время выполнения запроса не поменялось, к сожалению.
Видимо, и правда остается только "добавить поле id_mkd во вторую таблицу и считать каунты только по ней", если не будет других предложений.
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133908
paver
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
LiYing
если не будет других предложений.

Еще мысли чайника. Все необходимые для запроса данные содержатся в индексах, СУБД нет никакой необходимости читать сами таблицы. Для одного месяца так и происходит, при увеличении периода подключается Using filesort. Может следует поиграться с параметрами СУБД, добавить памяти для кешей? Какие именно и сколько - не подскажу, увы.
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133909
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Участник
Я бы скорее думал о партиционировании по дате. Это позволит резко уменьшить количество читаемых данных (partition pruning).
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133920
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Akina
Я бы скорее думал о партиционировании по дате. Это позволит резко уменьшить количество читаемых данных (partition pruning).

Не сталкивался еще с партиционированием, буду изучать - спасибо!

paver, тоже спасибо за участие. Про параметры почитаю.
...
Рейтинг: 0 / 0
Как оптимизировать выборку по диапазону?
    #40133965
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость
Akina,

Partition pruning при беглом ознакомлении показалось многообещающим, но обнаружилась печалька: Foreign keys not supported for partitioned InnoDB tables. - https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html
а у меня в табличке:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
CREATE TABLE `data_sgrc` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`id_contragent` INT(10) NULL DEFAULT NULL,
	...
	INDEX `fk_contr` (`id_contragent`) USING BTREE,
	CONSTRAINT `fk_contr` FOREIGN KEY (`id_contragent`) REFERENCES `direct_contracts`.`contragents` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;
...
Рейтинг: 0 / 0
25 сообщений из 31, страница 1 из 2
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как оптимизировать выборку по диапазону?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]