Гость
Войти | Профиль | Очистить
Map
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как оптимизировать выборку по диапазону? / 25 сообщений из 31, страница 1 из 2
  1  все
10.02.2022, 10:45
    #40133042
LiYing
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
| Ответить | Цитировать | Написать  
10.02.2022, 10:54
    #40133051
SergiiW
SergiiW
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать выборку по диапазону?
LiYing
каждый раз для следующего месяца, займет всего ~1.5 сек

Это предположение или Вы проверяли?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 11:10
    #40133056
LiYing
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
| Ответить | Цитировать | Написать  
10.02.2022, 11:43
    #40133079
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать выборку по диапазону?
LiYing,

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

попробуйте сделать OPTIMIZE TABLE для обеих таблиц.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 11:56
    #40133089
LiYing
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
| Ответить | Цитировать | Написать  
10.02.2022, 13:15
    #40133119
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать выборку по диапазону?
LiYing, маленько офтопа
Вот интересно, учитывая, что "ON d.id_contragent=c.id", как изменится время и план выполнения, если заменить COUNT(c.id) на COUNT(d.id_contragent)? Кстати, эти поля проиндексированы? в обеих таблицах?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 13:32
    #40133127
LiYing
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
| Ответить | Цитировать | Написать  
10.02.2022, 17:29
    #40133267
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать выборку по диапазону?
В первом случае селективность индекса 79974 / 1128204 ~ 7%. В принципе и это много, где-то на границе оптимальности. Но тем не менее использование индекса всё ещё возможно.

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

PS. И вообще, где CREATE TABLE обеих таблиц? о статистике данных даже не говорю...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 20:13
    #40133318
LiYing
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
| Ответить | Цитировать | Написать  
10.02.2022, 20:39
    #40133321
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать выборку по диапазону?
Индексы i_per и fk_contr - ниачём. Создай вместо них один по (id_contragent, period) .
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2022, 08:26
    #40133376
LiYing
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
| Ответить | Цитировать | Написать  
11.02.2022, 10:09
    #40133388
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать выборку по диапазону?
Akina, а конструкция "INDEX `mk_idmkd` (`id_mkd`, `id`) USING BTREE" вообще осмысленна?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2022, 11:42
    #40133429
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать выборку по диапазону?
А что с ней не так, с этой конструкцией? композитный индекс из двух полей, может, где-то для какого-то запроса ну очень нужный...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2022, 13:09
    #40133464
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать выборку по диапазону?
Akina
А что с ней не так, с этой конструкцией? композитный индекс из двух полей, может, где-то для какого-то запроса ну очень нужный...

Мне казалось включение первичного ключа в составной индекс ненужным излишеством. Наверное в башке попутались понятия первичный ключ и внутренний идентификатор
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2022, 13:15
    #40133467
LiYing
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
| Ответить | Цитировать | Написать  
11.02.2022, 13:33
    #40133481
LiYing
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
| Ответить | Цитировать | Написать  
11.02.2022, 13:57
    #40133499
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать выборку по диапазону?
LiYing

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

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

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

Для однотабличных запросов можно попробовать покрывающий индекс. И сделать принудительным использование индексов
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
11.02.2022, 14:33
    #40133509
LiYing
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
| Ответить | Цитировать | Написать  
11.02.2022, 15:28
    #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
| Ответить | Цитировать | Написать  
14.02.2022, 07:01
    #40133880
paver
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
| Ответить | Цитировать | Написать  
14.02.2022, 08:35
    #40133889
LiYing
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
| Ответить | Цитировать | Написать  
14.02.2022, 10:51
    #40133908
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать выборку по диапазону?
LiYing
если не будет других предложений.

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

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

paver, тоже спасибо за участие. Про параметры почитаю.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.02.2022, 13:42
    #40133965
LiYing
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
| Ответить | Цитировать | Написать  
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как оптимизировать выборку по диапазону? / 25 сообщений из 31, страница 1 из 2
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 1114ms
Закрыть
start [/forum/topic.php?fid=47&tablet=1&tid=1827770]:
 0ms
get settings:
 24ms
get forum list:
 24ms
check forum access:
 5ms
check topic access:
 5ms
track hit:
 64ms
get topic data:
 50ms
get forum data:
 3ms
get page messages:
 612ms
get tp. blocked users:
 2ms
others: 325ms
total:  1114ms
x
x
Закрыть


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