Thứ tự của chỉ mục là: `ten`, `tuoi`, `chucvu`
VD1
EXPLAIN SELECT * FROM nhanvien WHERE ten = 'LêMinh' AND chucvu = 'dev' ORDER BY tuoi;
Phân tích:
Chỉ sử dụng trường `ten` từ chỉ mục聯合 để thực hiện tìm kiếm bằng giá trị cụ thể (như được thấy qua `key_len = 74` do độ dài của trường `ten` là 74). Trên cơ sở này, sử dụng `tuoi` để sắp xếp. Tuy nhiên, không sử dụng được chỉ mục phủ kín do có sự ngắt quãng trong quá trình tìm kiếm.
VD2
EXPLAIN SELECT * FROM nhanvien WHERE ten = 'LêMinh' ORDER BY chucvu;
Phân tích:
- Qua `type`, `key`, `key_len = 74` có thể thấy rằng chỉ mục được sử dụng là trường `ten`.
- Qua `Extra: Using filesort` có thể thấy rằng MySQL đã sử dụng phương pháp sắp xếp bên ngoài do trường `chucvu` không tuân thủ nguyên tắc "tối ưu nhất từ bên trái".
VD3
EXPLAIN SELECT * FROM nhanvien WHERE ten = 'LêMinh' ORDER BY tuoi, chucvu;
Phân tích:
Chỉ sử dụng chỉ mục `ten` cho điều kiện tìm kiếm, các trường `tuoi` và `chucvu` được sử dụng cho sắp xếp nhưng không cần dùng đến `Using filesort`.
VD4
EXPLAIN SELECT * FROM nhanvien WHERE ten = 'LêMinh' ORDER BY chucvu, tuoi;
Phân tích:
Trong trường hợp này, mặc dù cấu trúc của bảng và truy vấn tương tự như VD3, nhưng MySQL đã sử dụng `Using filesort` do trường `tuoi` và `chucvu` trong mệnh đề `ORDER BY` không tuân thủ thứ tự của chỉ mục.
VD5
EXPLAIN SELECT * FROM nhanvien WHERE ten = 'LêMinh' AND tuoi = 18 ORDER BY chucvu, tuoi;
Phân tích cho phiên bản MySQL 8:
- Sử dụng chỉ mục cho trường `ten` và `tuoi` (độ dài `key_len = 78`).
- Do không có kết quả trả về, không có thông tin thêm (`Extra`).
Phân tích cho phiên bản MySQL 5.7:
- Không sử dụng `Using filesort` do `tuoi` được sử dụng như một hằng số trong quá trình sắp xếp.
VD6
EXPLAIN SELECT * FROM nhanvien WHERE ten = 'zhuge' ORDER BY tuoi ASC, chucvu DESC;
Phân tích:
- Chỉ sử dụng trường `ten` từ chỉ mục聯合 để thực hiện tìm kiếm.
- Do trường `tuoi` được sắp xếp theo thứ tự tăng dần (`ASC`), không phù hợp với cấu trúc của chỉ mục, nên MySQL đã sử dụng phương pháp sắp xếp bên ngoài (`Using filesort`).
VD7
EXPLAIN SELECT * FROM nhanvien WHERE ten IN ('LêMinh','zhuge') ORDER BY tuoi, chucvu;
Phân tích cho phiên bản MySQL 8:
- Chỉ mục được sử dụng cho trường `ten`.
- Do trường `tuoi` và `chucvu` không tuân thủ nguyên tắc "tối ưu nhất từ bên trái", nên MySQL đã sử dụng phương pháp sắp xếp bên ngoài (`Using filesort`).
VD8
(Có thể tập trung vào việc chọn kế hoạch thực thi khác nhau tùy thuộc vào điều kiện phạm vi và kết quả trả về)
EXPLAIN SELECT * FROM nhanvien WHERE ten > 'a' ORDER BY ten;
EXPLAIN SELECT * FROM nhanvien WHERE ten > 'zzz' ORDER BY ten;
Phân tích:
- Đối với `ten > 'a'`: Sản sinh kết quả trả về lớn, nên MySQL không thể sử dụng chỉ mục để sắp xếp và đã sử dụng phương pháp sắp xếp bên ngoài (`Using filesort`).
- Đối với `ten > 'zzz'`: Sản sinh kết quả trả về nhỏ, nên MySQL đã sử dụng chỉ mục và phương pháp `Using index condition` để sắp xếp.
Optimizing `ten > 'a'`:
EXPLAIN SELECT ten, tuoi, chucvu FROM nhanvien WHERE ten > 'a' ORDER BY ten;
Phân tích:
MySQL đã sử dụng chỉ mục để sắp xếp do đã chọn các trường từ chỉ mục聯合.
Tóm tắt tối ưu hóa:
- MySQL hỗ trợ hai cách sắp xếp: `filesort` và `index`. `Using index` cho phép MySQL quét chỉ mục để hoàn thành việc sắp xếp, đây là phương pháp hiệu quả hơn.
- Điều kiện để sử dụng `Using index`:
- Mệnh đề `ORDER BY` sử dụng các trường từ bên trái của chỉ mục聯合.
- Các trường trong mệnh đề `WHERE` và `ORDER BY` kết hợp với nhau để tuân thủ nguyên tắc "tối ưu nhất từ bên trái".
- Nỗ lực sử dụng chỉ mục聯合 để thực hiện việc sắp xếp và tuân thủ nguyên tắc "tối ưu nhất từ bên trái".
- Nếu các trường trong mệnh đề `ORDER BY` không nằm trong chỉ mục聯合, MySQL sẽ phải sử dụng phương pháp sắp xếp bên ngoài (`Using filesort`).
- Nếu có thể, hãy sử dụng chỉ mục phủ kín để tránh việc đọc dữ liệu từ bảng.
- Quá trình tối ưu hóa `GROUP BY` tương tự như `ORDER BY`, thực chất là sắp xếp trước sau đó mới nhóm các kết quả. Để tối ưu hóa `GROUP BY`, nếu không cần sắp xếp, có thể thêm `ORDER BY NULL` để tránh việc sắp xếp không cần thiết. Lưu ý rằng các điều kiện lọc trong `WHERE` có hiệu lực cao hơn so với `HAVING`, nên nếu có thể, hãy đặt các điều kiện lọc trong `WHERE` thay vì `HAVING`.