|
Задавайте вопросы, мы ответим
Вы не зашли.
Насколько я поня лпроблема не только у меня...
Есть таблица 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? 
Очень жду..
Неактивен
А еще интересно, есть ли какие способы ускорения кроме разбития на части большой таблицы?
Неактивен
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. Кто пишет?
Вообще, LIMIT большоечисло, маленькоечисло - это не удачное решение,
такой LIMIT никак не влияет на использование индексов, т.к. Вы выбираете
почти всю таблицу, просто выбрасываете первые данные.
Возможная оптимизация - выбирать сначала нужные id из таблицы с нужными
where и сортировками во временную табличку, а потом второй запрос с join по
id. Увеличение производительности в том, что сортировать нужно меньше
данных (только id).
Неактивен
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. Кто пишет?
http://www.mysql.ru/docs/man/ORDER_BY_optimisation.html
paulus написал:
Возможная оптимизация - выбирать сначала нужные id из таблицы с нужными
where и сортировками во временную табличку, а потом второй запрос с join по
id. Увеличение производительности в том, что сортировать нужно меньше
данных (только id).
На мой взгляд это все делает MYSQL - т.е. сначала делает выборку куска по индексам, а потом
сортирует во временной таблице, которая лежит в файле (filesort). Но таблица получается слишком большой.
А сделать временные таблицы для каждого варианта набора условий WHERE по-моему нереально.
Неактивен
Нет, "<" будет ничуть не лучше, чем NOT.
Прирост на SELECT может быть, если в таблицу никто не пишет. Значительного не будет.
Память под сортировку данных выделяется на поток, в момент необходимости, освобождается
после выполнения запроса. Память под индексы выделяется в момент старта сервера,
освобождается при остановке сервера.
mysql.ru - это странный ресурс, Вы не первый, кто говорит о том, что у них написаны странные
вещи. Пользуйтесь mysql.com Указанная переменная есть в исходниках MySQL
Само ничего не происходит. Глядите, пусть есть тривиальный случай таблицы с двумя полями
А и Б, где А - индекс, Б - данные. Если Вам надо отсортировать (filesort) по А, а потом выбрать
оттуда LIMIT 500,5, то логичнее выбрать оттуда _только_ А (который занимает меньше места),
отсортировать его filesort, выбрать нужные значения А, а потом подставить уже данные Б.
Отличие от Вашего метода в том, что Вы выбираете и А и Б (а, стало быть, гоняете по памяти
при сортировке кучу ненужных данных).
P.S. Нашел такую переменную в 4.0
Неактивен
paulus написал:
Само ничего не происходит. Глядите, пусть есть тривиальный случай таблицы с двумя полями
А и Б, где А - индекс, Б - данные. Если Вам надо отсортировать (filesort) по А, а потом выбрать
оттуда LIMIT 500,5, то логичнее выбрать оттуда _только_ А (который занимает меньше места),
отсортировать его filesort, выбрать нужные значения А, а потом подставить уже данные Б.
Отличие от Вашего метода в том, что Вы выбираете и А и Б (а, стало быть, гоняете по памяти
при сортировке кучу ненужных данных).
По-моему, Вы про другой случай
У меня только два варианта:
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
Я ожидал, что в этом случае будет использоваться только индекс! 
Может ошибка в чем-то?
Неактивен
Хорошо, сравните время выполнения двух конструкций
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
Я подозреваю, что вторая будет выполняться быстрее в несколько раз.
Неактивен
Поясню пример Павла. Пусть есть таблица 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);
Неактивен
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
Я ожидал, что в этом случае будет использоваться только индекс!
Может ошибка в чем-то?
Попробуйте изменить ключ на KEY `so` (`id_business`,`id_region`,`so`)
Неактивен
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% то можно потом понизить соответствующее значение, но для этого нужно время. Поэтому и хочется знать - как то можно все эти параметры рассчитать заранее, на основе имеющихся данных о таблицах/индексах/запросах?
Неактивен
Вам наверное следует использовать индивидуальные кэши для таблиц, индексы которых собираетесь загрузить полностью. И команду LOAD INDEX INTO CACHE. Как описано в статье http://sqlinfo.ru/articles/info/3.html
Сколько займет весь индекс можно оценить, загрузив его в кэш. Альтернативные способы: размер MYI файла или длину индекса умножить на число строк.
Негативных последствий здесь нет.
sort_buffer_size - выделяется для каждой сессии при необходимости сортировки. Попробуйте записать данные, которые будете сортировать во временную таблицу и посмотреть ее размер.
Неактивен
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);
Все равно немного не понял
Я думал, что если указано сортировать по D, то сортируется только D, а А вынимается и В вынимается по ссылкам из индексов.
Зачем mysql соритрует А и В, если их нужно только вывести?
В вашем примере вывод будет другой. Вы хотите сразу отсечь кусок в 10 элементов.
Но тогда после исключения лишнего останется меньше 10 элементов.
SELECT A,B from T where A in (A1, A2, A3, ...., A10); - тут вообще сортировки нет, т.е. элементы будут расположены произвольно.
Я пришел к выводу, что сделать быстро данную операцию на больших ступенях limit невозможно.
Да и Яндекс, и Гугл не показывают больше 1000 страниц, просят уточнить поиск
Неактивен
Mikasto написал:
Все равно немного не понял
Я думал, что если указано сортировать по D, то сортируется только D, а А вынимается и В вынимается по ссылкам из индексов.
Зачем mysql соритрует А и В, если их нужно только вывести?
Зачем - это другой совсем вопрос. Факт, что делает он именно так. В случае без LIMIT, это более быстрый способ, чем потом вытягивать данные из таблицы по индексу, расположенные в произвольном порядке на диске.
В вашем примере вывод будет другой. Вы хотите сразу отсечь кусок в 10 элементов.
Но тогда после исключения лишнего останется меньше 10 элементов.
Все исключение лишнего должны быть включено в первый запрос.
SELECT A,B from T where A in (A1, A2, A3, ...., A10); - тут вообще сортировки нет, т.е. элементы будут расположены произвольно.
Да, здесь нужно добавить ORDER BY D, сортировка 10 элементов не займет времени.
Я пришел к выводу, что сделать быстро данную операцию на больших ступенях limit невозможно.
Да и Яндекс, и Гугл не показывают больше 1000 страниц, просят уточнить поиск
Попробуйте все таки сравнить производительность сортировки всей таблицы и сортировки только индексного поля.
Неактивен
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)
Неактивен
К сожалению, одним запросом не обойтись из-за еще не устраненных ограничений, связанных с подзапросами
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'
Неактивен
не знал...
вообще странное ограничение. Логика же элементарная для оптимизатора, по крайней мере в случае автономного подзапроса (не ссылающегося на внешний). "Вычислить подзапрос-> Передать наверх"
Странно что это не сделано в mysql
Неактивен
Обещают сделать в 5.2 = 6.0. В 5.1 точно не будет
Неактивен
