Nhiều lập trình viên thường thắc mắc: Tại sao câu lệnh SQL của tôi đã sử dụng chỉ mục (index), nhưng vẫn bị ghi vào danh sách truy vấn chậm? Hôm nay, chúng ta sẽ cùng phân tích vấn đề này và thảo luận về mối quan hệ giữa chỉ mục và truy vấn chậm.
Một lưu ý nhỏ, việc sử dụng ORM (Object-Relational Mapping) trong đội ngũ cần được cân nhắc kỹ lưỡng. Có thể tham khảo bài viết <Ưu và nhược điểm của ORM>. Nên tận dụng lợi thế của ORM trong lập trình hướng đối tượng và thao tác ghi, đồng thời tránh các vấn đề có thể phát sinh với truy vấn kết hợp (trừ khi bạn thành thạo LINQ). ORM che giấu quá nhiều kiến thức tầng cơ sở dữ liệu, điều này không tốt cho lập trình viên, đặc biệt là các đội có yêu cầu về hiệu suất cao nhưng chưa hiểu sâu về ORM.
Phân tích tình huống
Để minh họa, tôi tạo bảng như sau:
CREATE TABLE `nhan_vien`(
`id` int(11) NOT NULL,
`tuoi` int(11) DEFAULT NULL,
`ten` varchar(50) DEFAULT NULL,
PRIMARY KEY(`id`),
KEY `idx_tuoi`(`tuoi`),
KEY `idx_ten`(`ten`)
) ENGINE=InnoDB;
Bảng này có ba trường, trong đó id là chỉ mục khóa chính, và tuoi, ten là các chỉ mục thông thường.
Đầu tiên, SQL quyết định một câu lệnh có phải là truy vấn chậm dựa trên thời gian thực thi. Nó so sánh thời gian thực thi của câu lệnh với tham số hệ thống long_query_time. Nếu thời gian thực thi lớn hơn giá trị này, câu lệnh sẽ được ghi vào nhật ký truy vấn chậm. Giá trị mặc định của tham số này là 10 giây. Tuy nhiên, trong môi trường sản xuất, chúng ta thường đặt giá trị này là 1 giây, đối với một số nghiệp vụ nhạy cảm, có thể đặt giá trị nhỏ hơn 1 giây.
Để xem liệu một câu lệnh có sử dụng chỉ mục của bảng trong quá trình thực thi, ta có thể xem kết quả xuất ra khi lệnh explain cho câu lệnh đó, giá trị của trường KEY không phải là NULL.
Xem xét explain select * from nhan_vien; trường KEY cho kết quả NULL
Xem xét explain select * from nhan_vien where id=10; trường KEY cho kết PRIMARY, tức là đã sử dụng chỉ mục khóa chính
Xem xét explain select tuoi from nhan_vien; trường KEY cho kết idx_tuoi, tức là đã sử dụng chỉ mục tuoi.
Mặc dù hai câu lệnh sau này có KEY không phải NULL, nhưng câu lệnh cuối cùng thực tế đã quét toàn bộ cây chỉ mục tuoi.
Giả sử bảng này có 1 triệu bản ghi, câu lệnh thứ hai vẫn có thể thực thi nhanh, nhưng câu lệnh thứ ba chắc chắn sẽ rất chậm. Trong trường hợp cực đoan hơn, ví dụ như CPU của cơ sở dữ liệu bị quá tải, thì thời gian thực thi của câu lệnh thứ hai cũng có thể vượt quá long_query_time và bị ghi vào nhật ký truy vấn chậm.
Vì vậy, chúng ta có thể rút ra kết luận: Việc sử dụng chỉ mục và việc vào danh sách truy vấn chậm không có mối liên hệ tất yếu. Việc sử dụng chỉ mục chỉ thể hiện quá trình thực thi của một câu lệnh SQL, trong việc có vào danh sách truy vấn chậm hay không được quyết định bởi thời gian thực thi, và thời gian thực thi này có thể bị ảnh hưởng bởi nhiều yếu tố bên ngoài. Nói cách khác, ngay cả khi đã sử dụng chỉ mục, câu lệnh của bạn vẫn có thể thực thi rất chậm.
Hạn chế của quét toàn bộ chỉ mục
Nếu nhìn sâu hơn vào vấn đề này, thực chất còn một vấn đề cần làm rõ: Điều gì được gọi là đã sử dụng chỉ mục.
Chúng ta đều biết InnoDB là bảng có tổ chức chỉ mục, tất cả dữ liệu đều được lưu trên cây chỉ mục. Ví dụ với bảng nhan_vien ở trên, bảng này chứa hai chỉ mục: một chỉ mục khóa chính và một chỉ mục thông thường. Trong InnoDB, dữ liệu được đặt trong chỉ mục khóa chính.
Nếu xét về mặt logic, tất cả các truy vấn trên bảng InnoDB đều đã sử dụng ít nhất một chỉ mục. Vì vậy, bây giờ tôi hỏi bạn, nếu bạn thực hiện select * from nhan_vien where id>0, bạn nghĩ câu lệnh này đã sử dụng chỉ mục chưa?
Kết quả xuất hiện của explain cho câu lệnh này cho thấy PRIMARY. Thực tế về dữ liệu, bạn biết rằng câu lệnh này chắc chắn đã quét toàn bộ. Tuy nhiên, bộ tối ưu hóa cho rằng, trong quá trình thực thi câu lệnh, cần phải dựa vào chỉ mục khóa chính để định vị giá trị đầu tiên thỏa mãn ID>0, điều này cũng được coi là đã sử dụng chỉ mục.
Vì vậy, ngay cả khi kết quả explain ghi KEY không phải NULL, thực tế có thể vẫn là quét toàn bộ bảng. Do đó, trong InnoDB chỉ có một trường hợp được gọi là không sử dụng chỉ mục, đó là bắt đầu từ nút lá bên trái nhất của chỉ mục khóa chính và quét toàn bộ cây chỉ mục sang phải.
Nói cách khác, không sử dụng chỉ mục không phải là một mô tả chính xác:
Bạn có thể dùng quét toàn bảng để mô tả một truy vấn đã duyệt qua toàn bộ cây chỉ mục khóa chính; Hoặc dùng quét toàn chỉ mục, để giải thích như câu lệnh select tuoi from nhan_vien; nó đã quét toàn bộ cây chỉ mục thông thường; Còn câu lệnh select * from nhan_vien where id=10 mới là câu lệnh chúng ta thường nói là đã sử dụng chỉ mục. Nó có nghĩa là chúng ta đã sử dụng chức năng tìm kiếm nhanh của chỉ mục và giảm hiệu quả số dòng quét.
Khả năng lọc của chỉ mục phải đủ tốt
Dựa trên phân tích trên, chúng ta biết quét toàn bộ chỉ mục làm cho truy vấn chậm hơn, tiếp theo chúng ta sẽ nói về khả năng lọc của chỉ mục.
Giả sử bạn đang quản lý một bảng ghi thông tin cơ bản của 1,4 tỷ người Trung Quốc. Bây giờ cần tìm tất cả những người có độ tuổi từ 10-15 và hiển thị tên và thông tin cơ bản. Câu lệnh của bạn sẽ được viết như sau: select * from nhan_vien where tuoi between 10 and 15.
Bạn thấy ngay rằng câu lệnh này phải tạo chỉ mục trên trường tuổi, nếu không sẽ là quét toàn bộ. Nhưng bạn sẽ nhận thấy rằng sau khi tạo chỉ mục, câu lệnh này vẫn thực thi chậm, vì số dữ liệu thỏa mãn điều kiện này có thể vượt quá 100 triệu bản ghi.
Chúng ta xem xét cấu trúc bảng sau khi tạo chỉ mục:
Quy trình thực thi của câu lệnh này là:
Từ chỉ mục, dùng cây tìm kiếm để lấy bản ghi đầu tiên có tuổi bằng 10, lấy giá trị id khóa chính, theo giá trị id này đến chỉ mục khóa chính để lấy thông tin toàn hàng, làm một phần của tập kết quả trả về; Quét sang phải trên chỉ mục tuoi, lấy giá trị id tiếp theo, đến chỉ mục khóa chính lấy thông tin toàn hàng, làm một phần của tập kết quả trả về; Lặp lại các bước trên, cho đến khi gặp bản ghi đầu tiên có tuổi lớn hơn 15;
Bạn thấy rằng mặc dù câu lệnh này đã sử dụng chỉ mục, nhưng nó đã quét hơn 100 triệu dòng. Vì vậy bây giờ bạn đã biết, khi chúng ta thảo luận về việc có sử dụng chỉ mục hay không, thực chất chúng ta quan tâm đến số dòng quét.
Đối với một bảng lớn, không chỉ cần có chỉ mục, mà khả năng lọc của chỉ mục còn phải đủ tốt.
Giống như ví dụ ở trên, trường tuổi của bảng nhan_vien, khả năng lọc của nó không đủ tốt. Khi thiết kế cấu trúc bảng, chúng ta cần đảm bảo tất cả các chỉ mục đều có khả năng lọc tốt, tức là độ phân biệt cao.
Chi phí của việc truy lại bảng
Vậy nếu khả năng lọc tốt, có nghĩa là số dòng quét của truy vấn chắc chắn ít không?
Chúng ta lại xem một ví dụ khác:
Nếu câu lệnh thực thi của bạn là select * from nhan_vien where name='Trần' and age=8
Trên bảng nhan_vien có một chỉ mục kết hợp gồm tên và tuổi. Khả năng lọc của chỉ mục kết hợp này chắc chắn sẽ tốt, có thể nhanh chóng tìm thấy người đầu tiên có tên là Trần và tuổi 8 trên chỉ mục kết hợp. Tất nhiên số người như vậy không nhiều, do đó số dòng quét sang phải ít, hiệu suất truy vấn rất cao.
Nhưng khả năng lọc của truy vấn và khả năng lọc của chỉ mục không nhất thiết giống nhau. Nếu yêu cầu hiện tại của bạn là tìm tất cả những người có chữ cái đầu tiên của tên là Tr và tuổi 8, câu lệnh của bạn sẽ viết thế nào?
Rõ ràng bạn sẽ viết như sau: select * from nhan_vien where name like 'Tr%' and age=8;
Trong phiên bản MySQL 5.5 và trước đó, quy trình thực thi của câu lệnh này là:
Đầu tiên, từ chỉ mục kết hợp tìm thấy bản ghi đầu tiên có tên bắt đầu bằng chữ Tr, lấy id khóa chính, sau đó đến chỉ mục khóa chính theo id để lấy giá trị toàn hàng; Kiểm tra xem trường tuổi có bằng 8 không, nếu có thì trả về làm một hàng của tập kết quả, nếu không thì bỏ qua; Quét sang phải trên chỉ mục kết hợp, và lặp lại logic truy lại bảng và kiểm tra, cho đến khi gặp bản ghi có chữ cái đầu tiên của tên không phải là Tr trên chỉ mục kết hợp.
Chúng ta gọi hành động lấy dữ liệu toàn hàng theo id từ chỉ mục khóa chính là truy lại bảng. Bạn có thể thấy rằng trong quá trình thực thi này, bước tốn nhiều thời gian nhất chính là truy lại bảng. Giả sử cả nước có 80 triệu người có chữ cái đầu tiên của tên là Tr, thì quá trình này sẽ truy lại bảng 80 triệu lần. Khi định vị bản ghi đầu tiên, chỉ có thể sử dụng tiền tố bên trái của chỉ mục và chỉ mục kết hợp, nguyên tắc này được gọi là nguyên tắc tiền tố bên trái.
Bạn có thể thấy rằng quy trình thực thi này có số lần truy lại bảng rất nhiều, hiệu suất không tốt. Có phương pháp tối ưu nào không?
Từ phiên bản MySQL 5.6, đã giới thiệu tối ưu index condition pushdown. Chúng ta xem xét quy trình thực thi của tối ưu này:
Đầu tiên, từ chỉ mục kết hợp tìm thấy bản ghi đầu tiên có tên bắt đầu bằng chữ Tr, kiểm tra xem giá trị trường tuổi trong bản ghi chỉ mục này có bằng 8 không, nếu có thì truy lại bảng, lấy giá trị toàn hàng, làm một phần của tập kết quả trả về, nếu không thì bỏ qua; Quét sang phải trên chỉ mục kết hợp, và sau khi kiểm tra trường tuổi, thực hiện truy lại bảng khi cần, cho đến khi gặp bản ghi có chữ cái đầu tiên của tên không phải là Tr trên chỉ mục kết hợp.
Sự khác biệt giữa quá trình này và quá trình trên là, trong quá trình quét chỉ mục kết hợp, điều kiện tuổi bằng 8 được đẩy xuống toàn bộ quá trình quét, giảm số lần truy lại bảng. Giả sử trong số 80 triệu người có chữ cái đầu tiên của tên là Tr, có 1 triệu người là 8 tuổi, thì trong quá trình truy vấn này, cần quét 80 triệu lần trên chỉ mục kết hợp, nhưng chỉ cần truy lại bảng 1 triệu lần.
Cột ảo
Có thể thấy hiệu quả của tối ưu này vẫn rất tốt, nhưng tối ưu này vẫn không thoát khỏi sự hạn chế của nguyên tắc tiền tố bên trái, trên chỉ mục kết hợp vẫn cần quét 80 triệu dòng. Vậy có phương pháp tối ưu sâu hơn không?
Chúng ta có thể xem xét việc tạo chỉ mục kết hợp giữa chữ cái đầu tiên của tên và tuổi. Ở đây có thể sử dụng tính năng cột ảo được giới thiệu trong MySQL 5.7 để thực hiện. Câu lệnh SQL tương ứng để sửa đổi cấu trúc bảng:
alter table nhan_vien add name_first varchar(2) generated (left(name,1)), add index(name_first,age);
Chúng ta xem xét hiệu quả thực thi của câu lệnh SQL này:
CREATE TABLE `nhan_vien`(
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`name_first` varchar(2) GENERATED ALWAYS AS (left(`name`,1)) VIRTUAL,
KEY `idx_name_first_age`(`name_first`, `age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Trước hết, nó tạo một trường tên name_first trên bảng nhan_vien, sau đó tạo chỉ mục kết hợp trên name_first và age, và để giá trị của cột ảo này luôn bằng hai byte đầu của trường name. Cột ảo không thể chỉ định giá trị khi chèn dữ liệu, cũng không thể chủ động sửa đổi khi cập nhật, giá trị của nó sẽ được tạo tự động theo định nghĩa, và cũng sẽ tự động sửa đổi khi trường name được sửa đổi.
Có chỉ mục kết hợp mới này, khi tìm những người có chữ cái đầu tiên của tên là Tr và tuổi 8, câu lệnh SQL có thể được viết như sau: select * from nhan_vien where name_first='Tr' and age=8.
Điều này có nghĩa là quá trình thực thi của câu lệnh này chỉ cần quét 1 triệu dòng trên chỉ mục kết hợp và truy lại bảng 1 triệu lần. Bản chất của tối ưu này là chúng ta đã tạo một chỉ mục chặt chẽ hơn để tăng tốc quá trình truy vấn.
Tổng kết
Bài viết này đã giới thiệu cấu trúc cơ bản của chỉ mục và một số tư duy tối ưu hóa truy vấn. Bây giờ bạn đã biết, ngay cả khi sử dụng chỉ mục, câu lệnh vẫn có thể là truy vấn chậm. Quá trình tối ưu hóa truy vấn của chúng ta thường là quá trình giảm số dòng quét.
Truy vấn chậm có thể được phân loại thành các trường hợp sau:
Quét toàn bảng Quét toàn chỉ mục Khả năng lọc của chỉ mục không tốt Chi phí truy lại bảng thường xuyên
Đặt câu hỏi Giả sử yêu cầu nghiệp vụ là phải thống kê số lượng 1,4 tỷ người có độ tuổi từ 10-15, không thể tăng thêm yếu tố lọc, thì nên làm thế nào? (select * from nhan_vien where age between 10 and 15) Giả thống kê này phải là OLTP, hiển thị dữ liệu thống kê theo thời gian thực, thì giải pháp như thế nào?