Mysql chọn tối ưu hóa

Hiệu suất của cơ sở dữ liệu phụ thuộc vào nhiều yếu tố ở cấp cơ sở dữ liệu. bảng, truy vấn, và thiết lập cấu hình. Cấu trúc phần mềm dẫn đến các hoạt động của CPU và I/O ở phần cứng cấp cao, mà bạn phải giảm thiểu và thực hiện sao cho hiệu quả càng cao càng tốt. Ban đầu cần tìm hiểu các luật cao cấp và hướng dẫn ở phần mềm, và đo hiệu suất bằng cách sử dụng đồng hồ để đo thời gian thực hiện. Sau đó, để làm tốt hơn công việc tối ưu cần tìm hiểu thêm về những điều xảy ra trong nội bộ, và bắt đầu đo lường những thứ như chu kỳ CPU và hoạt động I/O. Người sử dụng thông tin phổ biến muốn có được hiệu suất cơ sở dữ liệu tốt nhất dựa trên cấu hình phần mềm và phần cứng hiện có của họ. Người dùng cao cấp tìm kiếm các cơ hội để cải thiện phần mềm MySQL của họ, hoặc phát triển các công cụ lưu trữ và các thiết bị phần cứng của mình.

• Tối ưu hóa ở cấp cơ sở dữ liệu. Yếu tố quan trọng nhất trong công việc đưa ra một ứng dụng cơ sở dữ liệu có tốc độ nhanh là thiết kế cơ bản của nó
• Tối ưu hóa ở cấp phần cứng. Bất kỳ ứng dụng cơ sở dữ liệu nào cuối cùng cũng chạm tới giới hạn phần cứng khi các cơ sở dữ liệu ngày càng trở nên cồng kềnh hơn. Một thành viên quản trị cơ sở dữ liệu phải đánh giá rằng dữ liệu có thể điều chỉnh các ứng dụng hoặc phải cấu hình lại máy chủ để tránh những tắc nghẽn, hoặc là bắt buộc phải bổ sung thêm phần cứng tài nguyên hơn.

• Các bảng đã được cấu trúc đúng cách?
• Bạn đang sử dụng các công cụ lưu trữ thích hợp cho mỗi bảng, và lợi dụng những điểm mạnh và tính năng của từng công cụ lưu trữ mà bạn sử dụng không? . Lưu ý. Trong MySQL 5. 5 và cao hơn, InnoDB là công cụ lưu trữ mặc định cho các bảng mới. Trong thực tế, các tính năng hiệu suất cao của InnoDB có nghĩa là bảng InnoDB thường tốt hơn bảng MyISAM đơn giản, nhất là đối với một cơ sở dữ liệu bận rộn
• Mỗi bảng đã sử dụng một định dạng hàng thích hợp? . Ít hơn sử dụng bảng nén không gian đĩa -> cần ít kích hoạt I/O để đọc và ghi dữ liệu. Nén is available in engine InnoDB, and only have at table only read in engine MyISAM
• Liệu các ứng dụng sử dụng một chiến lược thích hợp lược lược?
• Phải tất cả các vùng nhớ được sử dụng để bộ nhớ đệm có kích thước hợp lý?

Hệ thống tắc nghẽn thường phát sinh từ các nguồn sau

• đĩa tìm kiếm. must loss a time to drive disk to find a fragment of data. Với ổ đĩa hiện đại, thời gian trung bình cho công việc này thường thấp hơn 10ms, vì vậy trong lý thuyết có thể thực hiện khoảng 100 lượt tìm kiếm trong một giây. Cách để tối ưu hóa thời gian tìm kiếm tối ưu hóa là phân phối dữ liệu lên nhiều đĩa
• Read and write. với đĩa hiện đại, đĩa cung cấp thông số ít nhất là 10-20MB/s. Làm điều đó dễ dàng hơn để tối ưu hóa tìm kiếm bởi vì bạn có thể đọc bài hát từ nhiều đĩa một lúc
• Chu kỳ CPU. khi dữ liệu trong bộ nhớ chính, chúng ta phải xử lý nó để đạt được kết quả cần thiết. Có các bảng nhỏ so với số lượng bộ nhớ là phần tử biến phổ biến nhất hạn chế. Nhưng với các bảng nhỏ, tốc độ thường không phải là vấn đề
• Bộ nhớ băng. khi CPU cần nhiều hơn dữ liệu có thể phù hợp với bộ nhớ cache của CPU, băng thông của bộ nhớ chính trở thành một nút cổ chai. Đây là một nút cổ chai không phổ biến đối với hầu hết các hệ thống, nhưng phải nhận thức được vấn đề này

Sau đây chúng ta sẽ đi vào 2 vấn đề chính là Tối ưu hóa SQL Statement và Tối ưu hóa cấu trúc cơ sở dữ liệu

Các xem xét chính để tối ưu hóa các truy vấn là

1. Thiết lập các chỉ mục trên cột được sử dụng trong mệnh đề WHERE. Để tránh lãng phí không gian đĩa, hãy xây dựng một tập hợp chỉ mục nhỏ mà tăng tốc độ nhiều truy vấn liên quan được sử dụng trong ứng dụng của bạn
2. Giảm thiểu số bảng bị quét trong truy vấn của bạn, đặc biệt là các bảng lớn
3. Giữ cập nhật bảng thống kê, từ đó bộ hóa tối ưu hóa có các thông tin cần thiết để xây dựng một kế hoạch thực hiện hiệu quả
4. Tìm hiểu các kỹ thuật điều chỉnh, kỹ thuật thiết lập chỉ mục và các tham số cấu hình cụ thể cho các công cụ lưu trữ cho mỗi bảng. Cả InnoDB và MyISAM đều có bộ hướng dẫn cho phép đạt hiệu suất cao trong các truy vấn
5. Cô thiết lập và điều chỉnh bất kỳ phần nào của truy vấn, chẳng hạn như một lời gọi hàm, nếu nó phức tạp quá nhiều thời gian
6. Chuyển các truy vấn sang cách làm cho nó khó hiểu
7. Nếu một vấn đề hiệu quả không dễ dàng giải quyết bằng một trong những hướng dẫn cơ bản, điều tra các chi tiết bên trong các truy vấn của công cụ có thể bằng cách đọc các kế hoạch Giải thích và điều chỉnh các chỉ mục của bạn, các mệnh
8. Điều chỉnh kích thước và tính chất của các vùng nhớ mà MySQL sử dụng cho bộ nhớ đệm. Với việc sử dụng có hiệu quả của bộ nhớ đệm, các truy vấn vòng lặp chạy lại chạy nhanh hơn bởi vì kết quả được lấy từ bộ nhớ ở lần thứ hai trở đi
9. Ngay cả đối với một truy vấn chạy nhanh sử dụng các vùng bộ nhớ cache, bạn vẫn có thể tối ưu hóa hơn nữa để chúng yêu cầu bộ nhớ cache ít hơn, làm cho ứng dụng của bạn có thể mở rộng hơn. Khả năng mở rộng có nghĩa là ứng dụng của bạn có thể xử lý nhiều người dùng đồng thời, với yêu cầu lớn hơn

Tối ưu hóa mệnh đề MySQL ở đâu như thế nào?

Một số ví dụ về truy vấn rất nhanh

SELECT COUNT [*] FROM tbl_name;
SELECT MIN [key_part1], MAX [key_part1] FROM tbl_name;
SELECT MAX [key_part2] FROM tbl_name WHERE key_part1 = constant;
SELECT .. FROM tbl_name ORDER BY key_part1, key_part2, .. LIMIT 10;
SELECT .. FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC, .. LIMIT 10;

MySQL giải quyết các truy vấn sau đây chỉ sử dụng các mục từ một mục phụ, nếu các cột bị đánh chỉ mục là số

SELECT key_part1, key_part2 FROM tbl_name WHERE key_part1 = val;
SELECT COUNT [*] FROM tbl_name WHERE key_part1 = VAL1 AND key_part2 = VAL2;
SELECT key_part2 FROM tbl_name GROUP BY key_part1;

Các truy vấn sau đây sử dụng các chỉ mục dữ liệu để lấy ra bản ghi trong thứ tự sắp xếp

SELECT .. FROM tbl_name ORDER BY key_part1, key_part2, ...;
SELECT .. FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC, ...;

Bạn có thể bị cuốn hút để viết lại các truy vấn của bạn nhằm làm cho các phép tính nhanh hơn, trong khi hy sinh khả năng đọc. Vì MySQL không tự động tối ưu hóa tương tự, bạn có thể tránh việc này và viết các truy vấn theo một hình thức dễ hiểu và dễ bảo trì hơn

Một số tối ưu hóa được thực hiện bởi MySQL sau

Loại bỏ các dấu ngoặc đơn không cần thiết:
[[a AND b] AND c OR [[[a AND b] AND [c AND d]]]] -> [a AND b AND c] OR [a AND b AND c AND d]
Thay thế hằng số:
[a < b AND b = c] AND a = 5 -> b > 5 AND b = c AND a = 5
Loại bỏ điều kiện hằng số:
[b >= 5 AND b = 5] OR [b = 6 AND 5 = 5] OR [b = 7 AND 5 = 6] -> b = 5 OR b = 6

Hằng số biểu thức được sử dụng bởi các mục chỉ được đánh giá chỉ một lần. COUNT[*] trên một bảng duy nhất mà không có mệnh đề WHERE được lấy trực tiếp từ các bảng thông tin đối với MyISAM và các bảng trong bộ nhớ trong. Điều này cũng được thực hiện cho bất kỳ biểu thức NOT NULL nào khi được sử dụng chỉ với một bảng. Phát sớm các biểu thức hằng số không hợp lệ. MySQL nhanh chóng phát hiện một số câu lệnh CHỌN không thể thực hiện và không trả về bất kỳ bản ghi nào

HAVING are input with WHERE if you don't used GROUP BY or chức năng tổng hợp. Đối với mỗi bảng trong mệnh đề THAM GIA, một mệnh đề WHERE đơn giản được xây dựng để có được một đánh giá nhanh chóng mệnh đề WHERE cho bảng và cũng để bỏ qua các bản ghi càng sớm càng tốt. Tất cả các bảng không thay đổi đều được đọc đầu tiên trước bất kỳ bảng nào khác trong truy vấn. Một bảng không đổi là bảng có bất kỳ tính chất nào sau đây

• Một bảng trống hoặc một bảng với một hàng
• Một bảng được sử dụng với một mệnh đề WHERE at a PRIMARY KEY or index UNIQUE, where that all the other section only are so sánh với biểu thức hằng số và được định nghĩa là NOT NULL

Sự kết hợp tham gia tốt nhất cho việc kết nối các bảng được tìm thấy bằng cách thử tất cả các khả năng. Nếu tất cả các cột trong mệnh đề ORDER BY và GROUP BY đến từ cùng một bảng, thì bảng đó được lựa chọn đầu tiên khi tham gia. Nếu có một mệnh đề ORDER BY và GROUP BY khác, hoặc nếu ORDER BY hoặc GROUP BY chứa các cột từ bảng khác với bảng đầu tiên trong hàng chờ tham gia, bảng tạm thời được tạo ra. Nếu bạn sử dụng tùy chọn SQL_SMALL_RESULT, MySQL sẽ sử dụng bảng tạm thời trong bộ nhớ

Mỗi bảng đánh chỉ mục được truy vấn và chỉ mục tốt nhất được sử dụng trừ khi bộ tối thiểu tin rằng nó hiệu quả hơn để sử dụng một bảng quét. Bộ tối ưu tại thời điểm này là phức tạp hơn và ước tính dựa trên các yếu tố khác như kích thước bảng, số lượng bản ghi và kích thước blog I/O. MySQL đôi khi có thể tạo kết quả truy vấn sử dụng dữ liệu từ chỉ mục mà không tham khảo dữ liệu của bảng. Nếu tất cả các cột được sử dụng từ các mục chỉ là kiểu số, thì chỉ có mục dữ liệu được sử dụng để giải quyết các vấn đề. Trước khi mỗi hàng được đưa ra kết quả truy vấn, những bản ghi không phù hợp với mệnh đề HAVING bị bỏ qua

Tối ưu hóa truy vấn GIỚI HẠN

