Як оптимізувати великі таблиці в SQL

Posted on by data-life-ua

Передмова
Оптимізація великих таблиць — це давня тема, але коли бізнес масштабується, хтось завжди «потрапляє в халепу» через це.

Багато баз даних працюють добре на початку — запити виконуються плавно. Але як тільки обсяг даних у таблиці досягає десятків мільйонів записів, з’являються проблеми з продуктивністю: повільні запити, затримки при записі, повільна пагінація і навіть іноді збої системи.

У цей момент може виникнути питання: чи достатньо потужна база даних? Можливо, потрібно оновити апаратне забезпечення?

Насправді ж, корінь проблеми часто полягає в поганій оптимізації.

Сьогодні ми почнемо з ядра проблеми, крок за кроком аналізуючи типові вузькі місця продуктивності великих таблиць і розглянемо, як їх оптимізувати поетапно. Сподіваюсь, це буде для вас корисним.

1. Чому великі таблиці працюють повільно?

Перед тим як переходити до оптимізації, давайте спочатку зрозуміємо корінні причини проблем з продуктивністю великих таблиць. Чому база даних сповільнюється, коли обсяг даних зростає?

1.1 Вузьке місце: Disk I/O

Дані великих таблиць зберігаються на диску, і запити бази даних зазвичай передбачають читання блоків даних з диска.

Коли набір даних дуже великий, один запит може потребувати прочитати величезну кількість даних з кількох блоків диска. Швидкість читання/запису диска прямо обмежує продуктивність запиту.

Приклад:

Припустимо, у вас є таблиця замовлень orders з 50 мільйонами записів, і ви хочете отримати останні 10 замовлень певного користувача:

SELECT * FROM orders WHERE user_id = 123 ORDER BY order_time DESC LIMIT 10;

Якщо індексу немає, база даних буде сканувати всю таблицю та сортувати результати — продуктивність однозначно постраждає.

1.2 Відсутній або неефективний індекс

Якщо запит не використовує індекс, база даних виконає повне сканування таблиці — читає всі рядки один за одним.

Ця операція дуже ресурсоємна при десятках мільйонів записів, і продуктивність різко падає.

Приклад:

Припустимо, ви виконуєте запит на кшталт:

SELECT * FROM orders WHERE DATE(order_time) = '2025-01-01';

Тут використовується функція DATE(), яка змушує базу даних обчислювати значення order_time для всіх записів, через що індекс стає неефективним.

1.3 Погіршення продуктивності при пагінації

Пагінація часто використовується з великими таблицями, але глибока пагінація (наприклад, після сторінки 100) призводить до проблем з продуктивністю.

Навіть якщо потрібні лише 10 записів, база даних все одно повинна спочатку обробити всі попередні.

Приклад:

Отримати 10 записів зі сторінки 1000:

SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;

Цей SQL-запит змушує базу даних отримати перші 9990 записів, відкинути їх і повернути наступні 10.

Зі збільшенням номера сторінки продуктивність запиту продовжує погіршуватися.

1.4 Конкуренція за блокування

У сценаріях з високою конкуренцією кілька потоків одночасно виконують insert, update, delete або select на одній таблиці, що може призвести до конфліктів через row lock або table lock, і, як наслідок, зниження продуктивності.

2. Загальний підхід до оптимізації продуктивності

Суть оптимізації продуктивності — зменшити непотрібні I/O, обчислення та конкуренцію за блокування. Мета — мінімізувати «марні зусилля» бази даних.

Загальний підхід до оптимізації можна підсумувати так:

  • Розумне проектування структури таблиць: Уникати зайвих полів; розбивати дані, де можливо.
  • Ефективне індексування: Проектувати відповідні структури індексів та уникати їх невалідності
  • Оптимізація SQL-запитів: Робити умови точними і уникати повного сканування таблиць, коли це можливо.
  • Шардінг таблиць: Використовувати горизонтальний або вертикальний шардінг, щоб зменшити обсяг даних у таблиці.
  • Кешування та асинхронність: Зменшити прямий тиск на базу даних.

Далі ми розглянемо кожен із пунктів детальніше.

3. Оптимізація структури таблиць

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

3.1 Раціоналізація типів полів

Типи полів визначають розмір зберігання та продуктивність запитів.

  • Використовуйте INT замість BIGINT, коли можливо.
  • Використовуйте VARCHAR(100) замість TEXT, якщо це доцільно.
  • Для полів часу краще застосовувати TIMESTAMP або DATETIME, а не CHAR чи VARCHAR.

Приклад:

-- Not recommended
CREATE TABLE orders (
    id BIGINT,
    user_id BIGINT,
    order_status VARCHAR(255),
    remarks TEXT
);

-- Optimized
CREATE TABLE orders (
    id BIGINT,
    user_id INT UNSIGNED,
    order_status TINYINT,  -- Use enum for status
    remarks VARCHAR(500)   -- Set a maximum length
);

Це допомагає заощадити місце для зберігання та покращити продуктивність запитів.

3.2 Шардінг таблиць: вертикальний та горизонтальний

Вертикальний шардінг

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

Приклад:Розділити таблицю orders на дві таблиці — orders_basic і orders_details.

-- Basic information table
CREATE TABLE orders_basic (
    id BIGINT PRIMARY KEY,
    user_id INT UNSIGNED,
    order_time TIMESTAMP
);

-- Details table
CREATE TABLE orders_details (
    id BIGINT PRIMARY KEY,
    remarks VARCHAR(500),
    shipping_address VARCHAR(255)
);

Горизонтальний шардінг

Якщо одна таблиця містить занадто багато записів, її можна розділити на кілька таблиць за певними правилами.

Приклад:Розділити таблицю orders за user_id:

orders_0 -- Stores orders where user_id % 2 = 0
orders_1 -- Stores orders where user_id % 2 = 1

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

4. Оптимізація індексів

Індекси — це “основна зброя” для оптимізації продуктивності бази даних. Проте багато розробників не знають, як ефективно використовувати індекси, що може призвести до зниження, а не покращення продуктивності.

4.1 Створення відповідних індексів

Створюйте індекси для полів, які часто використовуються у запитах, таких як первинні ключі, зовнішні ключі та поля, що беруть участь у умовах WHERE.

Приклад:

CREATE INDEX idx_user_id_order_time ON orders (user_id, order_time DESC);

Вищенаведений складений індекс може значно прискорити запити, які одночасно фільтрують за user_id і сортують за order_time.

4.2 Уникнення невалідності індексів

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

Неправильно:

SELECT * FROM orders WHERE DATE(order_time) = '2023-01-01';

Оптимізовано:

SELECT * FROM orders WHERE order_time >= '2023-01-01 00:00:00'
  AND order_time < '2023-01-02 00:00:00';

Будьте обережні з неявним приведенням типів

Неправильно:

SELECT * FROM orders WHERE user_id = '123';

Оптимізовано:

SELECT * FROM orders WHERE user_id = 123;

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

5. Оптимізація SQL

5.1 Зменшуйте кількість запитуваних полів

Запитуйте лише ті поля, які вам реально потрібні. Уникайте використання SELECT *.

-- Incorrect
SELECT * FROM orders WHERE user_id = 123;

-- Optimized
SELECT id, order_time FROM orders WHERE user_id = 123;

Запит зайвих полів збільшує I/O та використання пам’яті, особливо коли таблиця містить великі поля, такі як TEXT або BLOB.

5.2 Оптимізація пагінації

Для глибокої пагінації використовуйте метод “seek” (також називають keyset pagination або cursor-based pagination), щоб уникнути сканування надмірної кількості даних.

-- Deep pagination (poor performance)
SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;

