当面试官问:怎么优化sql?

in 网站建设
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

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

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

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

上面的都是理论,在实践中,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型)

数据库优化

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

关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看