|
Задавайте вопросы, мы ответим
Вы не зашли.
Доброе время суток.
Форум ни один раз помогал, но в качестве задающего впервые.
Столкнулся с медленными запросами, которые не могу объяснить. Надеюсь на вашу помощь
Сам запрос чуть больше, но урезанная версия тоже медленная, но обо всем по порядку
Для проверки запросов используется 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] не получится, т.е. есть статусы и их надо учитывать...
Неактивен
Посмотрите эту тему http://sqlinfo.ru/forum/viewtopic.php?id=6876
Неактивен
да даже без 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)
Неактивен
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 строками.
Неактивен
logach написал:
да даже без SQL_CALC_FOUND_ROWS
в explain запроса
делается fullscan таблицы
Приведите EXPLAIN и SHOW CREATE TABLE
Неактивен
logach написал:
да даже без SQL_CALC_FOUND_ROWS
в explain запросаselect SQL_NO_CACHE *
from map_order o
order by o.date_activated DESC, o.status ASC
limit 1type ALL
rows ~ 60k
т.е. делается fullscan таблицы
Тут причина в том, что сортировка в разном направлении, поэтому целиком по индексу она не может быть выполнена.
Неактивен
vasya написал:
logach написал:
да даже без SQL_CALC_FOUND_ROWS
в explain запросаselect SQL_NO_CACHE *
from orders o
order by o.date_activated DESC, o.status ASC
limit 1type ALL
rows ~ 60k
т.е. делается fullscan таблицыТут причина в том, что сортировка в разном направлении, поэтому целиком по индексу она не может быть выполнена.
Как тогда быть?
Т.к. надо выбрать все записи(постранично) где в самом начале НОВЫЕ и АКТИВНЫЕ а в самом конце СТАРЫЕ и УДАЛЕННЫЕ
Разбивать на несколько таблиц?
партиции?
Неактивен
Вы бы все-таки показали EXPLAIN и SHOW CREATE TABLE
status это 0 или 1, тогда where status=1 order by o.date_activated DESC
Неактивен
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. попробуем
Неактивен
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)
Неактивен
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 строками.
Неактивен
vasya написал:
logach написал:
еще что не могу понять - на сервере explain выполняется 0.090сек, а на локалке 0.000(workbench)
Бывает, что и select 1; выполняется долго. Это указывает на загруженность сервера.
Действительно select 1; ~0.06s выполняется. Это если через workbench
Если подключиться к серверу, а там к mysql(консольному) и выполнить select 1; - то 0.00s ...странно. Ведь workbench показывает именно скорость выполнения запроса, а не подключение+запрос..так?
по рекоменации, как попробую, отпишусь. Спасибо.
Неактивен
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 то опять возникнет проблема с медленным запросом...а ведь данные будут только расти
голову уже сломал =\
Неактивен
и как тогда так бытсро отдает, к примеру, авито?
железо + еще что-то?
http://www.avito.ru/rossiya/kvartiry/prodam
Неактивен
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
Неактивен
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. Обусловлено исключительно тем, что досталось в наследство 
. Все заказы изначально со статусом 1. Default NULL - это опять же исторически сложилось.
Неактивен
Кстати, вы когда только 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.* то зачем вообще это объединение?
Касательно группировки, не видя данных, сложно сказать определенно, но есть ощущения что не все в порядке с логикой программы/данных.
Неактивен
