Optimizing MySQL Queries Tips

Optimizing MySQL Queries là một chủ đề lớn trong lập trình và đặc biệt là môi trường web develop,và được coi là kiến thức mức cao cho lập trình viên,tuy nhiên lại không phải như vậy,Optimizing MySQL Queries đôi khi bắt đầu từ những điều đơn giản mà cần thiết,dưới đây là những kiến thức vô cùng đơn giản mà nếu làm tốt ta đã có thể  tránh được những vấn đề phổ biến nhất.

1 – Giảm tối đa lượng dữ liệu cần thao tác ,bớt dữ liệu thừa.

Chỉ SELECT những trường cần thiết.

Hầu hết chúng ta đều không để ý đến điều này và sử dụng query : SELECT * để lấy dữ liệu. Điều này sẽ không sao nếu số lượng dữ liệu bạn lấy  không nhiều nhưng SELECT * có thể gây chậm trên các bảng dữ liệu lớn,và đôi khi chết câu truy vấn khi lượng data lấy về quá lớn . Vì vậy, thay vì chọn tất cả mọi trường, chỉ cần chọn các trường bạn cần. Nó chiếm ít bộ nhớ trên máy chủ của bạn và do đó cho phép máy chủ cơ sở dữ liệu của bạn sử dụng bộ nhớ trống để xử lý các truy vấn và các tiến trình khác.

Sử dụng loại(type) trường(field) chính xác cho dữ liệu

Ngoài việc sử dụng tốt câu select ta còn 1 cách nữa để giảm dữ liệu dư thừa đó là sử dụng đúng type cho field trong table.Giả sử bạn có 1 field để check  user có được hoạt động hay không và dữ liệu của trường này là 0(không được active ) và  1 (được active ) khi đó bạn chỉ nên nên để type cho field này là TINYINT thay vì INTERGER.Việc sử dụng hợp lý type cho field giảm không gian lưu trữ trên máy chủ của bạn và giảm bộ nhớ đệm mỗi khi thao tác.

2 – Dùng index ở những trường(fields) hay dùng.

Ngoài đường truyền thì máy chủ server tính toán quá nhiều cũng sẽ làm chậm website của bạn. Đâ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). Để lấy được dữ liệu cần thiết theo lối thông thường thì  câu truy vấn (query)   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ể.

Ví dụ : để tìm thông tin của user có id=123  ta có sử dụng query như sau:

SELECT (user informations ) FROM users WHERE user_id = ‘123′;

MySQL biết rằng phải tìm ở table users 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  để tìm thông tin về user có id ‘123’.

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 , MySQL sẽ dễ dàng tìm ra được mã số 1 cách nhanh chóng. Cũng như  ví dụ quyển sách, khi cần tìm 1 thông tin, ta 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.

3 – Giảm thiểu sự kết nối tới MySQL Server.

Điều này dễ dàng nhận thấy khi bạn insert vào csdl.Giả sử bạn có 50 bản ghi và muốn insert vào csdl,bạn đơn giản là kết nối tới database và thực hiện hiện insert  lần lượt 50 bản ghi đó vào,việc này thật sự dễ dàng với số lượng bản ghi ít,tuy nhiên với số lượng bản ghi lớn hơn như 50 000 hay 100 000 bản ghi ta không thể thực hiện 50 000 tới 100 000 lệnh insert như vậy được,việc này chiếm  giữ rất nhiều tài nguyên hệ thống và có thể dẫn đến vượt quá maximum execution time gây chết câu truy vấn.Thay vào đó ta ghép 500 – 1000 bản ghi và insert trong 1 lần query với lượng dữ liệu lớn sẽ tốt hơn rất nhiều.

Nếu hệ thống của bạn không sử dụng 1 PDO nào để kết nối tới database mà sử dụng phương pháp kết nối thuần,ta thường sử dụng 2 hàm sau  MySQL_connect() và MySQL_pconnect(). Về cơ bản thì hai hàm này có các tham số y hệt nhau, nhưng nội hàm của chúng có những khác biệt đáng kể.

Theo lý thuyết, mỗi lần gọi hàm MySQL_connect(), hệ thống sẽ khởi tạo một kết nối mới tới CSDL, còn khi sử dụng hàm MySQL_pconnect(), hệ thống sẽ tận dụng kết nối đã được thiết lập trước đó.

Nếu trang Web của chúng ta được triệu gọi nhiều lần trong một khoảng thời gian ngắn, hàm MySQL_connect() sẽ tiêu tốn một lượng đáng kể tài nguyên của hệ thống để thiết lập kết nối. Vì vậy, hãy cố gắng sử dụng hàm kết nối MySQL_pconnect().

