SQL-Ex blog

Пересказ статьи Pradip Bhusnar. SQL Pagination Techniques: Enhancing Query Performance and Managing Memory Efficiently

Эффективная обработка разбивки на страницы (пагинация) в SQL, особенно в высоконагруженных системах, требует тщательного учета как использования памяти, так и производительности. Здесь представлен детальный обзор проектирования и эффективного применения запросов постраничной разбивки.

Основные методы эффективной разбивки на страницы в SQL

1. Limit и Offset:

• Традиционная разбивка на страницы с использованием LIMIT и OFFSET проста, но может оказаться неэффективной при больших смещениях.


• Например:
  
  

  SELECT * FROM data_table
  
  ORDER BY timestamp DESC
  
  LIMIT 10 OFFSET 1000;



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

Continue reading "Методы разбиения на страницы в SQL: повышение производительности запросов и эффективное управление памятью"

Пересказ статьи Lukas Vileikis. MySQL Index Overviews FULLTEXT B-Tree Indexes

Что такое полнотекстовое индексирование?

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

Полнотекстовое индексирование работает аналогичным образом во многих системах управления базами данных и существенно не менялось годами - загляните в блог Роберта Шелдона, посвященного полнотекстовому поиску более двух десятилетий назад, и вы конечно найдете что-нибудь, что изменилось в вашей установке SQL Server, но вы также обнаружите множество вещей, которые остались неизменными; и то же самое можно сказать о других системах управления базами данных.
Continue reading "Обзор индексов в MySQL: B-Tree индексы FULLTEXT"

Пересказ статьи Simon Liew. Optimize SQL LIKE Wildcard Searches

Полный поиск по шаблону (например, LIKE '%поисковая_фраза%') в Microsoft SQL Server может быть медленным и неэффективным, поскольку гарантируется сканирование всех строк в таблице. Имеются ли какие-нибудь варианты оптимизации запросов с оператором SQL LIKE?

Оптимизация независимого от регистра полного поиска по шаблону с начальным и конечным подстановочным знаком является проблемой в базах данных SQL - эти шаблоны LIKE не получают выгоды от индексирования. Здесь исследуются потенциальные варианты оптимизации такого поиска и проверяются распространенные заблуждения.
Continue reading "Оптимизация поиска при использовании SQL LIKE с подстановочными знаками"

Пересказ статьи Brent Ozar. How Many Indexes Is Too Many?

Давайте начнем с базы данных Stack Overflow (будет работать версия любого размера), удалим все индексы на таблице Users и выполним DELETE:

SET STATISTICS IO ON;

GO

BEGIN TRAN

DELETE dbo.Users WHERE DisplayName = N'Brent Ozar';

Я использую SET STATISTICS IO ON, о чем мы говорили в статье "Как думать подобно серверу SQL Server" для иллюстрации количества прочитанных данных, и я делаю это в транзакции, которую я могу периодически откатывать, каждый раз демонстрируя полученные эффекты. Вот действительный план выполнения:


Continue reading "Слишком много индексов — это сколько?"

Пересказ статьи Jeyaram Ayyalusamy. 09 - PostgreSQL 17 Performance Tuning: How Updates and VACUUM Affect Storage

Модель хранения в PostgreSQL отличается от многих других баз данных. Вместо переписывания строки по месту, PostgreSQL создает новые версии строк при обновлении данных. Эта схема хороша для безопасности транзакций и параллелизма, но при этом влияет на размер таблицы и VACUUM приобретает важное значение для производительности.

Давайте пошагово пройдем тестовый пример, чтобы увидеть, как это работает на практике.

Отключение autovacuum (только для тестирования)

PostgreSQL обычно выполняет фоновый процесс, который называется autovacuum, для очистки мертвых кортежей и предотвращения раздувания таблиц.

• Для реальных приложений выключение autovacuum не рекомендуется, поскольку это критически важно для работоспособности базы данных.


• Но в целях тестирования он может быть выключен для конкретной таблицы для гарантии, что ничего не будет происходить автоматически в фоновом режиме.

Это позволяет нам в точности наблюдать, как PostgreSQL управляет хранилищем. Continue reading "Настройка производительности в PostgreSQL 17: как обновления и VACUUM влияют на хранилище"

Пересказ статьи Dmitry Romanoff. Understanding MySQL Process List Queries: A Guide to Monitoring and Optimizing Performance

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

1. Просмотр всех процессов

Чтобы получить исчерпывающий обзор всех текущих процессов на сервере MySQL, вы можете использовать следующий запрос:

SELECT * FROM information_schema.processlist;

Continue reading "Понимание запросов списка процессов MySQL: руководство по мониторингу и оптимизации производительности"

