Tối ưu và mở rộng một website sử dụng MySQL

Tối ưu và mở rộng một website sử dụng MySQL có nghĩa là gì?

Nhắm mắt và tưởng tượng rằng:

Bây giờ hãy tưởng tượng bạn đang có một website sử dụng MySQL làm database. Và bỗng nhiên một ngày đẹp trời nào đó website của bạn có lượng truy cập gấp 1000 lần. Ừ thì vui thì cũng có vui nhưng bạn lại thấy MySQL bị chết do timeout. Vậy thì chúng ta nên làm gì?

Rồi, lúc này khi bạn vác sách tới hỏi 3 anh ban đầu thì bạn sẽ nhận được 3 câu trả lời như sau:

Anh developer sẽ có quan điểm rằng là: “Tầm này thì tao nghĩ chúng ta nên tối lưu lại tầng ứng dụng, tối ưu các logic để xử lý tốt hơn. Humm”.

Anh database admin tiếp lời: “Tối ưu ứng dụng là cần thiết nhưng tao nghĩ cũng nên tối ưu tầng MySQL nữa. Tối ưu các câu query này, đánh index cho bảng này, rồi là dùng các công cụ hỗ trợ này,…”

Anh DevOps dõng dạc: “Ừ thì 2 chú nói cũng đúng, nhưng mà anh nói thật này. Các chú có tối ưu thế nào đi nữa mà không mở rộng hạ tầng server ấy, thì có mà còn khướt nhé”.

Vậy nên nghe theo ai? Theo mình thì nên nghe cả 3 anh. Nhiệm vụ của chúng ta sẽ là tối ưu toàn bộ hệ thống chứ không riêng gì một thành phần nào cả. Bắt đầu thôi nào!

Tối ưu tầng ứng dụng

Cache Queries

Việc chúng ta caching các query của cơ sở dữ liệu vào một in-memory database (ví dụ như Redis) sẽ khiến cho việc truy vấn đến cơ sở dữ liệu được giảm bớt. Tất nhiên là không phải query nào chúng ta cũng sẽ cache lại. Chúng ta sẽ chỉ cache những câu query nào thường xuyên được sử dụng nhất cũng như là có độ phức tạp hay là có thời gian truy vấn chậm nhất.

Tuy nhiên, có một nhược điểm của việc này đó chính là nó sẽ làm tăng độ phức tạp của việc code và đồng thời nó sẽ khiến chúng ta khó debug ứng dụng hơn nếu xảy ra lỗi. Sự phức tạp này đến từ việc phải luôn giữ cho ứng dụng hoạt động một cách đúng đắn. Giả dụ như dữ liệu được cập nhật từ lâu rồi nhưng cache vẫn là dữ liệu cũ dẫn đến việc hiển thị, tính toán sai thông tin thì đúng là thảm họa.

Kỹ thuật caching phổ biến đó là nếu như khi dữ liệu một bảng được cập nhật thì chúng ta sẽ xóa cache của nó đi. Để nó sẽ truy vấn và lấy ra dữ liệu mới nhất từ trong database và lưu kết quả vào cache.

Cách caching này thì sẽ hoạt động tốt với các ứng dụng có số lượng đọc cao. Với các ứng dụng có lượng ghi vào database lớn nó sẽ mất đi tác dụng, thậm chí làm chậm ứng dụng của bạn.

Tránh truy vấn N+1 query

Vậy truy vấn N+1 là gì? Các bạn hãy xem ví dụ dưới đây để hiểu rõ hơn nhé (ở ví dụ dưới mình sử dụng Laravel nha):

Giả dụ nhiệm vụ của chúng ta là lấy ra tất cả bài viết và thông tin của những người viết ra bài viết đó.

Câu truy vấn đầu tiên chúng ta sẽ lấy ra tất cả các quyển sách trước này. Cái này sẽ là 1 câu truy vấn.

1

2

$books = App\Book::all();

// SELECT * FROM books

Tiếp theo chúng ta sẽ code như sau để hiển thị tên những tác giả:

1

2

3

4

foreach ($books as $book) {

echo $book->author->name;

// SELECT * FROM authors where id =

}

Tưởng tượng bạn có 10000 quyển sách và nó sẽ phải dùng 10001 câu truy vấn. Chết là cái chắc đúng không nào.

Thay vì vậy thì chúng ta nên sử dụng Eager Loading/Lazy Loading để biến nó thành 2 câu query.

1

2

3

4

5

6

7

8

$books = App\Book::with('author')->all();

// SELECT * FROM books

// SELECT * FROM authors WHERE id IN (author_ids,....)

foreach ($books as $book) {

echo $book->author->name;

// không có query nào được sử dụng

}

Ngoài Laravel thì mình thấy hầu hết các framework đều hỗ trợ vậy xử lý N+1 query. Các bạn hãy thử google để xem nhé.

Lưu dữ liệu trả về vào biến

Nếu như bạn sử dụng kết quả của 1 câu query ở nhiều nơi trong code thì bạn nên đặt nó thành 1 biến. Theo mình thì cái này đa phần mọi người đề làm rồi. Tuy nhiên có thể những bạn mới sẽ cần phải lưu ý nhé.

Thay vì tốn nhiều 3 query như này:

1

2

3

4

5

$id = 1;

echo 'Họ tên: ' . User::find($id)->name;

echo 'Tuổi: ' . User::find($id)->age;

echo 'Địa chỉ: ' . User::find($id)->address;

Thì mình khuyên các bạn nên gán kết quả trả về của user vào 1 biến rồi sử dụng lại. Như vậy bạn chỉ cần 1 query duy nhất thôi.

1

2

3

4

5

6

$id = 1;

$user = User::find($id);

echo 'Họ tên: ' . $user->name;

