SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 26.01.2014 00:49:40

logach
Участник
Зарегистрирован: 25.01.2014
Сообщений: 11

SQL_CALC_FOUND_ROWS, медленный запрос

Доброе время суток.
Форум ни один раз помогал, но в качестве задающего впервые.

Столкнулся с медленными запросами, которые не могу объяснить. Надеюсь на вашу помощь
Сам запрос чуть больше, но урезанная версия тоже медленная, но обо всем по порядку

Для проверки запросов используется workbench

Запросы выпоняются на разных серверах. на localhost и на production

Исходные данные
Записей ~ 60k
колонок 32
Индексы:
- user_id (status)
- status (status)
- idx (date)
- idx2 (date_created,status)

есть несколько вопросов

1) Время выполнения запроса разное(т.е. не погрешность, а в разы). Хотя сервер помощнее чем мой ноут (:

Запрос:

select SQL_NO_CACHE SQL_CALC_FOUND_ROWS *
from items o
order by i.date DESC, i.status ASC
limit 1

localhost
Duration ~ 0.05
Fetch ~ 0.000


production
Duration ~ 0.115
Fetch ~ 0.000

План запроса:
type ALL,
rows 63431
extra Using filesort


2)
если выполнить:

explain select SQL_NO_CACHE *
from items
order by date
limit 100

то видим:
type index
rows 100
extra EMPTY

Причем если выполнить этот запрос с LIMIT 3000, то плане увидим:
type ALL
rows 63459
extra Using filesort

с force index конечно все нормально

explain select SQL_NO_CACHE *
from items force index(idx)
order by date
limit 3000

но не могу понять ПОЧЕМУ

-------------
Есть еще один запрос.

SELECT SQL_NO_CACHE o.*
FROM items i
LEFT JOIN item_category AS oc ON i.id = oc.order_id
LEFT JOIN item_p AS c ON i.id = c.order_id
LEFT JOIN p_category AS mc ON c.map_id = mc.map_id AND c.category_id = mc.map_category_id
LEFT JOIN address AS af ON i.address_from = af.id
LEFT JOIN address AS at ON i.address_to = at.id
WHERE i.status!=1
AND oc.category_id IN (1, 2, 3)
AND (mc.category_id IN (201,202,203) OR mc.category_id IS NULL)
GROUP BY i.id
ORDER BY i.date DESC, i.status ASC
LIMIT 30

выполняется более 2секунд
items ~ 60k
item_category ~70k

вторичные индексы стоят

Это изначальный запрос. То что в начале топика - дошел из этого
использовать Where [border1]<=id<=[border2] не получится, т.е. есть статусы и их надо учитывать...

Неактивен

 

#2 26.01.2014 00:58:32

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

Re: SQL_CALC_FOUND_ROWS, медленный запрос

Посмотрите эту тему http://sqlinfo.ru/forum/viewtopic.php?id=6876

Неактивен

 

#3 26.01.2014 01:15:15

logach
Участник
Зарегистрирован: 25.01.2014
Сообщений: 11

Re: SQL_CALC_FOUND_ROWS, медленный запрос

да даже без SQL_CALC_FOUND_ROWS

в explain запроса

select SQL_NO_CACHE *
from orders o
order by o.date_activated DESC, o.status ASC
limit 1

type ALL
rows ~ 60k

т.е. делается fullscan таблицы

Отредактированно logach (26.01.2014 02:00:21)

Неактивен

 

#4 26.01.2014 01:16:49

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: SQL_CALC_FOUND_ROWS, медленный запрос

SQL_CALC_FOUND_ROWS фактически заставляет выполнить запрос без LIMIT, поэтому LIMIT не влияет на план запроса. Лучше не использовать эту опцию никогда, см. тему, на которую дал ссылку vasya.

Второй запрос - к какой таблице относится o.*? Независимо от этого несколько соображений:
1. i.status!=1 не позволяет использовать индекс дальше. Если возможно, замените на точное равенство.
2. запрос некорректный - ORDER BY идет по полю date, которое не участвует в группировке. Нужна ли группировка?
3. в любом случае поможет замена на SELECT i.id FROM ... LIMIT 30, а затем вторым запросом SELECT .. FROM ... WHERE i.id IN (2,134,41,41,541,515,45);
В этом случае первый запрос будет работать с filesort, но сортировать маленькую табличку из id-шников, а второй будет работать только с 30 строками.

Неактивен

 

#5 26.01.2014 01:18:08

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: SQL_CALC_FOUND_ROWS, медленный запрос

logach написал:

да даже без SQL_CALC_FOUND_ROWS

в explain запроса
делается fullscan таблицы

Приведите EXPLAIN и SHOW CREATE TABLE

Неактивен

 

#6 26.01.2014 01:26:32

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

Re: SQL_CALC_FOUND_ROWS, медленный запрос

logach написал:

да даже без SQL_CALC_FOUND_ROWS

в explain запроса

select SQL_NO_CACHE *
from map_order o
order by o.date_activated DESC, o.status ASC
limit 1

type ALL
rows ~ 60k

т.е. делается fullscan таблицы

Тут причина в том, что сортировка в разном направлении, поэтому целиком по индексу она не может быть выполнена.

Неактивен

 

#7 26.01.2014 02:06:59

logach
Участник
Зарегистрирован: 25.01.2014
Сообщений: 11

Re: SQL_CALC_FOUND_ROWS, медленный запрос

vasya написал:

logach написал:

да даже без SQL_CALC_FOUND_ROWS

в explain запроса

select SQL_NO_CACHE *
from orders o
order by o.date_activated DESC, o.status ASC
limit 1

type ALL
rows ~ 60k

т.е. делается fullscan таблицы

Тут причина в том, что сортировка в разном направлении, поэтому целиком по индексу она не может быть выполнена.

Как тогда быть?
Т.к. надо выбрать все записи(постранично) где в самом начале НОВЫЕ и АКТИВНЫЕ а в самом конце СТАРЫЕ и УДАЛЕННЫЕ
Разбивать на несколько таблиц?
партиции?

Неактивен

 

#8 26.01.2014 02:16:30

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

Re: SQL_CALC_FOUND_ROWS, медленный запрос

Вы бы все-таки показали EXPLAIN и SHOW CREATE TABLE

status это 0 или 1, тогда where status=1 order by o.date_activated DESC

Неактивен

 

#9 26.01.2014 02:57:53

logach
Участник
Зарегистрирован: 25.01.2014
Сообщений: 11

Re: SQL_CALC_FOUND_ROWS, медленный запрос

rgbeast написал:

SQL_CALC_FOUND_ROWS фактически заставляет выполнить запрос без LIMIT, поэтому LIMIT не влияет на план запроса. Лучше не использовать эту опцию никогда, см. тему, на которую дал ссылку vasya.

Второй запрос - к какой таблице относится o.*? Независимо от этого несколько соображений:
1. i.status!=1 не позволяет использовать индекс дальше. Если возможно, замените на точное равенство.
2. запрос некорректный - ORDER BY идет по полю date, которое не участвует в группировке. Нужна ли группировка?
3. в любом случае поможет замена на SELECT i.id FROM ... LIMIT 30, а затем вторым запросом SELECT .. FROM ... WHERE i.id IN (2,134,41,41,541,515,45);
В этом случае первый запрос будет работать с filesort, но сортировать маленькую табличку из id-шников, а второй будет работать только с 30 строками.

o.* - это опечатка. должно быть i.*

1. Статусов несколько. [1,2,3,4,5]
надо выбрать все кроме 1.
2. Группировка нужна, т.к появляются дубликаты. Как я писал чуть выше это надо для выборки сначала НОВЫХ со статусом 2, а на самой последней СТАРЫЕ со статусом 5
3. ОЧень медленно выполняется именно из-за order by  и group by. попробуем

Неактивен

 

#10 26.01.2014 03:16:19

logach
Участник
Зарегистрирован: 25.01.2014
Сообщений: 11

Re: SQL_CALC_FOUND_ROWS, медленный запрос

vasya написал:

Вы бы все-таки показали EXPLAIN и SHOW CREATE TABLE

status это 0 или 1, тогда where status=1 order by o.date_activated DESC

статусов больше, поэтому строгое равенство не получится использовать

CREATE TABLE `orders` (
  `id` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(12) unsigned DEFAULT NULL,
  `type_id` tinyint(4) DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_activated` timestamp NULL DEFAULT NULL,
  `address_id_from` int(10) unsigned DEFAULT NULL,
  `address_id_to` int(10) unsigned DEFAULT NULL,
  `date_take` timestamp NULL DEFAULT NULL,
  `date_take_deadline` timestamp NULL DEFAULT NULL,
  `date_delivery` timestamp NULL DEFAULT NULL,
  `date_delivery_deadline` timestamp NULL DEFAULT NULL,
  `date_agreement` timestamp NULL DEFAULT NULL,
  `max_price` float DEFAULT NULL,
  `suggested_price` float DEFAULT NULL,
  `suggestions_count` int(10) DEFAULT NULL,
  `distance` float DEFAULT NULL,
  `distance_status` tinyint(1) DEFAULT NULL,
  `notifications` tinyint(4) NOT NULL,
  `allow_auto_suggestion` tinyint(1) NOT NULL DEFAULT '0',
  `v2` tinyint(4) unsigned DEFAULT '0',
  `seo_links` text COLLATE utf8_bin NOT NULL,
  `seo_name` varchar(150) COLLATE utf8_bin DEFAULT NULL,
  `has_seo` tinyint(1) NOT NULL DEFAULT '0',
  `external` tinyint(4) NOT NULL DEFAULT '0',
  `crm_id` int(12) NOT NULL,
  `payment_type` tinyint(2) NOT NULL,
  `payment_value` int(3) DEFAULT NULL,
  `prepaid` tinyint(4) NOT NULL,
  `prepay_required` tinyint(4) NOT NULL,
  `prepay_accept_bid` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userID` (`user_id`),
  KEY `status` (`status`) USING BTREE,
  KEY `idx` (`date_activated`),
  KEY `idx2` (`date_created`,`status`)
) ENGINE=MyISAM AUTO_INCREMENT=71054 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

