Hướng dẫn lập Index cho trường trong CSDL MySQL

Hướng dẫn lập Index cho trường trong CSDL MySQL

Một ngày nào đó, bạn nhận ra website của mình có dấu hiệu chậm đi. Có thể là do đường truyền Internet, nhưng còn một nguyên nhân khác đó là dữ liệu của website bạn phình to lên, dẫn đến máy chủ phải tính toán nhiều hơn dẫn đến tăng thời gian đáp ứng. Thời gian chờ đợi là yếu tố quyết định tới khách có ở lại với website của bạn hay không, vì vậy tốc độ đáp ứng của website là một vấn đề khiến các webmaster luôn quan tâm.

Điều này thường xuyên xảy ra với các website có dữ liệu lớn như Forum, Tin tức, Thương mại điện tử mỗi table của chúng có khi lớn tới hàng triệu bản ghi. 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 khi truy vấn một yêu cầu phải duyệt qua tất cả các bản ghi để có thể tìm ra được dữ liệu thích hợp. Cũng giống như khi chúng ta tìm kiếm một trang sách, nếu quyển sách đó mỏng thì sẽ nhanh chóng tìm ra được, nhưng nếu quyển sách dày thì thời gian tìm kiếm sẽ mất lâu hơn.

Hướng dẫn lập Index cho trường trong MySQL

Hướng dẫn lập Index cho trường trong MySQL

Ngoài ra, việc database quá tải còn dẫn đến nhiều thiệt hại không mong muốn khác như:

  • Hàng đợi (queue) dài ra khiến không đáp ứng kịp thời các request mới
  • File Logs bị tăng kích thước, chiếm hết không gian đĩa khi có request sẽ bị từ chối dẫn đến thông báo thường gặp “To many connections”

Những vấn đề trên thông thường bắt nguồn từ khâu thiết kế database, định nghĩa dữ liệu và không sử dụng Index hợp lý. Nếu các bạn nghiên cứu kỹ và khắc phục các vấn đề trên thì tốc độ truy vấn của database sẽ nhanh chóng lên đáng kể.

Nguyên nhân gây ra xử lý truy vấn chậm chạp?

 Và để tìm kiếm thông tin email tài khoản: ktweb (có mã userId = 12599) ta sẽ có câu truy vấn như sau:

MySQL sẽ biết rằng cần phải tìm kiếm ở table: accounts nhưng nó sẽ không biết bắt nguồn từ đâu, có bao nhiêu kết quả được trả về. Nó sẽ phải thực hiện duyệt qua hơn 12 nghìn bản ghi để có thể tìm thấy bản ghi thứ 12599 trả về cho bạn. Hãy tưởng tượng có hơn 20 triệu bản ghi, CPU của bạn chỉ có khả năng xử lý 1 triệu phép tính/s thì sẽ mất tới 20s để có thể hoàn thành và trả kết quả. Nhưng các bạn biết đó nếu mất 20s mới có thể phản hồi website thì khách sẽ ngay lập tức bỏ đi phải không nào?

Index trong MySQL là gì?

Index là một tập tin riêng biệt được lưu trữ tại máy chủ, nó chỉ lập chỉ mục cho các trường dữ liệu mà bạn muốn. Giả sử khi bạn lập chỉ mục cho trường user_id thì MySQL sẽ dễ dàng tìm ra được email của userId thứ 12599 dựa trên một số thuật toán tìm kiếm nhị phân, …  Trở lại về ví dụ quyển sách, khi chúng ta tìm kiếm thường tìm đến phần “mục lục” để tìm kiếm nhanh tới vị trí mong muốn thì trong MySQL cũng như vậy.

Hướng dẫn tối ưu MySQL

Trong MySQL có một câu lệnh có cú pháp như sau:

 Câu lệnh này có tác dụng kiểm tra các truy vấn của bạn được thực hiện như thế nào, điều gì đang xảy ra, … Ví dụ như sau:

Câu lệnh EXPLAIN

Mình sẽ giải thích cho các bạn các thông số mà câu lệnh EXPLAIN trả về như sau:

  • select_type: Kiểu select
  • table: Các bảng dữ liệu liên quan trong quá trình truy vấn
  • type: Đây là một thông tin quan trọng để chẩn đoán tốc độ câu lệnh. Mức độ từ nhanh nhất đến chậm nhất như sau: system, const, eq_ref, ref, range, index, all
  • possible_keys: Đưa ra những index có thể được lựa chọn để truy vấn
  • key: index nào đang được sử dụng cho truy vấn
  • key_len: Độ dài của từng mục trong index
  • ref: Cột nào đang được sử dụng
  • rows: Số hàng (bản ghi) mà MySQL dự đoán phải tìm qua
  • Extra: Các thông tin phụ mở rộng, thật là một điều không tốt nếu có chứa các cụm từ như: “using temporary”, “using filesort”

Như các bạn có thể thấy không có possible_keys (index) nào được tham gia vào quá trình truy vấn. MySQL cần phải thực hiện duyệt ra hơn 12 nghìn bản ghi mới có thể tìm thấy bản ghi phù hợp. Thật là khủng khiếp nếu database của bạn lên tới hàng triệu bản ghi phải không nào, rất có hại cho ổ cứng, và mất thời gian làm việc cho CPU.

Bây giờ mình sẽ thực hiện add index cho trường userId sau đó tiến hành explain lại truy vấn để xem kết quả nhé:

Câu lệnh Add Index

Thực hiện lại truy vấn để xem kết quả

 

Add Index và Explain lại truy vấn

Add Index và Explain lại truy vấn

Các bạn thấy đó kiểu truy vấn const được lựa chọn tốc độ chỉ đứng sau system, Index userId được đưa vào sử dụng. MySQL chỉ cần thực hiện duyệt một bản ghi để đưa ra kết quả. Đi một bước thay vì hơn 12 nghìn bước để tới đích tất nhiên là có sự khác nhau rõ rệt rồi phải không nào?

Lời kết

Bất kỳ khi có sự thay đổi nào về cách thức truy vấn, bạn cần phải thực hiện tạo lập lại index để sao cho câu truy vấn của mình tận dụng được các index hiện có một cách tối ưu nhất. Đồng thời những câu lệnh: UPDATE và DELETE sau khi thực hiện thường để lại những khoảng trống vô nghĩa cho bảng của bạn. Vì vậy sau một quá trình xử lý bạn nên cần thường xuyên sử dụng lệnh: OPTIMIZE TableName; để thực hiện tối ưu lại table của mình.