Việc tối ưu hiệu năng trong MySQL chủ yếu xoay quanh việc thiết kế và sử dụng chỉ mục (index) hợp lý.

Dưới đây là cấu trúc hai bảng minh họa:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Bảng danh mục sản phẩm
DROP TABLE IF EXISTS `Category`;
CREATE TABLE `Category` (
  `Id` int NOT NULL,
  `Name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`Id`),
  INDEX `idx_id_name` (`Id`, `Name`)
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `Category` VALUES (1, 'Tủ lạnh'), (2, 'Tivi');

-- Bảng sản phẩm
DROP TABLE IF EXISTS `Products`;
CREATE TABLE `Products` (
  `Id` int NOT NULL,
  `Name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `CategoryId` int DEFAULT NULL,
  `Price` decimal(10, 2) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  INDEX `idx_category_name_price` (`CategoryId`, `Name`, `Price`)
) ENGINE=InnoDB CHARACTER SET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `Products` VALUES 
(1, 'Tủ lạnh Changhong', 1, 22.00),
(2, 'Tivi Hisense', 2, 11.00),
(3, 'Máy tính Great Wall', 3, 1230.00);

SET FOREIGN_KEY_CHECKS = 1;

Khi chưa có chỉ mục phù hợp, các truy vấn sẽ thực hiện quét toàn bộ bảng (full table scan), dẫn đến hiệu năng kém.

Để tận dụng chỉ mục phủ (covering index), nên tránh dùng SELECT *. Thay vào đó, chỉ chọn những cột cần thiết — đặc biệt là những cột đã được bao phủ bởi chỉ mục. Ví dụ:

SELECT Price FROM Products WHERE CategoryId = 1;

Nếu chỉ mục (CategoryId, Name, Price) tồn tại, truy vấn trên có thể được phục vụ hoàn toàn từ chỉ mục mà không cần truy cập lại bảng gốc (không cần "back to table lookup").

Với các truy vấn có điều kiện OR, hãy đảm bảo tất cả các cột tham gia điều kiện đều nằm trong chỉ mục để duy trì khả năng phủ chỉ mục.

Lưu ý về truy vấn LIKE:

• LIKE '%abc' hoặc LIKE '%abc%': không sử dụng được chỉ mục → full scan.
• LIKE 'abc%': có thể sử dụng chỉ mục nhờ nguyên tắc tiền tố trái (leftmost prefix).

Khi thực hiện JOIN giữa nhiều bảng, thứ tự các cột trong chỉ mục liên hợp rất quan trọng. Ví dụ với truy vấn:

EXPLAIN
SELECT p.Price 
FROM Products p
INNER JOIN Category c ON p.CategoryId = c.Id;

Nếu chỉ mục trên bảng Products được định nghĩa là (Name, CategoryId, Price), thì cột CategoryId không nằm ở vị trí đầu tiên → không thể sử dụng hiệu quả cho điều kiện JOIN.

Tuy nhiên, nếu đổi thành (CategoryId, Name, Price), chỉ mục sẽ hỗ trợ tốt cho phép JOIN và truy vấn giá, nhờ CategoryId đứng đầu.

Một số nguyên tắc khi thiết kế chỉ mục:

• Luôn chỉ định rõ tên cột trong SELECT để tận dụng covering index.
• Trong chỉ mục liên hợp, đặt các cột dùng trong WHERE, JOIN, hoặc ORDER BY lên trước.
• Ưu tiên các cột có tính chọn lọc cao (cardinality cao) ở đầu chỉ mục.
• Với điều kiện OR, cân nhắc tạo chỉ mục riêng cho từng nhánh hoặc đảm bảo tất cả cột đều nằm trong cùng một chỉ mục.