Гость
Войти | Профиль | Очистить
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Почему меняется план на ALL? / 12 сообщений из 12, страница 1 из 1
15.04.2020, 10:54
    #39947387
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему меняется план на ALL?
Пример. Есть такая БД:
 
Код: 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, 11:26
    #39947403
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему меняется план на ALL?
Чертовщина какая-то... Со вчерашнего вечера и по момент написания поста последний план не менялся, а сейчас внезапно стал таким, как ожидалось:

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

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

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

ну для чистоты экперимента можно пробовать
SELECT SQL_NO_CACHE
хотя как будто меняется приоритет операций JOIN
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.04.2020, 12:04
    #39947428
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему меняется план на ALL?
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:47
    #39947454
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему меняется план на ALL?
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, 13:47
    #39947483
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Почему меняется план на ALL?
Akina,

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

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

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

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

спасибо, стратегия понятна - лепить, выяснять и хинтить! :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Почему меняется план на ALL? / 12 сообщений из 12, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 446ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1828634]:
 0ms
get settings:
 11ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 40ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 51ms
get tp. blocked users:
 2ms
others: 302ms
total:  446ms
x
x
Закрыть


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