11 Технік Оптимізації Бази Даних

Posted on by data-life-ua

База даних часто стає “вузьким горлом” у продуктивності програмного забезпечення. Оптимізована база даних є ключовою для високопродуктивних систем. Ось 11 ефективних технік оптимізації баз даних:

Індексація (INDEX):

Створення Індексів: Індекси – це структури даних, які забезпечують швидкий механізм пошуку та значно покращують продуктивність запитів. Вони працюють, створюючи впорядковану структуру даних, яка дозволяє двигуну бази даних швидко знаходити рядки, які задовольняють умові WHERE. Хоча індекси прискорюють запити SELECT, вони можуть уповільнювати операції запису(INSERT), тому важливо знаходити баланс між читанням та записом для покращення продуктивності.

CREATE INDEX idx_username ON users(username);

Використання Композитних Індексів (Composite Indexes): Композитні індекси включають кілька стовпців і корисні для запитів, які фільтрують або сортують за кількома умовами. Це зменшує потребу в окремих індексах для кожного стовпця та покращує ефективність планувальника запитів.

CREATE INDEX idx_name_age ON employees(NAME, age);

Нормалізація та Денормалізація:

Нормалізація: Цей процес організовує дані для мінімізації зайвості та залежності, зменшуючи ймовірність аномалій в даних. Розбиваючи великі таблиці на менші, пов’язані, нормалізація забезпечує консистентність даних. Однак це може призвести до більш складних запитів.

Фрагмент SQL-коду (приклад третьої нормальної форми):

CREATE TABLE customers
  (
     customer_id   INT PRIMARY KEY,
     customer_name VARCHAR(100),
     address       VARCHAR(255)
  );

CREATE TABLE orders
  (
     order_id    INT PRIMARY KEY,
     customer_id INT,
     order_date  DATE,
     FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  );

Денормалізація: У той час як нормалізація зменшує зайвість, денормалізація вводить контрольовану зайвість для покращення продуктивності запитів, особливо для операцій з читанням. Це може включати додавання стратегічних зайвих стовпців або таблиць.

CREATE TABLE denormalized_orders
  (
     order_id      INT PRIMARY KEY,
     customer_id   INT,
     customer_name VARCHAR(100),
     order_date    DATE
  );

Оптимізація Запитів

Оптимізація Запитів: Регулярно аналізуйте та оптимізуйте часто використовувані запити. Використовуйте інструменти, такі як EXPLAIN, щоб зрозуміти план виконання запиту та визначити області для покращення.

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Уникайте SELECT *: Отримуйте лише необхідні стовпці, а не вибирайте всі стовпці. Це зменшує обсяг передаваної та оброблюваної інформації, поліпшуючи продуктивність запиту.

SELECT order_id,
       order_date
FROM   orders
WHERE  customer_id = 123;

Партиціювання (Partitioning)

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

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN (2010),
    PARTITION p3 VALUES LESS THAN (2020),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

Обрізання Розділів/Партицій: Переконайтеся, що планувальник запитів обрізає непотрібні розділи під час виконання запиту. Це запобігає просуванню по всьому набору даних та покращує продуктивність.

SELECT *
FROM   sales
WHERE  sale_date >= '2022-01-01'
   AND sale_date < '2023-01-01';

Кешування

Кешування Запитів: Впроваджуйте механізм кешування для зберігання результатів часто виконуваних запитів. Це зменшує навантаження на базу даних, надаючи збережені результати, що покращує час відгуку.

-- Pseudocode
DECLARE @cacheKey NVARCHAR(255) = 'query_cache_key';
DECLARE @cachedResult NVARCHAR(MAX);

SET @cachedResult = REDIS.GET(@cacheKey);

IF @cachedResult IS NULL
BEGIN
    -- Execute the query and store the result in the cache
    SET @cachedResult = EXECUTE_QUERY('SELECT * FROM large_table');
    REDIS.SET(@cacheKey, @cachedResult, EXPIRY_TIME);
END

-- Use @cachedResult for further processing

Кешування Об’єктів: Кешуйте часто використовувані об’єкти або дані на рівні додатка для мінімізації запитів до бази даних. Це можна досягти за допомогою бібліотек чи фреймворків кешування в пам’яті.

from django.core.cache import cache

