Java性能调优SQL篇优化分页查询
作者 | 浩说编程
来源 | 公众号:浩说编程
[ 大厂技术资源 | 研发必备安装包 | 限时免费获取 ]
在日常的业务需求开发中,数据库分页通常被架构封装到方法中直接调用
但在大数据量的情况下,分页查询是否存在效率问题?
怎样分析SQL效率?
如何优化分页查询效率?
本篇就针对该部分做探讨。一、如何分析SQL性能
mysql提供了EXPLAIN命令来查看SQL语句的执行计划,其中包含了语句是否有应用索引以及遍历的数据量,举个例子:EXPLAIN SELECT * FROM `user_`;
执行计划
以下是执行计划中每个字段的含义:
1、id:SQL查询中的序列号,id列数字越大越先执行,如果数字一样大,那么就从上往下依次执行。
2、select_type:查询类型
SIMPLE
简单SELECT(不使用UNION或子查询)
PRIMARY
最外层的SELECT
UNION
UNION中第二个或之后的SELECT语句
DEPENDENT UNION
UNION中第二个或之后的SELECT语句取决于外面的查询
UNION RESULT
UNION的结果
SUBQUERY
子查询中的第一个SELECT
DEPENDENT SUBQUERY
子查询中的第一个SELECT, 取决于外面的查询
DERIVED
衍生表(FROM子句中的子查询)
MATERIALIZED
物化子查询
UNCACHEABLE SUBQUERY
结果集无法缓存的子查询,必须重新评估外部查询的每一行
UNCACHEABLE UNION
UNION中第二个或之后的SELECT,属于无法缓存的子查询
3、table:查询的表名
4、type:查询类型,表示从表中查询到行所执行的方式,查询方式是 SQL 优化中一个很重要的指标,结果值从好到差依次是:system > const > eq_ref > ref > range > index > ALL。
system
表中只有一行数据
const
通过索引一次就找到了,const用于比较primary key或者unique索引,因为只匹配一行数据
eq_ref
使用唯一索引扫描,常见于多表连接中使用主键和唯一索引作为关联条件。
ref
对于来自前面表的每一行,在此表的索引中可以匹配到多行,ref可用于使用"="或"<=>"操作符作比较的索引列。
fulltext
使用全文索引的时候是这个类型。
ref_or_null
跟ref类型类似,只是增加了null值的比较。实际用的不多。
index_merge
表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引
unique_subquery
用于where中的in形式子查询,子查询返回不重复值唯一值,可以完全替换子查询,效率更高。
index_subquery
该连接类型类似于unique_subquery。适用于非唯一索引,可以返回重复值。
range
索引范围查询,常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。
index
索引全表扫描,把索引从头到尾扫一遍。
all
全表扫描,性能最差。
5、partitions:分区表命中的分区情况
6、possible_keys:查询可能使用到的索引都会在这里列出来,查询到的索引不一定是真正被用到的
7、key:查询真正使用到的索引
8、key_len:查询用到的索引长度(字节数)。
9、ref:如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
10、rows:mysql估算的需要扫描的行数(不是精确值)。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.
11、filtered:这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数。这个字段不重要
12、extra:额外信息,包含以下几种
distinct
在select部分使用了distinc关键字Using
index
"覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错Using
filesort
当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.Using
temporary
使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于order by和分组查询group byUsing
where
表明使用了where过滤Using
join
buffer
表明使用了连接缓存,如在查询的时候会有多次join,则可能会产生临时表impossible
where
表示where子句的值总是false,不能用来获取任何元祖二、优化大数据量下的分页查询
在使用分页查询LIMIT M(起始位置),N(偏移量)的时候,虽然我们只需要N条数据,但数据库至少会扫描M+N条数据,如果M足够大,将会产生效率问题,比如这个例子:select * from `demo`.`order` order by order_no limit 10000, 20;
执行计划
执行效率
可以看到,mysql在帮我们获取id从10001~10020的二十条数据的时候,rows字段为10020,也就是说扫描了10020条数据,然后去掉前一万条数据进而获取了我们需要的最后二十条数据,效率问题就产生在数据的扫描量上。
那么我们是否可以优化一下,毕竟前一万条数据是我们并不需要的。优化建议
通过子查询的方式,先获取数据起始点id,然后根据该值获取需要的偏移量数据。
优化之后的语句:select * from `demo`.`order` where id> (select id from `demo`.`order` order by order_no limit 10000, 1) limit 20;
执行计划
执行效率
通过上面对执行计划的说明可以了解到,id为2的子查询优先执行,并且扫描了10001条数据,然后执行主语句,虽然扫描的数据量并未减少很多,但效率有所提升。
作者 | 浩说编程
来源 | 公众号:浩说编程
山东卓文智能型断路器的好处山东卓文智能型断路器的好处对于现代需求的提升以及人们对安全用电意识的增强,传统断路器功能上逐渐无法满足日常生活中对用电安全的需求,山东卓文智能型断路器在传统空开配电设备上进行升级,
远程控制电源开关无线远程控制开关山东卓文信息科技有限公司远程控制电源开关无线远程控制开关山东卓文信息科技有限公司山东卓文信息科技有限公司推出的远程控制电源开关(智能微型融合断路器)具有智能断路云控电技术,是一款可以进行远程控制分合闸的远
山东卓文信息科技有限公司配电物联网解决方案助力泛在电力建设山东卓文信息科技有限公司配电物联网解决方案助力泛在电力建设关键词泛在电力物联网智能电网智能电表智慧用电配电物联网的建设已经成为当下泛在电力物联网建设的主要支撑。在今年年初关于三型两
便携的移动路由器,让出行用网更灵活对于一个普通的人来说,外出的时候只要带上手机,就能轻松搞定一切问题,因为手机不仅有电,还可以上网。可手机也是有很多弊端,比如屏幕比较小,办公不是很方便。所以很多人外出的时候,会带上
轮胎加气居然要三十块钱轮胎加气居然要三十块钱,今天大早上的也是客户急急忙忙的要货,于是装了一车货在三轮车上,看着轮胎似乎有点瘪,没多少气,也没管很多,客户这边也是吹的急,然后送了过去,回来的时候心想着加
知产晨报魅族公开汽车无死角驱蚊专利,爱奇艺诉字节不正当竞争8月13日知识产权快讯1hr专利魅族公开汽车无死角驱蚊专利可判断蚊子品种性别位置珠海市魅族科技有限公司公开一种汽车驱蚊方法处理器及驱蚊系统专利。专利摘要显示,本发明汽车驱蚊方法包括
知产晨报华为公开自动驾驶测量积水深度专利理想汽车01hr专利华为公开自动驾驶测量积水深度专利近日,暴雨频繁。司机开车通过积水路段时,以前只能靠蒙,或者下车进行测量,而现在,华为申请的一项专利可能会解决这个问题。华为技术有限公司公
知产晨报小米汽车商标被驳回,字节跳动加入开源专利社区OIN9月03日知识产权快讯1hr专利OPPO虚拟SIM卡专利获授权,无需卡托或卡座即可实现SIM卡功能9月1日消息,OPPO广东移动通信有限公司虚拟SIM卡的实现方法装置系统及移动终端
别人的东西就是香华硕灵耀U10笔记本体验最近帮同事物色一个商务本,他需求主要是性能颜值轻便型。把购买商务本当做项目去做,第一步就是确定需求,缩小范围,询问预算和具体用途。预算7000,但是最高有10的活动空间(私房钱),
公共广播系统与专业音响系统可以通用吗?阜新声艺视听与民用(家庭)音响相比,公共广播(PUBLICADDRESS)也是一种专业音响。但是我们通常说的专业音响(PROFESSIONALAUDIO)是特指舞台(演出)类音响。而公共广播同
了解7种常见类型的麦克风阜新声艺视听麦克风对于音乐人来说是一件很个人的物品,在选择时往往要考虑多种因素。在品类繁多的麦克风里,也并没有某种麦克风是最好的之类的定理。每个类型的麦克风都有他们独特的性质和用途,产生的声音