SQL Flashcards
SQL Оптимизации запросов
Методы оптимизации SQL-запросов:
1) Индексирование (Создание индексов на часто используемых столбцах позволяет базе данных быстро находить нужные строки без необходимости сканирования всей таблицы)
2) Разбиение больших таблиц на более мелкие, более управляемые сегменты помогает уменьшить объём данных, которые необходимо сканировать
3) Нормализация таблиц (уменьшает объём избыточных данных, делая запросы более быстрыми и эффективными)
4) Оптимизация алгоритмов запросов
5) Использование хранимых процедур (они компилируются один раз, можно использовать для инкапсуляции часто используемых запросов)
6) Кэширование результатов запроса для повторного использования (во временной таблице или табличном выражении)
7) Изучение плана выполнения запроса (Explain - показывает, как движок SQL выполняет запрос, и раскрывает проблемные фрагменты)
Основные практики оптимизации:
1) Использование индексов для больших таблиц, для нечасто обновляющихся таблиц, для часто исп. столбцов
2) Использовать WHERE вместо HAVING:
- HAVING фильтрует данные при создании групп (GROUP BY)
- WHERE позволяет отфильтровать данные до создания групп (соответственно для группировки будет меньше работы)
3) Извлекать только нужные столбцы (Не использовать SELECT *)
4) Избегайте запросов внутри цикла (нужные запросы можно сделать вне цикла и сохранить результаты в кэш)
5) Используйте Exist() вместо Count() (Exist() прекращает выполнение после первого выполнения условия)
6) Применение денормализации таблиц (таблицы будут хранить избыточную информацию, но запросы могут выполняться быстрее (т.к. снижается необходимость использования медленных операций JOIN))
SQL Подзапросы
Подзапросы - запросы, встроенные в другие запросы.
Коррелирующие подзапросы - подзапросы, результаты которых зависят от строк, которые выбираются в основном запросе.
Пример: найдем товары, цена которых выше средней = 42142
SELECT *
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products)
Операторы ALL, ANY, SOME, EXISTS
- ALL (условие в операции сравнения должно быть верно для всех значений, которые возвращаются подзапросом):
SELECT * FROM Products
WHERE Price < ALL(SELECT Price FROM Products WHERE Manufacturer=’Apple’) - ANY (условие в операции сравнения должно быть истинным для хотя бы одного из значений, возвращаемых подзапросом)
- EXISTS (позволяет проверить, возвращает ли подзапрос какое-либо значение, т.е. указывает, что в базе данных есть как минимум одна строка, которые соответствует данному запросу)
Подзапросы в основных командах SQL
1) в SELECT: с помощью WHERE, HAVING, FROM
2) в INSERT
3) в UPDATE
4) в DELETE:
DELETE FROM Orders
WHERE ProductId=(SELECT Id FROM Products WHERE ProductName=’Galaxy S8’)
SQL Реляционная модель БД
Реляционная модель - основана на таблицах, состоящих из строк и столбцов, и отношениях между ними, соотв. правилам Кодда (мат. операции над таблицами - выборка, проекция, объединение, соединение).
Атрибут - колонка таблицы с названием и типом данных (аналог свойства класса, где класс - это таблица).
Запись - строка таблицы (аналог экземпляра класса).
Постулаты реляционной модели:
1) Порядок строк в таблице не имеет значения
2) В таблице нет двух одинаковых строк
Первичный ключ - колонка для уникального id записи.
Представление - это выборка данных из таблицы и их визуализация в виде таблицы.
Условия целостности данных - ограничения на столбцы.
Транзакция - группа неразрывных действий, т.е. она либо полностью выполняется, либо полностью отменяется.
Правила Кодда
Правила Кодда:
1) Правило информации. Любые данные хранятся в ячейке таблицы, будь то метаданные или пользовательские данные.
2) Гарантированный доступ. Для доступа необходимо и достаточно: имя таблицы, первичный ключ, имя колонки. Косвенный доступ (через указатели) запрещён.
3) NULL-ы должны обрабатываться единообразно.
4) Активный каталог. Метаданные должны храниться в специальной базе. Пользователь может работать с ними, как и с любыми другими.
5) Доступ к данным открыт ТОЛЬКО через язык с линейным синтаксисом, поддерживающий описание данных, манипуляцию данными, управление транзакциями. Нельзя в обход, напрямую.
6) Все представления, которые могут обновляться, обновляются системой.
7) Высокоуровневая вставка, обновление, удаление данных. Вставка, обновление, удаление нескольких строк сразу.
8) Физическая независимость: приложениям должно быть не важно, как данные хранятся внутри.
9) Логическая независимость: представление не должно поменяться, если поменялись подлежащие таблицы.
10) Независимость целостности. Приложение должно быть независимым от условий целостности данных. Если меняются условия целостности данных, приложение менять не требуется.
11) Независимость от распределённости. Конечному пользователю должно быть всё равно, хранятся данные тут, там или раскиданы по всему миру.
12) Устойчивость. Если система предоставляет обходной интерфейс к данным, обходной путь не должен позволять обходить настройки безопасности и целостности данных.
Индексы БД
Индекс - вспомогательная информация для ускорения запросов, он привязан к колонке или колонкам (с любым типом данных с фиксированным размером), представляет собой сбалансированное двоичное дерево поиска (быстрее линейного поиска).
Типы индексов:
1) Кластеризованный индекс:
- в листьях дерева такого индекса хранятся сами строки таблицы
- может быть только один
- таблица физически отсортирована по этому индексу
2) Некластеризованный индекс:
- в листьях хранятся не сами строки, а косвенная информация, по которой можно будет найти настоящую строку (указатель для кучи, значение ключа для не кучи)
- Поиск по некластеризованному индексу медленнее, чем по кластеризованному. Требуется дополнительная операция перехода
- может быть много
3) Составной индекс: сортирует таблицу по нескольким столбцам:
- при этом оптимизация будет достигаться, если делать выборку по этим столбцам в порядке следования в индексе, либо делать выборку по первому столбцу индекса
4) Уникальный индекс: Обеспечивает уникальность значений в столбце или их сочетаний
Замечания:
Индексы ускоряют операции чтения, но могут замедлять операции записи (вставка, обновление, удаление), так как индексы нужно обновлять при изменении данных.
Недостатки индексов
Недостатки индексов:
1) занимает много памяти и места на жестком диске
2) может не давать выгоды, если запросы по этой колонке редкие
3) издержки системы на поддержание индекса в корректном состоянии
MS SQL Server Системные базы и таблицы:
- master (данные о самой СУБД)
- model (образ БД для создания на ее основе других)
- msdb (для планирования и автоматического запуска задач по расписанию)
- Resource (База только для чтения, где хранятся все метаданные (например, sys.objects и sys.indexes))
- tempdb (Пространство для временных таблиц. Будут уничтожены при выходе)
Отношения между таблицами
1) Один к одному (Каждой записи слева соответствует ровно одна запись справа)
2) Один ко многим (Пример: В каждой стране может быть МНОГО городов и Каждый город принадлежит лишь ОДНОЙ стране)
3) Много ко многим (Пример: Каждая группа в течение обучения проходит МНОГО предметов и Каждый предмет изучается МНОГИМИ группами):
- выражается в промежуточной таблице - по сути будет 2 отношения один ко многим
Нормализация и Нормальные формы
Нормализация - процесс поэтапного улучшения структуры БД.
Нормальные формы:
1) Первая нормальная форма (1НФ)
- В ячейках запрещено хранить составные значения
2) Вторая нормальная форма (2НФ):
- Таблица должна быть в 1НФ
- Данные в строке должны зависеть от всего ключа (не от части ключа!) - Надо находить зависимые колонки в таблице и отселять их в отдельную таблицу
3) Третья нормальная форма (3НФ):
- Таблица находится в 2НФ
- Нет транзитивных зависимостей между колонками (когда данные зависят от ключа не напрямую, а через промежуточную колонку)
SQL Соединение таблиц
1) Неявное соединение:
SELECT * FROM Orders, Customers
WHERE Orders.CustomerId = Customers.Id
2) Явное соединение таблиц:
2.1) INNER JOIN - внутреннее соединение:
SELECT Orders.CreatedAt, Orders.ProductCount, Products.ProductName
FROM Orders
JOIN Products ON Products.Id = Orders.ProductId
2.2) OUTER JOIN - внешнее соединение:
2.2.1) LEFT ( выборка будет содержать все строки из первой или левой таблицы):
SELECT FirstName, CreatedAt, ProductCount, Price, ProductId
FROM Orders LEFT JOIN Customers
ON Orders.CustomerId = Customers.Id
2.2.2) RIGHT (выборка будет содержать все строки из второй или правой таблицы)
2.2.3) FULL (выборка будет содержать все строки из обоих таблиц)
2.2.4) CROSS JOIN (перекрестное (каждая строка из одной таблицы соединяется с каждой строкой из второй таблицы))
3) Соединение однотипных выборок:
- UNION (без дубликатов, соответствие столбцов)
- EXCEPT (разность двух выборок)
- INTERSECT (пересечение (общие строки))
Сравнение INNER и OUTER JOIN
LEFT JOIN взял все строки из таблицы Customers, не смотря на то, что кто-то из них не имеет заказа, а INNER JOIN берет только те, которые связаны со второй таблицей.
T-SQL DDL (Data Definition Language)
T-SQL DDL (Data Definition Language):
1) Создание и удаление БД
2) Создание и удаление таблиц
3) Изменение таблицы
Пример:
CREATE DATABASE internetstore;
GO
USE internetstore;
CREATE TABLE Customers
(
Id INT PRIMARY KEY IDENTITY,
Age INT DEFAULT 18,
FirstName NVARCHAR(20) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
Email VARCHAR(30) UNIQUE,
Phone VARCHAR(20) UNIQUE
);
CREATE TABLE Orders
(
Id INT PRIMARY KEY IDENTITY,
CustomerId INT,
CreatedAt DATE,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE
);
T-SQL Пакеты команд
Пакеты команд - набор команд в скрипте, применяют потому, что одни выражения должны успешно выполниться до запуска других выражений, в качестве сигнала завершения пакета (т.е. выполнения всех его команд) служит команда GO
T-SQL DML (Data Manipulation Language)
T-SQL DML (Data Manipulation Language):
1) INSERT
2) SELECT
3) ORDER BY
4) WHERE
5) UPDATE
6) DELETE
7) GROUP BY, HAVING
T-SQL Встроенные функции для строк
T-SQL Встроенные функции для строк:
- LEN: возвращает количество символов в строке
- LTRIM: удаляет начальные пробелы из строки.
- RTRIM: удаляет конечные пробелы из строки
- CHARINDEX: возвращает индекс, по которому находится первое вхождение подстроки в строке
- PATINDEX: возвращает индекс, по которому находится первое вхождение определенного шаблона в строке
- LEFT: вырезает с начала строки определенное количество символов
- RIGHT: вырезает с конца строки определенное количество символов.
- SUBSTRING: вырезает из строки подстроку определенной длиной, начиная с определенного индекса
- REPLACE: заменяет одну подстроку другой в рамках строки
- REVERSE: переворачивает строку наоборот
- CONCAT: объединяет две строки в одну
- LOWER: переводит строку в нижний регистр
- UPPER: переводит строку в верхний регистр
- SPACE: возвращает строку, которая содержит определенное количество пробелов
T-SQL Встроенные функции для чисел
T-SQL Встроенные функции для чисел:
- ROUND: округляет число
- ISNUMERIC: определяет, является ли значение числом
- ABS: возвращает абсолютное значение числа
- CEILING: возвращает наименьшее целое число, которое больше или равно текущему значению
- FLOOR: возвращает наибольшее целое число, которое меньше или равно текущему значению
- SQUARE: возводит число в квадрат
- SQRT: получает квадратный корень числа
- RAND: генерирует случайное число с плавающей точкой в диапазоне от 0 до 1
- COS: возвращает косинус угла, выраженного в радианах
- SIN: возвращает синус угла, выраженного в радианах
- TAN: возвращает тангенс угла, выраженного в радианах
T-SQL Встроенные функции для даты/времени
T-SQL Встроенные функции для даты/времени:
- GETDATE: возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime
- GETUTCDATE: возвращает текущую локальную дату и время по гринвичу (UTC/GMT)
- SYSDATETIME: возвращает текущую локальную дату и время на основе системных часов в виде объекта datetime2
- SYSUTCDATETIME
- DAY: возвращает день даты, который передается в качестве параметра
- MONTH: возвращает месяц даты
- YEAR: возвращает год из даты
- DATENAME: возвращает часть даты в виде строки
- DATEPART: возвращает часть даты в виде числа
- DATEADD: возвращает дату, которая является результатом сложения числа к определенному компоненту даты
- DATEDIFF: возвращает разницу между двумя датами.
- ISDATE: проверяет, является ли выражение датой
- DATEFROMPARTS: по году, месяцу и дню создает дату
T-SQL Встроенные функции для преобразования данных
T-SQL Встроенные функции для преобразования данных:
- CAST (преобразует выражение одного типа к другому.)
- CONVERT (преобразование с форматированием)
- TRY_CONVERT (если выражение не удается преобразовать к нужному типу, то функция возвращает NULL)
- STR(float [, length [,decimal]]): преобразует число в строку. Второй параметр указывает на длину строки, а третий - сколько знаков в дробной части числа надо оставлять
- CHAR(int): преобразует числовой код ASCII в символ. Нередко используется для тех ситуаций, когда необходим символ, который нельзя ввести с клавиатуры
- ASCII(char): преобразует символ в числовой код ASCII
- NCHAR(int): преобразует числовой код UNICODE в символ
- UNICODE(char): преобразует символ в числовой код UNICODE
T-SQL Встроенные функции для создания условий
T-SQL Встроенные функции для создания условий:
- CASE .. WHEN…END (проверяет значение некоторого выражение, и в зависимости от результата проверки может возвращать тот или иной результат):
SELECT ProductName, Manufacturer,
CASE ProductCount
WHEN 1 THEN ‘Товар заканчивается’
WHEN 2 THEN ‘Мало товара’
WHEN 3 THEN ‘Есть в наличии’
ELSE ‘Много товара’
END AS EvaluateCount
FROM Products
- IIF (в зависимости от результата условного выражения возвращает одно из двух значений - типо тернарный оператора):
SELECT ProductName, Manufacturer,
IIF(ProductCount>3, ‘Много товара’, ‘Мало товара’)
FROM Products
T-SQL Основы
T-SQL - процедурное расширение языка SQL с новыми возможностями:
- Обработка ошибок с помощью TRY / CATCH
- управляющие операторы,
- Ветвления, циклы: IF / ELSE, WHILE, блок BEGIN .. END
- локальные и глобальные переменные:
DECLARE @i;
SET @i = 0;
- различные дополнительные функции для обработки строк, дат, математики ит.п.,
- поддержка аутентификации Microsoft Windows.
Типы команд T-SQL:
- DDL (Data Definition Language)
- DML (Data Manipulation Language)
- DCL (Data Control Language)
- TCL (Transaction Control Language)
Транзакционные уровни изоляции
Транзакционные уровни изоляции:
- READ UNCOMMITTED - Разрешает грязные чтения. Самый низкий уровень
- READ COMMITTED - Запрещает грязные чтения. Режим по умолчанию
- REPEATABLE READ - Гарантирует, что повторные выборки возвращают одно и то же
- SNAPSHOT - Делает слепок базы данных в начале транзакции
- SERIALIZABLE - Блокирует другим транзакциям
Высокие уровни изоляции снижают скорость работы
T-SQL Переменные
Переменная - именованный объект, хранящий значение:
DECLARE @name NVARCHAR(20);
SET @name=’Tom’;
Присваивание переменной результата запроса:
DECLARE @count INT;
SET @count = (SELECT SUM(ProductCount) FROM Orders);
Переменные в сложных запросах:
DECLARE @sum MONEY, @id INT, @prodid INT, @name NVARCHAR(20);
SET @id=2;
SELECT @sum = SUM(Orders.Price*Orders.ProductCount),
@name=Products.ProductName, @prodid = Products.Id
FROM Orders
INNER JOIN Products ON ProductId = Products.Id
GROUP BY Products.ProductName, Products.Id
HAVING Products.Id=@id
