ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Расстановка индексов
8 сообщений из 8, страница 1 из 1
Расстановка индексов
    #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, 14:16
| Ответить | Цитировать | Написать  
Расстановка индексов
    #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, 15:33
| Ответить | Цитировать | Написать  
Расстановка индексов
    #39318047
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
GemorrojВ результате запрос получился таким
Версия сервера - не догма, их порой обновляют. Рискуешь... возьми запросы в скобки, чтобы гарантировать сортировку и лимит по суммарному запросу.
...
Рейтинг: 0 / 0
29.09.2016, 17:36
| Ответить | Цитировать | Написать  
Расстановка индексов
    #39318060
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Gemorroj,

Покажите DDL таблицы с индексами.
Сколько записей в таблице и сколько идет в результат, если убрать LIMIT ?
OPTIMIZE TABLE, особенно после создания индексов, не пробовали делать?
Gemorrojиспользуется PRIMARY индекс, для данного запроса (я вообще не совсем понимаю, как оптимизатор на него вышел).Для сортировки по id вышел.
Gemorrojсудя, по EXPLAIN все значительно хуже.А по фактическому времени выполнения?
...
Рейтинг: 0 / 0
29.09.2016, 17:54
| Ответить | Цитировать | Написать  
Расстановка индексов
    #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:18
| Ответить | Цитировать | Написать  
Расстановка индексов
    #39318086
Gemorroj
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gemorroj
Гость
Реально если указать USE INDEX запрос отрабатывает за 0.2-0.3 сек.
Если не указывать, то... В SHOW PROCESSLIST показывает "Sending data" уже секунд 500
...
Рейтинг: 0 / 0
29.09.2016, 18:27
| Ответить | Цитировать | Написать  
Расстановка индексов
    #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, 01:21
| Ответить | Цитировать | Написать  
Расстановка индексов
    #39318347
Gemorroj
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gemorroj
Гость
MasterZiv,
>> тебе нужно два индекса
не работает так. Использует только один из 2-х. EXPLAIN показывает план аналогичный если бы индекс был receiver_id + status.
В случае переписывания запроса через UNION, да, подходят 2 индекса.
...
Рейтинг: 0 / 0
30.09.2016, 09:49
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Расстановка индексов
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=39318086&tid=1831370]:
 0ms
get settings:
 6ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 161ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 30ms
get tp. blocked users:
 1ms
others: 202ms
total:  428ms
созд. / загр.: 428ms
x
x
Закрыть


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