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

吾爱主题阅读：131 2024-04-02 08:03:06 评论：0

一.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