Tối ưu hóa truy vấn với Index trong MySQL

Sử dụng index trong MySql

Index trong MySql là gì ?

Khi phát triển ứng dụng website, sau khi một thời gian bạn có thể nhận thấy website của mình đang chạy chậm đi, có thể là do đường truyền nhưng còn 1 nguyên nhân khác, đó là máy chủ server tính toán quá nhiều dẫn đến kết quả đưa ra bị chậm . Đây là điều thường thấy ở những website về Diễn Đàn (Forum), Tin Tức (Portal) và Thương mại điện tử (Ecommerce).

Khi số lượng thành viên, số lượng bài viết tăng lên, đồng nghĩa với việc Database khi truy vấn (query) 1 yêu cầu phải duyệt qua tất cả các dữ liệu hiện có để tìm ra dữ liệu thích hợp. Cũng giống như 1 quyển sách. Nếu sách là mỏng, bạn dễ dàng tìm ra thông tin mình cần. Nhưng khi sách dầy lên, thời gian tìm kiếm của bạn sẽ tăng đáng kể.

Việc Database quá tải còn dẫn đến nhiều thiệt hại khác, các hàng đợi (Queuie) dài ra, file logs lớn lên chiếm hết không gian đĩa và user khi kết nối sẽ bị từ chối. Có thể dẫn đến câu báo lỗi “Too many connections” không phải là hiếm gặp trong các website trên Internet. Những lỗi trên thông thường bắt nguồn từ khâu định nghĩa Database (define) hay không sử dụng Indexes. Khắc phục những thiếu sót trên, Database của bạn sẽ “nhẹ nhàng” và nhanh chóng đáng kể.

MySQL biết rằng phải tìm ở table doan_vien nhưng nó sẽ không biết bắt đầu từ đâu. Thậm chí nó cũng không biết trước rằng có bao nhiêu kết quả. Do đó nó sẽ duyệt qua tất cả danh sách (vd trong bảng này có 10000 bản ghi) để tìm thông tin về doan_vien có mã số DOANVIEN_00935 .

Index là 1 file riêng biệt được lưu trữ ở máy chủ và chỉ chứa những Fields mà bạn muốn nó chứa. Nếu bạn tạo 1 Index cho Field ma_doan_vien (mã số đoàn viên), MySQL sẽ dễ dàng tìm ra được mã số 1 cách nhanh chóng. Trở lại ví dụ quyển sách, khi cần tìm 1 thông tin, ta thường lật ngay tới phần “Mục Lục” và tìm từ đó để tăng tốc độ tìm. Và việc tạo ra Index này sẽ làm bạn thấy Database của bạn chạy nhanh 1 cách khác thường, khi thường xuyên phải truy vấn ở những cơ sở dữ liệu nhiều bản ghi.

Trước khi sử dụng index, ta hãy xem câu lệnh truy vấn trên chạy như thế nào . Hãy sử dụng lệnh EXPLAIN

Thêm EXPLAIN vào đầu câu query. Bằng lệnh này bạn sẽ nhận ra được với câu Query của bạn, điều gì đang xảy ra và kiểu kết hợp (Join) nào đang diễn ra bên trong.

Ở đây ta có :

possible_keys : Đưa ra những Index có thể sử dụng để query
key : vIndex nào đang được sử dụng
key_len : Chiều dài của từng mục trong Index
ref : Cột nào đang sử dụng
rows : Số hàng (rows) mà MySQL dự đoán phải tìm
extra : Thông tin phụ, thật tệ nếu tại cột này là “using temporary” hay “using filesort”

Như vậy ta có thể thấy để tìm được doan_vien này MySql phải tìm qua 9611 gần như là duyệt toàn bộ CSDL . Nếu database của bạn có 1.000.000 bản ghi thì thật là rất mất thời gian . Giảm hiệu xuất hoạt động của hệ thống .

Như bạn thấy ở đây sau khi được đánh index thì MySql chỉ cần tìm qua 1 rows là đã có được kết quả . Tốc độ truy vấn đã được tăng lên rất nhiều so với trước đây .

Khi nào thì cần Add Index ?

Bất cứ khi nào bạn thay đổi Table bạn đều cần Add Index lại, giống như khi bạn thay đổi nội dung quyển sách, bạn cần phải làm lại mục lục. Vậy hãy cân nhắc, nếu Database của bạn sử dụng INSERT hay UPDATE nhiều hơn là SELECT thì Index chỉ làm chậm thêm mà thôi.

Có thể nhanh hơn nữa không ?

Có thể ! Bạn không cần phải làm Index cho cả Field mà chỉ cần 1 phần. Giống như chi tiết Mục Lục của sách mà quá dài cũng làm bạn khá vất vả, do đó họ chỉ trích dẫn 1 tựa đề. Quay lại với table của chúng ta, ten và ho_ten_dem là dạng varchar có maximum 255 chars, nếu chúng ta tạo index của ten và ho_ten_dem, thì có thể chúng ta tạo ra mỗi record đến 500 chars . Quá dài , có thể tiết kiệm bằng cách sau

Bây giờ thì bạn tiết kiệm được rất nhiều mà vẫn đảm bảo được tốc độ rồi đó (Không nên tạo index trong trường hơp này quá ngắn vì có thể ảnh hưởng đến tốc độ tìm kiếm ).

Khi sử dụng index trong MySql thì cần chú ý những gì ?

Những lệnh DELETE và UPDATE để lại rất nhiều những khoảng trống (gaps) vô nghĩa cho table (Đặc biệt là khi bạn dùng kiểu varchar hay text/blob). Điều đó có nghĩa rằng MySQL cũng phải đọc và phân tích những thứ vô nghĩa đó khi query. Điều này được khắc phục khi bạn chạy như câu lệnh trên thì điều kiện Where này có thể sử dụng index của cột ma_doan_vien có sẵn.

Vì index giúp tăng hiệu năng của câu lệnh lên rất nhiều, việc viết code để sao cho các điều kiện tìm kiếm trở thành sargable là một mục tiêu rất quan trọng. Một nguyên tắc rất cơ bản trong SQL Server mà bạn có thể áp dụng trong rất nhiều trường hợp, đó là cột cần tìm phải đứng một mình ở một phía của biểu thức tìm kiếm, nói cách khác là không có hàm số hay phép tính toán nào áp dụng trên cột đó.

Khi nào nên đánh index trên 1 cột nào đó ?

Khi trong cột muốn đánh index có số giá trị duy nhất nhiều thì nên sử dụng , nhưng nếu số giá trị duy nhất trong column đó quá thấp thì bạn không nên dùng index đối với trường hợp này. Ví dụ trong cột này có quá nhiều giá trị trùng nhau . Số giá trị khác nhau quá ít thì index sẽ kém hiệu quả.

Tài liệu tham khảo :

https://thanhnp.wordpress.com/2012/05/26/mysql-co-nen-su-index-khi-nao-thi-nen-su-dung-index/

http://aptech.vn/kien-thuc-tin-hoc/su-dung-index-trong-sql-server-mot-cach-hieu-qua-2.html