MySQL 百萬(wàn)級(jí)數(shù)據(jù)的4種查詢優(yōu)化方式

更新時(shí)間：2021年06月07日 10:48:48 作者：小豆

本文講解了MySQL 百萬(wàn)級(jí)數(shù)據(jù)的4種查詢優(yōu)化方式，大家可以根據(jù)自身需求，選擇適合自己的優(yōu)化方式

一.limit越往后越慢的原因

當(dāng)我們使用limit來(lái)對(duì)數(shù)據(jù)進(jìn)行分頁(yè)操作的時(shí)，會(huì)發(fā)現(xiàn)：查看前幾頁(yè)的時(shí)候，發(fā)現(xiàn)速度非?？?，比如 limit 200,25，瞬間就出來(lái)了。但是越往后，速度就越慢，特別是百萬(wàn)條之后，卡到不行，那這個(gè)是什么原理呢。先看一下我們翻頁(yè)翻到后面時(shí)，查詢的sql是怎樣的：

select * from t_name where c_name1='xxx' order by c_name2 limit 2000000,25;

這種查詢的慢，其實(shí)是因?yàn)閘imit后面的偏移量太大導(dǎo)致的。比如像上面的 limit 2000000,25 ，這個(gè)等同于數(shù)據(jù)庫(kù)要掃描出 2000025條數(shù)據(jù)，然后再丟棄前面的 20000000條數(shù)據(jù)，返回剩下25條數(shù)據(jù)給用戶，這種取法明顯不合理。

二.百萬(wàn)數(shù)據(jù)模擬

1、創(chuàng)建員工表和部門表，編寫存儲(chǔ)過(guò)程插數(shù)據(jù)

/*部門表,存在則進(jìn)行刪除 */
drop table if EXISTS dep;
create table dep(
    id int unsigned primary key auto_increment,
    depno mediumint unsigned not null default 0,
    depname varchar(20) not null default "",
    memo varchar(200) not null default ""
);

/*員工表,存在則進(jìn)行刪除*/
drop table if EXISTS emp;
create table emp(
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0,
    empname varchar(20) not null default "",
    job varchar(9) not null default "",
    mgr mediumint unsigned not null default 0,
    hiredate datetime not null,
    sal decimal(7,2) not null,
    comn decimal(7,2) not null,
    depno mediumint unsigned not null default 0
);
/* 產(chǎn)生隨機(jī)字符串的函數(shù)*/
DELIMITER $
drop FUNCTION if EXISTS rand_string;
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    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()*52),1));
    SET i = i+1;
    END WHILE;
    RETURN return_str;
END $
DELIMITER;


/*產(chǎn)生隨機(jī)部門編號(hào)的函數(shù)*/
DELIMITER $
drop FUNCTION if EXISTS rand_num;
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(100+RAND()*10);
    RETURN i;
END $
DELIMITER;
/*建立存儲(chǔ)過(guò)程：往emp表中插入數(shù)據(jù)*/
DELIMITER $
drop PROCEDURE if EXISTS insert_emp;
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    /*set autocommit =0 把a(bǔ)utocommit設(shè)置成0，把默認(rèn)提交關(guān)閉*/
    SET autocommit = 0;
    REPEAT
    SET i = i + 1;
    INSERT INTO emp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),'SALEMAN',0001,now(),2000,400,rand_num());
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $
DELIMITER;

/*建立存儲(chǔ)過(guò)程：往dep表中插入數(shù)據(jù)*/
DELIMITER $
drop PROCEDURE if EXISTS insert_dept;
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
    SET i = i+1;
    INSERT  INTO dep( depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END $
DELIMITER;

2.執(zhí)行存儲(chǔ)過(guò)程

/*插入120條數(shù)據(jù)*/
call insert_dept(1,120);
/*插入500W條數(shù)據(jù)*/
call insert_emp(0,5000000);

插入500萬(wàn)條數(shù)據(jù)可能很慢

三.4種查詢方式

1.普通limit分頁(yè)

/*偏移量為100，取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;
/*偏移量為4800000，取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;

執(zhí)行結(jié)果

[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;
受影響的行: 0
時(shí)間: 0.001s
[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;
受影響的行: 0
時(shí)間: 12.275s

越往后，查詢效率越慢

2.使用索引覆蓋+子查詢優(yōu)化

因?yàn)槲覀冇兄麈Iid，并且在上面建了索引，所以可以先在索引樹中找到開(kāi)始位置的 id值，再根據(jù)找到的id值查詢行數(shù)據(jù)。

/*子查詢獲取偏移100條的位置的id，在這個(gè)位置上往后取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;

/*子查詢獲取偏移4800000條的位置的id，在這個(gè)位置上往后取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 4800000,1)
order by a.id limit 25;

執(zhí)行結(jié)果

[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;
受影響的行: 0
時(shí)間: 0.106s

[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 4800000,1)
order by a.id limit 25;
受影響的行: 0
時(shí)間: 1.541s

3.起始位置重定義

適用于主鍵是自增主鍵的表

/*記住了上次的分頁(yè)的最后一條數(shù)據(jù)的id是100，這邊就直接跳過(guò)100，從101開(kāi)始掃描表*/
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;

/*記住了上次的分頁(yè)的最后一條數(shù)據(jù)的id是4800000，這邊就直接跳過(guò)4800000，從4800001開(kāi)始掃描表*/
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;
受影響的行: 0
時(shí)間: 0.001s

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;
受影響的行: 0
時(shí)間: 0.000s

這個(gè)效率是最好的，無(wú)論怎么分頁(yè)，耗時(shí)基本都是一致的，因?yàn)樗麍?zhí)行完條件之后，都只掃描了25條數(shù)據(jù)。

4,降級(jí)策略（百度的做法）

這個(gè)策略是最簡(jiǎn)單有效的，因?yàn)橐话愕拇髷?shù)據(jù)查詢都會(huì)有搜索條件，沒(méi)人會(huì)關(guān)注100頁(yè)以后的內(nèi)容，當(dāng)用戶查詢頁(yè)數(shù)過(guò)大時(shí)，給它返回一個(gè)錯(cuò)誤就行了，例如百度就只能搜索到76頁(yè)

以上就是MySQL 百萬(wàn)級(jí)數(shù)據(jù)的4種查詢優(yōu)化方式的詳細(xì)內(nèi)容，更多關(guān)于MySQL 百萬(wàn)級(jí)數(shù)據(jù)查詢優(yōu)化的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: