SQL-Ex blog

Пересказ статьи Cláudio Silva. What happens when we drop a column on a SQL Server table? Where's my space

Короткий ответ: столбец отмечается как "удаленный" и перестанет быть видимым/используемым. Но, что наиболее важно - размер записи/таблицы останется неизменным.

Операция с метаданными

Удаление столбца является логической операцией с метаданными, а не физической. Это означает, что данные не удаляются/перезаписываются при этом действии. Если говорить об удалении данных (записей), то как упоминает здесь Пол Рэндал:

«стоимость этого будет отложена для вставляющих, а не для удаляющих».
Continue reading "Что происходит при удалении столбца в таблице SQL Server? Где мое пространство?"

Пересказ статьи Hagen Hübel. Understanding the Performance Difference in Adding Columns in PostgreSQL

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

Вопрос

Представьте, что у вас есть большая таблица, содержащая десятки тысяч записей. Вы хотите добавить новый допускающий NULL-значения столбец без значения по умолчанию, а затем выполнить оператор UPDATE, чтобы установить для этого нового столбца заданное значение. Этот процесс занимает значительное время. Однако, если вы вместо этого добавляете новый столбец со значением по умолчанию, это не занимает так много времени. Почему имеет место такая разница в производительности?
Continue reading "Понимание разницы в производительности при добавлении столбцов в PostgreSQL"

Пересказ статьи Hugo Kornelis. Plansplaining part 30 – Static cursors

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

Тестовый запрос

В этой серии я буду придерживаться использования одного и того же тестового запроса, который выводит данные по продажам и товарам, которые были проданы в количестве более 10 единиц в пределах заданного диапазона заказов.
Continue reading "Статические курсоры"

Пересказ статьи Lukas Vileikis. Optimizing MySQL: Deleting Data

Удаление данных - основы

Спросите любого администратора баз данных, как удалить данные, и вы услышите об одном или двух следующих методов:

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


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

Для начала рассмотрим обычный запрос DELETE, который в MySQL выглядит следующим образом:

DELETE FROM имя_таблицы WHERE [логическое_выражение]

По частям:
Continue reading "Оптимизация MySQL: удаление данных"

Пересказ статьи Eitan Blumin. SQL Server Index Mastery: Choosing the Right Column Order

Введение

Оптимизация производительности SQL Server - непростая тема, и проектирование индексов играет в ней жизненно важную роль, способствуя эффективности выполнения запросов к базе данных.

Одним из ключевых аспектов, которые часто влияют на производительность, является порядок столбцов в индексе.

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

Пересказ статьи Simon Liew. SQL Server Filtered Index Essentials Guide

Фильтрованные индексы могут значительно повысить производительность запроса, но простые ошибки могут помешать их использованию. Мы поможем вам понять, как использовать фильтрованные индексы SQL Server и выявить запросы, которые могут получить от этого преимущество, которого они не имели.

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

Ключом для правильного использования оптимизатором SQL Server фильтрованных индексов является:

1. Убедиться, то предикат (предикаты) запроса эквивалентны выражению фильтрованного индекса. Иногда предикат не должен точно совпадать с выражением, и оптимизатор SQL Server может определить это. Однако чем проще, тем лучше.


