Команды SQL для работы с таблицами и базами данных #sql

Типы ключей базы данных #sql

Роудмап для изучения баз данных
#database #sql

Шпаргалка по основным команда SQL по категориям #sql

Сравнение операций в Excel, SQL и Python (Pandas)
#sql #python

Добавил мобильную версию руководства по языку SQL и работе с базами данных на примере SQL Server
https://www.rustore.ru/catalog/app/com.metanit.sql_tutorial
#sql #database

Большая шпаргалка по SQL #sql

Как происходят SQL-инъекции #sql

20 распространенных рекомендаций по оптимизации SQL-запросов:

1. Индексы для шаблонов доступа к запросам (составные, селективные, покрывающие); не ориентируйтесь на количество строк; поддерживайте актуальность статистики.

2. Используйте EXISTS для проверки наличия записей; применяйте COUNT(*) только когда действительно нужен подсчёт.

3. Выбирайте конкретные столбцы явно; избегайте SELECT * для сокращения операций ввода-вывода и использования покрывающих индексов.

4. Предпочитайте SARG-предикаты (предикаты, допускающие использование индекса); переписывайте медленные коррелированные подзапросы с помощью JOIN/EXISTS.

5. Избегайте DISTINCT как временного решения; исправьте соединения/ключи; используйте его только когда действительно требуется удаление дубликатов.

6. Фильтрацию выполняйте в WHERE; резервируйте HAVING только для фильтрации после агрегации.

7. Используйте явные JOIN ... ON; избегайте неявных соединений в WHERE.

8. Применяйте пагинацию по ключам; избегайте OFFSET/LIMIT на больших наборах данных; для выборки используйте TABLESAMPLE (если доступно).

9. Используйте UNION ALL вместо UNION, когда дубликаты допустимы.

10. Заменяйте широкие OR-предикаты на UNION ALL только когда каждая ветвь может использовать разные индексы.

11. Планируйте выполнение тяжёлых запросов в периоды низкой нагрузки; применяйте ограничения ресурсов/очереди, если они доступны.

12. Избегайте OR в предикатах соединения; используйте вычисляемые столбцы или UNION ALL, когда это позволяет использовать поиск по индексу.

13. Используйте GROUP BY, когда нужны сгруппированные строки; применяйте оконные функции, когда требуется детализация строк с агрегатами.

14. Используйте производные/временные таблицы, когда они сокращают объём работы или добавляют статистику; остерегайтесь блокирующих спусков.

15. При массовой загрузке: отключайте/удаляйте некластерные индексы, выполняйте пакетную вставку, затем восстанавливайте; сохраняйте PK/кластерные индексы, когда это полезно.

16. Используйте материализованные представления для медленно меняющихся, ресурсоёмких агрегатов; планируйте обновление/недействительность.

17. Избегайте не-SARG-сравнений (например, <>) на столбцах с низкой селективностью; переписывайте в диапазоны, когда это возможно.

18. Минимизируйте коррелированные подзапросы над большими наборами; предпочитайте множественные соединения/EXISTS.

19. Выбирайте INNER вместо LEFT/RIGHT по семантике; INNER часто работает лучше, когда применимо.

20. Кешируйте повторяющиеся наборы результатов: временные таблицы (на сессию), кеш результатов или материализованные представления, с правилами актуальности.

#sql

Фантомные чтения
(подробное описание в следующем посте)

#sql #mysql #postgresql #database

Фантомные чтения
(описание к предыдущему посту)

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

Рассмотрим пример с базой данных, в которой работают два клиента. Клиент A начинает транзакцию и выполняет SELECT всех заказов с общей ценой > 100 долларов. Пока он продолжает выполнять другие запросы, клиент B вставляет в таблицу новый заказ и фиксирует изменения (COMMIT). Наконец, клиент A снова запрашивает заказы с общей суммой > 100 долларов, но на этот раз видит новую строку, добавленную клиентом B!

Допускается ли такое поведение, зависит от настроенного уровня изоляции.

По умолчанию PostgreSQL использует уровень READ COMMITTED, который допускает фантомные чтения. Отдельные запросы получают согласованное представление базы данных, но между запросами в рамках одной транзакции могут наблюдаться изменения, зафиксированные другими транзакциями.

Как и PostgreSQL, MySQL имеет четыре настраиваемых пользователем уровня изоляции. Использование строгого уровня, такого как SERIALIZABLE, предотвращает фантомные чтения, в то время как более свободные уровни, такие как READ COMMITTED, допускают их (по умолчанию в MySQL используется REPEATABLE READ).

Почему бы не установить везде уровень SERIALIZABLE? Всё дело в производительности! Более строгие уровни требуют большего количества блокировок и снижают производительность, в то время как более свободные уровни обеспечивают более высокую производительность за счёт возможных несоответствий.

#sql #mysql #postgresql #database

6 основных типов ключей в базах данных:

1. Primary key (первичный ключ) — уникальный идентификатор для каждой записи в таблице; не может быть пустым и должен быть уникальным во всей таблице.

2. Foreign key (внешний ключ) — поле в таблице, которое соответствует первичному ключу другой таблицы; создаёт связь между таблицами.

3. Composite key (составной ключ) — комбинация двух или более столбцов, используемых как первичный ключ; применяется, если один столбец не может однозначно идентифицировать запись.

4. Alternate key (альтернативный ключ) — уникальный идентификатор записи, который не является первичным ключом; используется как вторичный способ поиска данных.

5. Candidate key (потенциальный ключ) — уникальный идентификатор записи, который потенциально может быть использован как первичный ключ.

6. Surrogate key (суррогатный ключ) — уникальный идентификатор, присвоенный записи в таблице, обычно создаваемый самой базой данных, а не пользователем.

#sql #database

Внешние ключи vs ограничения. Многие путают эти понятия

Внешний ключ (foreign key): столбец, который устанавливает связь между двумя таблицами. Обычно реализуется как столбец в одной таблице (post), хранящий значения первичного ключа из другой таблицы (user) для обеспечения связи между ними.

Ограничение внешнего ключа (foreign key constraint): механизм, заставляющий СУБД обеспечивать целостность внешних ключей. Он гарантирует, что значения в столбце внешнего ключа (post→user_id) ссылаются на существующую запись в другой таблице (user→id)

Ограничения — полезное удобство (они поддерживают компонент «C» в ACID), но имеют свою цену: операции вставки, обновления и удаления в таблицах с ограничениями требуют дополнительных вычислительных ресурсов и операций ввода‑вывода для их соблюдения

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

Многие используют СУБД Postgres, но не многие знают, что Postgres предоставляет специальную утилиту для проверки производительности - explain.
Для проверки производительности работы с базой данных регулярно запускайте команды explain и explain analyze и научитесь интерпретировать их выходные данные. Это может помочь найти узкие места при запросах к БД

#sql #postgresql #database

Что происходит, когда вы добавляете строку в Postgres?
(продолжение предыдущего поста)

Postgres должен гарантировать сохранность данных, обеспечивая при этом высокую производительность записи и возможность восстановления после сбоев. Ключ к решению этой задачи — журнал упреждающей записи (Write‑Ahead Log, WAL).

(1) Postgres получает запрос и определяет, на какую страницу данных его разместить. Эта страница может уже находиться в памяти (в буферном пуле), либо её придётся загрузить с диска, либо даже создать новую.

(2) Новая запись помещается только в эту страницу в памяти. Страница помечается как «грязная» (*dirty*), что означает: её нужно будет в будущем записать на диск — но не немедленно.

(3) Новая запись добавляется в буфер памяти для WAL. Она содержит всю информацию, необходимую для восстановления операции вставки.

(4) WAL записывается на диск (с помощью fsync или аналогичного механизма), чтобы гарантировать сохранение данных в надёжном хранилище. После успешного выполнения этого шага Postgres возвращает клиенту подтверждение об успешном выполнении.

Когда клиент получает подтверждение об успехе, данные гарантированно записаны в последовательный WAL (что обеспечивает высокую производительность записи), но не обязательно — в файл данных таблицы (где шаблоны операций ввода‑вывода менее предсказуемы). Последнее происходит позже — посредством контрольных точек, фоновых заданий или принудительной записи из‑за вытеснения страниц памяти. Если сбой сервера произойдёт до того, как данные будут записаны на диск, журнал будет воспроизведён для восстановления подтверждённых данных.

WAL — это основа всего процесса! Он обеспечивает высокопроизводительный ввод‑вывод и возможность восстановления после сбоев.

#sql #database #postgresql

Microsoft выпустил новое поколение свой СУБД - SQL Server 2025.
Основные изменения:

- Интеграция ИИ непосредственно в ядро ​​SQL Server, что обеспечивает расширенный семантический поиск для более глубокого анализа и понимания естественного языка в корпоративных данных.
Управление моделями встроено в T-SQL, поддерживая бесшовную интеграцию с Microsoft Foundry, Azure OpenAI Service, OpenAI, Ollama и другими системами, что позволяет безопасно разворачивать их где угодно, как локально, так и в облаке. Разработчики могут легко переключаться между моделями, не изменяя код, а такие важные элементы ИИ, как векторное встраивание, фрагментация текста и индексация DiskANN, поддерживаются нативно.

- Большие возможности для разработчиков SQL за последнее десятилетие, оптимизируя разработку и повышая производительность. Встроенная поддержка JSON, REST API, регулярных выражений и нечеткого соответствия строк обеспечивает более эффективное обогащение и проверку данных. Потоковая передача событий изменений позволяет создавать приложения, управляемые событиями в режиме реального времени.

- Инструменты SQL. SQL Server 2025 предлагает важные обновления для всей платформы данных. SQL Server Management Studio (SSMS 22) уже доступна для всех пользователей и предлагает официальную поддержку SQL Server 2025, улучшенную поддержку ИИ и поддержку ARM64. SSMS 22 также включает поддержку ИИ при установке рабочей нагрузки GitHub Copilot, которая использует ту же подписку GitHub, что и GitHub Copilot в Visual Studio или VS Code.

- SQL Server 2025 продолжает развивать безопасность СУБД. Оптимизированная блокировка снижает потребление памяти для блокировки, минимизирует блокировку и повышает параллелизм.
Управление ресурсами пространства Tempdb повышает надежность сервера. Дополнительная оптимизация плана параметров делает производительность запросов более стабильной. SQL Server 2025 продолжает укреплять свои критически важные возможности, улучшая группы доступности Always On (AG) и возможности аварийного восстановления. Основное внимание уделяется более быстрому отказоустойчивому режиму, улучшенной диагностике и гибридной гибкости.

- SQL Server 2025 на Linux представляет несколько важных улучшений. Безопасность усилена поддержкой TLS 1.3, настраиваемыми политиками паролей и подписанными образами контейнеров. Поддержка платформ расширена и теперь включает RHEL 10 и Ubuntu 24.04, а производительность повышена благодаря поддержке tmpfs для tempdb и контейнерных развертываний. Расширенная аналитика доступна благодаря поддержке универсальных источников данных ODBC через PolyBase. Процесс разработки оптимизирован благодаря интеграции с Visual Studio Code для локального развертывания контейнеров с использованием расширения mssql и проверенных шаблонов развертывания в сотрудничестве с Red Hat, что обеспечивает поддержку современных рабочих нагрузок и сценариев ИИ в гибридных средах.

По словам Microsoft, предварительные тесты показывают, что SQL Server 2025, работающий на процессорах AMD EPYC с оборудованием HPE, обеспечивает ощутимый прирост производительности и ценности. Рабочая нагрузка объёмом 10 ТБ устанавливает новый рекорд производительности для SQL Server. По соотношению цена/производительность SQL Server 2025 демонстрирует улучшение на 4% в категории 3 ТБ по сравнению с предыдущими результатами.

https://techcommunity.microsoft.com/blog/sqlserver/sql-server-2025-is-now-generally-available/4470570

#sql #sqlserver #database

Что такое ACID
(продолжение предыдущего поста)

ACID - набор свойств, которые обеспечивают надежность и целостность данных при обработке транзакций в системах управления базами данных (СУБД). ACID расшифровывается как Atomicity (атомарность), Consistency (согласованность), Isolation (изолированность), Durability (долговечность)

1. Atomicity (Атомарность) — «Всё или ничего» (верхний левый угол):
* Гарантирует, что транзакция либо выполняется полностью, либо не выполняется вовсе.
* Если транзакция не может быть завершена, СУБД откатывает все изменения (возвращает базу данных к предыдущему состоянию).
* На схеме показан пример транзакции с операциями write 1, write 2, write 3, write 4, которые либо все фиксируются (commit all), либо отменяются (or nothing).

2. Consistency (Согласованность) — «Сохранение инвариантов базы данных» (верхний правый угол):
* Обеспечивает, что каждая транзакция переводит базу данных из одного согласованного состояния в другое.
* Транзакции не нарушают правила целостности данных.
* На схеме показано, как база данных переходит из consistent state A (согласованное состояние A) в consistent state B (согласованное состояние B) через выполнение транзакций.

3. Isolation (Изолированность) — «Параллельные транзакции изолированы друг от друга» (нижний левый угол):
* Гарантирует, что каждая транзакция выполняется независимо от других, без видимости промежуточных изменений.
* Это предотвращает конфликты между транзакциями.
* На схеме изображены две транзакции (Transaction A и Transaction B), которые выполняются изолированно, не влияя друг на друга.

4. Durability (Долговечность) — «Данные сохраняются после фиксации транзакции даже при сбое системы» (нижний правый угол):
* Гарантирует, что изменения, внесённые транзакцией, сохраняются и становятся постоянными.
* Даже если система выйдет из строя после фиксации транзакции, данные останутся доступными.
* На схеме показан процесс:
1. Фиксация транзакции (commit).
2. Репликация данных на копии базы (replica a, replica b), что обеспечивает сохранность информации.

#database #sql

Порядок выполнения команд в запросе SQL #sql

Визуальное руководство по командам SQL #sql