MySQL Loose Scan Optimization: Đánh giá hiệu suất so sánh giữa PostgreSQL và Microsoft

Cách chung nhất để đáp ứng mệnh đề GROUP BY là quét toàn bộ bảng hoặc chỉ mục và chỉ trích xuất các giá trị riêng biệt từ nó. Có thể có 2 chiến lược trong hoạt động này.

Tập hợp băm

Kỹ thuật này thường được sử dụng khi truy vấn cần nhóm dữ liệu dựa trên các cột không được lập chỉ mục nhất định. Trong Tập hợp băm, một bảng băm được xây dựng, trong đó các khóa biểu thị sự kết hợp duy nhất của các giá trị theo từng nhóm.

Khi công cụ cơ sở dữ liệu quét qua các hàng, nó sẽ tính toán giá trị băm cho các giá trị theo từng nhóm của từng hàng và lưu trữ dữ liệu tổng hợp tương ứng với từng giá trị băm trong bảng băm.

Mặc dù phương pháp này có thể tốn nhiều bộ nhớ đối với các tập dữ liệu lớn, nhưng đây thường là cách tiếp cận nhanh nhất khi máy chủ có đủ bộ nhớ để lưu trữ bảng băm.

Tổng hợp luồng

Tập hợp luồng được sử dụng khi dữ liệu được nhóm đã được sắp xếp hoặc gần được sắp xếp trên các cột theo nhóm. Khi luồng dữ liệu đến, công cụ cơ sở dữ liệu sẽ so sánh hàng hiện tại với hàng trước đó.

Nếu hàng hiện tại thuộc cùng một nhóm, công cụ cơ sở dữ liệu sẽ tiếp tục tổng hợp. Khi một nhóm mới bắt đầu, kết quả tổng hợp của nhóm trước đó được trả về và một tổng hợp mới bắt đầu.

Tập hợp luồng sử dụng ít bộ nhớ hơn so với Tập hợp băm, nhưng nó yêu cầu dữ liệu phải được sắp xếp, điều này có thể liên quan đến thao tác sắp xếp bổ sung nếu dữ liệu chưa được sắp xếp.

Nhưng cả hai chiến lược này vẫn yêu cầu quét toàn bộ cột và có một chiến lược tốt hơn khi xử lý các cột có số lượng thẻ thấp mà PostgreSQL và MS SQL Server không có, nhưng MySQL thì có.

Quét rời là gì?

Loose Scan là một kỹ thuật tối ưu hóa MySQL nâng cao có thể áp dụng trong ngữ cảnh của một số hoạt động NHÓM THEO nhất định, đặc biệt là trong các tình huống có số lượng hàng tương đối nhỏ đang được xử lý so với tổng số hàng trong bảng.

Kỹ thuật này cải thiện đáng kể hiệu suất của các truy vấn bằng cách giảm lượng dữ liệu cần thiết để đọc từ cơ sở dữ liệu.

Nguyên tắc cơ bản của quét rời

Về bản chất, kỹ thuật Quét lỏng lẻo hoạt động trên một nguyên tắc đơn giản: thay vì quét toàn bộ chỉ mục để tìm các hàng đủ điều kiện (còn gọi là quét 'chặt chẽ'), nó sẽ quét 'lỏng lẻo' hàng phù hợp đầu tiên cho mỗi nhóm. Sau khi tìm thấy một trận đấu, nó ngay lập tức chuyển sang nhóm tiếp theo.

Phương pháp này đảm bảo giảm số lượng hàng cần được đánh giá, do đó giảm tổng thời gian thực hiện truy vấn.

Bên cạnh MySQL, một kỹ thuật tương tự cũng được thực hiện trong các công cụ cơ sở dữ liệu khác. Nó được gọi là "Bỏ qua quét" trong tiên tri , SQLite , Và GiánDB , “Quét nhảy” trong DB2 , và “Quét kết hợp” trong YugabyteDB .

Thiết lập môi trường

