CFSDN nhấn mạnh vào giá trị tạo ra nguồn mở và chúng tôi cam kết xây dựng nền tảng chia sẻ tài nguyên để mọi nhân viên CNTT có thể tìm thấy thế giới tuyệt vời của bạn tại đây.

Bài đăng trên blog CFSDN này sẽ đưa nó vào hoạt động! Cùng thảo luận về cách giải quyết vấn đề phân trang sâu của MySQL được tác giả sưu tầm và biên soạn. Nếu bạn quan tâm đến bài viết này thì nhớ like nhé.

Lời nói đầu

Xin chào mọi người, mình là cậu bé sưu tập ốc sên. (Xin vui lòng đặt một ngôi sao ở trên cùng).

Khi thực hiện các yêu cầu phân trang hàng ngày, chúng tôi thường sử dụng giới hạn để đạt được nó. Tuy nhiên, khi độ lệch đặc biệt lớn, hiệu quả truy vấn sẽ trở nên thấp. Bài viết này sẽ được chia thành bốn giải pháp để thảo luận về cách tối ưu hóa vấn đề phân trang sâu của hàng triệu dữ liệu MySQL và đính kèm một trường hợp thực tế gần đây về việc tối ưu hóa SQL chậm trong sản xuất.

Tại sao giới hạn phân trang sâu trở nên chậm hơn?

Chúng ta hãy xem cấu trúc bảng dưới đây:

1. TẠO NÊN BÀN tài khoản ( 
2.   nhận dạng số nguyên(11) KHÔNG VÔ GIÁ TRỊ BÌNH LUẬN TĂNG TỰ ĐỘNG 'Id khóa chính', 
3.   tên varchar(255) MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN 'Tên tài khoản', 
4.   THĂNG BẰNG số nguyên(11) MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN 'Sự cân bằng', 
5.   tạo_thời_gian ngày giờ KHÔNG VÔ GIÁ TRỊ BÌNH LUẬN 'Thời gian sáng tạo', 
6.   cập nhật_thời gian ngày giờ KHÔNG VÔ GIÁ TRỊ TRÊN CẬP NHẬT DẤU THỜI GIAN HIỆN TẠI BÌNH LUẬN 'thời gian cập nhật', 
7.   SƠ ĐẲNG CHÌA KHÓA (nhận dạng), 
8.   CHÌA KHÓA idx_tên (tên), 
9.   CHÌA KHÓA idx_update_time (update_time) //chỉ mục 
10. ) ENGINE=InnoDB TỰ ĐỘNG TĂNG TỐC=1570068 MẶC ĐỊNH CHARSET=utf8 ROW_FORMAT=BÌNH LUẬN THÙ='Bảng tài khoản'; 

Giả sử rằng câu lệnh SQL thực thi cho phân trang sâu như sau:

1. lựa chọn nhận dạng,tên,THĂNG BẰNG từ tài khoản Ở đâu thời gian cập nhật> '2020-09-19' giới hạn 100000,10; 

Thời gian thực hiện của SQL này như sau:

Phải mất 0,742 giây để hoàn thành quá trình thực hiện. Tại sao phân trang sâu trở nên chậm hơn? Nếu thay đổi thành giới hạn 0,10 thì chỉ mất 0,006 giây.

Trước tiên chúng ta hãy xem quá trình thực thi của SQL này:

1. Thông qua cây chỉ mục phụ thông thường idx_update_time, lọc điều kiện update_time và tìm ID bản ghi đáp ứng điều kiện.
2. Thông qua ID, quay lại cây chỉ mục khóa chính, tìm hàng thỏa mãn bản ghi rồi lấy ra cột hiển thị (quay lại bảng)
3. Quét 100010 hàng đáp ứng điều kiện, sau đó vứt đi 100000 hàng đầu tiên và quay lại.

Luồng thực thi SQL.

Kế hoạch thực hiện như sau:

Có hai lý do khiến SQL trở nên chậm:

