mysql實作翻頁功能

1.limit分頁公式

1.1 limit分頁公式

1.limit分頁公式：curPage是目前第幾頁

上一頁：limit (curPage-1)*pageSize,pageSize

下一頁：limit (curPage+1)*pageSize,pageSize

2.sql

select * from student order by sid limit (curPage-1)*pageSize,pageSize
           

1.2 擷取總頁碼

1.總頁數公式：totalRecord是總記錄數；pageSize是一頁分多少條記錄

int totalPageNum = (totalRecord +pageSize - 1) / pageSize;

2.用途：前台UI分頁插件顯示分頁碼

3.詢總條數：totalRecord是總記錄數: SELECT COUNT(*) FROM student

2. 分頁方式

2.1 limit m,n分頁

limit 3,3的意思掃描滿足條件的3+3行，撇去前面的3行，傳回最後的3行，那麼問題來了，如果是limit 200000,200，需要掃描200200行，如果在一個高并發的應用裡，每次查詢需要掃描超過20W行，效率十分低下。

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='test')
  
# 遊标設定為字典類型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

def view_page():
    current_page_no = 0
    init_status = None
    while True:
        result = []
        status = input("請翻頁（1：上一頁，2：下一頁）:")
        if int(status)==1:
            if current_page_no>0:
                print(f"目前頁是第{current_page_no}頁, 您選擇了上一頁")
            else:
                page = current_page_no+1
                print(f"目前頁是第{page}頁, 您選擇了上一頁")
            if init_status==None:
                pass
            elif not init_status:
                current_page_no = current_page_no -1;
            else:
                pass
            if current_page_no - 1>0:
                current_page_no = current_page_no - 1
                cursor.execute("select * from student order by sid limit %s,10", (current_page_no*10))
                result = cursor.fetchall()
            else:
                current_page_no = 0
                cursor.execute("select * from student order by sid limit %s,10",(current_page_no))
                result = cursor.fetchall()
            init_status = True
        elif int(status)==2:
            if current_page_no>0:
                print(f"目前頁是第{current_page_no}頁, 您選擇了下一頁")
            else:
                page = current_page_no+1
                print(f"目前頁是第{page}頁, 您選擇了下一頁")
            
            if init_status==None:
                pass
            elif init_status:
                current_page_no = current_page_no + 1
            else:
                pass
            cursor.execute("select * from student order by sid limit %s,10", (current_page_no*10))
            result = cursor.fetchall()
            current_page_no = current_page_no + 1;
            init_status = False
        else:
            print("請輸入正确的代碼。")
        for info in result:
            print(info)
view_page()
           

2.2 limit m

//下一頁
select * from student where sid >10 order by sid asc limit 10
//上一頁
select * from student where sid <60 order by sid desc limit 10
           

這種方式不管翻多少頁隻需要掃描n條資料。

但是，雖然掃描的資料量少了，但是在某些需要跳轉到多少也得時候就無法實作，這時還是需要用到方法1，既然不能避免，那麼我們可以考慮盡量減小m的值，是以我們可以給這條語句加上一個條件限制。使得每次掃描不用從第一條開始。這樣就能盡量減少掃描的資料量。

2.3

例如：每頁10條資料，目前是第10頁，目前條目ID的最大值是109，最小值是100.(目前100-109)

跳到第9頁：

跳到第8頁：

跳到第6頁：

跳到第11頁：

3. 優化

3.1 使用子查詢優化

這種方式先定位偏移位置的 id，然後往後查詢，這種方式适用于 id 遞增的情況。

//0.0037
select * from student where sname="了解" limit 5000,1

//0.0025
select sid from student where sname="了解" limit 5000,1

//0.0021
SELECT
    *
FROM
    student
WHERE
    sname = "了解" AND sid >=(
    SELECT
        sid
    FROM
        student
    WHERE
        sname = "了解"
    LIMIT 5000,
    1
)
LIMIT 100;

//0.0030
select * from student where sname="了解" limit 5000,100
           

1>比較第1條語句和第2條語句：使用 select id 代替 select * 速度增加了3倍

2>比較第2條語句和第3條語句：速度相差幾十毫秒

3>比較第3條語句和第4條語句：得益于 select id 速度增加，第3條語句查詢速度增加了3倍

3.2 使用 id 限定優化

這種方式假設資料表的id是連續遞增的，則我們根據查詢的頁數和查詢的記錄數可以算出查詢的id的範圍，可以使用 id between and 來查詢：’

select * from student where sname="了解"
and sid between 1000000 and 1000100 limit 100;
           

這種查詢方式能夠極大地優化查詢速度，基本能夠在幾十毫秒之内完成。限制是隻能使用于明确知道id的情況，不過一般建立表的時候，都會添加基本的id字段，這為分頁查詢帶來很多便利。

還可以有另外一種寫法：

當然還可以使用 in 的方式來進行查詢，這種方式經常用在多表關聯的時候進行查詢，使用其他表查詢的id集合，來進行查詢：

select * from student where id in
(select student_id from class where class_id=1)
limit 100;
           

3.3 使用臨時表優化

這種方式已經不屬于查詢優化，這兒附帶提一下。

對于使用 id 限定優化中的問題，需要 id 是連續遞增的，但是在一些場景下，比如使用曆史表的時候，或者出現過資料缺失問題時，可以考慮使用臨時存儲的表來記錄分頁的id，使用分頁的id來進行 in 查詢。這樣能夠極大的提高傳統的分頁查詢速度，尤其是資料量上千萬的時候。

3.4 關于資料表的id說明

一般情況下，在資料庫中建立表的時候，強制為每一張表添加 id 遞增字段，這樣友善查詢。

如果像是訂單庫等資料量非常龐大，一般會進行分庫分表。這個時候不建議使用資料庫的 id 作為唯一辨別，而應該使用分布式的高并發唯一 id 生成器來生成，并在資料表中使用另外的字段來存儲這個唯一辨別。

使用先使用範圍查詢定位 id （或者索引），然後再使用索引進行定位資料，能夠提高好幾倍查詢速度。即先 select id，然後再 select *；

https://blog.csdn.net/bandaoyu/article/details/89844673