Tối ưu hóa truy vấn SQL và MariaDB trên VPS

Trong hướng dẫn này, chúng ta sẽ thảo luận về một số biện pháp tối ưu hóa truy vấn SQL và MariaDB. Đây là những lựa chọn phổ biến cho hệ thống quản lý cơ sở dữ liệu. Cả hai đều sử dụng ngôn ngữ truy vấn SQL để nhập và truy vấn dữ liệu.

Mặc dù các truy vấn SQL là các lệnh đơn giản dễ học, nhưng không phải tất cả các truy vấn và chức năng cơ sở dữ liệu hoạt động với cùng hiệu suất. Điều này trở nên ngày càng quan trọng khi lượng thông tin bạn lưu trữ tăng lê. Và trường hợp cơ sở dữ liệu của bạn đang hỗ trợ một trang web khác, khi trang web trở nên phổ biến hơn.

Tổng quát về thiết kế bảng

Một trong những cách cơ bản nhất để cải thiện tối ưu hóa truy vấn SQL bắt đầu từ thiết kế cấu trúc bảng chính nó. Bạn cần bắt đầu xem xét cách tốt nhất để tổ chức dữ liệu trước khi sử dụng phần mềm.

Dưới đây là một số câu hỏi mà bạn nên đặt cho chính mình:

Bảng của bạn sẽ được sử dụng như thế nào?

Dự đoán cách bạn sử dụng dữ liệu trong bảng sẽ giúp xác định thiết kế cấu trúc dữ liệu tốt nhất.

Nếu bạn thường xuyên cập nhật một số phần dữ liệu, tốt nhất nên để chúng trong một bảng riêng. Nếu không, bộ nhớ cache truy vấn, một bộ nhớ cache nội bộ được duy trì trong phần mềm, sẽ được xóa và xây dựng lại nhiều lần vì nó nhận ra có thông tin mới. Nếu điều này xảy ra trong một bảng riêng biệt, các cột khác vẫn có thể tận dụng bộ nhớ cache.

Các hoạt động cập nhật, nói chung sẽ nhanh hơn trên các bảng nhỏ. Trong khi phân tích sâu về dữ liệu phức tạp thường là một nhiệm vụ tốt nhất được giao cho các bảng lớn, vì các hoạt động kết hợp có thể tốn kém.

Loại dữ liệu nào được yêu cầu?

Đôi khi, việc cung cấp một số ràng buộc cho kích thước dữ liệu từ đầu có thể giúp bạn tiết kiệm thời gian đáng kể.

Ví dụ, nếu có một số lượng giới hạn các giá trị hợp lệ cho một trường cụ thể mà nhận các giá trị chuỗi, bạn có thể sử dụng kiểu "enum" thay vì "varchar". Kiểu dữ liệu này rất gọn nhẹ và nhanh chóng để truy vấn.

Ví dụ, nếu bạn chỉ có một số loại người dùng khác nhau, bạn có thể tạo cột đó là "enum" với các giá trị có thể: admin, moderator, poweruser, user.

Các cột bạn sẽ truy vấn?

Biết trước các trường mà bạn sẽ truy vấn lặp đi lặp lại có thể cải thiện đáng kể tốc độ của bạn.

Tạo chỉ mục cho các cột mà bạn dự định sử dụng để tìm kiếm rất hữu ích. Bạn có thể thêm chỉ mục khi tạo bảng bằng cú pháp sau:

 CREATE TABLE example_table ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(50), address VARCHAR(150), username VARCHAR(16), PRIMARY KEY (id), <span class=“highlight”>INDEX (username)</span> );

Điều này sẽ hữu ích nếu chúng ta biết rằng người dùng của chúng ta sẽ tìm kiếm thông tin theo tên người dùng. Điều này sẽ tạo ra một bảng với các thuộc tính sau:

explain example_table; </pre> <pre> ±---------±-------------±-----±----±--------±---------------+ | Field | Type | Null | Key | Default | Extra | ±---------±-------------±-----±----±--------±---------------+ | id | int(11) | NO | <span class=“highlight”>PRI</span> | NULL | auto_increment | | name | varchar(50) | YES | | NULL | | | address | varchar(150) | YES | | NULL | | | username | varchar(16) | YES | <span class=“highlight”>MUL</span> | NULL | | ±---------±-------------±-----±----±--------±---------------+ 4 rows in set (0.00 sec)

Như bạn có thể thấy, chúng ta có hai chỉ mục cho bảng của chúng ta. Chỉ mục đầu tiên là khóa chính, trong trường hợp này là trường id. Chỉ mục thứ hai là chỉ mục chúng ta đã thêm cho trường username. Điều này sẽ cải thiện các truy vấn sử dụng trường này.

Mặc dù từ một góc độ khái niệm, việc nghĩ về những trường nên được tạo chỉ mục trong quá trình tạo bảng là hữu ích, nhưng cũng rất đơn giản để thêm chỉ mục vào các bảng hiện có. Bạn có thể thêm chỉ mục như sau:

 CREATE INDEX <span class=“highlight”>index_name</span> ON <span class=“highlight”>table_name</span>(<span class=“highlight”>column_name</span>);

Một cách khác để thực hiện điều tương tự là:

ALTER TABLE <span class=“highlight”>table_name</span> ADD INDEX ( <span class=“highlight”>column_name</span> );

Sử dụng Explain tìm điểm tạo chỉ mục trong truy vấn

