Thanaoscar

Day 15 - Pro: Indexes, Partitioning & Query Optimization

Thana Oscar's photo
Thana Oscar
·

4 min read

Bối cảnh: Hệ thống E-commerce (Thương mại điện tử)

1. User management

  • User management: Quản lý người dùng và vai trò (roles) trong hệ thống database.

  • Ví dụ: Tạo người dùng mới và gán quyền:

      CREATE USER analyst WITH PASSWORD 'securepassword';
  GRANT SELECT ON orders TO analyst;

    Câu lệnh này tạo người dùng analyst và cấp quyền đọc (SELECT) trên bảng orders.

2. CREATE USER | ROLE

  • CREATE USER: Tạo người dùng mới.

  • CREATE ROLE: Tạo vai trò mới để quản lý quyền.

  • Ví dụ:

      CREATE ROLE data_analyst;
  GRANT SELECT, INSERT ON products TO data_analyst;

    Câu lệnh này tạo vai trò data_analyst và cấp quyền đọc (SELECT) và thêm (INSERT) trên bảng products.

3. GRANT & REVOKE privileges

  • GRANT: Cấp quyền cho người dùng hoặc vai trò.

  • REVOKE: Thu hồi quyền từ người dùng hoặc vai trò.

  • Ví dụ:

      GRANT UPDATE ON customers TO data_analyst;
  REVOKE INSERT ON products FROM data_analyst;

    Câu lệnh này cấp quyền cập nhật (UPDATE) trên bảng customers và thu hồi quyền thêm (INSERT) trên bảng products từ vai trò data_analyst.

4. Privileges - Hands-on

  • Thực hành cấp và thu hồi quyền:

      -- Cấp quyền đọc và cập nhật trên bảng orders
  GRANT SELECT, UPDATE ON orders TO analyst;

  -- Thu hồi quyền cập nhật trên bảng orders
  REVOKE UPDATE ON orders FROM analyst;

5. Challenge: Privileges

  • Yêu cầu: Tạo vai trò inventory_manager và cấp quyền đọc, thêm, cập nhật trên bảng inventory.

6. Solution: Privileges

  • Giải pháp:

      CREATE ROLE inventory_manager;
  GRANT SELECT, INSERT, UPDATE ON inventory TO inventory_manager;

7. What are indexes?

  • Indexes: Cấu trúc dữ liệu giúp tăng tốc độ truy vấn bằng cách giảm số lượng hàng cần quét.

  • Ví dụ: Tạo index trên cột product_name trong bảng products:

      CREATE INDEX idx_product_name ON products(product_name);

8. B-Tree index

  • B-Tree index: Loại index phổ biến, phù hợp cho các truy vấn so sánh và sắp xếp.

  • Ví dụ: Tạo B-Tree index trên cột order_date trong bảng orders:

      CREATE INDEX idx_order_date ON orders(order_date);

9. Bitmap index

  • Bitmap index: Phù hợp cho các cột có giá trị phân biệt thấp (ví dụ: giới tính, trạng thái).

  • Ví dụ: Tạo bitmap index trên cột payment_method trong bảng orders:

      CREATE BITMAP INDEX idx_payment_method ON orders(payment_method);

10. Indexes - Guidelines

  • Khi nào sử dụng index:

    • Cột thường xuyên được sử dụng trong điều kiện WHERE, JOIN, ORDER BY.

    • Cột có giá trị duy nhất hoặc phân biệt cao.

  • Khi không sử dụng index:

    • Cột ít được sử dụng trong truy vấn.

    • Bảng có kích thước nhỏ.

11. Creating indexes

  • Tạo index:

      CREATE INDEX idx_customer_name ON customers(customer_name);

  • Xóa index:

      DROP INDEX idx_customer_name;

12. Execution plan & query performance

  • Execution plan: Kế hoạch thực thi truy vấn, giúp phân tích hiệu suất truy vấn.

  • Ví dụ: Xem execution plan của một truy vấn:

      EXPLAIN ANALYZE
  SELECT * FROM orders WHERE total_amount > 100;

    Kết quả trả về thông tin về cách database thực thi truy vấn, bao gồm số hàng quét và thời gian thực thi.

13. Partitioning

  • Partitioning: Chia bảng thành các phần nhỏ hơn để cải thiện hiệu suất truy vấn và quản lý dữ liệu.

  • Ví dụ: Phân vùng bảng orders theo tháng:

      CREATE TABLE orders (
      order_id INT PRIMARY KEY,
      customer_id INT,
      order_date DATE,
      total_amount DECIMAL
  ) PARTITION BY RANGE (order_date);

  CREATE TABLE orders_2023_01 PARTITION OF orders
      FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

  CREATE TABLE orders_2023_02 PARTITION OF orders
      FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

14. Today's slides

  • Tổng kết lại các kiến thức về Indexes, Partitioning, và Query Optimization.

  • Slide tham khảo cung cấp ví dụ chi tiết và ứng dụng trong hệ thống E-commerce.

15. Today's summary

  • Hôm nay, bạn đã học:

    • Cách quản lý người dùng và quyền trong database.

    • Cách tạo và sử dụng Indexes để tối ưu hóa truy vấn.

    • Cách sử dụng Partitioning để cải thiện hiệu suất và quản lý dữ liệu.

    • Cách phân tích Execution Plan để tối ưu hóa truy vấn.

  • Hẹn gặp lại bạn vào Extra Day với chủ đề CTEs (Common Table Expressions)!

16. Today's challenges

  • Thử thách 1: Tạo index trên cột category trong bảng products và phân tích hiệu suất truy vấn.

  • Thử thách 2: Phân vùng bảng orders theo năm và tháng.

  • Thử thách 3: Phân tích execution plan của truy vấn tìm khách hàng có tổng giá trị đơn hàng lớn hơn 500.

SQL