-- Optimized: use a cursor
SELECT * FROM orders
WHERE order_time < '2023-01-01 12:00:00'
ORDER BY order_time DESC
LIMIT 10;

Використовуючи контрольну точку (в цьому випадку order_time), база даних може пропускати непотрібні рядки та безпосередньо знаходити наступну партію даних, що значно підвищує продуктивність при роботі з великими наборами даних.

6. Розділення бази даних і таблиць

6.1 Горизонтальне розділення бази даних і таблиць

Якщо розділення однієї таблиці все ще не забезпечує потрібну продуктивність, можна розподілити дані між кількома базами даних і таблицями.

Поширені стратегії розділення:

  • Modulo за user_id
  • Розділення за модулем ідентифікатора користувача (user ID)

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

7. Кешування та асинхронність

7.1 Використання Redis для кешування «гарячих» даних

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

Приклад:

// Read data from cache
String result = redis.get("orders:user:123");
if (result == null) {
    result = database.query("SELECT * FROM orders WHERE user_id = 123");
    redis.set("orders:user:123", result, 3600); // Cache for 1 hour
}

Цей підхід особливо ефективний для даних, що не змінюються часто, наприклад, історії замовлень користувача за останні 30 днів.

7.2 Використання черг повідомлень для асинхронної обробки записів

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

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

8. Реальний приклад

Проблема:

Система e-commerce має таблицю orders з 50 мільйонами записів. Коли користувачі запитують деталі замовлень, сторінка завантажується більше 10 секунд.

Рішення:

  • Вертикальний шардінг таблиці orders: перемістити деталізовані поля в окрему таблицю.
  • Створення складених індексів: додати індекси на user_id та order_time.
  • Кешування через Redis: зберігати останні 30 днів замовлень користувача в Redis.
  • Оптимізація пагінації: використовувати search_after або інші методи cursor-based замість LIMIT для глибокої пагінації.

Ці оптимізації знизили час відповіді запиту з понад 10 секунд до менше 500 мс.

Підсумок

Оптимізація продуктивності великих таблиць — це системна задача, що вимагає комплексного підходу: від структури таблиць та індексів до SQL-запитів та архітектурного дизайну.

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

Найважливіший висновок: обирайте стратегію оптимізації, що найкраще підходить для вашого бізнес-сценарію, а не сліпо прагніть «красивих» або надмірно складних рішень.

Сподіваємося, ці уроки та техніки будуть для вас корисними!

Ми — Leapcell, ваш найкращий вибір для хостингу бекенд-проєктів.

Leapcell — це платформа наступного покоління безсерверного хостингу для веб-додатків, асинхронних задач та Redis:

Підтримка кількох мов

  • Розробляйте на Node.js, Python, Go або Rust.

Розгортайте необмежену кількість проєктів безкоштовно

  • платите лише за використання, без додаткових витрат за запити.

Неперевершена економічна ефективність

  • Модель pay-as-you-go без плати за простої.
  • Приклад: $25 підтримує 6,94 млн запитів зі середнім часом відповіді 60 мс.

Зручність для розробників

  • Інтуїтивний UI для легкого налаштування.
  • Повністю автоматизовані CI/CD пайплайни та інтеграція з GitOps.
  • Реальні метрики та логування для отримання корисної аналітики.

Легка масштабованість і висока продуктивність

  • Авто-масштабування для обробки високої конкуренції.
  • Нульове операційне навантаження — зосереджуйтесь лише на розробці.

Більше деталей можна знайти в документації!

Слідкуйте за нами на X:@LeapcellHQ

Читайте на нашому блозі

ОРИГІНАЛ СТАТТІ:How to Optimize Large Tables in SQL

АВТОР СТАТІ:Leapcell

🚀Долучайтесь до нашої спільноти Telegram:

🚀Долучайтесь до нашої спільноти FaceBook:

🚀Долучайтесь до нашої спільноти Twiter X:

Leave a Reply

Related Posts