sql性能优化

最新推荐文章于 2022-08-31 17:20:54 发布

weixin_33713707

最新推荐文章于 2022-08-31 17:20:54 发布

阅读量75

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/3100849/blog/3010603

版权

2019独角兽企业重金招聘Python工程师标准>>>

1.优化目标

1.减少 IO 次数

IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

2.降低 CPU 计算

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标。

2.优化方法

1.改变 SQL 执行计划

明确了优化目标之后，我们需要确定达到我们目标的方法。对于 SQL 语句来说，达到上述2个目标的方法其实只有一个，那就是改变 SQL 的执行计划，让他尽量“少走弯路”，尽量通过各种“捷径”来找到我们需要的数据，以达到 “减少 IO 次数” 和 “降低 CPU 计算” 的目标。

explain sql；

2.开启查询缓存

对于完全相同的sql，使用已经存在的执行计划，从而跳过解析和生成执行计划的过程。

应用场景：不经常变的表，服务器收到该表的大量相同查询。查询必须是完全相同的(逐字节相同)才能够被认为是相同的

Mysql 判断是否命中缓存的办法很简单，首先会将要缓存的结果放在引用表中，然后使用查询语句，数据库名称，客户端协议的版本等因素算出一个hash值，这个hash值与引用表中的结果相关联。如果在执行查询时，根据一些相关的条件算出的hash值能与引用表中的数据相关联，则表示查询命中。

缓存机制失效

引用了一些返回值不确定的函数
引用自定义函数
引用自定义变量
引用mysql系统数据库中的表

额外的消耗

查询的时候会检查是否命中缓存，这个消耗相对较小；
如果没有命中查询缓存，MYSQL会判断该查询是否可以被缓存，而且系统中还没有对应的缓存，则会将其结果写入查询缓存；
如果一个表被更改了，那么使用那个表的所有缓冲查询将不再有效，并且从缓冲区中移出。这包括那些映射到改变了的表的使用MERGE表的查询。一个表可以被许多类型的语句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。

3.查询语句优化

区分in和exist

select * from 表A where id in (select id from 表B)
这句相当于
select * from 表A where exists(select * from 表B where 表B.id=表A.id)

exists：对于表A的每一条数据,都执行select * from 表B where 表B.id=表A.id的存在性判断,如果表B中存在表A当前行相同的id,则exists为真,该行显示,否则不显示。

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

尽量用join代替子查询

MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句在临时表中查询记录。查询完毕后，MySQL需要插销这些临时表。所以在MySQL中可以使用连接查询来代替子查询。连接查询不需要建立临时表，其速度比子查询要快。

尽量少排序

排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL 的响应时间。

减少参与排序的记录条数

非必要不对数据进行排序

尽量避免select *

尽量少or

很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

尽量用 union all 代替 union

union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。

尽量早过滤

在 SQL 编写中同样可以使用这一原则来优化一些 Join 的 SQL。比如我们在多个表进行分页数据查询的时候，我们最好是能够在一个表上先过滤好数据分好页，然后再用分好页的结果集与另外的表 Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省 IO 操作所消耗的时间。

优先优化高并发的 SQL，而不是执行频率低某些“大”SQL

对于破坏性来说，高并发的 SQL 总是会比低频率的来得大，因为高并发的 SQL 一旦出现问题，甚至不会给我们任何喘息的机会就会将系统压跨。而对于一些虽然需要消耗大量 IO 而且响应很慢的 SQL，由于频率低，即使遇到，最多就是让整个系统响应慢一点，但至少可能撑一会儿，让我们有缓冲的机会。

尽量避免where子句中对字段进行null值的判断

会导致引擎放弃索引，进而进行全表扫描。

尽量不要给数据库留null值，尽可能地使用not null填充数据库。可以为每个null型的字段设置一个和null对应的实际内容表述。

避免在where中使用!=, >, <操作符

否则引擎放弃使用索引，进行全表扫描

常用查询字段建索引

in和not in关键词慎用，容易导致全表扫面

避免在where子句中对字段进行表达式操作或进行函数操作

会导致引擎放弃使用索引

选择最有效率的表名顺序(只在基于规则的优化器中有效)

Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。

WHERE子句中的连接顺序

Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

转载于:https://my.oschina.net/u/3100849/blog/3010603