一般我們數據量大的時候，然后就需要進行分頁，一般分頁語句就是limit offset，rows。這種分頁數據量小的時候是沒啥影響的，一旦數據量越來越大隨著offset的變大，性能就會越來越差。下面我們就來實驗下：

準備數據

建一個測試表引擎為MyISAM(插入數據沒有事務提交，插入速度快)的表。

CREATE TABLE USER (id INT ( 20 ) NOT NULL auto_increment,NAME VARCHAR ( 20 ) NOT NULL,address VARCHAR ( 20 ) NOT NULL,PRIMARY KEY ( id )) ENGINE = MyISAM;

寫一個批量插入的存儲過程

delimiter //# 刪除表數據TRUNCATE TABLE t;# 如果已經有sp_test_batch存儲過程，將其刪除，后面重新創建DROP PROCEDURE IF EXISTS sp_test_batch;# 創建存儲過程，包含num和batch輸入，num表示插入的總行數，batch表示每次插入的行數CREATE PROCEDURE sp_test_batch(IN num INT,IN batch INT)BEGINSET @insert_value = '';# 已經插入的記錄總行數SET @count = 0;#SET @batch_count = 0;WHILE @count < num DO# 內while循環用于拼接INSERT INTO t VALUES (),(),(),...語句中VALUES后面部分WHILE (@batch_count < batch AND @count < num) DOIF @batch_count>0THENSET @insert_value = concat(@insert_value,',');END IF;SET @insert_value = concat(@insert_value,"('name", @count, "','address", @count, "')");SET @batch_count = @batch_count+1;END WHILE;SET @count = @count + @batch_count;# 拼接SQL語句并執行SET @exesql = concat("insert into user(name,address) values ", @insert_value);PREPARE stmt FROM @exesql;EXECUTE stmt;DEALLOCATE PREPARE stmt;# 重置變量值SET @insert_value = '';SET @batch_count=0;END WHILE;# 數據插入完成后，查看表中總記錄數SELECT COUNT(id) FROM user;ENDCALL sp_test_batch(10000000,10000);

插入1000w數據

測試性能? ? 下面我們分別針對于offset等于不同的值來進行實：offset等于10000時耗時

offset等于100000時耗時

offset等于1000000時耗時

offset等于5000000時耗時

offset等于10000000時耗時

從上圖可以得出隨著offset的值越大耗時就越來越多。這還只是1000w數據，如果我們上億數據呢，可想而知這時候查詢的效率有多差。下面我們來進行優化。

4 .進行優化

子查詢的分頁方式：

SELECT * FROM user WHERE id >=(SELECT id FROM user ORDER BY id LIMIT 9000000, 1) LIMIT 10

從圖可以得出子查詢確實速度快了一倍。

JOIN分頁方式：

SELECT * FROM user t1 INNER join

(SELECT id FROM user ORDER BY id LIMIT 9000000, 10) t2 on t2.id =t1.id

join的方式比子查詢性能在稍微好點。

終極優化：

這個時間性能是最好的。這種優化必須要依賴前一次的查詢的最大ID，如果是那種分頁直接可以指定多少頁的是不行的，必須是只能后一頁，后一頁這么點擊。

SELECT id FROM user where id > 9000000 ORDER BY id LIMIT 10;

總結

以上是生活随笔為你收集整理的mysql大量数据分页优化_mysql大量数据分页优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。