欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL百萬級數(shù)據(jù)大分頁查詢優(yōu)化的實現(xiàn)

 更新時間：2022年01月12日 09:16:12   作者：Java后端何哥  
在數(shù)據(jù)庫開發(fā)過程中我們經(jīng)常會使用分頁，但是如果是百萬級數(shù)據(jù)呢，本文就詳細(xì)的介紹一下MySQL百萬級數(shù)據(jù)大分頁查詢優(yōu)化的實現(xiàn)，感興趣的可以了解一下

前言：在數(shù)據(jù)庫開發(fā)過程中我們經(jīng)常會使用分頁，核心技術(shù)是使用用limit start, count分頁語句進(jìn)行數(shù)據(jù)的讀取。 

一、MySQL分頁起點越大查詢速度越慢

直接用limit start, count分頁語句，表示從第start條記錄開始選擇count條記錄 ：

select * from product limit start, count

當(dāng)起始頁較小時，查詢沒有性能問題，我們分別看下從10， 1000， 10000， 100000開始分頁的執(zhí)行時間（每頁取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)看出隨著起始記錄的增加，時間也隨著增大， 這說明分頁語句limit跟起始頁碼是有很大關(guān)系的，那么我們把起始記錄改為100w看下:

select * from product limit 1000000, 20   0.682秒

我們驚訝的發(fā)現(xiàn)MySQL在數(shù)據(jù)量大的情況下分頁起點越大查詢速度越慢，300萬條起的查詢速度已經(jīng)需要1.368秒鐘。這是為什么呢?因為limit 3000000,10的語法實際上是mysql掃描到前3000020條數(shù)據(jù),之后丟棄前面的3000000行，這個步驟其實是浪費掉的。

select * from product limit 3000000, 20 1.368秒

從中我們也能總結(jié)出兩件事情：

  • limit語句的查詢時間與起始記錄的位置成正比
  • mysql的limit語句是很方便，但是對記錄很多的表并不適合直接使用。

二、 limit大分頁問題的性能優(yōu)化方法

（1）利用表的覆蓋索引來加速分頁查詢

MySQL的查詢完全命中索引的時候，稱為覆蓋索引，是非?？斓摹Ｒ驗椴樵冎恍枰谒饕线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，這樣性能就沒有問題，因為MySQL只掃描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)存表插入速度快的特點，先利用函數(shù)和存儲過程在內(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ù)的存儲過程

#創(chuàng)建插入內(nèi)存表數(shù)據(jù)存儲過程，入?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ù)插入普通表的存儲過程

此處利用對內(nèi)存表的循環(huán)插入和刪除來實現(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)存臨時表的大小，配置的值越大，能存進(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、運行存儲過程插入數(shù)據(jù)

#循環(huán)調(diào)用100次，每次插入1W條數(shù)據(jù)
add_vote_memory_to_vote(100,10000);

插入一百萬條數(shù)據(jù)，花了2分半鐘：

 我執(zhí)行了兩次，查詢vote_record表的行記錄總數(shù)為兩百萬條：

參考鏈接：

MySQL的limit使用及解決超大分頁問題

MySQL優(yōu)化之limit分頁

mysql 快速生成百萬條測試數(shù)據(jù)

mysql 如何快速生成百萬測試數(shù)據(jù)

到此這篇關(guān)于MySQL百萬級數(shù)據(jù)大分頁查詢優(yōu)化的實現(xiàn) 的文章就介紹到這了,更多相關(guān)MySQL 分頁查詢優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

相關(guān)文章

  • MySQL 參數(shù)相關(guān)概念及查詢更改方法

    MySQL 參數(shù)相關(guān)概念及查詢更改方法

    這篇文章主要介紹了MySQL 參數(shù)相關(guān)概念及查詢更改方法，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下
    2020-09-09
  • MySQL主從復(fù)制的原理及配置方法（比較詳細(xì)）

    MySQL主從復(fù)制的原理及配置方法（比較詳細(xì)）

    MySQL 的數(shù)據(jù)庫的高可用性的架構(gòu)大概有以下幾種：集群，讀寫分離，主備。而后面兩種都是通過復(fù)制來實現(xiàn)的。下面將簡單介紹復(fù)制的原理及配置，以及一些常見的問題
    2014-05-05
  • MySQL8.0之CTE(公用表表達(dá)式)的使用

    MySQL8.0之CTE(公用表表達(dá)式)的使用

    本文主要介紹了MySQL8.0之CTE(公用表表達(dá)式)的使用，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2023-07-07
  • Mysql 下中文亂碼的問題解決方法總結(jié)

    Mysql 下中文亂碼的問題解決方法總結(jié)

    這篇文章主要介紹了Mysql 下中文亂碼的問題解決方法總結(jié)的相關(guān)資料,這里提供了解決 Mysql 中文亂碼問題的辦法，需要的朋友可以參考下
    2016-11-11
  • 如何用SQL命令查看Mysql數(shù)據(jù)庫大小

    如何用SQL命令查看Mysql數(shù)據(jù)庫大小

    本篇文章是對用SQL命令查看Mysql數(shù)據(jù)庫大小的方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下
    2013-06-06
  • linux mysql忘記密碼的多種解決或Access denied for user ''root''@''localhost''

    linux mysql忘記密碼的多種解決或Access denied for user ''root''@''local

    linux mysql忘記密碼的多種解決方法。
    2009-07-07
  • 關(guān)于MySQL死鎖問題的深入分析

    關(guān)于MySQL死鎖問題的深入分析

    這篇文章主要給大家介紹了關(guān)于MySQL死鎖問題的深入分析，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧
    2019-11-11
  • MySql中的IFNULL、NULLIF和ISNULL用法詳解

    MySql中的IFNULL、NULLIF和ISNULL用法詳解

    本文主要介紹了MySql中的IFNULL、NULLIF和ISNULL用法詳解，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2023-03-03
  • 關(guān)于mysql的時區(qū)問題

    關(guān)于mysql的時區(qū)問題

    這篇文章主要介紹了關(guān)于mysql的時區(qū)問題,具有很好的參考價值,希望對大家有所幫助,以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家,
    2023-11-11
  • SQL實現(xiàn)LeetCode(178.分?jǐn)?shù)排行)

    SQL實現(xiàn)LeetCode(178.分?jǐn)?shù)排行)

    這篇文章主要介紹了SQL實現(xiàn)LeetCode(178.分?jǐn)?shù)排行),本篇文章通過簡要的案例,講解了該項技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下
    2021-08-08

最新評論