вторник, 10 октября 2017 г.

Турбоускоритель для MySQL

Турбоускоритель для MySQL

Часто ли ты задумывался об оптимизации работы своих сайтов? Что, к примеру, будет, если ты раскрутишься до 5000 посетителей в день? 10000? Недавно мне пришлось заняться небольшим исследованием в этой области.

Один из моих новых социальных проектов, который вот-вот запустится, требует от сервера приличных ресурсов: база на несколько сотен тысяч записей, много работы со строками, массивами, много логики. Для PHP+MySQL это оказалось не такой простой задачей — в первом приближении результат выдавался пользователю за 15 секунд!!! Что-то с этим надо было делать.

Тут многие из вас начнут говорить про то, что крупные проекты на PHP не делаются. Заранее с вами согласен, но тестовую версию проще, быстрее и дешевле сделать на PHP.

Используем индексы в таблицах

После тестирования на время разных частей скрипта я обнаружил, что есть запросы, которые работают очень долго. Я погуглил и выяснил: введение индексов должно заметно сокращать время выдачи результата по запросу.

Индексирование таблицы позволяет СУБД обращаться к записям как к элементам массива, что намного быстрее, чем простой перебор. В phpMyAdmin это делается просто: в режиме «Структура таблицы» напротив нужного поля надо нажать иконку с молнией. Через консоль это делается так:

1
ALTER TABLE mytable ADD INDEX (mycolumn)

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

Есть один момент: индексы надо создавать по тем полям, значения которых имеют очень большой диапазон. Например, поле цены у товара может быть от 100 до 10000 рублей: по такому полю имеет смысл делать индексацию. А вот по полям, которые могут принимать менее 10 значений, индексы прироста не дадут. Но в любом случае надо экспериментировать.

В результате введения индексов время работы моего скрипта сократилось до трех-четырех секунд.

Чудо-ключи

Но не каждый пользователь готов ждать ответ на свой вопрос целых четыре секунды. Погуглив дальше я нашел отличную штуку: ключи в SELECT-запросе. Самым удачным в плане повышения скорости выполнения запроса оказался ключ STRAIGHT_JOIN. Этот ключ принуждает MySQL делать выборку из нескольких таблиц в строго заданном порядке. Пример использования этого ключа:

1
2
3
SELECT /*! STRAIGHT_JOIN*/ * 
FROM cars,users 
WHERE users.carid=car.id AND cars.id = 5

Принудительная последовательность выборки из нескольких таблиц нужна для того, чтобы уменьшить количество перебираемых строк. То есть, сначала нужно делать выборку, результат которой ожидается самым минимальным по количеству строк, а потом уже пересекать его с более объемными таблицами. В любом случае, с порядком перечисления таблиц в запросе всегда можно экспериментировать.

В результате применения ключей STRAIGHT_JOIN и SQL_NO_CACHE время обработки сократилось с 3—4 секунд до 0.7—0.9. В итоге после применения всех оптимизаций время работы скрипта сократилось с 15 секунд в 16-22 раза. Совершенно бесплатно 8)

На сладкое

На десерт сегодня оператор EXPLAIN, который показывает последовательность выполнения запроса. Очень полезная штука, которая как раз и позволяет найти нужный порядок таблиц в сложных выборках.

И вообще из множества статей на тему оптимизации работы веб-приложений мне стало понятно одно: чем больше переложено на СУБД — тем быстрее будет работать приложение. Да и портировать его на другие платформы будет намного проще.

Автор: Юрий Гугнин.



Комментарии:


Дядя Эдик
Полезная статья, дающая повод дальнейшему изучению предмета. Оптимизацию SQL структур в маленькой заметке не напишешь. Индексирование всего и вся - не панацея, это может привести к еще большим тормозам запросов. Да, еще. Индекс - это не массив. а скорее - двунаправленный упорядоченный список. C MySQL работать по оптимизации не приходилось, а многогигабайтные базы SQL сервера ворочать приходилось на протяжении 3-х лет :)

Юрий Гугнин
Про ненужность индексирования полей с маленьким множеством значений я написал.
Насчет полного описания фишек оптимизации — согласен, потому и дал много ссылок для самостоятельного изучения 8)

Дядя Эдик
Не то что бы их не надо индексировать. Я, к сожалению, мало знаю про MySQL, но на опыте SQL сервер могу сказать, что иногда их можно включать в составной индекс, который поможет выполнению запроса.

Юрий Гугнин
У меня в MySQL от таких индексов были только ухудшения. Вполне возможно, что в MS SQL Server другие алгоритмы.
Как ни крути, а оптимизация работы с БД зачастую связана с магией и разбором каждого конкретного случая 8)))

Дядя Эдик
Возможно. Стратегии выбора оптимального плана выполнения запросов - святая святых и коммерческая и интеллектуальная тайна (сам алгоритм) любой РСУБД.

vova
Жаль, что мне данная область пока не знакома, но в скором будующем уже собирпюсь осваивать, поэтому статью припрятал.

Kolya
А вы знаете шо индексирование открывает широкие возможности для всякого рода атак))) примеры приводить не буду, пусть это станет для вас сюрпризом)))

Дядя Эдик
каким образом? индексы - это же внутреннее дело сервака. Хотя принудительное включение индекса вполне может привести к вреду. Но для этого надо знать текст запроса.

Юрий Гугнин
Если честно, потратил в гугле около 15 минут в поисках хотя бы отдаленного упоминания об утечках безопасности при пользовании индексами, ничего подобного не нашел.
Уж вряд ли есть такой эксплоит «с широкими возможностями», который не обмусолили на форумах 8)

Дядя Эдик
вот и я про это же самое написал. индекс используется исключительно сервисом оптимизатора запросов на движке сервака.


Другие посты по этой теме:



Комментариев нет: