Cách tối ưu hóa các truy vấn và bảng trong MySQL và MariaDB trên VPS
MySQL và MariaDB là những lựa chọn phổ biến cho các hệ quản trị database . 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ù 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à hàm database đều hoạt động với hiệu quả như nhau. Điều này ngày càng trở nên quan trọng khi lượng thông tin bạn đang lưu trữ ngày càng tăng và, nếu database của bạn đang hỗ trợ một trang web, khi mức độ phổ biến của trang web tăng lên.
Trong hướng dẫn này, ta sẽ thảo luận về một số biện pháp đơn giản mà bạn có thể thực hiện để tăng tốc các truy vấn MySQL và MariaDB của bạn . Ta sẽ giả định bạn đã cài đặt MySQL hoặc MariaDB bằng cách sử dụng một trong các hướng dẫn của ta phù hợp với hệ điều hành của bạn.
Khái 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ốc độ truy vấn bắt đầu bằng chính thiết kế cấu trúc bảng. Điều này nghĩa là bạn cần bắt đầu xem xét cách tốt nhất để tổ chức dữ liệu của bạn trước khi bắt đầu sử dụng phần mềm.
Đây là một số câu hỏi mà bạn nên tự hỏi:
Bảng của bạn sẽ được sử dụng chủ yếu như thế nào?
Dự đoán cách bạn sẽ sử dụng dữ liệu của bảng thường chỉ ra cách tiếp cận tốt nhất để thiết kế cấu trúc dữ liệu.
Nếu bạn thường xuyên cập nhật một số phần dữ liệu nhất định, thì tốt nhất bạn nên có những phần đó trong bảng riêng của chúng. Không làm được điều này có thể khiến cache truy vấn, cache bên trong được duy trì trong phần mềm, bị kết xuất và xây dựng lại nhiều lần vì nó nhận ra rằng 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 có thể tiếp tục tận dụng bộ nhớ cache.
Nói chung, các thao tác cập nhật nhanh hơn trên các bảng nhỏ hơn, trong khi phân tích chuyên sâu dữ liệu phức tạp thường là nhiệm vụ tốt nhất nên chuyển sang các bảng lớn, vì các phép nối có thể là các thao tác tốn kém.
Loại dữ liệu nào được yêu cầu?
Đôi khi, nó có thể giúp bạn tiết kiệm thời gian đáng kể về lâu dài nếu bạn có thể đưa ra một số hạn chế cho kích thước dữ liệu của bạn .
Ví dụ: nếu có một số lượng hạn chế các mục nhập hợp lệ cho một trường cụ thể 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 nhỏ gọn và do đó truy vấn nhanh chóng.
Ví dụ: nếu bạn chỉ có một số loại user khác nhau, bạn có thể tạo cột xử lý “enum” đó với các giá trị có thể có: admin, moderator, poweruser, user.
Bạn sẽ Truy vấn Cột nào?
Biết trước những trường bạn sẽ truy vấn lặp lại có thể cải thiện đáng kể tốc độ của bạn.
Lập index các cột mà bạn muốn sử dụng để tìm kiếm sẽ giúp ích rất nhiều. Bạn có thể thêm index khi tạo bảng bằng cú pháp sau:
<pre>
Bảng mẫu CREATE TABLE (
id INTEGER NOT NULL AUTO INCREMENT,
tên VARCHAR (50),
địa chỉ VARCHAR (150),
tên user VARCHAR (16),
KHÓA CHÍNH (id),
<span class = “highlight”> INDEX (tên user ) </span>
);
</pre>
Điều này sẽ hữu ích nếu ta biết rằng user của ta sẽ tìm kiếm thông tin bằng tên user . Thao tác này sẽ tạo một bảng với các thuộc tính sau:
<pre>
giải thích bảng ví dụ ;
</pre>
<pre>
+ ———- + ————– + —— + —– + ——— + —————- +
| Lĩnh vực | Loại | Không có | Key | Mặc định | Thêm |
+ ———- + ————– + —— + —– + ——— + —————- +
| id | int (11) | KHÔNG | <span class = “highlight”> PRI </span> | NULL | tăng tự động |
| tên | varchar (50) | CÓ | | NULL | |
| địa chỉ | varchar (150) | CÓ | | NULL | |
| tên user | varchar (16) | CÓ | <span class = “highlight”> MUL </span> | NULL | |
+ ———- + ————– + —— + —– + ——— + —————- +
4 hàng trong bộ (0,00 giây)
</pre>
Như bạn thấy , ta có hai chỉ số cho bảng của ta .Đầu tiên là khóa chính, trong trường hợp này là trường id . Thứ hai là index 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ù việc suy nghĩ về những trường nào nên được lập index trong quá trình tạo là hữu ích, nhưng việc thêm các index vào các bảng đã có trước cũng rất đơn giản. Bạn có thể thêm một cái như thế này:
<pre>
TẠO CHỈ SỐ <span class = “highlight”> tên index </span> BẬT <span class = “highlight”> tên bảng </span> (<span class = “highlight”> column_name </span>);
</pre>
Một cách khác để đạt được điều tương tự là:
<pre>
ALTER TABLE <span class = “highlight”> tên bảng </span> THÊM INDEX (<span class = “highlight”> tên cột </span>);
</pre>
Sử dụng Giải thích để tìm điểm cần lập index trong truy vấn
Nếu chương trình của bạn đang truy vấn theo cách rất dễ đoán, bạn nên phân tích các truy vấn của bạn đảm bảo rằng chúng đang sử dụng các index khi nào có thể. Điều này thật dễ dàng với chức năng explain .
Ta sẽ nhập database mẫu MySQL để xem một số database này hoạt động như thế nào:
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 Bây giờ ta có thể đăng nhập lại vào MySQL để có thể chạy một số truy vấn:
mysql -u root -p use employees; Trước tiên, ta cần chỉ rõ rằng MySQL không nên sử dụng bộ nhớ cache của nó, để ta có thể đánh giá chính xác thời gian hoàn thành các việc 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ờ, 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 cách MySQL thực thi truy vấn, 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 bạn nhìn vào trường key , bạn sẽ thấy rằng giá trị của nó là NULL . Điều này nghĩa là không có index nào được sử dụng cho truy vấn này.
Hãy thêm một và chạy lại truy vấn để xem nó có tăng tốc 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) Như bạn thấy , điều này cải thiện đáng kể hiệu suất truy vấn của ta .
Một luật chung khác để sử dụng với các chỉ số là chú ý đến các phép nối bảng. Bạn nên tạo các index và chỉ định cùng một kiểu dữ liệu trên bất kỳ cột nào sẽ được sử dụng để nối các bảng.
Ví dụ: nếu bạn có một bảng được gọi là "pho mát" và một bảng có tên "thành phần", bạn có thể cần tham gia vào một trường thành phần_id tương tự trong mỗi bảng, có thể là INT.
Sau đó, ta có thể tạo các index cho cả hai trường này và các phép nối của ta sẽ tăng tốc.
Tối ưu hóa truy vấn cho tốc độ
Một 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 chính các truy vấn. Một số hoạt động nhất định chuyên sâu hơn về mặt tính toán so với những hoạt động khác. Thường có nhiều cách để đạt được cùng một kết quả, một số cách sẽ tránh được các hoạt động tốn kém.
Tùy thuộc vào những gì bạn đang sử dụng kết quả truy vấn, bạn có thể chỉ cần một số kết quả giới hạn. Ví dụ: nếu bạn chỉ cần tìm xem có ai trong công ty kiếm được ít hơn 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 thi cực nhanh vì về cơ bản nó ngắn mạch ở kết quả dương tính đầu tiên.
Nếu truy vấn của bạn sử dụng so sánh “hoặc” và hai phần thành phần đang kiểm tra các trường khác nhau, thì truy vấn của bạn có thể dài hơn mức cần thiết.
Ví dụ: nếu bạn đang tìm kiếm một nhân viên có họ hoặc tên 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 ta thực hiện tìm kiếm tên trong một truy vấn, thực hiện tìm kiếm họ phù hợp trong một truy vấn khác, sau đó kết hợp kết quả . Ta có thể làm điều này với nhà điều hành công đoàn:
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 hoạt động liên hợp. Ví dụ trên thực sự là một trường hợp mà MySQL sẽ tự động làm điều này. Bạn có thể xem liệu đâ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 lại.
Kết luận
Có rất nhiều cách đặc biệt mà bạn có thể tinh chỉnh các bảng và database MySQL và MariaDB của bạn tùy theo trường hợp sử dụng của bạn. Bài viết này chỉ chứa một số mẹo có thể hữu ích để giúp bạn bắt đầu.
Các hệ thống quản lý database này có tài liệu tuyệt vời về cách tối ưu hóa và tinh chỉnh các tình huống khác nhau. Các chi tiết cụ thể phụ thuộc rất nhiều vào loại chức năng bạn muốn tối ưu hóa, nếu không, chúng sẽ được tối ưu hóa hoàn toàn ngay từ đầu. Khi bạn đã củng cố các yêu cầu của bạn và nắm được các thao tác sẽ được thực hiện lặp đi lặp lại, bạn có thể học cách điều chỉnh cài đặt của bạn cho các truy vấn đó.
<div class = “author”> Bởi Justin Ellingwood </div>
Các tin liên quan
Cách bảo mật sao chép MySQL bằng SSH trên VPS2013-09-18
Cách tạo và quản lý database trong MySQL và MariaDB trên server cloud
2013-07-29
Cách thực hiện các truy vấn cơ bản trong MySQL và MariaDB trên server cloud
2013-07-29
Cách cài đặt Etherpad cho Sản xuất với Node.js và MySQL trên VPS
2013-07-26
Cách bảo mật database MySQL và MariaDB trong VPS Linux
2013-07-23
Chuyển sang MariaDB từ MySQL
2013-07-18
Cách di chuyển database MySQL giữa hai server
2012-08-09
Cách thiết lập Master Slave Replication trong MySQL
2012-07-25
Cách nhập và xuất database và đặt lại mật khẩu gốc trong MySQL
2012-06-12
Hướng dẫn MySQL Cơ bản
2012-06-12
