Hướng dẫn how to make query faster in mysql - cách làm cho truy vấn nhanh hơn trong mysql

Đây là một cái nhìn nhanh chóng và toàn diện về cách cải thiện hiệu suất truy vấn MySQL theo những cách đơn giản

MySQL là Hệ thống quản lý cơ sở dữ liệu quan hệ nguồn mở được tìm kiếm nhiều nhất (RDBMS) sử dụng ngôn ngữ truy vấn có cấu trúc (SQL).

Một truy vấn SQL được chế tạo kém hoặc vô tổ chức hoạt động như một kẻ giết người mạnh mẽ về hiệu suất tổng thể của ứng dụng của bạn. Do đó, điều quan trọng là tối ưu hóa hiệu suất truy vấn của bạn. MySQL đi kèm với các công cụ giúp chúng tôi tối ưu hóa các truy vấn. Hãy cùng đi sâu vào các mẹo thiết yếu và hữu ích để cải thiện truy vấn MySQL về tốc độ và hiệu suất.

1. Tối ưu hóa cơ sở dữ liệu của bạn

Bạn cần biết cách thiết kế các lược đồ để hỗ trợ các truy vấn hiệu quả. Các truy vấn và lược đồ được thiết kế tốt là rất quan trọng để ứng dụng của bạn hoạt động đúng.

Tối ưu hóa các truy vấn MySQL của bạn một mình sẽ không mang lại hiệu suất cơ sở dữ liệu tuyệt vời. Một cơ sở dữ liệu có cấu trúc tốt là rất quan trọng cùng với một truy vấn được tối ưu hóa. Mặt khác, trong trường hợp tăng dữ liệu, hiệu suất cơ sở dữ liệu sẽ bị ảnh hưởng xấu.

Các chiến lược sau đây sẽ giúp bạn tối ưu hóa cơ sở dữ liệu của bạn.

một. Bình thường hóa bảng

Bình thường hóa là một kỹ thuật thiết kế cơ sở dữ liệu sắp xếp các bảng theo phong cách làm giảm sự dư thừa và sự phụ thuộc của dữ liệu. Nó chia các bảng lớn hơn thành các bảng nhỏ hơn và liên kết chúng bằng các mối quan hệ. Điều này giúp tránh các lĩnh vực sẽ là null. Nó cũng đảm bảo rằng tất cả các trường trong bảng chỉ thuộc về một miền của dữ liệu được mô tả.

Ví dụ, trong bảng nhân viên, các trường có thể là ID, tên, số an sinh xã hội, nhưng ba lĩnh vực đó không liên quan gì đến bộ phận. Chỉ ID nhân viên mô tả bộ phận mà nhân viên thuộc về. Vì vậy, điều này ngụ ý rằng bộ phận mà một nhân viên đang ở trong một bảng khác.

b. Sử dụng Tối ưu Dữ liệu TypeMysQL hỗ trợ các loại dữ liệu khác nhau và chọn loại chính xác để lưu trữ dữ liệu của bạn là rất quan trọng để có hiệu suất tốt. MySQL hỗ trợ dữ liệu của các loại số, loại ngày và thời gian và các loại chuỗi (ký tự) (số nguyên, float, gấp đôi, ngày, ngày_time, varchar, văn bản, v.v.). Các loại dữ liệu khác nhau phục vụ các mục đích khác nhau. Khi tạo bảng của bạn, bạn cần hiểu loại dữ liệu nào mà mỗi cột sẽ giữ và chọn loại dữ liệu phù hợp nhất.

MySQL supports different data types and choosing the correct type to store your data is vital to have good performance. MySQL supports data of numeric types, date and time types, and string (character) types (integer, float, double, date, date_time, Varchar, text, etc.). Different data types serve different purposes. When creating your tables you need to understand what type of data each column will hold and choose the most fitting data type.

Nếu một trường mong đợi một giá trị ngày, sử dụng kiểu dữ liệu DATE_TIME là tốt nhất vì bạn không phải chạy các hàm phức tạp để chuyển đổi trường cho đến nay khi truy xuất các bản ghi bằng SQL. Sử dụng các giá trị số nguyên nếu bạn mong đợi tất cả các giá trị là số. Khi nói đến tính toán, MySQL có thể làm tốt hơn với các giá trị số nguyên so với các loại dữ liệu văn bản như varchar (lưu trữ chuỗi ký tự có độ dài biến và là loại dữ liệu chuỗi phổ biến nhất).

Làm cho độ dài của loại dữ liệu càng nhỏ càng tốt. Ví dụ, Varchar (10) luôn hoạt động tốt hơn Varchar (255).

c. Tránh các giá trị null

Cho phép các giá trị null (không có bất kỳ giá trị nào trong một cột) trong cơ sở dữ liệu của bạn là một ý tưởng thực sự xấu trừ khi trường có thể có giá trị null một cách hợp lý. Sự hiện diện của giá trị null có thể ảnh hưởng xấu đến kết quả cơ sở dữ liệu của bạn.

Chẳng hạn, nếu bạn muốn có được tổng của tất cả các đơn đặt hàng trong cơ sở dữ liệu, kết quả dự kiến ​​có thể hoạt động tồi tệ nếu một bản ghi đơn hàng cụ thể có số tiền null. Loại hành vi sai trái này có thể không chỉ xảy ra nếu bạn đã sử dụng câu lệnh MySQL 'IFNULL' để trả về giá trị thay thế.

d. Tránh quá nhiều cột

Nhược điểm lớn nhất để có nhiều cột là thêm IO và lưu trữ trên đầu. Các bảng rộng có thể cực kỳ tốn kém và gây ra chi phí lưu trữ. Đó là lý tưởng không để vượt trên một trăm trừ khi logic kinh doanh của bạn đặc biệt đòi hỏi điều này.
Having wide tables can be extremely expensive and causes storage overhead. It is ideal no to go above a hundred unless your business logic specifically necessitates this.

Trái ngược với việc tạo ra một bảng rộng, tách nó ra thành các cấu trúc logic có thể có lợi. Giả sử bạn đang tạo ra một bảng nhân viên. Trong một số trường hợp nhất định, bạn nhận ra rằng một nhân viên có thể có nhiều địa chỉ. Sau đó, tốt hơn là tạo một bảng riêng để nhập các địa chỉ của nhân viên tham khảo bảng nhân viên bằng trường 'nhân viên_id'.

Tránh sử dụng từ khóa của**và chỉ định dữ liệu cần thiết trong mệnh đề chọn. Điều này có thể giúp giảm việc sử dụng mạng trong khi tìm nạp dữ liệu lớn.

2. Tối ưu hóa tham gia

Giảm các tuyên bố tham gia trong các truy vấn. Một tuyên bố SQL với mô hình được thiết kế kém liên quan đến rất nhiều lần nối có thể không hoạt động tốt. Một quy tắc ngón tay cái là có tối đa một tá tham gia cho mỗi truy vấn.

3. Chỉ mục tất cả các cột được sử dụng trong 'WHERE', 'Order by' và 'Group by'

Chỉ mục. Chỉ số cơ sở dữ liệu là một cấu trúc dữ liệu cải thiện tốc độ hoạt động trong bảng. Các chỉ mục có thể được tạo bằng cách sử dụng một hoặc nhiều cột, cung cấp cơ sở cho cả việc tra cứu ngẫu nhiên nhanh chóng và thứ tự hiệu quả truy cập vào hồ sơ.

Khi tạo một chỉ mục, các điều kiện bình đẳng trong các điều kiện ở đâu và tham gia là thực sự quan trọng. Chẳng hạn, các điều kiện như name = ‘John, sẽ cho phép cơ sở dữ liệu lọc hầu hết các hàng từ bảng và chạy qua một số lượng nhỏ các hàng để trả về các kết quả cần thiết. Do đó, chúng ta nên bắt đầu lập chỉ mục bằng cách thêm các cột này vào chỉ mục.

Sau đó, bạn chỉ nên thêm một trong những điều kiện phạm vi chọn lọc nhất vì MySQL có thể xử lý nhiều hơn trong số chúng. Trong một số trường hợp khi không có điều kiện phạm vi, việc thêm nhóm theo / đặt hàng là hợp lý, giả sử đặt hàng chỉ được thực hiện theo một hướng (ASC / DESC).

Nhóm theo mệnh đề được sử dụng để đặt hàng kết quả và do đó nếu: clause is used for ordering the result and hence if:

-Thứ tự chính xác của chỉ mục được sử dụng các cột ngoài cùng bên trái OR được sử dụng trong nhóm theo nhóm-cột ngoài cùng bên trái được sử dụng trong đó mệnh đề và phần còn lại theo đúng thứ tự trong nhóm theo chỉ số mệnh đề sẽ được sử dụng.
-only leftmost columns are used in group by
-the leftmost column is used in WHERE clause and rest in the correct order in GROUP BY clause index would be used.

Cố gắng áp dụng chỉ số lực nếu MySQL không sử dụng chỉ mục thích hợp. Hiệu suất của từng chỉ số có thể được quan sát bằng cách sử dụng câu lệnh giải thích.

4. Sử dụng các tìm kiếm toàn văn

Tìm kiếm toàn văn bản (FTS) MySQL nhanh hơn nhiều so với các truy vấn sử dụng ký tự đại diện. Để thêm một chỉ mục tìm kiếm toàn văn vào bảng mẫu của sinh viên, chúng tôi có thể sử dụng lệnh MySQL bên dưới:

mysql> sinh viên bảng thay đổi thêm fulltext (first_name, last_name);

mysql> chọn * từ các sinh viên nơi khớp (first_name, last_name) chống lại ('jones');

Trong ví dụ trên, chúng tôi đã chỉ định các cột mà chúng tôi muốn được khớp (First_name và Last_Name) với từ khóa tìm kiếm của chúng tôi ('Jones').

Chỉ có một hàng sẽ được quét ngay cả khi cơ sở dữ liệu của sinh viên của chúng tôi có các hàng lớn và điều này sẽ tăng tốc cơ sở dữ liệu.

5. Tối ưu hóa như tuyên bố với mệnh đề Liên minh

Hãy xem xét một tình huống khi bạn muốn chạy các truy vấn bằng toán tử so sánh 'hoặc' trên các trường hoặc cột khác nhau trong một bảng cụ thể. Ở đây, nếu bạn sử dụng từ khóa 'hoặc' quá mức trong mệnh đề WHERE, có khả năng trình tối ưu hóa MySQL có thể chọn không chính xác việc quét bảng đầy đủ để truy xuất bản ghi.

Nếu bạn có một chỉ mục có thể tối ưu hóa một bên của truy vấn và một chỉ mục khác để tối ưu hóa phía bên kia, một điều khoản liên minh có thể làm cho truy vấn chạy nhanh hơn. Ví dụ; Hãy xem xét một trường hợp bạn đang chạy truy vấn dưới đây với 'First_name' và 'Last_Name' đã được lập chỉ mục:'first_name' and 'last_name' indexed:

mysql> chọn * từ các sinh viên trong đó first_name như 'jones%' hoặc last_name như 'jones%';

Truy vấn ở trên có thể chạy chậm hơn rất nhiều so với truy vấn dưới đây sử dụng toán tử công đoàn hợp nhất kết quả của 2 truy vấn nhanh riêng biệt tận dụng các chỉ mục.

mysql> Chọn từ các sinh viên trong đó First_name như 'Jones%' Liên minh tất cả đều chọn từ các sinh viên trong đó Last_name thích '

6. Bộ nhớ đệm truy vấn MySQL

Như chúng ta đã biết, bộ nhớ đệm được sử dụng để cải thiện hiệu suất. Nó sẽ nhanh hơn trang web hoặc ứng dụng. Bộ đệm truy vấn MySQL là một bộ nhớ toàn cầu được chia sẻ giữa các phiên. Truy vấn bộ đệm lưu trữ kết quả của các truy vấn được chọn cho phép trả về truy vấn nhanh nếu nhận được truy vấn giống hệt nhau trong tương lai. Khi máy chủ cho cùng một truy vấn, MySQL hỏi sẽ lấy lại kết quả từ bộ đệm thay vì chạy truy vấn lại. Điều này thực sự sẽ buộc chặt quá trình đáng kể. Các kết quả sẽ được đặt trong bộ nhớ cache bộ nhớ như Memcached hoặc Cassandra.

