MySQL：五个常见优化SQL的技巧

时间：2022-05-19 23:48:16

SQL命令因为语法简单、操作高效受到了很多用户的欢迎。但我们经常碰到质量不高、或者性能极差的SQL语句，这时，大多数人的想法是：重构这个SQL语句，让其查询的结果集和原来保持一样，并且希望SQL性能得以提升。

其实，在重构SQL时，我们可以运用一些小技巧，让我们的优化工作更简单。

MySQL：五个常见优化SQL的技巧

分解SQL

 

面对一个复杂SQL，我们可以将它分解成多个简单SQL，即使变简单了，但是也能够得到相同的处理结果。

复杂的SQL通常会出现在一些老的产品、项目中，因为从前的开发者认为，多次交互，在网络带宽、程序与数据库间网络通信等方面是一件代价很高的事情。然而现在的技术发展已经能够解决这个不足，因为运行多个SQL已经不是问题。

复杂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才知道查询结果是否超出限制。

原文链接：https://www.toutiao.com/i6967161855005458948/

标签：

相关文章