Есть таблица MyISAM в MySQL, 100k записей.
Выполняю запрос такого вида:
SELECT c1 FROM t WHERE c2=x ORDER BY c3 LIMIT 10000,10
c2 — varchar(2), c3 date.
Время выполнения порядка 150 мс. Есть индексы на c2, c3 и на c2 и c3 одновременно. Игрался с FORCE INDEX, дает только ухудшение производительности, но если посмотреть explain в этих случаях исчезает примечание filesort. Переменные mySQL: sort buffer size 1m, myisam sort buffer size 16m.
Простые запросы SELECT c1 FROM t ORDER BY c3 LIMIT 10000,10, SELECT c1 FROM t WHERE c2=x LIMIT 10000,10 оба отрабатывают ~50 мс. Таких же результатов хочется получить и в исходным запросе. Возможно ли и какие есть варианты?
Здравствуйте, vb-develop, Вы писали:
v> Есть таблица MyISAM в MySQL, 100k записей.
Покажи explain на все три запроса. А еще лучше все же DDL таблички и скрипт ее заполнения, который бы воспроизводил ситуацию.
В общем случае, инструкция LIMIT 10000,10 — очень плохая. Потому как согласно ей под условие выборки попадает > 10% записей таблицы и индексы могут стать совершенно неэффективными.
В частном случае, ORDER BY можно выполнить на клиенте — для 10 записей эта операция будет существенно быстрее.
Здравствуйте, Anton Batenev, Вы писали:
AB>Здравствуйте, vb-develop, Вы писали:
v>> Есть таблица MyISAM в MySQL, 100k записей.
AB>Покажи explain на все три запроса. А еще лучше все же DDL таблички и скрипт ее заполнения, который бы воспроизводил ситуацию. AB>В общем случае, инструкция LIMIT 10000,10 — очень плохая. Потому как согласно ей под условие выборки попадает > 10% записей таблицы и индексы могут стать совершенно неэффективными. AB>В частном случае, ORDER BY можно выполнить на клиенте — для 10 записей эта операция будет существенно быстрее.
Это в продакшне работает (скрипт если только тестовый сгенерить), сайт на основе DataLife engine (редкостная бяка, но почему-то популярная).
Задача этим запросом решается такая: постраничная навигация внутри списка категорий. Элементы упорядочены по дате (col3 в моем примере), фильтруются по категориям (col2). Т.е. тут задача найти 10 записей на N-й странице, при сортивке записей по дате (кстати, если сортировать по PK, не добавляя новых индексов, перфоманс падает еще в 2-3 раза).
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE dle_post ref category_3 category_3 4 const 21084 Using where; Using filesort
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE dle_post index NULL date 8 NULL 76950
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE dle_post ref category_3 category_3 4 const 21085 Using where
Re[2]: MySQL как улучшить производительность запроса
Здравствуйте, Anton Batenev, Вы писали:
AB>В общем случае, инструкция LIMIT 10000,10 — очень плохая. Потому как согласно ей под условие выборки попадает > 10% записей таблицы и индексы могут стать совершенно неэффективными. AB>В частном случае, ORDER BY можно выполнить на клиенте — для 10 записей эта операция будет существенно быстрее.
Как постраничную навигации по заданной категории эффективнее реализовать?
Re[3]: MySQL как улучшить производительность запроса
VD> Т.е. тут задача найти 10 записей на N-й странице, при сортивке записей по дате (кстати, если сортировать по PK, не добавляя новых индексов, перфоманс падает еще в 2-3 раза).
А нужно именно на сотой странице? Я бы на твоем месте проверил, работает ли приемлимо для первых 10 страниц и не парился на счёт скорости выдачи сотой страницы
In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:
....
— The key used to fetch the rows is not the same as the one used in the ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
Почитай там советы, на вскидку может стоит пошаманить с max_length_for_sort_data ?
VD>Время выполнения порядка 150 мс. Есть индексы на c2, c3 и на c2 и c3 одновременно. Игрался с FORCE INDEX, дает только ухудшение производительности, но если посмотреть explain в этих случаях исчезает примечание filesort. Переменные mySQL: sort buffer size 1m, myisam sort buffer size 16m.
Здравствуйте, vb-develop, Вы писали:
v> Как постраничную навигации по заданной категории эффективнее реализовать?
Завести непрерывный счетчик внутри каждой категории и обновлять его в случае удаления записи в направлении ближайшего "хвоста". Поскольку записи удаляются не часто и чаще всего из последних, то цена обновления не столь критична как цена выборки, а выборка будет делаться простой арифметикой по диапазону MAX-MIN с соответствующей страницы. Это решение из наиболее простых.
К-л общего решения для эффективной постраничной навигации нет.
P.S. Планы запросов действительно ужасны для 100К записей и дальше будет только хуже (с ростом числа записей).
Здравствуйте, avpavlov, Вы писали:
a> А нужно именно на сотой странице? Я бы на твоем месте проверил, работает ли приемлимо для первых 10 страниц и не парился на счёт скорости выдачи сотой страницы
Это справедливо, если учитывать поведение только реальных пользователей, которые почти на ходят дальше первых двух. Но увы, есть еще роботы и есть "зловреды", которые при планировании DDoS атак используют данный фактор — 99.99% CMS с постраничной навигацией не умеют эффективно работать с последними страницами, что делает их достаточно легкими для заваливания.
Здравствуйте, Anton Batenev, Вы писали:
AB>Здравствуйте, avpavlov, Вы писали:
a>> А нужно именно на сотой странице? Я бы на твоем месте проверил, работает ли приемлимо для первых 10 страниц и не парился на счёт скорости выдачи сотой страницы
AB>есть еще роботы
А разве для роботов не существует "глубины просмотра"? Полезет ли он на сотую страницу иными словами говоря?
AB>и есть "зловреды",
Ну планировать борьбу с Ддос путем ускорения выдачи это чересчур Так и так завалят, если захотят, и если не предусмотрено более продвинутой защиты, типа ограничения числа запросов с одного и того же ИП.
Re[6]: MySQL как улучшить производительность запроса
Здравствуйте, avpavlov, Вы писали:
a> AB>есть еще роботы a> А разве для роботов не существует "глубины просмотра"? Полезет ли он на сотую страницу иными словами говоря?
А почему нет? Роботу же безразлично — начал он с первой страницы (главной) или со страницы, где в URL есть "?page=100".
a> Ну планировать борьбу с Ддос путем ускорения выдачи это чересчур Так и так завалят, если захотят, и если не предусмотрено более продвинутой защиты, типа ограничения числа запросов с одного и того же ИП.
Да, но разница в цене вопроса для заказчика. Ограничение по числу запросов с IP должно носить разумный характер, чтобы не забанить легитимных пользователей => мы его преодолеваем простым увеличением количества бот-машин. С учетом того, что одна бот-машина стоит около 1-10$ в сутки (по разным расценкам), получаем существенный профит в виде того, сколько будет длиться атака и хватит ли у заказчика вообще бабла, чтобы положить сервер.
AB>А почему нет? Роботу же безразлично — начал он с первой страницы (главной) или со страницы, где в URL есть "?page=100".
Насколько я понимаю, робот начнёт от тех страниц, что указаны в роботс.тхт. Просто так он с сотой не начнёт.
AB>Да, но разница в цене вопроса для заказчика. Ограничение по числу запросов с IP должно носить разумный характер, чтобы не забанить легитимных пользователей => мы его преодолеваем простым увеличением количества бот-машин. С учетом того, что одна бот-машина стоит около 1-10$ в сутки (по разным расценкам), получаем существенный профит в виде того, сколько будет длиться атака и хватит ли у заказчика вообще бабла, чтобы положить сервер.
Ну допустим речь идёт о 8ми ядерном сервере. ТС хочет добиться от запроса 50мс. Пусть будет, что кроме запроса ничего нет. Значит одним ядром мы обработаем 20 запросов в секунду, а на 8ми — 160 запросов. Я не знаю какую избыточность закладывают ДДОСеры, допустим 3 кратную (да и этого не нужно, даже для 161 запроса уже начнёт расти очередь) и каждый бот должен посылать запрос не чаще чем раз в 3 секунды.
Тогда на генерацию 480 запросов каждую секунду требуется 1440 ботов. Если ответ требует 150мс тогда 480 ботов. Разница в 1000 ботов для заявленных тобой цен не выглядит как-то сильно большой, просто заложат в сумму выкупа.
А вот простейшая защита на число запросов не только в секунду, но и допустим в 10 минут (больше 100 запросов = ддосер) заставит каждые 5 минут вводить нового бота, что усложняет атаку.
Re[8]: MySQL как улучшить производительность запроса
Здравствуйте, avpavlov, Вы писали:
a> AB>А почему нет? Роботу же безразлично — начал он с первой страницы (главной) или со страницы, где в URL есть "?page=100". a> Насколько я понимаю, робот начнёт от тех страниц, что указаны в роботс.тхт. Просто так он с сотой не начнёт.
Возможно я не совсем тебя понимаю, но что мешает во внешнем мире появиться ссылке на 100-ю страницу с которой робот на нее и "перейдет"? Или что мешает роботу перейти на первую, с первой на вторую и т.д.?
a> Тогда на генерацию 480 запросов каждую секунду требуется 1440 ботов. Если ответ требует 150мс тогда 480 ботов. Разница в 1000 ботов для заявленных тобой цен не выглядит как-то сильно большой, просто заложат в сумму выкупа.
Разница в 1000$ в день для заказчика. И заказчику эти деньги придется как-то потом отбивать играя на лежачем конкуренте.
a> А вот простейшая защита на число запросов не только в секунду, но и допустим в 10 минут (больше 100 запросов = ддосер) заставит каждые 5 минут вводить нового бота, что усложняет атаку.
Исполнителю данная техника так же известна — это просто делает атаку дороже и стоимость растет в прогрессии от производительности.
З.Ы. Что-то мы ушли в оффтоп. Преждевременная оптимизация, конечно, зло, но и выборки по 20К строк, которые будут линейно расти с увеличением количества записей, согласись, тоже ничего хорошего не сулят.
VD>>Время выполнения порядка 150 мс. Есть индексы на c2, c3 и на c2 и c3 одновременно. Игрался с FORCE INDEX, дает только ухудшение производительности, но если посмотреть explain в этих случаях исчезает примечание filesort. Переменные mySQL: sort buffer size 1m, myisam sort buffer size 16m.
A>И кстати рекомендация
A>http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
A>
A> The following queries use the index to resolve the ORDER BY part:
A>...
A>SELECT * FROM t1
A> WHERE key_part1=1
A> ORDER BY key_part1 DESC, key_part2 DESC;
A>Короче, пропиши фильтр в ORDER BY, даже если он там и выглядит лишним
Спасибо за предложение, но в плане времени отклика изменение ничего не дало
Re[4]: MySQL как улучшить производительность запроса
Здравствуйте, Anton Batenev, Вы писали:
AB>Здравствуйте, vb-develop, Вы писали:
v>> Как постраничную навигации по заданной категории эффективнее реализовать?
AB>Завести непрерывный счетчик внутри каждой категории и обновлять его в случае удаления записи в направлении ближайшего "хвоста". Поскольку записи удаляются не часто и чаще всего из последних, то цена обновления не столь критична как цена выборки, а выборка будет делаться простой арифметикой по диапазону MAX-MIN с соответствующей страницы. Это решение из наиболее простых.
Записи вообще не удаляются и не редактируются, либо чрезвычайно редко. Но твою идею не совсем понял. А если кроме категории есть еще фильтры по другим полям, например enabled, по интервалу дат (в исходном посте для простоты я их не стал включать в запрос, т.к. что с ними, что без них результат одинаковый).
Re[5]: MySQL как улучшить производительность запроса
Здравствуйте, vb-develop, Вы писали:
v> Записи вообще не удаляются и не редактируются, либо чрезвычайно редко. Но твою идею не совсем понял. А если кроме категории есть еще фильтры по другим полям, например enabled, по интервалу дат (в исходном посте для простоты я их не стал включать в запрос, т.к. что с ними, что без них результат одинаковый).
Если фильтры постоянные и их мало, то рассчитать для каждого из фильтров.
Если же фильтры динамические, то можно попробовать другой способ — запоминать значения монотонного поля (ORDER BY) на текущей странице, чтобы на следующей изначально не включать в фильтр то, что точно в него не сможет попасть. Т.е., например, для монотонно возрастающего автоинкрементального ID получаем для первой страницы:
... LIMIT 0,10
Для второй и последующей страницы:
... WHERE `id` > 'max_id_on_prev_page' LIMIT 0,10
Т.е. первая цифра в LIMIT у нас теперь всегда остается 0 за счет того, что мы добавляем условие, которое отсекает заведомо лишние страницы по одному из полей упорядочивания (id, дата и т.д.).
AB>Т.е. первая цифра в LIMIT у нас теперь всегда остается 0 за счет того, что мы добавляем условие, которое отсекает заведомо лишние страницы по одному из полей упорядочивания (id, дата и т.д.).
Спасибо, неплохая оптимизация на заметку. Правда работает только при навигации вперед-назад, либо max_id_on_prev_page нужно отдельно кешировать для каждой странице и каждой категории. Из интереса на моем запросе время отклика уменьшилось на 50 мс (было 150, стало 100), что очень неплохо.
