Trong bài đầu tiên của series, chúng ta đã tìm hiểu về nền tảng tối ưu hóa cơ sở dữ liệu và tư duy phương pháp. Bây giờ, chúng ta sẽ đi sâu vào một trong những kỹ năng quan trọng nhất mà bất kỳ nhà phát triển nào làm việc với cơ sở dữ liệu cần phải thành thạo: phân tích và tối ưu câu truy vấn SQL.

Câu truy vấn SQL là nơi mà hiệu năng cơ sở dữ liệu được quyết định. Một câu truy vấn được viết tốt có thể chạy trong vài mili giây, trong khi một câu truy vấn không tối ưu có thể mất hàng phút hoặc thậm chí làm treo hệ thống. Trong bài viết này, chúng ta sẽ khám phá cách phân tích, hiểu và tối ưu câu truy vấn SQL một cách toàn diện.

Execution plan và cách đọc hiểu

Execution plan (hay query plan) là bản thiết kế chi tiết về cách hệ quản trị cơ sở dữ liệu thực thi một câu truy vấn. Đây là công cụ quan trọng nhất để hiểu và tối ưu câu truy vấn.

Cách lấy execution plan

Mỗi hệ quản trị cơ sở dữ liệu có cú pháp riêng để lấy execution plan:

-- MySQL/MariaDB
EXPLAIN SELECT * FROM products WHERE category_id = 5;

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM products WHERE category_id = 5;

-- SQL Server
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM products WHERE category_id = 5;
GO
SET SHOWPLAN_ALL OFF;

-- Oracle
EXPLAIN PLAN FOR SELECT * FROM products WHERE category_id = 5;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Các thành phần chính của execution plan

  1. Access methods: Cách database truy cập dữ liệu

    • Table scan (full scan)
    • Index scan
    • Index seek
    • Range scan
    • Lookup operations

  2. Join algorithms:

    • Nested loop join
    • Hash join
    • Merge join

  3. Operators:

    • Filter
    • Sort (filesort)
    • Aggregate
    • Temporary table

Phân tích execution plan hiệu quả

Khi đọc execution plan, hãy chú ý đến các yếu tố sau:

  1. Scan types: Full table scan thường là dấu hiệu của vấn đề
  2. Rows examined: Số lượng rows được quét qua
  3. Cost estimates: Chi phí tương đối của mỗi operation
  4. Temporary tables và filesorts: Thường là dấu hiệu của vấn đề hiệu năng
  5. Index usage: Index nào được sử dụng và cách sử dụng

Ví dụ phân tích execution plan trong MySQL:

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEproductsNULLALLNULLNULLNULLNULL100010.00Using where

Trong ví dụ này, type: ALL cho thấy đang thực hiện full table scan, không sử dụng index nào. Điều này có thể là vấn đề nếu bảng products có nhiều rows.

Case study: Từ execution plan đến tối ưu

Giả sử chúng ta có câu truy vấn sau:

SELECT o.order_id, o.order_date, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-01-31'
ORDER BY o.order_date DESC;

Execution plan cho thấy:

  1. Full table scan trên bảng orders
  2. Nested loop join với customers
  3. Nested loop join với order_items
  4. Nested loop join với products
  5. Filesort cho ORDER BY

Vấn đề: Không có index trên order_date và các khóa ngoại.

Giải pháp:

-- Thêm index cho order_date
CREATE INDEX idx_orders_date ON orders(order_date);

-- Thêm index cho các khóa ngoại nếu chưa có
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

Sau khi tối ưu, execution plan mới:

  1. Index range scan trên orders sử dụng idx_orders_date
  2. Index lookup trên các bảng còn lại
  3. Vẫn có filesort nhưng trên tập dữ liệu nhỏ hơn nhiều

Kết quả: Thời gian truy vấn giảm từ 2.5 giây xuống 150ms.

Kỹ thuật tối ưu câu truy vấn hiệu quả

Sau khi hiểu cách đọc execution plan, chúng ta có thể áp dụng các kỹ thuật tối ưu câu truy vấn.

1. Sử dụng index hiệu quả

Index là yếu tố quan trọng nhất trong tối ưu truy vấn, nhưng cần sử dụng đúng cách:

-- Không tốt: Hàm trên cột được index làm mất khả năng sử dụng index
SELECT * FROM users WHERE YEAR(created_at) = 2023;

-- Tốt: Viết lại để sử dụng được index
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

