了解between和limit及count(*)

最新推荐文章于 2023-12-14 18:16:01 发布

戎·码一生

最新推荐文章于 2023-12-14 18:16:01 发布

阅读量2.1k

点赞数 3

分类专栏：数据库文章标签： between limit效率 count

本文链接：https://blog.csdn.net/gogokongyin/article/details/51871692

版权

数据库专栏收录该内容

11 篇文章 1 订阅

订阅专栏

1、了解between...and的执行流程？

1.1、between..and和in的区别

前言

设计表结构或者是创建索引的时候，我们的目标就是在满足当前需求的情况下，减少对数据库的io次数，避免大量数据的获取及排序，减少资源的消耗。

1、了解between...and的执行流程？

实例中数据库表结构如下：

CREATE TABLE `USER` (
  `id` int(11) NOT NULL,
  `age` int(11) ,
  `orderId` bigint NOT NULL,
  `email` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `orderId` (`orderId`)
) ENGINE=InnoDB

插入数据：

insert into USER (id, age, orderId) values (1,18,101),(2,19,102),(3,20,103),(4,21,104),(5,22,105);

查询ordeId在102和103之间的数据，查询sql：

select * from USER where orderId between 102 and 103;

执行过程：

1、在 普通orderId索引 上找到orderId=102的记录，获取id的值2；
2、再到 主键索引树上 查询 id=2 对应的行记录；
3、在普通orderId索引上找到orderId=103的记录，获取id的值3；
4、再到主键索引树上查询 id=3 对应的行记录；
5、再到普通orderId索引上找到orderId=104的记录，不满足条件，循环结束；

我们知道完整的行记录信息是存储在主键索引上的，普通索引只是存储了索引值和主键key的数据。1-3-5步骤是查询了普通索引树上的值，然后回表了2次主键索引才获取到结果。那么我们只要避免回表就可以提高查询的速度。

1.1、between..and和in的区别

思考：为什么建议使用between...and?

select * from USER where id in(1,2,3,4,5);   -- sql1
select * from USER where id between 1 and 5; -- sql2

原因：

第一个要搜索树5次，类似于id=1，id=2…分别单次的查询；
第二个搜索一次，因为是范围查询，顺序读取直到id不满足条件为止;

2、了解limit的优缺点？

limit分页标准语法：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

2.1、limit效率高？

分页查询；
避免全表扫描，提高查询效率。常说的Limit的执行效率高，是对于一种特定条件下来说的：即数据库的数量很大，但是只需要查询一部分数据的情况。

实例：每个用户的email是唯一的，如果用户使用email作为用户名登陆的话，就需要查询出email对应的一条记录。

select * from USER where email="itcrazy@sohu.com" ;        -- sql1
select * from USER where email="itcrazy@sohu.com" limit 1; -- sql2

上面的语句实现了查询email对应的一条用户信息，但是由于email这一列没有加索引：

语句1:会导致全表扫描，效率会很低。
语句2:加上LIMIT 1，只要找到了对应的一条记录，就不会继续向下扫描了，效率会大大提高。

所以使用建议： 查询明确只有一条数据的时候加上 - limit1

2.2、limit效率低？

 select * from score limit 100000,5;    -- 语句一

当offset特别大时，这条语句的执行效率会明显减低，而且效率是随着offset的增大而降低的。

原因：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，当offset特别大，然后单条数据也很大的时候，每次查询需要获取的数据就越多，自然就会很慢。数据库会完整扫描 offset 的10000行，然后继续扫描 5 行之后才把结果集返回。

优化方案： 利用索引id 最优-需要保证id时递增的；

Select * from score where id > 100000 limit 5; -- sql2

这样可以利用索引快速定位到10w行的位置，再取5条记录，避免了前10w行数据的获取，提高效率。

**3、一直以为count(*)的效率最低？**

结论先行：效率：count(字段) < count(id) < count(1) < count(*)

count()的语义： count()是一个聚合函数，对于返回结果集，一行行的判断，如果count函数不为null，累计值+1，否则不加，最后返回这个累加值。

count(字段)： 字段不为null的总个数
- 1、如果这个字段是定义not null的话，一行行的从记录里面读取这个字段，判断不能为null，按行累加；
- 2、如果这个字段是定义null的话，那么执行的时候又可能是null，还要把值取出来再判断一下，不是null才累加；
- 另外count(字段)，如果字段没有走索引的话，则会走主键索引，较耗时；
count(主键 id)：选择最小的索引树，取出id，返回给server层，server拿到id后，判断不可能为空，也不可能为空，就按行累加；
count(1) ：遍历整张表， 不取值，放一个数字”1”进去，判断不可能为空，按行累加；理解为返回一行，0个字段。
- 单看count(1) 和count(id)，你会发现count(1)比count(id)快，不涉及取值，解析，字段的拷贝等操作；
count(*)：并不会把全部的字段取出来，而是 专门做了优化，不取值。count(*)不为null，按行累加，效率最高；