Chuyển tới nội dung chính

Bài 10: View, Index & Tối Ưu Truy Vấn

Giai đoạn 3 – Nâng Cao · Mục tiêu: Tạo View để đơn giản hoá truy vấn, sử dụng Index để tăng tốc, và biết cách phân tích hiệu năng truy vấn.

1. VIEW – Bảng ảo​

View là gì?​

View là một câu SELECT được lưu lại dưới dạng bảng ảo. Khi truy vấn View, MySQL sẽ chạy câu SELECT gốc.

Tạo View – CREATE VIEW​

-- View hiển thị đơn hàng kèm tên khách hàng
CREATE VIEW vw_DonHangChiTiet AS
SELECT 
    dh.MaDH,
    kh.HoTen AS KhachHang,
    kh.DienThoai,
    dh.SanPham,
    dh.TongTien,
    dh.NgayDat,
    dh.TrangThai
FROM DonHang AS dh
INNER JOIN KhachHang AS kh ON dh.MaKH = kh.MaKH;

Sử dụng View​

-- Truy vấn View như bảng thường
SELECT * FROM vw_DonHangChiTiet;

-- Lọc trên View
SELECT KhachHang, TongTien 
FROM vw_DonHangChiTiet 
WHERE TrangThai = 'Hoàn thành';

-- JOIN View với bảng khác
SELECT v.*, nv.HoTen AS NhanVienXuLy
FROM vw_DonHangChiTiet AS v
INNER JOIN NhanVien AS nv ON v.MaNV = nv.MaNV;

Các View hữu ích​

-- View thống kê doanh thu theo tháng
CREATE VIEW vw_DoanhThuThang AS
SELECT 
    YEAR(NgayDat) AS Nam,
    MONTH(NgayDat) AS Thang,
    COUNT(*) AS SoDonHang,
    SUM(TongTien) AS TongDoanhThu,
    ROUND(AVG(TongTien), 0) AS TBMoiDon
FROM DonHang
WHERE TrangThai = 'Hoàn thành'
GROUP BY YEAR(NgayDat), MONTH(NgayDat);

-- View nhân viên kèm phòng ban
CREATE VIEW vw_NhanVienPhongBan AS
SELECT 
    nv.MaNV,
    nv.HoTen,
    pb.TenPB AS PhongBan,
    nv.ChucVu,
    nv.Luong
FROM NhanVien AS nv
LEFT JOIN PhongBan AS pb ON nv.MaPB = pb.MaPB;

-- View sản phẩm sắp hết hàng
CREATE VIEW vw_SapHetHang AS
SELECT TenSP, DanhMuc, SoLuongTon
FROM SanPham
WHERE SoLuongTon <= 10;

Sửa và xoá View​

-- Sửa View
CREATE OR REPLACE VIEW vw_SapHetHang AS
SELECT TenSP, DanhMuc, SoLuongTon, Gia
FROM SanPham
WHERE SoLuongTon <= 5;

-- Xoá View
DROP VIEW IF EXISTS vw_SapHetHang;

Lợi ích của View​

Lợi íchMô tả
Đơn giản hoáẨn câu JOIN phức tạp, chỉ cần SELECT * FROM view
Bảo mậtChỉ cho phép truy cập View (ẩn cột nhạy cảm như lương, CMND)
Nhất quánMọi người dùng cùng 1 logic truy vấn
Tái sử dụngKhông cần viết lại câu SQL phức tạp

:::info View không lưu dữ liệu View chỉ lưu câu SELECT, không lưu dữ liệu. Mỗi lần truy vấn View, MySQL sẽ chạy lại câu SELECT gốc. :::

2. INDEX – Tăng tốc truy vấn​

Index là gì?​

Index (chỉ mục) giống như mục lục của sách — giúp MySQL tìm dữ liệu nhanh hơn thay vì quét toàn bộ bảng.

Tạo Index​

-- Index trên 1 cột
CREATE INDEX idx_sanpham_danhmuc ON SanPham(DanhMuc);

-- Index trên nhiều cột (composite index)
CREATE INDEX idx_donhang_khach_ngay ON DonHang(MaKH, NgayDat);

-- Index duy nhất (giá trị không trùng)
CREATE UNIQUE INDEX idx_khachhang_email ON KhachHang(Email);

Khi nào nên tạo Index?​

Nên tạo IndexKhông nên tạo Index
Cột dùng trong WHERE thường xuyênBảng nhỏ (< 1000 dòng)
Cột dùng trong JOINCột ít giá trị khác nhau (VD: Giới tính)
Cột dùng trong ORDER BYBảng thường xuyên INSERT/UPDATE
Khoá chính, khoá ngoạiCột ít khi dùng trong truy vấn

Xem Index của bảng​

SHOW INDEX FROM SanPham;

Xoá Index​

DROP INDEX idx_sanpham_danhmuc ON SanPham;