4 – Sử dụng đúng loại bảng để lưu trữ dữ liệu (engine type).

-> Với 1 ứng dụng có tần suất đọc cao như trang tin tức,blog… thì bạn nên dùng MyISAM.
-> Với ứng dụng có tần suất insert và update cao như: Diễn đàn, mạng xã hội.. thì bạn nên dùng InnoDB
-> Bạn nên dùng MEMORY Storage Engine cho các table chứa dữ liệu tạm và thông tin phiên làm việc của người dùng (Session)
-> Việc chuyển đổi 1 table từ storage engine này sang storage engine khác sẽ diễn ra tương đối lâu nếu dữ liệu trên table lớn. Do đó cần kiên nhẫn.

Tại sao lại như vậy :

MyISAM 

Storage Engine này cho phép lập chỉ mục toàn cột (Full Text Index). Do đó, Storage Engine này cho tốc độ truy suất (Đọc và tìm kiếm) nhanh nhất trong các Storage Engine.

Tuy nhiên, Nhược điểm của MyISAM là hoạt động theo kiểu Table Level locking nên khi cập nhật (Thêm,xóa,sửa) 1 bản ghi nào đó trong cùng 1 table thì table đó sẽ bị khóa lại, không cho cập nhật (Thêm,xóa,sửa) cho đến khi thao tác cập nhật trước đó thực hiện xong.

Ngoài ra, do thiết kế đơn giản và không kiểm tra ràng buộc dữ liệu nên loại Storage Engine này dễ bị hỏng chỉ mục và dễ bị Crash. Đây là cơn ác mộng của các webmaster khi table Crash là table có dung lượng lớn, khi phục hồi rất lâu và hồi hộp

Làm sao để chuyển 1 bảng từ Storage Engine khác (VD: InnoDB) sang MyISAM ?
Bạn có thể dùng truy vấn sau:

InnoDB

Storage Engine này không hỗ trợ Full Text Index như MyISAM (đã hỗ trợ từ version mysql 5.6) nhưng hỗ trợ quan hệ giữa các bảng (Khóa ngoại). Do đó, kiểu Storage này kiểm tra tính toàn vẹn dữ liệu và ràng buộc rất cao => Khó sảy ra tình trạng hỏng chỉ mục và Crash như MyISAM.

Ngoài ra, kiểu Storage Engine này hoạt động theo cơ chế Row Level Locking nên khi cập nhật (Thêm,xóa,sửa) 1 bảng thì chỉ có bản ghi đang bị thao tác bị khóa mà thôi, các hoạt động khác trên table này vẫn diễn ra bình thường.

Vì những tính chất trên, kiểu Storage Engine này thích hợp sử dụng cho Ngân hàng và các trang web có tần suất cập nhật dữ liệu cao như Mạng xã hội, diễn đàn….

Làm sao để chuyển 1 bảng từ Storage Engine khác (VD: MyISAM) sang InnoDB ?
Bạn có thể dùng truy vấn sau:

(* table MyISAM mà có cột nào đặt Full Text Index thì bạn phải xóa Full Text Index trên cột đó đi mới có thể chuyển được)

Memory

Đây là kiểu Storage Engine được lưu trữ dữ liệu trực tiếp lên RAM nên tốc độ truy xuất và cập nhật rất nhanh. Vì thế, nó được dùng làm các table chứa dữ liệu tạm, chứa các phiên làm việc của user…

MEMORY sử dụng cơ chế table-level locking như MyISAM.

Dung lượng của 1 bảng Storage Engine dạng MEMORY tối đa là bao nhiêu ?
Nó phụ thuộc vào cấu hình thông số max_heap_table_size trong file my.cnf, mặc định 1 bảng kiểu MEMORY có dung lượng tối đa là 16MB. Nếu vượt quá bạn sẽ nhận được lỗi: Table xyz is full…

5 -Chấp nhận dư thừa dữ liệu.

Trong trường các bạn cũng đã được biết đến rất nhiều các chuẩn thiết kế csdl 1NF ,2NF , 3NF . . .  .Vấn đề ở đây tôi thấy nhiều bạn quá máy móc và thiết kế 1 csdl quá chặt theo các chuẩn đó,khi đó dữ liệu dư thừa trong csdl của bạn là thấp nhất nhưng đôi khi bạn phải xới tung cả csdl đó lên để lấy 1 vài bản ghi.Dẫn đến gây chậm chạp hệ thống và ảnh hưởng đến cảm nhận của người dùng,nếu bạn đặt tiêu chí người dùng lên đầu tôi khuyên nên chấp nhận dư thừa dữ liệu với 1 số trường hợp cần thiết.