SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 22.11.2007 16:47:25

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Замедление при ORDER BY LIMIT

Насколько я поня лпроблема не только у меня...

Есть таблица 500 тыс. записей.
CREATE TABLE `orgs_wait` (
`so` int(11) NOT NULL,
`Name_Fil` varchar(50) DEFAULT NULL,
`Code_Fil` varchar(5) DEFAULT NULL,
`OrgName` varchar(255) NOT NULL,
`DocID` varchar(32) NOT NULL,
`Adress` varchar(255) DEFAULT NULL,
`Telephone` varchar(100) DEFAULT NULL,
`Rubrica1` varchar(100) DEFAULT NULL,
`Rubrica2` varchar(100) DEFAULT NULL,
`Rubrica3` varchar(100) DEFAULT NULL,
`Status` tinyint(2) DEFAULT NULL,
`id_region` int(11) NOT NULL,
`id_business` int(11) NOT NULL,
`ORGSUBJECT` varchar(255) DEFAULT NULL,
PRIMARY KEY (`so`),
UNIQUE KEY `DocID` (`DocID`),
KEY `OrgName` (`OrgName`),
KEY `id_business` (`id_business`,`id_region`,`Status`,`so`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Составной ключ используется для запроса:
SELECT *
FROM `orgs_wait` ow
WHERE
NOT ow.`Status` = 99
AND ow.`id_business` IN (
189,190,360,73,74,191,194,198,208,219,237,239,4641,4817,5648,8312,8385,9925,
10148,10650,10932,12009,12129,12322,364,371,430,624,630,647,688,700,711,732,
746,761,777,791,803,813,4644,4824,5649,6061 ,333
)
AND ow.`id_region` IN (
60,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,3239 ,33
)
ORDER BY ow.`so`
LIMIT 3655, 10

Вид запроса оптимизирован под сортировку наименьшей выборки из 500 тыс.

EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ow range id_business id_business 10 13854 Using where; Using filesort

Делаю выводы:
1. Если rows значительно меньше 500 тыс. значит ключи `id_business`, `id_region` и `Status` прекрасно используются.
2. Выборка производится первый раз за несколько секунд, остальные разы за 0.09 сек, причем при любых значениях WHere и маленьких limit. Значит индекс кэшируется, и используется в будущем очень быстро.
3. При limit 13000, 10 запрос жутко тормозит. Значит, при маленьких лимитах сортируется только маленький кусочек  таблицы, а при больших почти вся. И наверно всю таблицу не получается отсортировать в памяти.

Вопросы:
1. Почему explain выдает filesort?
2. Как влияет sort_buffer_size на filesort? Каким можно его поставить?
3. Как узнать размер памяти для индекса, чтобы он точно поместился в память?
4. Как заставить filesort исчезнуть, какие переменные настроить?
5. Как использовать sort_buffer_size?
6. Как влияет увеличение переменных на заполнение ОЗУ сервера?
7. Почему  пишут про record_rnd_buffer, а я не могу найти его variables? smile

Очень жду..

Неактивен

 

#2 22.11.2007 16:50:13

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Re: Замедление при ORDER BY LIMIT

А еще интересно, есть ли какие способы ускорения кроме разбития на части большой таблицы?

Неактивен

 

#3 22.11.2007 17:02:26

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Замедление при ORDER BY LIMIT

1. Потому что используется filesort. Используется первые 10 байт ключа.
NOT блокирует использование ключа дальше.

2. Влияет напрямую. Поставить:
в my.cnf: sort_buffer_size=10M
перед выполнением запроса: SET sort_buffer_size=10 * 1024 * 1024;

3. Для MyISAM это key_buffer_size (см. также статью про индексы).
Для InnoDB это innodb_buffer_pool (там не только индексы).

4. Никак. Для такого запроса с NOT он останется в любом случае.

5. Выставить его.

6. Напрямую. Поставили значение - выделили память. Буфер сортировки
выделяется по мере необходимости.

7. Кто пишет? smile

Вообще, LIMIT большоечисло, маленькоечисло - это не удачное решение,
такой LIMIT никак не влияет на использование индексов, т.к. Вы выбираете
почти всю таблицу, просто выбрасываете первые данные.

Возможная оптимизация - выбирать сначала нужные id из таблицы с нужными
where и сортировками во временную табличку, а потом второй запрос с join по
id. Увеличение производительности в том, что сортировать нужно меньше
данных (только id).

Неактивен

 

#4 22.11.2007 18:03:23

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Re: Замедление при ORDER BY LIMIT

paulus написал:

1. Потому что используется filesort. Используется первые 10 байт ключа.
NOT блокирует использование ключа дальше.

Почему NOT блокирует? А если написать вместо
NOT ow.`Status` = 99
строку
ow.`Status` < 99
или другие варианты, есть возможность ускорить?

paulus написал:

2. Влияет напрямую. Поставить:
в my.cnf: sort_buffer_size=10M
перед выполнением запроса: SET sort_buffer_size=10 * 1024 * 1024;

У меня стоит 20М, с пред установкой
SET sort_buffer_size=10 * 1024 * 1024;
SET sort_buffer_size=20 * 1024 * 1024;
изменений не было видно

paulus написал:

3. Для MyISAM это key_buffer_size (см. также статью про индексы).
Для InnoDB это innodb_buffer_pool (там не только индексы).

Спасибо, возникли вопросы:
Стоит ли перейти на myisam - будет значительный прирост?

paulus написал:

6. Напрямую. Поставили значение - выделили память. Буфер сортировки
выделяется по мере необходимости.

Мне интересно, память выделяется один раз для всех запросов или для каждого?
До какого момента хранится кэш?

paulus написал:

7. Кто пишет? smile

http://www.mysql.ru/docs/man/ORDER_BY_optimisation.html

paulus написал:

Возможная оптимизация - выбирать сначала нужные id из таблицы с нужными
where и сортировками во временную табличку, а потом второй запрос с join по
id. Увеличение производительности в том, что сортировать нужно меньше
данных (только id).

На мой взгляд это все делает MYSQL - т.е. сначала делает выборку куска по индексам, а потом
сортирует во временной таблице, которая лежит в файле (filesort). Но таблица получается слишком большой.
А сделать временные таблицы для каждого варианта набора условий WHERE по-моему нереально.

Неактивен

 

#5 22.11.2007 18:23:27

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Замедление при ORDER BY LIMIT

Нет, "<" будет ничуть не лучше, чем NOT.

Прирост на SELECT может быть, если в таблицу никто не пишет. Значительного не будет.

Память под сортировку данных выделяется на поток, в момент необходимости, освобождается
после выполнения запроса.  Память под индексы выделяется в момент старта сервера,
освобождается при остановке сервера.

mysql.ru - это странный ресурс, Вы не первый, кто говорит о том, что у них написаны странные
вещи. Пользуйтесь mysql.com smile      Указанная переменная есть в исходниках MySQL smile

Само ничего не происходит. Глядите, пусть есть тривиальный случай таблицы с двумя полями
А и Б, где А - индекс, Б - данные. Если Вам надо отсортировать (filesort) по А, а потом выбрать
оттуда LIMIT 500,5, то логичнее выбрать оттуда _только_ А (который занимает меньше места),
отсортировать его filesort, выбрать нужные значения А, а потом подставить уже данные Б.
Отличие от Вашего метода в том, что Вы выбираете и А и Б (а, стало быть, гоняете по памяти
при сортировке кучу ненужных данных).

P.S. Нашел такую переменную в 4.0 smile

Неактивен

 

#6 22.11.2007 18:59:41

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Re: Замедление при ORDER BY LIMIT

paulus написал:

Само ничего не происходит. Глядите, пусть есть тривиальный случай таблицы с двумя полями
А и Б, где А - индекс, Б - данные. Если Вам надо отсортировать (filesort) по А, а потом выбрать
оттуда LIMIT 500,5, то логичнее выбрать оттуда _только_ А (который занимает меньше места),
отсортировать его filesort, выбрать нужные значения А, а потом подставить уже данные Б.
Отличие от Вашего метода в том, что Вы выбираете и А и Б (а, стало быть, гоняете по памяти
при сортировке кучу ненужных данных).

По-моему, Вы про другой случай smile

У меня только два варианта:
1) Отсортировать весь список, потом вырезать лишние, потом достать кусок (наверно этот вариант Вы имели в виду, но непонятно объяснили)
2) Вырезать лишние, отсортировать оставшиеся, потом достать кусок (используется сейчас)
3) Возможно есть, но я не знаю..

