Как ускорить 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, но всегда есть чему поучиться. Вот несколько хороших следующих шагов:

Источник

Оптимизация MySQL запросов

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

При написании запросов не используйте выборку всех полей — «*». Перечислите только те поля, которые вам действительно нужны. Это сократит количество выбираемых и пересылаемых данных. Кроме этого, не забывайте про покрывающие индексы. Даже если вам на самом деле необходимы все поля в таблице, лучше их перечислить. Во-первых, это повышает читабельность кода. При использовании звездочки невозможно узнать какие поля есть в таблице без заглядывания в нее. Во-вторых, со временем количество столбцов в вашей таблице может изменяться, и если сегодня это пять INT столбцов, то через месяц могут добавиться TEXT и BLOB поля, которые будут замедлять выборку.

Правило очень простое — чем меньше запросов, тем лучше (хотя из этого, как и из любого правила, есть исключения). Не забывайте про конструкцию IN(). Приведенный код можно написать одним запросом:
SELECT title, body FROM today_news INNER JOIN news USING(news_id)

Гораздо более эффективно склеить и выполнить один запрос:
INSERT INTO logs (value) VALUES (. ), (. )

3. Обновления
Иногда бывает нужно обновить несколько строк в одной таблице. Если обновляемое значение одинаковое, то все просто:
UPDATE news SET title=’test’ WHERE id IN (1, 2, 3).

Если изменяемое значение для каждой записи разное, то это можно сделать таким запросом:
UPDATE news SET
title = CASE
WHEN news_id = 1 THEN ‘aa’
WHEN news_id = 2 THEN ‘bb’ END
WHERE news_id IN (1, 2)

Наши тесты показывают, что такой запрос выполняется в 2-3 раза быстрее, чем несколько отдельных запросов.

Аналогичный пример:
SELECT user_id FROM users WHERE TO_DAYS(CURRENT_DATE) — TO_DAYS(registered) = DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
будет.

Нужно помнить, что при связи таблиц один-ко многим количество строк в выборке будет расти при каждом очередном JOIN’е. Для подобных случаев более быстрым бывает разбить подобный запрос на несколько простых.

Источник

Повышение скорости работы SQL-запросов

Сразу оговорюсь, запросы в примерах – Transact SQL, он мне как-то роднее =)
Но принципы, в общем-то, должны работать везде.
Статья не претендует на новизну, и тем более, на полноту. Я лишь попытался вспомнить часто встречающиеся ошибки или недочеты в запросах, которые приводят к медленной работе с БД.

Поиск показал, что статья частично пересекается с этим топиком, но не во всем =)

Типы данных в полях

Самое очевидное – не использовать типы данных «с запасом». То есть если у нас есть поле «ICQ» типа VarChar, делать его длиннее 10 символов бессмысленно. Аналогично, если есть внешний ключ к справочнику, в котором всего несколько записей, нет смысла задавать ему тип Int, хватит и SmallInt. Несмотря на очевидность ошибки, встречается повсеместно.

Использование * в запросе

Вообще говоря, было много споров на эту тему, но я стараюсь не использовать «*» в SQL-запросах.
Во-первых, явное перечисление выбираемых полей повышает читабельность кода.
Во-вторых, в выборке далеко не всегда нужны все поля таблицы. А если мы связываем в запросе несколько таблиц, то практически всегда конструкция «Select *» потянет из базы в выборку кучу ненужных полей, например, ключи, по которым связаны таблицы. Столкнулся один раз с ситуацией, когда в таблице в текстовом поле хранились наименования файлов, а в бинарном поле – их содержимое. И запрос, который должен был всего лишь выдавать список файлов, грузил в память сервера еще и их содержимое. Тормозило это безбожно.

Использование курсоров

Вариант 1, с курсором:

Вариант 2, без курсора:

Понятно, что на таком простом примере вариант 2 очевиден. Но при более сложных вычислениях, для простоты реализации программист выбирает вариант с курсором – и ощутимо проигрывает в скорости.

Использование индексов

Без комментариев. Про индексы забывают сплошь и рядом, особенно новички.

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

При выполнении сложных вычислений, использующих много значений из БД, их лучше оформить в виде хранимых процедур на сервере, нежели вычислять на клиентской стороне – зачем передавать на клиент исходные данные для вычислений, когда можно передать только результат.

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

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

Пожалуй это все, что пришло в голову навскидку, если статья кого-нибудь заинтересует, можно повспоминать еще.

Источник

Оптимизация сложных запросов MySQL

Введение

MySQL — весьма противоречивый продукт. С одной стороны, он имеет несравненное преимущество в скорости перед другими базами данных на простейших операциях/запросах. С другой стороны, он имеет настолько неразвитый (если не сказать недоразвитый) оптимизатор, что на сложных запросах проигрывает вчистую.

Прежде всего хотелось бы ограничить круг рассматриваемых проблем оптимизации «широкими» и большими таблицами. Скажем до 10m записей и размером до 20Gb, с большим количеством изменяемых запросов к ним. Если в вашей в таблице много миллионов записей, каждая размером по 100 байт, и пять несложных возможных запросов к ней — это статья не для Вас. NB: Рассматривается движок MySQL innodb/percona — в дальнейшем просто MySQL.

Большинство запросов не являются очень сложными. Поэтому очень важно знать как построить индекс для использования нужным запросом и/или модифицировать запрос таким образом, чтобы он использовал уже имеющиеся индексы. Мы рассмотрим работу оптимизатора для выбора индекса обычных запросов (select_type=simple), без джойнов, подзапросов и объединений.

Отбросим простейшие случаи для очень небольших таблиц, для которых оптимизатор зачастую использует type=all (полный просмотр) вне зависимости от наличия индексов — к примеру, классификатор с 40-ка записями. MySQL имеет алгоритм использования нескольких индексов (index merge), но работает этот алгоритм не очень часто, и только без order by. Единственный разумный способ пытаться использовать index merge — случаи выборки по разным столбцам с OR.

Еще одно отступление: подразумевается что читатель уже знаком с explain. Часто сам запрос немного модифицируется оптимизатором, поэтому для того, чтобы понять, почему использовался или нет тот или иной индекс, следует вызвать а затем который и покажет измененный оптимизатором запрос.

Покрывающий индекс — от толстых таблиц к индексам

Итак задача: пусть у нас есть довольно простой запрос, который выполняется довольно часто, но для такого частого вызова относительно медленно. Рассмотрим стратегию приведения нашего запроса к using index, как к наиболее быстрому выбору.

Следует указать на разницу в кешировании запросов в разных базах. Если PostgreSQL/Oracle кешируют планы запросов (как бы prepare for some timeout), то MySQL просто кеширует СТРОКУ запроса (включая значение параметров) и сохраняет результат запроса. То есть если последовательно селектировать несколько раз — то, если DDD не содержит изменяющихся функций, и таблица AAA не изменилась (в смысле используемой изоляции), результат будет взят прямо из кеша. Довольно спорное улучшение.

Источник

Справочник по языку SQL для ускорения запросов

Ускорение запросов поддерживает язык ANSI, аналогичный SQL, для выражения запросов к содержимому больших двоичных объектов. Диалект SQL для ускорения запросов — это подмножество ANSI SQL с ограниченным набором поддерживаемых типов данных, операторов и т. д. Оно также расширяет ANSI SQL для поддержки запросов к иерархическим частично структурированным форматам данных, таким как JSON.

Синтаксис SELECT

Единственной инструкцией SQL, поддерживаемой ускорением запросов, является инструкция SELECT. В этом примере возвращается каждая строка, для которой выражение возвращает значение true.

В следующем примере для каждой строки, для которой выражение WHERE возвращает значение true, эта инструкция вернет новую строку, созданную на основе вычисления каждого из выражений проекции.

Вы можете указать один или несколько отдельных столбцов в составе выражения SELECT (например, SELECT Title, Author, ISBN ).

В выражении SELECT можно использовать до 49 отдельных столбцов. Если вам нужно, чтобы результаты SELECT содержали более 49 столбцов, используйте в выражении SELECT подстановочный знак ( * ) (пример: SELECT * ).

В следующем примере возвращается статистическое вычисление (например, среднее значение конкретного столбца) для каждой строки, для которой expression возвращает значение true.

В следующем примере возвращаются подходящие смещения для разделения большого двоичного объекта в формате CSV. См. раздел Sys.Split этой статьи.

Типы данных

При чтении значений из данных в формате CSV все значения считываются как строки. Строковые значения могут быть преобразованы в другие типы с помощью выражений CAST. Значения могут быть неявно приведены к другим типам в зависимости от контекста. дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).

