Tìm nạp thời lượng trong MySQL Workbench

ご質問させてください。

Windows10でMySQL Workbench 8. 0. 15 を使用しています。

SQLを実行すると、右下の窓(Output)に実行したSQLとかかった時間が出ます。
かかった時間と言うのは、Duration/Fetchと表示されている内容になります。

このDurationとFetchは具体的に何を表しているのでしょうか？

SELECTをしているSQLがあり、チューニングを行っております。
チューニング前はDurationが600secでFetchが0. 063secと表示されていましたが、
チューニング後はDurationが0. 766secでFetchが100secと表示されるようになりました。

多少、SQLの書き換えは行いましたが、結果セットの行数はチューニング前とチューニング後で同じです。（3万行です。）

SELECTしているカラムは10カラム程度で、それぞれのカラムは２０バイト程度です。

色々と調べていると、Durationは実行時間と書いてるページが多いので、SQLの実行時間と理解しました。
Fetchは結果を転送するのにかかった時間でネットワークの状況によって増減すると書いてあるページが多かったです。

上記が正しいとした場合、分からない点があります。
それは、チューニング前とチューニング後で結果セットの行数が同じなのに、Fetch時間が大幅に違うということです。
同じデータ量を転送するのに、0. 063secと100secは違いすぎると思っています。

どなたかご存知の方がいらっしゃれば、ご教示頂ければ幸いです。

■追記です。
色々調べていて、パターンがわかってきました。
SQLにorder byがついている場合はDurationに時間がかかり、
order byがついていない場合はFetchに時間がかかるようです。

Thời gian tìm nạp - đo lường thời gian chuyển các kết quả đã tìm nạp mất bao lâu, điều này không liên quan đến việc thực thi truy vấn. Tôi sẽ không coi đó là tùy chọn gỡ lỗi/tối ưu hóa truy vấn sql vì thời gian tìm nạp phụ thuộc vào kết nối mạng, bản thân nó không liên quan gì đến tối ưu hóa truy vấn. Nếu thời gian tìm nạp bị tắc nghẽn thì nhiều khả năng có một số vấn đề về mạng

Ghi chú. thời gian tìm nạp có thể khác nhau trên mỗi lần thực hiện truy vấn

Khoảng thời gian - là thời gian mà truy vấn cần được thực thi. Bạn nên cố gắng giảm thiểu nó khi tối ưu hóa hiệu suất của truy vấn sql

大致意思为：

1、fetch time 是传输获取结果所消耗的时间，与sql语句执行时间无关。由于fetch时间依赖网络连接的快慢，所以不能作为优化sql语句时间参考。如果fetch time过长，需要检查网络原因。

Một kết nối MySQL liên kết (kết nối) Bàn làm việc với máy chủ MySQL. Hầu hết các hành động được thực hiện trong Workbench sau đó được thực hiện đối với máy chủ MySQL được kết nối. Mỗi kết nối MySQL chứa tập hợp các định nghĩa riêng, vì vậy bạn có thể xác định nhiều kết nối MySQL trong Workbench. Ví dụ: các kết nối có thể kết nối với các máy chủ MySQL khác nhau hoặc cùng một máy chủ MySQL với các tên người dùng khác nhau hoặc bật SSL cho một máy chủ hoặc bạn có thể thiết lập kết nối với máy chủ MySQL từ xa (trên máy chủ lưu trữ web của bạn?) bằng SSH

Đối với nhiều kết nối đến cùng một máy chủ MySQL cục bộ, bạn có thể có một kết nối sử dụng "root" với một kết nối khác sử dụng người dùng ít đặc quyền hơn. Tùy thuộc vào cách bạn thiết lập người dùng, họ có thể (hoặc không) đều có quyền xem và sử dụng cùng một cơ sở dữ liệu (thông tin). Ví dụ: bạn có thể sử dụng Workbench để định cấu hình và sử dụng người dùng ít đặc quyền hơn mà bạn sử dụng cho ứng dụng web của mình