Chỉ thị cấu hình

Hãy để chúng tôi kiểm tra cách bật bộ đệm truy vấn trong máy chủ MySQL.

Để cho phép bộ nhớ đệm truy vấn, chúng tôi cần thêm các chỉ thị cấu hình sau.

1.
2) query_cache_type=OPTION
query_cache_size=SIZE

Chỉ thị đầu tiên cần thiết để cho phép bộ nhớ đệm truy vấn trong các máy chủ MySQL là query query_cache_size = size.

Chỉ thị này cho phép chúng tôi đặt kích thước bộ nhớ được phân bổ cho kết quả truy vấn lưu trữ. Trong một máy chủ điển hình, giá trị mặc định cho chỉ thị này sẽ là ‘0. Nó có nghĩa là bộ đệm truy vấn bị vô hiệu hóa. Để cho phép bộ nhớ đệm truy vấn, chúng tôi cần đặt một số giá trị cho việc này. Chúng ta nên đặt giá trị theo số lượng bộ nhớ chúng ta dự định phân bổ cho bộ nhớ đệm truy vấn.

query_cache_type = Tùy chọn Chỉ thị cấu hình tiếp theo cần được đặt để bật bộ nhớ đệm truy vấn là trên query_cache_type = tùy chọn. Sử dụng chỉ thị này, chúng tôi chỉ định loại bộ đệm truy vấn nào chúng tôi đang cài đặt.
The next configuration directive that is needed to be set to enable query caching is “query_cache_type=OPTION”. Using this directive, we specify which type of query cache we are setting.

Có 3 tùy chọn có thể được đặt cho Chỉ thị này. Họ đang:

1) 0 0 0 0: - Nó cho máy chủ nói như sau: Đừng lưu trữ kết quả hoặc truy xuất kết quả từ bộ đệm truy vấn.2) . Nếu chúng ta đặt giá trị cho chỉ thị này là một, nó biểu thị rằng, để lưu trữ tất cả các kết quả truy vấn ngoại trừ các kết quả bắt đầu bằng sql_no_cache.3) Kết quả bộ đệm đó chỉ cho các truy vấn bắt đầu bằng sql_cache chọn. – It tells the server the following: Don’t cache the results in or retrieve results from the query cache.
2) The one “1” : - There are query results that start with “SQL_NO_CACHE”. If we set the value for this directive as one, it signifies that, to cache all the query results except for those that begin with SQL_NO_CACHE.
3) The two “2”:- If we set the value as “2”, it means that cache results only for queries that begin with SELECT SQL_CACHE.

Cho phép lưu trữ truy vấn trong MySQL

Chúng ta có thể thiết lập bộ đệm trong định dạng sau. Bạn cần nhập vào mysql.assume rằng, chúng tôi đang thiết lập bộ đệm truy vấn cho 32 MB. Chúng ta cần phải làm những điều sau đây để có được điều này.
Assume that, we are setting up the query cache for 32 Mb. We need to do the following to acquire this.

Bây giờ, chúng ta có thể nối các chỉ thị cấu hình khác như sau: query_cache_size = 268435456query_cache_type = 1Query_cache_limit = 1048576 Các lệnh trên ngụ ý kích thước tối đa của kết quả truy vấn riêng lẻ. Kích thước bộ nhớ được đặt trong KB.
query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576
The above commands imply that the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. The memory size is set in Kb.

Kết luận Hướng dẫn này, tôi đã chỉ cho bạn cách tối ưu hóa máy chủ MySQL của bạn. Thực hiện theo các bước này cùng với việc tạo bảng cơ sở dữ liệu có cấu trúc tốt chắc chắn sẽ cải thiện hiệu suất truy vấn MySQL của bạn.
In this guide, I have shown you how to optimize your MySQL server. Following these steps along with creating a well-structured database table will definitely improve your MySQL Query performance.

Làm thế nào tôi có thể làm cho truy vấn mysql chạy nhanh hơn?

Làm cách nào để làm cho truy vấn cơ sở dữ liệu của tôi nhanh hơn?

Làm cách nào để sửa các truy vấn chậm trong mysql?

Làm thế nào tôi có thể cải thiện hiệu suất truy vấn SQL?