Раньше я использовал 1 способ. Время выполнения почти такое же, но чуть больше даже.
И даже если исключить NOT, то почему то выборка идет по всей таблице!
  PRIMARY KEY (`so`),
  UNIQUE KEY `DocID` (`DocID`),
  KEY `OrgName` (`OrgName`),
  KEY `so` (`so`,`id_business`,`id_region`)

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    ow    index        PRIMARY    4        486095    Using where

Я ожидал, что в этом случае будет использоваться только индекс! sad
Может ошибка в чем-то?

Неактивен

 

#7 22.11.2007 19:08:29

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Замедление при ORDER BY LIMIT

Хорошо, сравните время выполнения двух конструкций

Код:

SELECT * FROM `orgs_wait` ow WHERE NOT ow.`Status` = 99 AND ow.`id_business` IN (189,190,360,73,74,191,194,198,208,219,237,239,4641,4817,5648,8312,8385,9925, 10148,10650,10932,12009,12129,12322,364,371,430,624,630,647,688,700,711,732,
746,761,777,791,803,813,4644,4824,5649,6061,333)
AND ow.`id_region` IN (60,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,3239,33)
ORDER BY ow.`so`LIMIT 3655, 10

и

Код:

SELECT `so` FROM `orgs_wait` ow WHERE NOT ow.`Status` = 99 AND ow.`id_business` IN (189,190,360,73,74,191,194,198,208,219,237,239,4641,4817,5648,8312,8385,9925, 10148,10650,10932,12009,12129,12322,364,371,430,624,630,647,688,700,711,732,
746,761,777,791,803,813,4644,4824,5649,6061,333)
AND ow.`id_region` IN (60,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,3239,33)
ORDER BY ow.`so`LIMIT 3655, 10