Nếu chương trình của bạn truy vấn theo cách rất dễ dự đoán, bạn nên phân tích các truy vấn của mình để đảm bảo chúng đang sử dụng chỉ mục. Điều này dễ dàng với chức năng explain.

Nhập một cơ sở dữ liệu mẫu MySQL để kiểm tra hoạt động:

wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar xjvf employees_db-full-1.0.6.tar.bz2
cd employees_db
mysql -u root -p -t < employees.sql

Đăng nhập lại vào MySQL để chạy một số truy vấn:

mysql -u root -p
use employees;

Đầu tiên, cần chỉ định MySQL không sử dụng bộ nhớ cache của nó, điều này giúp đánh giá chính xác thời gian hoàn thành các tác vụ này:

SET GLOBAL query_cache_size = 0;
SHOW VARIABLES LIKE "query_cache_size";

+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_size | 0 |
+------------------+-------+
1 row in set (0.00 sec)

Bây giờ, chúng ta có thể chạy một truy vấn đơn giản trên một tập dữ liệu lớn:

SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----------+
| count(*) |
+----------+
| 588322 |
+----------+
1 row in set (0.60 sec)

Để xem MySQL thực hiện truy vấn như thế nào, bạn có thể thêm từ khóa explain trực tiếp trước truy vấn:

EXPLAIN SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | salaries | ALL | NULL | NULL | NULL | NULL | 2844738 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Nếu nhìn vào trường key, bạn sẽ thấy giá trị NULL. Điều này nghĩa là không có chỉ mục nào được sử dụng cho truy vấn này.

Thêm một chỉ mục và chạy truy vấn lại để kiểm tra liệu nó có tăng tốc hay không:

ALTER TABLE salaries ADD INDEX ( salary );
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;

+----------+
| count(*) |
+----------+
| 588322 |
+----------+
1 row in set (0.14 sec)

Điều này giúp cải thiện đáng kể hiệu suất truy vấn.

Một quy tắc chung khác khi sử dụng chỉ mục là chú ý đến việc liên kết các bảng. Bạn nên tạo chỉ mục và xác định cùng loại dữ liệu cho bất kỳ cột nào sẽ được sử dụng để kết hợp các bảng.

Ví dụ, nếu bạn có một bảng có tên "cheeses" và một bảng có tên "ingredients", bạn có thể kết hợp dựa trên một trường ingredient_id tương tự trong mỗi bảng, có thể là một số nguyên (INT).

Sau đó, chúng ta có thể tạo chỉ mục cho cả hai trường này và các kết hợp của chúng sẽ nhanh hơn.

Tối ưu hóa truy vấn SQL cho tốc độ

Nửa còn lại của phương trình khi cố gắng tăng tốc truy vấn là tối ưu hóa các truy vấn chính mình. Một số hoạt động tốn nhiều tài nguyên tính toán hơn các hoạt động khác. Thường có nhiều cách để đạt được cùng kết quả, trong đó có những cách tránh các hoạt động tốn kém.

Tùy thuộc vào mục đích sử dụng kết quả truy vấn, bạn có thể chỉ cần một số hạn chế về số lượng kết quả. Ví dụ, nếu bạn chỉ cần tìm hiểu liệu có ai trong công ty nhận lương dưới 40.000 đô la hay không, bạn có thể sử dụng:

SELECT * FROM SALARIES WHERE salary < 40000 LIMIT 1;

+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10022 | 39935 | 2000-09-02 | 2001-09-02 |
+--------+--------+------------+------------+
1 row in set (0.00 sec)

Truy vấn này thực hiện rất nhanh vì nó gần như bỏ qua khi gặp kết quả đầu tiên.

Nếu các truy vấn của bạn sử dụng so sánh "hoặc" và các phần thành phần đang kiểm tra các trường khác nhau, truy vấn của bạn có thể dài hơn cần thiết.

Ví dụ, nếu bạn đang tìm kiếm một nhân viên có tên đầu tiên hoặc họ bắt đầu bằng "Bre", bạn sẽ phải tìm kiếm hai cột riêng biệt.

SELECT * FROM employees WHERE last_name like 'Bre%' OR first_name like 'Bre%';

Thao tác này có thể nhanh hơn nếu chúng ta thực hiện tìm kiếm cho các tên đầu tiên trong một truy vấn, thực hiện tìm kiếm cho các họ trùng khớp trong một truy vấn khác, sau đó kết hợp kết quả. Chúng ta có thể làm điều này với toán tử union:

SELECT * FROM employees WHERE last_name like 'Bre%' UNION SELECT * FROM employees WHERE first_name like 'Bre%';

Trong một số trường hợp, MySQL sẽ tự động sử dụng toán tử union. Ví dụ trên thực tế là một trường hợp mà MySQL sẽ tự động làm điều này. Bạn có thể xem xem điều này có phải là trường hợp hay không bằng cách kiểm tra loại sắp xếp đang được thực hiện bằng cách sử dụng explain một lần nữa.

Kết luận

Như vậy, chúng tôi đã hoàn thành hướng dẫn tối ưu hóa truy vấn MySQL và MariaDB. Có nhiều cách để điều chỉnh chi tiết bảng và cơ sở dữ liệu MySQL và MariaDB. Bài viết này chỉ hướng dẫn một số mẹo nhỏ hữu ích để giúp bạn mới bắt đầu.

Mọi người cũng tìm kiếm: tăng tốc độ truy vấn sql, tối ưu truy vấn sql, tối ưu sql, truy vấn phổ biến nhất, tối ưu mysql, tối ưu query sql