常见面试题之：如何优化慢SQL（慢查询）即:SQL性能优化

一、SQL性能低原因：

        1.sql执行等待时间长

                锁表的操作导致查询一直处理等待状态，结合mysql锁的机制去分析SQL执行慢的原因；

        2.sql本身执行时间长

                1.sql语句写的不好

                2.索引失效

                3.关联查询或者子查询太多

                4.服务器调优以及各个参数设置；

二、性能优化目标：

        系统性能优化的目的或者目标那无可厚非是给广大系统用户提供良好的使用体验，甚至高效的系统响应可以吸引更多的用户；

三、慢查询优化思路：

1.可以从技术和业务代码两个方面来分析优化；

   明确优化目标，了解该性能问题瓶颈所在，并从以下两大方面入手：

        1).技术

                对于高并发的业务场景SQL，应该着重关注并及时优化解决。

                         例如该SQL每小时执行20000次，每次30个IO，优化后20个IO，每小时减少20万个IO；

               SQL语句本身有问题，未使用索引字段查询，导致数据扫描消耗太多时间即IO耗时太高；合理创建索引，使用索引字段作为查询条件；

               SQL语句带有函数等操作，比如排序、分组等，数据运算花费了大量的时间，占用CPU过高；（改为使用索引字段分组、排序更快，因为索引本来就是排序好的）

                随着系统的访问并发量增高，原有的网络带宽不足导致查询变慢；

                数据库数据量逐渐增多，优化SQL和表结构等设计后还是慢，可以考虑分库分表，数据库采用主从复制等技术方案加快数据访问速度。

                查询条件where后，尽量使用主键，索引等字段查询，不是后面的条件字段越多越好；

                关联查询语句不超过三张表，若又很多张表关联的复杂SQL，可以拆分为小的SQL获取结果，在业务代码中进行封装处理。因为join占用的资源比较多，会导致其他进程等待时间边长；

不过具体结合实际场景分析，不是拆的越少越好，个人经验，数据量少的参数类配置表信息，可以适当关联多几个表操作，减少数据库的IO。数据量大的表进行拆分查询后代码内封装处理好些；

        2).业务代码

                结合当前SQL具体功能，排查业务代码是否可以去掉冗余字段，该查询是否有必要，该查询是否为冗余查询等等；（冗余的查询可能让SQL查询长时间的等待导致查询慢甚至死锁）

                将多个查询合并为一个，减少非必须要的IO操作；

                查询的结果字段只获取自己当前业务中需要的字段即可，尽量必要使用select * 的操作。

四、慢查询SQL问题分析工具：

1.Mysql自带慢查询日志记录：

        1).通过开启mysql慢查询配置记录慢sql的执行信息；

            slow_query_log:是否开启慢查询日志记录开关，ON或者1表示开启，OFF或者0表示关闭；

            slow_query_log_file:配置mysql慢查询日志文件的存储路径；

            long_query_time:配置慢查询阈值时间配置（当查询sql执行多长时间被认定为慢查询SQL，记录到慢查询日志中）

        2).慢查询日志配置完成后，执行一个慢查询的sql，查看慢查询日志信息；

        日志内容参数：

                      time:SQL执行时间

                      user:执行用户

                      query_time:SQL查询耗时

                      lock_time:等待锁的时长

                      rows_sent:查询结果行数；

                      rows_examined:该次执行查询扫描的行数

                      set timestamp:时间戳

                      SQL:具体sql信息

2.SQL执行计划（explain）

       拿到慢查询SQL后，可使用explain来分析该SQL的执行计划，确定该SQL慢查询问题；

        1). explain的使用：explain + 具体问题SQL

                例如：explain select * from student where id = '20230101';

        2).explain参数说明：

            

参数列表：id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra

explain各参数介绍

id	SELECT识别符。这是SELECT的查询序列号，即在查询中执行select语句或操作表的顺序。查询出来的id有两种情况，可同时存在 1).id相同，执行顺序为从上到下 2).id不同，如果是子查询，id的序列号会递增，id值越大，优先级越高，越早被执行
select_type	SELECT类型,可以为以下任何一种: SIMPLE:简单SELECT(不使用UNION或子查询) PRIMARY:主查询，查询中包含子查询等复杂查询时，外层查询则被标记为PRIMARY。 UNION:UNION中的第二个或后面的查询语句 DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询 UNION RESULT:UNION 的结果 SUBQUERY:子查询中的第一个SELECT DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 DERIVED:导出表的SELECT(FROM子句的子查询)
table	表明数据是从那张表中查询的，关联了哪些表
partitions	表示查找的是哪个分区的数据
type	联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型（由上到下性能逐渐变差）进行排序: system:表仅有一行(=系统表)。这是const联接类型的一个特例。 const:表示通过索引一次就找到了匹配的数据，例如通过主键匹配的条件查询 eq_ref:类似ref，区别就在使用的索引是唯一索引。唯一性索引扫描，对每个索引键表中只有一条相对应的记录，也就是主键或唯一索引和他们对应的数据这样的情况 ref:非唯一性索引扫描，即索引查找出对应多个符合条件的数据 ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。 index_merge:该联接类型表示使用了索引合并优化方法。 unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。 index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) range:只检索给定范围的行,使用一个索引来选择行，常见于<，<=，>，>=，between等范围查询。 index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。从索引中读取全表。 ALL:对于每个来自于先前的表的行组合,进行完整的表扫描，全表扫描。
possible_keys	指出MySQL能使用哪个索引在该表中找到行，表示查询时可能使用的索引。
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len	显示MySQL决定使用的索引键长度。如果键是NULL,则长度为NULL。越短越好，该值为表计算估计出的最大长度，而不是实际使用长度
ref	显示索引的哪一列被使用到了
rows	显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。完成本次查询要扫描的表数据行数。
filtered	显示了通过条件过滤出的行数的百分比估计值。
Extra	该列包含MySQL解决查询的详细信息： Distinct:MySQL优化了distinct操作，匹配到第一组所需的数据后停止查找相同数据的操作 Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。 range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。 Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。 Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。 Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。 Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。 Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

注意：

        • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
        • EXPLAIN不考虑各种Cache
        • EXPLAIN不能显示MySQL在执行查询时所作的优化工作
        • 部分统计信息是估算的，并非精确值
        • EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

五、如何合理的设计并使用索引

        1.如何判断是否需要创建索引？

                1).频繁的作为查询条件的字段，建议创建为索引；

                2).唯一性太差的字段不适合单独创建索引，如果是频繁的作为查询条件，可以创建聚簇索引来查询。（当一条查询语句返回的数据量超过全表数据量的15%的话，就不应该再使用索引扫描来完成这个查询了，可以增加或者更换索引字段）

                3).更新非常频繁的字段不适合创建为索引，因为索引中的字段被更新的时候，不仅仅需要更新表数据，同事还需要更细索引数据，以确保索引信息是准确的；

       2.如何选择合适索引：

                 1).单健索引，选择针对当前查询过滤行更好的索引字段；

                 2).选择联合索引时候，当前过滤性最好的索引放在最前面（mysql有自带的SQL优化功能，但是还是习惯性的这么写）；