Optimizing MySQL Queries

Optimizing MySQL Queries

Optimizing MySQL Queries là một chủ đề hay 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 cũng như sysadmin ,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 Card Status 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 để type cho field này phù hợp có thể chọn 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 Card có id=123  ta có sử dụng query như sau:

SELECT (Card informations ) FROM Cards WHERE card_id = ‘123′;

MySQL biết rằng phải tìm ở table cards 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ề card có id ‘123’.

Index là 1 file riêng biệt được lưu trữ ở server 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 card_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 databases .Giả sử bạn có 50 records và muốn insert vào databases ,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 records đó 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ư 50k hay 100k records ta không thể thực hiện 50k tới 100k 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 – 1k records 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.

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:

ALTER TABLE table_name ENGINE = MyISAM;

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:

ALTER TABLE table_name ENGINE = InnoDB;

(* 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)

Ngoài ra nó còn 1 kiểu nữa đó là 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…

Tips fix mysql error :

‘Got fatal error 1236’ triggers

# Trên server Master :

mysql:root@localhost:mysql1.sock/(none)> show master status;
+——————+———–+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———–+————–+——————+
| mysql-bin.001128 | 348987090 | | |
+——————+———–+————–+——————+

# Trên server slave

mysql:root@localhost:mysql1.sock/(none)> stop slave;
CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.001128′, MASTER_LOG_POS=4;

mysql:root@localhost:mysql1.sock/(none)> start slave;

Trả lời

Email của bạn sẽ không được hiển thị công khai.