物理分頁為什么用limit

在講解limit之間，我們先說說分頁的事情。

分頁有邏輯分頁和物理分頁，就像刪除有邏輯刪除和物理刪除。邏輯刪除就是改變數(shù)據(jù)庫的狀態(tài)，物理刪除就是直接刪除數(shù)據(jù)庫的記錄，而邏輯刪除只是改變該數(shù)據(jù)庫的狀態(tài)。例如：

同理，邏輯分頁和物理分頁是有區(qū)別的

-- ----------------------------
-- Table structure for vote_record_memory
-- ----------------------------
DROP TABLE IF EXISTS `vote_record_memory`;
CREATE TABLE `vote_record_memory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(20) NOT NULL,
  `vote_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_id` (`user_id`) USING HASH
) ENGINE=MEMORY AUTO_INCREMENT=3000001 DEFAULT CHARSET=utf8;

向該表中插入300萬條數(shù)據(jù)后，再轉(zhuǎn)儲到桌面，查看轉(zhuǎn)儲后的SQL文件的屬性：

這是多么龐大的數(shù)據(jù)，占用的內(nèi)存多么可怕，為什么我們再選用數(shù)據(jù)庫。這也是我們使用云服務(wù)器時，設(shè)定mysql的存儲空間的大小。

我們一般不推薦使用邏輯分頁，而使用物理分頁。在使用物理分頁的時候，就要考慮到limit的用法。

解釋limit

limit X，Y ，跳過前X條數(shù)據(jù)，讀取Y條數(shù)據(jù)

• X表示第一個返回記錄行的偏移量，Y表示返回記錄行的最大數(shù)目
• 如果X為0的話，即 limit 0, Y，相當于limit Y、

通過業(yè)務(wù)分析limit

我有一張工資表，只顯示最新的前兩條記錄，同時進行員工姓名和工資提成備注查詢

SELECT
	cue.real_name empName,
	zs.push_money AS pushMoney,
	zs.push_money_note AS pushMoneyNote,
	zs.create_datetime AS createTime
FROM
	zq_salary zs  //主表
LEFT JOIN core_user_ext cue ON cue.id = zs.user_id   //從表 on之后是從表的條件
WHERE
	zs.is_deleted = 0
AND (
	cue.real_name LIKE '%李%'
	OR zs.push_money_note LIKE '%測%'
)
ORDER BY
	zs.create_datetime DESC
LIMIT 2;

就相當于
ORDER BY
	zs.create_datetime DESC
LIMIT 0,2;

limit的效率問題

我有一個需求，就是從vote_record_memory表中查出3600000到3800000的數(shù)據(jù)，此時在id上加個索引，索引的類型是Normal，索引的方法是BTREE，分別用兩種方法查詢

-- 方法1
SELECT * FROM vote_record_memory vrm  LIMIT 3600000,20000 ;

-- 方法2
SELECT * FROM vote_record_memory vrm WHERE vrm.id >= 3600000 LIMIT 20000 

你會發(fā)現(xiàn)，方法2的執(zhí)行效率遠比方法1的執(zhí)行效率高，幾乎是方法1的九分之一的時間。

為什么方法1的效率低，而方法二的效率高呢？

分析一

因為在方法1中，我們使用的單純的limit。limit隨著行偏移量的增大，當大到一定程度后，會出現(xiàn)效率下降。而方法2用上索引加where和limit，性能基本穩(wěn)定，受偏移量和行數(shù)的影響不大。

分析二

我們用explain來分析：

可見，limit語句的執(zhí)行效率未必很高，因為會進行全表掃描，這就是為什么方法1掃描的的行數(shù)是400萬行的原因。方法2的掃描行數(shù)是47945行，這也是為什么方法2執(zhí)行效率高的原因。我們盡量避免全表掃描查詢，尤其是數(shù)據(jù)非常龐大，這張表僅有400萬條數(shù)據(jù)，方法1和方法就有這么大差距，可想而知上千萬條的數(shù)據(jù)呢。

能用索引的盡量使用索引，type至少達到range級別，這不是我說的，這是阿里巴巴開發(fā)手冊的5.2.8中要求的

我不用索引查詢到的結(jié)果和返回的時間和方法1的時間差不多：

SELECT * FROM vote_record_memory vrm WHERE vrm.id >= 3600000 LIMIT

20000 受影響的行: 0 時間: 0.196s

這也就是我們?yōu)槭裁幢M量使用索引的原因。mysql索引方法一般有BTREE索引和HASH索引，hash索引的效率比BTREE索引的效率高，但我們經(jīng)常使用BTREE索引，而不是hash索引。因為最重要的一點就是：Hash索引僅僅能滿足"=",“IN"和”<=>"查詢，不能使用范圍查詢。

如果是范圍查詢，我們?yōu)槭裁从肂TREE索引的原因。BTREE索引就是二叉樹索引，學(xué)過數(shù)據(jù)結(jié)構(gòu)的應(yīng)該都清楚，這里就不贅述了。

limit物理分頁

我們都知道limit一般有兩個參數(shù)，X和Y，X表示跳過X個數(shù)據(jù)，讀取Y個數(shù)據(jù)，我們就此來查詢數(shù)據(jù)

-- 首頁
SELECT * from vote_record_memory LIMIT 0,20;

-- 第二頁
SELECT * from vote_record_memory LIMIT 20,20;

-- 第三頁
SELECT * from vote_record_memory LIMIT 40,20;

-- 第四頁
SELECT * from vote_record_memory LIMIT 60,20;

-- n頁
SELECT * from vote_record_memory LIMIT (n-1)*20,20;

因而，如果是用java的話，我們就可以寫一個方法，有兩個參數(shù)，一個是頁數(shù)，一個每頁顯示的行數(shù)

    /**
     * @description          簡單的模擬分頁雛形
     * @author               zby
     * @param currentPage    當前頁
     * @param lines          每頁顯示的多少條
     * @return               數(shù)據(jù)的集合
     */
    public List<Object> listObjects(int currentPage, int lines) {
        String sql = "SELECT * from vote_record_memory LIMIT " + (currentPage - 1) * lines + "," + lines;
        return null;
    }

參考https://www.cnblogs.com/tonghun/p/7122801.html

到此這篇關(guān)于mysql的limit用法及邏輯分頁和物理分頁的文章就介紹到這了,更多相關(guān)mysql limit邏輯分頁和物理分頁內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論