金三银四面试季,真实面试经历打开Mysql大门

2021年3月5日14:12:43 发表评论 98 views

金三银四的季节又来了,小贱继续开始了他的面试之旅,这次面试的宇宙巨头下面某书的一个部门。

面试官:看你简历上有做过分页查询,你能说说你是怎么做的吗?
小贱:主要是用到了limit来实现分页。
面试官:能说说具体放limit原理吗?
小贱:emm…

在我们日常开发中,经常需要返回表中前几行数据或者中间某几行数据,那么这个时候我们就可以用limit来实现这些需求。那么limit是如何使用的呢?

limit的使用方法

常见的limit使用方式有以下两种方式:

1. SELECT * FROM TABLE LIMIT XXX; 

2. SELECT * FROM TABLE LIMIT XXX, XXX;

如上所示,limit后可以接受一个参数,也可以接受两个参数,那么他们的区别是什么呢?

limit的使用语法如下:

SELECT … FROM … LIMIT OFFSET N;
第一个参数OFFSET:表示偏移量,表示从返回记录集的第几条开始截取数据,需要注意的是 初始偏移量是0而不是1;
第二个参数N:表示要返回的记录行的最大数目,当N = -1 表示返回到最后记录集的最后一行

举例说明:

 //表示返回记录集的第3-5行 
1. SELECT * FROM TABLE LIMIT 2, 3; 

    //表示返回记录集的第1-3行
2. SELECT * FROM TABLE LIMIT 3;
    //等价于下面的写法
    SELECT * FROM TABLE LIMIT 0, 3; // 同样表示返回记录集的第1-3行

limit的原理

那limit的原理是怎么样的呢?接下来我们通过实验来进行分析,实验申明:众所周知,不说明mysql版本和存储的引擎的mysql实验都是耍流氓,所以本次实验的mysql版本是,存储引擎是innodb。

首先我们先建一张实验用表,我们就取名为 test_limit吧,建表语句如下:

CREATE TABLE `test_limit` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `a` INT NULL,
  `b` INT NULL,
  PRIMARY KEY (`id`)) ENGINE=InnoDB ;

表结构很简单,就一个自增主键,两个整型字段。如下图:

金三银四面试季,真实面试经历打开Mysql大门

接着咱们往里面插入10万条数据:

delimiter ;;
create procedure gen_data()
    begin
    declare i int;
    set i=1;
    while(i<=100000)do
        insert into test_limit values(i, i, i);
        set i=i+1;
    end while;
end;;
delimiter ;
call gen_data();

结果如下图所示:

金三银四面试季,真实面试经历打开Mysql大门

接下来我们来分析limit的原理,分别取OFFSET的值10,100,1000,最大返回数据N均为5,并通过explain来查看sql的执行计划,如下图所示:

1. explain select * from test_limit order by id limit 10, 5;

2. explain select * from test_limit order by id limit 100, 5;

3. explain select * from test_limit order by id limit 1000, 5;

金三银四面试季,真实面试经历打开Mysql大门

执行计划的rows表示sql扫描的行数,从图中可以看出,当N=10时,一共扫描了15行,返回11-15行,当N=100时,一共扫描了105行,返回101-105行,当N=1000时,一共扫描1005行,返回1001-1005行。

由上可以看出:limit语句会先扫描OFFSET+N行,然后再丢弃掉前OFFSET行,返回后N行数据。

limit使用时可能存在的性能问题

从上面分析可知,当偏移量很大时,那么扫描的行数也会随之增大,那么性能消耗也是很大的,这种场景在分页时比较常见。

对此,我们进行了简单的实验,实验结果如下:
金三银四面试季,真实面试经历打开Mysql大门

从图中可以看出当OFFSET越大,查询所需时间越长。

通过子查询优化,结果如下:

金三银四面试季,真实面试经历打开Mysql大门

从结果中可以看出,查询时间比不使用子查询减小,效果不那么明显,是因为数据量的原因,那么减小的原因是啥呢?同样,通过explain查询sql执行计划,结果如下所示:

金三银四面试季,真实面试经历打开Mysql大门

从执行计划中可以看出,在没有进行子查询优化之前,sql的执行计划类型时ALL,即全表扫描;在使用子查询优化以后,可以看到sql的执行计划类型为range和index,即使用了索引,通过索引提高了sql查询性能。

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: