Tăng tốc độ truy vấn MySQL

Khi thiết kế các hệ thống lớn với nhiều người truy cập, một trong những điều người ta nghĩ đến ngay lập tức là thiết kế CSDL sao cho ta có thể truy vấn nhanh nhất có thể.

Loạt bài dưới đây sẽ trình bày các kỹ thuật tối ưu hoá hệ thống với CSDL MySQL.

Quy tắc 1: Giảm thiểu sự kết nối tới MySQL Server.
Khi kết nối tới CSDL MySQL, chúng ta có 2 hàm kết nối là 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().

Quy tắc 2: Thiết lập các trường index và cố gắng truy vấn dữ liệu thông qua các điều kiện xác lập trên chỉ số.

Nếu các bạn học qua cấu trúc dữ liệu và giải thuật, hẳn chúng ta cũng phải nhớ đến các giải thuật tìm kiếm nhanh. Chúng ta đã đúc kết được rằng giải thuật tìm kiếm là nhanh nhất với cách tìm dựa trên bảng băm hoặc trên mảng đã sắp xếp (với thuật toán tìm kiếm nhị phân nổi tiếng). 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ì vậy, hãy cố gắng thiết kế các truy vấn cũng như CSDL sao cho tối ưu nhất dựa trên nguyên tắc chỉ số này.

Quy tắc 3: Chấp nhận dư thừa dữ liệu

Một thiết kế dữ liệu theo dạng chuẩn 4 có thể rất đẹp mắt, nhưng khi truy vấn dữ liệu, chúng ta sẽ phải “xới tung” nhiều bảng quan hệ có khi chỉ để lấy ra một record. Ngày xưa, khi giá thành ổ cứng cao ngất ngểu, dung lượng ổ cứng bé tẹo nên các cụ phải thiết kế dữ liệu ở dạng “tiêu chuẩn cao” nhằm giảm dung lượng lưu trữ, nhưng ngày nay, dung lượng lưu trữ không còn là vấn đề đáng lo lắng, vì vậy trong một số trường hợp, hãy chịu khó hi sinh tính đẹp đẽ của chuẩn 4 để tăng tốc độ truy vấn. Nên nhớ rằng truy vấn trên một bảng sẽ nhanh hơn rất nhiều lần khi truy vấn trên nhiều bảng quan hệ.

Quy tắc 4: Chỉ lấy đúng và đủ dữ liệu cần thiết

Nhiều người thường thích truy vấn dạng “Select *…”. Dấu * ở đây sẽ bắt hệ thống làm việc mệt nhọc hơn vì phải xử lý nhiều dữ liệu hơn. Dữ liệu trả về cũng tiêu tốn nhiều bộ nhớ hơn. Vì vậy, thay vì select *, hãy chỉ select những trường cần thiết.

Một vấn đề nữa là khi sử dụng hàm mysql_fetch_array, nhiều người thường bỏ qua các tham số tuỳ chọn. Nếu có thể, hãy sử dụng tham số MYSQL_ASSOC, khi đó hệ thống sẽ trả về một mảng với chỉ số là tên trường, như vậy các bạn sẽ dễ hình dung và đỡ tốn bộ nhớ vì phải phát sinh thêm một mảng với chỉ số dạng số.

Quy tắc 5: Giải phóng bộ nhớ ngay sau khi sử dụng xong

Theo mặc định thì PHP sẽ giải phóng bộ nhớ sau khi chạy xong toàn bộ chương trình, nhưng với một cỗ máy chủ già nua cũ kỹ với hàng trăm lượt truy cập một lúc thì 1 KB bộ nhớ cũng là một tài nguyên cực kỳ quý giá. Vậy tại sao chúng ta không giải phóng bộ nhớ cho những thứ không dùng đến?

Sau khi thực hiện các truy vấn và thực hiện xong các phép tính toán với các bản ghi lấy được, hãy chịu khó nhét cái function mysql_free_result() vào ngay nhé.

Nguồn: phpvn.org

Tốc độ load trang ảnh hưởng rất nhiều bởi tốc độ truy vấn cơ sở dữ liệu. Nhất là những trang web có cơ sở dữ liệu lớn, truy vấn phức tạp. Do đó việc tối ưu icác câu lệnh truy vấn SQL do vậy hiểu được cách để tối ưu hóa câu lệnh SQL là rất quan trọng vì nó sẽ giúp cho hiệu suất hệ thống ta tăng lên đáng kể, giảm thời gian chờ đợi từ phía người dùng. Bài viết sẽ trình bày các cách để tối ưu hóa các câu lệnh này.

1. Đánh index ở các cột sử dụng where, order by, group by

