Những gì được lọc trong MySQL giải thích?

Tại sao giá trị cột được lọc luôn là 100% trong Mysql giải thích mở rộng?
1. thực hiện lệnh mở rộng giải thích Mysql để xuất một cột được lọc (MySQL5. 7 đầu ra được lọc theo mặc định), nó cho biết phần trăm số hàng trong kết quả trả về sẽ được đọc (giá trị của cột rows. Đã lọc là một giá trị rất hữu ích, bởi vì đối với các thao tác nối, kích thước tập hợp kết quả của bảng trước ảnh hưởng trực tiếp đến số chu kỳ. Tuy nhiên, kết quả kiểm tra trong môi trường của tôi là giá trị của bộ lọc luôn là 100%, nghĩa là nó vô nghĩa.

Tham khảo mysql 5 sau đây. 6 mã. Giá trị đã lọc chỉ có giá trị đối với chỉ mục và tất cả các lần quét (điều này có thể hiểu. Trong các trường hợp khác, giá trị hàng thường bằng kích thước của tập kết quả ước tính. ).
SQL/opt_explain. cc

1. Bool Giải thích_join. giải thích_rows_and_filtered ()
   
2. {
   
3. Nếu (bảng-> pos_in_table_list-> lược đồ_table)
   
4. Trả về sai;
   
5. Kiểm tra kép;
   
6. Nếu (chọn & chọn-> nhanh)
   
7. Examined_rows = rows2double (chọn-> nhanh-> bản ghi);
   
8. Khác nếu (tab-> loại = JT_INDEX_SCAN. tab-> loại = JT_ALL)
   
9. {
   
10. Nếu (tab-> giới hạn)
    
11. Examined_rows = rows2double (tab->giới hạn);
    
12. Khác
    
13. {
    
14. Table->pos_in_table_list->fetch_number_of_rows();
    
15. Examined_rows = rows2double (bảng-> tệp-> thống kê. Hồ sơ );
    
16. }
    
17. }
    
18. Khác
    
19. Examined_rows = tab->vị trí-> bản ghi_đọc;
    
20. Fmt-> mục nhập ()-> col_rows. đặt (static_cast (Đã kiểm tra_rows));
    
21. /* Thêm trường "đã lọc" */
    
22. Nếu (mô tả (DESCRIBE_EXTENDED ))
    
23. {
    
24. Phao f = 0. 0;
    
25. Nếu (đã kiểm tra)
    
26. f = 100. 0 * tab-> vị trí-> bản ghi_read/examined_rows;
    
27. Fmt-> mục nhập ()-> col_filtered. đặt (f );
    
28. }
    
29. Trả về sai;
    
30. }

Tuy nhiên, sau khi tôi quét toàn bộ bảng, kết quả lọc không chính xác. Kết quả vẫn là 100% và điều tôi mong đợi là 0. 1%.

1. Mysql> desc tb2;
   + ------- + -------------- + --- . Cánh đồng. Loại. Vô giá trị. Chìa khóa. Mặc định. Thêm.
   | Field | Type | Null | Key | Default | Extra |
   + ------- + -------------- + ------ + ----- + ---- . ID. int (11). KHÔNG. PRI. 0.
   | Id | int (11) | NO | PRI | 0 |
   . C1. int (11). VÂNG. VÔ GIÁ TRỊ.
   . C2. véc ni (100). VÂNG. VÔ GIÁ TRỊ.
   + ------- + -------------- + ------ + ----- + ---- . 00 giây)
   3 rows in set (0.00 sec)

   Mysql> giải thích lựa chọn mở rộng * từ tb2 trong đó c1 <100;+ ---- + ------------- + --- . ID. lựa chọn đối tượng. cái bàn. loại. có thể_keys. Chìa khóa. key_len. giới thiệu. hàng. lọc. Thêm. + ---- + ------------- + ------- + ------ + ------ . 1. GIẢN DỊ. tb2. TẤT CẢ CÁC. VÔ GIÁ TRỊ. 996355. 100. 00. Sử dụng ở đâu. + ---- + ------------- + ------- + ------ + ------ . 96 giây)
   + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + -------- + ---------- + ------------- +
   | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
   + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + -------- + ---------- + ------------- +
   | 1 | SIMPLE | tb2 | ALL | NULL | 996355 | 100.00 | Using where |
   + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + -------- + ---------- + ------------- +
   1 row in set, 1 warning (10 min 29.96 sec)

   Mysql> chọn số (*) từ tb2 trong đó c1 <100;+ ---------- +. Đếm (*). + ---------- +. 1, 1001. + ---------- +1 hàng trong bộ (1. 99 giây)
   + ---------- +
   | Count (*) |
   + ---------- +
   | 1, 1001 |
   + ---------- +
   1 row in set (1.99 sec)

Thông qua theo dõi gdb, thấy rằng nhánh của Mã là chính xác, nhưng các giá trị sau không chính xác.

1. (Gdb) p table->file->stats. bản ghi
   
2. $18 = 996355
   
3. (Gdb) p tab-> vị trí-> bản ghi_read
   
4. $19 = 996355

Tab trước-> vị trí-> bản ghi_read phải là số hàng được trả về ước tính. Giá trị chính xác phải là khoảng 1001, thay vì kích thước đầy đủ của bảng là 996355

2. Tại sao lại có vấn đề trên? .
Giống như các cơ sở dữ liệu chính thống khác, MySQL tự động cần thu thập số liệu thống kê để tạo kế hoạch thực hiện tốt hơn. Bạn cũng có thể sử dụng bảng phân tích để thu thập số liệu thống kê theo cách thủ công. Các số liệu thống kê thu thập được lưu trữ trong mysql. innodb_table_stats và mysql. trong innodb_index_stats.
Tham khảo. http. // nhà phát triển. mysql. com/doc/refman/5. 6/vi/innodb-persistent-stats. html#innodb-persistent-stats-tables

Tuy nhiên, đây không phải là điểm. Vấn đề là, khi bạn xem hai bảng này, bạn sẽ thấy rằng có rất ít số liệu thống kê được thu thập bởi MySQL

1. Mysql> chọn * từ mysql. innodb_table_stats trong đó table_name = 'tb2 ';
   + --------------- + ------------ + --- . Tên cơ sở dữ liệu. tên_bảng. cập nhật cuối cùng. n_rows. clustered_index_size. sum_of_other_index_sizes.
   | Database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
   + --------------- + ------------ + ----------- . Bài kiểm tra. tb2. 06. 26. 54. 996355. 3877. 0.
   | Test | tb2 | 06:26:54 | 996355 | 3877 | 0 |
   + --------------- + ------------ + ----------- . 00 giây)
   1 row in set (0.00 sec)

   Mysql> chọn * từ mysql. innodb_index_stats trong đó table_name = 'tb2 ';
   + --------------- + ------------ + --- . Tên cơ sở dữ liệu. tên_bảng. tên chỉ mục. cập nhật cuối cùng. stat_name. stat_value. cỡ mẫu. stat_description.
   | Database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
   + --------------- + ------------ + ----------- . Bài kiểm tra. tb2. SƠ CẤP. 06. 26. 54. n_diff_pfx01. 996355. 20. Tôi.
   | Test | tb2 | PRIMARY | 06:26:54 | n_diff_pfx01 | 996355 | 20 | id |
   . Bài kiểm tra. tb2. SƠ CẤP. 06. 26. 54. n_leaf_pages. 3841. VÔ GIÁ TRỊ. Số trang lá trong chỉ mục.
   . Bài kiểm tra. tb2. SƠ CẤP. 06. 26. 54. kích thước. 3877. VÔ GIÁ TRỊ. Số trang trong chỉ mục.
   + --------------- + ------------ + ----------- . 00 giây)
   3 rows in set (0.00 sec)
   

Có hai thông tin quan trọng. Một là tổng số bản ghi của bảng (n_rows) và một là số lượng giá trị duy nhất của các cột trong chỉ mục (n_diff_pfx01 ). Điều đó có nghĩa là, MySQL không tính phân phối giá trị của các cột không có chỉ mục. trong ví dụ truy vấn trước, vì c1 không được lập chỉ mục, do đó, MySQL không thể ước tính số lượng bản ghi mà "c1 <100" cuối cùng sẽ lọc. Theo cách này, giá trị của bộ lọc hiếm khi hiệu quả. Để tạo một chỉ mục trên cột xuất hiện trong điều kiện ở đâu, kế hoạch thực hiện không nên đi qua phạm vi hoặc quét tham chiếu của chỉ mục, cột được lọc là vô ích vì nó thực hiện quét toàn bộ bảng hoặc quét chỉ mục ghi đè

3. Hậu quả của việc thiếu số liệu thống kê của MySQL là gì?
Thật khó để tưởng tượng rằng nếu không có chỉ mục, MySQL có thể tạo ra các kế hoạch thực thi với hiệu suất kém, chẳng hạn như trình tự nối sai giữa các bảng lớn và nhỏ .

1. Mysql> giải thích số lượng lựa chọn mở rộng (*) từ tb1, tb2 trong đó tb1. c1 = tb2. c1 và tb2. c2 = 'xx ';
   + ---- + ------------- + ------- + ------ + . ID. lựa chọn đối tượng. cái bàn. loại. có thể_keys. Chìa khóa. key_len. giới thiệu. hàng. lọc. Thêm.
   | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
   + ---- + ------------- + ------- + ------ + ------ . 1. GIẢN DỊ. tb1. TẤT CẢ CÁC. VÔ GIÁ TRỊ. 1000. 100. 00. VÔ GIÁ TRỊ.
   | 1 | SIMPLE | tb1 | ALL | NULL | 1000 | 100.00 | NULL |
   . 1. GIẢN DỊ. tb2. TẤT CẢ CÁC. VÔ GIÁ TRỊ. 996355. 100. 00. Sử dụng ở đâu; .
   + ---- + ------------- + ------- + ------ + ------ . 00 giây)
   2 rows in set, 1 warning (0.00 sec)

Mặc dù Bảng t1 là một bảng nhỏ và bảng tb2 là một bảng lớn nhưng tập kết quả là 0 sau tb2. c2 = 'xx' được thêm vào bảng tb2, do đó, quét bảng tb2 là lựa chọn tốt hơn cho hiệu suất.
Đối với cùng một truy vấn, PostgreSQL cung cấp kế hoạch thực thi tốt hơn. Đầu tiên, nó quét bảng t2 để tái chế và quét bảng t1.

1. S = # giải thích chọn số lượng (*) từ tb1, tb2 trong đó tb1. c1 = tb2. c1 và tb2. c2 = 'xx';
   
2. KẾ HOẠCH HỎI
   
3. --------------------------------------------------
   
4. Tổng hợp (chi phí = 20865. 50. 20865. 51 hàng = 1 chiều rộng = 0)
   
5. -> Vòng lặp lồng nhau (chi phí = 0. 00. 20865. 50 hàng = 1 chiều rộng = 0)
   
6. Tham gia bộ lọc. (tb1. c1 = tb2. c1)
   
7. -> Seq Scan trên tb2 (chi phí = 0. 00. 20834. 00 hàng = 1 chiều rộng = 4)
   
8. Lọc. (c2). văn bản = 'xx'. chữ)
   
9. -> Seq Scan trên tb1 (chi phí = 0. 00. 19. 00 hàng = 1000 chiều rộng = 4)
   
10. (6 hàng)

Sau đây là bảng so sánh thời gian thực hiện

MySQL lấy 0. 34 giây

1. Mysql> chọn số (*) từ tb1, tb2 trong đó tb1. c1 = tb2. c1 và tb2. c2 = 'xx';
   
2. + ---------- +
   
3. Đếm (*)
   
4. + ---------- +
   
5. 0
   
6. + ---------- +
   
7. 1 hàng trong bộ (0. 34 giây)

Mất 0. 139 giây cho PostgreSQL

1. S = # chọn số lượng (*) từ tb1, tb2 trong đó tb1. c1 = tb2. c1 và tb2. c2 = 'xx';
   
2. Đếm
   
3. -------
   
4. 0
   
5. (1 hàng)
   
6. Thời gian. 139. 600 MS

Trong ví dụ trên, sự khác biệt về hiệu suất không lớn lắm. nếu tb2. c2 = 'xx' bị xóa, sự khác biệt sẽ rất lớn.
Mysql mất 1 phút 8 giây

1. Mysql> giải thích số lần chọn (*) từ tb1, tb2 trong đó tb1. c1 = tb2. c1;
   + ---- + ------------- + ------- + ------ + ---- . ID. lựa chọn đối tượng. cái bàn. loại. có thể_keys. Chìa khóa. key_len. giới thiệu. hàng. Thêm.
   | Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
   + ---- + ------------- + ------- + ------ + ------ . 1. GIẢN DỊ. tb1. TẤT CẢ CÁC. VÔ GIÁ TRỊ. 1000. VÔ GIÁ TRỊ.
   | 1 | SIMPLE | tb1 | ALL | NULL | 1000 | NULL |
   . 1. GIẢN DỊ. tb2. TẤT CẢ CÁC. VÔ GIÁ TRỊ. 996355. Sử dụng ở đâu; .
   + ---- + ------------- + ------- + ------ + ------ . 00 giây)
   2 rows in set (0.00 sec)

   Mysql> chọn số (*) từ tb1, tb2 trong đó tb1. c1 = tb2. c1;
   + ---------- +
   . Đếm (*).
   + ---------- +
   . 1, 9949.
   + ---------- +
   1 hàng trong bộ (1 phút 8. 26 giây)
   

PostgreSQL chỉ mất 0. 163 giây

1. S = # giải thích số lượng chọn (*) từ tb1, tb2 trong đó tb1. c1 = tb2. c1;
   
2. KẾ HOẠCH HỎI
   
3. --------------------------------------------------
   
4. Tổng hợp (chi phí = 23502. 34. 23502. 35 hàng = 1 chiều rộng = 0)
   
5. -> Hash Tham gia (chi phí = 31. 50. 23474. 97 hàng = 10947 chiều rộng = 0)
   
6. điều kiện băm. (tb2. c1 = tb1. c1)
   
7. -> Seq Scan trên tb2 (chi phí = 0. 00. 18334. 00 hàng = 1000000 chiều rộng = 4)
   
8. -> Băm (chi phí = 19. 00. 19. 00 hàng = 1000 chiều rộng = 4)
   
9. -> Seq Scan trên tb1 (chi phí = 0. 00. 19. 00 hàng = 1000 chiều rộng = 4)
   
10. (6 hàng)
    
11. Thời gian. 0. 690 MS
    
12. S = # chọn số (*) từ tb1, tb2 trong đó tb1. c1 = tb2. c1;
    
13. Đếm
    
14. -------
    
15. 10068
    
16. (1 hàng)
    
17. Thời gian. 163. MS 868

Tuy nhiên, sự khác biệt về hiệu suất này không liên quan gì đến số liệu thống kê, bởi vì PG hỗ trợ Nest Loop Join, Merge Join và Hash Join, trong khi MySQL chỉ hỗ trợ Nest Loop Join, nếu thiếu chỉ số Nest Loop, thao tác Join sẽ bị chậm

4. kết luận 1. mySQL có rất ít số liệu thống kê, chỉ có số lượng hàng trong bảng và số lượng giá trị duy nhất trong cột chỉ mục, điều này khiến trình tối ưu hóa MySQL thường không hiểu đúng về kích thước dữ liệu và đưa ra kế hoạch thực hiện với hiệu suất kém.
2. Hiệu quả của các hoạt động tham gia MySQL phụ thuộc rất nhiều vào các chỉ mục (Tôi đã từng giúp bạn điều chỉnh các câu lệnh MySQL SQL để lập chỉ mục hai lần). Điều đó không có nghĩa là các phép nối PostgreSQL không yêu cầu chỉ mục, nhưng nó không phản hồi nhanh vì MySQL thiếu chỉ mục. Trong ví dụ MySQL chạy hơn một phút, sau khi thêm chỉ mục, thời gian thực thi của cả MySQL và PG ngay lập tức giảm xuống dưới 10 mili giây. Do đó, khi thiết kế một bảng, các nhà phát triển nên đánh giá các phương thức truy vấn khả thi và xây dựng các chỉ mục (không ít hơn cũng không nhiều hơn).
3. Ngược lại, PG không chỉ đếm phân phối giá trị của tất cả các cột mà còn chứa thông tin như biểu đồ và giá trị thường xuyên bên cạnh các giá trị duy nhất. Nó hỗ trợ trình tối ưu hóa PG để đưa ra quyết định chính xác. Đây cũng là lý do để đồn đoán. Cộng đồng PG cho rằng trình tối ưu hóa PostgreSQL đủ thông minh và không cần thêm chức năng gợi ý tương tự như Oracle vào nhân của PG (vì gợi ý có thể bị lạm dụng, điều này khiến hệ thống khó bảo trì. Tuy nhiên, bạn có thể tự cài đặt plug-in pg_hint_plan nếu bạn thực sự muốn sử dụng nó ).

Lọc trong MySQL là gì?

Ý nghĩa của bộ lọc trong SQL giải thích là gì?

Lọc các bản ghi cơ sở dữ liệu có nghĩa là gì?

Giải thích nghĩa là gì trong MySQL?