SQLinfo.ru - Все о MySQL Фестиваль «Российские интернет-технологии» 2017

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

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

Вы не зашли.

#1 06.04.2017 12:19:26

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 18

Просьба помочь с запросом

Здравствуйте, для практики делаю что-то вроде сервиса по бронированию номеров. Есть вот такой запрос, фильтр для поиска свободных номеров. Цены сортируются с учетом количества дней. например если меньше 7 дней, то сортировка идет по room.price, если больше 7 и меньше 30 дней по room.price_week, если больше 30 дней по room.price_month, но это все вычисляется с помощью php. Цена вычисляется с прибавлением комиссии сервиса (В данном случае 5%).


SELECT room.id, users.id user_id, room_images.thumb_img, room.price, room.price_week, room.price_month, lang_budget.title budget_title, lang_stars_numb.title stars_title, lang_property_type.title type_title FROM room
INNER JOIN room_images
INNER JOIN lang_stars_numb
INNER JOIN lang_budget
INNER JOIN lang_property_type
INNER JOIN users
LEFT JOIN busy_dates ON busy_dates.room_id=room.id
AND ((busy_from <= '2017-05-01' AND busy_to >= '2017-05-04')
OR (busy_from = '2017-05-04' OR busy_to = '2017-05-01')
OR (busy_from >= '2017-05-01' AND busy_from <= '2017-05-04')
OR (busy_from <= '2017-05-01' AND busy_to > '2017-05-01'
AND busy_to < '2017-05-04'))
WHERE  users.id = room.user_id
AND room.budget_id IN ('1','2','3','4','5','6','7','8','9','10')
AND room.stars_numb_id IN ('1','2')
AND room.type_id IN ('1','2','3')
AND room.facility_id IN ('1','2','3','4','5')
AND room.district_id IN ('1','2')
AND room.status = '1'
AND room_images.id = room.first_image_id
AND lang_budget.budget_id = room.budget_id
AND lang_stars_numb.stars_numb_id = room.stars_numb_id
AND lang_lang_property_type.id_type = room.id_type
AND lang_budget.id_lang = 2
AND lang_stars_numb.id_lang = 2
AND lang_lang_property_type.id_lang = 2
AND busy_dates.room_id IS NULL AND ((room.price*"5"/"100")+room.price)*"4" BETWEEN '1' AND '10000'
ORDER BY ((room.price*"5"/"100")+room.price)*"4" ASC LIMIT 0, 10
 


Индексы в таблице room:

id (PRIMARY KEY)
room_1    (budget_id, stars_numb_id, type_id, facility_id, district_id)
room_2    (status, id)
room_3    (user_id,parent_id)
EXPLAIN