Я подозреваю, что вторая будет выполняться быстрее в несколько раз.

Неактивен

 

#8 22.11.2007 19:13:12

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

Re: Замедление при ORDER BY LIMIT

Поясню пример Павла. Пусть есть таблица  T (A int primary key, B varchar(10000), C int, D int);

У  Вас запрос
SELECT A,B from T where C=10 order by D limit 100,10;

При сортировке индекс может быть использован, если есть ключ KEY(C,D). Если ключ использован быть не может, то тогда MySQL сделает выборку всех строк, у которых C=10 и пары A,B отсортирует методом filesort. Подчеркну, MySQL будет сортировать строки как Вы их указали в запросе SELECT, то есть в них будут и значения B.

Предлагается следующая оптимизация, которая обычно дает 10кратный прирост производительности. Запрос заменяется на:
SELECT A from T where C=10 order by D limit 100,10;
Тогда сортироваться будут только значения A. Как результат запроса Вы получили 10 значений A. Теперь загоните их в список внутри своего приложения и сделайте второй запрос
SELECT A,B from T where A in (A1, A2, A3, ...., A10);

Неактивен

 

#9 22.11.2007 19:32:23

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

Re: Замедление при ORDER BY LIMIT

Mikasto написал:

И даже если исключить NOT, то почему то выборка идет по всей таблице!
  PRIMARY KEY (`so`),
  UNIQUE KEY `DocID` (`DocID`),
  KEY `OrgName` (`OrgName`),
  KEY `so` (`so`,`id_business`,`id_region`)

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    ow    index        PRIMARY    4        486095    Using where

Я ожидал, что в этом случае будет использоваться только индекс! sad
Может ошибка в чем-то?

Попробуйте изменить ключ на   KEY `so` (`id_business`,`id_region`,`so`)

Неактивен

 

#10 22.11.2007 21:11:40

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Замедление при ORDER BY LIMIT

paulus написал:

2. Влияет напрямую. Поставить:
в my.cnf: sort_buffer_size=10M
перед выполнением запроса: SET sort_buffer_size=10 * 1024 * 1024;

3. Для MyISAM это key_buffer_size (см. также статью про индексы).
Для InnoDB это innodb_buffer_pool (там не только индексы).

Объясните, если не трудно - откуда все таки брать конкретные значения?
почему sort_buffer_size=10M? а не 20, 30, 300, 1G?

если поставить много или очень много - то от этого может стать хуже? (подразумеваю, что оперативы хватает)

вот я например в порядке эксперимента поставил key_buffer_size=1G

глядя на графики утилиты mysqlard вижу, что из 1G используется 0.8 - так это же хорошо, насколько я понимаю.
Получается - чем больше, тем лучше?
Понятно, что если накопленная статистика будет показывать, что какой то кэш используется на 30% то можно потом понизить соответствующее значение, но для этого нужно время. Поэтому и хочется знать - как то можно все эти параметры рассчитать заранее, на основе имеющихся данных о таблицах/индексах/запросах?

Неактивен

 

#11 22.11.2007 21:21:30

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

Re: Замедление при ORDER BY LIMIT

Вам наверное следует использовать индивидуальные кэши для таблиц, индексы которых собираетесь загрузить полностью. И команду LOAD INDEX INTO CACHE. Как описано в статье http://sqlinfo.ru/articles/info/3.html

Сколько займет весь индекс можно оценить, загрузив его в кэш. Альтернативные способы: размер MYI файла или длину индекса умножить на число строк.

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

sort_buffer_size - выделяется для каждой сессии при необходимости сортировки. Попробуйте записать данные, которые будете сортировать во временную таблицу и посмотреть ее размер.

Неактивен

 

#12 23.11.2007 12:48:07

Mikasto
Завсегдатай
Зарегистрирован: 25.09.2007
Сообщений: 35

