干货|MySQL性能优化的4个小技巧

前言

  MySQL性能优化是一个老生常谈的问题，无论是在实际工作中还是面试中，都不可避免遇到相应的场景，下面博主就总结一些能够帮助大家解决这个问题的小技巧。

SQL优化之前需要确认哪些SQL需要优化，这时就需要引起SQL性能分析工具，主要优化的是查询语句。

SQL性能优化工具

在进行SQL优化之前首先需要确认哪些SQL需要优化，这时就需要使用到SQL性能分析工具，平常工作业务中，主要优化的是查询语句。

一、SQL的执行频率

  SQL性能优化一般是针对查询语句，所以在定位是否需要优化之前，可以先确认表的更删查改的一个执行频率对比，如果是查询占主导地位，则可以一步排查。

  MySQL支持客户端通过：show [session|global] status命令对服务器状态进行查询。

查看执行频率方式：

• show global status like ‘com_______’(7个下划线,表示后面会有7个字符)

image-20220322225231943

二、慢查询日志

确认了SQL的执行频率，则需要通过慢查询日志进行进一步定位哪些SQL语句执行时间占用较长。

  慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认是10s)的所有SQL语句的日志。

  默认情况下，慢查询日志是没有开启的，需要在MySQL的配置文件(linux下默认路径为:/etc/my.cnf)中配置如下指令：

• 查询服务端是否开启慢查询日志：show variables like 'slow_query_log';
• 在mysql的配置文件中添加如下配置启动：
• 1、slow_query_log=1;开启mysql慢日志查询开关
• 2、long_query_time=xx;设置慢日志时间，只要SQL执行时间查过该值，则视为慢查询，记录在慢日志中。
• 配置完成后重启mysql服务端
• linux中mysql的慢日志文件在: /var/lib/mysql/localhost-slow.log
• window可以在my.ini文件中配置具体的地址,示例如下：

image-20220323000418257

执行结果示意图：

image-20220323000341018

参数具体含义如下：

• Query_time：表示 SQL执行的时间,越长则越慢
• Lock_time：表示在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
• Rows_sent：表示查询返回的行数
• Rows_examined：表示查询检查的行数

三、show profiles详情分析

  通过慢查询日志，我们可以定位到超过设置阈值的慢SQL，但是实际业务中，这并不能完全具有代表性，因为阈值是主观设置的，可能有大量执行时间低于阈值的SQL也存在问题，因此慢日志SQL并不能完全定位出所有的慢SQL,此时则需要借助新的工具: show profiles。

show profiles 能够让我们了解到SQL执行时时间都耗费到哪里了。 通过have_profiling参数，可以查看mysql是否支持该profile操作。

• 格式: select @@have_profiling;

image-20220323205440159

  默认情况下，profiling是关闭的，可以同set指令开启session|global级别的profiling。

• 格式: set global | session profiling = 1;

优化方案：

• show profiles：查看每一条SQL的耗时基本情况
• show profile for query query_id：查询指定query_id的SQL语句各个阶段的耗时情况
• show profile cpu for query query_id：查询指定query_id的SQL语句cpu使用情况

image-20220323210433492

四、explain执行计划

  前面介绍的几种方式都是通过执行时间长短来判断SQL语句执行的性能好坏，但是这个相对来说是比较片面的，想要更全面地评判SQL语句好坏，则需要使用explain查看SQL的执行计划。

Explain或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句过程中表如何连接和连接的顺序。

• 语法：explain | desc select xxxx...

1、ID参数

  select中的查询序号，表示的是查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上往下，id不同，值越大，越先执行)

2、select_type参数

  表示select查询类型，常见的有SIMPLE(简单表，即不使用表连接或者子查询)、primary(主查询，即外层查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)

3、type参数

  表示连接/访问类型，性能由好到差的连接类型为：null、system、const、eq_ref、ref、range、index、all

  在优化的时候，尽量将type往前优化，最差也要为index

• null：查询的时候不访问任何表，如:select "1"
• system：当访问一些系统表的时候会出现
• const：根据主键或者唯一索引访问时，会出现const
• eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
• ref：使用非唯一性索引进行访问时，可能出现ref
• range：使用索引进行范围查询时
• index：使用到了索引，但是对整个索引都进行了遍历，性能也比较差
• all：全表扫描，性能最差

4、possible_key参数： 显示在执行查询时，表中可能被使用到的索引，一个或者多个、

5、key参数： 在执行查询时，实际上会命中的索引

6、key_len参数： 表示使用到的索引的字节数，该值为索引字段最大可能长度，在不损失精确性的前提下，长度越短越好。

7、rows参数： MySQL认为必须要执行查询的行数，在innodb引擎表中，是一个估计值，可能并不总是准确的

8、ref参数： 哪些列或者常量被用作索引列上的值(如下图：图源网络，侵联删)

image-20220802210225445

9、filtered参数： 表示查询返回的行数占总读取行数的百分比，值越大越好

10、extra参数： 额外的一些执行信息如排序

写在最后

道理千遍，不如实践。性能优化更多的是结合理论的基础上进行亲自实践，这样才能够在众多的方式中找到符合的方式，希望本篇文章能够给大家一些启发

  博主最近开源了一个名为“轮子之王”的项目，其中集成了开发中常用的各项功能(现已集成有：csv、excel、ftp、文件服务器等导入导出轮子)，有兴趣的小伙伴可以通过下面的地址跳转看看，希望能够帮助大家提高开发效率。

Gitee地址： 轮子之王

Github地址： 轮子之王