网站建设_制作_设计,郑州管城建设网站,自己在家怎么做跨境电商,推广普通话内容大家好#xff0c;我是飘渺#xff01;我在面试的时候很喜欢问候选人这样一个问题#xff1a;“你在项目中遇到过慢查询问题吗#xff1f;你是怎么做SQL优化的#xff1f;”很多时候#xff0c;候选人会直接跟我说他们在编写SQL时会遵循的一些常用技巧#xff0c;比如我是飘渺我在面试的时候很喜欢问候选人这样一个问题“你在项目中遇到过慢查询问题吗你是怎么做SQL优化的”很多时候候选人会直接跟我说他们在编写SQL时会遵循的一些常用技巧比如合理使用索引使用UNION ALL替代UNION不要使用select * 写法JOIN字段建议建立索引避免复杂SQL语句这里不能说完全错误因为这些技巧确实可以提高SQL运行效率但是也不能说完全正确毕竟我是想问他具体怎么是做SQL优化的。接下来我问他我这里有一段复杂的SQL你可以动手帮我优化一下吗到这一步的时候就有很多候选人做不好打了退堂鼓。他们有很扎实的理论知识但是动手能力却差点火候。今天这篇文章就从实战的角度出发带大家走一遍SQL优化的真实流程。找出有问题的SQL在实际开发中要判断一段SQL有没有问题可以从两方面来判断1、系统层面CPU消耗严重IO等待严重页面响应时间过长应用的日志出现超时等错误2、SQL语句层面冗长执行时间过长从全表扫描获取数据执行计划中的rows、cost很大冗长的SQL都好理解一段SQL太长阅读性肯定会差出现问题的频率肯定会更高。更进一步判断SQL问题就必须得从执行计划入手如下所示执行计划告诉我们本次查询走了全表扫描TypeALLrows很大(9950400)基本可以判断这是一段有味道的SQL。查看SQL执行计划找到了有问题的SQL就要确定优化方案那究竟从何处下手呢这里必须要通过执行计划来观察。执行计划会告诉你哪些地方效率低哪里可以需要优化。我们以MYSQL为例看看执行计划是什么。每个数据库的执行计划都不一样需要自行了解explain select * from xxx当使用explain sql后会看到执行计划执行计划中几个重要字段的解释说明大家需要记住字段解释id每个被独立执行的操作标识标识对象被操作的顺序id值越大先被执行如果相同执行顺序从上到下select_type查询中每个select 字句的类型table被操作的对象名称通常是表名但有其他格式partitions匹配的分区信息(对于非分区表值为NULL)type连接操作的类型possible_keys可能用到的索引key优化器实际使用的索引(最重要的列) 从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL。当出现ALL时表示当前SQL出现了“坏味道”key_len被优化器选定的索引键长度单位是字节ref表示本行被操作对象的参照对象无参照对象为NULLrows查询执行所扫描的元组个数对于innodb此值为估计值filtered条件表上数据被过滤的元组个数百分比extra执行计划的重要补充信息当此列出现Using filesort , Using temporary 字样时就要小心了很可能SQL语句需要优化通过执行计划我们就可以确定优化方案优化一处后再回过头来观察执行计划如此往复循环直到找到最优目标为止。下面给出一段有问题的SQL具体操作一下。SQL优化案例慢查询1、表结构如下CREATE TABLE a
(id int(11) NOT NULLAUTO_INCREMENT,seller_id bigint(20) DEFAULT NULL,seller_name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,gmt_create varchar(30) DEFAULT NULL,PRIMARY KEY (id)
);
CREATE TABLE b
(id int(11) NOT NULLAUTO_INCREMENT,seller_name varchar(100) DEFAULT NULL,user_id varchar(50) DEFAULT NULL,user_name varchar(100) DEFAULT NULL,sales bigint(20) DEFAULT NULL,gmt_create varchar(30) DEFAULT NULL,PRIMARY KEY (id)
);
CREATE TABLE c
(id int(11) NOT NULLAUTO_INCREMENT,user_id varchar(50) DEFAULT NULL,order_id varchar(100) DEFAULT NULL,state bigint(20) DEFAULT NULL,gmt_create varchar(30) DEFAULT NULL,PRIMARY KEY (id)
);2、有问题的查询SQLselect a.seller_id,a.seller_name,b.user_name,c.state
from a,b,c
where a.seller_name b.seller_nameand b.user_id c.user_idand c.user_id 17and a.gmt_createBETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_createabc 三张表关联查询用户17 在当前时间前后10个小时的订单情况并根据订单创建时间升序排列优化步骤1、先查看各表数据量2、查看原执行时间总耗时0.21s3、查看原执行计划4、通过观察执行计划和SQL语句确定初步优化方案SQL中 where条件字段类型要跟表结构一致表中 user_id 为varchar(50)类型实际SQL用的int类型存在隐式转换也未添加索引。将b和c表 user_id 字段改成int类型。因存在b表和c表关联将b和c表 user_id创建索引因存在a表和b表关联将a和b表 seller_name字段创建索引利用复合索引消除临时表和排序初步优化的SQLalter table b modify user_id int(10) DEFAULT NULL;
alter table c modify user_id int(10) DEFAULT NULL;
alter table c add index idx_user_id(user_id);
alter table b add index idx_user_id_sell_name(user_id,seller_name);
alter table a add index idx_sellname_gmt_sellid(gmt_create,seller_name,seller_id);查看优化后的执行时间初步优化后执行速度提升了20倍是否还能继续优化呢5、继续查看优化后的执行计划这里只看到查询需要扫描的元素比较大不过还看到了有两处告警信息直接查看告警信息show warningsCannot use range access on index ‘idx_sellname_gmt_sellid’ due to type or collation conversion on field ‘get_create’这句话是告诉你由于gmt_create列发生了类型转换所以无法走索引。查看SQL建表语句发现gmt_create字段被设计成了varchar类型在SQL查询时需要转化成时间格式做查询确实不能走索引。所以需要调整一下gmt_create字段格式alter table a modify gmt_create datetime DEFAULT NULL;6、修改字段后再来查看执行时间执行速度非常完美。7、再观察优化后的执行计划可以看到执行计划也很完美至此SQL优化结束。SQL优化小结这里给大家总结一下优化SQL的套路再也不怕面试官问你怎么做SQL优化的啦。查看执行计划 explain如果有告警信息查看告警信息 show warnings;查看SQL涉及的表结构和索引信息根据执行计划思考可能的优化点按照可能的优化点执行表结构变更、增加索引、SQL改写等操作查看优化后的执行时间和执行计划如果优化效果不明显重复第四步操作在看、点赞、转发是对我最大的鼓励。您的支持就是我坚持下去的最大动力另外我的 知识星球 开通了点击 知识星球 获取限量40元优惠券加入每天不到3毛钱。目前更新了SpringCloud alibaba开发实战、Kubernetes云原生实战、分库分表实战、设计模式实战、一起学DDD 等还有每周的送书活动等着你....
