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

ReSQL.ru

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

Гость

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

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

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

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

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

31 сообщений из 31, показаны все 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

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

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

#40134108

Akina

Участник

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

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

Рейтинг: 0 / 0

LiYing

Partition pruning при беглом ознакомлении показалось многообещающим, но обнаружилась печалька

А вот тут надо хорошо в затылке почесать, что важнее. FK ведь можно и сэмулировать на триггерах.

...

Рейтинг:

0 / 0

14.02.2022, 23:07

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

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

#40134132

LiYing

Гость

Akina

А вот тут надо хорошо в затылке почесать, что важнее. FK ведь можно и сэмулировать на триггерах.

Это что-то типа такого? https://stackoverflow.com/questions/47230968/how-to-make-a-trigger-work-like-a-foreign-key
Если нет, то можно примерчик такого эмулирования?

...

Рейтинг:

0 / 0

15.02.2022, 08:24

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

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

#40134171

Akina

Участник

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

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

Рейтинг: 0 / 0

LiYing

Это что-то типа такого?

Угу, именно. То, что обычно делается системно, выполняется вручную триггерами. Конечно, полной эмуляции так не получить, но случаи, когда эмуляция невозможна, достаточно редки и экзотичны. Другой вопрос - надо ли реально, ведь поддержка такой эмуляции намного сложнее, и надо иметь очень серьёзные основания для её применения.

Есть и другие способы ускорения. Например, предрасчёт - предварительная агрегация. Например, посуточная, по дате и контрагенту. И соответственно запрос слать в предрасчётную таблицу, где будет явно поменьше записей.

...

Рейтинг:

0 / 0

15.02.2022, 11:23

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

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

#40135383

VDeltsov

Гость

авторLiYing,

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

Я бы для конкретно этого запроса сделал:
либо индекс data_sgrc по колонокам period + id_contragent, а не наоборот, как тут предлагали.
Но индекс такой нужен только при условии, что в реальном запросе из этой таблицы берется только дата и id_contragent, а другие колонки не берутся. Тогда после сканирования куска индекса мы в таблицу вообще не лезем.

Либо партиционирование по дате. При этом внешние ключи, как мне кажется, не должны мешать, так как с точки зрения здравого смысла делают ссылку в таблице с данными на таблицу справочника. А партиционирование мы делаем в таблице с данными, а не в справочнике, поэтому внешний ключ вполне можно будет создать. Если в MySQL даже это нельзя сделать, то это будет очень странно. В любом случае, постепенно базы уходят от внешних ключей с целью повышения производительности. Предполагая, что целостность поддерживается логикой программы. И не требуется дополнительно строить ключи. В OLAP внешние ключи даже если и делают, то только для видимости, и тут же их отключают.

В MS SQL Server я под такое сделал не партиционирование, а кластеризованный индекс по дате + ID, то есть таблица сама является индексом посортированным по дате + ID. Поэтому все запросы за любой период - день, месяц, год - всегда сканируют только нужную часть таблицы. Это было просто чудом. Есть ли такой в MySQL сомневаюсь. Вам тогда партиционирование по дате, как некий аналог этого.

Если уж совсем надо быстро, то я бы переписал запрос так, чтобы сначала просуммировать исходную таблицу, а уже потом на маленьком просуммированном наборе присоединять справочник.

SELECT d.period p, COUNT(DISTINCT c.id_mkd) c1, sum(c3) c2
from (
SELECT d1.period p, d1.id_contragent, count(*) as c3
FROM data_sgrc d1
WHERE d1.period='2021-10-01'
group by d1.period, d1.id_contragent
) d
INNER JOIN contragents c ON d.id_contragent=c.id
GROUP BY d.period

...

Рейтинг:

0 / 0

20.02.2022, 16:28

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

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

#40135482

LiYing

Гость

VDeltsov,

спасибо за советы!
Протестировал предложенный запрос:

№1 для одного месяца

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.

SELECT d.p p, COUNT(DISTINCT c.id_mkd) c1, sum(c3) c2
from (
SELECT d1.period p, d1.id_contragent, count(*) as c3
FROM data_sgrc d1
WHERE d1.period='2021-10-01'
group by d1.period, d1.id_contragent
) d
INNER JOIN contragents c ON d.id_contragent=c.id
GROUP BY d.p

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1PRIMARY<derived2>nullALLnullnullnullnull110843100.00Using where; Using filesort1PRIMARYcnulleq_refPRIMARYPRIMARY4d.id_contragent1100.00null2DERIVEDd1nullindexid_contr_perid_contr_per9null110843610.00Using where; Using index

