ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Неочевидная проблема с индексами
15 сообщений из 15, страница 1 из 1
Неочевидная проблема с индексами
    #40097000
freebsdd
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
freebsdd
Участник
Добррый всем день! Прошу мудрых советов

Дано:
version: mariadb-10.5.10-1
Есть 2 идентичные таблицы "test__addressobject" и "test2__addressobject"

В запросах по идее должны индексы подключиться на поля AOID, но не подключаются, почему? Что случилось? Где я затупил?
Ps: FORCE INDEX - Тоже не работает

По этому запросу можно понять, какие изменения есть в первой таблице от второй
Код: sql
1.
2.
3.
4.
5.
EXPLAIN EXTENDED
select d.*
from delta.test__addressobject d /*FORCE INDEX(PRIMARY)*/
left join delta.test2__addressobject f /*FORCE INDEX(PRIMARY)*/ on d.AOID = f.AOID
where f.AOID is null;




Вот здесь видно, что первая таблица не использует индекс:

Код: html
1.
2.
3.
id	select_type	table	type	possible_keys	key	key_len	ref		rows	filtered	Extra
1	SIMPLE		d	ALL	NULL		NULL	NULL	NULL		100	100	
1	SIMPLE		f	eq_ref	AOID		AOID	146	delta.d.AOID	1	100		Using where; Using index; Not exists



Вот структура:

Код: plsql
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.
--
-- База данных: `delta`
--
-- --------------------------------------------------------
--
-- Структура таблицы `test2__addressobject`
--

