Một vài kỹ thuật tăng tốc MySQL

1. Giới thiệu

Một trong những nhân tố ảnh hưởng lớn đến performance của hệ thống là truy vấn CSDL. Ở bài viết này sẽ đề cập đến một vài kỹ thuật tăng tốc MySQL

2. Các kỹ thuật

Chỉ SELECT những trường bạn cần

Thông thường ta hay sử dụng SELECT() để chọn tất cả các trường trong 1 row. Tuy nhiên, một dòng SELECT() có thể gây chậm khi chạy truy vấn, đặc biệt là trên các bảng dữ liệu 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ủ CSDL 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.

Hơn nữa là dùng SELECT(*) trả về mọi trường sẽ gây cản trở việc sử dụng index.

Đánh index hợp lý

Trong cấu trúc dữ liệu và giải thuật, ta có các giải thuật tìm kiếm nhanh, đó luôn là phương pháp tìm kiếm nhanh nhất.

Các trường được thiết lập ở dạng index sẽ được sắp xếp trên một file riêng, khi chúng ta truy vấn dữ liệu thông qua các trường index, các giải thuật tìm kiếm sẽ phát huy tính hiệu quả tối đa của nó, đặc biệt là các trường index dạng số.

Với các trường hay được sử dụng để trong câu WHERE hay các trường FOREIGN_KEY, PRIMARY_KEY nên được đánh index, để MYSQL không phải duyệt qua toàn bộ database để tìm dữ liệu ta cần.

Với các cột được dùng để UPDATE thì không nên đánh index, vì khi UPDATE sẽ thực hiện update lại toàn bộ index nên câu UPDATE sẽ bị chậm, index chỉ nên sử dụng cho lệnh SELECT.

Sử dụng EXPLAIN để phân tích query giúp cho việc đánh index hợp lý hơn.

Sử dụng EXPLAIN để phân tích câu query

EXPLAIN là lệnh mà mọi lập trình viên sử dụng MySQL nên cần phải biết, nó là lệnh rất hữu ích trong MySQL, nó có thể cung cấp một số chi tiết tuyệt vời về các truy vấn được chạy, những chỉ số được sử dụng, bao nhiêu hàng cần kiểm tra thông qua, bảng tạm thời và những điều khó chịu khác mà bạn muốn tránh.

Có rất nhiều bài viết nói về cách sử dụng EXPLAIN, bạn nên dành nhiều thời gian để tìm hiểu về nó, sẽ rất hữu ích khi sử dụng MySQL

Hạn chế sử dụng DISTINCT

Lệnh DISTINCT làm chậm truy vấn dữ liệu, vậy nên nếu như không thật cần thiết thì không nên sử dụng DISTINCT

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

Khi thiết kế Database, nếu thường tạo nhiều bảng cho các nhóm dữ liệu là điều ta đã được dạy, điều đó sẽ cho ra thiết kế đẹp mắt, dễ nhìn, tuy nhiên khi truy vấn dữ liệu ta phải xới tung nhiều bảng có quan hệ với nhau đế lấy ra vài dữ liệu, điều đó làm ảnh hưởng lớn đến tốc độ hệ thống

Vì vậy, nếu không thật cần thiết, thì ta có thể gộp vào 1 bảng, giảm bớt số bảng phải truy vấn, truy vấn trên 1 bảng sẽ luôn luôn nhanh hơn rất nhiều khi truy vấn trên nhiều bảng.

Tốt nhất là 1 câu query, không nên phải duyệt qua quá 3 bảng.

Hạn chế sử dụng nhiều INSERT cho nhiều row

Khi cần INSERT nhiều row, thay vì dùng lệnh INSERT cho từng row, ta có thể chỉ dùng 1 lệnh INSERT cho tất cả row, điều đó sẽ tăng tốc khá nhiều

INSERT INTO tableName
    (column1,column2,column3,column4)
VALUES
    ('value1' , 'value2', 'value3','value4'),
    ('value1' , 'value2', 'value3','value4'),
    ('value1' , 'value2', 'value3','value4');

Kết hợp SELECT khi cần INSERT với điều kiện gì đó thay vì sử dụng SELECT để lấy dữ liệu rồi chạy INSERT với từng record

INSERT INTO table
  (column1,column2,column3,column4)
  SELECT (column1,column2,column3,column4)
  FROM table2
  WHERE condition

Sử dụng kiểu dữ liệu của trường chính xác

Có rất nhiều các kiểu trường khác nhau trong MySQL, nhưng ta nên sử dụng những loại quen thuộc với chuỗi(string) và số(numeric) là tốt nhất cho dữ liệu.

Ví dụ, nếu chỉ lưu trữ các số 1, 2 và 3 trong một trường, hãy sử dụng TINYINT tốt hơn INTERGER. Nó chiếm ít không gian lưu trữ trên máy chủ.

Một vài thủ thuật khác

• Nên hạn chế sử dụng các toán tử sau trong mệnh đề WHERE: "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%abc'", vì các toán tử này sẽ không sử dụng đặc tính của index mà sẽ quét toàn bộ bảng.

Ví dụ: nên sử dụng default cho các trường thay vì để null

• Trong câu truy vấn có nhiều OR, thì nên dùng UNION ALL thì sẽ giúp tăng tốc độ truy vấn

# thay vì dùng
SELECT employeeID, firstname, lastname
FROM Employees
WHERE dept = 'prod' or city = 'Orlando' or division = 'food'

# ta sửa thành
SELECT employeeID, firstname, lastname FROM Employees WHERE dept = 'prod'
UNION ALL
SELECT employeeID, firstname, lastname FROM Employees WHERE city = 'Orlando'
UNION ALL
SELECT employeeID, firstname, lastname FROM Employees WHERE division = 'food'

• Nếu như cần xác nhận sự tồn tại của record, thì không nên sử dụng COUNT(*) vì không hiệu quả, gây lãng phí bộ nhớ mà nên dùng EXISTS sẽ nhanh hơn, vì lệnh EXISTS sẽ quét nếu tìm thấy 1 record thỏa mãn thì dùng duyệt database.

• Sử dụng CASE- WHEN trong lệnh UPDATE:

Giả sử ta cần update các record nhưng theo các trường hợp update khác nhau, nếu điều kiện cho các trường hợp không quá phức tạp thì thay vì xử lý bằng code web cho từng trường hợp rồi gọi lệnh UPDATE của mysql thì ta dùng CASE- WHEN trong lệnh UPDATE

Ví dụ:

case
when TH1
  table.update column = 'data1'
when TH2
  table.update column = 'data2'
when TH3
  table.update column = 'data3'
end

ta có thể dùng với Mysql

UPDATE table
SET table.column = (
   CASE
   WHEN TH1 THEN 'data1'
   WHEN TH2 THEN 'data2'
   ELSE WHEN TH3 THEN 'data3'
   END
)