首頁 > 資料庫 > mysql教程 > mysql大表分頁查詢翻頁最佳化方案

mysql大表分頁查詢翻頁最佳化方案

步履不停
發布: 2019-06-25 16:05:54
原創
4975 人瀏覽過

mysql大表分頁查詢翻頁最佳化方案

mysql分頁查詢是先查詢出來所有數據,然後跳過offset,取limit條記錄,造成了越往後的頁數,查詢時間越長

一般最佳化想法是轉換offset,讓offset盡可能的小,最好能每次查詢都是第一頁,也就是offset為0

 

查詢按id排序的情況

一、如果查詢是根據id排序的,並且id是連續的

這種網路上介紹比較多,根據要查的頁數直接算出來id的範圍

例如offset=40, limit=10, 表示查詢第5頁數據,那麼第5頁開始的id是41,增加查詢條件:id>40  limit 10

 

二、如果查詢是根據id排序的,但是id不是連續的

#通常翻頁頁數跳轉都不會很大,那我們可以根據上一次查詢的記錄,算出來下一次分頁查詢對應的新的offset和limit,也就是離上一次查詢記錄的offset

分頁查詢一般會有兩個參數:offset和limit,limit一般是固定,假設limit=10

 那為了優化offset太大的情況,每次查詢需要提供兩個額外的參數

參數lastEndId: 上一次查詢的最後一筆記錄的id

參數lastEndOffset: 上一次查詢的最後一筆記錄對應的offset,也就是上一次查詢的offset limit

  1. 第一種情況(與第二種其實是一樣):跳到下一頁,增加查詢條件:id>lastEndId limit 10
  2. 第二種情況:往下翻頁,跳到下任頁,算出新的newOffset=offset-lastEndOffset,增加查詢條件:id>lastEndId offset newOffset limit 10,但是如果newOffset也還是很大,比如,直接從第一頁跳轉到最後一頁,這時候我們可以根據id逆序(如果原來id是正序的換成倒序,如果是倒序就換成正序)查詢,根據總數量算出逆序查詢對應的offset和limit,那麼newOffset = totalCount - offset - limit, 查詢條件:id=totalCount ,也就是算出來的newOffset 可能小於0, 所以最後一頁的newOffset=0,limit = totalCount - offset
  3. 第三種情況:往上翻頁,跳轉到上任何頁,依id逆序,newOffset = lastEndOffset- offset - limit-1, 查詢條件:id

## 

三,

如果查詢是根據其他字段,例如一般使用的創建時間(createTime)排序

這種跟第二種情況差不多,差別是createTime不是唯一的,所以不能確定上一次最後一筆記錄對應的建立時間,哪些是下一頁的,哪些是上一頁的

這時候,增加一個請求參數lastEndCount:表示上一次查詢最後一筆記錄對應的建立時間,有多少筆是這同一時間的,這個根據上一次的資料統計

###根據第二種情況計算出來的newOffset加上# ##lastEndCount,是新的offset,其他的處理方式和第二個一致###### ######java 範例:###### ###

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

/**

     * 如果是根据创建时间排序的分页,根据上一条记录的创建时间优化分布查询

     *

     * @see 将会自动添加createTime排序

     * @param lastEndCreateTime

     *            上一次查询的最后一条记录的创建时间

     * @param lastEndCount 上一次查询的时间为lastEndCreateTime的数量

     * @param lastEndOffset  上一次查询的最后一条记录对应的偏移量     offset+limit

     **/

    public Page<T> page(QueryBuilder queryBuilder, Date lastEndCreateTime, Integer lastEndCount, Integer lastEndOffset,

            int offset, int limit) {

        FromBuilder fromBuilder = queryBuilder.from(getModelClass());

        Page<T> page = new Page<>();

        int count = dao.count(fromBuilder);

        page.setTotal(count);

        if (count == 0) {

            return page;

        }

        if (offset == 0 || lastEndCreateTime == null || lastEndCount == null || lastEndOffset == null) {

            List<T> list = dao.find(

                    SelectBuilder.selectFrom(fromBuilder.offsetLimit(offset, limit).order().desc("createTime").end()));

            page.setData(list);

            return page;

        }

        boolean isForward = offset >= lastEndOffset;

        if (isForward) {

            int calcOffset = offset - lastEndOffset + lastEndCount;

            int calcOffsetFormEnd = count - offset - limit;

            if (calcOffsetFormEnd <= calcOffset) {

                isForward = false;

                if (calcOffsetFormEnd > 0) {

                    fromBuilder.order().asc("createTime").end().offsetLimit(calcOffsetFormEnd, limit);

                else {

                    fromBuilder.order().asc("createTime").end().offsetLimit(0, calcOffsetFormEnd + limit);

                }

            else {

                fromBuilder.where().andLe("createTime", lastEndCreateTime).end().order().desc("createTime").end()

                        .offsetLimit(calcOffset, limit);

            }

        else {

            fromBuilder.where().andGe("createTime", lastEndCreateTime).end().order().asc("createTime").end()

                    .offsetLimit(lastEndOffset - offset - limit - 1 + lastEndCount, limit);

        }

        List<T> list = dao.find(SelectBuilder.selectFrom(fromBuilder));

        if (!isForward) {

            list.sort(new Comparator<T>() {

                @Override

                public int compare(T o1, T o2) {

                    return o1.getCreateTime().before(o2.getCreateTime()) ? 1 : -1;

                }

            });

        }

        page.setData(list);

        return page;

    }### #### ##前端js參數,基於bootstrap table###<pre class="brush:php;toolbar:false">    this.lastEndCreateTime = null;

    this.currentEndCreateTime = null;

     

    this.isRefresh = false;        

      this.currentEndOffset = 0;

        this.lastEndOffset = 0;

        this.lastEndCount = 0;

        this.currentEndCount = 0;

        $("#" + this.tableId).bootstrapTable({

            url: url,

            method: 'get',

            contentType: "application/x-www-form-urlencoded",//请求数据内容格式 默认是 application/json 自己根据格式自行服务端处理

            dataType:"json",

            dataField:"data",

            pagination: true,

            sidePagination: "server"// 服务端请求

            pageList: [10, 25, 50, 100, 200],

            search: true,

            showRefresh: true,

            toolbar: "#" + tableId + "Toolbar",

            iconSize: "outline",

            icons: {

                refresh: "icon fa-refresh",

            },

            queryParams: function(params){

                if(params.offset == 0){

                    this.currentEndOffset = params.offset + params.limit;

                }else{

                    if(params.offset + params.limit==this.currentEndOffset){ 

                        //刷新

                        this.isRefresh = true;

                        params.lastEndCreateTime = this.lastEndCreateTime;

                        params.lastEndOffset = this.lastEndOffset;

                        params.lastEndCount = this.lastEndCount;

                    }else

                        console.log(this.currentEndCount);

                        //跳页

                        this.isRefresh = false;

                        params.lastEndCreateTime = this.currentEndCreateTime;

                        params.lastEndOffset = this.currentEndOffset;

                        params.lastEndCount = this.currentEndCount;

                        this.lastEndOffset = this.currentEndOffset;

                        this.currentEndOffset = params.offset + params.limit;

                        console.log(params.lastEndOffset+","+params.lastEndCreateTime);

                         

                    }

                }

                return params;

            },

            onSearch: function (text) {

                this.keyword = text;

            },

            onPostBody : onPostBody,

            onLoadSuccess: function (resp) {

                 

                if(resp.code!=0){

                    alertUtils.error(resp.msg);

                }

                

                var data = resp.data;

                var dateLength = data.length;

                if(dateLength==0){

                    return;

                }

                if(!this.isRefresh){

                     this.lastEndCreateTime =  this.currentEndCreateTime;

                     this.currentEndCreateTime = data[data.length-1].createTime;

                     this.lastEndCount = this.currentEndCount;

                     this.currentEndCount = 0;

                     for (var i = 0; i < resp.data.length; i++) {

                        var item = resp.data[i];

                        if(item.createTime === this.currentEndCreateTime){

                            this.currentEndCount++;

                        }

                    }

                }

                 

            }

        });

登入後複製
###更多MySQL相關技術文章,請造訪###MySQL教學###欄位進行學習! ###

以上是mysql大表分頁查詢翻頁最佳化方案的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板