面试官:怎么优化 SQL?

时间: 2019-09-03阅读: 41标签: 面试

在面试的环节中,面试官问到:你是如何设计你的表结构的,画一下E-R图?接着又继续深挖,如果有慢查询,你是如何优化你的sql的?

今天,我就来和大家讲讲要怎么回答这道问题。首先,我们要稳住不要慌,自己是自己亲手做的项目,第一个问题应该都不大,第二个问题就需要在面试之前做好充分的准备啦…

在回答问题之前先要了解查询的流程:查询是由一系列的子任务组成的,包括从客户端,到服务器,然后在服务器上进行解析,生成执行计划,执行,并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段,这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理,包括排序、分组。为了完成这些任务,查询需要在不同的地方花费时间,包括网络,CPU计算,生成统计信息和执行计划、锁等待操作。进行一些不必要的额外操作时或者某些重复执行某些额外操作会消耗大量的时间。

查询性能低下最基本的原因是访问的数据太多。某些查询可能不可避免地需要筛选大量的数据,大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询,可以通过以下两个步骤来分析:

  • 确认应用程序是否在检索大量超过需要的数据。

  • 确认MySQL服务器是否在分析大量超过需要的数据行。

上面的都是理论,在实践中,MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化这三个方面。


SQL语句的优化:

1、少用子查询

尽量少用子查询,因为子查询会产生临时表;除非像count(*)临时表很小的。

2、少用SELECT *

每次看到SELECT *都需要用怀疑的眼光审视,是否真的需要返回全部的列?取出全部的列,会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的I/O、内存和CPU的消耗。

3、查询必要的记录

一个常见的错误是常常会误以为MySQL只会返回需要的数据,实际上MySQL却是先返回全部结果集再进行计算,建议在查询后面加上LIMIT。

4、不要重复查询相同的数据

不断执行相同的查询,然后每次都会返回完全相同的数据。可以采用的方案是初次查询的时候将这个数据缓存起来,需要的时候从缓存中取出,这样性能显然会更好。

5、COUNT查询优化

COUNT()聚合函数的作用:统计某一个列值的数量,也可以统计行数。需要注意的是统计列值时要求列值是非空的(不统计NULL),COUNT()查询尽可能少的行。

举个例子:如果我们直接查 id>100 的记录,涉及到的有两千多万行记录扫描。但是由于COUNT()特性,我们可以用 count() - (id<100)的做法,这样扫描的行就只有100行了。

6、Where子句中,where表之间的连接必须写在其他Where条件之前,那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。

7、用EXISTS替代IN、用NOT EXISTS替代NOT IN。

8、避免在索引列上使用计算。

9、避免在索引列上使用IS NULL和IS NOT NULL。

10、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

11、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描。

12、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。


索引优化

1、关联查询优化

确保ON 或则USING 子句的列上有索引。创建索引时就要考虑关联的顺序,当表A和表B用列c关联的时候,如果优化器关联顺序是B、A,就只需要在表A上建立索引,没用的索引会占用存储。

2、GROUP BY 和 DISTINCT优化

GROUP BY 和 DISTINCT的优化最有效的就是使用索引。所有对于分组的列一定要建立索引。比如:

select product, count(*) from orders group by product;

这样的一个查询,对product要建立索引。

3、LIMIT分页优化

进行分页操作时,通常都会通过偏移量来查询某些数据。然后再加上解释的order by,性能一般都不错。对于order by的列 一定要加上索引。但是对于limit 10000,10 这样检索目标10条记录必须先先查询前面的10000条记录。代价很高,这种时候优化最简单办法就是使用覆盖索引。

注意索引失效的情况,

1)以“%”开头的LIKE语句,模糊匹配

2)OR语句前后没有同时使用索引

3)数据类型出现隐式转化(如varchar不加单引号的话可能会自动转换为int型)


数据库优化

选择优化数据类型的几条建议:

  • 更小的通常更好,尽量使用可以正确存储数据的最小数据类型,因为占用更少的磁盘、内存和CPU缓存。

  • 简单最好,选择整数而不是字符串,选择MySQL内建的类型而不是字符串来存储时间和日期,使用整数来存储IP地址。

  • 尽量避免NULL,很多表都包含可为NULL的列,这是因为NULL是列的默认值,需要指定列为NOT NULL。

  • 整数类型数据一般用int,对于布尔类型的数据用tinyint,但是整数计算一般是使用64位的BIGINT整数。

  • 在需要对小数进行精确计算时,比如说存储财务数据才使用DECIMAL(浮点存储的float和double类型计算不精确),但是DECIMAL计算的代价很高,可以考虑使用BIGINT代替DECIMAL,将小数的位数乘以相应的倍数即可。

  • varchar和char

    当需要存储可变长的字符串用varchar,比使用char存储更节省空间,varchar使用1或者2个额外的字节来记录长度。至于用char来存储适用于下列几种情况,一是需要存储很短的字符串时(存储只有Y和N的值时),二是所有的值接近固定长度(存储MD5值),三是经常需要变更的值。

  • BIT

    在MySQL5.0之前,BIT是TINYINT的同义词,在MySQL5.0以及更新的版本,是一个完全不同的数据类型。BIT类型的新行为:(1)可以使用BIT列在一列中存储一个或者多个true/false值。MySQL把BIT当做字符串类型,而不是数字类型。当检索BIT(1)的值时,结果是一个包含二进制0或者1的字符串,而不是ASCII的“0”或“1”。

  • SET

    如果需要保存很多的true/false值,可以考虑合并这些列到一个SET数据类型,它在MySQL内部是一系列打包的位的集合来表示的。

  • 使用枚举代替常用的字符串类型,因为MySQL在存储枚举时非常紧凑,MySQL把每个枚举的值保存为整数,并且在表的.firm文件中保存“数字-字符串”映射关系的“查找表”。

  • DATATIME存储的范围更广,保存的值从1001年到9999年,精确到秒,与时区无关,使用8个字节的存储空间,使用一种可排序、无歧义的格式显示时间,TIMESTAMP类型保存了从1970年1月1日午夜以来的秒数,使用4个字节的存储空间,只能表示从1970年到2038年,依赖于时区,空间效率更高,推荐使用TIMESTAMP

  • 对于BOLB和TEXT类型他们都是为了存储很大的数据而设计的字符串,分别采用二进制和字符串方式存储。

  • 不能有太多的列

  • 单个查询最好在12个表以内做关联

  • 当遇到未知值的时候不要害怕使用NULL

  • 在实际的应用中需要混用范式和反范式,使用部分范式化的schema、缓存表、以及其他的技巧,最常见的反范式化数据的方法是复制或者缓存,在不同的表中存储相同的特定列。

  • 修改.frm文件来加快ALTER TABLE 操作的速度

  • 选取最适用的字段属性,尽可能减少定义字段宽度,尽量把字段设置NOTNULL,例如’省份’、’性别’最好适用ENUM

  • 使用连接(JOIN)来代替子查询

  • 用联合(UNION)来代替手动创建的临时表

  • 锁定表、优化事务处理

原文 https://mp.weixin.qq.com/s/a4CWa_PjR7sPk5kS9ZyPpA


吐血推荐

1.阿里云: 本站目前使用的是阿里云主机,安全/可靠/稳定。点击领取2000元代金券、了解最新阿里云产品的各种优惠活动点击进入...

2.腾讯云: 提供云服务器、云数据库、云存储、视频与CDN、域名等服务。腾讯云各类产品的最新活动,优惠券领取点击进入...

3.站长广告联盟: 整理了目前主流的广告联盟平台,如果你有流量,可以作为参考选择适合你的平台点击进入...

4.休闲娱乐: 网页游戏入口... H5游戏入口... 绝地求生首次打折降价了点此访问

链接: http://www.fly63.com/article/detial/6231

浅谈前端面试

最近组里大佬出差,面试的任务落在了我这个小喽啰身上。作为一位中级工程师,让我面试高级工程师,我也是压力山大,只得硬着头皮上。这几天面试了许许多多人,感慨良多,不吐不快

10道css综合面试问题

css sprite是什么,有什么优缺点;display: none; 与 visibility: hidden; 的区别;清除浮动的几种方式;css3有哪些新特性;position的值, relative和absolute定位原点是

面试官:如何用 Redis 实现全局锁

WEB服务,尤其是分布式的WEB服务,通常会面临需要实现安全高效的全局锁的服务场景。而redis的诸多特性,使得借助其实现的全局锁显得如此简单。本篇文章借助小明应聘魔笛手研发岗位的面试

35道必须要清楚的 React面试题

虚拟 DOM (VDOM)是真实 DOM 在内存中的表示。UI 的表示形式保存在内存中,并与实际的 DOM 同步。这是一个发生在渲染函数被调用和元素在屏幕上显示之间的步骤,整个过程被称为调和。函数组件和类组件当然是有区别的

大龄前端如何准备面试?

今年毋庸置疑是找工作的寒冬,今年出来找工作的每一个同学 都是值得尊敬的。 在寒冬季找工作,虽然略难,但是反过来看也会逼迫我们成为更加优秀的自己。但是不管是旺季还是寒冬,有一些优秀的同学找工作还是挺顺利的

面试官,不要再问我三次握手和四次挥手

三次握手和四次挥手是各个公司常见的考点,也具有一定的水平区分度,也被一些面试官作为热身题。很多小伙伴说这个问题刚开始回答的挺好,但是后面越回答越冒冷汗,最后就歇菜了。见过比较典型的面试场景是这样的:

7个带陷阱的js面试题

在 JS 面试中,经常会看到一些简单而又沙雕的题目,这些题目包含一些陷阱,但这些在我们规范的编码下或者业务中基本不会出现。 有些面试官就是这样,不专注于制定代码的标准和规范上,却用不规范的代码去检验别人是否细心

全网最全的http面试答案

HTTP1.0定义了三种请求方法: GET, POST 和 HEAD方法,HTTP1.1新增了五种请求方法:OPTIONS, PUT, DELETE, TRACE 和 CONNECT;这些方法的具体作用是什么?

Js字符串类面试题

解析 URL Params 为对象;模板引擎实现;转化为驼峰命名;查找字符串中出现最多的字符和个数;字符串查找请使用最基本的遍历来实现判断字符串 a 是否被包含在字符串 b 中

JS常见的面试题

在现代js中,let&const是创建变量的不同方式。 在早期的js中,咱们使用var关键字来创建变量。 let&const关键字是在ES6版本中引入的,其目的是在js中创建两种不同类型的变量,一种是不可变的,另一种是可变的。

fly63.com版权所有,内容以共享、参考、研究为目的,不存在任何商业目的。其版权属原作者所有,如有侵权,请与小编联系!情况属实本人将予以删除!

广告赞助文章投稿关于web前端网站点搜索站长推荐网站地图站长QQ:522607023

小程序专栏: 土味情话心理测试脑筋急转弯幽默笑话段子句子语录成语大全