2. Chỉ lấy dữ liệu cần thiết

-- Không tốt: SELECT *
SELECT * FROM products WHERE category_id = 5;

-- Tốt: Chỉ lấy các cột cần thiết
SELECT product_id, product_name, price FROM products WHERE category_id = 5;

3. Tối ưu JOIN operations

-- Không tốt: JOIN không cần thiết
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date > '2023-01-01';

-- Tốt: Loại bỏ JOIN không cần thiết
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date > '2023-01-01';

4. Sử dụng LIMIT và phân trang

-- Không tốt: Lấy tất cả kết quả
SELECT * FROM products ORDER BY created_at DESC;

-- Tốt: Sử dụng phân trang
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 40;

-- Tốt hơn: Sử dụng keyset pagination
SELECT * FROM products
WHERE created_at < '2023-05-10 15:30:00'
ORDER BY created_at DESC
LIMIT 20;

5. Tối ưu điều kiện WHERE

-- Không tốt: OR với nhiều điều kiện
SELECT * FROM products
WHERE category_id = 5 OR category_id = 6 OR category_id = 7;

-- Tốt: Sử dụng IN
SELECT * FROM products
WHERE category_id IN (5, 6, 7);

6. Sử dụng EXPLAIN để kiểm tra trước và sau khi tối ưu

Luôn sử dụng EXPLAIN để xác nhận rằng các thay đổi thực sự cải thiện execution plan.

Anti-patterns cần tránh trong SQL

Có một số mẫu thiết kế và cách viết SQL phổ biến nhưng lại gây hại cho hiệu năng. Dưới đây là những anti-pattern cần tránh:

1. N+1 query problem

// Không tốt: N+1 queries
$orders = Order::findAll();
foreach ($orders as $order) {
    $customer = Customer::findById($order->customer_id);
    echo $customer->name;
}

// Tốt: Eager loading
$orders = Order::findAll()->with('customer');
foreach ($orders as $order) {
    echo $order->customer->name;
}

2. Sử dụng hàm trên cột được index

-- Không tốt: Hàm trên cột được index
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Tốt: Sử dụng index
SELECT * FROM users WHERE email = '[email protected]';
-- Hoặc tạo functional index nếu cần
CREATE INDEX idx_lower_email ON users(LOWER(email));

3. Implicit conversions

-- Không tốt: Implicit conversion
SELECT * FROM users WHERE user_id = '42';  -- user_id là INTEGER

-- Tốt: Sử dụng đúng kiểu dữ liệu
SELECT * FROM users WHERE user_id = 42;

4. SELECT * khi không cần thiết

-- Không tốt: SELECT * trong JOIN
SELECT *
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

-- Tốt: Chỉ lấy cột cần thiết
SELECT o.order_id, o.order_date, p.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

5. Sử dụng DISTINCT không cần thiết

-- Không tốt: DISTINCT không cần thiết
SELECT DISTINCT customer_id FROM orders WHERE order_date > '2023-01-01';

-- Tốt: Sử dụng GROUP BY
SELECT customer_id FROM orders WHERE order_date > '2023-01-01' GROUP BY customer_id;

6. Subqueries không hiệu quả

-- Không tốt: Correlated subquery
SELECT p.product_name,
       (SELECT COUNT(*) FROM order_items oi WHERE oi.product_id = p.product_id) as order_count
FROM products p;

-- Tốt: Sử dụng JOIN và GROUP BY
SELECT p.product_name, COUNT(oi.order_item_id) as order_count
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name;

Xử lý các truy vấn phức tạp và trường hợp đặc biệt

Ngoài các kỹ thuật tối ưu cơ bản, chúng ta cần biết cách xử lý các trường hợp phức tạp hơn.

1. Tối ưu truy vấn phân tích (Analytical queries)

Truy vấn phân tích thường liên quan đến GROUP BY, aggregations và xử lý lượng lớn dữ liệu:

-- Truy vấn phân tích phức tạp
SELECT
    DATE_FORMAT(o.order_date, '%Y-%m') as month,
    c.country,
    p.category,
    SUM(oi.quantity * oi.unit_price) as revenue,
    COUNT(DISTINCT o.order_id) as order_count
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2022-01-01' AND '2023-12-31'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m'), c.country, p.category
ORDER BY month, revenue DESC;

