专栏首页技术那些事MySQL性能优化(六):常见优化SQL的技巧

MySQL性能优化(六):常见优化SQL的技巧

前期回顾:

MySQL性能优化(一):MySQL架构与核心问题

MySQL性能优化(二):选择优化的数据类型

MySQL性能优化(三):深入理解索引的这点事

MySQL性能优化(四):如何高效正确的使用索引

MySQL性能优化(五):为什么查询速度这么慢


在面对不够优化、或者性能极差的SQL语句时,我们通常的想法是将重构这个SQL语句,让其查询的结果集和原来保持一样,并且希望SQL性能得以提升。而在重构SQL时,一般都有一定方法技巧可供参考,本文将介绍如何通过这些技巧方法来重构SQL。

一、分解SQL

有时候对于一个复杂SQL,我们首先想到的是是否需要将一个复杂SQL分解成多个简单SQL,来完成相同业务处理结果。

在以前,大家总是强调需要数据库层来完成尽可能的工作,这也就不难理解在一些老的产品、项目中时常会看见很多超级复杂、超级长的SQL语句,这样做的逻辑在以前认为多次交互,在网络带宽、程序与数据库间网络通信等方面是一件代价很高的事情。然后在现在,无论是带宽还是延迟,网络速度比以前要快的很多,多次交互也没有太大的问题。即使在一个通用服务器上,也能够运行每秒超过10万的查询,所以运行多个小查询现在已经不是大问题了。

复杂SQL的分解,在面对超级复杂SQL语句时,性能提升尤为明显。所以,在面对超级复杂SQL语句,并且存在性能问题时,推荐分解为小查询来进行优化。

不过,在应用设计的时候,如果一个查询能够胜任并且不会产生性能问题,这时完全可以用一个稍微复杂的SQL来完成的,倘若再死板的强制拆分成多个小查询是不明智的。

在当今很多高性能的应用系统中,都是极力推荐使用单表操作,然后将单表查询结果在应用程序中进行关联,以满足复杂业务的查询需求。一个SQL可以搞定事情,为何要分开来写,而且还得在应用程序中多次执行SQL查询,再进行结果集的关联,这到底为什么要这么做呢?

乍一看,这样做复杂不说而且没有什么好处,原本一条查询,这样却变成了多条查询。事实上,这样分解有如下的优势:

  • 让缓存更高效。在应用程序中,可以很方便地缓存单表查询结果对应的结果对象,便于后续任何时候可以直接从结果对象中获取数据。
  • 分解查询后,执行单个查询可以减少表锁的竞争。
  • 在程序应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
  • 单表查询效率高于多表复杂查询。
  • 减少冗余记录的查询。在程序应用层关联,意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据记录。从这点来看,这样的重构还可能减少网络和内存的消耗。

二、查询切分

有时候对于一个大查询,即:结果集很大的查询,我们需要采用“分而治之”的思想,将大查询切分为小查询,每个查询功能完全一样,只是完成一小部分,每次只返回一小部分查询结果。通俗来讲,就是对where条件的过滤范围进行切分,每次只查询其中一部分数据,即:类似于分页查询。

这样做,不管对于SQL查询本身,还是对于上层业务来说,都是很小的开销。最典型的的案例就是分页查询,目前各类框架都有了很好的支持,如:MyBatis等,只需在实际使用时稍加留意就可避免。


三、执行计划

使用执行计划EXPLAIN关键字,可以使我们知道MySQL是如何执行SQL语句的,这样可以帮助我们分析我们的查询语句或是表结构的性能瓶颈。EXPLAIN的查询结果还会告诉我们索引主键是如何被利用的,数据表是如何被搜索或排序的…等等。

语法格式是:

EXPLAIN SELECT语句;

通过执行计划结果,将会指导我们进一步来重构SQL语句,如:增加索引、调整索引顺序、避免使用某些函数等等。

关于执行计划,后续章节将会单独详细讲解。


四、遵守原则

在平时写SQL时,养成好的习惯,多加留意,很大程度上就会避免一些SQL性能问题。汇总如下:

  • 永远为每张表设置一个ID主键。
  • 避免使用SELECT *。
  • 为搜索字段建立索引。
  • 在Join表的时候使用对应类型的列,并将其索引。
  • 尽可能的使用NOT NULL。
  • 越小的列会越快。
  • 当只要一行数据时使用LIMIT 1。
  • 操作符的优化,尽量不采用不利于索引的操作符,目的就是为了避免全表扫描。 1)in 和not in慎用,尽量用between代替in,用 not exists 代替 not in 2)is null和is not null慎用 3)!=或<>操作符能不用就不用,否则将使引擎放弃使用索引而进行全表扫描。
  • ……

五、使用查询缓存