explain запроса в аттаче, сам запрос

explain SELECT SQL_NO_CACHE o.*
FROM (orders AS o)
LEFT JOIN order_category AS oc ON o.id = oc.order_id
LEFT JOIN order_cargo AS c ON o.id = c.order_id
LEFT JOIN map_category AS mc ON c.map_id = mc.map_id AND c.category_id = mc.map_category_id
LEFT JOIN address AS ao ON o.address_id_from = ao.id
LEFT JOIN address AS at ON o.address_id_to = at.id
WHERE o.status!=1
AND oc.category_id IN (1, 2, 3,4,7,8,9,10,11,12)
AND (mc.category_id IN (201,202,203,204,901,103,305,801,1201,710,902,108,301,703,803,1204,106,309,805,1207,711,107,302,701,802,1202,104,306,704,804,1205,102,310,806,1208,712,101,303,702,1203,105,307,705,1206,109,304,1209,714,308,1210,716,311,706,715,713,707,709,708) OR mc.category_id IS NULL)
GROUP BY o.id
ORDER BY o.date_activated DESC, o.status ASC
LIMIT 30

этот запрос выполняется 2-4сек

еще что не могу понять - на сервере explain выполняется 0.090сек, а на локалке 0.000(workbench)


Прикрепленные файлы:
Attachment Icon explain.png, Размер: 39,716 байт, Скачано: 633

Неактивен

 

#11 26.01.2014 14:18:01

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

Re: SQL_CALC_FOUND_ROWS, медленный запрос

logach написал:

еще что не могу понять - на сервере explain выполняется 0.090сек, а на локалке 0.000(workbench)

Бывает, что и select 1; выполняется долго. Это указывает на загруженность сервера.

Начните с этой рекомендации:

rgbeast написал:

3. в любом случае поможет замена на SELECT i.id FROM ... LIMIT 30, а затем вторым запросом SELECT .. FROM ... WHERE i.id IN (2,134,41,41,541,515,45);
В этом случае первый запрос будет работать с filesort, но сортировать маленькую табличку из id-шников, а второй будет работать только с 30 строками.

Неактивен

 

#12 26.01.2014 15:21:08

logach
Участник
Зарегистрирован: 25.01.2014
Сообщений: 11

Re: SQL_CALC_FOUND_ROWS, медленный запрос

vasya написал:

logach написал:

еще что не могу понять - на сервере explain выполняется 0.090сек, а на локалке 0.000(workbench)

Бывает, что и select 1; выполняется долго. Это указывает на загруженность сервера.

Действительно select 1; ~0.06s выполняется. Это если через workbench
Если подключиться к серверу, а там к mysql(консольному) и выполнить select 1; - то 0.00s ...странно. Ведь workbench показывает именно скорость выполнения запроса, а не подключение+запрос..так?

по рекоменации, как попробую, отпишусь. Спасибо.

Неактивен

 

#13 26.01.2014 17:34:34

logach
Участник
Зарегистрирован: 25.01.2014
Сообщений: 11

Re: SQL_CALC_FOUND_ROWS, медленный запрос

rgbeast написал:

SQL_CALC_FOUND_ROWS фактически заставляет выполнить запрос без LIMIT, поэтому LIMIT не влияет на план запроса. Лучше не использовать эту опцию никогда, см. тему, на которую дал ссылку vasya.

Второй запрос - к какой таблице относится o.*? Независимо от этого несколько соображений:
1. i.status!=1 не позволяет использовать индекс дальше. Если возможно, замените на точное равенство.
2. запрос некорректный - ORDER BY идет по полю date, которое не участвует в группировке. Нужна ли группировка?
3. в любом случае поможет замена на SELECT i.id FROM ... LIMIT 30, а затем вторым запросом SELECT .. FROM ... WHERE i.id IN (2,134,41,41,541,515,45);
В этом случае первый запрос будет работать с filesort, но сортировать маленькую табличку из id-шников, а второй будет работать только с 30 строками.

