MySQL 百万级数据的4种查询优化方式

一.limit越往后越慢的原因

当我们使用limit来对数据进行分页操作的时，会发现：查看前几页的时候，发现速度非常快，比如 limit 200,25，瞬间就出来了。但是越往后，速度就越慢，特别是百万条之后，卡到不行，那这个是什么原理呢。先看一下我们翻页翻到后面时，查询的sql是怎样的：

?

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

这种查询的慢，其实是因为limit后面的偏移量太大导致的。比如像上面的 limit 2000000,25 ，这个等同于数据库要扫描出 2000025条数据，然后再丢弃前面的 20000000条数据，返回剩下25条数据给用户，这种取法明显不合理。

二.百万数据模拟

1、创建员工表和部门表，编写存储过程插数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81

/*部门表,存在则进行删除 */ 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.执行存储过程

?

1 2 3 4

/*插入120条数据*/ call insert_dept(1,120); /*插入500W条数据*/ call insert_emp(0,5000000);

插入500万条数据可能很慢

三.4种查询方式

1.普通limit分页

?

1 2 3 4 5 6

/*偏移量为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;

执行结果

?

1 2 3 4 5 6 7 8 9 10

[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 时间: 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 时间: 12.275s

越往后，查询效率越慢

2.使用索引覆盖+子查询优化

因为我们有主键id，并且在上面建了索引，所以可以先在索引树中找到开始位置的 id值，再根据找到的id值查询行数据。

?

1 2 3 4 5 6 7 8 9 10 11

/*子查询获取偏移100条的位置的id，在这个位置上往后取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，在这个位置上往后取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;

执行结果

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

[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 时间: 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 时间: 1.541s

3.起始位置重定义

适用于主键是自增主键的表

?

1 2 3 4 5 6 7 8 9 10

/*记住了上次的分页的最后一条数据的id是100，这边就直接跳过100，从101开始扫描表*/ 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;   /*记住了上次的分页的最后一条数据的id是4800000，这边就直接跳过4800000，从4800001开始扫描表*/ 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;

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

[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 时间: 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 时间: 0.000s

这个效率是最好的，无论怎么分页，耗时基本都是一致的，因为他执行完条件之后，都只扫描了25条数据。

4,降级策略（百度的做法）

这个策略是最简单有效的，因为一般的大数据查询都会有搜索条件，没人会关注100页以后的内容，当用户查询页数过大时，给它返回一个错误就行了，例如百度就只能搜索到76页

以上就是MySQL 百万级数据的4种查询优化方式的详细内容，更多关于MySQL 百万级数据查询优化的资料请关注服务器之家其它相关文章！

原文链接：https://www.cnblogs.com/xiaodou00/p/14813759.html