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 viết trên blog CFSDN này giải thích cách tối ưu hóa phân trang cho hàng tỷ dữ liệu trong một bảng MySQL duy nhất? Được tác giả sưu tầm và biên soạn. Nếu các bạn quan tâm tới bài viết này thì nhớ like nhé.

Lời nói đầu

  。

Một số người cho rằng nếu một bảng có hơn 10 triệu dữ liệu thì nên chia thành cơ sở dữ liệu và bảng. Điều này là không hợp lý. Tuy nhiên, đối với các doanh nghiệp đổi mới, không thể ước tính được công suất lớn như vậy ngay từ đầu khi thiết kế hệ thống kinh doanh. Chi phí và thời gian xây dựng không đủ để hoàn thành công việc phát triển hệ thống. Tôi nghĩ rằng để thiết kế hệ thống kinh doanh đổi mới, trước tiên chúng ta phải đáp ứng nhu cầu, thứ hai là xem xét các giải pháp tạm thời trong trường hợp hoạt động kinh doanh phát triển vượt bậc và dành thời gian cho việc nâng cấp hệ thống.

Mọi người đều muốn công việc kinh doanh thành công và nó đã đến.

Tôi sẽ không đi sâu vào chi tiết về thời gian cụ thể. Tôi bắt đầu kinh doanh mới và thấy một bảng có không quá 1 triệu mục dữ liệu tích lũy và một chức năng truy vấn. Sau đó, khối lượng kinh doanh tiếp tục tăng, đĩa mysql bắt đầu cảnh báo và xảy ra cảnh báo hết thời gian truy vấn. Hơn nữa, khách hàng cần truy vấn và tải xuống dữ liệu của bảng kinh doanh theo thời gian thực. Thật là một vấn đề đau đầu. Đã quá muộn để tạm thời thay đổi giải pháp lưu trữ và KPI không thể bị trì hoãn.

Để giải quyết vấn đề trước mắt, trước tiên hãy mở rộng đĩa. Dừng đồng bộ hóa các phòng máy tính kép để giảm các cảnh báo không cần thiết.

Tuy nhiên, người ta ước tính rằng 1000G sẽ không thể mang nó được lâu. Sau khi thảo luận với các bạn cùng lớp kinh doanh, dữ liệu trong phạm vi T-7 được doanh nghiệp chấp nhận có thể được truy vấn và tải xuống trong thời gian thực. Theo tốc độ tăng trưởng này, số lượng hồ sơ sẽ vượt quá 100 triệu trong 7 ngày. Nhưng một đĩa dữ liệu 7 ngày chắc chắn là đủ nên dữ liệu lịch sử trước tiên phải được lưu trữ ngoại tuyến.

Việc này cũng đơn giản, chỉ vài dòng mã. Tất nhiên, điều này phụ thuộc vào cơ sở hạ tầng hoàn hảo.

Sau khi vấn đề dung lượng được giải quyết, chúng ta có thể tối ưu hóa truy vấn phân trang dữ liệu. Để minh họa vấn đề, dữ liệu kinh doanh nhạy cảm sẽ bị xóa và cấu trúc bảng dữ liệu như sau:

1. TẠO NÊN BÀN `t` ( 
2.   `mã số` bigint(20) chưa ký KHÔNG VÔ GIÁ TRỊ BÌNH LUẬN TĂNG TỰ ĐỘNG 'Khóa chính', 
3.   `một` char(32) MẶC ĐỊNH '' BÌNH LUẬN '', 
4.   `b` varchar(64) MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN '', 
5.   `c` bigint(20) chưa ký KHÔNG VÔ GIÁ TRỊ BÌNH LUẬN '', 
6.   `d` varchar(64) KHÔNG VÔ GIÁ TRỊ BÌNH LUẬN '', 
7.   `e` nhỏ xíu(4) MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN '', 
8.   `f` số nguyên(11) KHÔNG VÔ GIÁ TRỊ MẶC ĐỊNH '0' BÌNH LUẬN '', 
9.   `g` varchar(32) KHÔNG VÔ GIÁ TRỊ BÌNH LUẬN '', 
10.   `h` char(32) MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN '', 
11.   `tôi` varchar(64) MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN '', 
12.   `j` varchar(64) MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN '', 
13.   `k` ngày giờ MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN '', 
14.   `tôi` số nguyên(11) MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN '', 
15.   `tôi` dấu thời gian VÔ GIÁ TRỊ MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN '', 
16.   `n` dấu thời gian VÔ GIÁ TRỊ MẶC ĐỊNH VÔ GIÁ TRỊ BÌNH LUẬN '' 
17.   SƠ ĐẲNG CHÌA KHÓA (`id`), 
18.   ĐỘC NHẤT CHÌA KHÓA `Anh_b` (`b`), 
19.   CHÌA KHÓA `IDX_c` (`c`,) SỬ DỤNG BTREE 
20. ) 

Khi lượng dữ liệu ít, sẽ không có vấn đề gì nếu chúng ta sử dụng cách phân trang sau:

1. LỰA CHỌN số, a, b… TỪ t GIỚI HẠN n,m 

Ví dụ:

pagesize: Số mục được hiển thị trên mỗi trang.

pageno: số trang.

Lưu ý m=pagesize; n=(pageno-1)*pagesize.

Nguyên tắc hoạt động của giới hạn của MySQL là trước tiên đọc n bản ghi đầu tiên, sau đó loại bỏ n bản ghi đầu tiên và đọc m bản ghi mong muốn tiếp theo. Do đó, n càng lớn thì độ lệch càng lớn và hiệu suất càng kém.

Sửa đổi sql để giảm mức tiêu thụ io.

1. LỰA CHỌN số, a, b… TỪ t Ở đâu nhận dạng TRONG(LỰA CHỌN nhận dạng TỪ t GIỚI HẠN n,m) 

Trên thực tế, điều này sẽ không tránh khỏi việc quét n mục đầu tiên nhưng sẽ tiết kiệm được rất nhiều thời gian.

Trên đây là RT yêu cầu cho mỗi trang Có thể thấy khi số lượng trang tăng lên thì RT cũng tăng dần.

Qps giảm dần.

Vì vậy nếu có quá nhiều dữ liệu thì khả năng hết trang cuối cùng sẽ rất cao.

Sau khi tối ưu hóa

  。

Trước tiên hãy nhìn vào góc độ và xem hiệu suất sau khi tối ưu hóa. Hiệu suất của giao diện này đã được cải thiện đáng kể. Như thể hiện trong hình ảnh:

RT trung bình là khoảng 10ms. Do quá trình xử lý dữ liệu được thực hiện khi quay trở lại nên RT cuối cùng là khoảng 15ms.

Qps cũng rất ổn định và có thể cao hơn tùy theo cuộc gọi của khách hàng.

Ý tưởng tối ưu hóa

  。

Quét toàn bộ bảng chắc chắn là không thực tế, vì vậy tôi đã nghĩ đến LSM, Cây hợp nhất có cấu trúc nhật ký. Cấu trúc dữ liệu này được sử dụng trong chiến lược cấu trúc tệp của nhiều sản phẩm: HBase, Cassandra, LevelDB, SQLite, Kafka, v.v. Nó là một cấu trúc dữ liệu tổng hợp rất phức tạp, bao gồm WAL (Nhật ký ghi trước), danh sách bỏ qua (SkipList) và danh sách có thứ tự phân cấp (SSTable, Bảng chuỗi được sắp xếp).

Ở đây không cần triển khai cây LSM mà chỉ tham khảo ý tưởng chỉ mục thưa thớt của nó để định vị dữ liệu một cách chính xác. Thế thôi. Các bước thực hiện như sau:

1. Dựa trên nghiệp vụ phân tích, xây dựng chỉ số chung của trường a và b. Bởi vì a và b là điều kiện truy vấn của dữ liệu và 1/7 dữ liệu có thể được tách ra.

1. THAY ĐỔI bàn  THÊM VÀO MỤC LỤC chỉ số a_b('Một','b') 

2. Bởi vì dữ liệu trong bảng này được cập nhật thông qua chèn ... vào cập nhật khóa trùng lặp ... [Đây cũng là một điềm báo do bài viết phân tích bế tắc trực tuyến để lại] và id là khóa chính tự động tăng, Vì vậy, tất cả dữ liệu đều được sắp xếp theo thứ tự được nhập vào cơ sở dữ liệu và các sửa đổi sẽ được cập nhật khi xảy ra xung đột sau đó, do đó id khóa chính sẽ không thay đổi.

Thiết kế các chỉ mục thưa thớt trong redis.

1. Thiết kế chỉ mục thưa thớt trong redis. 
2. chìa khóa = a+b+trang 
3. value = id bắt đầu của trang này 
4. Ví dụ: lấy 2 mẩu dữ liệu trên mỗi trang làm ví dụ 
5. key1 = ab1 giá trị =0; 
6. key2 = ab1 giá trị =4; 
7. key3 = ab1 giá trị =8; 
8. ..... 
9. Vì vậy, trang đầu tiên: 
10. lựa chọn * từ t Ở đâu số nhận dạng>0 Và một ='Một' b='b'  giới hạn 2; 
11. Trang thứ hai: 
12. lựa chọn * từ t Ở đâu số nhận dạng>4 Và một ='Một' b='b'   giới hạn 2; 
13. Trang ba: 
14. lựa chọn * từ t Ở đâu số nhận dạng>8 Và một ='Một' b='b'  giới hạn 2; 
15. .... 

Bằng cách này, id bắt đầu của mỗi trang có thể được xác định nhanh chóng mà không cần thực hiện nhiều thao tác quét, đồng thời, một chỉ mục được sử dụng. Mặc dù chỉ mục chung ab không thể phân biệt rõ ràng khi có các giá trị ab. Tương tự, điều này cũng đảm bảo việc theo thứ tự id, không cần phải sắp xếp theo thứ tự. Bởi vì các id của chỉ mục khóa chính vốn đã được sắp xếp theo thứ tự.

Thời gian tính toán chỉ số thưa thớt:

  。

Việc tính toán chỉ số thưa thớt bắt đầu sau khi một loạt dữ liệu được lưu trữ trong cơ sở dữ liệu.

Phương pháp tính toán:

Trang đầu tiên: id = 0.

1. Trang đầu tiên của dữ liệu 
2. lựa chọn * từ t Ở đâu số nhận dạng>0 Và một ='Một' b='b'  giới hạn 2; 

Trang thứ hai: Phương pháp tính ID,

1. lựa chọn tối đa(ngày giờ hiện tại) từ (lựa chọn * từ t Ở đâu số nhận dạng>0 Và một ='Một' b='b'  giới hạn 2) t;  

Trang 3: Phương pháp tính ID,

1. lựa chọn tối đa(ngày giờ hiện tại) từ (lựa chọn * từ t Ở đâu id>[Id trang thứ hai] Và một ='Một' b='b'  giới hạn 2) t;  

.......... 。

Và vân vân...

Sau đó viết vào redis và cập nhật theo cách tương tự.

Tại sao không sử dụng chỉ số bao phủ?

  。

Một số người chắc chắn sẽ nói tại sao không ghi đè lên chỉ mục, để không cần phải trả lại bảng.

Câu trả lời là không,

Nếu các trường chúng tôi trả về là a, b, cd, e, f thì chúng tôi xây dựng chỉ mục bao gồm x. Cây B+ của x có dạng sau:

Sau đó, nếu tại thời điểm này tôi thay đổi giá trị của id=5 từ a=4 thành a =1.

Vậy thì bây giờ các id không tuần tự!!!!!.

Còn việc sử dụng chỉ mục + thứ tự theo id thì sao?

  。

Sẽ không sao nếu lượng dữ liệu không lớn, nhưng tại sao phải bận tâm. Chúng ta hãy xem xét nguyên tắc sắp xếp theo thứ tự.

Đầu tiên, MySQL sẽ phân bổ một phần bộ nhớ cho mỗi luồng truy vấn, được gọi là Sort_buffer. Bộ nhớ này được sử dụng để sắp xếp. Bộ nhớ này lớn đến mức nào? Nó được điều khiển bởi tham số **sort_buffer_size** và có thể được xem thông qua lệnh sau.

1. # Kiểm tra kích thước của Sort_buffer 
2. hiển thị các biến giống 'kích thước bộ đệm sắp xếp'; 

Có hai vấn đề với điều này:

Mỗi lần nó được sắp xếp đầy đủ theo các điều kiện lọc.

Nếu lượng dữ liệu quá lớn và không đủ bộ nhớ, quá trình sắp xếp tệp sẽ được kích hoạt, quá trình này tương đối chậm.

Thế là tôi vẫn thực hiện kế hoạch vừa rồi. Hiệu ứng không tệ và chỉ thêm một vài dòng mã.

Kế hoạch tạm thời này đã diễn ra suôn sẻ trong hơn một năm. (>‿◠) .

Link gốc: https://www.toutiao.com/i6974629421202424353/.

Cuối cùng, bài viết này nói về cách tối ưu hóa việc phân trang hàng tỷ dữ liệu trong một bảng MySQL? Đó là tất cả cho bài viết này. Nếu bạn muốn biết thêm về cách tối ưu hóa phân trang cho hàng tỷ dữ liệu trong một bảng MySQL? Về nội dung, vui lòng tìm kiếm các bài viết của 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! .