|
Задавайте вопросы, мы ответим
Вы не зашли.
Есть запрос:
+----+-------------+------------+------------+-------+--------------------------------------------------------------------------------------------------------------+---------+---------+------+------+----------+-------------+
| 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
Неактивен
Есть еще директивы ignore index (можно попробовать через нее запретить primary использовать) и force_index.
Неактивен
А чем вам не нравится первый вариант?
Оптимизатор решил обходить таблицу в порядке уменьшения id, каждая строка проверяется на соответствие условию where, как только наберется 100 строк выполнение запроса прекращается.
P.S. А в честь чего такой ник?
Неактивен
# 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)
Неактивен
vasya написал:
А чем вам не нравится первый вариант?
Оптимизатор решил обходить таблицу в порядке уменьшения id, каждая строка проверяется на соответствие условию where, как только наберется 100 строк выполнение запроса прекращается.
vasya, ты имеешь в виду, что mysql так будет просто идти по записям последовательно от самого большого id до самого маленького (используя primary key в качестве индекса по id), и когда нафильтрует нужное количество - остановится, да?
Разве это будет лучше, чем использовать приведенную мной связку? Я к тому, что может все же стоит здесь помочь оптимизатору, подсунув ему грамотный индекс?
Неактивен
Действительно, экспериментирую с UNION, получаются хорошие результаты. Позже отпишусь что получилось.
Неактивен
explain может ошибаться на счет кол-ва строк, которые нужно будет проверить - это оценка. В slow_log точная цифра по итогу выполнения запроса.
Раз пришлось прочитать Rows_examined: 209865 строк, т.е. почти всю таблицу, то значит нужно переписывать запрос через UNION как советовал deadka
Неактивен
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'ы смотреть надо.
Неактивен
deadka написал:
vasya, ты имеешь в виду, что mysql так будет просто идти по записям последовательно от самого большого id до самого маленького (используя primary key в качестве индекса по id), и когда нафильтрует нужное количество - остановится, да?
Да.
deadka написал:
Разве это будет лучше, чем использовать приведенную мной связку? Я к тому, что может все же стоит здесь помочь оптимизатору, подсунув ему грамотный индекс?
Зависит от распределения данных. Например, если под условие where попадает значительная часть таблицы, то такой подход выгоден, так как прочитано будет кол-во строк сопоставимое со значение limit (на что указывал первый explain - rows = 840).
Из первого поста на основании "не нравится" было неясно есть ли проблема с производительностью, а сразу расписывать все варианты мне было лень. А так, да, ты прав.
Неактивен
Ага, спасибо. Ну что же, подождём explain'ов от ТС.
Неактивен
По shipper_storage_id/consignee_storage_id эта часть запроса на реальных данных редко встречается, много чаще запрос будет только на receiver_id и status.
Сделал 2 индекса
- receiver_id + status + shipper_storage_id
- receiver_id + status + consignee_storage_id
*************************** 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 - так понимаю буфер какой-то подкрутить надо
Неактивен
это значит, что происходит файловая сортировка с использованием временной таблицы
буфера подкручивать нужно только если таблица валится на диск
explain этого не показывает, смотреть нужно через show status
Неактивен
От Using temporary в случае UNION в mysql никак не избавиться, на сколько я понимаю?
В целом, запрос работает шустро и проблема решена. Большое спасибо за оперативную и квалифицированную помощь)
Неактивен
нет, никак не избавиться, но сам по себе он не страшен
это если бы выбиралось много строк и для каждой не только id, но и все остальные текстовые поля и потом всё это сортировалось. сваливаясь на жесткий, тогда было бы плохо.
Неактивен