Пересказ статьи Jeyaram Ayyalusamy. 22 - PostgreSQL 17 Performance Tuning: BRIN (Block Range INdex)

При работе с очень большими таблицами в PostgreSQL традиционные индексы типа B-Tree или GiST могут чрезвычайно вырасти в размерах, что занимает значительное время на их обслуживание. Для временных рядов или последовательно упорядоченных данных PostgreSQL предлагает более разумный и облегченный вариант: BRIN (Block Range Index).

Индексы BRIN группируют данные в диапазоны блоков вместо хранения отдельных записей строк, что делает их компактными, быстрыми в построении и эффективными для запросов на диапазон или упорядоченные данные.

Давайте пошагово рассмотрим пример.
Continue reading "Настройка производительности PostgreSQL 17: индекс BRIN (Block Range INdex)"

Пересказ статьи Jeyaram Ayyalusamy. 32 - PostgreSQL 17 Performance Tuning: Understanding Optimizer Cost Parameters

PostgreSQL известна как одна из наиболее продвинутых реляционных баз данных с открытыми кодами, и одна из основных причин ее силы - оптимизатор запросов на основе стоимости.

Когда вы запускаете запрос, оптимизатор не выполняет его непосредственно. Он генерирует множество возможных планов выполнения и оценивает их стоимость. Выбирается план с самой низкой оценкой стоимости. Стоимость не измеряется в миллисекундах или циклах ЦП - она представляет собой абстрактные единицы, которые PostgreSQL использует для сравнения.

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

В этой статье мы:

1. Создадим таблицу с 10 миллионами строк для имитации реальной рабочей нагрузки.


2. Создадим индексы, чтобы дать возможность PostgreSQL построить несколько планов выполнения.


3. Подробно разберем модель стоимости в PostgreSQL.


4. Покажем, как настройка параметров стоимости может изменить решение при выборе плана.

Continue reading "Настройка производительности в PostgreSQL 17: Понимание параметров стоимости оптимизатора"

Пересказ статьи Tomasz Gintowt. PostgreSQL Prepared Queries (Statements) For Beginners

Когда мы пишем запросы SQL, то часто выполняем один и тот же запрос снова и снова лишь меняя значения.

PostgreSQL обладает функциональностью, которая помогает решить эту проблему, - подготовленные запросы (также называемые подготовленными операторами).

В этой статье мы выясним:

• Что такое подготовленные запросы.


• Чем они полезны.


• Как их использовать в PostgreSQL.


• Реальные примеры, которые вы сами сможете опробовать.

Никаких непонятных слов. Никакой сложной теории. Простые ясные примеры.
Continue reading "Подготовленные запросы (операторы) в PostgreSQL для начинающих"

Пересказ статьи Jeyaram Ayyalusamy. 29 - PostgreSQL 17 Performance Tuning: Understanding Estimates vs. Actuals in Query Plans

Настройка производительности в PostgreSQL часто сводится к единственному навыку: умению читать планы выполнения. Команда PostgreSQL EXPLAIN ANALYZE - главный инструмент для этого. Она показывает не только то, как выполняется запрос, но и то, чего ожидал оптимизатор PostgreSQL, и что произошло на самом деле.

При просмотре плана выполнения вы всегда должны задать себе два больших вопроса:

1. Оправданы ли временные параметры, указанные в выводе команды EXPLAIN ANALYZE, для данного запроса?


2. В каком месте происходит внезапный скачок времени выполнения?

Места этих скачков в плане выполнения часто обнаруживают точный узел или операцию, которая замедляет ваш запрос.
Continue reading "Настройка производительности в PostgreSQL 17: понимание преполагаемого и действительного планов выполнения"

Пересказ статьи Robins Tharakan. Settling COUNT(*) vs COUNT(1) debate in Postgres 19

Недавнее изменение в основной ветке PostgreSQL принесло лучшее качество жизни очень общего паттерна SQL в плане оптимизации - улучшение производительности до 64% для SELECT COUNT(h), где h - столбец NOT NULL.

Если вы когда-либо задавались вопросом, что использовать - COUNT(*) или COUNT(1), или вы послушно придерживались использования COUNT(id) на не-NULL столбце, это изменение для вас.

Замечание: Эта функциональность в настоящее время реализована в основной ветке PostgreSQL (зафиксировано в ноябре 2025). Как и любая фиксация на основной ветке, она может подвергаться изменениям или даже отмене до финального релиза, хотя подобное происходит редко для зафиксированных функций. Если все будет нормально, это изменение станет частью релиза основной версии PostgreSQL 19.

Continue reading "Разрешение спора между COUNT(*) и COUNT(1) в Postgres 19"

Пересказ статьи Darren Tan. SQL Best Practices: Hard-Learned Lessons from my years as a Software Engineer

База данных похожа на шутку: если ее приходится объяснять, значит, она, скорее всего, плохо спроектирована...

Представьте: 3 часа утра в субботу, а я сижу за столом, три часа на то, что должно быть простой обработкой пакета производственных данных. Задача казалась простой - обработать набор данных 1200 заказчиков. Что я не мог предвидеть, так это то, что на каждый вызов API должен срабатывать триггер с тысячами операций, происходящих в базе данных, превращая простую работу в ночной кошмар.

Эта бессонная ночь субботы дала мне больше в плане понимания оптимизации SQL, чем все курсы информатики, которые я когда-либо проходил. Хотя бизнес-команда в конце концов получила свои данные (хотя и с некоторым ожиданием), я получил нечто более ценное: глубокое уважение как к силе, так и подводным камням запросов SQL.

Являетесь ли вы начинающим разработчиком, только приступающим к работе, или опытным архитектором, я надеюсь, что, делясь усвоенными мной уроками, я помогу вам избежать некоторого негативного опыта, который я получил той ночью. Continue reading "Лучшие практики SQL: уроки, усвоенные мной за годы работы инженером-программистом"

Автор: Joe Celko, The VALUES clause or building tables out of nothing

Предложение VALUES, вероятно, одна из самых неправильно используемых возможностей в SQL. Если вы посмотрите на онлайн-форумы по SQL, вы увидите, что люди используют его как второе предложение в операторе вставки, но используют его для построения только одной строки за раз, например так:

BEGIN

INSERT INTO Zodiac (astro_sign, astro_start_date, astro_end_date)

 VALUES ('Aries', '2025-03-21', '2025-04-19');

INSERT INTO Zodiac (astro_sign, astro_start_date, astro_end_date)

 VALUES ('Taurus', '2025-04-20', '2025-05-20');

… 

INSERT INTO Zodiac (astro_sign, astro_start_date, astro_end_date)

 VALUES ('Pisces', '2023-05-19', '2026-03-20');

END;

Каждый оператор вставки заканчивается точкой с запятой, поэтому они будут выполняться отдельно и в представленном порядке. Оптимизатор не осмеливается их объединять, потому что может быть прямая ссылка на предыдущие вставки.

Я думаю, люди пишут такой код, потому что именно так вы бы читали перфокарты. Каждая карта поступает в устройство чтения карт, буферизуется и записывается в порядке поступления на магнитную ленту или дисковый файл. Добро пожаловать в 1960-е! Перестаньте подражать старым языкам программирования, таким как FORTRAN или BASIC, в которых были операторы WRITE, помещающие по одной записи за раз в файл. Начните думать о работе с целыми множествами.

Continue reading "Предложение VALUES или создание таблиц из ничего"

Пересказ статьи Matheus dos Santos. Mastering PostgreSQL Performance: A Step-by-Step Guide to pg_hint_plan

Планировщик запросов PostgreSQL - это сложный инженерный механизм, обычно принимающий блестящие решения относительно того, как выполнять ваши запросы. Однако в сложных сценариях или при необычных распределениях данных вы можете знать лучший способ. Именно в таких ситуациях на помощь приходит pg_hint_plan - мощное расширение, которое позволяет вам руководить, или "советовать", планировщиком для выбора специфичного пути выполнения.

Это руководство проведет вас через весь процесс, начиная с установки pg_hint_plan из источника до использования его, чтобы принудительно выполнить сканирование индекса на большом наборе данных, демонстрируя возможности непосредственного управления производительностью вашего запроса.
Continue reading "Повышение производительности PostgreSQL: пошаговое руководство по использованию pg_hint_plan"

Пересказ статьи Udaya Veeramreddygari. Oracle Performance Tuning: Practical Techniques Every DBA Should Master

Как специалисты по базам данных, мы все сталкивались с этим ужасным моментом, когда пользователи начинают жаловаться на медленные запросы, и внезапно все смотрят на вас с выражением «исправьте это сейчас же». Настройка производительности Oracle может показаться невероятно сложной, особенно в условиях стресса, но хорошая новость состоит в том, что большинство проблем с производительностью возникают по нескольким распространённым причинам. Позвольте мне рассказать вам о нескольких проверенных методах, которые спасали мне жизнь больше раз, чем я могу сосчитать.

Начнем с самого простого: статистика и планы выполнения

Прежде чем перейти с сложным стратегиям настройки, всегда проверяйте актуальность вашей статистики. Оптимизатор Oracle на основе стоимости всецело опирается на точность статистики для принятия умных решений относительно путей выполнения запросов. Мне приходилось видеть запросы, которые выполнялись в 10 раз медленнее только потому, что кто-то забыл обновить статистику после загрузки большого объема данных. Continue reading "Настройка производительности в Oracle: практические методы, которыми должен владеть каждый DBA"