2. Предикат ((предикаты) запроса на столбце (столбцах) фильтрованного индекса не параметризуются или не используют присвоение переменной.

Continue reading "Фильтрованные индекс в SQL Server: основы"

Пересказ статьи Yash Marathe. Generalized Inverted Index in PostgreSQL

Исследование достоинств и недостатков GIN-индексов в PostgreSQL

Содержание

1. Мотивация


2. Введение


3. GIN-индексирование изнутри


4. Практический пример


5. Уроки индекса GIN Trigram GitLab


6. Заключение


7. Ссылки

Continue reading "Обобщенный инвертированный индекс в PostgreSQL"

Пересказ статьи Erik Darling. The How To Write SQL Server Queries Correctly Cheat Sheet Conditional Join and Where Clauses

Так или иначе

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

Например, IN(1, 2, 3) может в результате стать = 1 OR = 2 OR = 3 без вашего участия. Оптимизаторы так забавляются. Забавные маленькие кролики.

Проблема обычно возникает не тогда, когда вы пишете в запросе IN или OR для одного столбца со списком литеральных значений, а когда вы:

• Используете OR по множеству столбцов в предложении WHERE.


• Используете OR в предложении JOIN любого сорта.


• Используете OR для обработки параметров или переменных NULL.

Добавьте немного сложности, объединив две таблицы и попросив что-то вроде: Continue reading "Шпаргалка по правильному написанию запросов к SQL Server: условное соединение и предложение WHERE"

Пересказ статьи Lorenzo Uriel. The SQL Week: Data Modeling

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

Одним из наиболее любимых мной процессов является моделирование данных.

Итак, из чего состоит процесс моделирования?

Можно упомянуть:

1. Анализ требований
2. Концептуальная модель (ERD - ERM)
3. Логическая модель (таблицы и связи)
4. Физическая модель (создание таблиц в базе данных)

Это будут пункты этой части, в результате которой я хочу построить с вами реальный пример с нуля.
Continue reading "Неделя SQL: моделирование данных"

Пересказ статьи Andy Brownsword. Solving Parameter Sniffing with Multiple Execution Plans

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

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

Давайте начнем с процедуры и индекса в базе данных StackOverflow:

CREATE OR ALTER PROCEDURE dbo.GetPopularUsers (

    @MinimumViews INT

) AS

BEGIN

    SELECT Id, DisplayName

    FROM dbo.Users

    WHERE [Views] >= @MinimumViews;

END

GO

CREATE INDEX [Views]

ON dbo.Users ([Views]);

Continue reading "Решение проблемы прослушивания параметра при помощи нескольких планов выполнения"

Пересказ статьи Erik Darling. The How To Write SQL Server Queries Correctly Cheat Sheet: Cross Apply And Outer Apply

Ситуации

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

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

Короткий ответ заключается в том, что я начинаю мысленно представлять себе, как синтаксис apply может быть полезен, когда:

• Имеется небольшая внешняя таблица (FROM) и большая внутренняя таблица (APPLY).


• Мне требуется выполнить значительный объем работы на внутренней стороне соединения.


• Целью запроса является получение top N на группу или что-то подобное.


• Я пытаюсь получить параллельные вложенные циклы вместо выбора некоторого альтернативного плана.


• Чтобы заменить скалярную UDF в списке select на встроенную (inline) UDF.


• Чтобы использовать конструкцию VALUES необычным способом.

Большинство этого ситуативно и требует немного опыта и знакомства, чтобы быстро это заметить.
Continue reading "Шпаргалка по правильному написанию запросов к SQL Server: Cross Apply и Outer Apply"

Пересказ статьи Erik Darling. The How To Write SQL Server Queries Correctly Cheat Sheet: EXISTS and NOT EXISTS

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

Ужасная вещь, относящаяся к SQL, заключается в том, что в нем столько же правил, которые применяются избирательно, как и в самом английском языке.

У меня, конечно, есть свои жалобы и недовольство относительно выбора некоторых из них, и вы тоже в этом убедитесь, когда глубже вникните в язык. Типичным примером этого типа является мое желание использовать в SQL GET вместо SELECT для извлечения данных.

Очень немного людей идут в магазин выбирать (select) молоко, яйца, масло, соль, перец и виски. Большинство из нас просто идет взять (get) их. Но хватит о завтраке.

Давайте поговорим о двух наиболее часто упускаемых из виду и недооцененных возможностях в языке SQL: EXISTS и NOT EXISTS. Возможно, они бы больше привлекали внимание, если бы назывались THERE and NOT THERE, но тогда была бы опасность начального затруднения с предложениями WHERE и THERE.

Часто очерняемые теми, кто читал статью в 1998 о "подзапросах в MySQL, которые медленнее, чем соединения", эти предикаты являются двумя наиболее полезными вещами, которые вы можете освоить и использовать регулярно.

Хотя они несколько напоминают подзапросы, столбцы, которые вы выбираете в подзапросе EXISTS или NOT EXISTS не могут использоваться во внешнем запросе. Вы можете поместить что угодно в список SELECT, от * или 1 до COUNT или 1/0, или всего содержимого Библии короля Якова, и это никогда не приведет ни к каким даже самым незначительным изменениям в мире. Аналогично, добавление DISTINCT, TOP или любого другого ограничителя числа строк не сделает ничего такого, чтобы изменить план запроса или его производительность.

Преодолейте себя.

И EXISTS, и NOT EXISTS уже установили число строк в 1, поскольку все, что нужно сделать каждому из них, это определить, существует хотя бы одна строка или нет, и наоборот.
Continue reading "Шпаргалка по правильному написанию запросов к SQL server: EXISTS и NOT EXISTS"

Пересказ статьи Semab Tariq. PostgreSQL Internals Part 2: Understanding Page Structure

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

Во второй части мы изучим внутреннюю структуру страницы в PostgreSQL. Первую часть вы можете найти здесь.

Обзор макета страницы в PostgreSQL

При создании таблицы генерируется соответствующий файл данных. Внутри этого файла данные размещаются на страницах фиксированной длины, обычно 8-килобайтных, что принимается по умолчанию. Каждой странице присваивается последовательный номер, начиная с 0, который называется номером блока. PostgreSQL добавляет новую пустую страницу к концу файла, когда он заполняется. Тем самым увеличивается размер файла данных.
Continue reading "PostgreSQL изнутри. Часть 2: понимание структуры страницы"

Пересказ статьи Dmitry Romanoff. PostgreSQL. How do you find potentially ineffective indexes?

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

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

Следующий запрос поможет вам найти потенциально неэффективные индексы.
Continue reading "PostgreSQL. Как обнаружить потенциально неэффективные индексы?"

Пересказ статьи Dileep kumar. PostgreSQL -Row level & Table level Locks

Пессимистичные блокировки в PostgreSQL

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

Что такое блокировка

В мире баз данных блокировка является логическим объектом, который упорядочивает доступ к данным, когда два или более пользователей пытаются получить доступ к одним и тем же данным. Являясь развитой СУБД, PostgreSQL предоставляет несколько механизмов блокирования для обработки конкурентных запросов, что в конечном итоге обеспечивает большую согласованность данных.

В последующих разделах рассматриваются блокировки уровня строки, таблицы, сессии и транзакции.

Continue reading "PostgreSQL - блокировки уровня строки и уровня таблицы"