SQL查询优化与接口性能提升实践-CSDN博客

本文链接：https://blog.csdn.net/qq_50115489/article/details/127841567

记一次查询接口的优化

问题描述

在业务中有一个查询接口的需求，需要查询在一定条件下的账单详情，这些信息被分散到了4个表中。这四个表其中一个表为常量信息表，剩余三个表最大的规模也才1万出头，但是在系统中该接口的反应时间达到了400ms，趁着这个接口需要调整，顺便优化一下接口的执行时间，目标是在50ms左右。

接口耗时分析

该接口由以下6个部分组成：

解析请求信息
根据获取基础订单信息
获取机构信息
处理各信息格式
处理特殊订单的特殊运算规则（可能运行）
打包并发送

其中：

1和6不可控制，与网络链接有关

2包含一个大的SQL查询，主要是获取被指定的账单的基础信息，并计算金额

3原本和2在同一次查询，但由于连接表的数目过多，故将其提取出来，有效降低了整体时间，由于是一个静态表，查询非常快

4则只负责对小数位数的处理，耗时在1ms以内

5为915需求新增代码，某些账单为试点账单，金额计算规则有区别，需要进行二次运算并覆盖结果。

也就是说该接口的耗时集中于两个SQL查询，要想优化接口响应时间就要减少这两个接口的耗时。

优化步骤

SQL运行情况监控工具——log4jdbc

根据分析发现了接口耗时集中在SQL语句运行这一步。尝试将SQL语句复制出来在本地运行后，发现其运行时间远小于在程序中运行，于是需要去判断在程序中SQL运行时多出来的耗时花费在哪里。

于是使用log4jdbc来对程序中的SQL执行进行监控。log4jdbc 是一个 Java JDBC Driver，可以记录 SQL 日志和 SQL 执行时间等信息，使用 SLF4J作为日志系统。通过配置jdbc.sqltiming，我们可以看到程序运行过程中SQL语句的用时。

可以发现，在一次运行中，数据库服务运行了两条SQL语句，其中第二条语句是业务需求语句，第一条是COUNT语句，COUNT对象是第二条语句。因此，在程序中运行时，耗费时间差不多是在本地直接运行的两倍。

pageHelper耗时

通过筛查，发现这多出来的语句是Mybatis插件pageHelper运行时调用的COUNT语句。

pagehelper是mybatis提供的分页插件，目前支持Oracle, Mysql, MariaDB, SQLite, Hsqldb, PostgreSQL 六种数据库。在项目中我们执行一个分页查询时，很多时候还会需要知道该查询的其他信息，如总数、每页数量、当前页数、上一页或下一页等等，这些信息如果每次都自己写的话就太麻烦了，而pagehelper能够帮助我们更方便地获取这些信息，大大方便了我们的开发效率。

因此pageHelper在获取这些信息时，需要知道数据库中所有符合条件的数据数量，即需要运行一次COUNT语句。若使用者没有在使用时复写COUNT函数，还插件会默认以该查询语句为子表进行COUNT的方式来进行运算。如果查询语句耗时很大，COUNT语句则会将这耗时翻倍，这将会大大增长运行时间。若能够自行编写出一个等效结果但运行时间更短的COUNT函数进行复写，则能减少这方面的花销。

复写方法为：
对于一个命名为A的查询函数，实现一个返回值为Long，函数名为A_COUNT的函数，在程序运行时会自动检索，若能查找到，则会调用该方法而不是使用默认的方法。

复写后在此运行程序，调用接口，接口平均耗时由480ms变为290ms，将近减少一半耗时。

EXPLAIN和show profiles;

在处理完系统上的耗时后，就该关注SQL语句本身了。要想查看SQL语句的具体执行情况，可以通过EXPLAIN和show profiles;语句来查看。

EXPLAIN

通过 explain 命令获取 select 语句的执行计划，通过 explain 可以知道 select 语句以下信息：id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra, 其中重点需要关注的是type、key、rows、extra

type

type指示了查询使用何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般而言，当type为index或者all时，该查询将不会使用到索引来加速，此时查询的效率将会极低，在此时可以考虑通过建立索引（可以一查一建？）或者修改where里的限定词，order by、group by的内容，使得索引更够正确工作。

key

key是查询中实际使用到的索引，若没有使用索引，显示为NULL，

但是即使explain的结果里写的KEY不是NULL，实际上也可能是全表扫描的，例如选择的条件为id>0，从数据层面来说，所谓索引没有意义，本质上还是遍历了整个表，最关键还是看查询了多少行数据。

rows

以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数。一般情况下 rows值越小越好，例如当使用索引能直接直接定位到所需数据行时，其rows值为1，当需要遍历全表，即type为all时，rows为该表的数据总数。

extra

显示额外的SQL运行时的状态信息。

需要注意，当出现Using filesort时，说明无法利用索引完成的排序操作，也就是ORDER BY的字段没有索引，通常这样的SQL都是需要优化的，（但其实也有可能是因为索引失效）。

当使用联表查询时，若出现Using join buffer，说明表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果，此时type应该也会是all，说明可以通过建立索引或者修改where语句等方式使索引有效。

SHOW PROFILE

show profile 和 explain不同，explain是对SQL语句的分析和运行预测，show profiles则是展示语句在运行时各个步骤的耗时，当发现在某个步骤耗时严重时，重点优化通常能取得较好结果。

具体show profile展示的条目在此不多赘述，除去查询的打开表等准备工作和清理缓存等收尾工作，我们可以重点关注sending data 和 creating sort index 两项工作的时间。

sending data 不仅仅指的是发送数据的耗时，而是指“收集 + 发送”数据。发送数据很好理解，收集数据的原因在于mysql使用“索引”完成查询结束后，mysql得到了一堆行id，如果有的列并不在索引中，mysql需要重新到“数据行”上将需要返回的数据读取出来返回给客户端。由于该期间操作倾向于大规模的磁盘读取，所以通常是查询周期中运行时间最长的阶段。我们可以通过查看该状态下cpu或io调用情况来判断语句的性能瓶颈在于读取还是处理，并作相应的优化调整。

而Creating sort index常见于order by 没有索引的情况，需要进行filesort排序，执行计划中会出现Using filesort关键字，这种情况下建议创建排序索引，减少内存排序的时间。

索引

SQL中的索引分为两种，一种为聚集索引和非聚集索引。

聚集索引一般是指数据库中数据的存储顺序，因为数据库中的数据只能按照一个顺序进行排列，所以聚集索引一个数据库只能有一个。在mysql中，不能自己创建聚集索引，一般而言，主键即为聚集索引。

主键有UUID和自增主键两种选择。MySQL官方推荐使用自增主键，因为这样的话，插入操作一定会位于数据库的末端，提升了页面的最大填充率和插入速度，减少了页分裂和碎片的产生。但是同时也会导致并发插入时的锁竞争问题，也会有根据ID值爬取数据库记录的风险。

选择UUID作为主键信息安全性要优于自增主键，且具有全局唯一性，在做系统重构或集成时能避免主键冲突的问题。但由于UUID的无序性，会影响数据的插入速度以及索引效率。不过有分布式自增ID算法snowFlake（雪花算法），能做到创建高性能，低延迟的分布式自增ID。

非聚集索引可以简单理解为有序目录，是一种以空间换取时间的方法。在添加了非聚集索引后，数据库将将该列以B+树为内部结构为数据排序，在查询时便可查询B+树，大大缩短了查询所需时间。

在以下情况应该为各列添加索引：

频繁作为 WHERE 查询条件的字段
经常 GROUP BY 和 ORDER BY 的列
对用于连接的字段创建索引

由于过多的索引会对数据的插入速度产生影响，相对应的，以下情况则不适合创建索引:

有大量重复数据的列上不要建立索引，例如用户性别
在where中使用不到的字段，不要建立索引
避免定义冗余或重复的索引，删除不再使用或者很少使用的索引

索引失效

数据库表中添加索引后确实会让查询速度有所提升，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。

OR 链接的条件，只有OR左右两边的条件都有索引时，才会使用索引，若任意一列没有创建索引，则索引失效
范围查询。当Mysql发现通过索引扫描的行并没有低于全表的一定比例时，会放弃使用索引
模糊搜索如果以%开头，则索引失效
隐式类型转换会导致索引失效
出现负向查询即NOT、!=、<>、NOT IN、NOT LIKE等判断符，索引失效（存疑）
联合索引违背最左匹配原则，即条件列中不存在联合索引的第一列，索引失效
在索引列上有计算，则会放弃索引

总而言之，当系统发现使用索引后，需要搜索的行数没有显著降低，就不会使用索引，而是进行全表搜索，此时效率会降低，应考虑更改判断条件，使索引生效。

需要注意的是，即使索引生效，最终的运行速度也不一定会比没有索引快，还是要以实际运行状况为准。

减少表链接

接口中的SQL语句严格来说可以分为两个部分：第一部分，查询数据库中所有符合条件的账单Id，并通过pageHelper分页处理；第二部分：获取对应的账单Id的具体信息，这些信息需要链接3个表才能够获取。也就是说原本的SQL语句总共连接了4个表，这样即使单个表数据库数据不算多，在进行多次表连接后的临时表规模也会变得很大，不仅占据大量内存空间，在排序时进行的filesort也会使查询时间变得很长。

在进行JOIN操作时，由于第一部分的查询中限定语句包含模糊查询等条件，不能使用索引，SQL要识别符合要求的数据，只能进行全表扫描。而且由于业务需要排序，数据库需要先对临时表进行一次filesort，又会进行一次耗时很长的排序。

所以通过之前的分析，可以考虑将两次查询拆分，先搜索数据库中所有符合条件的账单Id，再根据需要获取的账单Id通过其他三个表连接进行查询。这样即使第一部分的查询需要走全表索引，规模也限定在了第一个表的规模中，不会因为连接操作对额外的数据进行查询，减少查询时间。而第二部分的查询都是定值连接，可以通过索引减少耗时，总体而言虽然调用了两次SQL语句，但是总体时间是缩短了。

去除JOIN操作

在《阿里巴巴JAVA开发手册》中，明确规定超过三个表禁止JOIN，即使双表JOIN也要注意表索引、SQL性能。

这是因为多表JOIN对MYSQL来说处理时比较吃力的，这主要是由于MYSQL使用Nested-Loop Join（嵌套循环连接）的算法来优化JOIN方法。顾名思义，该方法主要是以循环的思想来完成表连接的操作，虽然该算法可以分为Index Nested-Loop Join、Block Nested-Loop Join、Simple Nested-Loop Join三种优化算法，但无论哪一种算法要么会导致与MYSQL的多次交互，要么会产生一个巨大的临时表，并对该临时表进行后续操作。不使用JOIN还可以在业务在分布式数据库中实现，故建议不要在SQL语句中使用JOIN。

在实际操作中我们可以用代码来实现JOIN类似的SQL语句功能，比如利用JAVA8的Stream特性来进行聚合排序等操作，通过JAVA自身的循环遍历来进行筛选，通过JAVA来进行这些操作，由于语言自带的优化特性，能取得比在MYSQL中运算更好的效果。但是若本身表的规模不大，有可能因为调用SQL语句的开销是的总时间反而增加，数据库数据规模越大，此操作效果越明显。

总结

要优化一个接口的耗时，首先可以先分析他的耗时组成，可以通过log打时间戳的方式来了解各部分的耗时情况，并针对最关键的部分进行优化。

而对于SQL查询方面的优化，我们可以使用log4jdbc、EXPLAIN和show profiles等工具来获取SQL语句的调用情况，通过调整SQL语句组成，建立索引等方式来加快SQL查询速度。

使用EXPLAIN查看查询计划，观察需要优化的地方，重点关注type、key、rows、extra四列，总体来说，需要注意的是rows的大小，这决定了语句的运行速率。而减少查询的rows则需要注意是否正确使用了索引以及索引是否正常工作。
除了优化索引，还可以通过减少链接的表的数量来减少最终查询的行数，有时也可以通过冗余存储的方式来以空间换时间。一般而言，连接的表的数目应不大于3，驱动表与被驱动表的链接条件也需要注意创建索引，否则连接的两个表都为all的话有可能最终行数为乘积。连接表的顺序也是有讲究的，尽量使限定后行数小的表在前，大的表在后，虽然SQL引擎会尝试优化，但有时候也不一定成功，最好手动调整顺序。
在使用了子表查询时，要注意谓词推进，即将子表外部的谓词尽可能的推进到子表查询内部，以减少子表的最终行数。因为使用子表查询时，创建出来的子表是无法构建索引的，只能进行全表查询。所以我们可以手动推进谓词，这样即使进行全表查询也不至于耗费太多时间。