Trình tối ưu mysql
Gặp gỡ EverSQL, DBA dựa trên AI cá nhân của bạnEverSQL là cách nhanh nhất để tự động tối ưu hóa cơ sở dữ liệu và truy vấn SQL của bạn Show EverSQL 100% không xâm nhập và không truy cập bất kỳ dữ liệu nhạy cảm nào trong cơ sở dữ liệu của bạn Khi bạn đưa ra một truy vấn chọn các hàng, MySQL sẽ phân tích truy vấn đó để xem có thể sử dụng bất kỳ tối ưu hóa nào để xử lý truy vấn nhanh hơn không. Trong phần này, chúng ta sẽ xem xét cách thức hoạt động của trình tối ưu hóa truy vấn. Để biết thêm thông tin về các biện pháp tối ưu hóa mà MySQL thực hiện, hãy tham khảo chương tối ưu hóa trong Hướng dẫn tham khảo MySQL Tất nhiên, trình tối ưu hóa truy vấn MySQL tận dụng các chỉ mục, nhưng nó cũng sử dụng các thông tin khác. Ví dụ: nếu bạn đưa ra truy vấn sau, MySQL sẽ thực hiện nó rất nhanh, bất kể bảng có lớn đến đâu SELECT * FROM tbl_name WHERE 0; Trong trường hợp này, MySQL xem xét mệnh đề WHERE, nhận ra rằng không có hàng nào có thể đáp ứng truy vấn và thậm chí không thèm tìm kiếm trong bảng. Bạn có thể thấy điều này bằng cách đưa ra một câu lệnh GIẢI THÍCH, câu lệnh này yêu cầu MySQL hiển thị một số thông tin về cách nó sẽ thực thi một truy vấn CHỌN mà không thực sự thực thi nó. 1 Để sử dụng EXPLAIN, chỉ cần đặt từ EXPLAIN trước câu lệnh SELECT mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHEREThông thường, EXPLAIN trả về nhiều thông tin hơn thế, bao gồm thông tin không NULL về các chỉ mục sẽ được sử dụng để quét các bảng, các loại liên kết sẽ được sử dụng và ước tính số lượng hàng sẽ cần được kiểm tra từ mỗi bảng Cách thức hoạt động của trình tối ưu hóaTrình tối ưu hóa truy vấn MySQL có một số mục tiêu, nhưng mục tiêu chính của nó là sử dụng chỉ mục bất cứ khi nào có thể và sử dụng chỉ mục hạn chế nhất để loại bỏ càng nhiều hàng càng sớm càng tốt. Phần cuối cùng đó nghe có vẻ lạc hậu và không trực quan. Xét cho cùng, mục tiêu của bạn khi đưa ra câu lệnh SELECT là tìm các hàng chứ không phải từ chối chúng. Lý do trình tối ưu hóa cố gắng từ chối các hàng là vì nó có thể loại bỏ các hàng khỏi quá trình xem xét càng nhanh thì các hàng phù hợp với tiêu chí của bạn càng được tìm thấy nhanh hơn. Các truy vấn có thể được xử lý nhanh hơn nếu các thử nghiệm hạn chế nhất có thể được thực hiện trước. Giả sử rằng bạn có một truy vấn kiểm tra hai cột, mỗi cột có một chỉ mục trên đó SELECT col3 FROM mytable WHERE col1 = 'some value' AND col2 = 'some other value'; Cũng giả sử rằng thử nghiệm trên col1 khớp với 900 hàng, thử nghiệm trên col2 khớp với 300 hàng và cả hai thử nghiệm cùng thành công trên 30 hàng. Kiểm tra col1 trước tiên cho kết quả trong 900 hàng phải được kiểm tra để tìm 30 hàng cũng khớp với giá trị col2. Đó là 870 bài kiểm tra thất bại. Kiểm tra col2 trước tiên cho kết quả trong 300 hàng phải được kiểm tra để tìm 30 hàng cũng khớp với giá trị col1. Đó chỉ là 270 bài kiểm tra không thành công, do đó cần ít tính toán và I/O đĩa hơn. Do đó, trình tối ưu hóa sẽ kiểm tra col2 trước vì làm như vậy dẫn đến ít công việc hơn Bạn có thể giúp trình tối ưu hóa tận dụng các chỉ mục bằng cách sử dụng các nguyên tắc sau Cố gắng so sánh các cột có cùng kiểu dữ liệu. Khi bạn sử dụng các cột được lập chỉ mục để so sánh, hãy sử dụng các cột cùng loại. Các loại dữ liệu giống hệt nhau sẽ mang lại cho bạn hiệu suất tốt hơn các loại dữ liệu không giống nhau. Ví dụ: INT khác với BIGINT. CHAR(10) được coi giống như CHAR(10) hoặc VARCHAR(10) nhưng khác với CHAR(12) hoặc VARCHAR(12). Nếu các cột bạn đang so sánh có các loại khác nhau, bạn có thể sử dụng ALTER TABLE để sửa đổi một trong số chúng sao cho các loại khớp với nhau Cố gắng làm cho các cột được lập chỉ mục đứng một mình trong các biểu thức so sánh. Nếu bạn sử dụng một cột trong lệnh gọi hàm hoặc là một phần của thuật ngữ phức tạp hơn trong biểu thức số học, thì MySQL không thể sử dụng chỉ mục vì nó phải tính giá trị của biểu thức cho mỗi hàng. Đôi khi điều này là không thể tránh khỏi, nhưng nhiều khi bạn có thể viết lại truy vấn để cột được lập chỉ mục tự xuất hiện Các mệnh đề WHERE sau đây minh họa cách thức hoạt động của điều này. Chúng tương đương nhau về mặt số học, nhưng khá khác nhau cho mục đích tối ưu hóa WHERE mycol < 4 / 2 WHERE mycol * 2 < 4 Đối với dòng đầu tiên, trình tối ưu hóa đơn giản hóa biểu thức 4/2 thành giá trị 2, sau đó sử dụng chỉ mục trên mycol để tìm nhanh các giá trị nhỏ hơn 2. Đối với biểu thức thứ hai, MySQL phải truy xuất giá trị của mycol cho mỗi hàng, nhân với 2 và sau đó so sánh kết quả với 4. Trong trường hợp này, không thể sử dụng chỉ số. Mỗi giá trị trong cột phải được truy xuất để có thể đánh giá biểu thức ở phía bên trái của phép so sánh Hãy xem xét một ví dụ khác. Giả sử rằng bạn có một cột được lập chỉ mục date_col. Nếu bạn đưa ra một truy vấn chẳng hạn như truy vấn sau, thì chỉ mục không được sử dụng SELECT * FROM mytbl WHERE YEAR(date_col) < 1990; Biểu thức không so sánh 1990 với một cột được lập chỉ mục; . Do đó, chỉ mục trên date_col không được sử dụng vì việc thực hiện truy vấn yêu cầu quét toàn bộ bảng. Cách khắc phục là gì? WHERE date_col < '1990-01-01' Nhưng giả sử rằng bạn không có một ngày cụ thể. Thay vào đó, bạn có thể quan tâm đến việc tìm các bản ghi có ngày nằm trong một số ngày nhất định kể từ hôm nay. Có một số cách để thể hiện sự so sánh kiểu này—không phải cách nào cũng hiệu quả như nhau. Đây là ba khả năng WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE()) WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY) Đối với dòng đầu tiên, không có chỉ mục nào được sử dụng vì phải truy xuất cột cho mỗi hàng để có thể tính được giá trị của TO_DAYS(date_col). Dòng thứ hai tốt hơn. Cả điểm cắt và TO_DAYS(CURDATE()) đều là hằng số, do đó, phía bên phải của phép so sánh có thể được trình tối ưu hóa tính toán một lần trước khi xử lý truy vấn, thay vì một lần trên mỗi hàng. Nhưng cột date_col vẫn xuất hiện trong lệnh gọi hàm, ngăn việc sử dụng chỉ mục. Dòng thứ ba là tốt nhất của tất cả. Một lần nữa, phía bên tay phải của so sánh có thể được tính một lần như một hằng số trước khi thực hiện truy vấn, nhưng bây giờ giá trị là một ngày. Giá trị đó có thể được so sánh trực tiếp với giá trị date_col, không cần phải chuyển đổi thành ngày nữa. Trong trường hợp này, chỉ mục có thể được sử dụng Không sử dụng ký tự đại diện ở đầu mẫu THÍCH. Một số tìm kiếm chuỗi sử dụng mệnh đề WHERE có dạng sau WHERE col_name LIKE '%string%' Đó là điều nên làm nếu bạn muốn tìm một chuỗi bất kể nó xuất hiện ở đâu trong cột. Nhưng đừng đặt '%' ở cả hai bên của chuỗi chỉ vì thói quen. Nếu bạn thực sự chỉ tìm kiếm chuỗi khi nó xuất hiện ở đầu cột, hãy bỏ qua '%' đầu tiên. Giả sử bạn đang tìm trong một cột chứa họ của những tên như MacGregor hoặc MacDougall bắt đầu bằng 'Mac'. Trong trường hợp đó, hãy viết mệnh đề WHERE như thế này ________số 8_______Trình tối ưu hóa xem xét phần đầu theo nghĩa đen của mẫu và sử dụng chỉ mục để tìm các hàng khớp như thể bạn đã viết biểu thức sau, biểu thức này ở dạng cho phép sử dụng chỉ mục trên last_name WHERE last_name >= 'Mac' AND last_name < 'Mad' Tối ưu hóa này không áp dụng cho các đối sánh mẫu sử dụng toán tử REGEXP. Biểu thức REGEXP không bao giờ được tối ưu hóa Giúp trình tối ưu hóa ước tính tốt hơn về hiệu quả của chỉ mục. Theo mặc định, khi bạn so sánh các giá trị trong các cột được lập chỉ mục với một hằng số, trình tối ưu hóa sẽ giả định rằng các giá trị chính được phân phối đồng đều trong chỉ mục. Trình tối ưu hóa cũng sẽ kiểm tra nhanh chỉ mục để ước tính có bao nhiêu mục nhập sẽ được sử dụng khi xác định xem chỉ mục có nên được sử dụng để so sánh liên tục hay không. Đối với các bảng MyISAM, InnoDB và BDB, bạn có thể yêu cầu máy chủ thực hiện phân tích các giá trị chính bằng cách sử dụng ANALYZE TABLE. Điều này cung cấp cho trình tối ưu hóa thông tin tốt hơn Sử dụng GIẢI THÍCH để xác minh hoạt động của trình tối ưu hóa. Câu lệnh EXPLAIN có thể cho bạn biết liệu các chỉ mục có đang được sử dụng hay không. Thông tin này hữu ích khi bạn đang thử các cách viết câu lệnh khác nhau hoặc kiểm tra xem việc thêm chỉ mục có thực sự tạo ra sự khác biệt về hiệu quả thực thi truy vấn hay không. Ví dụ, hãy xem "Sử dụng GIẢI THÍCH để kiểm tra hoạt động của Trình tối ưu hóa. " Đưa ra các gợi ý tối ưu hóa khi cần thiết. Thông thường, trình tối ưu hóa MySQL tự coi mình rảnh rỗi để xác định thứ tự quét các bảng để lấy hàng nhanh nhất. Đôi khi, trình tối ưu hóa sẽ đưa ra lựa chọn không tối ưu. Nếu bạn thấy điều này xảy ra, bạn có thể ghi đè lựa chọn của trình tối ưu hóa bằng cách sử dụng từ khóa STRAIGHT_JOIN. Phép nối được thực hiện với STRAIGHT_JOIN giống như phép nối chéo nhưng buộc các bảng phải được nối theo thứ tự có tên trong mệnh đề TỪ STRAIGHT_JOIN có thể được chỉ định tại hai điểm trong câu lệnh CHỌN. Bạn có thể chỉ định nó giữa từ khóa SELECT và danh sách lựa chọn để có hiệu ứng toàn cầu đối với tất cả các phép nối chéo trong câu lệnh hoặc bạn có thể chỉ định nó trong mệnh đề TỪ. Hai mệnh đề sau là tương đương mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE0Chạy truy vấn cả khi có và không có STRAIGHT_JOIN; . (Kiểm tra các kế hoạch thực hiện với EXPLAIN để xem cách MySQL xử lý từng câu lệnh. ) Bạn cũng có thể sử dụng FORCE INDEX, USE INDEX hoặc IGNORE INDEX để cung cấp cho máy chủ hướng dẫn về chỉ mục nào nên ưu tiên hơn Tận dụng các lĩnh vực mà trình tối ưu hóa hoàn thiện hơn. MySQL có thể thực hiện phép nối và truy vấn con, nhưng hỗ trợ truy vấn con mới được hỗ trợ gần đây hơn, đã được thêm vào MySQL 4. 1. Do đó, trình tối ưu hóa đã được điều chỉnh tốt hơn cho các phép nối hơn là cho các truy vấn con trong một số trường hợp. Điều này có ý nghĩa thực tế khi bạn có truy vấn con chạy chậm. Như đã thảo luận trong "Viết lại truy vấn con dưới dạng phép nối" trong Chương 2, một số truy vấn con có thể được định dạng lại thành phép nối tương đương về mặt logic. Nếu truy vấn con chậm của bạn là một trong những truy vấn này, hãy thử viết nó dưới dạng tham gia để xem nó có hoạt động tốt hơn không Kiểm tra các dạng truy vấn thay thế, nhưng chạy chúng nhiều lần. Khi thử nghiệm các hình thức thay thế của truy vấn (ví dụ: truy vấn con so với phép nối tương đương), hãy chạy truy vấn đó nhiều lần theo mỗi cách. Nếu bạn chỉ chạy một truy vấn một lần trong hai cách khác nhau, bạn sẽ thường thấy rằng truy vấn thứ hai nhanh hơn chỉ vì thông tin từ truy vấn đầu tiên vẫn được lưu trong bộ nhớ đệm và thực sự không cần phải đọc từ đĩa. Bạn cũng nên cố gắng chạy các truy vấn khi tải hệ thống tương đối ổn định để tránh ảnh hưởng do các hoạt động khác trên hệ thống của bạn Tránh lạm dụng chuyển đổi loại tự động của MySQL. MySQL sẽ thực hiện chuyển đổi kiểu tự động, nhưng nếu bạn có thể tránh chuyển đổi, bạn có thể đạt được hiệu suất tốt hơn. Ví dụ: nếu num_col là một cột số nguyên, mỗi truy vấn này sẽ trả về cùng một kết quả mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE1Nhưng truy vấn thứ hai liên quan đến chuyển đổi loại. Bản thân hoạt động chuyển đổi liên quan đến một hình phạt hiệu suất nhỏ để chuyển đổi số nguyên và chuỗi thành gấp đôi để thực hiện so sánh. Một vấn đề nghiêm trọng hơn là nếu num_col được lập chỉ mục, thì việc so sánh liên quan đến chuyển đổi loại có thể khiến chỉ mục không được sử dụng Kiểu so sánh ngược lại (so sánh cột chuỗi với giá trị số) cũng có thể ngăn việc sử dụng chỉ mục. Giả sử rằng bạn viết một truy vấn như thế này mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE2Trong trường hợp này, không thể sử dụng chỉ mục trên str_col vì có thể có nhiều giá trị chuỗi khác nhau trong str_col bằng 4 khi được chuyển đổi thành số (ví dụ: '4', '4. 0' và 'thứ 4' ). Cách duy nhất để biết giá trị nào đủ điều kiện là đọc từng giá trị và thực hiện so sánh Sử dụng EXPLAIN để kiểm tra hoạt động của Trình tối ưu hóaCâu lệnh EXPLAIN rất hữu ích để hiểu sâu hơn về các kế hoạch thực hiện mà trình tối ưu hóa tạo ra để xử lý các câu lệnh. Trong phần này, tôi sẽ chỉ ra hai cách sử dụng của EXPLAIN
Cuộc thảo luận chỉ mô tả những trường đầu ra GIẢI THÍCH có liên quan đến các ví dụ. Thông tin thêm về ý nghĩa của đầu ra EXPLAIN có thể được tìm thấy trong Phụ lục E, "Tham khảo cú pháp SQL. " Trước đó, trong "Cách thức hoạt động của trình tối ưu hóa", điểm đã được đưa ra là cách bạn viết một biểu thức có thể xác định liệu trình tối ưu hóa có thể sử dụng các chỉ mục có sẵn hay không. Cụ thể, cuộc thảo luận ở đó đã sử dụng ví dụ về ba mệnh đề WHERE tương đương logic sau đây, chỉ mệnh đề thứ ba cho phép sử dụng chỉ mục WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE()) WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY) EXPLAIN cho phép bạn kiểm tra xem cách viết một biểu thức có tốt hơn cách viết khác không. Để thấy điều này, hãy thử sử dụng từng mệnh đề WHERE để tìm kiếm các giá trị cột hết hạn trong bảng thành viên, sử dụng giá trị ngưỡng là 30 ngày. Tuy nhiên, như được tạo ban đầu, bảng thành viên không có chỉ mục trên cột hết hạn. Để cho phép nhìn thấy mối quan hệ giữa việc sử dụng chỉ mục và cách viết một biểu thức, trước tiên hãy lập chỉ mục cho cột hết hạn mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE4Sau đó thử GIẢI THÍCH với từng dạng của biểu thức để xem trình tối ưu hóa đưa ra loại kế hoạch thực thi nào mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE5Kết quả cho hai câu lệnh đầu tiên cho thấy chỉ mục không được sử dụng. Giá trị loại cho biết cách các giá trị sẽ được đọc từ một bảng. TẤT CẢ có nghĩa là "tất cả hồ sơ sẽ được kiểm tra. " Tức là, quá trình quét toàn bộ bảng sẽ được thực hiện mà không có lợi ích của chỉ mục. NULL trong mỗi cột liên quan đến khóa cũng cho biết rằng sẽ không có chỉ mục nào được sử dụng Ngược lại, kết quả cho câu lệnh thứ ba cho thấy mệnh đề WHERE đã được viết sao cho trình tối ưu hóa có thể sử dụng chỉ mục trên cột hết hạn
Cách sử dụng thứ hai của EXPLAIN là tìm hiểu xem việc thêm các chỉ mục có giúp trình tối ưu hóa thực thi một câu lệnh hiệu quả hơn hay không. Đối với ví dụ này, tôi sẽ chỉ sử dụng hai bảng ban đầu không được lập chỉ mục. Điều này đủ để cho thấy tác dụng của việc tạo chỉ mục. Các nguyên tắc tương tự áp dụng cho các phép nối phức tạp hơn liên quan đến nhiều bảng Hỗ trợ rằng chúng tôi có hai bảng t1 và t2, mỗi bảng có 1.000 hàng chứa các giá trị từ 1 đến 1000. Truy vấn mà chúng ta sẽ kiểm tra sẽ tìm những hàng có giá trị tương ứng từ hai bảng giống nhau mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE6Không có chỉ mục nào trên một trong hai bảng, EXPLAIN tạo ra kết quả này mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE7Ở đây, TẤT CẢ trong cột loại biểu thị quét toàn bộ bảng kiểm tra tất cả các hàng. NULL trong cột có thể_keys chỉ ra rằng không tìm thấy chỉ mục ứng cử viên nào để tăng tốc độ truy vấn. (Tất cả các cột key, key_len và ref đều là NULL do thiếu chỉ mục phù hợp. ) Sử dụng where cho biết thông tin trong mệnh đề WHERE được sử dụng để xác định các hàng đủ điều kiện Những mẩu thông tin này cho chúng tôi biết rằng trình tối ưu hóa không tìm thấy thông tin hữu ích nào để thực hiện truy vấn hiệu quả hơn
Các giá trị hàng hiển thị ước tính của trình tối ưu hóa về số lượng hàng cần kiểm tra ở mỗi giai đoạn của truy vấn. Ước tính là 1000 cho t1 vì quá trình quét toàn bộ sẽ được thực hiện. Tương tự, ước tính là 1000 cho t2, nhưng đây là cho mỗi hàng trong t1. Nói cách khác, số lượng kết hợp hàng mà trình tối ưu hóa ước tính nó sẽ kiểm tra để xử lý truy vấn là 1.000 x 1.000 hoặc một triệu. Điều đó rất lãng phí công sức, bởi vì chỉ có 1.000 kết hợp thực sự thỏa mãn các điều kiện trong mệnh đề WHERE Để thực hiện truy vấn này hiệu quả hơn, hãy thêm một chỉ mục trên một trong các cột đã nối và thử lại câu lệnh GIẢI THÍCH mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE8Đây là một cải tiến. Đầu ra cho t1 không thay đổi (chỉ ra rằng quá trình quét toàn bộ vẫn sẽ được thực hiện trên bảng), nhưng trình tối ưu hóa có thể xử lý t2 theo cách khác
Có giá trị nào trong việc lập chỉ mục t1 không? . Để xem có ảnh hưởng gì không, chỉ số t1. i1 và chạy lại GIẢI THÍCH mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE9Đầu ra này tương tự như đầu ra của GIẢI THÍCH trước, nhưng việc thêm chỉ mục đã tạo ra một số khác biệt trong đầu ra cho t1. loại đã thay đổi từ NULL thành chỉ mục và Extra đã thay đổi từ trống thành Sử dụng chỉ mục. Những thay đổi này chỉ ra rằng, mặc dù việc quét toàn bộ các giá trị được lập chỉ mục vẫn sẽ được thực hiện, nhưng giờ đây trình tối ưu hóa có thể đọc chúng trực tiếp từ chỉ mục mà không cần chạm vào tệp dữ liệu. Bạn sẽ thấy loại kết quả này cho bảng MyISAM khi trình tối ưu hóa biết rằng nó có thể lấy tất cả thông tin nó cần bằng cách chỉ tham khảo tệp chỉ mục. Bạn cũng sẽ thấy nó cho các bảng InnoDB và BDB khi trình tối ưu hóa chỉ có thể sử dụng thông tin từ chỉ mục mà không cần tìm cách khác để lấy hàng dữ liệu Một bước nữa chúng ta có thể thực hiện để giúp trình tối ưu hóa ước tính chi phí tốt hơn là chạy ANALYZE TABLE. Điều này khiến máy chủ tạo số liệu thống kê về việc phân phối các giá trị chính. Phân tích bảng và chạy lại EXPLAIN mang lại ước tính hàng tốt hơn SELECT col3 FROM mytable WHERE col1 = 'some value' AND col2 = 'some other value';0 Trong trường hợp này, trình tối ưu hóa hiện ước tính rằng mỗi giá trị từ t1 sẽ chỉ khớp với một hàng trong t2 Ghi đè tối ưu hóaNghe có vẻ kỳ quặc, nhưng có thể có lúc bạn muốn đánh bại hành vi tối ưu hóa của MySQL Để ghi đè thứ tự tham gia bảng của trình tối ưu hóa. Sử dụng STRAIGHT_JOIN để buộc trình tối ưu hóa sử dụng các bảng theo một thứ tự cụ thể. Nếu bạn làm điều này, bạn nên sắp xếp các bảng sao cho bảng đầu tiên là bảng có số lượng hàng nhỏ nhất sẽ được chọn. Nếu bạn không chắc đây là bảng nào, hãy đặt bảng có số lượng hàng lớn nhất trước. Nói cách khác, hãy cố gắng sắp xếp thứ tự các bảng để đưa ra lựa chọn hạn chế nhất trước. Các truy vấn hoạt động tốt hơn khi bạn có thể thu hẹp các hàng ứng cử viên có thể sớm hơn. Đảm bảo thử truy vấn có và không có STRAIGHT_JOIN. Có thể có một số lý do khiến trình tối ưu hóa không tham gia các bảng theo cách bạn nghĩ và STRAIGHT_JOIN có thể không thực sự hữu ích Một khả năng khác là sử dụng các công cụ sửa đổi FORCE INDEX, USE INDEX và IGNORE INDEX sau tên bảng trong danh sách bảng của phép nối để cho MySQL biết cách sử dụng chỉ mục. Điều này có thể hữu ích trong trường hợp trình tối ưu hóa không đưa ra lựa chọn chính xác Để làm trống một bảng với tác dụng phụ tối thiểu. Khi bạn cần làm trống hoàn toàn bảng MyISAM, cách nhanh nhất là yêu cầu máy chủ hủy bảng và tạo lại bảng dựa trên mô tả được lưu trữ trong bảng đó. tập tin frm. Để làm điều này, hãy sử dụng câu lệnh TRUNCATE TABLE SELECT col3 FROM mytable WHERE col1 = 'some value' AND col2 = 'some other value';1 Việc máy chủ tối ưu hóa việc làm trống bảng MyISAM bằng cách tạo lại bảng từ đầu giúp thao tác cực kỳ nhanh, vì không cần phải xóa từng hàng riêng lẻ Tuy nhiên, có một số tác dụng phụ của TRUNCATE TABLE có thể không mong muốn trong một số trường hợp nhất định
SELECT col3 FROM mytable WHERE col1 = 'some value' AND col2 = 'some other value';2 Đối với các bảng MyISAM, các giá trị AUTO_INCREMENT thường không được sử dụng lại khi các hàng bị xóa. (Xem Chương 3, "Làm việc với Dữ liệu trong MySQL. ") Tuy nhiên, làm trống bảng bằng cách tạo lại bảng có thể đặt lại trình tự để bắt đầu lại từ 1. Để tránh điều này, hãy sử dụng câu lệnh DELETE toàn bảng "không được tối ưu hóa" bao gồm mệnh đề WHERE tầm thường đúng SELECT col3 FROM mytable WHERE col1 = 'some value' AND col2 = 'some other value';3 Việc thêm mệnh đề WHERE buộc MySQL phải thực hiện xóa từng hàng, bởi vì nó phải đánh giá điều kiện cho từng hàng để xác định xem có nên xóa nó hay không. Câu lệnh thực thi chậm hơn nhiều nhưng vẫn giữ nguyên số thứ tự AUTO_INCREMENT hiện tại Trình tối ưu hóa SQL là gì?Tối ưu hóa truy vấn là quy trình tổng thể chọn phương tiện hiệu quả nhất để thực thi câu lệnh SQL . SQL là một ngôn ngữ phi thủ tục, vì vậy trình tối ưu hóa có thể tự do hợp nhất, sắp xếp lại và xử lý theo bất kỳ thứ tự nào. Cơ sở dữ liệu tối ưu hóa từng câu lệnh SQL dựa trên số liệu thống kê được thu thập về dữ liệu được truy cập.
Làm cách nào để cải thiện hiệu suất trong MySQL?Điều chỉnh hiệu suất máy chủ MySQL bằng mẹo để tối ưu hóa hiệu quả . Kiểm tra phần cứng và phần mềm được đề xuất. . Tối ưu hóa việc sử dụng bộ nhớ, đĩa và CPU. . Công cụ điều chỉnh hiệu suất phần mềm. . Sử dụng chỉ mục MySQL cho hiệu suất. . Cải thiện hiệu suất với InnoDB. . Tối ưu hóa truy vấn MySQL. . Điều chỉnh các tùy chọn máy chủ để tăng hiệu suất Có một hồ sơ cho MySQL?Trình cấu hình truy vấn, được tích hợp trong dbForge Studio dành cho MySQL , là công cụ tối ưu hóa truy vấn tốt nhất để điều chỉnh các truy vấn MySQL và điều tra các vấn đề về hiệu suất truy vấn một cách hiệu quả và nhanh chóng. Nó giúp xây dựng một bức tranh về cách các truy vấn được chạy để truy cập dữ liệu và những thao tác nào tác động đến ứng dụng của bạn.
Truy vấn nào nhanh hơn trong MySQL?Tìm kiếm toàn văn bản MySQL (FTS) nhanh hơn nhiều so với các truy vấn sử dụng ký tự đại diện. |
