Khi nào và tại sao phải dùng Index trong Mysql

Một ngày kia bạn nhận ra rằng website của bạn 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 chậm.

Khi số lượng record tăng lên thì thời gian tìm kiếm của bạn sẽ tăng đáng kể.
“Too many connections” không phải là hiếm gặp trong các website trên Internet.

Hãy xem xét ví dụ sau:

CREATE TABLE employee (
employee_number char(10) NOT NULL,
firstname varchar(40),
surname varchar(40),
address text,
tel_no varchar(25),
salary int(11),
overtime_rate int(10) NOT NULL
);

Và để tìm thông tin Lương của Nguyễn Nam (mã số 101802) , bạn sẽ query như sau:
SELECT salary FROM employee WHERE employee_number = ‘101832′;

MySQL biết rằng phải tìm ở table Employee 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 Hơn 300000 người) để tìm thông tin về Nguyễn Nam.

Một trong những cách để giải quyết vấn đề là dùng Index cho table

Làm sao để quyết định Index cho những field nào hay làm index cho tất cả các field

===> Trả lời

- Index những field mà bạn thường xuyên dùng để làm tiêu chí search( ý nói ở đây là trong phần điều kiện where)
- Nên hạn chế index những field không cần thiết và thường xuyên bị update... vì DBMS cần thời gian để cập nhật cho file index.

Cách theo dõi "công việc tăng tốc" mà ta đang làm

Cú pháp: EXPLAIN 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.

mysql>> EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number= ‘10875′;
+———-+——-+—————+———+———+——-+——+——-+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+———-+——-+—————+———+———+——-+——+——-+
| employee | const | PRIMARY | PRIMARY | 10 | const | 1 | |
+———-+——-+—————+———+———+——-+——+——-+
Tốt hơn nhiều rồi , kiểu TYPE = Const có nghĩa rằng MYSQL hiểu ra chỉ có 1 hàng đúng với ý ta, và thể hiện qua cột Rows = 1 , kiểu key= PRIMARY được sử dụng và chiều dài key_len là 10.Chỉ tìm 1 hàng tất nhiên rằng tốt hơn nhiều so với tìm N hàng.

Vậy câu hỏi đặt ra là , nếu tôi muốn thêm Index cho những cột mà có thể có nhiều hơn 1 kết quả khi query thì sao? Vẫn add index như bình thường

Nhưng nên lưu ý vấn đề sau:
mysql>> ALTER TABLE employee ADD INDEX(surname,firstname);
mysql>> EXPLAIN SELECT overtime_rate FROM employee WHERE surname=’Nguyễn’ and firstname=”Nam”;
+——–+——+——————-+———+———+——-+——+———–+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+——–+——+——————-+———+———+——-+——+———–+
|employee| ref | surname,surname_2 | surname | 41 | const | 1 |where used |
+——–+——+——————-+———+———+——-+——+———–+

Tuy nhiên , nếu chỉ cần Firstname

mysql>> EXPLAIN SELECT overtime_rate FROM employee WHERE firstname=’Name’;
+———-+——+—————+——+———+——+——+————+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+———-+——+—————+——+———+——+——+————+
| employee | ALL | NULL | NULL | NULL | NULL | 9475| where used |
+———-+——+—————+——+———+——+——+————+
thì MySQL sẽ tìm hết vì không hề có Index cho Firstname mà chỉ có Index cho (Surname,Firstname)

Nếu chỉ muốn Index cho 1 phần??
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 , Surname và Firstname chỉ maximum là 40 chars , nếu chúng ta index nó , chúng ta tạo ra mỗi record đến 80 chars . Có thể tiết kiệm bằng cách sau

mysql>> ALTER TABLE employee ADD INDEX(surname(20),firstname(20));


ĐIỀU KÌ DIỆU VỚI OPTIMIZE VÀ ANALYZE

“Ma thuật” của MySQL là biết cách chọn khoá (key) nào để query(nếu có). Quá trình này gọi là “query optimizer”, nó sẽ “liếc” qua những Index đang có để quyết định sẽ dùng Index nào để tìm. Hãy tưởng tượng bạn đang tìm 1 dĩa CD của “Maria Carrey” có tên là “I Love You” , có nghĩa là có 2 Indexes ở đây , 1 cho tên tác giả và 1 cho tên CD. Bạn nhận thấy rằng danh mục có 20000 tên tác giả và 400000 tên Album. Một cách đơn giản ,bạn sẽ tìm theo tên tác giả. Khi có được , bạn lại thấy rằng “Maria Carrey” có 50 CDs và CD “I Love You” bắt đầu bằng chữ I. Đơn giản và dễ dàng tìm ra cái mình muốn phải không ? MySQL cũng vậy nhưng …bạn phải chỉ cho nó bằng cách:

ANALYZE TABLE tablename;

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

OPTIMIZE tablename;