Ngôn ngữ truy vấn có cấu trúc [SQL] là ngôn ngữ truy vấn có cấu trúc được sử dụng để lưu trữ, thao tác và truy xuất dữ liệu từ cơ sở dữ liệu. Tất cả các hệ thống quản lý cơ sở dữ liệu quan hệ [RDMS] như MySQL, MS Access, Oracle, Sybase, Informix, Postgres và SQL Server đều sử dụng SQL làm ngôn ngữ cơ sở dữ liệu tiêu chuẩn. Bằng cách sử dụng các câu lệnh SQL, các nhà phát triển dễ dàng tạo, cập nhật và xóa dữ liệu. Khi khối lượng dữ liệu tăng lên và công nghệ ngày càng phức tạp, việc tối ưu hóa cơ sở dữ liệu MySQL đúng cách để nâng cao trải nghiệm người dùng và giảm chi phí cơ sở hạ tầng trở nên quan trọng hơn
Với sự phức tạp của dữ liệu khối ngày càng tăng và các thao tác công việc thay đổi liên tục, thì việc điều chỉnh hiệu suất cơ sở dữ liệu và tối ưu hóa lệnh truy vấn MySQL rất cần thiết để tối đa hóa việc sử dụng tài nguyên và hiệu suất hệ thống
Có nhiều lý do khiến việc điều chỉnh SQL trở nên phức tạp đối với các nhà phát triển. Thứ nhất, nó yêu cầu chuyên môn kỹ thuật sâu rộng để viết và hiểu các lệnh thực thi khác nhau. Trong khi viết các câu lệnh SQL sạch sẽ và hoàn thành là trách nhiệm của người có kiến thức định hướng về nó
Bên cạnh những khu phức hợp đó, việc điều chỉnh là rất tốn thời gian. Bởi vì khi bạn có số lượng lớn các câu lệnh SQL để sắp xếp, bạn cần chắc chắn tìm ra câu lệnh nào bạn phải điều chỉnh và câu lệnh nào bạn nên để lại
Trong bài viết này, mình sẽ đề cập đến một số mẹo điều chỉnh hiệu suất của MySQL để tối ưu hóa các thao tác với cơ sở dữ liệu MySQL
Các lợi ích của công việc điều chỉnh MySQL Performance
Lợi ích của việc xác định các phần ảnh hưởng tốc độ của cơ sở dữ liệu giúp bạn tránh cung cấp quá mức và giảm chi phí bằng cách chọn đúng kích thước máy chủ. Nó giúp bạn nhận ra việc có hay không. Chuyển đổi data storage hoặc thêm server để cải thiện tốc độ hoặc không, và nếu như vậy thì bao nhiêu cũng là đủ
Điều chỉnh cơ sở dữ liệu để tối ưu hóa hiệu suất truy vấn MySQL không đi kèm với các thức thức nhỏ. Tuy nhiên, một khi được điều chỉnh đúng, cơ sở dữ liệu cho kết quả hiệu quả đáng giá với các chức năng tuyệt vời. Nó không chỉ làm giảm tải nhiệm vụ không mong muốn mà còn tối ưu hóa cơ sở dữ liệu MySQL để truy xuất dữ liệu nhanh hơn
Nguyên tắc tối ưu hóa truy vấn MySQL
Thực hiện theo các hướng dẫn sau sẽ điều chỉnh hiệu suất của MySQL và tối ưu hóa tốc độ truy vấn cơ sở dữ liệu
Trước hết, chắc chắn lập chỉ mục của tất cả các vị từ trong mệnh đề WHERE, JOIN, ORBER BY và GROUP BY. WebSphere Commerce nhấn mạnh vào việc lập chỉ mục của các vị từ để tăng hiệu năng SQL. Bởi vì việc lập chỉ mục các truy vấn SQL sai có thể gây ra quét cả bảng, cuối cùng dẫn đến các vấn đề khóa và các vấn đề khác
Do đó, ta nên lập chỉ mục cho tất cả các cột vị ngữ vì như thế sẽ tối ưu hóa truy vấn MySQL
- Cannot use functions in predicates
Cơ sở dữ liệu không sử dụng chỉ mục nếu nó có một số chức năng được xác định trước trong cột
Ví dụ
SELECT * FROM TABLE1 WHERE UPPER[COL1]='ABC'Copy
Thực hiện chức năng UPPER[], cơ sở dữ liệu không sử dụng chỉ mục trên COL1. Nếu có bất kỳ cách nào để tránh chức năng đó trong SQL, bạn phải tạo một chỉ mục dựa trên chức năng mới hoặc phải tùy chỉnh các cột trong cơ sở dữ liệu để cải thiện hiệu suất
- Left to use the character % at the first question
Ví dụ trong trường hợp sau LIKE '%abc' gây quét toàn bộ bảng
SELECT * FROM TABLE1 WHERE COL1 LIKE '%ABC'Copy
Trong hầu hết các trường hợp, việc sử dụng các ký tự % này gây ra hiệu suất giới hạn lớn
- SELECT các cột không cần thiết
Thay vì viết "SELECT * " , thì hãy luôn chỉ định các cột trong mệnh đề SELECT để cải thiện hiệu suất của MySQL. Bởi vì các cột SELECT không cần thiết gây ra tải thêm nhiều dữ liệu trên cơ sở dữ liệu, làm chậm hiệu suất của MySQL cũng như toàn bộ quy trình hệ thống
- Sử dụng kết nối bên trong, thay thế kết nối bên ngoài nếu có thể
Chỉ sử dụng kết nối bên ngoài khi cần thiết. Việc sử dụng nó khi không cần thiết không chỉ gây ra giới hạn hiệu suất cơ sở dữ liệu mà còn giới hạn các tùy chọn tối ưu hóa truy vấn MySQL, dẫn đến việc thực thi các câu lệnh SQL chậm hơn
- Chỉ sử dụng DISTINCT và UNION khi cần thiết
Việc sử dụng UNION và DISTINCT mà không có bất kỳ mục đích chính nào gây ra sự sắp xếp không mong muốn và làm chậm quá trình thực thi SQL. Thay vì UNION, sử dụng UNION ALL mang lại hiệu quả cao hơn trong quy trình và cải thiện hiệu suất chính xác hơn của MySQL
- Việc sử dụng ORDER BY để sắp xếp kết quả trả về
ORDER BY sắp xếp kết quả tập tin theo cột được chỉ định. Mặc dù câu lệnh mang lại lợi ích trong việc lấy dữ liệu được sắp xếp, nhưng nó cũng tạo ra tác động hiệu năng bit trong thực thi SQL. Bởi vì truy vấn trước tiên cần sắp xếp dữ liệu để tạo kết quả cuối cùng của tập tin, gây ra một chút phức tạp trong quá trình thực thi SQL
Không sử dụng MySQL làm Queue
Hàng đợi thực sự có thể ảnh hưởng đến hiệu suất cơ sở dữ liệu của bạn ngay từ trong lõi và có thể nhập vào cơ sở dữ liệu ứng dụng mà bạn không biết. Không giới hạn, nếu bạn đang thiết lập trạng thái cho một mặt hàng cụ thể thì một quy trình có liên quan có thể truy cập vào đó, bạn vô tình tạo ra một hàng đợi. Những gì nó thực sự làm là ngốn thêm thời gian tải để truy cập tài nguyên mà không có bất kỳ lý do chính nào cả
Hàng đợi gây ra vấn đề vì hai lý do chính. Chúng tuần tự hóa khối lượng công việc của bạn, ngăn chặn việc hoàn thành các nhiệm vụ theo bài hát và kết quả là thường có một bảng chứa các công việc cũng được xử lý như dữ liệu lịch sử xử lý từ các công việc đã hoàn thành. Nó không chỉ bổ sung tốc độ cho ứng dụng mà còn gây thêm trở ngại cho việc điều chỉnh hiệu suất MySQL
Hiểu thêm 4 tài nguyên cơ bản
You must have a resource resource to create database function. CPU, đĩa, bộ nhớ và mạng. Nếu bất kỳ một trong các tài nguyên này không hoạt động chính xác, nó sẽ ảnh hưởng đến máy chủ cơ sở dữ liệu và dẫn đến hiệu suất kém
Để hiểu đúng các tài nguyên cơ bản, bạn cần tập trung vào hai lĩnh vực cụ thể, đó là chọn đúng phần cứng và xử lý sự cố với nó
Luôn đảm bảo sử dụng các thành phần hiệu suất toàn diện khi chọn phần cứng cho cơ sở dữ liệu MySQL. Không chỉ lựa chọn tốt nhất các ngăn xếp, mà còn chắc chắn rằng cần phải có sự cân bằng phù hợp giữa chúng. Chúng ta thường thấy rằng các doanh nghiệp có xu hướng chọn máy chủ có CPU nhanh và đĩa lớn, nhưng họ nhầm lẫn với bộ nhớ bị bỏ đói cuối cùng sẽ giết chết hiệu suất
Trong một số vấn đề, việc bổ sung bộ nhớ trở nên rất đáng kể để cải thiện hiệu suất. Nó trông có vẻ hơi trực quan, nhưng trong hầu hết các trường hợp, việc sử dụng quá mức đĩa ảnh ảnh hưởng trực tiếp đến hiệu suất cơ sở dữ liệu. Do thiếu vùng nhớ để lưu dữ liệu máy chủ, gây ra các tác nhân làm giảm hiệu suất cơ sở dữ liệu
Khi nói đến giải quyết sự cố, luôn luôn kiểm tra hiệu suất của cả bốn loại tài nguyên cơ bản. Xác nhận một cách xác định rằng họ đang thực hiện theo sự cải thiện như cầu trong các tiêu đề duy nhất. Đưa công việc kiểm toán này vào xem xét thông thường sẽ nhanh chóng giải quyết các vấn đề phát sinh
Khi nói đến giải quyết sự cố, luôn luôn kiểm tra hiệu suất của cả bốn loại tài nguyên cơ bản. Xác nhận một cách xác định rằng các tài nguyên này đang thực sự ổn định. Đưa công việc kiểm toán này vào xem xét thông thường sẽ nhanh chóng giải quyết các vấn đề phát sinh
Truy vấn phân trang
Các trang web có phân trang sẽ làm máy chủ chậm lại. Khi hiển thị cho bạn một kết quả trang, với một liên kết để đến trang tiếp theo, các ứng dụng này được nhóm thông thường và sắp xếp theo cách không thể sử dụng chỉ mục và chúng sử dụng hàm LIMIT và offset khiến máy chủ thực hiện tạo
Bạn có thể thấy tối ưu hóa trong giao diện người dùng chính. Thay vì hiển thị số trang chính xác trong kết quả và liên kết đến trang riêng lẻ, bạn chỉ có thể hiển thị một liên kết đến trang tiếp theo. Bạn cũng có thể ngăn mọi người đến các trang không liên quan
Về phía truy vấn, thay vì sử dụng GIỚI HẠN với phần bù, bạn có thể chọn thêm một hàng phù hợp với nhu cầu của mình và khi người dùng nhấp vào liên kết "Trang tiếp theo", bạn có thể chỉ định hàng cuối cùng để làm điểm. . Ví dụ. nếu người dùng đã xem một trang có hàng từ 101 đến 120, bạn cũng sẽ phải chọn hàng 121;
Tối ưu hóa các MySQL Subqueries
Các truy vấn con là đối tượng của công việc tối ưu hóa bởi nhóm tối ưu hóa và các phiên bản sắp tới của MySQL có thể có nhiều truy vấn con tối ưu hóa. Hãy kiểm tra xem những tối ưu hóa nào sẽ kết thúc trong mã được phát hành và các mức độ khác biệt mà chúng sẽ tạo ra
Bộ đệm truy vấn Mysql
Một trong những khía cạnh quan trọng nhất của công việc đo lường hiệu suất là lưu trữ nội dung. MySQL cung cấp bộ nhớ đệm truy vấn cơ sở dữ liệu lưu trữ việc thực thi câu lệnh SELECT và kết quả được truy xuất. Do đó, bất cứ khi nào bạn tạo một cơ sở dữ liệu trùng lặp, bạn gọi bộ đệm truy vấn MySQL, nó sẽ trả lời bạn và hiển thị kết quả từ bộ đệm và không có thao tác nào sẽ được phân tích cú pháp. . Theo cách này, bạn có thể tối đa hóa quá trình tối ưu hóa bộ đệm của MySQL
Để thiết lập bộ đệm truy vấn MySQL, bạn phải thêm một vài cài đặt vào MySQL. Trước hết, bạn phải kiểm tra xem bộ đệm truy vấn có khả năng sử dụng hay không bằng lệnh sau
mysql> SHOW VARIABLES LIKE 'have_query_cache';
Khi kết quả trả về là YES. Điều này có nghĩa là bộ đệm MySQL hoạt động tốt
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
Bây giờ, bạn có thể thiết lập kích thước và loại bộ đệm truy vấn MySQL. Hãy nhớ kích thước mặc định tối thiểu là 40KB. Kích thước tối đa là 32 MB. Bạn có thể thiết lập MySQL query_cache_size bằng cách sử dụng lệnh sau
mysql> SET GLOBAL query_cache_size = 40000;
Gõ bộ đệm truy vấn có thể xác định tất cả các kết nối. Bạn cũng có thể tắt bộ đệm Truy vấn như sau
mysql> SET SESSION query_cache_type = OFF;
Bạn cũng có thể đặt các giá trị như 0,1 và 2 để thiết lập trạng thái kết nối
Use Memcached for MySQL Caching
Memcached là một hệ thống lưu trữ bản sao các đối tượng [object] và dữ liệu được truy cập nhiều lần để tăng tốc độ truy xuất độc lập. Mục đích chính của nó là để tăng tốc độ ứng dụng web bằng cách truy vấn cơ sở dữ liệu bộ nhớ đệm, nội dung hoặc kết quả tính toán khác
Để sử dụng memcache, bạn chỉ đơn giản là sử dụng 2 cách dưới đây
- Save the value V with key K
- Truy xuất value V with key K
Chỉ với 2 cơ chế đơn giản này, bạn có thể làm được rất nhiều việc và trả lại hiệu quả không đơn giản chút nào cho trang web của bạn. Để hiểu rõ hơn về memcached, bạn có thể đọc hướng dẫn về cách thiết lập memcache trong php
Trên đây là một số mẹo điều chỉnh Hiệu suất MySQL để tối ưu hóa cơ sở dữ liệu được mình tham khảo tại đây của Shahroze Nawaz. Hy vọng sẽ giúp ích cho các bạn khi làm việc với cơ sở dữ liệu