前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >工作中遇到的99%SQL优化,这里都能给你解决方案(三)

工作中遇到的99%SQL优化,这里都能给你解决方案(三)

作者头像
程序员小强
发布2019-09-12 12:45:55
8720
发布2019-09-12 12:45:55
举报
文章被收录于专栏:小强的进阶之路

预计阅读时间:7分钟

前几篇文章介绍了mysql的底层数据结构和sql优化分析,这篇文章介绍按照主键和非主键的分页查询优化,以及in、exists的sql优化,使用哪种count查询效率更高。 MySQL如何选择合适的索引 工作中遇到的99%SQL优化,这里都能给你解决方案

代码语言:javascript
复制
-- 示例表CREATE TABLE `employees` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',  `age` int(20) NOT NULL DEFAULT '0' COMMENT '年龄',  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '入职时间',  PRIMARY KEY (`id`),  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE,  KEY `idx_age` (`age`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=136326 DEFAULT CHARSET=utf8 COMMENT='员工表'
--创建100000条记录drop procedure if EXISTS insert_emp;delimiter ;;create procedure insert_emp()BEGIN    declare i int;    set i=1;    while(i < 100000)DO        INSERT INTO employees(name,age,position) values(CONCAT('xiaoqiang',i),i,'coder');        SET i=i+1;    end WHILE;end;;delimiter ;call insert_emp();
根据自增且连续的主键排序的分页查询
代码语言:javascript
复制
select * from employees LIMIT 9999 ,5;

表示从表employees 中取出从10000行开始的5行记录。看似只查询5条记录,实际这条SQL是先读取10005条记录,然后抛弃前10000条记录,然后读到后面5条想要的数据。没有添加单独的order by,表示通过主键排序。

因此要查询一张大表比较靠后的数据,执行效率是非常低的。因为主键是自增且连续的,所以可以改写成按照主键查询从第10001开始的五行数据,如下:

代码语言:javascript
复制
select * from  employees WHERE id > 9999 limit 5;

可以看到两个sql的执行计划,显然改写后的sql走了索引,而且扫描的行数大大减少,执行效率会更高。但是,这条改写的sql在很多场景下并不实用,因为表中可能某些记录被删除后,主键空缺,导致结果不一致。

先删除一条记录,然后测试下原来sql和优化后的sql:

代码语言:javascript
复制
select * from employees LIMIT 9999 ,5;
代码语言:javascript
复制
 select * from employees where id> 9999 limit 5;

两条sql的结果不一样,因此,如果主键不连续,不能使用上面描述的方法。

另外由于原来sql是order by非主键字段,按照上面的方法改写sql的结果不一致。所以这种改写得满足以下两个条件:

  • 主键自增且连续
  • 结果是按照主键排序的
根据非主键字段排序的分页查询
代码语言:javascript
复制
select * from employees order by name limit 9000, 5;
代码语言:javascript
复制
 explain select * from employees order by name limit 9000, 5;

key字段对应的值为null,发现并没有使用name字段的索引。因为扫描整个索引并查找到没有索引的行,可能要便利多个索引树,其成本比扫描全表的成本更高,索引优化器放弃使用索引。

优化的关键是:让排序时返回的字段尽可能的少,所以可以让排序和分页操作先查出主键,然后根据主键查到对应的记录。改下如下:

代码语言:javascript
复制
select * from employees as e inner join(select id from employees order by name limit 9000,5) as ed on e.id=ed.id;

可以看到结果与原来的sql结果是一致的,执行时间减少了一般以上,再对比下执行计划:

原来的sql使用的是filesort排序,而优化后的sql使用的是索引排序。

in和exists优化

原则:小表驱动大表,即小表的数据集驱动大表的数据集 in:当B表的数据集小于A表的数据集时,in由于exists

代码语言:javascript
复制
select * from A where id in(select id from B)等价于 for(select id from B){     select * from A where A.id=B.id }

exists:当A表的数据集小于B表的数据集时,exitsts优于in

当著查询A的数据,放到子查询B中做条件验证,根据验证结果(true或false)来决定著查询的数据是否保留。

代码语言:javascript
复制
select * from A  exists(select 1 from B where A.id=B.id)
等价于for(select * from A){    select * from B where A.id=B.id}
count(*)查询优化
代码语言:javascript
复制
explain select count(1) from employees;explain select count(id) from employees;explain select count(name) from employees;explain select count(*) from employees;

四个sql的执行计划几乎一样的,count(name)使用的是联合索引, 主要区别根据某个字段做count操作不会统计字段为null的值的数据行。

除了count(name)的其他count操作,都是用的辅助索引而不是主键索引, 因为二级索引存储数据更少,检索性能更高。

End

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-09-11,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 MoziInnovations 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 根据自增且连续的主键排序的分页查询
  • 根据非主键字段排序的分页查询
  • in和exists优化
  • count(*)查询优化
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档