Vì vậy, để tóm tắt, các kết nối chỉ cần kết nối với máy chủ MySQL. Nếu hai kết nối sử dụng cùng một thông tin chính xác thì kết quả trong Workbench sẽ giống hệt nhau, mặc dù đây không phải là trường hợp sử dụng phổ biến. Để biết thêm thông tin về kết nối MySQL trong MySQL Workbench, hãy xem Chương 5, Kết nối trong MySQL Workbench

Tôi đoán vấn đề là với Thời lượng, thời gian thực hiện truy vấn. Tôi nghi ngờ số liệu thống kê không tính đến quyền truy cập đĩa và bạn có thể thiếu một số chỉ mục. Lý do khác là có thể có thời gian chờ đợi để khóa, được cho là

Bạn có thể làm như sau để biết thêm chi tiết về nơi truy vấn của bạn dành thời gian

set profiling=1;

show profiles;
show profile for query 1;

Thêm câu hỏi với thẻ tương tự

Tôi biết rằng nhìn chung, tôi nên mong đợi thời gian tìm nạp chậm hơn đối với các tập kết quả lớn, nhưng tôi không hiểu tại sao quá trình tìm nạp lại chậm như vậy. Khối lượng công việc tìm nạp một số lượng lớn hàng (1-10 triệu) từ một bảng lớn (hơn 100 triệu hàng)

mysql> describe testdb.test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | NO   | PRI | NULL    |       |
| col2  | int(11) | NO   | PRI | NULL    |       |
| col3  | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

Đối với trường hợp thử nghiệm, tôi sử dụng truy vấn đơn giản nhất có thể

select * from test_table limit 1000000;

Tuy nhiên, khách hàng dường như bị tắc nghẽn khoảng 1. 5-2. 5 triệu hàng/giây trên mỗi truy vấn (1. 5 triệu trong ứng dụng khách Python, 2. 5 triệu trong Bàn làm việc của MySQL). tôi biết 2. 5 triệu hàng/giây có vẻ khá nhanh, nhưng điều đó chỉ hoạt động ở mức 30 MB/giây (2. 5M * 3 cols * int 4 byte). Tôi đang dùng macOS 10. 15. 4, MySQL5. 7. 29 được cài đặt qua Homebrew và kết nối với máy chủ qua localhost. Python đang sử dụng trình điều khiển db MySQLClient

Kế hoạch truy vấn hiển thị kết quả được chọn từ chỉ mục chính, như mong đợi

+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | my_table | NULL       | index | NULL          | PRIMARY | 12      | NULL | 10821795 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+----------+----------+-------------+

Hồ sơ truy vấn không chỉ ra bất kỳ điểm nóng nào

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000055 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000016 |
| init                 | 0.000015 |
| System lock          | 0.000021 |
| optimizing           | 0.000011 |
| statistics           | 0.000012 |
| preparing            | 0.000019 |
| explaining           | 0.000028 |
| end                  | 0.000007 |
| query end            | 0.000007 |
| closing tables       | 0.000009 |
| freeing items        | 0.000015 |
| cleaning up          | 0.000023 |
+----------------------+----------+

Và tốt nhất tôi có thể nói, toàn bộ db thử nghiệm phù hợp với vùng đệm và không có IO đĩa nào xảy ra trong quá trình truy vấn. Giá trị Innodb_buffer_pool_reads không thay đổi sau khi thực hiện truy vấn và chỉ số

select * from test_table limit 1000000;

0 từ bảng điều khiển hiệu suất của MySQL Workbench đều bằng 0 trong suốt thời gian chạy của nó

Trong các cột

select * from test_table limit 1000000;

1 của MySQL Workbench, thời lượng duy trì ổn định dưới 1ms, bất kể số lượng hàng được chọn. Tuy nhiên, thời gian tìm nạp tỷ lệ thuận với các hàng được trả về. ~0. 5 giây cho 1M và và 5. 0 giây cho 10 triệu hàng

