SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 16.11.2021 22:17:44

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Не используется левая часть составного индекса при JOIN [решено]

Коллеги, наткнулся на странную, на мой взгляд, ситуацию, когда при JOIN простой индекс на колонке используется, а составной, где эта колонка идет первой - нет.

upd: решено: нужно выполнить ANALYZE TABLE (спасибо deadka).

Есть две таблицы - заказы и товары в заказах:

Код:

CREATE TABLE `orders` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `client_id` int unsigned NOT NULL,
  # есть еще колонки, но они отношения к делу не имеют
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`)
) ENGINE=MyISAM

Код:

 CREATE TABLE `order_items` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `order_id` int unsigned NOT NULL DEFAULT '0',
  `prod_id` int unsigned NOT NULL DEFAULT '0',
  `quantity` int NOT NULL DEFAULT '0',
  `1C_sale_cost` decimal(8,0) DEFAULT NULL,
  `1C_sale_code` char(11) DEFAULT NULL,
  `1C_sale_return_code` char(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `order_id_2` (`order_id`,`prod_id`)
) ENGINE=MyISAM

Размер таблиц - ~300 т. и ~400 т. записей соответственно.

Нужно посчитать сумму продаж для конкретного клиента, для чего используется вот такой запрос:

Код:

SELECT SUM(oi.1C_sale_cost * oi.quantity)
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.client_id = ...
  AND oi.1C_sale_code IS NOT NULL
  AND oi.1C_sale_return_code IS NULL

При вышеуказанной структуре таблиц запрос выполняется с полным сканированием таблицы order_items. EXPLAIN запроса:

Код:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: oi
   partitions: NULL
         type: ALL
possible_keys: order_id_2
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 392367
     filtered: 9.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: o
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,client_id
          key: PRIMARY
      key_len: 4
          ref: some_db.oi.order_id
         rows: 1
     filtered: 5.00
        Extra: Using where

Причем оптимизатор подумывал использовать ключ order_id_2, но почему-то не стал. Ни USE INDEX, ни FORCE INDEX не помогли.

Однако стоит добавить на колонку order_id простой индекс:

Код:

ALTER TABLE order_items ADD KEY (order_id)

Как оптимизатор его прекрасно использует! EXPLAIN того же запроса после добавления индекса (обратите внимание: порядок следования таблиц поменялся):

Код:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: o
   partitions: NULL
         type: ref
possible_keys: PRIMARY,client_id
          key: client_id
      key_len: 4
          ref: const
         rows: 164
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: oi
   partitions: NULL
         type: ref
possible_keys: order_id_2,order_id
          key: order_id
      key_len: 4
          ref: some_db.o.id
         rows: 1
     filtered: 9.00
        Extra: Using where

Размер key_buffer_size проверял, он достаточен, поэтому версия, что индекс вдруг не используется из-за того, что не влезает в память, отпадает.

Никак не могу взять в толк, почему СУБД игнорирует составной ключ и использует только простой. По моим понятиям, левой части составного ей вполне должно хватать.

Версия MySQL - 8.0.22.

Может кто-нибудь пролить свет на эту ситуацию?

Отредактированно LazY (17.11.2021 22:43:36)

Неактивен

 

#2 17.11.2021 04:20:41

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Не используется левая часть составного индекса при JOIN [решено]

LazY, Подозреваю, что ты это делал, но всё же - если сделать analyze table на обе таблицы или просто пересобрать обе таблицы (ну или через pt-online-schema-change пропустить) - меняется что-то?

И - что показывает explain format=json?

И какой explain если сделать use/force index?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#3 17.11.2021 20:57:28

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Не используется левая часть составного индекса при JOIN [решено]

Чудеса творит ANALYZE TABLE! EXPLAIN запроса стал как после добавления второго индекса, то есть, такой, как надо.

Вообще не подумал в эту сторону, а как всё просто оказалось.

deadka, низкий тебе поклон!

Неактивен

 

#4 17.11.2021 21:19:55

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Не используется левая часть составного индекса при JOIN [решено]

Славно smile.

Собственно, когда довелось на эту тему беседовать с перконистом, то алгоритм он говорил такой (в случае если вот как у тебя, если идёт гон с подбором индекса):
1) пересобрать табличку или optimize/analyze. Пересборке я верю больше, pt-online-schema-change в помощь.
2) если не помогло, то force/use/ignore index, straight_join итд.
3) если и тут не получается, то уже пытаться менять на свой страх конфиг.

Кстати, я правильно понимаю, что у тебя в эти таблички не только последовательная запись?
Там и DELETE и UPDATE'ы ведь имеются?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#5 17.11.2021 22:45:38

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Не используется левая часть составного индекса при JOIN [решено]

1) пересобрать табличку или optimize/analyze. Пересборке я верю больше, pt-online-schema-change в помощь.
2) если не помогло, то force/use/ignore index, straight_join итд.

В моем случае эти пункты надо поменять местами.
Я пробовал USE/FORCE INDEX, не помогало. А ANALYZE TABLE помогло.

Кстати, я правильно понимаю, что у тебя в эти таблички не только последовательная запись?
Там и DELETE и UPDATE'ы ведь имеются?

Систематически - нет. Только как редкая разовая операция.

Неактивен

 

#6 18.11.2021 08:26:06

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Не используется левая часть составного индекса при JOIN [решено]

Не, я имел в виду, что надо начинать с analyze/optimize - если что-то идёт не так.
А дальше уже пытаться шаманить с остальным.

>Систематически - нет. Только как редкая разовая операция.
А вот тогда прямо и непонятно - кто ж тебе статистику индексов испортиль...


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson