как ускорить работу скрипта sql
Как оптимизировать SQL-запросы?
При оптимизации производительности разработчики и архитекторы часто упускают из виду настройку своих SQL-запросов. Понимание того, как работают базы данных, и написание более качественных SQL-запросов играет огромную роль в повышении производительности. Эффективные запросы SQL означают качественные масштабируемые приложения.
В этом руководстве мы рассмотрим 8 основных советов по SQL для оптимизации вашего SQL-сервера.
Совет 1. Выберите правильный тип данных для столбца
Каждый столбец таблицы в SQL имеет связанный тип данных. Вы можете выбирать из целых чисел, дат, переменных, логических значений, текста и т.д. При разработке важно выбрать правильный тип данных. Числа должны быть числового типа, даты должны быть датами и т.д. Это чрезвычайно важно для индексации.
Давайте посмотрим на пример ниже.
Совет 2: Табличные переменные и объединения
Давайте посмотрим на пример соединения:
Табличные переменные — это локальные переменные, которые временно хранят данные и обладают всеми свойствами локальных переменных. Не используйте табличные переменные в объединениях, как SQL видит их как одну строку. Несмотря на то, что они быстрые, табличные переменные плохо работают в соединениях.
Совет 3. Используйте условное предложение WHERE
Условные предложения WHERE используются для подмножества. Допустим, у вас есть такая ситуация:
С условным предложением WHERE это будет выглядеть так:
Совет 4: используйте SET NOCOUNT ON
С SET NOCOUNT ON SQL не будет подсчитывать затронутые строки и улучшить производительность.
В следующем примере мы предотвращаем отображение сообщения о количестве затронутых строк.
Совет 5: Избегайте ORDER BY, GROUP BY и DISTINCT
Совет 6. Полностью уточняйте имена объектов базы данных
Цель использования полностью определенных имен объектов базы данных — устранить двусмысленность. Полное имя объекта выглядит так:
Когда у вас есть доступ к нескольким базам данных, схемам и таблицам, становится важным указать, к чему вы хотите получить доступ. Вам не нужно этого делать, если вы не работаете с большими базами данных с несколькими пользователями и схемами, но это хорошая практика.
Поэтому вместо использования такого оператора:
Вам следует использовать:
Совет 7. Узнайте, как полностью защитить свой код
Базы данных хранят всевозможную информацию, что делает их основными целями атак. Распространенные атаки включают SQL-инъекции, когда пользователь вводит инструкцию SQL вместо имени пользователя и извлекает или изменяет вашу базу данных. Примеры SQL-инъекций:
Допустим, у вас есть это, вы textuserIDполучите ввод от пользователя. Вот как это может пойти не так:
Поскольку 1=1 всегда верно, он будет извлекать все данные из таблицы Users.
Вы можете защитить свою базу данных от SQL-инъекций, используя параметризованные операторы, проверки ввода, очистку ввода и т. Д. Как вы защищаете свою базу данных, зависит от СУБД. Вам нужно будет разобраться в своей СУБД и ее проблемах безопасности, чтобы вы могли писать безопасный код.
Совет 8: используйте LAG и LEAD для последовательных строк
Функция LAG позволяет запрашивать более одной строки в таблице, не вступая в таблицу к себе. Он возвращает значения из предыдущей строки таблицы.
Функция LEAD делает то же самое, но и для следующей строки.
Отказ от использования самостоятельных соединений повышает производительность, поскольку уменьшается количество операций чтения. Но, вы должны проверить, как LEAD и LAG влияют на производительность запросов.
Что изучать дальше
В этой статье мы рассмотрели несколько важных советов по SQL, но всегда есть чему поучиться. Вот несколько хороших следующих шагов:
Повышение скорости работы SQL-запросов
Сразу оговорюсь, запросы в примерах – Transact SQL, он мне как-то роднее =)
Но принципы, в общем-то, должны работать везде.
Статья не претендует на новизну, и тем более, на полноту. Я лишь попытался вспомнить часто встречающиеся ошибки или недочеты в запросах, которые приводят к медленной работе с БД.
Поиск показал, что статья частично пересекается с этим топиком, но не во всем =)
Типы данных в полях
Самое очевидное – не использовать типы данных «с запасом». То есть если у нас есть поле «ICQ» типа VarChar, делать его длиннее 10 символов бессмысленно. Аналогично, если есть внешний ключ к справочнику, в котором всего несколько записей, нет смысла задавать ему тип Int, хватит и SmallInt. Несмотря на очевидность ошибки, встречается повсеместно.
Использование * в запросе
Вообще говоря, было много споров на эту тему, но я стараюсь не использовать «*» в SQL-запросах.
Во-первых, явное перечисление выбираемых полей повышает читабельность кода.
Во-вторых, в выборке далеко не всегда нужны все поля таблицы. А если мы связываем в запросе несколько таблиц, то практически всегда конструкция «Select *» потянет из базы в выборку кучу ненужных полей, например, ключи, по которым связаны таблицы. Столкнулся один раз с ситуацией, когда в таблице в текстовом поле хранились наименования файлов, а в бинарном поле – их содержимое. И запрос, который должен был всего лишь выдавать список файлов, грузил в память сервера еще и их содержимое. Тормозило это безбожно.
Использование курсоров
Вариант 1, с курсором:
Вариант 2, без курсора:
Понятно, что на таком простом примере вариант 2 очевиден. Но при более сложных вычислениях, для простоты реализации программист выбирает вариант с курсором – и ощутимо проигрывает в скорости.
Использование индексов
Без комментариев. Про индексы забывают сплошь и рядом, особенно новички.
Использование хранимых процедур
При выполнении сложных вычислений, использующих много значений из БД, их лучше оформить в виде хранимых процедур на сервере, нежели вычислять на клиентской стороне – зачем передавать на клиент исходные данные для вычислений, когда можно передать только результат.
Использование временных таблиц
Периодически, особенно при работе с результатами вычислений или сводными данными, проще и быстрее один раз запустить сложный запрос на выборку всех необходимых данных, сохранить его результаты во временную таблицу и дальше работать уже с ней, нежели каждый раз запускать сложный и медленный запрос.
Пожалуй это все, что пришло в голову навскидку, если статья кого-нибудь заинтересует, можно повспоминать еще.
Оптимизация SQL запросов для ускорения сайтов
Дата публикации: 2017-12-06
От автора: вы знаете, что быстрый сайт == счастливые пользователи, выше рейтинг в google и повышенная конверсия. Возможно, вы думаете, что ваш сайт на WordPress максимально быстрый: вы смотрите за производительностью по статьям «лучшие практики настройки сервера», «решение проблем с медленным кодом» и «перемещение изображений на CDN». Но все ли это?
В динамических сайтах типа WordPress с базой данных у вас будет одна и та же проблема – запросы к базе данных замедляют сайт. В этой статье я покажу вам, как делается оптимизация SQL запросов, вызывающих замедления, и как понять, в чем проблема в этих запросах. Я буду использовать реальный запрос, который мы недавно правили, так как он замедлял портал покупателя deliciousbrains.com.
Определение
Первый шаг в решении медленных SQL запросов – найти их. Ashley восхваляла плагин отладки Query Monitor в блоге. Бесценным этот инструмент делает функция плагина запросов к базе данных для определения медленных SQL запросов. Вы можете фильтровать запросы по коду или компоненту (плагин, тема или ядро WordPress), которые их совершают. А также плагин умеет выделять повторяющиеся и медленные запросы:
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
Понятие
После нахождения медленных запросов далее необходимо попытаться понять, что их замедляет. Недавно при разработке нашего сайта мы нашли запрос, который выполнялся 8 секунд!
Для запуска магазина плагинов мы использовали WooCommerce и плагин WooCommerce Software Subscriptions. Суть запроса – получение всех подписок покупателя по его номеру. В WooCommerce довольно сложная модель данных. Хотя заказ и хранится в виде кастомного типа поста, но id покупателя не хранится в post_author, а является частью post мета данных. Также есть несколько подключений к пользовательским таблицам, созданным плагином подписки на ПО. Давайте более подробно разберем запрос.
MySQL твой друг
В MySQL есть полезное выражение DESCRIBE, с помощью которого можно выводить информацию о структуре таблицы (ее колонки, типы данных, значения по умолчанию). Если выполнить DESCRIBE wp_postmeta;, то вы увидите:
Круто, но вы, возможно, уже знаете об этом. Но знали ли вы, что префикс DESCRIBE можно использовать на SELECT, INSERT, UPDATE, REPLACE и DELETE? Более широко известен синоним EXPLAIN, который даст нам подробную информацию о том, как будет выполняться выражение.
Результат для медленного запроса:
На первый взгляд разобраться сложно. К счастью, ребята с SitePoint собрали полное руководство к пониманию выражения.
Самая главная колонка type, в ней описывается способ объединения таблиц. Если стоит ALL, значит MySQL читает всю таблицу с диска, увеличивает скорость чтения/записи и перекладывает загрузку на CPU. Процесс называется полное сканирование таблицы (более подробно позже).
Колонка rows также указывает на то, что MySQL должен делать. Она показывает, сколько строк необходимо просмотреть для нахождения результата.
Explain дает больше информации, чем мы можем оптимизировать. Например, таблица pm2 (wp_postmeta) говорит нам, что мы используем Using filesort, так как хотим, чтобы результаты хранились с помощью выражения ORDER BY. Если бы мы еще группировали запрос, это добавило бы еще больше нагрузки на выполнение.
Визуальное расследование
MySQL Workbench – еще один хороший бесплатный инструмент для расследований. Для баз данных MySQL 5.6 и выше результат EXPLAIN можно вывести в виде JSON, а MySQL Workbench превращает этот JSON в визуальный план выполнения выражения:
Он автоматически рисует предупреждения по проблемам, окрашивая части запроса, которые будут долго выполняться. Мы сразу видим, что присоединение к таблице wp_woocommerce_software_licences (алиас |) добавляет проблем.
Решение
Часть запроса выполняет полное сканирование таблицы, чего следует избегать, так как она использует колонку без индекса order_id для объединения между таблицами wp_woocommerce_software_licences и wp_posts. Распространенная проблема в медленных запросах. Ее очень легко решить.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
Индексы
Order_id – довольно важная часть определения данных в базе данных, и если мы будем строить запрос таким образом, то нам понадобится добавить индекс в эту колонку, или MySQL будет буквально сканировать все строки таблицы, пока не найдет необходимые. Добавим индекс и посмотрим, что он даст:
Нам удалось сэкономить 5 секунд запроса простым добавлением индекса, круто!
Знайте свой запрос
Проанализируйте запрос – join за join, подзапрос за подзапросом. Запрос делает то, что не должен? Его можно оптимизировать?
В нашем случае мы подключаем таблицу лицензий к таблице постов с помощью order_id, постоянно ограничивая выражение типами постов shop_order. Это необходимо для обеспечения целостности данных, чтобы убедиться, что мы используем только правильные записи заказов. На самом деле эта часть запроса устарела. Мы знаем, что безопаснее, чтобы в строке лицензии ПО в таблице был order_id, относящийся к заказу WooCommerce в таблице постов, поскольку это применяется в коде плагина PHP. Давайте удалим join и посмотрим, улучшит ли это запрос:
Несильно, но запрос теперь выполняется меньше 3 секунд.
Кэшируйте все!
Если на вашем сервере нет MySQL кэширования запросов по умолчанию, то лучше его включить. Так MySQL будет сохранять запись всех выполненных выражений с результатами, если выражение будет выполнено снова, вернется кэшированный результат. Кэш не устаревает, так как MySQL сбрасывает кэш при изменении таблицы.
Query Monitor выяснил, что наш запрос запускается 4 раза за одну загрузку страницы. Хотя и хорошо кэшировать MySQL запросы, но дублировать считывания из базы данных в одном запросе точно не стоит. Статическое кэширование в коде PHP – простой и крайне эффективный способ решения проблемы. Вы получаете результат запроса из базы данных при первом запросе и храните результат в статическом свойстве класса. В последующих вызовах будет возвращаться результат из статического свойства:
Кэш имеет время жизни запроса, более точно – время жизни объекта. Если вы ищите постоянные результаты запроса в других запросах, вам нужно реализовать постоянный Object Cache. Ваш код должен уметь включать кэш и устаревать кэш при изменении даты.
Мыслите нестандартно
Можно предпринять и другие способы для ускорения выполнения запросов, в которых нужно чуть больше, чем просто поправить запрос или добавить индекс. Одна из самых медленных частей нашего запроса – процесс объединения таблиц для перехода от id покупателя к id товара, и это необходимо делать для каждого покупателя. А что если сделать все объединения за раз, чтобы получать данные о покупателе тогда, когда это необходимо?
Вы можете денормализовать данные, создав таблицы, в которой хранятся данные лицензии, а также id пользователя и товара для всех лицензий, а также запрос к конкретному покупателю. Понадобится пересобрать таблицу с помощью MySQL triggers на INSERT/UPDATE/DELETE для таблицы лицензий (или других в зависимости от изменения данных), но это значительно повысит производительность запроса данных.
Если несколько join замедляют запрос, можно ускорить его, разбив на 2 и более выражения, после чего выполнять их отдельно в PHP, собирать и фильтровать результаты в коде. Laravel делает что-то похожее в жадной загрузке в Eloquent.
WordPress может замедлять запросы к таблице wp_posts, если объем данных большой, и присутствует множество кастомных типов постов. Если запросы типов постов замедляют сайт, попробуйте уйти от модели хранения кастомных типов постов в сторону кастомной таблицы.
Результаты
С помощью этих подходов по оптимизации запросов нам удалось ускорить наш запрос с 8 до 2 секунд, а также снизить количество вызовов с 4 до 1. Время запросов записывалось на версии для разработки, в продакшн оно было бы меньше.
Надеюсь, это руководство по ускорению медленных запросов помогло вам. Оптимизация запросов может показаться сложной, но как только вы попробуете, и у вас что-то получится, вы захотите еще сильнее улучшить запрос.
Автор: Iain Poulson
Редакция: Команда webformyself.
Бесплатный курс по PHP программированию
Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC
В курсе 39 уроков | 15 часов видео | исходники для каждого урока
Оптимизация SQL запросов или розыск опасных преступников
Кейс компании Appbooster
Полагаю, практически каждый проект, использующий Ruby on Rails и Postgres в качестве основного вооружения на бэкенде находится в перманентной борьбе между скоростью разработки, читаемостью/поддерживаемостью кода и скоростью работы проекта в продакшене. Я расскажу о своем опыте балансирования между этими тремя китами в кейсе, где на входе страдали читаемость и скорость работы, а на выходе получилось сделать то, что до меня безуспешно пытались сделать несколько талантливых инженеров.
Полностью вся история займёт несколько частей. Это первая, где я расскажу о том что такое PMDSC для оптимизации SQL-запросов, поделюсь полезными инструментами измерения эффективности запросов в postgres и напомню об одной полезной старой шпаргалке, которая до сих пор актуальна.
Сейчас, спустя какое-то время, “задним умом” я понимаю, что на входе в этот кейс совершенно не ожидал что у меня всё получится. Поэтому этот пост будет полезен скорее для смелых и не самых опытных разработчиков, чем для супер-сеньоров видавших рельсы с голым SQL.
Вводные данные
Мы в Appbooster занимаемся продвижением мобильных приложений. Чтобы легко выдвигать и проверять гипотезы мы разрабатываем несколько своих приложений. Бэкенд большинства из них это Rails API и Postgresql.
Герой этой публикации разрабатывается с конца 2013 года – тогда только-только вышел rails 4.1.0.beta1. С тех пор проект вырос в полноценно нагруженное веб-приложение, которое крутится на нескольких серверах в Amazon EC2 c отдельным инстансом базы данных в Amazon RDS (db.t3.xlarge с 4 vCPU и 16 GB RAM). Пиковые нагрузки доходят до 25k RPM, средняя нагрузка днём 8-10k RPM.
С инстанса базы данных, точнее с её кредитного баланса и началась эта история.
Как работает инстанс Postgres типа “t” в Амазон RDS: если ваша база данных работает со средним потреблением процессорного времени ниже определенного значения, то у вас на счету накапливаются кредиты, которые инстанс может тратить на потребление процессора в часы высокой нагрузки – это позволяет не переплачивать за серверные мощности и справляться с высокой нагрузкой. Более подробно о том за что и сколько платят, используя AWS можно прочитать в статье нашего CTO.
В один прекрасный день, я написал в ежедневном саммари о том, что очень устал тушить периодически возникающие в разных местах проекта “пожары”. Если так будет продолжаться, то бизнес задачам будет уделять время выгоревший разработчик. В тот же день я подошел к главному менеджеру проектов, объяснил расклад и попросил время на расследование причин периодических пожаров и ремонт. Получив добро, я начал собирать данные из разных систем мониторинга.
Мы используем Newrelic для отслеживания общего времени отклика за сутки. Картина выглядела так:
Желтым на графике выделена часть времени ответа, которую занимает Postgres. Как видно, иногда время ответа доходило до 1000 ms и большую часть времени именно база данных размышляла над ответом. Значит надо смотреть что происходит с SQL запросами.
PMDSC – простая и понятная практика для любой скучной работы оптимизации SQL запросов
Play it!
Measure it!
Draw it!
Suppose it!
Check it!
Play it!
Пожалуй, самая важная часть всей практики. Когда кто-то произносит фразу «Оптимизация SQL запросов» – это скорее вызывает приступ зевоты и скуку у абсолютного большинства людей. Когда ты произносишь «Детективное расследование и розыск опасных злодеев» – это сильнее вовлекает и настраивает тебя самого на нужный лад. Поэтому важно войти в игру. Мне понравилось играть в детектива. Я представлял себе что проблемы с базой данных либо опасные преступники, либо редкие болезни. А себя представлял в роли Шерлока Холмса, Лейтенанта Коломбо или Доктора Хауса. Выбирай героя на свой вкус и вперед!
Measure It!
Для анализа статистики запросов, я установил PgHero. Это очень удобный способ читать данные из расширения pg_stat_statements для Postgres. Заходим в /queries и смотрим на статистику всех запросов за последние сутки. Сортировка запросов по умолчанию по колонке Total Time – доля общего времени которое база данных обрабатывает запрос – ценный источник в поиске подозреваемых. Average Time – сколько в среднем запрос выполняется. Calls – сколько запросов было за выбранное время. PgHero считает медленными запросы, которые выполнялись более 100 раз за сутки и занимали в среднем более 20 миллисекунд. Список медленных запросов на первой странице, сразу после списка дублирующихся индексов.
Берём первый в списке и смотрим детали запроса, тут же можно посмотреть его explain analyze. Eсли planning time сильно меньше execution time, значит с этим запросом что-то не так и мы концентрируем внимание на этом подозреваемом.
В PgHero есть свой способ визуализации, но мне больше понравилось использовать explain.depesz.com копируя туда данные из explain analyze.
Один из подозреваемых запросов использует Index Scan. На визуализации видно что этот индекс не эффективен и является слабым местом – выделено красным. Отлично! Мы изучили следы подозреваемого и нашли важную улику! Правосудие неизбежно!
Draw it!
Нарисуем множество данных которые используются в проблемной части запроса. Будет полезно сравнить с тем какие данные покрывает индекс.
Немного контекста. Мы тестировали один из способов удержания аудитории в приложении – что-то вроде лотереи, в которой можно выиграть немного внутренней валюты. Делаешь ставку, загадываешь число от 0 до 100 и забираешь весь банк, если твое число оказалось ближе всех к тому что получил генератор случайных чисел. Мы назвали это “Арена”, а розыгрыши назвали “Битвами”.
В базе данных на момент расследования около пятисот тысяч записей о битвах. В проблемной части запроса мы ищем битвы в которых ставка не превышает баланс пользователя и статус битвы – жду игроков. Видим что пересечение множеств (выделено оранжевым) совсем маленькое количество записей.
Индекс, используемый в подозреваемой части запроса покрывает все созданные битвы по полю created_at. Запрос пробегает по 505330 записям из которых выбирает 40, а 505290 отсеивает. Выглядит очень расточительно.
Suppose it!
Выдвигаем гипотезу. Что поможет базе данных найти сорок записей из пятисот тысяч? Попробуем сделать индекс который покрывает поле ставка, только для битв со статусом “жду игроков” – паршиал индекс.
Паршиал индекс – существует только для тех записей, которые подходят под условие: поле статус равно “жду_игроков” и индексирует поле ставка – ровно то что в условии запроса. Очень выгодно использовать именно этот индекс: он занимает всего 40 килобайт и не покрывает те битвы которые уже сыграны и не нужны нам для получения выборки. Для сравнения – индекс index_arena_battles_on_created_at, который использовался подозреваемым занимает около 40 Мб, а таблица с битвами около 70 Мб. Этот индекс можно смело удалить, если его не используют другие запросы.
Check it!
Выкатываем миграцию с новым индексом в продакшен и наблюдаем за тем как изменился отклик эндпоинта с битвами.
На графике видно во сколько мы выкатили миграцию. Вечером 6 декабря время отклика уменьшилось примерно в 10 раз с
50ms. Подозреваемый в суде получил статус заключенного и теперь сидит в тюрьме. Отлично!
Prison Break
Спустя несколько дней мы поняли что рано радовались. Похоже, заключенный нашел сообщников, разработал и осуществил план побега.
Утром 11 декабря планировщик запросов postgres решил что использовать свежий паршиал индекс, ему больше не выгодно и стал снова использовать старый.
Мы снова на этапе Suppose it! Собираем дифференциальный диагноз, в духе доктора Хауса:
Давай вместе пройдемся по этому SQL. Выбираем все поля битвы из таблицы битв статус которых равен “жду игроков” и ставка меньше или равна некоему числу. Пока все понятно. Следующее слагаемое условия выглядит жутко.
Мы ищем не существующий результат подзапроса. Достань первое поле из таблицы участий в битвах, где идентификатор битвы совпадает и профиль участника принадлежит нашему игроку. Попробую нарисовать множество описанное в подзапросе.
Сложно осмыслить, но в итоге этим подзапросом мы пробовали исключить те битвы в которых игрок уже участвует. Смотрим общий explain запроса и видим Planning time: 0.180 ms, Execution time: 12.119 ms. Мы нашли сообщника!
Настало время моей любимой шпаргалки, которая гуляет по интернетам с 2008 года. Вот она:
Да! Как только в запросе встречается что-то, что должно исключить какое-то количество записей на основе данных из другой таблицы, в памяти должен всплыть этот мем с бородой и кудрями.
На самом деле вот что нам нужно:
Сохрани себе эту картинку, а еще лучше распечатай и повесь в нескольких местах в офисе.
Переписываем подзапрос на LEFT JOIN WHERE B.key IS NULL, получаем:
Исправленный запрос бежит сразу по двум таблицам. Мы присоединили “слева” таблицу с записями участий в битвах пользователя и добавили условие что идентификатор участия не существует. Смотрим explain analyze полученного запроса: Planning time: 0.185 ms, Execution time: 0.337 ms. Отлично! Теперь планировщик запросов не будет сомневаться что ему стоит использовать паршиал индекс, а будет использовать самый быстрый вариант. Сбежавший заключенный и его сообщник приговорены на пожизненное заключение в заведении строгого режима. Сбежать им будет сложнее.