Khi tôi quan sát các quy trình với

select * from test_table limit 1000000;

2, tôi có thể thấy MySQL tăng vọt lên 100% CPU trong một thời gian ngắn, sau đó là MySQLWorkbench tăng vọt lên 100% trong khoảng thời gian còn lại của truy vấn sau khi truy vấn hoàn tất. Thử nghiệm tương tự với ứng dụng khách Python (không thực hiện bất kỳ công việc bổ sung nào) cho thấy thời gian được chia đều hơn một chút, nhưng thật khó để đo lường

Điều đó dường như chỉ khiến trình điều khiển máy khách db hoặc chính kết nối mạng là nút cổ chai. Tôi cho rằng đó không phải là mạng vì tôi đang thử nghiệm trên máy chủ cục bộ (mặc dù tôi chưa thử nghiệm máy chủ cục bộ riêng lẻ). Có nghĩa là máy khách bị tắc nghẽn khi xử lý các hàng ở tốc độ 30MB/giây?

Cập nhật

Bao gồm trạng thái chung, biến, danh sách quy trình và trạng thái innodb được yêu cầu, nhưng lưu ý rằng đây không phải trên máy chủ chuyên dụng. Các thử nghiệm của tôi là trên MacBook Pro có RAM 16 GB, 4 nhân với Siêu phân luồng (i. e. macOS thấy 8 luồng phần cứng). Ổ cứng là một NVMe (~232k Read IOPS RND4k@QD32), nhưng như tôi đã lưu ý ở trên, tôi không quan sát thấy đĩa IO nào (và điều đó bao gồm cả việc hoán đổi/phân trang của HĐH)

Tôi muốn tách biệt hơn nữa thời lượng so với thời gian tìm nạp, vì vậy tôi đã cắt bảng thành chính xác 10 triệu hàng, sau đó so sánh một lựa chọn lớn với phiên bản tổng hợp của cùng một truy vấn bằng cách sử dụng hàm tổng hợp "rẻ tiền"

________số 8_______

Khoảng thời gian. 0. 00082 giây, Thời gian tìm nạp. 4. 729 giây

select count(col1), count(col2), count(col3) 
from test_table;

Khoảng thời gian. 2. 692 giây, Thời gian tìm nạp. 0. 000011 giây

Tôi nghĩ rằng các số liệu về thời lượng/thời gian tìm nạp hơi khó hiểu, vì tôi cho rằng thời lượng truy vấn đầu tiên chỉ bao gồm thời gian để xác định ID hàng (không đệm chúng), trong khi truy vấn thứ hai phải lấy từng hàng trong bộ nhớ, một bước trùng lặp với tìm nạp

Giả sử chi phí hoạt động là 25% cho

select * from test_table limit 1000000;

3, tức là mất khoảng 2 giây để đi qua 10 triệu hàng, vì vậy 5 triệu hàng hoặc 60 MB/giây. Giả sử cùng một chi phí thời gian truy cập cho truy vấn đầu tiên, điều đó có nghĩa là thêm ~45 MB/giây để sao chép chúng vào bộ đệm để tìm nạp

Trong mọi trường hợp, ngay cả khi giả sử chi phí dữ liệu bổ sung trên mỗi hàng, có vẻ như ở mức tối thiểu, chỉ cần truy cập các hàng trong bộ nhớ trong InnoDB là một nút cổ chai đáng kể, bất kể trình điều khiển hay mạng.

select * from test_table limit 1000000;

4 CPU% sử dụng tối đa một luồng trong khoảng thời gian của truy vấn tổng hợp, do đó, có vẻ như đây là hoạt động liên kết với CPU. Điều đó nghe có đúng không?

Tìm nạp thời lượng trong bàn làm việc của MySQL là gì?

Làm cách nào để kiểm tra thời gian thực hiện truy vấn trong MySQL Workbench?

Làm cách nào để tìm nạp dữ liệu nhanh hơn từ MySQL?

Thời gian tìm nạp là gì?