MySQL đôi khi tối ưu hóa một truy vấn mà có một mệnh đề GIỚI HẠN và không có mệnh đề HAVING. Nếu bạn chỉ lấy ra một vài bản ghi với GIỚI HẠN, MySQL sử dụng các chỉ mục trong một số trường hợp trong khi bình thường nó ưu tiên quét toàn bộ bảng. Nếu bạn sử dụng LIMIT ROW_COUNT với ORDER BY, MySQL kết thúc việc sắp xếp ngay sau khi nó đã tìm thấy ROW_COUNT hàng đầu tiên của kết quả đã được sắp xếp, chứ không phải sắp xếp toàn bộ kết quả. Nếu việc sắp xếp được thực hiện bằng cách sử dụng một mục duy nhất, nó sẽ được thực hiện rất nhanh. Khi kết hợp LIMIT ROW_COUNT với DISTINCT, MySQL dừng lại ngay khi nó tìm thấy ROW_COUNT bản ghi duy nhất. Trong một số trường hợp, GROUP BY có thể được giải quyết bằng cách đọc từ khóa sắp xếp [hoặc thực hiện sắp xếp trên các từ khóa] và sau đó tính toán bảng tóm tắt cho đến khi giá trị khóa thay đổi. Trong trường hợp này, LIMIT ROW_COUNT không tính toán bất kỳ giá trị GROUP BY nào không cần thiết. Ngay sau khi MySQL gửi số lượng bản ghi yêu cầu cho máy khách, nó hủy bỏ các truy vấn, trừ khi bạn đang sử dụng SQL_CALC_FOUND_ROWS

Tối ưu hóa Kích thước dữ liệu

Bạn có thể đạt được hiệu quả tốt hơn cho một bảng và giảm thiểu không gian lưu trữ bằng cách sử dụng các kỹ thuật được liệt kê ở đây

• Các cột. sử dụng hiệu quả nhất [nhỏ nhất] các loại dữ liệu có thể [MEDIUMINT thường là lựa chọn tốt hơn so với INT vì nó sử dụng ít hơn 25% không gian]. Khai báo cột là NOT NULL nếu có thể. Bạn cũng tiết kiệm được một chút không gian lưu trữ, một chút cho mỗi cột. Nếu bạn thực sự cần các giá trị NULL trong bảng của bạn thì hãy sử dụng chúng. Chỉ cần né thiết lập mặc định cho phép giá trị NULL trong mỗi cột

• định dạng hàng. bảng InnoDB sử dụng một định dạng lưu trữ nhỏ gọn. Trong các phiên bản của MySQL nhỏ hơn 5. 0. 3, các hàng của InnoDB có chứa một số thông tin không cần thiết, chẳng hạn như số lượng các cột và độ dài của mỗi cột, ngay cả đối với các kích thước cố định của các cột. Theo mặc định, các bảng được tạo ra trong định dạng thu gọn. Sự hiện diện của các định dạng hàng hóa được thu gọn lại không giảm khoảng 20% ​​lưu trữ và chi phí sử dụng CPU gia tăng cho một số hoạt động. Để giảm thiểu không gian hơn nữa bằng cách lưu trữ bảng dữ liệu ở định dạng nén, chỉ rõ ROW_FORMAT=COMPRESSED khi tạo bảng InnoDB [bảng nén InnoDB có thể được đọc và ghi, trong khi bảng nén MyISAM chỉ là đọc]

• Index. chỉ mục chính của một bảng nên càng ngắn càng tốt. Đối với các bảng InnoDB, các cột từ khóa chính được lặp lại ở mỗi chỉ mục thứ cấp, do đó, một từ khóa chính rút ngắn giúp tiết kiệm điện năng không đáng kể nếu bạn có nhiều mục trung tâm. Chỉ tạo các mục mà bạn cần để cải thiện hiệu quả truy vấn. Mục đích chỉ tốt cho hoạt động lấy kết quả về, nhưng làm chậm hoạt động thêm và cập nhật. Nếu bạn truy cập vào một bảng chủ yếu bằng cách tìm kiếm trên sự kết hợp của các cột, hãy tạo ra một mục tổng hợp duy nhất trên chúng ta tốt hơn là một chỉ mục riêng biệt cho mỗi cột. Nếu nó rất có khả năng rằng một chuỗi dài cột có tiền tố duy nhất trên số ký tự đầu tiên, thì nó tốt hơn là đánh chỉ mục tiền tố này

• tham gia. trong một số trường hợp, nó có thể có lợi để phân chia thành hai bảng được quét rất thường xuyên. Khai báo các cột với các thông tin giống nhau trong các bảng khác nhau với các kiểu dữ liệu giống nhau, để tăng tốc độ tham gia dựa trên các cột tương ứng. Giữ tên cột đơn giản, làm như vậy bạn có thể sử dụng cùng tên trên các bảng khác nhau và đơn giản hóa truy vấn tham gia