№2 для трех месяцев

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.

SELECT d.p p, COUNT(DISTINCT c.id_mkd) c1, sum(c3) c2
from (
SELECT d1.period p, d1.id_contragent, count(*) as c3
FROM data_sgrc d1
WHERE d1.period BETWEEN '2021-10-01' AND '2021-12-01'
group by d1.period, d1.id_contragent
) d
INNER JOIN contragents c ON d.id_contragent=c.id
GROUP BY d.p

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1PRIMARY<derived2>nullALLnullnullnullnull123147100.00Using where; Using filesort1PRIMARYcnulleq_refPRIMARYPRIMARY4d.id_contragent1100.00null2DERIVEDd1nullindexid_contr_perid_contr_per9null110843611.11Using where; Using index; Using temporary
№1 > Query Time: 1,08s
№2 > Query Time: 2,627s
Т.е. запросы выполняются еще дольше. Первый, для периода в 1 месяц, в 2 раза дольше, а второй - незначительно, но все равно дольше.

...

Рейтинг:

0 / 0

21.02.2022, 08:36

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

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

#40135860

VDeltsov

Гость

LiYing, увидел, что у вас тут миллион клиентов, поэтому быстрее чем за секунду вы тут вряд ли получите.

Самый быстрый вариант - это исключительно для тестов сделать два индекса, чтобы узнать самое минимальное время выполнения:
data_sgrc по колонкам period + id_contragent
contragents по колонкам id + id_mkd

При этом таблицы вообще не будут считываться, только соединение индексов.
Посмотреть, какое будет минимальное время второго выполнения, когда нужные куски индекса будут уже в памяти.
Если это итак будет в районе 1 секунды, то будет ясно, что это - предел.

И останется только подсказками попробовать два варианта соединения - hash join (и full index scan на таблицу клиентов) и nested loop (на таблицу клиентов). Как в MySQL делать подсказки - не знаю.

Кстати, в MySQL есть подсказка типа PARALLEL 8, как в ORACLE, или option (maxdop 8), как в MS SQL?

Следующий вариант, боюсь посоветовать, так как за колоночные таблицы в соседних ветках меня Ролин Хун затролллил.
Ровно аналогичный запрос к колоночным таблицам MS SQL Server, где в таблице фактов 440 млн строк, в таблице клиентов 1,8 млн.
В результате фильтра по дате (за год) получаю 60 млн строк, в результате первой группировки получают 1 миллион строк (миллион сочетаний дата+клиент), затем соединяю на клиентов и группирую по дням, делаю distinct id_mkd - получают 335 строк.
И всё это за 0,5 секунд при максимальной параллельности на 12 ядер процессора.
Если брать за один большой день (31 декабря), где 700 000 строк данных, то результат будет 0,090 секунд.
На одном старом процессоре (2006 года выпуска) с отключенной параллельностью: 2 секунды (для 60 млн строк) и 0,260 секунд (для 700 тыс строк).

При этом никакого партиционирования, никаких дополнительных индексов вообще не требуется.
Все данные с сжатом в 20 раз виде итак помещаются в память.

Но это всё в MS SQL Server в колоночных таблицах.

В MySQL колоночные таблицы есть только в MariaDB в виде отдельно подключаемого модуля, и только в Линуксе, и еще и ним надо научиться работать. Но зато OLAP-запросы будут супер быстрыми. При условии, что вставляете данные пачками, а не по одной.
Если попробуете их - расскажите тут или в моей ветке https://www.sql.ru/forum/1342038/

...

Рейтинг:

0 / 0

22.02.2022, 13:53

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

31 сообщений из 31, показаны все 2 страниц

все

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

Цитировать

Написать

Автор*:

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

Тема*:

Сообщение

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

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

Автор:

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

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

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

Вложение:

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

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

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

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

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

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

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

Start [/forum/topic.php?all=1&fid=47&tid=1827770]:	0ms
get settings:	4ms
get forum list:	6ms
check forum access:	2ms
check topic access:	2ms
track hit:	40ms
get topic data:	6ms
get forum data:	1ms
get page messages:	584ms
get tp. blocked users:	1ms
others:	270ms

total:	916ms