CREATE TABLE `test2__addressobject` (
  `AOGUID` varchar(36) DEFAULT NULL,
  `PARENTGUID` varchar(36) DEFAULT NULL,
  `AOID` varchar(36) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Классификатор адресообразующих элементов';

--
-- Индексы сохранённых таблиц
--

--
-- Индексы таблицы `test2__addressobject`
--
ALTER TABLE `test2__addressobject`
  ADD PRIMARY KEY (`AOID`);


/*******************************************/



CREATE TABLE `test__addressobject` (
  `AOGUID` varchar(36) DEFAULT NULL,
  `PARENTGUID` varchar(36) DEFAULT NULL,
  `AOID` varchar(36) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Классификатор адресообразующих элементов';

--
-- Индексы сохранённых таблиц
--

--
-- Индексы таблицы `test__addressobject`
--
ALTER TABLE `test__addressobject`
  ADD PRIMARY KEY (`AOID`);
...
Рейтинг: 0 / 0
12.09.2021, 19:13
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40097007
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
freebsdd,

так а зачем для чтения всего d использовать индекс?
...
Рейтинг: 0 / 0
12.09.2021, 20:17
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40097034
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
freebsdd
здесь видно, что первая таблица не использует индекс
И не должна.

Если основная задача - оптимизировать запрос, то его следует переписать на WHERE NOT EXISTS.
...
Рейтинг: 0 / 0
13.09.2021, 07:48
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40097124
freebsdd
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
freebsdd
Участник
Melkij,

Так ведь запрос сначала смотрит, чего нет в основной таблице по AOID, значит и должен использовать индекс, или я не верно представляю алгоритм действия работы mysql?
...
Рейтинг: 0 / 0
13.09.2021, 12:00
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40097131
freebsdd
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
freebsdd
Участник
Akina,

Как я себе вижу это всё действо, это не совсем оптимизация, т.к. при WHERE NOT EXISTS mysql каждый раз во время просмотра каждой записи таблицы d выполняет запрос в таблицу f, чтоб узнать есть ли такая запись. Поправьте меня, если я ошибаюсь

Чтоб имели представление, в таблице f на данный момент около 90 млн записей (и постоянно пополняется), а в таблице d в среднем около 7-9 млн записей (включая новые и изменённые)

По Вашему совету сделал запрос:

Код: sql
1.
2.
3.
4.
EXPLAIN EXTENDED
select d.*
from delta.test__addressobject d
where not EXISTS (select AOID from delta.test2__addressobject f where f.AOID = d.AOID);



Код: html
1.
2.
3.
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra	
1	PRIMARY		d	ALL	NULL		NULL	NULL	NULL	100	100		Using where	
2	MATERIALIZED	f	index	PRIMARY,AOID	AOID	146	NULL	150	100		Using index



Вижу d не использует индексы, что вроде логично, т.к. тут идёт перебор всех записей от начала и до конца + каждое обращение к индексу таблицы f. Вот честно я не вижу оптимизации работы, или я ошибаюсь?
...
Рейтинг: 0 / 0
13.09.2021, 12:16
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40097177
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
freebsdd
Melkij,

Так ведь запрос сначала смотрит, чего нет в основной таблице по AOID, значит и должен использовать индекс, или я не верно представляю алгоритм действия работы mysql?

Что такое "основная таблица"? d или f?

В любом случае, опишите просто на словах такой алгоритм вычитания множеств, чтобы был смысл использовать какой бы то ни было индекс по левому множеству.
Всё равно читать весь d целиком.

Ну, merge join можно было бы попробовать применить. Если бы mariadb его ещё умела
...
Рейтинг: 0 / 0
13.09.2021, 13:13
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40097181
freebsdd
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
freebsdd
Участник
Melkij,

В Таблице f - Основные данные (это основная таблица), а в таблице d - изменённые и новые данные (туда заливаются обновления), таблицы полностью идентичные. Задача: быстро обновить данные из таблицы d в таблицу f, без индексов это происходит несколько часов.
...
Рейтинг: 0 / 0
13.09.2021, 13:20
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40097338
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
freebsdd
Так ведь запрос сначала смотрит, чего нет в основной таблице по AOID
Кто Вам сказал такую глупость?
И вообще - как правило, реальный порядок выполнения запроса имеет мало общего с его текстом. Иногда даже план выполнения, и то не очень-то адекватно отражает реальный порядок выполнения.
...
Рейтинг: 0 / 0
13.09.2021, 18:56
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40097352
freebsdd
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
freebsdd
Участник
Akina,

Akina
freebsdd
Так ведь запрос сначала смотрит, чего нет в основной таблице по AOID
Кто Вам сказал такую глупость?
И вообще - как правило, реальный порядок выполнения запроса имеет мало общего с его текстом. Иногда даже план выполнения, и то не очень-то адекватно отражает реальный порядок выполнения.



Малость Вас не понял, поясните пожалуйста, вот запрос:

Код: sql
1.
2.
3.
4.
select d.*
from delta.test__addressobject d /*FORCE INDEX(PRIMARY)*/
left join delta.test2__addressobject f /*FORCE INDEX(PRIMARY)*/ on d.AOID = f.AOID
where f.AOID is null;



1. Тут логично и очевидно следующее: сравниваем таблицы f и d по их первичным ключам AOID с помощью LEFT JOIN, благодаря чему мы получим отсутствующие аналогичные значения таблицы f в виде null, опираясь на это, мы получаем все значения таблицы d которых нет в таблице f (исключая все подобные с помощью AOID is null), в чём я заблуждаюсь?
2. И ещё момент - если у обоих таблиц AOID первичные ключи, то по логике вещей он должен их использовать при left join

В 1 и 2 вопросе, что я упускаю?
...
Рейтинг: 0 / 0
13.09.2021, 19:33
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40097365
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
freebsdd
если у обоих таблиц AOID первичные ключи, то по логике вещей он должен их использовать при left join

Да никому он ничего не обязан. Тем более при SELECT * - вот нафига нужен индекс?
...
Рейтинг: 0 / 0
13.09.2021, 20:28
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40097374
freebsdd
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
freebsdd
Участник
Akina
freebsdd
если у обоих таблиц AOID первичные ключи, то по логике вещей он должен их использовать при left join

Да никому он ничего не обязан. Тем более при SELECT * - вот нафига нужен индекс?


Нет мочи пока понять ход Ваших мыслей, скажите причём тут SELECT *? Ведь индексы должны учитываться в теле, всё что после from, разве нет?
...
Рейтинг: 0 / 0
13.09.2021, 21:37
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40097395
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
freebsdd
Ведь индексы должны учитываться в теле, всё что после from, разве нет?
Вот бредовая какая-то фраза... "индексы должны учитываться" - это вообще о чём?

Индексы - всего лишь дополнительная структура, способная ускорить выполнение запроса. Но и способная замедлить, если его использовать не к месту. В данном случае - однозначно не к месту. Вот и не используется.
...
Рейтинг: 0 / 0
13.09.2021, 23:01
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40098027
freebsdd
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
freebsdd
Участник
Akina
freebsdd
Ведь индексы должны учитываться в теле, всё что после from, разве нет?
Вот бредовая какая-то фраза... "индексы должны учитываться" - это вообще о чём?

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



Ладно, что тогда сделать можно, чтоб ускорить выполнение запроса? Несколько часов это очень долго
...
Рейтинг: 0 / 0
16.09.2021, 14:15
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40098058
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Попробуйте переписать на NOT EXISTS:
Код: sql
1.
2.
3.
4.
5.
SELECT *
FROM delta.test__addressobject d
WHERE NOT EXISTS ( SELECT NULL
                   FROM delta.test2__addressobject f
                   WHERE d.AOID = f.AOID )


Правда, скорее всего ничего не изменится. Всё же связывание по VARCHAR - процесс заведомо небыстрый. Там же UUID? попробуйте конвертировать в BINARY(16) - всё побыстрее будет.

Если такой тип запросов - и частый, и критичный по скорости, подумайте о переопределении данных и дополнительной связующей таблице, обновляемой триггерами.
...
Рейтинг: 0 / 0
16.09.2021, 15:17
| Ответить | Цитировать | Написать  
Неочевидная проблема с индексами
    #40098095
freebsdd
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
freebsdd
Участник
Akina
Попробуйте переписать на NOT EXISTS:
Код: sql
1.
2.
3.
4.
5.
SELECT *
FROM delta.test__addressobject d
WHERE NOT EXISTS ( SELECT NULL
                   FROM delta.test2__addressobject f
                   WHERE d.AOID = f.AOID )


Правда, скорее всего ничего не изменится. Всё же связывание по VARCHAR - процесс заведомо небыстрый. Там же UUID? попробуйте конвертировать в BINARY(16) - всё побыстрее будет.

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


Да там UUID, хорошо, если других вариантов нет, попробую и так и так, понаблюдаю, спасибо Akina!
...
Рейтинг: 0 / 0
16.09.2021, 16:45
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Неочевидная проблема с индексами
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=40097007&tid=1827948]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 161ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 57ms
get tp. blocked users:
 2ms
others: 292ms
total:  560ms
созд. / загр.: 560ms
x
x
Закрыть


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