Kế hoạch giải thích trong MySQL là gì?
|
Thông tin quan trọng nhất nằm trong cột Show
Trong hướng dẫn này, chúng ta sẽ đi sâu vào thế giới của kế hoạch thực hiện truy vấn MySQL. Chúng tôi sẽ chia nó thành các khái niệm cơ bản và tìm hiểu cách thức hoạt động cũng như cách sử dụng nó trong các quy trình tối ưu hóa SQL của chúng tôi
Trình tối ưu hóa truy vấn MySQL là một chức năng tích hợp sẵn của cơ sở dữ liệu sẽ tự động chạy khi bạn thực hiện một truy vấn. Công việc của nó là thiết kế một kế hoạch thực hiện truy vấn MySQL tối ưu cho mọi truy vấn đơn lẻ được thực thi. Kế hoạch giải thích của MySQL cho phép bạn xem kế hoạch bằng cách sử dụng từ khóa EXPLAIN làm tiền tố cho yêu cầu của bạn MySQL Giải thích kế hoạch là gì?EXPLAIN ANALYZE là một công cụ lập hồ sơ cho các truy vấn của bạn, công cụ này sẽ cho bạn biết MySQL dành thời gian cho truy vấn của bạn ở đâu và tại sao. Nó sẽ lập kế hoạch cho truy vấn, thiết bị và thực hiện nó trong khi đếm các hàng và đo thời gian dành cho các điểm khác nhau trong kế hoạch thực hiện. Khi quá trình thực hiện kết thúc, EXPLAIN ANALYZE sẽ in kế hoạch và các phép đo thay vì kết quả truy vấn Từ khóa GIẢI THÍCH là một công cụ cực kỳ mạnh mẽ để hiểu và tối ưu hóa các truy vấn MySQL. Họ đưa ra lời giải thích và thông tin chi tiết về lý do truy vấn của bạn chậm hoặc hoạt động kém. Tuy nhiên, chúng tôi đã thấy các DBA và nhà phát triển hiếm khi sử dụng nó. Vì bạn là vậy nên đó là dấu hiệu cho thấy bạn muốn thực hiện các truy vấn của mình nhanh hơn. Vì vậy, hãy tìm hiểu cách chúng ta có thể diễn giải kết quả mà câu lệnh EXPLAIN mang lại cho chúng ta Cách đúng để diễn giải kết quả EXPLAIN Trong cuộc sống hàng ngày, chúng ta thường có xu hướng hỏi về giá cả của hàng hóa trước khi thực sự mua chúng. Tương tự, trong lĩnh vực kế hoạch giải thích của MySQL, công cụ EXPLAIN giúp tìm nạp chi phí chạy của một truy vấn trước khi nó thực sự được thực thi Công cụ EXPLAIN trong MySQL mô tả cách DML sẽ được thực thi và bao gồm cả cấu trúc bảng. Điều quan trọng cần lưu ý ở đây là kể từ MySQL 5. 7, các lệnh DML (Chọn, Cập nhật, Xóa, Chèn và Thay thế) được cho phép trong GIẢI THÍCH—Vì vậy, chúng tôi sẽ không chỉ đề cập đến CHỌN trong phần giải thích của mình Hãy xem cú pháp của EXPLAIN {GIẢI THÍCH. DIỄN TẢ. DESC} tbl_name [tên_cột. hoang dã] {GIẢI THÍCH. DIỄN TẢ. DESC} [explain_type] {explainable_stmt. ĐỂ KẾT NỐI connection_id} giải thích_type. {. ĐỊNH DẠNG = format_name } format_name. { TRUYỀN THỐNG. JSON Hãy bắt đầu bằng cách phân tích đầu ra của một truy vấn đơn giản sử dụng từ khóa EXPLAIN và sau đó tiến tới những truy vấn phức tạp hơn. Trước khi chúng tôi tiếp tục, điều quan trọng là đảm bảo rằng bạn có đặc quyền CHỌN để sử dụng công cụ GIẢI THÍCH và đặc quyền SHOW VIEW để làm việc với các dạng xem Đây là một ví dụ Vì chúng tôi đã sử dụng EXPLAIN trong truy vấn ở trên nên chúng tôi có thể thấy các bảng thiếu chỉ mục. Điều này cho phép bạn thực hiện các điều chỉnh cần thiết và tối ưu hóa các truy vấn của mình. Hãy nhớ rằng bạn có thể cần chạy EXPLAIN để đưa truy vấn của mình đến mức tối ưu Khi chúng tôi làm việc với rất nhiều tbl_example, chúng tôi thường thấy một số mẫu trong mối quan tâm mà chúng mang lại cho chúng tôi. Đây là một trong những câu hỏi phổ biến nhất mà chúng tôi nhận được. Tại sao truy vấn của tôi không sử dụng các chỉ mục mà chúng tôi đã tạo? Không có câu trả lời duy nhất cho lý do tại sao trình tối ưu hóa MySQL không sử dụng chỉ mục. Tuy nhiên, một trong những nguyên nhân chính là do số liệu thống kê chưa cập nhật. Tin vui là bạn có thể làm mới số liệu thống kê mà trình tối ưu hóa MySQL sử dụng bằng cách chạy lệnh sau. PHÂN TÍCH BẢNG [tên_bảng]; Ví dụ: đây là cách bạn có thể chạy nó trên bảng tbl_example PHÂN TÍCH BẢNG tbl_example; Hình ảnh bên dưới mô tả đầu ra của lệnh ANALYZE trên bảng tbl_example Một lời cảnh báo. Nếu bạn đang xử lý các bảng có hàng triệu hàng, lệnh ANALYZE có thể khóa bảng trong một khoảng thời gian ngắn. Vì vậy, chúng tôi khuyên bạn nên thực hiện lệnh này trong thời gian tải cơ sở dữ liệu thấp Dưới đây là chế độ xem các cột kết quả để thực thi lệnh EXPLAIN PLAN trên bản phát hành mới nhất của MySQL là 8. x Hãy đi sâu vào từng hàng bạn thấy trong bảng trên 1. id (tên JSON. select_id)Trường id là một tham chiếu tuần tự được sử dụng trong truy vấn của MySQL. Quan sát đầu ra của lệnh EXPLAIN có nhiều dòng sẽ cho thấy rằng đầu ra có đánh số thứ tự cho các hàng 2. select_type (tên JSON. không ai)Trường loại chọn cung cấp nhiều thông tin nhất so với các trường khác. Nó chứa các tài liệu tham khảo về các câu lệnh DML như CHỌN và nó cũng cho biết cách nó sẽ được giải thích bởi MySQL Dưới đây là các giá trị khác nhau mà tùy chọn select_type cung cấp 3. bảng (tên JSON. tên_bảng)Trường này đại diện cho tên của bảng mà kế hoạch GIẢI THÍCH sử dụng. 4. phân vùng (tên JSON. phân vùng)Nếu bảng được truy vấn sử dụng có các phân vùng, thì trường này sẽ giải thích chi tiết về phân vùng mà truy vấn sẽ sử dụng 5. gõ (tên JSON. Loại truy cập)Trường loại giải thích loại liên kết đang được sử dụng. Trường loại có thể đại diện cho nhiều loại liên kết có thể thực hiện được như được mô tả trong bảng sau. Cột 1Cột 2hệ thốngÁp dụng cho các bảng hệ thống và chứa một bản ghi. constLoại này đại diện cho một giá trị không thay đổi. Nó nhanh vì bản ghi của loại này sẽ chỉ được đọc một lần. Đây là một ví dụ. CHỌN * TỪ tbl_example WHERE ID=1;eq_refViệc sử dụng loại này là một trong những lựa chọn tốt nhất ngoài loại const. Lý do là eq_ref sẽ được sử dụng trong tất cả các khả năng lập chỉ mục và một bản ghi được đọc trong số các kết hợp của các bảng trước đó. Đây là một ví dụ. CHỌN * TỪ tbl_example, hóa đơn WHERE tbl_example. ID=hóa đơn. clientID;refTất cả các bản ghi được tìm thấy trong một bảng được so khớp trong chỉ mục. Để có hiệu suất tối ưu, loại ref cần được sử dụng với cột được lập chỉ mục. Đây là một ví dụ. CHỌN * TỪ tbl_example WHERE ID=1;fulltext Loại này được sử dụng đặc biệt để thực hiện tìm kiếm toàn văn trên một chỉ mục. ref_or_null Loại này tương tự như loại ref nhưng ngoại trừ MySQL sẽ thực hiện một bước bổ sung ở đây để phát hiện các hàng có giá trị NULL. Đây là một ví dụ. CHỌN * TỪ tbl_example WHERE ID=1 OR last_billing IS NULL;Cột 1Cột 2index_mergeLoại này cho thấy Tối ưu hóa Hợp nhất đang được sử dụng. Vì loại này được sử dụng bởi các chỉ mục, nên loại key_len sẽ chứa danh sách giá trị lớn hơn. unique_subqueryKhi truy vấn phụ được sử dụng, loại này sẽ thay thế loại eq_ref bằng một số giá trị của hàm IN. Đây là một ví dụ. 10 IN (ID CHỌN TỪ tbl_example WHERE ID < 100000)index_subquery Loại này thường được sử dụng khi có các giá trị không phải là duy nhất. Nó khá giống với kiểu unique_subquery nhưng nó sẽ thay thế hàm IN. phạm vi Loại này sẽ được sử dụng trong so sánh mở rộng giữa hai giá trị. Lệnh kế hoạch EXPLAIN sẽ hiển thị các chỉ mục đang được sử dụng. Đây là một ví dụ. CHỌN * TỪ tbl_example WHERE ID GIỮA 1000 đến 2000; chỉ mục Loại chỉ mục giống như một phạm vi với ngoại lệ là quá trình quét toàn bộ được thực hiện ở chỉ mục chứ không phải ở cấp độ bảng. Nếu tất cả các tiêu chí đáp ứng với chỉ mục, thì sẽ có một cột bổ sung với nhiều giải thích hơn. allMặc dù loại này tồn tại, nhưng nó không được khuyến nghị vì nó chỉ ra rằng MySQL sẽ quét toàn bộ bảng và nó sẽ không sử dụng bất kỳ chỉ mục nào.
Mẹo tối ưu hóa chuyên nghiệp. Khi bạn đang thực hiện tối ưu hóa và cố gắng làm cho truy vấn của mình chạy nhanh hơn, hãy kiểm tra thông tin trong cột BỔ SUNG. Xem liệu bạn có thể tìm thấy các thông báo chẳng hạn như “Sử dụng sắp xếp tệp và sử dụng bảng tạm thời”. Chúng tôi sẽ đề cập đến cách xử lý các tình huống trong đó các thông báo này xuất hiện sau, Tiếp theo, hãy xem những thông báo quan trọng nhất mà bạn sẽ tìm thấy trong chế độ xem GIẢI THÍCH
CHỌN * TỪ tbl_example TRÁI THAM GIA tbl_example_history TRÊN tbl_example. id=tbl_example_history. id Ở ĐÂU t2. id LÀ NULL; Bây giờ, hãy xem xét rằng tbl_example_history. id được định nghĩa là KHÔNG NULL. Trong trường hợp này, MySQL quét bảng tbl_example và tìm kiếm các hàng trong tbl_example_history sử dụng các giá trị từ tbl_example. cột id. Nếu MySQL tìm thấy một dòng phù hợp trong tbl_example_history, nó sẽ biết rằng tbl_example_history. id không bao giờ có thể là NULL và không quét phần còn lại của các hàng trong bảng tbl_example_history để tìm cùng một giá trị ID
Thông qua hướng dẫn này, giờ đây bạn đã học được cách đọc công cụ kế hoạch EXPLAIN một cách hiệu quả và cách diễn giải chế độ xem kế hoạch giải thích MySQL. Bạn cũng đã hiểu sâu hơn về các tùy chọn và thông báo khác nhau có thể hiển thị cho bạn mỗi khi bạn chạy GIẢI THÍCH cho một truy vấn Kế hoạch giải thích trong SQL là gì?Kết quả KẾ HOẠCH GIẢI THÍCH cho phép bạn xác định xem trình tối ưu hóa có chọn một kế hoạch thực hiện cụ thể hay không , chẳng hạn như tham gia các vòng lặp lồng nhau. Nó cũng giúp bạn hiểu các quyết định của trình tối ưu hóa, chẳng hạn như lý do tại sao trình tối ưu hóa chọn phép nối vòng lặp lồng nhau thay vì phép nối hàm băm và cho phép bạn hiểu hiệu suất của truy vấn.
Việc sử dụng giải thích trong MySQL là gì?Câu lệnh EXPLAIN cung cấp thông tin về cách MySQL thực thi các câu lệnh . EXPLAIN hoạt động với các câu lệnh SELECT , DELETE , INSERT , REPLACE và UPDATE. Khi EXPLAIN được sử dụng với một câu lệnh có thể giải thích được, MySQL sẽ hiển thị thông tin từ trình tối ưu hóa về kế hoạch thực hiện câu lệnh.
Kế hoạch truy vấn giải thích cho bạn thấy điều gì?Lệnh SQL EXPLAIN QUERY PLAN được sử dụng để lấy mô tả cấp cao về chiến lược hoặc kế hoạch mà SQLite sử dụng để triển khai một truy vấn SQL cụ thể. Most significantly, EXPLAIN QUERY PLAN reports on the way in which the query uses database indices.
Làm cách nào để đọc kế hoạch giải thích trong MySQL?Để xem kế hoạch thực hiện được giải thích trực quan, thực hiện truy vấn của bạn từ trình soạn thảo SQL, sau đó chọn Kế hoạch thực hiện trong tab kết quả truy vấn . Kế hoạch thực thi mặc định là Visual Explain , nhưng nó cũng bao gồm chế độ xem Giải thích dạng bảng tương tự như những gì bạn thấy khi thực thi EXPLAIN trong máy khách MySQL. |