Tôi đoán đó là đủ lý thuyết; hãy chuyển sang phần thực hành và thực hiện phân tích so sánh Loose Scan trong MySQL so với PostgreSQL và Microsoft SQL Server. Tôi sẽ sử dụng bộ chứa docker mới nhất với MySQL 8.0.33, PostgreSQL 15.3 và MS SQL 2022-CU4 trên máy tính xách tay của mình.

Tôi sẽ tạo một bảng có 1 triệu hàng và ba cột kiểu dữ liệu số nguyên với các số lượng khác nhau. Cột đầu tiên có 100 nghìn giá trị duy nhất, 1 nghìn thứ hai và cột thứ ba chỉ có 10 giá trị duy nhất.

Tôi sẽ tạo ba chỉ mục không nhóm riêng biệt và chạy các truy vấn NHÓM THEO trên mỗi cột. Mỗi truy vấn sẽ được thực hiện 5 lần mà không tính toán thời gian đã trôi qua chỉ để khởi động cơ sở dữ liệu và sau đó thêm 20 lần nữa và chúng tôi sẽ so sánh thời gian thực hiện trung bình sau đó.

Vì vậy, tôi đã cố gắng đặt tất cả các công cụ cơ sở dữ liệu vào một tình huống khá bình đẳng.

Có một tập lệnh tôi đã sử dụng để khởi tạo các bảng mẫu trong tất cả các cơ sở dữ liệu:

 -- MySQL create table numbers ( id int not null ); insert into numbers(id) with tmp as ( select a.id + b.id * 10 + c.id * 100 + d.id * 1000 as id from (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c cross join (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d ) select id from tmp; create table group_by_table ( id int not null, a int not null, b int not null, c int not null, primary key (id) ); insert into group_by_table(id, a, b, c) with tmp as ( select a.id + b.id * 10000 as id from numbers as a cross join numbers as b ) select id, floor(rand() * 100000) as a, floor(rand() * 1000) as b, floor(rand() * 10) as c from tmp where id < 1000000; create index idx_group_by_table_a on group_by_table(a); create index idx_group_by_table_b on group_by_table(b); create index idx_group_by_table_c on group_by_table(c); -- PostgreSQL create table group_by_table ( id int not null, a int not null, b int not null, c int not null, primary key (id) ); insert into group_by_table(id, a, b, c) select id, floor(random() * 100000) as a, floor(random() * 1000) as b, floor(random() * 10) as c from generate_series(1, 1000000, 1) as numbers(id); create index idx_group_by_table_a on group_by_table(a); create index idx_group_by_table_b on group_by_table(b); create index idx_group_by_table_c on group_by_table(c); -- MS SQL Server create table group_by_table ( id int not null, a int not null, b int not null, c int not null, primary key clustered (id) ); with tmp as ( select row_number() over (order by (select 1)) - 1 as id from sys.all_columns as a cross join sys.all_columns as b ) insert into group_by_table(id, a, b, c) select id, floor(rand(checksum(newid())) * 100000) as a, floor(rand(checksum(newid())) * 1000) as b, floor(rand(checksum(newid())) * 10) as c from tmp where id < 1000000; create nonclustered index idx_group_by_table_a on group_by_table(a); create nonclustered index idx_group_by_table_b on group_by_table(b); create nonclustered index idx_group_by_table_c on group_by_table(c);

Kết quả

Tất cả các cơ sở dữ liệu hoạt động khá giống nhau trên cột A, nơi mà số lượng dữ liệu cao (100 nghìn giá trị duy nhất trong số 1 triệu hàng). Tổng thời gian thực thi của PostgreSQL là 3,57 giây, MS SQL Server - 2,72 giây và MySQL - 3,44 giây.

Nhưng ở cột B, nơi mà cardinality chỉ có 1000 giá trị duy nhất, tổng thời gian thực hiện của MySQL giảm xuống còn 70,76 mili giây, trong khi PostgreSQL thực hiện trong 1,56 giây và MS SQL Server là 2,52 giây.

Vì vậy, MySQL hoàn thành truy vấn thứ hai nhanh hơn 22 lần so với PostgreSQL và nhanh hơn 35 lần so với MS SQL Server.

Tình hình thậm chí còn tốt hơn ở cột C, nơi chỉ có 10 giá trị duy nhất: MySQL - 16,66ms, PostgreSQL - 1,58 giây và MS SQL Server - 2,55 giây.

Trong ví dụ trước, MySQL cực kỳ nhanh và vượt trội hơn PostgreSQL gần 95 lần và MS SQL Server hơn 150 lần.

Dưới đây, có một hình ảnh trực quan sử dụng thang logarit. Nó hiển thị tổng thời gian thực hiện sau 20 lần thực hiện.

Có thể làm gì trong MS SQL và PostgreSQL để cải thiện hiệu suất?

Mặc dù PostgreSQL và MS SQL Server hiện tại thiếu khả năng tối ưu hóa như vậy, nhưng bạn có thể thực hiện một mẹo nhỏ để cải thiện hiệu suất của các truy vấn GROUP BY trên các công cụ này. Ý tưởng là thực hiện một số tra cứu trong chỉ mục, thay vì dựa vào Quét chỉ mục đầy đủ mặc định.

Ví dụ: trong PostgreSQL, bạn có thể thực hiện truy vấn đệ quy để tìm tất cả các giá trị duy nhất. Lần lặp đầu tiên chọn giá trị nhỏ nhất từ cột, trong khi mọi lần lặp khác chọn giá trị tiếp theo lớn hơn giá trị trước đó.

 with recursive t as ( select min(a) as x from group_by_table union all select (select min(a) from group_by_table where a > tx) from t where tx is not null ) select count(*) from ( select x from t where x is not null union all select null where exists (select 1 from group_by_table where a is null) ) as tmp;

Chúng ta cũng có thể thực hiện thủ thuật tương tự trong MS SQL Server. Nhưng, thật không may, các truy vấn đệ quy của MS SQL Server không hỗ trợ các toán tử TOP hoặc tổng hợp, vì vậy tôi sẽ sử dụng một bảng tạm thời để lưu trữ kết quả và lặp lại bằng LOOP.

Tất nhiên, cái nào có nhiều chi phí hoạt động hơn, nhưng có vẻ như không có cách chung nào khác để hoàn thành việc tối ưu hóa như vậy trong SQL Server.

 create table #result (x int); declare @current int; select top (1) @current = a from group_by_table order by a; while @@rowcount > 0 begin insert into #result values (@current); select top (1) @current = a from group_by_table where a > @current order by a; end; select count(*) from #result;

Bây giờ chúng ta hãy so sánh cách các truy vấn đã sửa đổi này chạy so với truy vấn gốc và MySQL. Tôi sẽ tham khảo các truy vấn đã sửa đổi là A1, B1 và C1. Đây là bảng với kết quả đầy đủ.

Phần kết luận

Kết quả khá rõ ràng, Loose Scan là một tối ưu hóa tuyệt vời giúp giảm đáng kể số lượng hàng được đánh giá cho các truy vấn GROUP BY hoặc DISTINCT khi sử dụng chỉ mục.

Mặc dù PostgreSQL cho phép bạn viết các truy vấn đệ quy phức tạp để xử lý các cột có lực lượng thấp với cùng hiệu quả như MySQL, nhưng nó có một hình phạt hiệu suất đáng kể trên cột A, nơi có lực lượng cao.

MS SQL Server hoạt động tốt hơn các máy chủ khác trên cột A, nhưng rõ ràng hoạt động kém hơn trong mọi trường hợp khác, nhưng tất nhiên, một số cách giải quyết vẫn tốt hơn so với truy vấn ban đầu.

Tôi hy vọng PostgreSQL và MS SQL Server thỉnh thoảng triển khai tối ưu hóa Skip Scan trong các phiên bản tiếp theo.