Выражения

Ссылки на поля

Для данных в формате JSON или данных в формате CSV со строкой заголовка на поля можно ссылаться по имени. Имена полей можно заключать в кавычки или использовать без кавычек. Заключенные в кавычки имена полей заключаются в двойные кавычки ( » ) и могут содержать пробелы, а также чувствительны к регистру. В именах полей без кавычек регистр не учитывается и они не могут содержать специальные символы.

Операторы

Поддерживаются следующие стандартные операторы SQL.

Если типы данных слева и справа от оператора различаются, автоматическое преобразование будет выполняться в соответствии с указанными здесь правилами: Приоритет типов данных (Transact-SQL).

Язык SQL с ускорением запросов поддерживает только небольшое подмножество типов данных, обсуждаемых в этой статье. Дополнительные сведения см. в разделе Типы данных этой статьи.

Приведения

Язык SQL с ускорением запросов поддерживает оператор CAST в соответствии с правилами, приведенными здесь: Преобразование типов данных (ядро СУБД).

Язык SQL с ускорением запросов поддерживает только небольшое подмножество типов данных, обсуждаемых в этой статье. Дополнительные сведения см. в разделе Типы данных этой статьи.

Строковые функции

Язык SQL для ускорения запросов поддерживает следующие стандартные строковые функции SQL.

Вот несколько примеров.

Функции данных

Поддерживаются следующие стандартные функции даты SQL:

На данный момент все форматы даты стандартного IS08601 преобразованы.

Функция DATE_ADD

Функция DATE_DIFF

Функция EXTRACT

Функция TO_STRING

Функция TO_TIMESTAMP

Поддерживаются только форматы IS08601.

Можно также использовать функцию UTCNOW для получения системного времени.

Статистические выражения

Инструкция SELECT может содержать либо одно, либо несколько выражений проекции или одно статистическое выражение. Поддерживаются следующие статистические функции.

MISSING

Оператор IS MISSING является единственным нестандартным, поддерживаемым языком SQL для ускорения запросов. Если в данных JSON отсутствует поле из определенной входной записи, то поле выражения IS MISSING будет иметь логическое значение true.

Дескрипторы таблиц

Для данных JSON доступны дополнительные параметры:

Это позволяет выполнять запросы к подмножествам данных JSON.

Для запросов JSON можно указать путь в части предложения FROM. Эти пути могут помочь при анализе подмножества данных JSON. Эти пути могут ссылаться на значения массива и объекта JSON.

Давайте рассмотрим пример подробнее, чтобы разобраться в этом.

Это наш пример данных:

Возможно, вас интересует только объект JSON warehouses из указанных выше данных. Объект warehouses является типом массива JSON, поэтому его можно упомянуть в предложении FROM. Образец запроса может выглядеть примерно так.

Запрос получает все поля, но выбирает только широту.

BlobStorage и BlobStorage[*] ссылаются на весь объект. Однако если в предложении FROM есть путь, то необходимо использовать BlobStorage[*].path

Sys.Split

Это специальная форма инструкции SELECT, которая доступна только для данных в формате CSV.

Используйте эту инструкцию в тех случаях, когда необходимо загрузить и затем обработать записи данных в формате CSV в пакетах. Таким образом, можно обрабатывать записи параллельно, вместо того чтобы загружать все записи за один раз. Эта инструкция не возвращает записи из CSV-файла. Вместо этого он возвращает коллекцию размеров пакетов. Затем можно использовать каждый из размеров пакетов для получения пакета записей данных.

Используйте параметр split_size, чтобы указать число байтов, которое должен содержать каждый пакет. Например, если вы хотите обрабатывать только 10 МБ данных одновременно, то оператор будет выглядеть следующим образом: SELECT sys.split(10485760)FROM BlobStorage так как 10 МБ равно 10 485 760 байт. Каждый пакет будет содержать столько записей, сколько может уместиться в 10 МБ.

В большинстве случаев размер каждого пакета будет немного выше указанного числа. Это обусловлено тем, что пакет не может содержать частичную запись. Если последняя запись в пакете начинается до окончания порогового значения, пакет будет больше, чтобы он мог содержать всю запись. Размер последнего пакета, скорее всего, будет меньше указанного размера.

Значение split_size должно быть не менее 10 МБ (10485760).

Источник