def get_user_data(user_id):
    # Try to fetch user data from cache
    user_data = cache.get(f'user_{user_id}')

    if user_data is None:
        # If not in cache, fetch from the database
        user_data = User.objects.get(id=user_id)

        # Store the data in cache for future requests
        cache.set(f'user_{user_id}', user_data, TIMEOUT)

    return user_data

Регулярне Обслуговування:

Оновлення Статистики: Підтримка актуальних статистичних даних є важливою для планувальника запитів при прийнятті інформованих рішень щодо планів виконання. Регулярно оновлюйте статистику для забезпечення точної та ефективної оптимізації запитів.

-- Update statistics for a table
UPDATE STATISTICS table_name;

Архівація Даних: Архівуйте або очищайте старі дані, які більше не потрібні. Це може покращити продуктивність запитів та зменшити обсяг використаного місця, особливо в системах із великими історичними наборами даних.

-- Archive data older than a certain date
DELETE FROM historical_data
WHERE  date < '2020-01-01';

Оптимізація Апаратних Засобів:

Оптимізація Конфігурації Сервера: Налаштовуйте параметри та конфігурації сервера бази даних відповідно до навантаження та можливостей апаратного забезпечення. Це включає параметри, такі як розміри буферів, налаштування кешування та обмеження з’єднань.

-- Example: Increase the size of the query cache
SET global query_cache_size = 256m;

Використовуйте SSD: Розгляньте використання твердотільних накопичувачів (SSD) для зберігання. SSD забезпечують швидший доступ до даних порівняно з традиційними жорсткими дисками (HDD), що призводить до покращення загальної продуктивності бази даних.

Контроль Конкурентності:

Рівні Ізоляції: Налаштовуйте рівні ізоляції відповідно до вимог вашого додатка. Рівні ізоляції контролюють видимість змін, внесених одною транзакцією, іншим транзакціям. Вибір відповідного рівня ізоляції є ключовим для забезпечення балансу між консистентністю та продуктивністю.

-- Set isolation level to READ COMMITTED
SET TRANSACTION isolation level READ committed;

Пул з’єднань:

Використовуйте Пул з’єднань (Connection Pooling): Перевикористовуйте з’єднання з базою даних, щоб уникнути надмірної витрати ресурсів на встановлення нових з’єднань для кожного запиту. Пул з’єднань допомагає ефективно управляти та перевикористовувати з’єднання з базою даних.

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/database");
config.setUsername("username");
config.setPassword("password");
config.setMaximumPoolSize(10);

HikariDataSource dataSource = new HikariDataSource(config);

Проектування баз даних:

Ефективне проектування схеми: Проектуйте схему бази даних з урахуванням продуктивності. Оптимізуйте типи даних, використовуйте відповідні обмеження і мінімізуйте зайві відносини. Добре проектована схема може значно впливати на ефективність запитів.

CREATE TABLE products
  (
     product_id   INT PRIMARY KEY,
     product_name VARCHAR(255),
     price        DECIMAL(10, 2),
  -- Additional columns as needed
  );

Моніторинг та профілювання:

Регулярний моніторинг: Впроваджуйте інструменти моніторингу для відстеження продуктивності бази даних протягом часу. Регулярно моніторте ключові метри, такі як використання ЦП, використання пам’яті та час виконання запитів, щоб виявити потенційні проблеми.

SHOW status LIKE `cpu%`;

Профілювання запитів: Профілюйте та аналізуйте продуктивність окремих запитів для виявлення проблемних місць. Інструменти, такі як MySQL Performance Schema, можуть надавати детальні відомості про виконання запитів.

-- Enable Performance Schema

SET GLOBAL performance_schema = ON;

--Profile a specific query;
SELECT * FROM orders WHERE customer_id = 123;

Ці методи – найбільш загальні техніки оптимізації баз даних. Залиште коментар нижче, який саме метод ви найчастіше використовуєте і який виявився для вас найбільш ефективним.

ОРИГІНАЛ СТАТТІ:11 Database Optimization Techniques

АВТОР СТАТІ:Daniel Foo

Долучайтесь до нашої спільноти Telegram
* Data Life UA
* Data Analysis UA
* DATA ENGINEERING UA
Долучайтесь до нашої спільноти FaceBook
* Data-Life-UA

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts