开发者社区> 问答> 正文

SQL优化原则是什么呀

SQL优化原则是什么呀

展开
收起
清风拂袖 2021-10-12 11:12:56 978 0
1 条回答
写回答
取消 提交回答
  • 解决pycharm中文输入问题 登录

    doubel_山里娃 关注 SQL优化原则 原创 2020-11-23 08:39:42

    doubel_山里娃

    码龄11年

    关注 当多个单列索引建立时候,然后SQLwhere条件后面分别用这几列作为条件,那么此时只有一个索引有效;

    SQL查询事件有两部分组成:查询本身的计算时间和查询前的等待时间。经常索引影响的是前者,而锁机制影响的就是后者;Myisam:采用的是表级别的的锁定,即在有多个查询时候不进行锁定,一旦有更新操作,就进行操作。因此适合查询次数很多,但是更新比较少的应用系统。

    尽量使用查询缓存。当有很多相同的查询被执行多次的时候,这些数据就会放到缓存里面,但是需要注意的是这些数据没有被修改过,如果这条数据的某一个字段一旦被修改,那么这条缓存就会失效;

    逻辑上对表进行分割,把常修改的数据和不常修改的数据分别放在不同的表里面。这样好处是可以减少一个表里面的字段个数,因为当字段多的时候,一条记录会存放在不同的页表里面,操作查询的复杂;第二个是 如果字段多,那么其中一个字段发生了修改操作,就会是查询缓存失效,也就不能有效的利用查询缓存;

    使用explain执行计划,来查看SQL是否执行了索引操作;

    当一条数据的时候使用limit.1;

    使用procedure analyse 会给你出某列需要设置那种合适的索引结构;

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

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

    区分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) 对于表A的每一条数据,都执行select * from 表B where 表B.id=表A.id的存在性判断,如果表B中存在表A当前行相同的id,则exists为真,该行显示,否则不显示 区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况;

    复杂多表尽量使用Join:MySQL 的优势在于简单,但这在某些方面其实也是其劣势。MySQL 优化器效率高,但是由于其统计信息的量有限,优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join,一方面由于其优化器受限,再者在 Join 这方面所下的功夫还不够,所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询,这一差距就会极小甚至在有些场景下要优于这些数据库前辈。

    尽量JOIN代替子查询:虽然 Join 性能并不佳,但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句在临时表中查询记录。查询完毕后,MySQL需要插销这些临时表。所以在MySQL中可以使用连接查询来代替子查询。连接查询不需要建立临时表,其速度比子查询要快;

    尽量少排序:排序操作会消耗较多的 CPU 资源,所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL 的响应时间。对于MySQL来说,减少排序有多种办法,比如: 上面误区中提到的通过利用索引来排序的方式进行优化 减少参与排序的记录条数 非必要不对数据进行排序;

    尽量避免select *:大多数关系型数据库都是按照行(row)的方式存储,而数据存取操作都是以一个固定大小的IO单元(被称作 block 或者 page)为单位,一般为4KB,8KB… 大多数时候,每个IO单元中存储了多行,每行都是存储了该行的所有字段(lob等特殊类型字段除外)。所以,我们是取一个字段还是多个字段,实际上数据库在表中需要访问的数据量其实是一样的。也有例外情况,那就是我们的这个查询在索引中就可以完成,也就是说当只取 a,b两个字段的时候,不需要回表,而c这个字段不在使用的索引中,需要回表取得其数据。在这样的情况下,二者的IO量会有较大差异。

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

    避免类型转换:这里所说的“类型转换”是指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换:人为在column_name 上通过转换函数进行转换直接导致 MySQL(实际上其他数据库也会有同样的问题)无法使用索引,最终全表扫描,如果非要转换,应该在传入的参数上进行转换,由数据库自己进行转换,如果我们传入的数据类型和字段类型不一致,同时我们又没有做任何类型转换处理,MySQL 可能会自己对我们的数据进行类型转换操作,也可能不进行处理而交由存储引擎去处理,这样一来,就会出现索引无法使用的情况而造成执行计划问题。

    优先优化高并发的 SQL,而不是执行频率低某些“大”SQL:这个很关键,对于破坏性来说,高并发的 SQL 总是会比低频率的来得大,因为高并发的 SQL 一旦出现问题,甚至不会给我们任何喘息的机会就会将系统压跨。而对于一些虽然需要消耗大量 IO 而且响应很慢的 SQL,由于频率低,即使遇到,最多就是让整个系统响应慢一点,但至少可能撑一会儿,让我们有缓冲的机会。高并发的SQL一旦慢的化,单个SQL可能较快,但是并发量上来之后,就会整体拖垮数据库;

    避免在where中使用!=, >, <操作符:否则引擎放弃使用索引,进行全表扫描。

    in和not in关键词慎用,容易导致全表扫面:对连续的数值尽量用between;

    避免在where中使用or:

    2021-10-12 13:57:15
    赞同 展开评论 打赏
问答分类:
SQL
问答地址:
问答排行榜
最热
最新

相关电子书

更多
SQL Server 2017 立即下载
GeoMesa on Spark SQL 立即下载
原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili 立即下载