1    SIMPLE   room        ALL    room_1,room_2,room_3  NULL NULL NULL    8  Using where; Using temporary; Using filesort           
1    SIMPLE   busy_dates    ALL    busy_from    NULL NULL NULL    2  Using where; Not exists; Using join buffer (flat, ...

По всем остальным таблицам используются индексы, тип eq_ref

Скажите пожалуйста как можно избавиться в таком запросе от Using temporary; Using filesort и от сканирования всей таблицы. Проблема еще и в том, что при сортировке по room.id, тоже получаю Using temporary; Using filesort. Спасибо.

Отредактированно webJunior (06.04.2017 12:21:15)

Неактивен

 

#2 06.04.2017 20:49:02

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

Re: Просьба помочь с запросом

от файловой сортировки (Using temporary; Using filesort) никак.
можно лишь уточнить где создается временная таблица - в памяти или на диске
show status like
Created_tmp_disk_tables -- количество неявных временных таблиц на диске, созданных во время выполнения операторов.
сравните значение до и после выполнения запроса, при необходимости увеличьте tmp_table_size

от all у busy_dates, имхо, не избавится
а вот заставить использовать room_1 можно, но стоит ли? сервер думал прежде чем отказаться от него

и зачем вы числовые значения указываете как строки?

Неактивен

 

#3 06.04.2017 23:58:26

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 18

Re: Просьба помочь с запросом

vasya написал:

от файловой сортировки (Using temporary; Using filesort) никак.
можно лишь уточнить где создается временная таблица - в памяти или на диске
show status like
Created_tmp_disk_tables -- количество неявных временных таблиц на диске, созданных во время выполнения операторов.
сравните значение до и после выполнения запроса, при необходимости увеличьте tmp_table_size

от all у busy_dates, имхо, не избавится
а вот заставить использовать room_1 можно, но стоит ли? сервер думал прежде чем отказаться от него

и зачем вы числовые значения указываете как строки?

Насчет числовых значений, да действительно, исправил.

Created_tmp_disk_tables до запроса 0, после запроса 4. tmp_table_size 25m, чтобы проверить увеличил до 100, тот же результат. Никак нельзя оптимизировать такой запрос?

Отредактированно webJunior (06.04.2017 23:58:46)

Неактивен

 

#4 08.04.2017 15:43:49

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

Re: Просьба помочь с запросом

webJunior написал:

tmp_table_size 25m, чтобы проверить увеличил до 100, тот же результат.

увеличьте ещё max_heap_table_size и емнип, наличие text/blob тоже приводят к такому результату.

Неактивен

 

#5 08.04.2017 16:15:39

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 18

Re: Просьба помочь с запросом

vasya написал:

webJunior написал:

tmp_table_size 25m, чтобы проверить увеличил до 100, тот же результат.

увеличьте ещё max_heap_table_size и емнип, наличие text/blob тоже приводят к такому результату.

Увеличил tmp_table_size 256m, max_heap_table_size 256m. Так же Created_tmp_disk_tables 4

Насчет text/blob имеете ввиду типы полей которые выбираются? Нет там только числовые и varchar

Отредактированно webJunior (08.04.2017 16:15:58)

Неактивен

 

#6 08.04.2017 17:23:14

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

Re: Просьба помочь с запросом

а в чем вы запускаете запрос? в консоли?
будут ли отличаться показания, если после запроса дважды выполнить
SHOW SESSION STATUS LIKE ‘Created%’;

Неактивен

 

#7 08.04.2017 17:42:11

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 18

Re: Просьба помочь с запросом

vasya написал:

а в чем вы запускаете запрос? в консоли?
будут ли отличаться показания, если после запроса дважды выполнить
SHOW SESSION STATUS LIKE ‘Created%’;

В консоли phpmyadmin

SHOW SESSION STATUS LIKE ‘Created%’; выводит ошибку 

Ответ MySQL: Документация

#1064 - У вас ошибка в запросе. Изучите документацию по используемой версии MariaDB на предмет корректного синтаксиса около '‘Created%’' на строке 1

Работает только SHOW SESSION STATUS, без каких-либо изменений

Отредактированно webJunior (08.04.2017 17:46:32)

Неактивен

 

#8 08.04.2017 20:35:24

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

Re: Просьба помочь с запросом

а есть возможность выполнить через консоль, чтобы исключить глюки phpadmina?

Неактивен

 

#9 08.04.2017 21:54:43

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 18

Re: Просьба помочь с запросом

vasya написал:

а есть возможность выполнить через консоль, чтобы исключить глюки phpadmina?

Вы правы это глюк phpmyadmin. Запустил через командную строку, SHOW SESSION STATUS LIKE ‘Created%’;

В настройках по умолчанию и с изменениями показывает

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+

Created_tmp_files даже без запроса показывает 5

Отредактированно webJunior (08.04.2017 21:56:04)

Неактивен

 

#10 08.04.2017 23:18:03

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

Re: Просьба помочь с запросом

webJunior написал:

Created_tmp_files даже без запроса показывает 5

это нормально

возвращаясь к исходному запросу, имхо, вряд ли можно его улучшить

Неактивен

 

#11 09.04.2017 11:19:27

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 18

Re: Просьба помочь с запросом

vasya написал:

webJunior написал:

Created_tmp_files даже без запроса показывает 5

это нормально

возвращаясь к исходному запросу, имхо, вряд ли можно его улучшить

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

Подскажите пожалуйста почему когда идет сортировка по room.id, так же получаю Using temporary; Using filesort?

Неактивен

 

#12 09.04.2017 19:11:08

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

Re: Просьба помочь с запросом

webJunior написал:

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

Временная таблица создается на диске если её размер превышает минимум из max_heap_table_size/tmp_table_size. Т.е. это связано с объемом выборки, а не вообще данных. Например, при бронировании вы ищите свободные номера на ближайший месяц, хотя сама таблица может содержать данные о постояльцах за последнее столетие.

webJunior написал:

Подскажите пожалуйста почему когда идет сортировка по room.id, так же получаю Using temporary; Using filesort?

видимо оптимизатор считает, что придется перебрать много записей и проще прочитать последовательно всю таблицу.
http://sqlinfo.ru/forum/viewtopic.php?pid=25439#p25439

Неактивен

 

#13 09.04.2017 19:28:30

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 18

Re: Просьба помочь с запросом

Временная таблица создается на диске если её размер превышает минимум из max_heap_table_size/tmp_table_size. Т.е. это связано с объемом выборки, а не вообще данных. Например, при бронировании вы ищите свободные номера на ближайший месяц, хотя сама таблица может содержать данные о постояльцах за последнее столетие.

Т.е размер временной таблицы может зависеть от количества проверяемых строк? То что показывает ячейка rows при Explain?

Неактивен

 

#14 09.04.2017 19:35:43

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

Re: Просьба помочь с запросом

от количества выбираемых
пусть в таблице N строк
проверяем M строк
удовлетворяет условию выборки X строк
эти X строк сортируются методом файловой сортировки и если X*(размер одной строки) > min(max_heap_table_size/tmp_table_size), то временная таблица создается на диске

rows в explain показывает ожидаемую оптимизатором оценку M

Неактивен

 

#15 09.04.2017 23:58:22

webJunior
Участник
Зарегистрирован: 30.03.2017
Сообщений: 18

Re: Просьба помочь с запросом

vasya написал:

от количества выбираемых
пусть в таблице N строк
проверяем M строк
удовлетворяет условию выборки X строк
эти X строк сортируются методом файловой сортировки и если X*(размер одной строки) > min(max_heap_table_size/tmp_table_size), то временная таблица создается на диске

rows в explain показывает ожидаемую оптимизатором оценку M

Ясно, большое спасибо!

Неактивен

 

Board footer

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