SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 29.09.2016 14:04:21

Gemorroj
Участник
Зарегистрирован: 29.09.2016
Сообщений: 5

Расстановка индексов

Есть запрос:

EXPLAIN SELECT * FROM e_waybills
WHERE receiver_id = 657
AND `status` IN ('accept','need_change','sent','answered','canceled')
AND (shipper_storage_id = 1629 OR consignee_storage_id = 1629 OR shipper_storage_id IS NULL OR consignee_storage_id IS NULL)
ORDER BY id DESC
LIMIT 0,100

И результат:

+----+-------------+------------+------------+-------+--------------------------------------------------------------------------------------------------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys                                                                                                | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+--------------------------------------------------------------------------------------------------------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | e_waybills | NULL       | index | unique_document,receiver_pk,receiver_grid_idx,shipper_storage_id,consignee_storage_id,receiver_grid_full_idx | PRIMARY | 4       | NULL |  840 |     4.44 | Using where |
+----+-------------+------------+------------+-------+--------------------------------------------------------------------------------------------------------------+---------+---------+------+------+----------+-------------+

Мне не нравится, что используется PRIMARY индекс, для данного запроса (я вообще не совсем понимаю, как оптимизатор на него вышел).
Есть индекс receiver_grid_idx на 2 колонки - receiver_id и status.
Говорим mysql его использовать (USE INDEX) и получаем следующий план запроса:

+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+-------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys     | key               | key_len | ref  | rows  | filtered | Extra                                              |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | e_waybills | NULL       | range | receiver_grid_idx | receiver_grid_idx | 6       | NULL | 20478 |    34.39 | Using index condition; Using where; Using filesort |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+-------+----------+----------------------------------------------------+

Т.е. судя, по EXPLAIN все значительно хуже.
Добавляем для эксперимента индекс по receiver_id + status + shipper_storage_id + consignee_storage_id + id.
Получаем следующее:

+----+-------------+------------+------------+-------+------------------------+------------------------+---------+------+-------+----------+---------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys          | key                    | key_len | ref  | rows  | filtered | Extra                                 |
+----+-------------+------------+------------+-------+------------------------+------------------------+---------+------+-------+----------+---------------------------------------+
|  1 | SIMPLE      | e_waybills | NULL       | range | receiver_grid_full_idx | receiver_grid_full_idx | 6       | NULL | 21631 |    34.39 | Using index condition; Using filesort |
+----+-------------+------------+------------+-------+------------------------+------------------------+---------+------+-------+----------+---------------------------------------+

Что ситуацию практически не меняет.
Что можете посоветовать для оптимизации выборки?
MySQL версии 5.7

Неактивен

 

#2 29.09.2016 14:14:20

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

Re: Расстановка индексов

Есть еще директивы ignore index (можно попробовать через нее запретить primary использовать) и force_index.

Show create table `e_waybills`;

Покажите результат.

Еще - с OR оптимизатор хреново дружит (неоднократно на это налетали), попробуйте через UNION переколдовать запрос.

Так-то тут хорошо должен сработать (особливо если переписать через UNION исходный запрос)
индекс (receiver_id,shipper_storage_id),
а может быть и
индекс (receiver_id,shipper_storage_id,id)


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

Неактивен

 

#3 29.09.2016 14:15:13

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Расстановка индексов

А чем вам не нравится первый вариант?
Оптимизатор решил обходить таблицу в порядке уменьшения id, каждая строка проверяется на соответствие условию where, как только наберется 100 строк выполнение запроса прекращается.

P.S. А в честь чего такой ник?

Неактивен

 

#4 29.09.2016 14:25:25

Gemorroj
Участник
Зарегистрирован: 29.09.2016
Сообщений: 5

Re: Расстановка индексов

