Советы по SQL для повышения производительности базы данных
Когда мы разрабатываем, важно знать, как правильно взаимодействовать с нашей базой данных. Однако часто мы смотрим только на параметры конфигурации базы данных и мощность сервера, на котором она работает. Хотя это важно, гораздо важнее знать, как правильно использовать язык SQL для взаимодействия с базой данных. По этой причине в этом посте мы увидим ряд советов по созданию SQL-запросов, которые повысят производительность нашей базы данных.
Примеры могут быть применены к различным реляционным базам данных.
Давайте начнем.
Общие советы
1. Проанализируйте план выполнения
Когда мы выполняем оператор SQL, наш механизм базы данных сначала анализирует, нет ли синтаксических ошибок. Затем он определяет оптимальный способ выполнения оператора; то есть он создает свой план выполнения.
Мы можем использовать этот план выполнения SQL для анализа выполнения оператора SQL и поиска проблем с производительностью.
Способ сделать это просто. Например, в Oracle мы можем использовать следующую команду:
Или в PostgreSQL:
Первое число в кортеже стоимости (36.04..37.05) — это начальные затраты, а второе — общие затраты. Общая стоимость — это все время выполнения компонента, от чтения данных до записи выходных данных.
Предыдущие результаты являются приблизительными, поскольку я использовал 'EXPLAIN'. Если нам нужны реальные показатели, мы можем использоватьвместо 'EXPLAIN ANALYZE'.
Мы можем проверить, какая часть нашего оператора SQL нуждается в большей оптимизации, проанализировав результаты.
2. Избегайте циклов кодирования
Использование циклов для вставки в таблицу — плохая идея, потому что мы не позволяем механизму базы данных оптимизировать вставки. Я часто видел в коде циклы для вставки сотен строк вместо использования других, более оптимизированных способов.
Использование цикла for:
Мы могли бы преобразовать приведенный выше фрагмент, используя уникальный оператор INSERT или BATCH с несколькими строками и значениями:
3. Избегайте коррелированных подзапросов SQL
Коррелированный подзапрос — это запрос, использующий значения из родительского запроса. Если оптимизатор механизма SQL не переписывает коррелированный подзапрос с помощью JOIN, этот тип SQL-запроса выполняется один раз для каждой строки, возвращаемой внешним запросом, и снижает общую производительность SQL-запроса.
Как мы видим здесь, проблема заключается в том, что внутренний запрос (SELECT c.name…) выполняется для каждой строки, возвращаемой внешним запросом (SELECT r.id,..), и делает то же самое снова и снова для каждой строки, обрабатываемой внешний запрос.
Мы можем исправить это, переписав оператор следующим образом:
4. Разделение
Разделение таблиц в SQL — это процесс разделения больших таблиц данных на небольшие управляемые таблицы, называемые разделами. С помощью этого метода мы можем повысить производительность запросов и упростить управление данными и запросы к ним.
У нас есть в основном два типа разметки:
Вертикальное разбиение
Вертикальное разбиение — это вертикальное разбиение таблиц по столбцам. Таким образом, один набор столбцов попадает в одно хранилище данных, а другой — в другое.
Горизонтальное разбиение
Горизонтальное разбиение — это разделение таблиц по горизонтали и по строкам. Один набор строк попадает в одно хранилище данных, а другой — в другое хранилище данных.
Разделение таблиц обычно используется с таблицами, содержащими исторические данные. Например, в этих случаях мы могли бы разбить данные по годам, что обеспечило бы быстрый доступ к таблице с данными за текущий год, которые обычно используются чаще всего.
5. Проверьте индексы
Индексы, если их правильно использовать, — это одна из вещей, которая ускорит наши итерации с базой данных.
Рекомендуется создавать индексы для полей, используемых в предложениях WHERE и JOINS, и, если возможно, создавать индексы для столбцов с числовыми значениями, а не с символьными значениями.
6. Удалите неиспользуемые индексы
Создание индексов для всего, не беспокоясь об этом, может привести к нехватке места в табличном пространстве, особенно если таблица, для которой мы создаем индекс, огромна. По этой причине рекомендуется время от времени проводить очистку и проверять, нужны ли все имеющиеся у нас индексы.
7. Выберите подходящий тип данных
Не все типы занимают одинаковое место, и когда мы используем конкретный тип данных, мы также можем ограничить его размер в зависимости от того, что мы храним. Например, VARCHAR(4000) — это не то же самое, что VARCHAR(40). Нам всегда приходится приспосабливаться к тому, что мы будем хранить в наших полях, чтобы не занимать лишнее место в нашей базе данных.
8. Транзакции
Держите транзакции небольшими, так как транзакции блокируют данные таблиц обработки и могут привести к взаимоблокировкам.
Советы по синтаксису
9. Используйте СУЩЕСТВУЕТ-НЕ СУЩЕСТВУЕТ вместо IN-NOT IN
Если мы хотим подтвердить существование значения в определенной таблице, рекомендуется использовать предложение EXIST.
Это связано с тем, что процесс, выполняющий предложение EXISTS, останавливается, как только находит требуемое значение в таблице. Но, с другой стороны, запрос с предложением IN просматривает все даже после нахождения нужного значения.
10. Используйте оператор LIKE только в случае необходимости
Для поиска строки мы можем использовать либо оператор LIKE, либо оператор =.
Основное различие между оператором LIKE и оператором = заключается в том, что мы можем искать неполные строки, используя подстановочные знаки с оператором LIKE. Напротив, с оператором = мы ищем точные совпадения.
Если мы выполняем точный поиск, всегда лучше использовать оператор =, потому что он использует индексированные столбцы и работает быстрее.
11. Выберите только те поля, которые вам нужны
Запрос SELECT * возвращает записи из всех столбцов таблицы. Хотя в некоторых случаях это полезно, во многих других случаях в этом нет необходимости.
Вот некоторые из проблем, связанных с получением всех столбцов:
- Перерасход полосы пропускания.
- Столбцы можно добавлять или удалять, а также изменять имена столбцов в таблице. Поэтому мы можем получить неожиданные результаты для SELECT *
- Порядок столбцов, возвращаемых SELECT *, может быть другим.
12. Используйте соединения вместо подзапросов
Подзапрос — это оператор SELECT, встроенный в предложение другого оператора SQL. Его легче писать, но JOIN обычно лучше оптимизированы для механизмов баз данных.
В некоторых случаях использование подзапросов может быть лучше, чем использование JOIN. Например, при объединении с большими таблицами. В этих случаях предпочтительным способом является выполнение подвыборки из этой большой таблицы (с ограничением количества строк), а затем объединение их.
Последние мысли
SQL (язык структурированных запросов) — это мощный предметно-ориентированный язык, который позволяет нам легко управлять и извлекать информацию из реляционных баз данных.
Проблема в том, что если вы не умеете им правильно пользоваться, производительность вашей базы данных значительно упадет. Вот почему важно учитывать основные рекомендации, которые я написал в этом небольшом посте, и в качестве хорошей практики использовать хорошее руководство по стилю при написании SQL.
Существует множество руководств по стилю для SQL, но важно выбрать одно и использовать его во всех наших разработках.
И наконец, давайте не будем забывать, что первое, что нам нужно сделать, это понять наши данные, их тип и то, как они соотносятся друг с другом, чтобы принимать наилучшие решения при взаимодействии с нашей базой данных.
Я надеюсь, что вы нашли этот пост полезным, если у вас есть какие-либо вопросы, пожалуйста, не стесняйтесь обращаться ко мне. Я постараюсь ответить как можно скорее.
Если вам понравилась эта статья, рассмотрите возможность подписки на Medium через мой профиль. Спасибо!