1. Câu lệnh giới hạn trước tiên sẽ quét offset+n hàng, sau đó loại bỏ hàng offset đầu tiên và trả về n hàng dữ liệu tiếp theo. Nói cách khác, nếu giới hạn là 100000,10 thì 100010 hàng sẽ được quét, trong khi nếu giới hạn là 0,10 thì chỉ quét 10 hàng.
2. giới hạn 100000,10 quét nhiều hàng hơn, điều này cũng có nghĩa là nhiều lần trả lại bảng hơn.

Tối ưu hóa thông qua truy vấn phụ

Vì SQL trên quay lại bảng 100010 lần nên thực tế chúng ta chỉ cần 10 mẩu dữ liệu, tức là chúng ta chỉ cần quay lại bảng 10 lần, thực ra là đủ. Vì vậy, chúng ta có thể tối ưu hóa bằng cách giảm số lần trả về bảng.

Xem lại cấu trúc cây B+.

Vì vậy, làm cách nào để giảm số lượng bảng trả về trước?

Trong InnoDB, các chỉ mục được chia thành các chỉ mục khóa chính (chỉ mục được nhóm) và các chỉ mục phụ.

• Chỉ mục khóa chính, các nút lá lưu trữ toàn bộ hàng dữ liệu
• Trong chỉ mục phụ, các nút lá lưu trữ giá trị của khóa chính.

Chuyển điều kiện sang cây chỉ mục khóa chính.

Nếu chúng ta chuyển các điều kiện truy vấn trở lại cây chỉ mục khóa chính, chúng ta có thể giảm số lần trả về bảng. Nếu bạn chuyển sang truy vấn cây chỉ mục khóa chính, các điều kiện truy vấn phải được thay đổi thành id khóa chính. Tôi nên làm gì với các điều kiện update_time của SQL trước đó?

Làm cách nào để trích xuất truy vấn con? Vì nút lá chỉ mục phụ có ID khóa chính nên chúng ta có thể kiểm tra trực tiếp ID khóa chính dựa trên update_time. Đồng thời, chúng ta cũng chuyển điều kiện giới hạn 100000 sang truy vấn con SQL hoàn chỉnh. như sau:

1. lựa chọn nhận dạng,tên,THĂNG BẰNG TỪ tài khoản Ở đâu định danh >= (lựa chọn sự giúp đỡ từ tài khoản a Ở đâu a.thời_gian_cập_nhật >= '2020-09-19' giới hạn 100000, 1) GIỚI HẠN 10; 

Hiệu ứng truy vấn giống nhau và thời gian thực hiện chỉ mất 0,038 giây.

Chúng ta hãy xem kế hoạch thực hiện.

Từ kế hoạch thực hiện, chúng tôi biết rằng bảng truy vấn con mà truy vấn sử dụng chỉ mục idx_update_time. Đầu tiên, ID khóa chính của chỉ mục được nhóm được lấy trên chỉ mục, loại bỏ nhu cầu quay lại bảng. Sau đó, truy vấn thứ hai có thể trực tiếp kiểm tra thêm 10 truy vấn nữa dựa trên ID của truy vấn đầu tiên.

Vì vậy, giải pháp này là có thể thực hiện được~.

INNER JOIN bị trì hoãn tham gia

Ý tưởng tối ưu hóa liên kết bị trì hoãn thực sự giống như ý tưởng tối ưu hóa truy vấn con: vừa chuyển các điều kiện sang cây chỉ mục khóa chính, sau đó giảm số lượng trả về bảng. Sự khác biệt là liên kết bị trì hoãn sử dụng phép nối bên trong thay vì truy vấn con.

SQL được tối ưu hóa như sau:

1. LỰA CHỌN  acct1.id, acct1.tên,tài khoản1.cân bằng TỪ tài khoản acct1 BÊN TRONG THAM GIA (LỰA CHỌN sự giúp đỡ TỪ tài khoản a Ở ĐÂU a.thời_gian_cập_nhật >= '2020-09-19' ĐẶT HÀNG QUA a.update_time GIỚI HẠN 100000, 10) BẰNG   acct2 TRÊN acct1.id= acct2.id; 

Hiệu ứng truy vấn cũng được tận dụng, chỉ mất 0,034 giây.

Kế hoạch thực hiện như sau:

Ý tưởng truy vấn trước tiên là truy vấn ID khóa chính đáp ứng các điều kiện thông qua cây chỉ mục phụ idx_update_time, sau đó kết nối nó với bảng gốc thông qua ID khóa chính. Bằng cách này, chỉ mục khóa chính sẽ được sử dụng trực tiếp sau này và sau đó. lợi nhuận của bảng cũng giảm.

phương pháp gắn thẻ

Lý do cơ bản cho vấn đề phân trang sâu giới hạn là: offset (offset) càng lớn thì mysql sẽ quét càng nhiều hàng và sau đó loại bỏ. Điều này dẫn đến giảm hiệu suất truy vấn.

Trên thực tế, chúng ta có thể sử dụng phương pháp ghi nhãn, tức là đánh dấu mục nào được truy vấn lần trước. Khi kiểm tra lại lần sau, chúng ta sẽ quét từ mục đó xuống. Nó giống như đọc một cuốn sách, bạn chỉ cần gấp nó lại hoặc đánh dấu vào nơi bạn nhìn thấy nó lần trước, bạn có thể mở nó ra.

Giả sử rằng bản ghi cuối cùng đạt 100000, SQL có thể được sửa đổi thành:

1. lựa chọn  nhận dạng,tên,THĂNG BẰNG TỪ tài khoản Ở đâu số nhận dạng > 100000 đặt hàng qua id giới hạn 10; 

Trong trường hợp này, dù có lật bao nhiêu trang sau thì hiệu suất vẫn tốt vì chỉ mục id được đánh. Nhưng phương pháp này có những hạn chế: nó yêu cầu một trường tương tự như tăng tự động liên tục.

Sử dụng giữa...và...

Trong nhiều trường hợp, truy vấn giới hạn có thể được chuyển đổi thành truy vấn có vị trí đã biết để MySQL có thể nhận được kết quả tương ứng thông qua việc quét phạm vi giữa...và.

Nếu bạn biết rằng giá trị biên là 100000 thì sau 100010, bạn có thể tối ưu hóa như sau:

1. lựa chọn  nhận dạng,tên,THĂNG BẰNG TỪ tài khoản Ở đâu nhận dạng giữa 100000 Và 100010 đặt hàng qua nhận dạng; 

Các trường hợp thực hành thực tế

Chúng ta hãy cùng nhau xem xét một trường hợp thực tế. Giả sử cấu trúc bảng như sau và có 2 triệu dữ liệu...

1. TẠO NÊN BÀN tài khoản ( 
2.  nhận dạng varchar(32) ĐỐI CHIẾU utf8_bin KHÔNG VÔ GIÁ TRỊ BÌNH LUẬN 'Khóa chính', 
3.  tài khoản_không varchar(64) ĐỐI CHIẾU utf8_bin KHÔNG VÔ GIÁ TRỊ MẶC ĐỊNH '' BÌNH LUẬN 'tài khoản' 
4.  số lượng số thập phân(20,2) MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN 'Số lượng' 
5.  kiểu varchar(10) ĐỐI CHIẾU utf8_bin MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN 'Loại A, B' 
6.  tạo_thời_gian ngày giờ MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN 'Thời gian sáng tạo', 
7.  cập nhật_thời gian ngày giờ MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN 'thời gian cập nhật', 
8.  SƠ ĐẲNG CHÌA KHÓA (nhận dạng), 
9.  CHÌA KHÓA `idx_account_no` (số_tài_khoản), 
10.  CHÌA KHÓA `idx_create_time` (thời gian tạo) 
11.  ) ĐỘNG CƠ=InnoDB MẶC ĐỊNH KÝ TỰ=utf8 ĐỐI CHIẾU=utf8_bin BÌNH LUẬN='Bảng tài khoản'  

Nhu cầu kinh doanh như sau: lấy dữ liệu tài khoản loại A mới nhất vào năm 2021 và báo cáo lên nền tảng dữ liệu lớn.

Thực hiện các ý tưởng chung

Khi có nhiều đối tác nhận được yêu cầu như vậy thì họ sẽ trực tiếp thực hiện như sau:

1. //Truy vấn tổng số lượng được báo cáo 
2. Số nguyên tổng = accountDAO.countAccount(); 
3.  
4. // Truy vấn SQL tương ứng với tổng số lượng được báo cáo 
5. <lựa chọn định danh ='đếmTàiKhoản' Loại kết quả="java.lang.Integer"> 
6.   tìm kiếm đếm(1)  
7.   từ tài khoản 
8.   Ở đâu tạo_thời_gian >='2021-01-01 00:00:00' 
9.   Và  loại ='MỘT' 
10. lựa chọn> 
11.  
12. //Tính số trang 
13. số nguyên pageNo = tổng số % pageSize == 0 ? tổng số / pageSize : (tổng số / pageSize + 1); 
14.  
15. //Truy vấn trang và báo cáo 
16. vì(số nguyên i = 0; i < số trang; i++){ 
17.  Danh sách danh sách = accountDAO.listAccountByPage(startRow,pageSize); 
18.  startRow = (pageNo-1)*kích thước trang; 
19.  //Báo cáo dữ liệu lớn 
20.  postBigData(danh sách); 
21. } 
22.   
23. // Truy vấn trang SQL (có thể có vấn đề phân trang sâu giới hạn, vì khối lượng dữ liệu của bảng tài khoản là hàng triệu) 
24. <lựa chọn định danh ='listAccountByPage' > 
25.   chọn *  
26.   từ tài khoản 
27.   Ở đâu tạo_thời_gian >='2021-01-01 00:00:00' 
28.   Và  loại ='MỘT' 
29.   giới hạn #{startRow},#{pageSize} 
30. lựa chọn> 

 Kế hoạch tối ưu hóa thực tế

Kế hoạch triển khai ở trên sẽ gặp phải vấn đề giới hạn phân trang sâu vì khối lượng dữ liệu của bảng tài khoản là hàng triệu. Vậy làm thế nào để tối ưu hóa nó?

Trên thực tế, bạn có thể sử dụng phương pháp ghi thẻ Một số đối tác có thể nhầm lẫn. Khóa chính ID không liên tục.

Tất nhiên, các id không liên tục. Chúng ta có thể làm cho chúng liên tục theo thứ tự. Kế hoạch tối ưu hóa như sau:

1. // Truy vấn ID tối thiểu 
2. Chuỗi lastId = accountDAO.queryMinId(); 
3.  
4. // Truy vấn SQL tương ứng với ID lớn nhất 
5. <lựa chọn định danh="queryMinId" returnType=“java.lang.String”> 
6. lựa chọn TỐI THIỂU(nhận dạng)  
7. từ tài khoản 
8. Ở đâu tạo_thời_gian >='2021-01-01 00:00:00' 
9. Và loại ='MỘT' 
10. lựa chọn> 
11.  
12. //Số mục trên một trang 
13. Số nguyên Kích thước trang = 100; 
14.  
15. Danh sách; 
16. LÀM{ 
17.    danh sách = danh sáchAccountByPage(lastId,pageSize); 
18.    //Phương thức ghi thẻ, ghi lại ID được truy vấn cuối cùng 
19.    lastId = list.get(list,kích cỡ()-1).getId(); 
20.     //Báo cáo dữ liệu lớn 
21.     postBigData(danh sách); 
22. }while(CollectionUtils.isNotEmpty(danh sách)); 
23.  
24. <lựa chọn định danh ="listAccountByPage"> 
25.   lựa chọn *  
26.   từ tài khoản  
27.   Ở đâu tạo_thời_gian >='2021-01-01 00:00:00' 
28.   Và id > #{lastId} 
29.   Và loại ='MỘT' 
30.   đặt hàng qua nhận dạng tăng dần   
31.   giới hạn #{pageSize} 
32. lựa chọn> 

Liên kết gốc: https://mp.weixin.qq.com/s/vj3dSl2mxxQeNl2KU2QNDA.

Bài cuối cùng là về thực chiến! Bài viết này nói về cách giải quyết vấn đề phân trang sâu MySQL nếu bạn muốn biết thêm về chiến đấu thực tế! Để nói về cách giải quyết vấn đề phân trang sâu MySQL, vui lòng tìm kiếm các bài viết CFSDN hoặc tiếp tục duyệt các bài viết liên quan. Tôi hy vọng bạn sẽ ủng hộ blog của tôi trong tương lai! .