Tăng tốc MySQL

MySQL là 1 Database kết hợp chặt chẽ với PHP trong rất nhiều ứng dụng web động. Nhờ tính năng mã nguồn mở và nhỏ gọn , MySQL hiện đang là hệ Database nhanh nhất cho các ứng dụng thông thường. Tuy nhiên theo thời gian , các ứng dụng web sử dụng MySQL ngày càng "phình" to ra và Website của bạn ngày càng chạy chậm đi. Đó có phải là điều không thể tránh khỏi ? Hãy tham khảo bài viết sau đây để tăng tốc MySQL và website của bạn lên gấp nhiều lần.

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 (Xem phần phụ lục để biết cách xác định nguyên nhân gây 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. 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 "Mục Lục" (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: CODE 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: CODE 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ục Lục" (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 employee_number char (mã số công nhâ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. 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: CODE 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: CODE MySQL>EXPLAIN SELECT employee_number,firstname,surname FROM employee WHERE employee_number= '10875'; +----------+------+---------------+------+---------+------+------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +----------+------+---------------+------+---------+------+------+------------+ | employee | ALL | NULL | NULL | NULL | NULL | 9475 | 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 9475 hàng mới tìm ra cái ta cần (Hãy tưởng tượng 1 Forum sẽ có đến hơn 200 000 hàng). Bây giờ chúng ta sẽ thêm Index vào và query lại CODE MySQL>ALTER TABLE employee ADD INDEX(employee_number char); 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 9475 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 , tên là Nam CODE 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 CODE 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) 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ó  ! 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 CODE MySQL>ALTER TABLE employee ADD INDEX(surname(20),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ó 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: CODE 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 CODE 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.

Thêm vào trang Google +