DEV Community

Erlan Akbaraliev
Erlan Akbaraliev

Posted on • Edited on

БД6 (Index, оптимизация)

#database #performance #beginners #sql
  1. Index
  2. Transaction block
  3. CASE
  4. Процедура
  5. Контроль доступа
  6. Date

1. Index

Index - структура которая используется чтобы ускорить процесс получения данных.

SELECT * FROM книги
WHERE название='Белая книга';
Enter fullscreen mode Exit fullscreen mode

Index1

Мы получили данные за 0.103 секунд.
Это очень быстро, но если у нас в базе данных есть миллион книг, а не 73, то в этом случае запрос займет несколько секунд.

Мы можем индексировать столбец название таблицы книги, чтобы получить данные быстрее.

CREATE INDEX книги_название_индекс
ON книги (название);
Enter fullscreen mode Exit fullscreen mode

Если индексы ускоряют можно ли индексировать все столбцы наших таблиц?
Создать индекс практически на каждый столбец возможно, но категорически не рекомендуется.
Индексирование столбцов требует много памяти, это увеличивает стоимость хранения и замедляет скорость измение данных (INSERT, UPDATE, DELETE).

Можно и создать индексы на конкретные данные:

CREATE INDEX последние_книги
ON книги(год)
WHERE год=2023;
Enter fullscreen mode Exit fullscreen mode

Если у нас часто спрашивают книги которые были опубликованы в 2023 году, можно эти данные индексировать чтобы быстро их получать.

2. Транзакции

Тразакционный блок используется чтобы выпольнить несколько запросов ОДНОВРЕМЕННО. Если все запросы внутри транзакционного блока успешно выполнятся, сохрани результаты в таблицу немедленно (COMMIT). Если один запрос внутри транзакционного блока не выполнится успешно, приведи базу данных в предыдущее состояние (ROLLBACK).

Создайте эту таблицу

CREATE table счета (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT check(balance > 0)
);

INSERT INTO счета (name, balance)
VALUES 
('erlan', 10),
('алия', 20),
('нурайым', 30);
Enter fullscreen mode Exit fullscreen mode

Как выполнять несколько запросов одновременно?
Если Эрлан отправляет Алии 10 сомов, мы хотим немедленно вычесть 10 сомов со счета Эрлана и добавить 10 сомов на счет Алии.

BEGIN;
    UPDATE счета
    SET balance=balance-10
    WHERE name='erlan';

    UPDATE счета
    SET balance=balance+10
    WHERE name='алия';
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Теперь у Эрлана 0 сомов и у Алии 30 сомов.
Если опять выполним этот одновременный запрос, то мы получим ошибку. Как только мы начинаем транзакцию и пишем несколько SQL-запросов, если хотя бы один из них терпит неудачу, мы можем завершить транзакцию командой ROLLBACK (откат), чтобы вернуть все значения к их состоянию до начала транзакции.

3. CASE 

CASE
      WHEN условие_1  THEN результат_1
      WHEN условие_2  THEN результат_2
      [WHEN ...]
      [ELSE результат_по_умолчанию]
END
Enter fullscreen mode Exit fullscreen mode 
SELECT 
    название,
    CASE
        WHEN страниц >= 0 AND страниц <= 200 THEN 'маленький'
        WHEN страниц > 200 AND страниц <= 600 THEN 'средний'
        WHEN страниц > 600 THEN 'большой'
    END
FROM книги;
Enter fullscreen mode Exit fullscreen mode

Улучшенная версия:

SELECT название,
    CASE
        WHEN страниц <= 200 THEN 'маленький'
        WHEN страниц <= 600 THEN 'средний'
        ELSE 'большой'
    END
FROM книги;
Enter fullscreen mode Exit fullscreen mode 
SELECT название,
    CASE
        WHEN формат='мягкая обложка' THEN 'м'
        WHEN формат='твёрдая обложка' THEN 'т'
    END
FROM книги;
Enter fullscreen mode Exit fullscreen mode

Улучшенная версия:

SELECT название,
    CASE формат
        WHEN 'мягкая обложка' THEN 'м'
        WHEN 'твёрдая обложка' THEN 'т'
    END
FROM книги;
Enter fullscreen mode Exit fullscreen mode 
SELECT
    SUM (
        CASE WHEN страниц >= 0 AND страниц <= 200 THEN 1 ELSE 0 END       
    ) AS "маленькие",
    SUM (
        CASE WHEN страниц > 200 AND страниц <= 600 THEN 1 ELSE 0 END
    ) AS "средние",
    SUM (
        CASE WHEN страниц > 600 THEN 1 ELSE 0 END
    ) AS "большие"