CREATE TABLE `e_waybills` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Id',
 `status` enum('draft','draft_answer','sent','accept','answered','...') NOT NULL DEFAULT 'draft' COMMENT 'Статусы',
 `shipper_storage_id` int(10) unsigned DEFAULT NULL COMMENT 'ID склада отправителя',
 `consignee_storage_id` int(10) unsigned DEFAULT NULL COMMENT 'ID склада получателя',
 `receiver_id` int(10) unsigned DEFAULT NULL COMMENT 'ID организации, получающей документ',
 `sender_id` int(10) unsigned DEFAULT NULL COMMENT 'ID организации, создавшей/отправившей документ',
 ...
 PRIMARY KEY (`id`),
 KEY `receiver_pk` (`receiver_id`),
 KEY `sender_pk` (`sender_id`),
 KEY `receiver_grid_idx` (`receiver_id`,`status`) USING BTREE,
 KEY `sender_grid_idx` (`sender_id`,`status`) USING BTREE,
 KEY `shipper_storage_id` (`shipper_storage_id`),
 KEY `consignee_storage_id` (`consignee_storage_id`),
 KEY `receiver_grid_full_idx` (`receiver_id`,`status`,`shipper_storage_id`,`consignee_storage_id`,`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=212660 DEFAULT CHARSET=utf8

в slow_log попадают такие вещи:

# Query_time: 3.833072  Lock_time: 0.000434 Rows_sent: 98  Rows_examined: 209865

что я пытаюсь исправить.
Мне кажется, что EXPLAIN врет на счет первого плана запроса (мало выделено памяти в каком-то/каких-то буферах?)

P.S. ник - так сказать legacy из юности (когда-то было смешно).

Отредактированно Gemorroj (29.09.2016 16:29:50)

Неактивен

 

#5 29.09.2016 14:37:35

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

Re: Расстановка индексов

vasya написал:

А чем вам не нравится первый вариант?
Оптимизатор решил обходить таблицу в порядке уменьшения id, каждая строка проверяется на соответствие условию where, как только наберется 100 строк выполнение запроса прекращается.

vasya, ты имеешь в виду, что mysql так будет просто идти по записям последовательно от самого большого id до самого маленького (используя primary key в качестве индекса по id), и когда нафильтрует нужное количество - остановится, да?

Разве это будет лучше, чем использовать приведенную мной связку? Я к тому, что может все же стоит здесь помочь оптимизатору, подсунув ему грамотный индекс?


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

Неактивен

 

#6 29.09.2016 14:40:46

Gemorroj
Участник
Зарегистрирован: 29.09.2016
Сообщений: 5

Re: Расстановка индексов

Действительно, экспериментирую с UNION, получаются хорошие результаты. Позже отпишусь что получилось.

Неактивен

 

#7 29.09.2016 14:42:05

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Расстановка индексов

explain может ошибаться на счет кол-ва строк, которые нужно будет проверить - это оценка. В slow_log точная цифра по итогу выполнения запроса.
Раз пришлось прочитать Rows_examined: 209865 строк, т.е. почти всю таблицу, то значит нужно переписывать запрос через UNION как советовал deadka

Неактивен

 

#8 29.09.2016 14:46:53

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

Re: Расстановка индексов

UNION - это да, еще про сам запрос тут с ключиками бы поиграться.
У Вас есть

KEY `receiver_grid_full_idx` (`receiver_id`,`status`,`shipper_storage_id`,`consignee_storage_id`,`id`) USING BTREE,

И есть условие в WHERE, где на receiver_id прямое равенство, а на status - непрямое. Поэтому думаю, что
до третьей части индекса
shipper_storage_id
уже добраться не получиться.

Так что тут вот если бы поменять местами `status`и `shipper_storage_id` - может веселей получиться, но опять же, на explain'ы смотреть надо.


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

Неактивен

 

#9 29.09.2016 14:49:50

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Расстановка индексов

deadka написал:

vasya, ты имеешь в виду, что mysql так будет просто идти по записям последовательно от самого большого id до самого маленького (используя primary key в качестве индекса по id), и когда нафильтрует нужное количество - остановится, да?

Да.


deadka написал:

Разве это будет лучше, чем использовать приведенную мной связку? Я к тому, что может все же стоит здесь помочь оптимизатору, подсунув ему грамотный индекс?

Зависит от распределения данных. Например, если под условие where попадает значительная часть таблицы, то такой подход выгоден, так как прочитано будет кол-во строк сопоставимое со значение limit (на что указывал первый explain - rows = 840).

Из первого поста на основании "не нравится" было неясно есть ли проблема с производительностью, а сразу расписывать все варианты мне было лень. А так, да, ты прав.

Неактивен

 

#10 29.09.2016 14:52:02

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

Re: Расстановка индексов

Ага, спасибо. Ну что же, подождём explain'ов от ТС.


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

Неактивен

 

#11 29.09.2016 15:06:22

Gemorroj
Участник
Зарегистрирован: 29.09.2016
Сообщений: 5

Re: Расстановка индексов

По shipper_storage_id/consignee_storage_id эта часть запроса на реальных данных редко встречается, много чаще запрос будет только на receiver_id и status.
Сделал 2 индекса
- receiver_id + status + shipper_storage_id
- receiver_id + status + consignee_storage_id


EXPLAIN
    SELECT id FROM e_waybills
    WHERE receiver_id = 657
    AND `status` IN ('accept','need_change','sent','answered','canceled')
    AND (shipper_storage_id = 1629 OR shipper_storage_id IS NULL)
UNION DISTINCT
    SELECT id FROM e_waybills
    WHERE receiver_id = 657
    AND `status` IN ('accept','need_change','sent','answered','canceled')
    AND (consignee_storage_id = 1629 OR consignee_storage_id IS NULL)
ORDER BY id DESC
LIMIT 0,100\G;
 


*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: e_waybills
   partitions: NULL
         type: range
possible_keys: unique_document,receiver_pk,receiver_grid_idx,shipper_storage_id,receiver_grid_shipper_idx,receiver_grid_consignee_idx
          key: receiver_grid_shipper_idx
      key_len: 11
          ref: NULL
         rows: 20
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: e_waybills
   partitions: NULL
         type: range
possible_keys: unique_document,receiver_pk,receiver_grid_idx,consignee_storage_id,receiver_grid_shipper_idx,receiver_grid_consignee_idx
          key: receiver_grid_consignee_idx
      key_len: 11
          ref: NULL
         rows: 75
     filtered: 100.00
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary; Using filesort
3 rows in set, 1 warning (0.01 sec)

Using temporary; Using filesort - так понимаю буфер какой-то подкрутить надо

Неактивен

 

#12 29.09.2016 15:21:26

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Расстановка индексов

это значит, что происходит файловая сортировка с использованием временной таблицы
буфера подкручивать нужно только если таблица валится на диск
explain этого не показывает, смотреть нужно через show status

Неактивен

 

#13 29.09.2016 15:27:19

Gemorroj
Участник
Зарегистрирован: 29.09.2016
Сообщений: 5

Re: Расстановка индексов

От Using temporary в случае UNION в mysql никак не избавиться, на сколько я понимаю?
В целом, запрос работает шустро и проблема решена. Большое спасибо за оперативную и квалифицированную помощь)

Неактивен

 

#14 29.09.2016 15:31:49

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Расстановка индексов

нет, никак не избавиться, но сам по себе он не страшен
это если бы выбиралось много строк и для каждой не только id, но и все остальные текстовые поля и потом всё это сортировалось. сваливаясь на жесткий, тогда было бы плохо.

Неактивен

 

Board footer

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