Mẹo nhỏ để tối ưu hiệu năng MySQL

1 Lựa chọn storage engine

Storage Engine mặc định MySQL v5.5(2010) trở lên là InnoDB, trước đó Oracle đặt MyISAM là storage mặc định cho MySQL và InnoDB ra đời để hỗ trợ transaction. Ở những phiên bản trước thường có những so sánh giữa InnoDB và MyISAM để chọn làm storage engine cho cơ sở dữ liệu. Tuy nhiên với phiên bản hiện tại 5.7 hoặc 5.6 bạn hãy chọn InnoDB bởi tính năng vượt trội hơn của nó so với các storage engine khác của MySQL. Với phiên bản hiện tại InnoDB cũng đã hỗ trợ full-text search hoặc bạn cũng có thể sử dụng các search engine khác hiệu năng tốt hơn tích hợp vào MySQL. Hơn thế nữa InnoDB vẫn đang được Oracle hỗ trợ rất tốt trong khi MyISAM thì không, InnoDB sẽ giúp bạn tiếp tục mở rộng hơn cơ sở dữ liệu của mình.

2 Thiết kế một database đủ tốt

Thiết kế database là 1 vấn đề lớn không thể đề cập hết trong 1 topic được với kinh nghiệm ít ỏi mình chỉ có thể gợi ý 1 vài gợi ý sau đây để làm cho cơ sơ dữ liệu có hiệu năng cải thiện hơn chút:

2.1 Lựa chọn kiểu dữ liệu cho các thuộc tính của bảng

Đây là bước cơ sở khi thiết kế databse lựa chọn kiểu dữ liệu nào cũng cần dựa vào nhiều yếu tố. Tuy nhiên có 1 số điều bạn cần đạt được đó là:

1. Chọn kiểu dữ liệu càng "rẻ" càng tốt mà vẫn đảm bảo việc lưu trữ và đặc trưng cho dữ liệu: Rẻ ở đây là số lượng CPU cycles để xử lí các thao táo cũng như không gian bộ nhớ lưu trữ chúng. Chẳng hạn như dùng kiểu integer chắc chắn sẽ "rẻ" hơn dùng string. Tuy nhiên nó vẫn phải đảm bảo việc lưu trữ và đăc trưng cho dữ liệu.
   MySQL hỗ trợ rất nhiều kiểu dữ liệu, bởi vậy hãy lựa chọn kiểu dữ liệu thật phù hợp.
2. Càng "nhỏ" càng tốt: Việc dùng int để định danh cho các thành phố là khá thừa thãi thay vào đó hãy dùng tinyint sẽ làm giảm không gian lưu trữ trên bộ nhớ cũng như cache hay việc dùng unsigned nếu không sử dụng số âm giúp tăng gấp đôi range nhưng không làm tăng không gian lưu trữ.
3. Tránh dùng null nếu không cần thiết: Việc dữ liệu của cột đó phải trộn thêm null vào rõ ràng sẽ làm giảm khả năng tối ưu của MySQL. Do vậy chỉ nên dùng null khi cần thiết.

2.2 Đánh Index phù hợp

Có thể nói index là công cụ tốt nhất giúp storage engine tăng hiệu năng tìm kiếm rows cũng như tối ưu truy vấn dựa vào index bởi những ưu điểm của index:

1. Giảm khối lượng dữ liệu cần xem xét từ O(N) (tuần tự tất cả các bản ghi trong bảng) xuống O(logN) nếu dùng B-tree. Nếu N = 1 tỷ thì logN = 30
2. Tránh phải sorting(Bản thân B-tree đã có thứ tự) và bảng tạm.
3. chuyển từ đọc ghi ngẫu nhiên sang đọc ghi tuần tự.

Tuy nhiên, để có được hiệu năng tìm kiếm, ta phải đánh đổi hiệu năng insert, update và delete bởi mỗi khi thực hiện các thao tác trên ta phải xử lí index cho dữ liệu. Do đó bạn cần cân nhắc lựa chọn nếu thường xuyên update, insert, delete hơn thì tốt nhất không nên đánh index. Ngoài ra việc đánh index thiếu hợp lí cũng khiến cho hiệu năng không được tối ưu.

3.Tối ưu việc viết câu truy vấn

Khi đã có best-design rồi bạn cũng cần nghĩ tới việc tối ưu những câu truy vấn. Bởi ngay cả khi có được 1 schema tốt chưa hẳn đã đem lại 1 hiệu năng tốt nếu những câu truy vấn rất tồi. Mình xin được liệt kê 1 số câu truy vấn thường gặp mà chưa được tối ưu:

1. Select quá thừa thãi cả những thứ không dùng tới: Nhiều lúc code chúng ta thường xuyên dùng SELECT * mà không quan tâm tới việc liệu chúng có thực sự cần thiết hay không. Thậm chí SELECT * còn xuất hiện trong cả câu truy vấn chứa join:
   Ví dụ cần lấy ra các cuốn sách được sở hữu bởi những người ở Hà Nội

    SELECT * 
    FROM users 
    INNER JOIN cities USING(city_id)
    INNER JOIN books ON books.user_id = users.id
    WHERE cities.name = 'Hanoi';

Tuy nhiên, thay vì chỉ lấy ra thông tin các cột bảng books thì kết quả cho ra lại là cả 3 bảng.
Việc lấy ra tất cả các cột gây cản trở việc tối ưu của MySQL làm tăng thêm bộ nhớ và CPU dẫn tới hiệu năng câu truy vấn bị giảm.

1. Select quá nhiều rows: Nhiều lúc, thay vì phải lấy toàn bộ bản ghi lên đến hàng trăm nghìn ta chỉ cần lấy khoảng vài chục, vài trăm bản ghi. Chẳng hạn ta lấy ra các articles để filled vào trang chủ website. Rõ ràng khả năng hiển thị của website chỉ tới vài chục articles thay vì phải đợi lấy toàn bộ mà không dùng tới. Trong MySQL ta sử dụng LIMIT để lấy ra số lượng kết quả mong muốn.
2. LIMIT everywhere: Không chỉ dùng LIMIT để giới hạn bản ghi lấy ra. Ta còn sử dụng LIMIT cho update, insert, delete. Các máy chủ SQL phải xử lí rất nhiều câu truy vấn yêu cầu thay đổi dữ liệu vào cùng 1 thời điểm(Bài toán concurrency control). Do đó máy chủ sẽ thực thi 1 hệ thống locking. Trong MySQL thường đề cập tới read/write locks, tables lock và rows lock trong storage engine(InnoDB, XtraDB). Khi thực hiện câu lệnh update, delete rất nhiều bản ghi nó sẽ lock rất nhiều row trong 1 thời gian dài và block cả những câu truy vấn nhỏ hợp lệ khác. Do đó thay vì viết

DELETE FROM books WHERE publish_at < '2018-01-01';

Hãy xử lí nó trong PHP như sau:

$row;
do {
    rows = mysqli_query("DELETE FROM books WHERE publish_at < '2018-01-01' LIMIT 1000;");
} while $row > 0;

Lúc này nó chỉ thực hiện delete 1000 bản ghi và giữa các lần DELETE sẽ có 1 khoảng thời gian nghỉ nhỏ để các câu truy vấn khác có thể thực hiện vào tài nguyên bị lock vừa rồi.

1. Update và select trên cùng một bảng:

UPDATE
  products_description AS pd
SET 
  pd.products_seo = (
    SELECT
      pd2.products_seo
    FROM 
      products_description AS pd2
    WHERE
        pd2.language_id = 1
    AND pd2.products_id = pd.products_id
  )
WHERE
  pd.language_id <> 1

MySQL không cho phép SELECT từ 1 bảng đang chạy UPDATE hoặc DELETE. Đây là 1 hạn chế từ việc tối ưu xử lí subquery của MySQL. Và đây là bài viết giải thích khá chính xác về điều này https://dba.stackexchange.com/questions/1371/problem-with-mysql-subquery/1384#1384
Giải pháp ở đây rất triệt để đó là ta không update và select trên cùng 1 bảng nữa mà trên 2 bảng. subquery sẽ mở và đóng bảng trước khi update inner join. Đảm bảo subquery được thực hiện thành công

UPDATE
        products_description pd INNER JOIN products_description pd2 ON
    (pd.products_id=pd2.products_id AND pd2.language_id=1 AND pd.language_id<>1)
SET pd.products_seo = pd2.products_seo;

1. Dùng ít câu truy vấn hơn để lấy ra kết quả: Thay vì viết nhiều câu truy vấn đơn hãy cố gắng viết ít câu truy vấn hơn. Chẳng hạn như đếm số lượng Doctor, Professor, Singer, and Actor trong bảng Occupations:

Occupations

Để lấy số lượng các Doctor, Professor, Singer, and Actor trong bảng Occupations thay vì viết 4 cầu truy vấn đơn:

    SELECT COUNT(*) FROM Occupations WHERE occupation = 'Doctor';
    SELECT COUNT(*) FROM Occupations WHERE occupation = 'Profession';
    SELECT COUNT(*) FROM Occupations WHERE occupation = 'Singer';
    SELECT COUNT(*) FROM Occupations WHERE occupation = 'Actor';

Hãy dùng if function trong MySQL:

SELECT  sum(if(occupation = 'Doctor', 1, 0)) as doctor,
        sum(if(occupation = 'Professor', 1, 0)) as professor,
        sum(if(occupation = 'Singer', 1, 0)) as singer,
        sum(if(occupation = 'Actor', 1, 0)) as actor 
FROM OCCUPATIONS

Ngoài if còn có case đây là 2 hàm rất hữu dụng mà ta hay bỏ qua. 1 số bài toán hay nên dùng if và case

https://www.hackerrank.com/challenges/occupations/problem https://www.hackerrank.com/challenges/binary-search-tree-1/problem

1. Giảm số lượng các row phải quét tối đa nếu có thể: Để lấy ra các users có id > 10. Ta sẽ viết

    SELECT COUNT(*) FROM users WHERE id > 10;

Hoặc cũng có thể viết như sau:

    SELECT (SELECT COUNT(*) from users) - count(*) as number FROM users where id <= 10;

/pictures/picfullsizes/2018/10/16/mbz1539698941.png

Lý do bởi id là khóa chính đã được đánh index. Việc chỉ quét qua các id <= 10 do 10 là số cũng khá nhỏ sẽ tốn ít thời gian hơn phải quét qua các id > 10 nếu bảng lớn lên đến hàng trăm nghìn row. Ở ví dụ trên users được seed với 1000 bản ghi và đã có những khác biệt về thời gian truy vấn khá rõ ràng. Sẽ có nhiều trường hợp bạn có thể giảm số row phải quét lúc đó hãy cố gắng quét càng ít càng tốt.