ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Почему меняется план на ALL?
12 сообщений из 12, страница 1 из 1
Почему меняется план на ALL?
    #39947387
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Пример. Есть такая БД:
 
Код: 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.
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)

CREATE TABLE `mkd` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)

CREATE TABLE `contragents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_mkd` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_mkd` (`id_mkd`),
  CONSTRAINT `fk_mkd` FOREIGN KEY (`id_mkd`) REFERENCES `mkd` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
)

CREATE TABLE `claims` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_contragent` int(11) DEFAULT NULL,
  `id_user` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_claims_contr` (`id_contragent`),
  KEY `fk_claims_user` (`id_user`),
  CONSTRAINT `fk_claims_contr` FOREIGN KEY (`id_contragent`) REFERENCES `contragents` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_claims_user` FOREIGN KEY (`id_user`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
)

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_contragent` int(11) DEFAULT NULL,
  `id_claim` int(11) DEFAULT NULL,
  `id_user` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ord_contr` (`id_contragent`),
  KEY `fk_ord_user` (`id_user`),
  KEY `fk_ord_claim` (`id_claim`),
  CONSTRAINT `fk_ord_claim` FOREIGN KEY (`id_claim`) REFERENCES `claims` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_ord_contr` FOREIGN KEY (`id_contragent`) REFERENCES `contragents` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_ord_user` FOREIGN KEY (`id_user`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
)


Таким запросом выбираю все жалобы на контрагентов в нужном доме (c.id_mkd=12) и пользователя, кто ее создал:
Код: sql
1.
2.
3.
4.
SELECT x.id, c.id, u.id
FROM claims x
INNER JOIN contragents c ON x.id_contragent=c.id AND c.id_mkd=12
INNER JOIN users u ON x.id_user=u.id


idid(1)id(2)635624463764746386564642681464870346457174651723411725365372546557344
План


Здесь вроде все ок, индексы работают.

Но если усложнить этот запрос, добавив к нему еще выбор приказа, созданного по жалобе (если он есть, потому LEFT JOIN):
Код: sql
1.
2.
3.
4.
5.
SELECT x.id, c.id, u.id, o.id
FROM claims x
INNER JOIN contragents c ON x.id_contragent=c.id AND c.id_mkd=12
INNER JOIN users u ON x.id_user=u.id
LEFT JOIN orders o ON x.id=o.id_claim


idid(1)id(2)id(3)6356244null6376474null6386564null6426814null6487034null6457174null6517234null117253206537254null6557344null
то план становится таким:


Почему поменялся порядок обработки таблиц? Почему теперь сканируется вся таблица claims x (не работает индекс)? Как этого избежать?

PS ANALYZE делал, также как и изменял порядок джойнов - на плане никак не сказалось...
...
Рейтинг: 0 / 0
15.04.2020, 10:54
| Ответить | Цитировать | Написать  
Почему меняется план на ALL?
    #39947403
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Чертовщина какая-то... Со вчерашнего вечера и по момент написания поста последний план не менялся, а сейчас внезапно стал таким, как ожидалось:

Но ничего же в БД не менялось!
...
Рейтинг: 0 / 0
15.04.2020, 11:26
| Ответить | Цитировать | Написать  
Почему меняется план на ALL?
    #39947404
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
потому что
Код: sql
1.
2.
FROM claims x 
LEFT JOIN orders o ON x.id=o.id_claim

подразумевает ВСЕ записи claims
зачем индекс по Х использовать
-------------------------
на сек не успел до посл сообщения....
Значит все же что то случилось ...)
...
Рейтинг: 0 / 0
15.04.2020, 11:26
| Ответить | Цитировать | Написать  
Почему меняется план на ALL?
    #39947409
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Чудеса... сейчас вообще таким стал при тех же результатах:

Что-то закэшировалось что ли?
...
Рейтинг: 0 / 0
15.04.2020, 11:34
| Ответить | Цитировать | Написать  
Почему меняется план на ALL?
    #39947418
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
LiYing,

ну для чистоты экперимента можно пробовать
SELECT SQL_NO_CACHE
хотя как будто меняется приоритет операций JOIN
...
Рейтинг: 0 / 0
15.04.2020, 11:47
| Ответить | Цитировать | Написать  
Почему меняется план на ALL?
    #39947428
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Alex_Ustinov,

SQL_NO_CACHE никак не повлияло.

