Làm cách nào để truy vấn MySQL của tôi chạy nhanh hơn?

MySQL, là hệ thống quản lý cơ sở dữ liệu quan hệ cơ sở dữ liệu phổ biến nhất, đôi khi vẫn yêu cầu tối ưu hóa. Hơn thế nữa, trong trường hợp tập dữ liệu lớn và phức tạp, các hành động tối ưu hóa thường xuyên là điều cần thiết để hệ thống hoạt động hiệu quả

Tối ưu hóa hiệu suất của MySQL thường liên quan đến việc định cấu hình, lập hồ sơ và giám sát hiệu suất ở một số cấp độ. Để điều chỉnh hiệu suất của MySQL, bạn không nhất thiết phải có chuyên môn sâu rộng và hiểu sâu về SQL

Trong bài viết này, chúng tôi sẽ giới thiệu cho bạn các kỹ thuật điều chỉnh hiệu suất chính để bạn có thể đảm bảo tính ổn định, độ tin cậy và tốc độ của các ứng dụng dựa trên cơ sở dữ liệu của mình

Kiểm tra phần cứng và phần mềm được đề xuấtyêu cầu đối với MySQL

Điều đầu tiên cần làm, đặc biệt nếu bạn là chủ sở hữu của một PC cấp thấp, là kiểm tra các yêu cầu phần cứng và phần mềm tối ưu cho MySQL, vì các giới hạn phần cứng có thể ảnh hưởng đáng kể đến hiệu suất

Yêu cầu phần cứng máy chủ cơ sở dữ liệu MySQL tối thiểu (đối với phiên bản 5. 7 - 8. 0)

• bộ vi xử lý 1Ghz
• RAM 512 MB
• Không gian đĩa cứng tùy thuộc vào kích thước của cơ sở dữ liệu

Cũng cần đề cập rằng tốt hơn là sử dụng phiên bản chính thức mới nhất của MySQL nếu có thể

Tối ưu hóa việc sử dụng bộ nhớ, đĩa và CPU

Ở cấp độ phần cứng, bạn có thể thực hiện nhiều hành động để cải thiện tài nguyên phần cứng và phần mềm

Dung lượng đĩa
Nếu đang sử dụng ổ đĩa cứng (HDD) truyền thống và đang tìm kiếm một giải pháp nâng cao hiệu suất, bạn nên cân nhắc nâng cấp lên SSD. Tài liệu MySQL chính thức không biểu thị rõ ràng dung lượng đĩa hoặc cài đặt bộ nhớ cần thiết để chạy máy chủ MySQL một cách hiệu quả vì chúng chủ yếu phụ thuộc vào kích thước của cơ sở dữ liệu hoặc cơ sở dữ liệu tiềm năng. Tuy nhiên, bạn nên theo dõi hiệu suất đĩa của mình, chẳng hạn như sử dụng các công cụ hiệu suất hệ thống sar và iostat. Nếu mức sử dụng đĩa cao hơn đáng kể so với mức sử dụng các tài nguyên khác, bạn chắc chắn nên bổ sung thêm dung lượng lưu trữ hoặc nâng cấp lên dung lượng nhanh hơn

ĐẬP
Thiếu bộ nhớ cũng có thể ảnh hưởng nghiêm trọng đến hiệu suất cơ sở dữ liệu. Điều này nghe có vẻ sáo mòn, nhưng nếu máy chủ của bạn thường xuyên sắp hết bộ nhớ và hiệu suất Đĩa RAM không thỏa mãn, bạn nên bổ sung thêm bộ nhớ. Khi bạn hết RAM, máy chủ MySQL sẽ lưu bộ nhớ vật lý vào bộ nhớ cache làm chậm hiệu suất. Như vậy, việc tối ưu bộ nhớ MySQL là vô cùng quan trọng

CPU
Việc tối ưu hóa mức sử dụng CPU của MySQL nên bắt đầu bằng việc phân tích cẩn thận các quy trình MySQL đang diễn ra trên máy của bạn và phần trăm mức sử dụng bộ xử lý mà chúng yêu cầu. Nâng cấp CPU không rẻ, tuy nhiên, nếu nó bị thắt cổ chai thì việc nâng cấp là cần thiết

kết nối Internet
Mạng là một phần quan trọng của cơ sở hạ tầng MySQL và điều quan trọng là phải theo dõi và phân tích lưu lượng mạng để đảm bảo bạn có đủ tài nguyên để quản lý khối lượng công việc của mình. Đảm bảo bạn có kết nối Internet tốt và ổn định để máy chủ MySQL của bạn hoạt động bình thường

Công cụ điều chỉnh hiệu suất phần mềm

Như chúng tôi đã đề cập, bạn có thể tối ưu hóa hiệu suất của MySQL ở cấp độ phần cứng và phần mềm. Bây giờ chúng ta hãy xem điều chỉnh hiệu suất phần mềm MySQL

Điều chỉnh hiệu suất của MySQL về mặt phần mềm liên quan đến việc định cấu hình các tùy chọn máy chủ MySQL, tăng hiệu suất của các truy vấn MySQL, điều chỉnh các chỉ mục MySQL, chuyển sang công cụ lưu trữ MySQL InnoDB, v.v. Hãy xem xét tất cả những điều này một cách chi tiết

Sử dụng chỉ mục MySQL cho hiệu suất

Lập chỉ mục thích hợp để nâng cao hiệu suất không dễ dàng và đòi hỏi trình độ chuyên môn nhất định, tuy nhiên, đây là một trong những cải tiến hiệu suất tốt nhất mà bạn có thể thực hiện đối với cơ sở dữ liệu của mình

MySQL sử dụng các chỉ mục làm chỉ mục sách hoặc lộ trình để tìm nhanh các giá trị cho một truy vấn nhất định. Nếu không có chỉ mục, MySQL sẽ quét toàn bộ bảng theo từng hàng để tìm dữ liệu liên quan. Như vậy, việc tối ưu hóa chỉ mục nhằm mục đích tăng tốc độ truy xuất dữ liệu. Các chỉ mục không hiển thị cho người dùng và chứa thông tin về nơi lưu trữ dữ liệu thực tế. Cũng cần lưu ý rằng độ dài chỉ mục MySQL cho các bảng InnoDB có giới hạn tùy thuộc vào định dạng hàng

Các chỉ mục MySQL cực kỳ hữu ích cho các tập dữ liệu lớn và điều chỉnh chỉ mục là điều nên làm nếu cơ sở dữ liệu của bạn đang phát triển nhanh chóng. Các chỉ mục đặc biệt có lợi cho các hoạt động sau. tìm các hàng khớp với mệnh đề WHERE, truy xuất dữ liệu bằng THAM GIA, sắp xếp và nhóm dữ liệu với sự trợ giúp của ORDER BY và GROUP BY

Vậy tại sao không chèn càng nhiều chỉ mục càng tốt? . Vì vậy, bạn phải tìm sự cân bằng phù hợp để đạt được mức sử dụng chỉ mục MySQL tối ưu

Cải thiện hiệu suất với InnoDB

Một trong những mẹo điều chỉnh đầu tiên dành cho những người có tải nặng trên cơ sở dữ liệu của họ là thử chuyển sang InnoDB từ công cụ lưu trữ MyISAM. Có một chỉ mục được nhóm, với dữ liệu trong các trang và các khối vật lý liên tiếp, InnoDB có hiệu suất tốt hơn đối với khối lượng dữ liệu lớn so với MyISAM

InnoDB cũng tự hào có một tập hợp phong phú các biến và cài đặt nâng cao có thể được định cấu hình để cải thiện hiệu suất của MySQL hơn nữa. Cài đặt hiệu suất InnoDB mở rộng hơn và do đó, có nhiều cách hơn để điều chỉnh InnoDB để có hiệu suất cao hơn so với điều chỉnh MyISAM

Tối ưu hóa truy vấn MySQL

Bây giờ chúng ta hãy xem cách tối ưu hóa truy vấn MySQL để có hiệu suất và tốc độ tốt hơn. Đối với những người muốn nâng cao các truy vấn MySQL, nên làm theo các kỹ thuật tối ưu hóa sau

Thêm chỉ mục vào các cột được sử dụng trong các mệnh đề WHERE, ORDER BY và GROUP BY
Bằng cách này, bạn sẽ tăng hiệu suất của truy vấn MySQL vì máy chủ MySQL sẽ tìm nạp kết quả từ cơ sở dữ liệu nhanh hơn đáng kể

Chỉ định các cột cần thiết trong câu lệnh SELECT
Cố gắng tránh sử dụng CHỌN * TỪ vì nó truy xuất tất cả các cột của bảng và do đó gây thêm tải cho máy chủ và làm chậm hiệu suất của nó. Đặt quy tắc luôn chỉ định các cột trong câu lệnh CHỌN

Sử dụng DISTINCT và UNION một cách tiết kiệm
Một mẹo hay khác để điều chỉnh truy vấn là chỉ sử dụng các toán tử DISTINCT và UNION khi cần thiết vì các truy vấn với chúng dẫn đến chi phí hoạt động của máy chủ và thường làm tăng thời gian phản hồi. Cân nhắc thay thế UNION bằng UNION ALL và DISTINCT bằng GROUP BY để mang lại hiệu quả cao hơn cho quy trình

Tránh sử dụng ký tự đại diện ở đầu các mẫu THÍCH
Các truy vấn MySQL với các toán tử LIKE thường dẫn đến suy giảm hiệu suất máy chủ, vì vậy chúng nên được sử dụng cẩn thận. MySQL không thể sử dụng chỉ mục khi mẫu THÍCH bắt đầu bằng ký tự đại diện, ví dụ: '%xyz' và thực hiện quét toàn bộ bảng trong trường hợp này. Bạn nên ghi nhớ điều này khi tối ưu hóa các truy vấn MySQL và thử sử dụng 'xyz%' thay thế bất cứ khi nào có thể

Sử dụng INNER JOIN thay vì OUTER JOIN
Chỉ sử dụng OUTER JOIN khi cần thiết. MySQL thực hiện nhiều công việc hơn để tìm nạp kết quả cho OUTER THAM GIA so với INNER THAM GIA. Chúng tôi khuyên bạn nên kiểm tra hiệu suất của các truy vấn THAM GIA của mình và trong trường hợp nó không thỏa mãn—hãy bắt đầu chuyển đổi OUTER THAM GIA của bạn thành INNER THAM GIA khi có thể. Tối ưu hóa MySQL THAM GIA có thể dẫn đến cải thiện hiệu suất đáng kể

Điều chỉnh các tùy chọn máy chủ để tăng hiệu suất

Bây giờ, hãy tập trung vào cách tối ưu hóa các tùy chọn máy chủ MySQL về điều chỉnh hiệu suất. Đối với điều này, bạn sẽ cần điều chỉnh tệp cấu hình (của tôi. cnf/của tôi. ini)

innodb_buffer_pool_size
Tham số này chỉ định dung lượng bộ nhớ được cấp phát bởi MySQL cho nhóm bộ đệm InnoDB. Giá trị được đề xuất cho tham số này là 70-80% bộ nhớ khả dụng. Tập dữ liệu của bạn càng lớn thì giá trị càng lớn

max_connection
Tham số này xác định số lượng kết nối máy khách đồng thời tối đa được phép và có giá trị mặc định là 151. Để tránh gặp lỗi "Quá nhiều kết nối", giá trị có thể được tăng lên. Tuy nhiên, hãy nhớ rằng quá nhiều kết nối mở có thể ảnh hưởng đến hiệu suất

query_cache_size
Tham số này đặt tổng dung lượng bộ nhớ được phân bổ cho bộ đệm truy vấn. Giá trị tối ưu cho nó phụ thuộc chủ yếu vào trường hợp làm việc của bạn và cần được xác định tạm thời. Ý tưởng là bắt đầu với dung lượng rất nhỏ—ví dụ: 10MB—sau đó tăng dần từng lượng nhỏ lên 100-200MB. Chỉnh query_cache_size, nhớ bật query cache (query-cache-type ON). Lưu ý rằng kích thước bộ đệm truy vấn lớn có thể dẫn đến giảm hiệu suất nghiêm trọng

innodb_io_capital
Tham số này chỉ định số thao tác I/O mỗi giây được phép cho các tác vụ được thực hiện trong nền và có giá trị mặc định là 200. Nói chung, giá trị khoảng 100 phù hợp với ổ cứng cấp trung bình, trong khi đối với các thiết bị lưu trữ nhanh hơn và hiện đại hơn, giá trị cao hơn sẽ có lợi

innodb_log_file_size
Tham số này chỉ định kích thước theo byte cho mỗi tệp nhật ký làm lại MySQL trong một nhóm nhật ký và có giá trị mặc định là 134.217.728 (khoảng 128 MB). Đến lượt nó, tham số innodb_log_files_in_group chỉ định số lượng tệp nhật ký trong nhóm nhật ký và có giá trị mặc định là 2. Trong trường hợp giá trị innodb_log_file_size nhỏ đối với khối lượng công việc của bạn và ứng dụng của bạn viết nhiều, chúng tôi khuyên bạn nên tăng giá trị này. Tuy nhiên, innodb_log_file_size quá lớn sẽ làm tăng thời gian khắc phục sự cố. Vì vậy, bạn sẽ phải tìm kích thước tối ưu của nó

Cấu hình MySQL và tối ưu hóa truy vấn vớidbForge Studio cho MySQL

dbForge Studio cho MySQL đi kèm với một trình cấu hình MySQL nâng cao cho phép thu thập số liệu thống kê đầy đủ nhất về các truy vấn đã thực hiện, phát hiện các truy vấn chậm và khắc phục sự cố hiệu suất dưới bất kỳ hình thức nào

Tại sao truy vấn MySQL của tôi chạy chậm?

Làm cách nào để tăng tốc độ xử lý truy vấn?