Re: Замедление при ORDER BY LIMIT

rgbeast написал:

Поясню пример Павла. Пусть есть таблица  T (A int primary key, B varchar(10000), C int, D int);

У  Вас запрос
SELECT A,B from T where C=10 order by D limit 100,10;

При сортировке индекс может быть использован, если есть ключ KEY(C,D). Если ключ использован быть не может, то тогда MySQL сделает выборку всех строк, у которых C=10 и пары A,B отсортирует методом filesort. Подчеркну, MySQL будет сортировать строки как Вы их указали в запросе SELECT, то есть в них будут и значения B.

Предлагается следующая оптимизация, которая обычно дает 10кратный прирост производительности. Запрос заменяется на:
SELECT A from T where C=10 order by D limit 100,10;
Тогда сортироваться будут только значения A. Как результат запроса Вы получили 10 значений A. Теперь загоните их в список внутри своего приложения и сделайте второй запрос
SELECT A,B from T where A in (A1, A2, A3, ...., A10);

Все равно немного не понял smile
Я думал, что если указано сортировать по D, то сортируется только D, а А вынимается и В вынимается по ссылкам из индексов.
Зачем mysql соритрует А и В, если их нужно только вывести?

В вашем примере вывод будет другой. Вы хотите сразу отсечь кусок в 10 элементов.
Но тогда после исключения лишнего останется меньше 10 элементов.

SELECT A,B from T where A in (A1, A2, A3, ...., A10); - тут вообще сортировки нет, т.е. элементы будут расположены произвольно.


Я пришел к выводу, что сделать быстро данную операцию на больших ступенях limit невозможно.
Да и Яндекс, и Гугл не показывают больше 1000 страниц, просят уточнить поиск smile

Неактивен

 

#13 23.11.2007 12:56:50

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

Re: Замедление при ORDER BY LIMIT

Mikasto написал:

Все равно немного не понял smile
Я думал, что если указано сортировать по D, то сортируется только D, а А вынимается и В вынимается по ссылкам из индексов.
Зачем mysql соритрует А и В, если их нужно только вывести?

Зачем - это другой совсем вопрос. Факт, что делает он именно так. В случае без LIMIT, это более быстрый способ, чем потом вытягивать данные из таблицы по индексу, расположенные в произвольном порядке на диске.

В вашем примере вывод будет другой. Вы хотите сразу отсечь кусок в 10 элементов.
Но тогда после исключения лишнего останется меньше 10 элементов.

Все исключение лишнего должны быть включено в первый запрос.

SELECT A,B from T where A in (A1, A2, A3, ...., A10); - тут вообще сортировки нет, т.е. элементы будут расположены произвольно.

Да, здесь нужно добавить ORDER BY D, сортировка 10 элементов не займет времени.

Я пришел к выводу, что сделать быстро данную операцию на больших ступенях limit невозможно.
Да и Яндекс, и Гугл не показывают больше 1000 страниц, просят уточнить поиск smile

Попробуйте все таки сравнить производительность сортировки всей таблицы и сортировки только индексного поля.

Неактивен

 

#14 04.04.2008 03:53:53

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Замедление при ORDER BY LIMIT

rgbeast написал:

Предлагается следующая оптимизация, которая обычно дает 10кратный прирост производительности. Запрос заменяется на:
SELECT A from T where C=10 order by D limit 100,10;
Тогда сортироваться будут только значения A. Как результат запроса Вы получили 10 значений A. Теперь загоните их в список внутри своего приложения и сделайте второй запрос
SELECT A,B from T where A in (A1, A2, A3, ...., A10);

перечитывал тут
а зачем второй запрос, можно же одним обойтись?


Код:

SELECT A,B from T where A 
in (
SELECT A from T where C=10 order by D limit 100,10;
)

Разве нет?

Отредактированно Shopen (04.04.2008 03:55:03)

Неактивен

 

#15 04.04.2008 11:16:52

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

Re: Замедление при ORDER BY LIMIT

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

mysql> SELECT * FROM users WHERE id IN (SELECT id FROM users LIMIT 3,4);
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Неактивен

 

#16 05.04.2008 04:02:21

Shopen
Гуру
Откуда: Москва
Зарегистрирован: 22.10.2007
Сообщений: 362

Re: Замедление при ORDER BY LIMIT

не знал...

вообще странное ограничение. Логика же элементарная для оптимизатора, по крайней мере в случае автономного подзапроса (не ссылающегося на внешний). "Вычислить подзапрос-> Передать наверх"

Странно что это не сделано в mysql

Неактивен

 

#17 05.04.2008 07:26:45

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Замедление при ORDER BY LIMIT

Обещают сделать в 5.2 = 6.0. В 5.1 точно не будет sad

Неактивен

 

Board footer

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