MySQL百萬級(jí)數(shù)據(jù)大分頁查詢優(yōu)化的實(shí)現(xiàn)
前言：在數(shù)據(jù)庫開發(fā)過程中我們經(jīng)常會(huì)使用分頁，核心技術(shù)是使用用limit start, count分頁語句進(jìn)行數(shù)據(jù)的讀取。
一、MySQL分頁起點(diǎn)越大查詢速度越慢
直接用limit start, count分頁語句，表示從第start條記錄開始選擇count條記錄 ：
select * from product limit start, count
當(dāng)起始頁較小時(shí)，查詢沒有性能問題，我們分別看下從10， 1000， 10000， 100000開始分頁的執(zhí)行時(shí)間（每頁取20條）。
select * from product limit 10, 20 0.002秒 select * from product limit 1000, 20 0.011秒 select * from product limit 10000, 20 0.027秒 select * from product limit 100000, 20 0.057秒
我們已經(jīng)看出隨著起始記錄的增加，時(shí)間也隨著增大， 這說明分頁語句limit跟起始頁碼是有很大關(guān)系的，那么我們把起始記錄改為100w看下:
select * from product limit 1000000, 20 0.682秒
我們驚訝的發(fā)現(xiàn)MySQL在數(shù)據(jù)量大的情況下分頁起點(diǎn)越大查詢速度越慢，300萬條起的查詢速度已經(jīng)需要1.368秒鐘。這是為什么呢?因?yàn)閘imit 3000000,10的語法實(shí)際上是mysql掃描到前3000020條數(shù)據(jù),之后丟棄前面的3000000行，這個(gè)步驟其實(shí)是浪費(fèi)掉的。
select * from product limit 3000000, 20 1.368秒
從中我們也能總結(jié)出兩件事情：
- limit語句的查詢時(shí)間與起始記錄的位置成正比
- mysql的limit語句是很方便，但是對(duì)記錄很多的表并不適合直接使用。
二、 limit大分頁問題的性能優(yōu)化方法
（1）利用表的覆蓋索引來加速分頁查詢
MySQL的查詢完全命中索引的時(shí)候，稱為覆蓋索引，是非?？斓?。因?yàn)椴樵冎恍枰谒饕线M(jìn)行查找,之后可以直接返回,而不用再回表拿數(shù)據(jù)。在我們的例子中，我們知道id字段是主鍵，自然就包含了默認(rèn)的主鍵索引?，F(xiàn)在讓我們看看利用覆蓋索引的查詢效果如何。
select id from product limit 1000000, 20 0.2秒
那么如果我們也要查詢所有列，如何優(yōu)化？
優(yōu)化的關(guān)鍵是要做到讓MySQL每次只掃描20條記錄，我們可以使用limit n，這樣性能就沒有問題，因?yàn)镸ySQL只掃描n行。我們可以先通過子查詢先獲取起始記錄的id,然后根據(jù)Id拿數(shù)據(jù)：
select * from vote_record where id>=(select id from vote_record limit 1000000,1) limit 20;
（2）用上次分頁的最大id優(yōu)化
先找到上次分頁的最大ID，然后利用id上的索引來查詢，類似于：
select * from user where id>1000000 limit 100
三、MySQL百萬數(shù)據(jù)快速生成
利用mysql內(nèi)存表插入速度快的特點(diǎn)，先利用函數(shù)和存儲(chǔ)過程在內(nèi)存表中生成數(shù)據(jù)，然后再從內(nèi)存表插入普通表中
3.1、創(chuàng)建內(nèi)存表及普通表
//內(nèi)存表 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`) ) ENGINE = MEMORY AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 //普通表 CREATE TABLE `vote_record` ( `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_user_id` (`user_id`) ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
3.2、創(chuàng)建函數(shù)
//創(chuàng)建函數(shù) CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1 BEGIN DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; DECLARE return_str varchar(255) DEFAULT '' ; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1)); SET i = i +1; END WHILE; RETURN return_str; END
3.3、創(chuàng)建插入內(nèi)存表數(shù)據(jù)的存儲(chǔ)過程
#創(chuàng)建插入內(nèi)存表數(shù)據(jù)存儲(chǔ)過程，入?yún)是多少就插入多少條數(shù)據(jù) CREATE PROCEDURE `add_vote_memory`(IN n int) BEGIN DECLARE i INT DEFAULT 1; WHILE (i <= n) DO INSERT into vote_record_memory (user_id,vote_id,group_id,create_time ) VALUEs (rand_string(20),FLOOR(RAND() * 1000),FLOOR(RAND() * 100) ,now() ); set i=i+1; END WHILE; END
3.4、創(chuàng)建內(nèi)存表數(shù)據(jù)插入普通表的存儲(chǔ)過程
此處利用對(duì)內(nèi)存表的循環(huán)插入和刪除來實(shí)現(xiàn)批量生成數(shù)據(jù)，這樣可以不需要更改mysql默認(rèn)的max_heap_table_size值也照樣可以生成百萬或者千萬的數(shù)據(jù)。
- max_heap_table_size默認(rèn)值是16M。
- max_heap_table_size的作用是配置用戶創(chuàng)建內(nèi)存臨時(shí)表的大小，配置的值越大，能存進(jìn)內(nèi)存表的數(shù)據(jù)就越多。
#循環(huán)從內(nèi)存表獲取數(shù)據(jù)插入普通表 #參數(shù)描述 n表示循環(huán)調(diào)用幾次；count表示每次插入內(nèi)存表和普通表的數(shù)據(jù)量 CREATE PROCEDURE `add_vote_memory_to_common`(IN n int, IN count int) BEGIN DECLARE i INT DEFAULT 1; WHILE (i <= n) DO CALL add_vote_memory(count); INSERT INTO vote_record SELECT * FROM vote_record_memory; delete from vote_record_memory; SET i = i + 1; END WHILE; END
3.5、運(yùn)行存儲(chǔ)過程插入數(shù)據(jù)
#循環(huán)調(diào)用100次，每次插入1W條數(shù)據(jù) add_vote_memory_to_vote(100,10000);
插入一百萬條數(shù)據(jù)，花了2分半鐘：
我執(zhí)行了兩次，查詢vote_record表的行記錄總數(shù)為兩百萬條：
參考鏈接：
到此這篇關(guān)于MySQL百萬級(jí)數(shù)據(jù)大分頁查詢優(yōu)化的實(shí)現(xiàn) 的文章就介紹到這了,更多相關(guān)MySQL 分頁查詢優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
在MySQL中,UPDATE?語句用于修改已存在的表中的記錄,下面通過示例介紹MySQL中update語法的使用記錄,感興趣的朋友一起看看吧2024-07-07
大家好，本篇文章主要講的是MYSQL數(shù)據(jù)庫中常用函數(shù)介紹，感興趣的同學(xué)趕快來看一看吧，對(duì)你有幫助的話記得收藏一下2022-01-01
隨著網(wǎng)站數(shù)據(jù)量與訪問量的增加，MySQL 查詢速度慢與性能差的問題就日漸明顯，這里為大家分享一下解決方法,需要的朋友可以參考下2019-09-09
mysql定時(shí)自動(dòng)備份數(shù)據(jù)庫的方法步驟
我們都知道數(shù)據(jù)是無價(jià)，如果不對(duì)數(shù)據(jù)進(jìn)行備份，相當(dāng)是讓數(shù)據(jù)在裸跑，本文就介紹一下如何給mysql定時(shí)自動(dòng)備份數(shù)據(jù)，感興趣的小伙伴們可以參考一下2021-07-07
使用MySQL Slow Log來解決MySQL CPU占用高的問題
在Linux VPS系統(tǒng)上有時(shí)候會(huì)發(fā)現(xiàn)MySQL占用CPU高，導(dǎo)致系統(tǒng)的負(fù)載比較高。這種情況很可能是某個(gè)SQL語句執(zhí)行的時(shí)間太長導(dǎo)致的。優(yōu)化一下這個(gè)SQL語句或者優(yōu)化一下這個(gè)SQL引用的某個(gè)表的索引一般能解決問題2013-03-03
如何解決mysql執(zhí)行導(dǎo)入sql文件速度太慢的問題
文章介紹了一種通過修改MySQL導(dǎo)出命令參數(shù)來優(yōu)化大SQL文件導(dǎo)入速度的方法,通過對(duì)比目標(biāo)庫和導(dǎo)出庫的參數(shù)值,并使用優(yōu)化后的參數(shù)進(jìn)行導(dǎo)出,再在目標(biāo)庫導(dǎo)入,顯著提高了導(dǎo)入速度2024-11-11
mysql通過生日計(jì)算年齡的實(shí)現(xiàn)方法
本文主要介紹了mysql通過生日計(jì)算年齡的實(shí)現(xiàn)方法,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-11-11
本文主要介紹了Mysql8和mysql5.7的區(qū)別，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-03-03
這篇文章主要介紹了mysql數(shù)據(jù)庫中的索引類型和原理，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-02-02
這篇文章主要為大家介紹了MySql數(shù)據(jù)類型的教程示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2021-10-10