MySQL 百萬級數據的4種查詢優化方式
當我們使用limit來對數據進行分頁操作的時，會發現：查看前幾頁的時候，發現速度非常快，比如 limit 200,25，瞬間就出來了。但是越往后，速度就越慢，特別是百萬條之后，卡到不行，那這個是什么原理呢。先看一下我們翻頁翻到后面時，查詢的sql是怎樣的：
select * from t_name where c_name1=’xxx’ order by c_name2 limit 2000000,25;
這種查詢的慢，其實是因為limit后面的偏移量太大導致的。比如像上面的 limit 2000000,25 ，這個等同于數據庫要掃描出 2000025條數據，然后再丟棄前面的 20000000條數據，返回剩下25條數據給用戶，這種取法明顯不合理。
/*部門表,存在則進行刪除 */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 '');/*員工表,存在則進行刪除*/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);/* 產生隨機字符串的函數*/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;/*產生隨機部門編號的函數*/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;/*建立存儲過程：往emp表中插入數據*/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 把autocommit設置成0，把默認提交關閉*/ 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;/*建立存儲過程：往dep表中插入數據*/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.執行存儲過程
/*插入120條數據*/call insert_dept(1,120);/*插入500W條數據*/call insert_emp(0,5000000);
插入500萬條數據可能很慢
三.4種查詢方式1.普通limit分頁/*偏移量為100，取25*/SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom 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.depnamefrom emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;
執行結果
[SQL]SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;受影響的行: 0時間: 0.001s[SQL]SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;受影響的行: 0時間: 12.275s
越往后，查詢效率越慢
2.使用索引覆蓋+子查詢優化因為我們有主鍵id，并且在上面建了索引，所以可以先在索引樹中找到開始位置的 id值，再根據找到的id值查詢行數據。
/*子查詢獲取偏移100條的位置的id，在這個位置上往后取25*/SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom emp a left join dep b on a.depno = b.depnowhere a.id >= (select id from emp order by id limit 100,1)order by a.id limit 25;/*子查詢獲取偏移4800000條的位置的id，在這個位置上往后取25*/SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom emp a left join dep b on a.depno = b.depnowhere a.id >= (select id from emp order by id limit 4800000,1)order by a.id limit 25;
執行結果
[SQL]SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom emp a left join dep b on a.depno = b.depnowhere a.id >= (select id from emp order by id limit 100,1)order by a.id limit 25;受影響的行: 0時間: 0.106s[SQL]SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom emp a left join dep b on a.depno = b.depnowhere a.id >= (select id from emp order by id limit 4800000,1)order by a.id limit 25;受影響的行: 0時間: 1.541s3.起始位置重定義
適用于主鍵是自增主鍵的表
/*記住了上次的分頁的最后一條數據的id是100，這邊就直接跳過100，從101開始掃描表*/SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom emp a left join dep b on a.depno = b.depnowhere a.id > 100 order by a.id limit 25;/*記住了上次的分頁的最后一條數據的id是4800000，這邊就直接跳過4800000，從4800001開始掃描表*/SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom emp a left join dep b on a.depno = b.depnowhere a.id > 4800000order by a.id limit 25;
[SQL]SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom emp a left join dep b on a.depno = b.depnowhere a.id > 100 order by a.id limit 25;受影響的行: 0時間: 0.001s[SQL]SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom emp a left join dep b on a.depno = b.depnowhere a.id > 4800000order by a.id limit 25;受影響的行: 0時間: 0.000s
這個效率是最好的，無論怎么分頁，耗時基本都是一致的，因為他執行完條件之后，都只掃描了25條數據。
4,降級策略（百度的做法）這個策略是最簡單有效的，因為一般的大數據查詢都會有搜索條件，沒人會關注100頁以后的內容，當用戶查詢頁數過大時，給它返回一個錯誤就行了，例如百度就只能搜索到76頁
以上就是MySQL 百萬級數據的4種查詢優化方式的詳細內容，更多關于MySQL 百萬級數據查詢優化的資料請關注好吧啦網其它相關文章！
相關文章：
網公網安備