慢SQL优化

最新推荐文章于 2024-04-16 06:25:47 发布

panxl6

最新推荐文章于 2024-04-16 06:25:47 发布

阅读量859

点赞数 1

分类专栏：性能优化 SQL 解决方案

本文链接：https://blog.csdn.net/panxl6/article/details/103741453

版权

解决方案同时被 3 个专栏收录

4 篇文章 0 订阅

订阅专栏

性能优化

1 篇文章 0 订阅

订阅专栏

SQL

1 篇文章 0 订阅

订阅专栏

一个SQL执行时间过长，长期占用MySQL会话连接，会耗尽连接数。这样其他请求就无法建立连接。一个SQL返回时间过长，对用户体验也会造成影响。另外，慢SQL会消耗数据库实例的资源，进一步把其他正常SQL也变成慢SQL。可能造成数据库实例失去响应。毕竟，雪崩的时候，没有没有一片雪花是无辜的。

代码层面的优化，受不同的编程语言、技术选型等因素的影响，解决方案也会有一定的差异。但是SQL优化则具备比较高的通用性。虽然不同厂商实现的数据库管理软件在执行SQL时会有一定的差异，但是都基于标准SQL。

慢SQL的判定

通常来说，执行时间超过１ｓ的SQL我们就认为这是一条慢SQL。我们可以从MySQL的慢查询日志中收集。
但是，仅凭执行时间判断是不够的。如果当前MySQL实例的负载很高，即使是一条按主键查询的简单SQL也会变成慢SQL。也需要综合其他因素进行判断，比如平均执行时间、扫描行数、执行次数。

MySQL的工作原理与慢的原因

MySQL使用Ｂ+树来组织数据和索引。四层的B+树，已经能够组织足够多的数据了。查询每一层的节点，都需要一次IO操作。而一次磁盘的操作，大概是10ms。因此，如果是走了索引，比如说是主键索引，最坏的情况下四次IO操作就可以取到数据了。50ms就够了。

慢SQL优化的一般步骤

优化，主要是找冗余。可以从减少IO操作和耗时计算两个方面考虑。
SQL的调试不像静态编程语言调试那样，可以设置断点。在MySQL中可以使用EXPLAIN命令查看SQL的执行情况。
执行优化后的sql时，为了得到准确的执行时间，加入SQL_NO_CACHE指令，避免SQL执行时间受到MySQL缓存的影响。另外，MySQL执行某个SQL后，会把相应的数据加载到缓冲区进行预热。因此初次执行的时间跟多次执行的时间会有一定的差别。
那些是需要优化的慢SQL。

慢SQL的常见类型

索引问题

查询没有走索引。如果一个表的数据量比较大，但是没有索引，会造成慢查询。这种类型的问题往往是某些场景没有考虑到，或者项目初期没有这么大的数据量，对查询的影响不大。一般这种问题可以通过工具来辅助发现问题sql。
索引的区分度不高。索引设计的不好，没办法有效的过滤掉不符合查询条件的记录。此时数据库仍然需要扫描大量的行。性别字段、表示开关状态的字段，加了索引也没有太大的作用。
问题索引。添加了索引，查询不一定就会变快。重复的索引。索引合并的情况。
索引的选择问题。MySQL没有选择最优的索引。此时可以选择强制走某个可能最优的索引，或者忽略某个最坏的索引。
排序。
索引无法命中的问题。类型不匹配，导致隐式类型转换。MySQL不支持函数索引，某个字段使用了函数导致该字段的索引无法使用。

分页查询

查询结果一次返回。随着业务量的增长，一次返回的数据量太大会造成性能问题。
数据量太大时，分页的查询也很慢。分页查询可以减少数据库单次查询的数据量，从而缓解性能压力。但是，MySQL的分页机制下，即使是分页数据库也会把符合条件的数据都拉取出来，然后返回指定分页区间的数据。随着分页的增长，查询依然会越来越慢。此时，可以先查询符合条件的记录主键，然后根据主键查询相应的行记录。

子查询

子查询会创建临时表，一定程度上会降低数据库实例的性能。

使用IN、EXISTS语法时，参与查询的行数不宜过多，100以内。多的话分页查询。
在应用层拆分。在代码层面先获取子查询的结果，然后再将其作为外层SQL的条件，分步实现。
将子查询改为连表查询。

连表查询

连接多个表的问题。

用数据量较小的表驱动数据量较多的表。
移除掉没必要的表。在select和where语句中都没有涉及到的表，是不需要连接的。
拆分为多个SQL。先查询主表，然后再根据连接字段分别查询对应的表的数据。
避免join操作。在对应的表上将某些常用字段冗余一份，可以减少不必要的连接。或者保存一个json字符串，将所需的结果都放入其中。换句话说，就是使用NoSQL的方式聚合对象信息。