✅如何进行SQL调优？

典型回答

SQL调优是面试中经常爱问的问题，这个问题可以考察一个候选人对于SQL的整体性能优化的理解和掌握程度，一般来说，SQL调优需要从以下几个方面和步骤入手。

首先，需要先发现问题，尤其是在面试中，最好是结合业务说明，比如是某一次线下报警出现了慢SQL，或者是接口RT比较长，做了性能分析发现瓶颈是在SQL查询上面都可以。但是不管怎么样，一定要有背景。

有了问题之后，那就是问题的分析了。

首先需要定位到具体的SQL语句，这个可以通过各类监控平台或者工具来实现，通过定位到SQL语句之后，我们就知道具体是哪张表、哪个SQL慢了。

那接下来就是进行分析了，一般一个SQL慢，可能有以下几种原因：

1、索引失效

2、多表join

3、查询字段太多

4、表中数据量太大

5、索引区分度不高

6、数据库连接数不够

7、数据库的表结构不合理

8、数据库IO或者CPU比较高

9、数据库参数不合理

10、事务比较长

11、锁竞争导致的等待

所以，一次完整的SQL调优，一般需要考虑以上几个因素，一般会涉及到其中的一个或者多个问题。那么就逐个优化。

首先，索引失效的问题一般是先通过执行计划分析是否走了索引，以及所走的索引是否符合预期，如果因为索引设计的不合理、或者索引失效导致的，那么就可以修改索引，或者修改SQL语句。或者强制执行使用某个索引。具体可以参考：

✅索引失效的问题如何排查？

其次，多表join也是SQL执行的比较慢的一个常见原因，关于这个问题，我们在以下文章中有详细的阐述背景和解决方案：

✅为什么大厂不建议使用多表join？

接下来，如果是索引区分度不高的话，这个其实也和索引不合理有关，但是其实到底快不快，用不用索引，并不是因为区分度高不高导致，其实还是索引扫描的行数的成本导致。所以，有的时候不能认为区分度不高就一定会效率低，或者一定就不适合创建索引。

✅区分度不高的字段建索引一定没用吗？

查询字段太多，这个有的时候是因为我们错误的用到了select * 导致的，一般来说，查询字段小于100个，都不是特别大的问题，除非真的是字段数特别多，这时候可以采用两种办法解决。第一个就是不要查询那些你不关心的字段，只查询少部分字段。第二个就是做分表，垂直分表，把数据拆分到多张表中。但是这么做可能也会带来需要多表join的问题，所以拆分的时候也需要考虑冗余。

表中数据量太大，一般来说，单表超过1000万，会导致查询效率变低，即使使用索引可能也会比较慢，所以如果表中数据量太大的话，这时候可能通过建索引并不一定能完全解决了。那么具体的解决方案有几种：

1、数据归档，把历史数据移出去，比如只保留最近半年的数据，半年前的数据做归档。

2、分库分表、分区。把数据拆分开，分散到多个地方去，这里不详细介绍了，我们的文档中有分库分表和分区的详细介绍，不展开了。

3、使用第三方的数据库，比如把数据同步到支持大数量查询的分布式数据库中，如oceanbase、tidb，或者搜索引擎中，如ES等。

数据库连接数不够，这个也需要具体分析，到底是什么原因，可能的原因有几个，第一个就是业务量太大了，单库确实扛不住了，那就选择分库吧。

第二个可能就是存在一些慢SQL、或者长事务导致的，慢SQL占用数据库链接，数据库连接数不够，其他的查询就会阻塞，就更慢。

✅数据库连接池满排查过程

数据库的表结构不合理，这个也是一个关键原因，有的时候比如某个字段中存了很长的内容，或者没有做合理的冗余需要多表关联查询等等。解决思路就是重构，或者分表。

数据库IO或者CPU比较高，这种问题也常见的，当数据库整体IO或者CPU飙高的时候，查询速度就有可能下降，所以需要分析背后的原因及解决思路，可以参考：

✅数据库CPU被打满排查过程

存在长事务，这个和慢SQL同理，都是占用了数据库链接，导致其他请求要等待。

锁竞争导致的等待，当有大并发争抢共享资源的时候，就会导致锁等待，这个过程就会拉长耗时，导致SQL变慢。这个也可以参考上面的CPU被打满的问题。

数据库参数不合理，这个也是经常会遇到的，针对我们具体的业务场景，做一些适当的参数调整，有时候也能大大的提升SQL的效率。比如调整内存大小、缓存大小、线程池大小等。

扩展知识

参数优化

假设我们有一个名为 mydb 的数据库，其中包含一个名为 mytable 的 InnoDB 表。该表有一个自增主键 id，一个整数类型字段 age 和一个字符串类型字段 name，我们希望对该表进行优化。

首先，我们可以使用 SHOW VARIABLES LIKE 'innodb%'; 命令查看当前的 InnoDB 参数设置。这些参数包括缓冲池大小、刷新间隔、日志大小等等。

接下来，我们可以尝试调整以下几个参数来优化数据库性能：

innodbbufferpoolsize: 缓冲池大小是 InnoDB 存储引擎的核心参数之一，它控制着 InnoDB 存储引擎使用的内存大小。通常，我们可以将该参数设置为系统可用内存的 70%-80%。例如，如果系统有 8GB 内存可用，我们可以将 innodbbufferpoolsize 设置为 6GB。在 MySQL 中，可以使用以下命令进行设置：

SET GLOBAL innodb_buffer_pool_size=6G;

innodbreadiothreads 和 innodbwriteiothreads: 这两个参数控制 InnoDB 存储引擎使用的 I/O 线程数量。通常情况下，我们可以将这两个参数设置为 CPU 核心数的一半。在 MySQL 中，可以使用以下命令进行设置：

SET GLOBAL innodb_read_io_threads=4;
SET GLOBAL innodb_write_io_threads=4;

innodblogfile_size: 这个参数控制事务日志文件的大小。这个值的默认为5M，是远远不够的。在 MySQL 中，可以使用以下命令进行设置：

SET GLOBAL innodb_log_file_size=1G;

一般在设置这个字段的时候，都是需要先进行数据采样，看一下一般在业务高峰期，2小时左右可以写入多少日志，然后把这个量作为你的日志文件的大小即可。如果简单点的话，一般设置为1G左右，或者系统内存的1/4即可。

原文: https://www.yuque.com/hollis666/xkm7k3/mgpczmz7la99dkft