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

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

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

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

一、MySQL分頁(yè)起點(diǎn)越大查詢速度越慢

直接用limit start, count分頁(yè)語(yǔ)句，表示從第start條記錄開(kāi)始選擇count條記錄 ：

select * from product limit start, count

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

select * from product limit 1000000, 20   0.682秒

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

select * from product limit 3000000, 20 1.368秒

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

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

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

（1）利用表的覆蓋索引來(lái)加速分頁(yè)查詢

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，這樣性能就沒(méi)有問(wèn)題，因?yàn)镸ySQL只掃描n行。我們可以先通過(guò)子查詢先獲取起始記錄的id,然后根據(jù)Id拿數(shù)據(jù)：

select * from vote_record where id>=(select id from vote_record limit 1000000,1) limit 20;

（2）用上次分頁(yè)的最大id優(yōu)化

先找到上次分頁(yè)的最大ID，然后利用id上的索引來(lái)查詢，類似于：

select * from user where id>1000000 limit 100

三、MySQL百萬(wàn)數(shù)據(jù)快速生成

利用mysql內(nèi)存表插入速度快的特點(diǎn)，先利用函數(shù)和存儲(chǔ)過(guò)程在內(nèi)存表中生成數(shù)據(jù)，然后再?gòu)膬?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ǔ)過(guò)程

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

此處利用對(duì)內(nèi)存表的循環(huán)插入和刪除來(lái)實(shí)現(xiàn)批量生成數(shù)據(jù)，這樣可以不需要更改mysql默認(rèn)的max_heap_table_size值也照樣可以生成百萬(wàn)或者千萬(wàn)的數(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ǔ)過(guò)程插入數(shù)據(jù)

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

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

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

參考鏈接：

MySQL的limit使用及解決超大分頁(yè)問(wèn)題

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

mysql 快速生成百萬(wàn)條測(cè)試數(shù)據(jù)

mysql 如何快速生成百萬(wàn)測(cè)試數(shù)據(jù)

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

相關(guān)文章

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

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

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

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

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

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

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

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

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

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

    本篇文章是對(duì)用SQL命令查看Mysql數(shù)據(jù)庫(kù)大小的方法進(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死鎖問(wèn)題的深入分析

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

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

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

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

    關(guān)于mysql的時(shí)區(qū)問(wèn)題

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

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

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

最新評(píng)論