Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Расстановка индексов / 8 сообщений из 8, страница 1 из 1
29.09.2016, 14:16
    #39317837
Gemorroj
Gemorroj
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расстановка индексов
Есть запрос:
Код: sql
1.
2.
3.
4.
5.
6.
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


И результат:
Код: plaintext
1.
2.
3.
4.
+----+-------------+------------+------------+-------+--------------------------------------------------------------------------------------------------------------+---------+---------+------+------+----------+-------------+
| 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) и получаем следующий план запроса:
Код: plaintext
1.
2.
3.
4.
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+-------+----------+----------------------------------------------------+
| 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 .
Получаем следующее:
Код: plaintext
1.
2.
3.
4.
+----+-------------+------------+------------+-------+------------------------+------------------------+---------+------+-------+----------+---------------------------------------+
| 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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.09.2016, 15:33
    #39317934
Gemorroj
Gemorroj
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расстановка индексов
Помогли в другом месте.
В результате запрос получился таким:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.09.2016, 17:36
    #39318047
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расстановка индексов
GemorrojВ результате запрос получился таким
Версия сервера - не догма, их порой обновляют. Рискуешь... возьми запросы в скобки, чтобы гарантировать сортировку и лимит по суммарному запросу.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.09.2016, 17:54
    #39318060
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расстановка индексов
Gemorroj,

Покажите DDL таблицы с индексами.
Сколько записей в таблице и сколько идет в результат, если убрать LIMIT ?
OPTIMIZE TABLE, особенно после создания индексов, не пробовали делать?
Gemorrojиспользуется PRIMARY индекс, для данного запроса (я вообще не совсем понимаю, как оптимизатор на него вышел).Для сортировки по id вышел.
Gemorrojсудя, по EXPLAIN все значительно хуже.А по фактическому времени выполнения?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.09.2016, 18:18
    #39318081
Gemorroj
Gemorroj
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расстановка индексов
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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


Если убрать LIMIT, то EXPLAIN показывает план запроса идентичный 2-му в 1 посте. Как будто сделали USE INDEX.
Всего записей в таблице сейчас 210154
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.09.2016, 18:27
    #39318086
Gemorroj
Gemorroj
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расстановка индексов
Реально если указать USE INDEX запрос отрабатывает за 0.2-0.3 сек.
Если не указывать, то... В SHOW PROCESSLIST показывает "Sending data" уже секунд 500
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2016, 01:21
    #39318243
MasterZiv
MasterZiv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расстановка индексов
[quot Gemorroj]Есть запрос:
Код: sql
1.
2.
3.
4.
5.
6.
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



тебе нужно два индекса

(receiver_id , `status`, shipper_storage_id )
(receiver_id , `status`, consignee_storage_id)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.09.2016, 09:49
    #39318347
Gemorroj
Gemorroj
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расстановка индексов
MasterZiv,
>> тебе нужно два индекса
не работает так. Использует только один из 2-х. EXPLAIN показывает план аналогичный если бы индекс был receiver_id + status.
В случае переписывания запроса через UNION, да, подходят 2 индекса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Расстановка индексов / 8 сообщений из 8, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 484ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1831370]:
 0ms
get settings:
 7ms
get forum list:
 14ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 192ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 33ms
get tp. blocked users:
 1ms
others: 224ms
total:  484ms
x
x
Закрыть


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