10 mẹo điều chỉnh hiệu suất MySQL cần thiết hơn

Mục lục

Cho xem nhiều hơn

MySQL là cơ sở dữ liệu nguồn mở được sử dụng rộng rãi nhất trên thế giới và xếp thứ hai về mức độ phổ biến trong số các cơ sở dữ liệu nói chung. Đó là một hệ thống quản lý cơ sở dữ liệu quan hệ hiệu quả, là trung tâm của các ứng dụng phổ biến trong nhiều năm. Tuy nhiên, nó có thể khó sử dụng và có nhiều cơ hội để cải thiện hiệu suất

Cũng đã có một số phát triển mới quan trọng trong vài năm qua đối với MySQL. Bài viết này cập nhật một bộ mẹo điều chỉnh hiệu suất MySQL trước đây do Baron Schwartz cung cấp. Mặc dù bài viết trước đó vẫn có liên quan nhưng bạn có thể thực hiện các bước bổ sung để đạt được hiệu suất tốt nhất cho việc triển khai MySQL của mình. Dưới đây là 10 mẹo điều chỉnh hiệu suất MySQL khác để thêm vào danh sách của bạn

Mẹo hiệu suất MySQL Không. 1. Thiết kế lược đồ cũng quan trọng như bất kỳ cài đặt MySQL nào khác

Thiết kế lược đồ là một trong những điều quan trọng nhất mà bạn sẽ làm trong cơ sở dữ liệu của mình. Đây là một nguyên tắc công nghệ cơ sở dữ liệu quan hệ chéo, vì các biểu mẫu thông thường đã được giới thiệu vào những năm 1970. Vì MySQL đã chuyển sang InnoDB làm công cụ lưu trữ mặc định trong phiên bản 5. 6, thiết kế lược đồ càng trở nên quan trọng hơn

Tại sao lại thế này? . Điều này liên quan đến cách InnoDB tổ chức dữ liệu. Trong InnoDB, khóa chính được nhóm lại và mỗi khóa phụ sẽ thêm một con trỏ nhập vào khóa chính. Nếu bạn không tính đến điều này trong thiết kế lược đồ của mình thì hiệu suất của bạn sẽ bị ảnh hưởng tiêu cực

Dữ liệu cũng được lưu trữ bằng các chỉ mục cây B, do đó, việc chèn dữ liệu theo cách có thứ tự (i. e. sử dụng các giá trị gần như tuần tự) ngăn chặn sự phân mảnh khóa chính và do đó làm giảm các thao tác I/O cần thiết để tìm các nút lá

Có một số trường hợp sử dụng trong đó các khóa chính tuần tự không phải là lựa chọn phù hợp — một ví dụ điển hình ở đây là Mã định danh duy nhất toàn cầu hoặc UUID. Bạn có thể tìm hiểu sâu hơn về các vấn đề xung quanh UUID và khóa chính tại đây. Tuy nhiên, nói chung, chúng tôi khuyên bạn nên sử dụng các khóa chính tuần tự cho hầu hết các trường hợp sử dụng

Mẹo hiệu suất MySQL Không. 2. Phím phụ không phải là kẻ thù của bạn

Các khóa phụ được cập nhật bởi một quy trình nền. Do đó, tác động hiệu suất không nghiêm trọng như bạn mong đợi. Thay vào đó, vấn đề nằm ở dung lượng đĩa vì việc thêm các khóa phụ sẽ làm tăng yêu cầu lưu trữ

Việc lọc một trường không có chỉ mục có thể dẫn đến việc quét toàn bộ bảng mỗi khi chạy truy vấn. Tất nhiên, điều này có thể dẫn đến tác động hiệu suất rất lớn. Do đó, tốt hơn là có một khóa phụ hơn là bỏ lỡ một khóa

Nói như vậy, bạn không nên lập chỉ mục quá mức cho cơ sở dữ liệu của mình, vì việc chạy nhiều chỉ mục có thể không mang lại những cải tiến về hiệu suất mà bạn muốn đạt được. Đồng thời, các chỉ mục bổ sung này có thể làm tăng chi phí lưu trữ của bạn và InnoDB phải thực hiện nhiều thao tác nền để luôn cập nhật chúng

Mẹo hiệu suất MySQL Không. 3. Các hàng có thể được phục vụ từ các chỉ mục

InnoDB có thể tìm và thực sự phục vụ các hàng trực tiếp từ các chỉ mục, trong khi khóa phụ trỏ đến khóa chính và khóa chính chứa chính hàng đó. Nếu Nhóm bộ đệm InnoDB đủ lớn, thì nó cũng có thể chứa hầu hết dữ liệu trong bộ nhớ. Bạn thậm chí có thể sử dụng các phím tổng hợp, thường hiệu quả hơn cho các truy vấn so với các phím riêng lẻ trên mỗi cột. MySQL có thể sử dụng một chỉ mục cho mỗi lần truy cập bảng, vì vậy nếu bạn đang chạy các truy vấn với một mệnh đề như

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and c=2

Hơn nữa, chỉ mục tổng hợp trên

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and b=2

MySQL sẽ sử dụng chỉ mục bao phủ và phục vụ

SELECT a,b,c FROM table WHERE a=1 and c=2

Trên thực tế, bạn có thể cải thiện hiệu suất bằng cách sử dụng chỉ mục tổng hợp khi có cơ hội làm như vậy. Bất cứ khi nào bạn đang thiết kế các chỉ mục, bạn cần nghĩ về chúng theo cách tự nhiên mà chúng được đọc. Điều này có nghĩa là các chỉ mục luôn được đọc từ trái sang phải, do đó, đưa ra một truy vấn như thế này

SELECT a,b,c FROM table WHERE a=1 and b=2

Sau đó, một chỉ mục trên

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE b=2

Sau đó, chỉ mục sẽ vô dụng và sẽ gây ra quá trình quét toàn bộ bảng. Ý tưởng luôn đọc các chỉ mục từ bên trái cũng áp dụng cho một số trường hợp khác. Ví dụ: đưa ra truy vấn sau

SELECT a,b,c FROM table WHERE a=1 and c=2

Sau đó, một chỉ mục trên

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE a=1 and b=2

Một yếu tố khác liên quan đến thiết kế truy vấn là cách tiếp cận chỉ mục ngoài cùng bên trái, vì đây là cách tối ưu hóa phổ biến được sử dụng trong MySQL. Ví dụ: một chỉ mục trên

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and b=2

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and b=2

Mẹo hiệu suất MySQL Không. 4. Đánh giá truy vấn, đánh giá truy vấn, đánh giá truy vấn

Chỉ cần có một chiếc xe Công thức Một không thắng cuộc đua. Sẽ không nếu bạn để một người lái xe thiếu kinh nghiệm ngồi sau tay lái và họ đâm vào góc cua đầu tiên. Tương tự như vậy, bạn có thể có máy chủ MySQL được điều chỉnh tốt nhất trên trái đất, nhưng nếu bạn có truy vấn không tốt thì cơ sở dữ liệu của bạn sẽ chậm hơn bình thường.

Bạn nên thường xuyên xem lại thiết kế truy vấn của mình theo thời gian khi ứng dụng của bạn thay đổi với các tính năng mới và bản sửa lỗi. Tập dữ liệu và các kiểu sử dụng của ứng dụng cũng có thể thay đổi theo thời gian, tất cả đều có thể ảnh hưởng đến hiệu suất truy vấn

Dành thời gian để xem xét truy vấn và theo dõi thời gian thực hiện truy vấn là rất quan trọng. Bạn có thể sử dụng nhật ký truy vấn chậm hoặc Lược đồ hiệu suất cho việc này, nhưng việc triển khai công cụ giám sát sẽ giúp bạn có được dữ liệu tốt hơn

Hãy nhớ rằng không phải lúc nào truy vấn chậm nhất cũng là truy vấn quan trọng nhất cần khắc phục. Ví dụ: bạn có thể có một truy vấn mất 30 giây nhưng chạy hai lần một ngày cùng với truy vấn mất một giây và chạy 100 lần một phút. Để giành chiến thắng lớn, bạn nên bắt đầu tối ưu hóa truy vấn thứ hai, vì việc cải thiện truy vấn đó có thể tiết kiệm rất nhiều thời gian và tài nguyên trong thời gian dài hơn

Mẹo hiệu suất MySQL Không. 5. tầm nhìn quan trọng

Giám sát là một trong những yếu tố chính của điều chỉnh hiệu suất. Không biết khối lượng công việc hiện tại và các mẫu, thật khó để đưa ra bất kỳ khuyến nghị cụ thể nào. Trong những năm gần đây, MySQL đã cải thiện mức độ hiển thị của các số liệu MySQL/InnoDB cấp thấp, điều này có thể giúp hiểu được khối lượng công việc

Chẳng hạn, trong các phiên bản trước, Lược đồ hiệu suất là một nút thắt cổ chai và có tác động đáng kể, đặc biệt nếu bạn có nhiều bảng. Trong các phiên bản gần đây của MySQL, nhiều thay đổi như Từ điển dữ liệu mới đã cải thiện hiệu suất và giờ đây bạn có thể có nhiều bảng mà không bị ảnh hưởng đáng kể

Hầu hết các công cụ giám sát hiện đại đều sử dụng Lược đồ hiệu suất theo một cách nào đó, vì vậy, một lời khuyên hữu ích là hãy kiểm tra các công cụ này và chọn một công cụ phù hợp nhất với nhu cầu của bạn. Khả năng hiển thị dữ liệu hiệu suất này có thể là một tài sản lớn trong các cuộc điều tra của bạn

Mẹo hiệu suất MySQL Không. 6. Cẩn thận với các công cụ điều chỉnh

Một số khuyến nghị chung được đưa ra bởi các công cụ điều chỉnh sẽ hoạt động trong hầu hết các trường hợp sử dụng. Tuy nhiên, mọi khối lượng công việc và mọi lược đồ đều khác nhau. Trong một số trường hợp, các khuyến nghị chung của các công cụ điều chỉnh không hoạt động và bạn nên cẩn thận khi tin tưởng vào các khuyến nghị này. Ngay cả , là công cụ riêng của Oracle và có sẵn trong MySQL, có thể thực hiện các thay đổi đáng ngờ đối với cấu hình

Ví dụ: đặt

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE a=1 and c=2

Một đề xuất tốt là đặt

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE b=2

SELECT a,b,c FROM table WHERE b=2

Đối với

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and c=2

Mẹo hiệu suất MySQL Không. 7. Hoạt động I/O vẫn còn tốn kém

MySQL và InnoDB cố gắng giảm thiểu số lượng thao tác I/O mà chúng thực hiện vì việc truy cập lớp lưu trữ rất tốn kém về mặt hiệu năng của ứng dụng. Có một số cài đặt có thể ảnh hưởng đến số lượng hoạt động I/O mà InnoDB thực hiện. Hai trong số các cài đặt này thường bị hiểu sai và việc thay đổi chúng thường gây ra các vấn đề về hiệu suất

SELECT a,b,c FROM table WHERE a=1 and c=2

SELECT a,b,c FROM table WHERE a=1 and c=2

Vấn đề đầu tiên là sự suy giảm hiệu suất do khiến InnoDB xóa các trang bẩn quá nhanh, do đó làm giảm cơ hội sửa đổi một trang nhiều lần trước khi bị xóa. Giữ các trang bẩn trong bộ nhớ có thể làm giảm đáng kể các hoạt động I/O cần thiết để ghi dữ liệu vào bộ lưu trữ

Thứ hai, SSD có số lần ghi dự kiến ​​trước khi chúng thấy hiệu suất giảm. Do đó, việc tăng số lượng thao tác ghi có thể ảnh hưởng đến tuổi thọ của ổ SSD của bạn, ngay cả khi bạn đang sử dụng ổ đĩa cao cấp

Dịch vụ lưu trữ đám mây ngày nay rất phổ biến và việc chạy phiên bản dịch vụ MySQL của bạn trên đám mây có thể rất hữu ích. Tuy nhiên, các máy chủ trên đám mây thường sẽ có giới hạn I/O hoặc sẽ tính phí nhiều hơn khi sử dụng nhiều I/O hơn. Khi nhận thức được những giới hạn này, bạn có thể cấu hình cẩn thận các tham số này để đảm bảo không đạt đến các giới hạn này và các hoạt động I/O được giảm thiểu

Điều quan trọng là phải đề cập đến cả

SELECT a,b,c FROM table WHERE a=1 and c=2

Một khuyến nghị tốt để làm theo là giữ mặc định trừ khi bạn biết bạn cần thay đổi chúng

Điều đáng nói là các ổ SSD mới nhất được tối ưu hóa đặc biệt cho cơ sở dữ liệu giao dịch. Một ví dụ là Western Digital, đã tìm kiếm sự hỗ trợ của chuyên gia để giúp họ đáp ứng các yêu cầu đối với làn sóng ứng dụng mới đang được tạo ra

Mẹo hiệu suất MySQL Không. số 8. Tận dụng các biểu thức bảng phổ biến

mysql 8. 0 chứng kiến ​​sự ra đời của các biểu thức bảng chung (CTE), giúp loại bỏ các truy vấn lồng nhau sẽ tạo ra các bảng dẫn xuất. Chức năng mới này cho phép bạn tạo truy vấn tùy chỉnh và tham chiếu kết quả như thể chúng là bảng tạm thời hoặc dạng xem. Sự khác biệt là CTE có thể được tham chiếu nhiều lần trong một giao dịch mà không cần tạo và loại bỏ chúng một cách rõ ràng

Cho rằng CTE chỉ được cụ thể hóa một lần, chúng có xu hướng nhanh hơn trong các giao dịch phức tạp chạy nhiều truy vấn. Ngoài ra, đệ quy CTE được hỗ trợ, vì vậy bạn có thể dễ dàng tạo các cấu trúc phức tạp bằng ngôn ngữ SQL như mô hình phân cấp và chuỗi. Nếu bạn muốn biết thêm chi tiết về CTE, bạn sẽ tìm thấy phần giới thiệu tại đây

Mẹo hiệu suất MySQL Không. 9. Hãy nhận biết về đám mây

Có nhiều tùy chọn đám mây khác nhau đáng để xem xét khi triển khai MySQL, từ triển khai phiên bản máy chủ MySQL trong máy ảo mà bạn quản lý, đến sử dụng cơ sở dữ liệu dưới dạng giải pháp dịch vụ (DBaaS). Phạm vi của các tùy chọn là rất lớn

Nhiều dịch vụ trong số này hứa hẹn sẽ mang lại hiệu suất tăng đáng kể và giải quyết tất cả các sự cố của bạn. Trong một số trường hợp sử dụng đơn giản có thể đúng. Tuy nhiên, ngay cả trong đám mây, bạn phải biết và hiểu các nguyên tắc cơ bản của cơ sở dữ liệu, nếu không chi phí của bạn sẽ tăng lên đáng kể. Việc tăng chi phí này thường xảy ra bởi vì về cơ bản, bạn đang giải quyết vấn đề bằng cách ném thêm phần cứng vào vấn đề hơn là sửa chữa thiết kế

Làm cách nào để điều chỉnh cơ sở dữ liệu MySQL để có hiệu suất tốt nhất?

Các tối ưu hóa phổ biến nhất mà bạn muốn đề xuất cho hiệu suất là gì?

Điều gì có thể được điều chỉnh để tăng hiệu suất trong một hệ thống cơ sở dữ liệu?

Làm cách nào để cải thiện hiệu suất InnoDB của tôi?