Tối ưu hóa cho dữ liệu số

Đối với ID duy nhất hoặc giá trị khác có thể được biểu diễn như là một trong hai kiểu chuỗi hoặc số, các cột số được ưa thích hơn chuỗi cột. Kể từ khi giá trị lớn nhất có thể được lưu trữ trong số byte ít hơn các chuỗi tương ứng, nó nhanh hơn và mất ít bộ nhớ hơn để truyền và so sánh chúng. Nếu bạn đang sử dụng data number, nó nhanh hơn trong nhiều trường hợp để truy cập thông tin từ một cơ sở dữ liệu [sử dụng kết nối trực tiếp] hơn là truy cập vào một tập tin văn bản

Tối ưu hóa cho các loại ký tự và chuỗi

Khi so sánh các giá trị từ các cột khác nhau, khai báo các cột này với các ký tự tương tự của tệp và đối chiếu bất cứ nơi nào có thể, để tránh chuyển đổi chuỗi trong khi chạy truy vấn. Đối với cột giá trị kích thước thấp hơn 8KB, sử dụng hệ thống nhị phân VARCHAR thay vì BLOB. Mệnh đề GROUP BY và ORDER BY có thể tạo ra các bảng tạm thời, và các bảng tạm thời có thể sử dụng các công cụ lưu trữ bộ nhớ nếu bảng gốc không chứa bất kỳ cột BLOB nào

Tối ưu hóa cho các loại BLOB

Khi lưu trữ một blob có chứa dữ liệu văn bản lớn, công việc đầu tiên là nén nó. Đối với một bảng có nhiều cột, để giảm yêu cầu bộ nhớ cho các truy vấn không sử dụng các cột BLOB, xem xét việc chia các cột BLOB thành một bảng riêng biệt và tham chiếu nó với một truy vấn tham gia khi cần thiết . Khi các yêu cầu về hiệu suất để lấy và hiển thị một giá trị BLOB có thể rất khác với các loại dữ liệu khác, bạn có thể đặt bảng BLOB riêng trên một thiết bị lưu trữ hoặc thậm chí trên một cơ sở dữ liệu

Tối ưu hóa cho nhiều bảng

Có một vài nhược điểm của việc tạo ra nhiều bảng trong cơ sở dữ liệu. Nếu bạn có nhiều bảng MyISAM trong thư mục cơ sở dữ liệu tương tự, hãy kích hoạt mở, đóng và tạo thực hiện một cách chậm rãi. Nếu bạn thực hiện câu lệnh SELECT trên nhiều bảng khác nhau, có một chút phí khi bộ nhớ cache của bảng đã đầy, bởi vì khi đó mỗi bảng đã được mở ra, các bảng khác phải được đóng lại. Bạn có thể giảm chi phí bằng cách tăng số lượng mục cho phép trong bảng bộ nhớ cache

MySQL sử dụng bảng tạm thời nội bộ như thế nào? . Máy chủ có thể tạo ra một bảng tạm thời ban đầu là một bảng trong bộ nhớ, sau đó chuyển đổi nó thành một bảng trên đĩa nếu nó trở nên quá lớn. Khi máy chủ tạo ra một bảng tạm thời nội bộ [hoặc trong bộ nhớ hoặc trên đĩa], nó sẽ tăng biến trạng thái Created_tmp_tables. Nếu máy chủ tạo ra các bảng trên đĩa [hoặc ngay từ lệnh cấm đầu tiên hoặc bằng cách chuyển đổi một bảng trong bộ nhớ] thì nó sẽ tăng giá trị biến trạng thái Created_tmp_disk_tables

Đến đây tôi đã giới thiệu các bạn vấn đề tối ưu hóa câu lệnh SQL và cơ sở dữ liệu cấu trúc tối ưu. Phần tiếp theo tôi sẽ giới thiệu về kế hoạch thực hiện truy vấn, đo lường hiệu suất và chi tiết các tối ưu hóa MySQL

Làm cách nào để tối ưu hóa hiệu suất của MySQL?

Tối ưu hóa trong MySQL là gì?

Làm cách nào để giảm thời gian thực hiện truy vấn MySQL?

Làm cách nào để có được hiệu suất truy vấn trong MySQL?

Trình tối ưu hóa truy vấn MySQL hoạt động như thế nào?