FROM книги;
Enter fullscreen mode Exit fullscreen mode

4. Procedure (процедура)

Процедура - это набор запросов к которым можно дать какое-то название и переиспользовать.
С помощью ключевого слова "CALL" можем вызвать эту процедуру.

CREATE PROCEDURE add_balance(имя text, количество int)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE счета 
    SET balance=balance+количество
    WHERE name=имя;
END;
$$;

CALL add_balance('nurgazy', 5);
Enter fullscreen mode Exit fullscreen mode

Разница межлу Процедурами и Триггер функциями:

  • Триггер функции вызываются автоматически. Используются например чтобы автоматически добавить данные во вторую таблицу когда добавляем новые данные в первую таблицу.
  • Процедуры вызываем вручную.

5. Контроль доступа

В прошлых уроках мы входили в базу данных как администратор (root user), у нас был полный доступ к базе данных:

  • Создание новых базы данных
  • Создание новых таблиц
  • Добавление данных в таблицу
  • Чтение данных из таблиц
  • Создание представлений
  • Создание индексков

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

CREATE USER junior WITH PASSWORD 'junior';
Enter fullscreen mode Exit fullscreen mode

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

Чтобы войти в базу данных как пользователь junior:

  1. Disconnect from the database image1
  2. Выберите "Свойства" Image1
  3. В "Соединении" измените "имя пользователя" на "junior", сохраните, и дважды нажмите на 'PostgreSQL 18' под 'Servers' чтобы соединится к базу данных. Image3
  4. Введите пароль аккаунта 'junior', в моем случае имя пользователя и пароль 'junior'. Image4

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

Это обозначает что вы соединились в базу данных с названием 'my_db1' как пользователь 'junior'.
Image6

Если вы теперь напишите SELECT * FROM книги;, вы получите ОШИБКА: permission denied for table книги(Доступ к таблице книги отклонен). Так как мы только что создали пользователь 'junior', у него нет никаких разрешений.

Давайте дадим разрешение пользователю 'junior' читать данные из таблицы книги, сперва нужно выйти из аккаунта 'junior', зайти как админ. Имя пользователя аккаунта с полным разрешением на базу 'postgres'.

Типы разрешений:
Для таблиц:

  • SELECT: позволяет читать данные (выполнять запросы SELECT).
  • INSERT: позволяет добавлять новые строки.
  • UPDATE: позволяет изменять существующие данные.
  • DELETE: позволяет удалять строки.
  • REFERENCES: позволяет создавать внешние ключи (foreign keys), ссылающиеся на эту таблицу.

Для баз данных:

  • CONNECT: позволяет пользователю подключаться к конкретной базе данных.
  • CREATE: позволяет пользователю создавать новые схемы (schemas) внутри базы данных.

Доступ только для чтения к таблице книги:

GRANT SELECT ON TABLE книги TO junior;
Enter fullscreen mode Exit fullscreen mode

Полный контроль над таблицей:

GRANT ALL PRIVILEGES ON TABLE книги TO junior;
Enter fullscreen mode Exit fullscreen mode

Разрешение на создание объектов в базе данных:

GRANT CREATE ON DATABASE my_db1 TO junior;
Enter fullscreen mode Exit fullscreen mode

Доступ чтения на несколько таблиц:

GRANT SELECT ON TABLE авторы, авторы_книги, издатели TO junior;
Enter fullscreen mode Exit fullscreen mode

Доступ чтения на все таблицы в базе данных

GRANT SELECT ON ALL TABLES IN SCHEMA public TO junior;
Enter fullscreen mode Exit fullscreen mode

Удалить разрешение на чтение данных из таблицы книги

REVOKE SELECT ON TABLE книги FROM junior;
Enter fullscreen mode Exit fullscreen mode

Date

У нас есть 2 столбца про дату публикации книги:

  • год (2020)
  • опубликовано (2020-01-01)

Эти столбца разного типа

  • год INTEGER (номер)
  • опубликовано Date (Дата)

Если хотим показать книги которые были опубликованы в 2020 году используя столбец год:

SELECT * FROM книги
WHERE год=2020;
Enter fullscreen mode Exit fullscreen mode

Книги которые были опубликованы в 2020 году используя столбец опубликовано:

SELECT * FROM книги
WHERE опубликовано>='2020-01-01' AND опубликовано<='2020-12-12';
Enter fullscreen mode Exit fullscreen mode 
SELECT * FROM книги
WHERE EXTRACT(YEAR FROM опубликовано)=2020;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)