echo 'Tuổi: ' . $user->age;

echo 'Địa chỉ: ' . $user->address;

Tạo thêm cột hoặc bảng cho việc thống kê

Ví dụ như trang web của bạn là 1 web chat chẳng hạn. Bạn sẽ có 2 bảng cơ bản là users và messages. Bây giờ website có 1 chức năng là đếm số message chưa đọc của mỗi user. Thay vì sử dụng query để COUNT thì cách tốt nhất là thêm 1 cột là unread_message_count vào bảng users chẳng hạn. Như vậy chỉ với 1 câu SELECT đơn giản là bạn có thể lấy ra được kết quả rồi. Tương tự với các phép tính toán khác như SUM, MIN, MAX, AVG.

Tương tự, nếu như bạn có rất rất nhiều dữ liệu và phải tính toán dựa trên bảng đó để lấy kết quả thì cách tốt nhất ở đây là tạo ra 1 bảng lưu giữ việc thống kê đó (Summary Table). Ví dụ như hệ thống của bạn là 1 CRM và có dashboard hiển thị tổng số sản phẩm, tổng sản phẩm bán được, tổng sản phẩm hết hàng,… thì thay vì query lại và tính toán khi reload trang thì hãy tính toán rồi lưu vào trong 1 bảng thống kê. Như vậy chỉ cần nhẹ nhàng 1 câu query là có thể giải quyết rồi phải không nào.

Sử dụng các công cụ tìm kiếm (Search Engine)

Bạn có thể giảm tải cho cơ sở dữ liệu bằng cách chuyển các truy vấn phức tạp hoặc dữ liệu nặng vào một công cụ tìm kiếm như kiểu là Elaticsearch chẳng hạn. Tất nhiên sẽ phải đánh đổi đó là nó làm tăng độ phức tạp của việc xây dựng, debug,…

Tối ưu tầng MySQL

Chỉ SELECT những column cần thiết

Đừng dùng SELECT * tùy tiện ở mọi chỗ nhé. Nếu bạn chỉ muốn lấy tên user thôi thì cũng ko cần thiết phải SELECT * FROM users để làm gì cả.

Sử dụng kiểu ENUM

Thay vì sử dụng:

`1`	`education VARCHAR(10) NOT NULL# Use ENUM`

Thì chúng ta có thể:

`1`	`education ENUM ('highschool', 'college', 'postgrad') NOT NULL`

Ngoài kiểm tra tính toàn vẹn dữ liệu, cột ENUM chỉ phải sử dụng 1 byte cho với 255 giá trị riêng biệt.

Tạo INDEX cho các trường cần thiết

INDEX trong SQL là gì?

Nói dễ hiểu thì bạn hãy tưởng tượng bạn có một quyển bách khoa toàn thư chứa thông tin về mọi thứ trên đời. Một ngày đẹp trời nào đó bạn bị “say nắng” ai đó và muốn tìm hiểu về cách tán gái/trai. Bạn sẽ làm thế nào nếu không có mục lục. Tất nhiên là bạn sẽ phải tìm qua từng trang một đến khi nào tìm được toàn bộ những thông tin về “bí kíp tán gái/trai” đúng không? Còn nếu có mục lục thì mọi thứ sẽ dễ dàng hơn. Chỉ cần dở mục lục ra là bạn sẽ tìm và đọc được ngay bí kíp rồi. Nhưng để mà tạo ra mục lục cho quyển sách khổng lồ này thì người viết cũng cần mất nhiều công sức hơn và chúng ta cũng cần tốn nhiều trang giấy để viết mục lục lên đó.

Bạn có thể xem chi tiết ở 2 bài sau:

Kiểm tra các câu query chậm (slow query log)

Trước đây mình từng tham gia một dự án, khi nó dần lớn lên và có nhiều người dùng thì xảy ra một vài vấn đề. Đó là một vài chức năng bỗng nhiên load rất chậm, vậy la mình tiến hành check các câu query chậm và phát hiện ra 1 câu query tốn đến 20s.

Với cách này bạn sẽ biết được câu nào bị chậm với thời gian mà bạn set. Ví dụ bạn muốn xem tất cả câu query có thời gian > 5s chẳng hạn.

1

2

3

SET GLOBAL slow_query_log = 'ON';

SET GLOBAL long_query_time = 5; # seconds

SET GLOBAL slow_query_log_file = '/path/filename';

Bạn có thể đọc thêm ở dưới đây:

https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html

Mở rộng hạ tầng server

Có 2 cách mở rộng đó là theo chiều ngang và theo chiều dọc.

MySQL Replication (chiều ngang)

MySQL Replication là một quá trình cho phép bạn dễ dàng duy trì nhiều bản sao của dữ liệu MySQL bằng cách cho họ sao chép tự động từ một master tạo ra một cơ sở dữ liệu slave. Điều này rất hữu ích vì nhiều lý do bao gồm việc tạo điều kiện cho sao lưu cho dữ liệu, một cách để phân tích nó mà không sử dụng các cơ sở dữ liệu chính, hoặc chỉ đơn giản là một phương tiện để mở rộng ra.

Để hiểu hơn về mô hình này các bạn có thể tham khảo:

MySQL Replication

Giới thiệu về MySQL Replication Master-Slave

Nâng cấp server (chiều dọc)

Nâng cấp server theo chiều dọc tức là việc bạn tăng RAM, CPU, dung lượng ổ cứng cho server của bạn để việc lưu trữ dữ liệu, xử lý dữ liệu diễn ra nhanh và tốt hơn. Hiện nay với việc các cloud service rất phát triển thì việc này rất đơn giản. Thậm chí chỉ cần vài cú click chuột là xong thôi.

Theo vantien.net

Japan IT Works

Tối ưu và mở rộng một website sử dụng MySQL