Шпаргалка по различным типам баз данных:
🔹 Реляционные БД
Хранят данные в таблицах со строками и столбцами, что позволяет выполнять сложные запросы и транзакции с высокой степенью согласованности и целостности, что идеально подходит для структурированных данных, которые хорошо вписываются в схемы.
🔹 Хранилища Ключ-Значение
Хранят данные в виде пар «ключ-значение», обеспечивая быстрый поиск по ключу, идеально подходят для сценариев, где критически важен быстрый доступ к неструктурированным данным.
🔹 In-Memort (БД в оперативной памяти)
Разработаны для обеспечения высокой скорости за счет хранения данных в оперативной памяти, а не на диске, и предлагают сверхбыстрые возможности чтения/записи, подходящие для аналитики и кэширования в реальном времени.
🔹 Документные БД
Хранят данные в виде документов JSON, BSON или XML, что делает их гибкими для хранения, извлечения и управления полуструктурированными данными.
🔹 БД с широкими столбцами (Wide column)
Используют табличный формат, но каждая строка может иметь разный набор столбцов; отлично подходит для анализа больших наборов данных с возможностью горизонтального масштабирования.
🔹 БД на основе графов
Разработаны для хранения сущностей и их взаимосвязей в графовой структуре, что позволяет эффективно запрашивать сложные и взаимосвязанные данные.
🔹 Бд на основе временных рядов (Time-series)
Оптимизированы для хранения и запроса последовательностей точек данных с течением времени, что делает их идеальными для мониторинга, отслеживания событий и аналитики в контекстах, чувствительных ко времени.
#database
🔹 Реляционные БД
Хранят данные в таблицах со строками и столбцами, что позволяет выполнять сложные запросы и транзакции с высокой степенью согласованности и целостности, что идеально подходит для структурированных данных, которые хорошо вписываются в схемы.
🔹 Хранилища Ключ-Значение
Хранят данные в виде пар «ключ-значение», обеспечивая быстрый поиск по ключу, идеально подходят для сценариев, где критически важен быстрый доступ к неструктурированным данным.
🔹 In-Memort (БД в оперативной памяти)
Разработаны для обеспечения высокой скорости за счет хранения данных в оперативной памяти, а не на диске, и предлагают сверхбыстрые возможности чтения/записи, подходящие для аналитики и кэширования в реальном времени.
🔹 Документные БД
Хранят данные в виде документов JSON, BSON или XML, что делает их гибкими для хранения, извлечения и управления полуструктурированными данными.
🔹 БД с широкими столбцами (Wide column)
Используют табличный формат, но каждая строка может иметь разный набор столбцов; отлично подходит для анализа больших наборов данных с возможностью горизонтального масштабирования.
🔹 БД на основе графов
Разработаны для хранения сущностей и их взаимосвязей в графовой структуре, что позволяет эффективно запрашивать сложные и взаимосвязанные данные.
🔹 Бд на основе временных рядов (Time-series)
Оптимизированы для хранения и запроса последовательностей точек данных с течением времени, что делает их идеальными для мониторинга, отслеживания событий и аналитики в контекстах, чувствительных ко времени.
#database
👍12❤2👏1
Типы ключей в базах данных
Primary key (Первичный ключ)
Уникальный идентификатор для каждой записи в таблице. Он не может быть равен NULL и должен быть уникальным для всей таблицы
Foreign key (Внешний ключ)
Поле в таблице, которое создает ссылочную связь между двумя таблицами. Оно используется для установления отношения между таблицами
Composite key (Составной ключ)
Комбинация двух или более столбцов, используемых в качестве первичного ключа, когда ни один отдельный столбец не достаточен для однозначной идентификации записи
Alternate key (Альтернативный ключ)
Уникальный идентификатор для записи, который не является первичным ключом. Его можно использовать в качестве вторичного ключа при поиске данных в таблице
Candidate key (Ключ-кандидат)
Уникальный идентификатор для записи в таблице, которая потенциально может использоваться в качестве первичного ключа
Surrogate key
Уникальный идентификатор для записи в таблице, обычно присваивается базой данных, а не самим пользователем
#database #sql
Primary key (Первичный ключ)
Уникальный идентификатор для каждой записи в таблице. Он не может быть равен NULL и должен быть уникальным для всей таблицы
Foreign key (Внешний ключ)
Поле в таблице, которое создает ссылочную связь между двумя таблицами. Оно используется для установления отношения между таблицами
Composite key (Составной ключ)
Комбинация двух или более столбцов, используемых в качестве первичного ключа, когда ни один отдельный столбец не достаточен для однозначной идентификации записи
Alternate key (Альтернативный ключ)
Уникальный идентификатор для записи, который не является первичным ключом. Его можно использовать в качестве вторичного ключа при поиске данных в таблице
Candidate key (Ключ-кандидат)
Уникальный идентификатор для записи в таблице, которая потенциально может использоваться в качестве первичного ключа
Surrogate key
Уникальный идентификатор для записи в таблице, обычно присваивается базой данных, а не самим пользователем
#database #sql
👍8🔥4🍾2
Некоторые распространеные методы оптимизации SQL-запросов:
1. Создавайте индекс для огромных таблиц (>1.000.000 строк)
2. Используйте EXIST() вместо COUNT(), чтобы найти элемент в таблице.
3. Используйте выборку отдельных полей с помощью SELECT вместо использования SELECT *
4. Избегайте подзапросов в предложении WHERE
5. Избегайте SELECT DISTINCT, где это возможно.
6. Используйте предложение WHERE вместо HAVING
7. Создавайте объединения с помощью INNER JOIN (не WHERE)
8. Используйте LIMIT для выборки результатов запроса
9. Используйте UNION ALL вместо UNION везде, где это возможно.
10. Используйте UNION where вместо запроса типа "WHERE ... OR ...".
11. Выполняйте запрос в часы наименьшей нагрузки
12. Избегайте использования OR в запросах на соединение.
14. Выберите GROUP BY вместо оконных функций (window function)
15. Используйте производные и временные таблицы
16. Удаляйте индекс перед загрузкой большого объема данных
17. Используйте материализованные представления (materialized views) вместо представлений
18. Избегайте операторов != или <> ( не равно)
19. Минимизируйте количество подзапросов.
20. Используйте INNER joinе как можно реже, если вы можете получить тот же результат с помощью LEFT/RIGHT join
21. Часто пытайтесь использовать временные источники для извлечения одного и того же набора данных.
#sql #database
1. Создавайте индекс для огромных таблиц (>1.000.000 строк)
2. Используйте EXIST() вместо COUNT(), чтобы найти элемент в таблице.
3. Используйте выборку отдельных полей с помощью SELECT вместо использования SELECT *
4. Избегайте подзапросов в предложении WHERE
5. Избегайте SELECT DISTINCT, где это возможно.
6. Используйте предложение WHERE вместо HAVING
7. Создавайте объединения с помощью INNER JOIN (не WHERE)
8. Используйте LIMIT для выборки результатов запроса
9. Используйте UNION ALL вместо UNION везде, где это возможно.
10. Используйте UNION where вместо запроса типа "WHERE ... OR ...".
11. Выполняйте запрос в часы наименьшей нагрузки
12. Избегайте использования OR в запросах на соединение.
14. Выберите GROUP BY вместо оконных функций (window function)
15. Используйте производные и временные таблицы
16. Удаляйте индекс перед загрузкой большого объема данных
17. Используйте материализованные представления (materialized views) вместо представлений
18. Избегайте операторов != или <> ( не равно)
19. Минимизируйте количество подзапросов.
20. Используйте INNER joinе как можно реже, если вы можете получить тот же результат с помощью LEFT/RIGHT join
21. Часто пытайтесь использовать временные источники для извлечения одного и того же набора данных.
#sql #database
👍24🥴1
Microsoft добавит в SQL Server 2025 (пока в состоянии Preview) поддержку регулярных выражений
Регулярные выражения предлагают несколько преимуществ при использовании в запросах SQL:
Гибкость в сопоставлении с шаблонами: регулярные выражения позволяют выполнять сложное и гибкое сопоставление с шаблонами, что упрощает поиск определенных текстовых шаблонов в больших наборах данных.
Эффективность в обработке данных: функции Regex могут эффективно обрабатывать и преобразовывать текстовые данные, уменьшая необходимость в нескольких строковых функциях и упрощая запросы.
Улучшенная проверка данных: регулярные выражения можно использовать для проверки форматов данных, гарантируя, что данные соответствуют определенным шаблонам, таким как адреса электронной почты, номера телефонов и почтовые индексы.
Улучшенное извлечение данных: функции Regex могут извлекать определенные части текстовых данных, что упрощает извлечение ценной информации из неструктурированных данных.
Стандартизация и очистка: Regex может помочь стандартизировать и очистить данные, удаляя нежелательные символы, пробелы и дубликаты.
Функция REGEXP_LIKE проверяет, соответствует ли строка шаблону. Пример:
SELECT [Name], Email
FROM Employees
WHERE REGEXP_LIKE(Email, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
Функция REGEXP_COUNT подсчитывает количество появлений шаблона в строке. Она полезна для анализа текстовых данных и выявления шаблонов.
SELECT REGEXP_SUBSTR(Email, '@(.+)$', 1, 1,'c',1) AS Domain, COUNT(*) AS Valid_email_count
FROM Employees
WHERE REGEXP_COUNT(Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') > 0
GROUP BY REGEXP_SUBSTR(Email, '@(.+)$', 1, 1,'c',1);
Функция REGEXP_INSTR находит позицию шаблона в строке. Она полезна для поиска определенных подстрок в текстовых данных.
SELECT Name, Email, REGEXP_INSTR(Email, '@') AS Position_of_@ FROM Employees;
Функция REGEXP_REPLACE возвращает измененную строку, замененную «строкой замены», где найдено вхождение шаблона регулярного выражения. Вы можете использовать ее для изменения или преобразования текстовых данных на основе шаблона регулярного выражения.
SELECT sensitive_info,
REGEXP_REPLACE(sensitive_info, '(\d{3}-\d{2}-\d{4}|\d{4}-\d{4}-\d{4}-\d{4})', '***-**-****') AS redacted_info
FROM personal_data;
Функция REGEXP_SUBSTR извлекает часть строки, которая соответствует шаблону. Она полезна для извлечения определенной информации из текстовых данных.
SELECT Name, Email, REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, 'i', 1) AS Domain
FROM Employees
WHERE REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');
Функция REGEXP_MATCHES находит все совпадения шаблона в строке и возвращает их в табличном формате (т. е. в виде строк и столбцов). Она полезна для извлечения нескольких вхождений шаблона. Обычно она используется для поиска всех вхождений шаблона или для извлечения частей строки с использованием групп захвата, особенно когда вы ожидаете несколько совпадений на вход.
SELECT *
FROM REGEXP_MATCHES('Name: John Doe; Email: john.doe@example.com; Phone: 123-456-7890', '(\w+):\s*([^;]+)');
Функция REGEXP_SPLIT_TO_TABLE разбивает строку на основе шаблона в качестве разделителя и возвращает фрагменты в виде строк в таблице. Она полезна для разбиения строк со списком элементов или разбиения текста на слова или предложения.
SELECT *
FROM REGEXP_SPLIT_TO_TABLE('Name: John Doe; Email: john.doe@example.com; Phone: 123-456-7890', '; ');
https://devblogs.microsoft.com/azure-sql/unlocking-the-power-of-regex-in-sql-server/
#sql #database
Регулярные выражения предлагают несколько преимуществ при использовании в запросах SQL:
Гибкость в сопоставлении с шаблонами: регулярные выражения позволяют выполнять сложное и гибкое сопоставление с шаблонами, что упрощает поиск определенных текстовых шаблонов в больших наборах данных.
Эффективность в обработке данных: функции Regex могут эффективно обрабатывать и преобразовывать текстовые данные, уменьшая необходимость в нескольких строковых функциях и упрощая запросы.
Улучшенная проверка данных: регулярные выражения можно использовать для проверки форматов данных, гарантируя, что данные соответствуют определенным шаблонам, таким как адреса электронной почты, номера телефонов и почтовые индексы.
Улучшенное извлечение данных: функции Regex могут извлекать определенные части текстовых данных, что упрощает извлечение ценной информации из неструктурированных данных.
Стандартизация и очистка: Regex может помочь стандартизировать и очистить данные, удаляя нежелательные символы, пробелы и дубликаты.
Функция REGEXP_LIKE проверяет, соответствует ли строка шаблону. Пример:
SELECT [Name], Email
FROM Employees
WHERE REGEXP_LIKE(Email, '[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
Функция REGEXP_COUNT подсчитывает количество появлений шаблона в строке. Она полезна для анализа текстовых данных и выявления шаблонов.
SELECT REGEXP_SUBSTR(Email, '@(.+)$', 1, 1,'c',1) AS Domain, COUNT(*) AS Valid_email_count
FROM Employees
WHERE REGEXP_COUNT(Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') > 0
GROUP BY REGEXP_SUBSTR(Email, '@(.+)$', 1, 1,'c',1);
Функция REGEXP_INSTR находит позицию шаблона в строке. Она полезна для поиска определенных подстрок в текстовых данных.
SELECT Name, Email, REGEXP_INSTR(Email, '@') AS Position_of_@ FROM Employees;
Функция REGEXP_REPLACE возвращает измененную строку, замененную «строкой замены», где найдено вхождение шаблона регулярного выражения. Вы можете использовать ее для изменения или преобразования текстовых данных на основе шаблона регулярного выражения.
SELECT sensitive_info,
REGEXP_REPLACE(sensitive_info, '(\d{3}-\d{2}-\d{4}|\d{4}-\d{4}-\d{4}-\d{4})', '***-**-****') AS redacted_info
FROM personal_data;
Функция REGEXP_SUBSTR извлекает часть строки, которая соответствует шаблону. Она полезна для извлечения определенной информации из текстовых данных.
SELECT Name, Email, REGEXP_SUBSTR(Email, '@(.+)$', 1, 1, 'i', 1) AS Domain
FROM Employees
WHERE REGEXP_LIKE(Email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');
Функция REGEXP_MATCHES находит все совпадения шаблона в строке и возвращает их в табличном формате (т. е. в виде строк и столбцов). Она полезна для извлечения нескольких вхождений шаблона. Обычно она используется для поиска всех вхождений шаблона или для извлечения частей строки с использованием групп захвата, особенно когда вы ожидаете несколько совпадений на вход.
SELECT *
FROM REGEXP_MATCHES('Name: John Doe; Email: john.doe@example.com; Phone: 123-456-7890', '(\w+):\s*([^;]+)');
Функция REGEXP_SPLIT_TO_TABLE разбивает строку на основе шаблона в качестве разделителя и возвращает фрагменты в виде строк в таблице. Она полезна для разбиения строк со списком элементов или разбиения текста на слова или предложения.
SELECT *
FROM REGEXP_SPLIT_TO_TABLE('Name: John Doe; Email: john.doe@example.com; Phone: 123-456-7890', '; ');
https://devblogs.microsoft.com/azure-sql/unlocking-the-power-of-regex-in-sql-server/
#sql #database
Microsoft News
Unlocking the Power of Regex in SQL Server
Regular expressions, or regex, are powerful tools for text manipulation and pattern matching. They are incredibly useful in various scenarios, such as data validation, extraction, and transformation. SQL Server 2025 introduces native support for regex functions…
🔥14👍2🥱2👏1
B+ trees (Деревья B+) и их применение в базах данных
Деревья B+ являются наиболее широко используемой структурой данных для индексирования в современных реляционных системах баз данных. Они специально разработаны для эффективного хранения и извлечения больших объемов данных на диске, обеспечивая высокую производительность поиска, вставки, удаления и запросов диапазона.
Основные реляционные базы данных, такие как MySQL (InnoDB), PostgreSQL, SQL Server и Oracle, используют деревья B+ для первичных и вторичных индексов, что делает их незаменимыми для масштабируемых, высокопроизводительных систем баз данных.
КЛЮЧЕВЫЕ ХАРАКТЕРИСТИКИ
- Все данные в листовых узлах
В B+ дереве все фактические данные записей (или указатели на полные записи) хранятся исключительно в листовых узлах. Внутренние узлы хранят только ключи и указатели на дочерние узлы, облегчая навигацию.
- Сбалансированная, многоуровневая структура
B+ деревья всегда сбалансированы по высоте. Все листовые узлы находятся на одной глубине, обеспечивая сложность O(log n) для операций поиска, вставки и удаления, независимо от размера базы данных.
- Эффективные запросы диапазона и упорядоченные запросы
Листовые узлы связаны вместе в двунаправленном списке, что позволяет быстро выполнять последовательный доступ для запросов диапазона, таких как BETWEEN, ORDER BY и сканирование индексов.
- Высокая степень заполнения
Каждый узел может содержать много ключей (высокая степень заполнения), что минимизирует высоту дерева и уменьшает дисковый ввод-вывод за счет максимизации данных на чтение.
- Оптимизация диска
B+ деревья разработаны для соответствия размерам блоков диска, что уменьшает количество обращений к диску, необходимых для операций.
ПОЧЕМУ B+ ДЕРЕВЬЯ ПРЕДПОЧИТАЮТСЯ В БАЗАХ ДАННЫХ?
- Сбалансированная структура обеспечивает последовательный доступ.
- Высокая степень заполнения соответствует размерам страниц диска, уменьшая ввод-вывод.
- Связанные листья делают сканирование диапазона быстрым.
- Узлы могут быть разделены/объединены с минимальным блокированием, поддерживая высококонкурентные среды.
ИСПОЛЬЗОВАНИЕ В ОСНОВНЫХ СИСТЕМАХ БАЗ ДАННЫХ
- MySQL (InnoDB)
Кластерный индекс (первичный ключ): хранит всю строку данных в B+ дереве.
Вторичные индексы: B+ деревья содержат индексированные столбцы и указатели на первичный ключ.
- PostgreSQL
Тип индекса по умолчанию (btree) для большинства столбцов.
Используется для уникальных и неуникальных индексов.
Поддерживает эффективные запросы на равенство и диапазон.
- СУБД Oracle
B+ деревья используются для стандартных индексов (одиночные, составные, основанные на функциях).
Организованные таблицы индексов (IOT) хранят все данные в листовых блоках B+ дерева.
Листовые блоки связаны для сканирования диапазона.
#database
Деревья B+ являются наиболее широко используемой структурой данных для индексирования в современных реляционных системах баз данных. Они специально разработаны для эффективного хранения и извлечения больших объемов данных на диске, обеспечивая высокую производительность поиска, вставки, удаления и запросов диапазона.
Основные реляционные базы данных, такие как MySQL (InnoDB), PostgreSQL, SQL Server и Oracle, используют деревья B+ для первичных и вторичных индексов, что делает их незаменимыми для масштабируемых, высокопроизводительных систем баз данных.
КЛЮЧЕВЫЕ ХАРАКТЕРИСТИКИ
- Все данные в листовых узлах
В B+ дереве все фактические данные записей (или указатели на полные записи) хранятся исключительно в листовых узлах. Внутренние узлы хранят только ключи и указатели на дочерние узлы, облегчая навигацию.
- Сбалансированная, многоуровневая структура
B+ деревья всегда сбалансированы по высоте. Все листовые узлы находятся на одной глубине, обеспечивая сложность O(log n) для операций поиска, вставки и удаления, независимо от размера базы данных.
- Эффективные запросы диапазона и упорядоченные запросы
Листовые узлы связаны вместе в двунаправленном списке, что позволяет быстро выполнять последовательный доступ для запросов диапазона, таких как BETWEEN, ORDER BY и сканирование индексов.
- Высокая степень заполнения
Каждый узел может содержать много ключей (высокая степень заполнения), что минимизирует высоту дерева и уменьшает дисковый ввод-вывод за счет максимизации данных на чтение.
- Оптимизация диска
B+ деревья разработаны для соответствия размерам блоков диска, что уменьшает количество обращений к диску, необходимых для операций.
ПОЧЕМУ B+ ДЕРЕВЬЯ ПРЕДПОЧИТАЮТСЯ В БАЗАХ ДАННЫХ?
- Сбалансированная структура обеспечивает последовательный доступ.
- Высокая степень заполнения соответствует размерам страниц диска, уменьшая ввод-вывод.
- Связанные листья делают сканирование диапазона быстрым.
- Узлы могут быть разделены/объединены с минимальным блокированием, поддерживая высококонкурентные среды.
ИСПОЛЬЗОВАНИЕ В ОСНОВНЫХ СИСТЕМАХ БАЗ ДАННЫХ
- MySQL (InnoDB)
Кластерный индекс (первичный ключ): хранит всю строку данных в B+ дереве.
Вторичные индексы: B+ деревья содержат индексированные столбцы и указатели на первичный ключ.
- PostgreSQL
Тип индекса по умолчанию (btree) для большинства столбцов.
Используется для уникальных и неуникальных индексов.
Поддерживает эффективные запросы на равенство и диапазон.
- СУБД Oracle
B+ деревья используются для стандартных индексов (одиночные, составные, основанные на функциях).
Организованные таблицы индексов (IOT) хранят все данные в листовых блоках B+ дерева.
Листовые блоки связаны для сканирования диапазона.
#database
🔥9👍3👏1🏆1