Как оптимизировать выборку по диапазону? / MySQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / 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

10.02.2022, 10:45

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133051

SergiiW

Гость

LiYing

каждый раз для следующего месяца, займет всего ~1.5 сек

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

...

Рейтинг:

0 / 0

10.02.2022, 10:54

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#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

10.02.2022, 11:10

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133079

miksoft

Участник

Сообщения: 36 746

Рейтинг: 0 / 0

LiYing,

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

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

...

Рейтинг:

0 / 0

10.02.2022, 11:43

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#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

10.02.2022, 11:56

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133119

paver

Гость

LiYing, маленько офтопа
Вот интересно, учитывая, что "ON d.id_contragent=c.id", как изменится время и план выполнения, если заменить COUNT(c.id) на COUNT(d.id_contragent)? Кстати, эти поля проиндексированы? в обеих таблицах?

...

Рейтинг:

0 / 0

10.02.2022, 13:15

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#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

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, 13:32

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133267

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

В первом случае селективность индекса 79974 / 1128204 ~ 7%. В принципе и это много, где-то на границе оптимальности. Но тем не менее использование индекса всё ещё возможно.

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

PS. И вообще, где CREATE TABLE обеих таблиц? о статистике данных даже не говорю...

...

Рейтинг:

0 / 0

10.02.2022, 17:29

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#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

10.02.2022, 20:13

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133321

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Индексы i_per и fk_contr - ниачём. Создай вместо них один по (id_contragent, period) .

...

Рейтинг:

0 / 0

10.02.2022, 20:39

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#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

11.02.2022, 08:26

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133388

paver

Гость

Akina, а конструкция "INDEX `mk_idmkd` (`id_mkd`, `id`) USING BTREE" вообще осмысленна?

...

Рейтинг:

0 / 0

11.02.2022, 10:09

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133429

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

А что с ней не так, с этой конструкцией? композитный индекс из двух полей, может, где-то для какого-то запроса ну очень нужный...

...

Рейтинг:

0 / 0

11.02.2022, 11:42

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133464

paver

Гость

Akina

Мне казалось включение первичного ключа в составной индекс ненужным излишеством. Наверное в башке попутались понятия первичный ключ и внутренний идентификатор

...

Рейтинг:

0 / 0

11.02.2022, 13:09

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#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

11.02.2022, 13:15

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#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

11.02.2022, 13:33

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133499

paver

Гость

LiYing

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

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

LiYing

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

Для однотабличных запросов можно попробовать покрывающий индекс. И сделать принудительным использование индексов

...

Рейтинг:

0 / 0

11.02.2022, 13:57

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133509

LiYing

Гость

paver

LiYing

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

Свел замеры в табличку:
периодов в запросе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, 14:33

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133526

Ролг Хупин

Участник

Откуда: Чебаркуль

Сообщения: 4 817

Рейтинг: 0 / 0

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

Код: 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

...

Рейтинг:

0 / 0

11.02.2022, 15:28

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#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

14.02.2022, 07:01

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#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

14.02.2022, 08:35

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133908

paver

Гость

LiYing

если не будет других предложений.

Еще мысли чайника. Все необходимые для запроса данные содержатся в индексах, СУБД нет никакой необходимости читать сами таблицы. Для одного месяца так и происходит, при увеличении периода подключается Using filesort. Может следует поиграться с параметрами СУБД, добавить памяти для кешей? Какие именно и сколько - не подскажу, увы.

...

Рейтинг:

0 / 0

14.02.2022, 10:51

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133909

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Я бы скорее думал о партиционировании по дате. Это позволит резко уменьшить количество читаемых данных (partition pruning).

...

Рейтинг:

0 / 0

14.02.2022, 10:52

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#40133920

LiYing

Гость

Akina

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

paver, тоже спасибо за участие. Про параметры почитаю.

...

Рейтинг:

0 / 0

14.02.2022, 11:56

| Ответить | Цитировать | Написать

Как оптимизировать выборку по диапазону?

#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

14.02.2022, 13:42

| Ответить | Цитировать | Написать

25 сообщений из 31, страница 1 из 2

все

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как оптимизировать выборку по диапазону?

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/moderation_log.php?user_name=Alen85]:	0ms
get settings:	28ms
get forum list:	13ms
get settings:	27ms
get forum list:	14ms
check forum access:	4ms
check topic access:	4ms
track hit:	91ms
get topic data:	14ms
get forum data:	3ms
get page messages:	560ms
get tp. blocked users:	2ms
others:	9873ms

total:	10633ms