• Bên cạnh việc đảm bảo tính duy nhất các bản ghi, index cho phép MySQL server lấy kết quả nhanh hơn. Index có thể chiếm bộ nhớ tài nguyên và giảm performance khi insert, delete, update. Tuy nhiên trong trường hợp bảng có nhiều bản ghi (> 10), chúng có thể giúp giảm thời gian cho các câu lệnh select. Hãy cùng xem ví dụ dưới đây khi chạy một câu lệnh SQL với 500 rows mà không sử dụng index

mysql> explain select customer_id, customer_name from customers where customer_id='1'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ |  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    10.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------|-------------+ 

Khi sử dụng EXPLAIN để xem chi tiết câu truy vấn trên ta thấy một số thông tin quan trọng:

• MySQL đã search toàn bộ table để lấy được bản ghi có id được truyền vào ban đầu dựa vào cột type: ALL, rows: 500
  Để sửa lại tối ưu cho câu truy vấn trên ta chỉ cần đánh index vào cột customer_id

mysql> Create index customer_id ON customers (customer_Id); 

Chạy lại câu truy vấn bên trên sẽ cho ra kết quả

mysql> Explain select customer_id, customer_name from customers where customer_id='1'; +----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ |  1 | SIMPLE      | customers | NULL       | ref  | customer_id   | customer_id | 13      | const |    1 |   100.00 | NULL  | +----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ 

Có thể thấy truy vấn đã được tối ưu do chỉ cần duyệt trên 1 rows mà không cần duyệt cả bảng như trên nữa. Việc chọn và đánh index cũng cần phải lựa chọn kỹ lưỡng để tránh việc lãng phí tài nguyên và không đem lại hiệu suất cho hệ thống. Tham khảo thêm ở đây

2. Tối ưu hóa câu lệnh bằng Union

Thỉnh thoảng chúng ta cũng cần chạy các câu truy vấn so sánh với 'like', 'or'. Khi sử dụng 'or' quá nhiều có thể mysql sẽ phải search toàn bộ bảng để tìm kiếm bản ghi. Union có thể giúp câu truy vấn trở nên nhanh hơn đặt biệt là trong trường hợp đã đánh index một cách hợp lý. Ví dụ trong trường hợp dưới đây

mysql> select * from students where first_name like  'A%'  or last_name like 'B%' ; 

Câu truy vấn trên có thêm được tối ưu hơn bằng cách sử dụng union để tận dụng index đã đánh

mysql> select  from students where first_name like  'A%'  union all select  from students where last_name like  'B%' ; 

3. Tránh sử dụng câu truy vấn cùng like với '%' phía trước

Hãy xem xét câu truy vấn dưới đây

mysql> select * from students where first_name like  '%A'  ; 

Sử dụng EXPLAIN để xem chi tiết hơn

mysql> explain select * from students where first_name like  '%A'  ;  +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ |  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    11.11 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 

có thể thấy index không có tác dụng trong trường hợp này, truy vấn vẫn cần phải duyệt toàn bộ bảng để tìm kiếm các bản ghi thỏa mãn yêu cầu. Trong trường hợp này có 2 lựa chọn hãy xem xét kỹ nếu có thật sự cần dùng % ở phía trước nếu không hãy loại bỏ nó hoặc xem xét có thể dùng full-text index

4. Sử dụng truy vấn của MySQL Full-text search

Trong trường hợp phải truy vấn với các toán tử wildcards như ở trên, ta có thể xem xét sử dụng MySQL full-text search (FTS) sẽ cho hiệu suất tốt hơn. Để thêm full-text search index ta sử dụng câu lệnh

mysql>Alter table students ADD FULLTEXT (first_name, last_name); mysql>Select * from students where match(first_name, last_name) AGAINST ('A'); 

Trong ví dụ bên trên, chúng ta đã xác định 2 cột mà muốn match (first_name, last_name) tìm kiếm với ('A'). Chạy EXPLAIN có kết quả như sau

mysql> explain Select * from students where match(first_name, last_name) AGAINST ('A'); +----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+ | id | select_type | table    | partitions | type     | possible_keys | key        | key_len | ref   | rows | filtered | Extra                         | +----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+ |  1 | SIMPLE      | students | NULL       | fulltext | first_name    | first_name | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted | +----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+ 

Có thể thấy câu truy vấn đã được tối ưu khi chỉ phải duyệt 1 rows chứ không phải duyệt toàn bộ cả bảng như trước nữa.

5. Tránh sử dụng các cột đã đánh index với function

Cùng xem xét câu truy vấn sau

mysql> select count(*) from orders where YEAR(finished_at) = ‘2018’ 

Chúng ta sử dụng function YEAR cùng với cột finished_at nó sẽ không cho phép database sử dụng index ở cột finished_at bởi vì index giá trị của finished_at chứ không phải YEAR(finished_at).
Để có thể tránh được điều này ta có thể sử dụng index cho function bằng generated columns
Hoặc một cách khác là tìm cách để viết lại câu truy vấn tương đương mà không phải sử dụng đến function