:::warning Index có chi phí

  • Index giúp đọc nhanh hơn nhưng làm ghi chậm hơn (INSERT, UPDATE, DELETE).
  • Mỗi index chiếm thêm dung lượng ổ đĩa.
  • Chỉ tạo index cho cột thực sự cần thiết. :::

3. EXPLAIN – Phân tích truy vấn​

Sử dụng EXPLAIN​

EXPLAIN SELECT * FROM SanPham WHERE DanhMuc = 'Điện thoại';

Kết quả mẫu:

+----+-------+------+---------+------+------+-------------+
| id | type  | table| key     | rows | Extra             |
+----+-------+------+---------+------+-------------------+
| 1  | ref   | SP   | idx_dm  | 15   | Using index cond  |  ← Dùng index 
+----+-------+------+---------+------+-------------------+

Các cột quan trọng trong EXPLAIN​

CộtÝ nghĩaGiá trị tốt
typeKiểu truy cậpconst > ref > range > ALL
keyIndex được sử dụngCó tên index (không NULL)
rowsSố dòng MySQL phải quétCàng nhỏ càng tốt
ExtraThông tin bổ sungUsing index là tốt nhất

So sánh có và không có Index​

-- Trước khi tạo index
EXPLAIN SELECT * FROM DonHang WHERE NgayDat = '2024-12-01';
-- type: ALL, rows: 100000 (quét toàn bảng)

-- Tạo index
CREATE INDEX idx_donhang_ngay ON DonHang(NgayDat);

-- Sau khi tạo index
EXPLAIN SELECT * FROM DonHang WHERE NgayDat = '2024-12-01';
-- type: ref, rows: 50 (chỉ quét 50 dòng)

4. Mẹo tối ưu truy vấn​

4.1. Chỉ SELECT cột cần thiết​

--  Chậm: Lấy tất cả cột
SELECT * FROM SanPham;

--  Nhanh: Chỉ lấy cột cần
SELECT TenSP, Gia FROM SanPham;

4.2. Dùng LIMIT khi có thể​

--  Lấy toàn bộ 1 triệu dòng
SELECT * FROM LogHeThong ORDER BY ThoiGian DESC;

--  Chỉ lấy 100 dòng mới nhất
SELECT * FROM LogHeThong ORDER BY ThoiGian DESC LIMIT 100;

4.3. Tránh dùng hàm trên cột trong WHERE​

--  Chậm: MySQL không dùng được index
WHERE YEAR(NgayDat) = 2024

--  Nhanh: Giữ cột nguyên
WHERE NgayDat BETWEEN '2024-01-01' AND '2024-12-31'

4.4. Dùng EXISTS thay vì IN với bảng lớn​

--  Chậm với bảng lớn
WHERE MaKH IN (SELECT MaKH FROM DonHang)

--  Nhanh hơn
WHERE EXISTS (SELECT 1 FROM DonHang WHERE DonHang.MaKH = KhachHang.MaKH)

4.5. Tránh SELECT DISTINCT không cần thiết​

--  DISTINCT tốn tài nguyên
SELECT DISTINCT KhachHang FROM DonHang;

--  Nếu có thể, dùng GROUP BY
SELECT KhachHang FROM DonHang GROUP BY KhachHang;

5. Bảng tóm tắt tối ưu​

Kỹ thuậtTrước (Chậm)Sau (Nhanh)
Chọn cộtSELECT *SELECT col1, col2
Giới hạnKhông có LIMITLIMIT 100
IndexKhông có indexCREATE INDEX trên cột WHERE
Hàm trong WHEREWHERE YEAR(col) = 2024WHERE col BETWEEN ...
Subquery lớnIN (SELECT ...)EXISTS (SELECT ...)

Bài tập thực hành​

Bài 1: Tạo View​

  1. Tạo View vw_NhanVienLuongCao hiển thị nhân viên có lương > 15 triệu.
  2. Tạo View vw_ThongKePhongBan thống kê số nhân viên, tổng lương, lương TB theo phòng ban.
  3. Tạo View vw_DuAnDangChay hiển thị dự án chưa kết thúc (NgayKetThuc IS NULL hoặc > NOW()).
  4. Truy vấn dữ liệu từ các View trên.

Bài 2: Tạo Index​

  1. Tạo index trên cột NgayDat của bảng DonHang.
  2. Tạo composite index trên (MaKH, NgayDat).
  3. Dùng EXPLAIN so sánh truy vấn trước và sau khi tạo index.

Bài 3: Tối ưu truy vấn​

Tối ưu các câu truy vấn sau:

-- Câu 1
SELECT * FROM DonHang WHERE MONTH(NgayDat) = 12;

-- Câu 2
SELECT * FROM NhanVien WHERE UPPER(HoTen) = 'NGUYỄN VĂN AN';

-- Câu 3
SELECT DISTINCT * FROM DonHang 
INNER JOIN KhachHang ON DonHang.MaKH = KhachHang.MaKH;

Quay lại: Roadmap · Bài tiếp: Bài 11 – Hàm Chuỗi, Ngày & Nâng Cao