Тут другое вылезло :) Если на одну жалобу создано несколько приказов (ну бывает у нас такое)), то в выборку естественно попадают эти жалобы НЕ в единственном числе как надо. Правильно ли будет использовать DISTINCT
Код: sql
1.
2.
3.
4.
5.
SELECT DISTINCT x.id, c.id, u.id, o.id
FROM claims x
INNER JOIN contragents c ON x.id_contragent=c.id AND c.id_mkd=12
INNER JOIN users u ON x.id_user=u.id
LEFT JOIN orders o ON x.id=o.id_claim


В плане появляется "Using temporary"


чтобы удалить повторы?
...
Рейтинг: 0 / 0
15.04.2020, 12:04
| Ответить | Цитировать | Написать  
Почему меняется план на ALL?
    #39947454
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
LiYing
Здесь вроде все ок, индексы работают.
Фраза неверная. Она должна быть "несмотря ни на что, индексы-таки используются".

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

Например, для оптимизации запроса
LiYing

Код: sql
1.
2.
3.
4.
SELECT x.id, c.id, u.id
FROM claims x
INNER JOIN contragents c ON x.id_contragent=c.id AND c.id_mkd=12
INNER JOIN users u ON x.id_user=u.id

желательны индексы
contragents (id_mkd, id)
users (id) - имеется
claims (id_contragent, id_user) - или поля в обратном порядке, в зависимости от статистики.
LiYing
Со вчерашнего вечера и по момент написания поста последний план не менялся, а сейчас внезапно стал таким, как ожидалось
Изменилась статистика - изменился и план. В отсутствии оптимальных индексов построитель всегда балансирует на грани.
...
Рейтинг: 0 / 0
15.04.2020, 12:47
| Ответить | Цитировать | Написать  
Почему меняется план на ALL?
    #39947483
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Akina,

спасибо, поэкспериментирую с индексами.

Боюсь наделать излишних индексов, поскольку таблицы используются в сотнях различных запросов с разными критериями выборки... Делать индексы на все возможные ситуации? Как тут поступить, посоветуете что?
...
Рейтинг: 0 / 0
15.04.2020, 13:47
| Ответить | Цитировать | Написать  
Почему меняется план на ALL?
    #39947503
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Индексы - ускоряют SELECT и тормозят всё остальное (INSERT/UPDATE/DELETE).

Так что всё зависит от использования БД.
Если это БД, в которой данные изменяются редко и интерактивно (т.е. чхать на скорость IUD) - самое оно налепить всех индексов, которые нужны хотя бы для одного запроса.
А если БД, наоборот, динамичная, с постоянной корректировкой данных - то вот тут индексов нужен самый минимум. Вернее, нужно искать баланс между тормозами корректировки из-за дохренищи индексов и тормозами выборки из-за отсутствия нужного индекса - да ещё с учётом приоритетов операций, в т.ч. приоритетов внешних. Непросто...
...
Рейтинг: 0 / 0
15.04.2020, 14:39
| Ответить | Цитировать | Написать  
Почему меняется план на ALL?
    #39947517
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Akina
это БД, в которой данные изменяются редко и интерактивно (т.е. чхать на скорость IUD)

Думаю, у нас именно этот вариант. Раз в месяц в пару таблиц разово добавляется 20-30 тысяч записей - это статичные данные. Все остальное построено вокруг данных из этой пары таблиц - в день добавляется/редактируется редко до сотни записей в еще 5 таблицах.
Значит, будем лепить индексы :)
...
Рейтинг: 0 / 0
15.04.2020, 15:04
| Ответить | Цитировать | Написать  
Почему меняется план на ALL?
    #39947557
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Да, учти, что чем больше индексов, тем больше надо времени планировщику для построения реально правильного плана. Так что если знаешь, какие индексы оптимальны для запроса - лучше хинтить. А если не знаешь - выяснять и хинтить.
...
Рейтинг: 0 / 0
15.04.2020, 16:34
| Ответить | Цитировать | Написать  
Почему меняется план на ALL?
    #39947576
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Akina,

спасибо, стратегия понятна - лепить, выяснять и хинтить! :)
...
Рейтинг: 0 / 0
15.04.2020, 17:10
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Почему меняется план на ALL?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=39947409&tid=1828634]:
 0ms
get settings:
 6ms
get forum list:
 11ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 152ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 45ms
get tp. blocked users:
 1ms
others: 300ms
total:  535ms
созд. / загр.: 535ms
x
x
Закрыть


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