mysql> select count(*) from orders where finished_at >= '2018-01-01' and finished_at < '2019-01-01' 

6. Tối ưu database schema

Tối ưu hóa kiểu dữ liệu

MySQL support nhiều kiểu dữ liệu khác nhau : integer, float, double, date, date_time, Varchar, text ... Khi thiết kế bảng ta nên tuân theo nguyên tắc "shorter is always better"
Ví dụ khi thiết kế bảng user chỉ chứa nhỏ hơn 100 bản ghi, chúng ta nên sử dụng 'TINYINT' cho cột user_id. Hay trong trường hợp cột liên quan đến ngày tháng (order_date) thì sử dụng kiểu là date_time sẽ hợp lý nhất vì ta sẽ không viết thêm gì để convert. Cột nào cần là số thì nên để là kiểu dạng là số như integer thay vì là char, varchar vì MySQL sẽ tính toán tốt hơn trong trường hợp là kiểu số nếu so sánh với varchar.

Tránh null value

Ta nên tránh sử lưu null value trong bảng dữ liệu vì có thể sẽ đem lại kết quả không mong muốn. Ví dụ trong trường hợp tính toán tiền của order mà một cột nào đó chứa null thì có thể dẫn đến kết quả không mong muốn mà ta phải sử dụng thêm điều kiện "if not null". Trong trường hợp này hãy xem xét đến sử dụng các giá trị default cho value.

7. Sử dụng MySQL query caching

Nếu hệ thống sử dụng nhiều câu lệnh select, ta có thể xem xét sử dụng mysql query caching sẽ làm tăng tốc độ đọc của hệ thống. Tuy nhiên trong trường hợp cần update nhiều thì có thể làm giảm hiệu suất hiệu năng. Có thể check nếu Mysql server sử dụng query cache hay không bằng cách chạy câu lệnh

mysql> show variables like 'have_query_cache'; +------------------+-------+ | Variable_name    | Value | +------------------+-------+ | have_query_cache | YES   | +------------------+-------+ 1 row in set (0.00 sec) 

Seting mysql query cache

• Ta có thể setting mysql query cache bằng cách edit file '/etc/mysql/my.cnf' hoặc '/etc/mysql/mysql.conf.d/mysqld.cnf'. Để check value của query cache ta sử dụng command

mysql> show variables like 'query_cache_%' ; +------------------------------+----------+ | Variable_name                | Value    | +------------------------------+----------+ | query_cache_limit            | 1048576  | | query_cache_min_res_unit     | 4096     | | query_cache_size             | 16777216 | | query_cache_type             | OFF      | | query_cache_wlock_invalidate | OFF      | +------------------------------+----------+ 5 rows in set (0.00 sec) 

Có thể thay đổi giá trị trên bằng cách thay đổi trong file config

query_cache_type=1 // bật query cache nếu đang off query_cache_size = 10M // default là 1MB lý tưởng sẽ set trong khoảng 1MB -> 10MB query_cache_limit=256k // default là 1MB giá trị này sẽ điều khiển số lượng kết quả query được cache 

8. Các lưu ý khác

Chỉ select các cột thật sự cần thiết

Thay vì sử dụng SELECT * thì ta chỉ nên select các cột mà cần sử dụng, điều này sẽ giúp hiệu suất câu truy vấn tăng lên trong quá trình làm việc.

Dùng inner join thay vì outer join khi có thể

Sử dụng outer join quá nhiều sẽ làm hiệu suất câu truy vấn giảm đi đáng kể thay vì dùng với inner join, trong các trường hợp tương đương nhau hãy sử inner join thay vì outer join

Dùng DISTINCT và UNION chỉ khi cần

Khi sử dụng union và distinct trong trường hợp không cần thiết có thể dẫn đến giảm performance của câu truy vấn. Thay vì sử dụng UNION có thể sử dụng UNION ALL sẽ cho kết quả tốt hơn.

Tránh sử dụng điều kiện với loại khác kiểu

Ví dụ khi so sánh bằng với where một bên là kiểu varchar và kiểu số thì index sẽ không có tác dụng do sẽ phải cast ngầm kiểu để đồng nhất dữ liệu. Trong trường hợp này ta cố gắng để kiểu so sánh giống nhau ngay từ đầu sẽ cho kết quả tốt hơn.

Nguồn

https://www.eversql.com/sql-performance-tuning-tips-for-mysql-query-optimization/
https://www.oreilly.com/library/view/high-performance-mysql/9780596101718/ch04.html
https://www.alibabacloud.com/blog/how-to-optimize-mysql-queries-for-speed-and-performance-on-alibaba-cloud-ecs_593872
https://www.cloudways.com/blog/mysql-performance-tuning/#wild