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. Đâ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. Rõ ràng là 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ể. Hãy xem xét ví dụ sau:
01 | CREATE TABLE tblUsers ( |
02 | user_id int(11) NOT NULL, |
05 | user_firstname varchar(40), |
06 | user_surname varchar(40), |
Và để tìm thông tin Điểm của Nguyễn Văn A (mã số 120956), bạn sẽ query như sau:
1 | SELECT user_goal FROM tblUsers WHERE user_id = ‘120956′; |
MySQL biết rằng phải tìm ở table tblUsers 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 300.000 người) để tìm thông tin về Nguyễn Văn A.
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 user_id (mã số người dùng), 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.
Nhưng trước khi sửa lại cấu trúc của table ở trên, tôi sẽ hướng dẫn bạn 1 chút về cách theo dõi kết quả “Tăng tốc MySQL” mà bạn đang làm. Hãy sử dụng lệnh EXPLAIN
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.
Xem ví dụ sau:
1 | mysql>EXPLAIN SELECT user_id,user_firstname,user_surname FROM tblUsers WHERE user_id= ‘120956‘; |
+———-+——+—————+——+———+——+————+————+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+———-+——+—————+——+———+——+————+————+
| tblUsers | ALL | NULL | NULL | NULL | NULL | 86792| where used |
+———-+——+—————+——+———+——+————+————+
Giải thích:
- table : Table nào đang liên quan đến output data
- type : Đây là thông tin quan trọng, nó cho chúng ta biết kiểu query nào nó đang sử dụng. Mức độ từ tốt 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ể sử dụng để query
- key : và Index 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”
Wow, nhìn lại câu query của chúng ta mới thật khủng khiếp. Không có Possible_keys nào được sử dụng, MySQL phải duyệt qua 86792 bản ghi mới tìm ra cái ta cần (Hãy tưởng tượng 1 Forum sẽ có đến hơn 500.000 bản ghi).
Bây giờ chúng ta sẽ thêm Index vào và query lại
1 | mysql>ALTER TABLE tblUsers ADD INDEX idx_userid(user_id); |
2 | mysql>EXPLAIN SELECT user_id,user_firstname,user_surname FROM tblUsers WHERE user_id= ‘120956‘; |
+———-+——-+—————+———+———+——-+——+——-+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+———-+——-+—————+———+———+——-+——+——-+
| tblUsers | 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 86792 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,giả sử bạn cần tìm những người có họ là Nguyễn Văn, tên là A
1 | mysql>ALTER TABLE tblUsers ADD INDEX idxSFname (user_surname, user_firstname); |
2 | mysql>EXPLAIN SELECT user_goal FROM tblUsers WHERE user_surname=’Nguyễn Văn‘ AND user_firstname=”A“; |
+——–+——+——————-+———+———+——-+——+———–+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+——–+——+——————-+———+———+——-+——+———–+
|tblUsers| ref | user_surname,user_firstname | idxSFname | 41 | const | 1 |where used |
+——–+——+——————-+———+———+——-+——+———–+
Tuy nhiên, nếu chỉ cần user_firstname
1 | mysql>EXPLAIN SELECT user_goal FROM tblUsers WHERE user_firstname=’Name’; |
+———-+——+—————+——+———+——+————+————+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+———-+——+—————+——+———+——+————+————+
| tblUsers | ALL | NULL | NULL | NULL | NULL | 86792 | where used |
+———-+——+—————+——+———+——+————+————+
thì MySQL sẽ tìm hết vì không hề có Index cho user_firstname mà chỉ có Index cho (user_surname, user_firstname).
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âu trả lời là Có! 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, user_surname và user_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
1 | mysql>ALTER TABLE tblUsers ADD INDEX(user_surname(20),user_firstname(20)); |
Bây giờ thì bạn tiết kiệm được đến 50% mà vẫn đảm bảo được tốc độ rồi đó (trừ phi bạn làm Index quá ngắn). Có thể bạn nói đĩa cứng server tôi “vô tư” nhưng hãy nhớ rằng “Nhỏ hơn là nhanh hơn”.
Đ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ó 20.000 tên tác giả và 400.000 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;
Do đó 2 câu lệnh trên bạn nên chạy 1 cách thường xuyên để bảo đảm tối ưu hoá Database của mình.
Other Recommended Posts on
MySQL
