Analysis of mysql paging data loss

Analysis of mysql paging data loss
1. Problematic code

CleverCode finds that data is lost by page in mysql. As shown in the following code, the system_user table contains 4 million rows of data. In this case, all the userids in this table need to be obtained. In this case, only 20 thousand entries can be retrieved by page at a time.

// System user table (4 million rows of data) $ systemUserTable = new systemUserTable (); // 20000 $ pageSize = 20000; for ($ curPage = 1 ;; $ curPage ++) {$ SQL = "select userid from system_user order by userid asc limit ". ($ curPage-1) * $ pageSize ). ",". $ pageSize; // get data $ rows = $ systemUserTable-> fetchAll ($ SQL); // if (empty ($ rows) {break ;} // traverse data foreach ($ rows as $ key => $ val) {$ userid = $ val ['userid']; echo $ userid. "\ r \ n ";}}

2 Problem Analysis 1) Suppose system_user only has 10 rows of data (u1, u2, u3, u4, u5, u6, u7, u8, u9, u10 ).
2) at this time, five rows are taken each time. Two times.
3) normally, the first page is taken (u1, u2, u3, u4, u5). Normally, the second page is taken (u6, u7, u8, u9, u10 ).
4) if you finish the first page, the second page will be taken from the previous time gap. U2 and u3 are deleted.
5) when the second page is retrieved. The data is changed to (u1, u4, u5, u6, u7, u8, u9, u10 ).
6) The data on the second page is changed to (u8, u9, u10 ).

7) u6 and u7 are lost.

3. solution where carries the maximum offset each time. The following code.

$ SystemUserTable = new systemUserTable (); // get 20000 $ pageSize = 20000 each time; // The maximum number per page $ pageMaxId = 0; while (1) {$ SQL = "select userid from system_user where userid >$ {pageMaxId} order by userid asc limit ". $ pageSize; // get data $ rows = $ fyHouse-> fetchAll ($ SQL); // if (empty ($ rows) {break ;} // traverse data foreach ($ rows as $ key => $ val) {$ userid = $ val ['userid']; // record the current maximum page number if ($ userid> $ pageMaxId) {$ pageMaxId = $ userid;} echo $ userid. "\ r \ n ";}}

Process Analysis:
1) system_user only has 10 rows of data (u1, u2, u3, u4, u5, u6, u7, u8, u9, u10 ).
2) for the first time, the maximum offset is 0, limit 5; then (u1, u2, u3, u4, u5 ). The maximum offset u5 of the record.
4) if you finish the first page, the second page will be taken from the previous time gap. U2 and u3 are deleted.
5) when the second page is retrieved. The data is changed to (u1, u4, u5, u6, u7, u8, u9, u10 ).
3) The second fetch, the maximum offset u5, limit 5; then the fetch (u6, u7, u8, u9, u10 ). The maximum offset u10 of a record.