3. попробовал
да, действительно запрос уменьшился до ~1.5s (было 2-4s)
есть одно. для того чтобы узнать кол-во записей(пагинация) отбрасываем limit и тут уже получаем ~ 1.2s

т.е. получается проблема осталась

1.5s все равно долго. все из-за order by

Можно попробовать заготовливать данные для вывода заранее, но только для данных без фильтров.

И если включить фильтр по address то опять возникнет проблема с медленным запросом...а ведь данные будут только расти

голову уже сломал =\

Неактивен

 

#14 26.01.2014 17:36:48

logach
Участник
Зарегистрирован: 25.01.2014
Сообщений: 11

Re: SQL_CALC_FOUND_ROWS, медленный запрос

и как тогда так бытсро отдает, к примеру, авито?
железо + еще что-то?
http://www.avito.ru/rossiya/kvartiry/prodam

Неактивен

 

#15 27.01.2014 23:55:04

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

Re: SQL_CALC_FOUND_ROWS, медленный запрос

logach написал:

Действительно select 1; ~0.06s выполняется. Это если через workbench
Если подключиться к серверу, а там к mysql(консольному) и выполнить select 1; - то 0.00s ...странно. Ведь workbench показывает именно скорость выполнения запроса, а не подключение+запрос..так?

Выполнение запроса включает в себя и передачу результата. Более подробно можете посмотреть через профилирование.

Что касается основного запроса.
1. проверьте везде ли там нужен left join. Например, условие  AND oc.category_id IN (1, 2, 3,4,7,8,9,10,11,12) фактически приводит LEFT JOIN order_category AS oc  к тому же результату что и JOIN.
2. действительно ли нужна группировка? Если дубликаты появляются в результате join, то как много их? Возможно проще будет сделать с запасом limit 40, а дубли обрезать в скрипте при отображении.
3. Чем обусловлена такая нумерация status? Например, можно переопределить номера в обратной последовательности (то что было 1 станет 5 и наоборот). Тогда сортировка по o.date_activated DESC, o.status DESC будет в одном направлении и можно будет использовать индекс. Нужны ли null значения для поля status или для неопределенных можно завести специальный числовой номер?

Посмотрите статью Как MySQL оптимизирует ORDER BY, LIMIT и DISTINCT

Неактивен

 

#16 28.01.2014 11:37:03

logach
Участник
Зарегистрирован: 25.01.2014
Сообщений: 11

Re: SQL_CALC_FOUND_ROWS, медленный запрос

vasya написал:

logach написал:

Действительно select 1; ~0.06s выполняется. Это если через workbench
Если подключиться к серверу, а там к mysql(консольному) и выполнить select 1; - то 0.00s ...странно. Ведь workbench показывает именно скорость выполнения запроса, а не подключение+запрос..так?

Выполнение запроса включает в себя и передачу результата. Более подробно можете посмотреть через профилирование.

Что касается основного запроса.
1. проверьте везде ли там нужен left join. Например, условие  AND oc.category_id IN (1, 2, 3,4,7,8,9,10,11,12) фактически приводит LEFT JOIN order_category AS oc  к тому же результату что и JOIN.
2. действительно ли нужна группировка? Если дубликаты появляются в результате join, то как много их? Возможно проще будет сделать с запасом limit 40, а дубли обрезать в скрипте при отображении.
3. Чем обусловлена такая нумерация status? Например, можно переопределить номера в обратной последовательности (то что было 1 станет 5 и наоборот). Тогда сортировка по o.date_activated DESC, o.status DESC будет в одном направлении и можно будет использовать индекс. Нужны ли null значения для поля status или для неопределенных можно завести специальный числовой номер?

Посмотрите статью Как MySQL оптимизирует ORDER BY, LIMIT и DISTINCT

Профилированием БД еще не занимался, спасибо.

1. Ввел избыточность в orders (добавил category_id) - тем самым избавившись от лишнего joina.
2. Группировка нужна только из-за того, что у category_id есть подкатегории. и при джойне (если в заказе были подкатегории) появляются дубли. Пока обошолся тем, что если у нас выбраны все подкатегории основной категории то это джойн убирается.
3. Обусловлено исключительно тем, что досталось в наследство smile. Все заказы изначально со статусом 1. Default NULL - это опять же исторически сложилось.

Неактивен

 

#17 28.01.2014 21:58:46

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

Re: SQL_CALC_FOUND_ROWS, медленный запрос

Кстати, вы когда только id выбираете лишние джойны (например,  LEFT JOIN address AS ao ON o.address_id_from = ao.id ) из запроса выкидываете? И считаете как count(*)?

LEFT JOIN address AS ao ON o.address_id_from = ao.id
Нужен ли здесь именно left join? А если, учесть, что у вас в запросе выбираются поля только из первой таблицы o.* то зачем вообще это объединение?

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

Неактивен

 

Board footer

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