当有很多相同的查询被执行了多次的时候,这些查询结果会被放入一个缓存中,这样后续的相同查询就不用操作而直接访问缓存结果了。

MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存,MySQL会like返回结果,跳过了解析、优化和执行截断。

这是提高查询性能最有效的方法之一,而且这是被MySQL引擎处理的,通常MySQL默认是不开启查询缓存的,需要手动开启。

查询缓存对应用程序是完全透明的。应用程序无需关心MySQL是通过查询返回的还是实际执行返回的结果。事实上,这两种方式执行的结果是完全相同的。换句话说,查询缓存无需使用任何语法。

随着现在的通用服务器越来越强大,查询缓存被发现是一个影响服务器扩展性的因素。它可能成为整个服务器的资源竞争单点,在多核服务器上还可能导致服务器僵死。所以大部分时候应该默认关闭查询缓存,如果查询缓存作用很大的话,可以配置个几十兆的小缓存空间。(在选择时,需要进行权衡)

关于查询缓存有如下参数可供配置:

  • query_cache_type 是否打开查询缓存。可以设置OFF、ON、DEMAND,DEMAND表示只有在查询语句中明确写入sql_cache的语句才放入查询缓存。
  • query_cache_size 查询缓存使用的总内存空间,单位是字节。这个值必须是1024的整倍数,否则实际分配的数据会和指定的大小有区别。
  • query_cache_min_res_unit 在查询缓存中分配内存块时的最小单位。
  • query_cache_limit 缓存的最大查询结果。如果查询结果大于这个值,则不会被缓存。因为查询缓存在数据生成的时候就开始尝试缓存数据,所以只有当结果全部返回后,MySQL才知道查询结果是否超出限制。

关于查询缓存,后续章节将会单独详细讲解。

本文分享自微信公众号 - 程序猿技术大咖(cxyjsdk),作者:xcbey0nd

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-04-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL性能优化(五):为什么查询速度这么慢

    谈到MySQL性能优化,查询优化作为优化的源头,它也是最能体现一个系统是否更快。 本章以及接下来的几章将会着重讲解关于查询性能优化的内容,从中会介绍一些查询...

    xcbeyond
  • JDK1.8新特性(一):JDK1.8究竟有哪些新特性呢

    目前JDK1.8被各大公司、各大项目纷纷使用,作为Java开发中使用最多的版本,细心的你,或许早已发现它与之前版本存在着较大的差异。如果能熟练掌握使用这些差异、...

    xcbeyond
  • JDK1.8新特性(一):JDK1.8究竟有哪些新特性呢

    目前JDK1.8被各大公司、各大项目纷纷使用,作为Java开发中使用最多的版本,细心的你,或许早已发现它与之前版本存在着较大的差异。如果能熟练掌握使用这些差异、...

    xcbeyond
  • MySQL 性能调优——SQL 查询优化

    如何设计最优的数据库表结构,如何建立最好的索引,以及如何扩展数据库的查询,这些对于高性能来说都是必不可少的。但是只有这些还不够,要获得良好的数据库性能,我们还要...

    烂猪皮
  • MySQL查询执行的过程

    当我们希望MySQL能够以更高的性能进行查询时,弄清楚MySQL中是如何优化和执行查询的就显得很有必要,这里,先搬出来一张图镇楼:

    AsiaYe
  • mysql性能调优

    mysql调优思路: 1.数据库设计与规划--以后再修该很麻烦,估计数据量,使用什么存储引擎  2.数据的应用--怎样取数据,sql语句的优化  3.mysql...

    闵开慧
  • MySQL探秘(二):SQL语句执行过程详解

     昔日庖丁解牛,未见全牛,所赖者是其对牛内部骨架结构的了解,对于MySQL亦是如此,只有更加全面地了解SQL语句执行的每个过程,才能更好的进行SQL的设计和优化...

    aoho求索
  • MySQL探秘(二):SQL语句执行过程详解

     昔日庖丁解牛,未见全牛,所赖者是其对牛内部骨架结构的了解,对于MySQL亦是如此,只有更加全面地了解SQL语句执行的每个过程,才能更好的进行SQL的设计和优化...

    remcarpediem
  • MySQL查询缓存

    MySQL查询缓存,query cache,是MySQL希望能提升查询性能的一个特性,它保存了客户端查询返回的完整结果,当新的客户端查询命中该缓存,My...

    lakezhong
  • SQL Server 中的逻辑读与物理读

    首先要理解逻辑读和物理读:   预读:用估计信息,去硬盘读取数据到缓存。预读100次,也就是估计将要从硬盘中读取了100页数据到缓存。   物理读:查询计划生成...

    用户1217611

扫码关注云+社区

领取腾讯云代金券