Kỹ thuật tối ưu:

  1. Sử dụng materialized views hoặc pre-aggregated tables
  2. Tạo index cho các cột trong GROUP BY và WHERE
  3. Cân nhắc sử dụng OLAP tools thay vì truy vấn trực tiếp OLTP database

2. Xử lý truy vấn full-text search

-- MySQL full-text search
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('database optimization' IN BOOLEAN MODE);

-- PostgreSQL full-text search
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'database & optimization');

Kỹ thuật tối ưu:

  1. Sử dụng full-text index
  2. Cân nhắc sử dụng specialized search engines như Elasticsearch
  3. Sử dụng trigram indexes cho fuzzy search

3. Xử lý truy vấn hierarchical data

-- Recursive CTE trong PostgreSQL/SQL Server
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 as level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

Kỹ thuật tối ưu:

  1. Sử dụng Materialized Path hoặc Nested Set models
  2. Cân nhắc denormalization cho truy vấn thường xuyên
  3. Sử dụng specialized graph databases cho dữ liệu phức tạp

4. Xử lý truy vấn temporal data

-- Truy vấn temporal data
SELECT * FROM product_prices
WHERE valid_from <= '2023-05-15'
AND (valid_to >= '2023-05-15' OR valid_to IS NULL);

Kỹ thuật tối ưu:

  1. Sử dụng composite index trên (valid_from, valid_to)
  2. Cân nhắc sử dụng temporal tables trong SQL Server hoặc PostgreSQL
  3. Partitioning theo thời gian

Công cụ và kỹ thuật nâng cao

Để tối ưu truy vấn hiệu quả hơn, chúng ta có thể sử dụng các công cụ và kỹ thuật nâng cao:

1. Query profiling tools

-- MySQL Query Profiler
SET profiling = 1;
SELECT * FROM large_table WHERE complex_condition;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- PostgreSQL pg_stat_statements
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
       nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

2. Sử dụng hints (khi cần thiết)

-- MySQL force index hint
SELECT * FROM products FORCE INDEX (idx_category) WHERE category_id = 5;

-- SQL Server query hint
SELECT * FROM products WITH (INDEX(idx_category)) WHERE category_id = 5;

-- Oracle hint
SELECT /*+ INDEX(p idx_category) */ * FROM products p WHERE category_id = 5;

Lưu ý: Hints nên được sử dụng cẩn thận và chỉ khi thực sự cần thiết, vì chúng có thể gây khó khăn cho việc bảo trì và có thể trở nên không tối ưu khi dữ liệu thay đổi.

3. Query rewriting

Đôi khi, viết lại hoàn toàn một câu truy vấn có thể mang lại hiệu quả tốt hơn:

-- Không tốt: Subquery trong WHERE
SELECT * FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');

-- Tốt: JOIN
SELECT o.* FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA';

4. Sử dụng window functions thay vì self-joins

-- Không tốt: Self-join để tính running total
SELECT o1.order_date,
       SUM(o2.order_amount) as running_total
FROM orders o1
JOIN orders o2 ON o2.order_date <= o1.order_date
GROUP BY o1.order_id, o1.order_date
ORDER BY o1.order_date;

-- Tốt: Window function
SELECT order_date,
       SUM(order_amount) OVER (ORDER BY order_date) as running_total
FROM orders
ORDER BY order_date;

Kết luận và bước tiếp theo

Phân tích và tối ưu câu truy vấn SQL là một kỹ năng thiết yếu cho bất kỳ nhà phát triển nào làm việc với cơ sở dữ liệu. Bằng cách hiểu execution plan, áp dụng các kỹ thuật tối ưu, tránh anti-patterns và xử lý các trường hợp phức tạp, chúng ta có thể cải thiện đáng kể hiệu năng ứng dụng.

Hãy nhớ rằng tối ưu truy vấn là một quá trình liên tục. Khi dữ liệu phát triển và thay đổi, các truy vấn cần được đánh giá lại và tối ưu định kỳ.

Trong bài tiếp theo của series, chúng ta sẽ đi sâu vào “Index và chiến lược indexing chuyên sâu” - một công cụ quan trọng khác trong bộ công cụ tối ưu cơ sở dữ liệu của chúng ta.

Bạn đã từng gặp phải trường hợp nào mà việc tối ưu một câu truy vấn SQL đã cải thiện đáng kể hiệu năng ứng dụng? Hãy chia sẻ kinh nghiệm của bạn trong phần bình luận bên dưới.