前言

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

SQL 性能优化工具

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

一、SQL 的执行频率

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

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

  查看执行频率方式：

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

二、慢查询日志

  确认了 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 文件中配置具体的地址,示例如下：

  执行结果示意图：

  参数具体含义如下：

• 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;

  默认情况下，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 使用情况

四、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 参数： 哪些列或者常量被用作索引列上的值(如下图：图源网络，侵联删)

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

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

性能优化技巧

  学习完如何使用调优工具定位需要优化的 SQL 后，下面就来认识 SQL 的增、删、查、改进行优化技巧吧。

一、插入数据优化

  插入数据的优化点：主要在于最大程度上利用每一次数据库连接，避免频繁创建数据连接，因此，常见的优化方式如下：

• 批量插入(单条插入需要每次都与数据创建链接，存在比较大消耗)

• 手动管理事务(可以将批量多个批量插入放在一个事务中，减少开启、关闭事务次数)

• 数据按照主键顺序插入(避免页分裂和重新指针指向，下面会具体介绍这两者的概念)

• 大数据量时使用 load 指令(如初始化时需要几百甚至上千万数据(百万数据十几秒)，此时则使用 load 命令来进行插入数据，mysql 原生支持大数据量插入，性能非常高)

  load 命令的使用：

• 如果是命令行连接，需要指定客户端需要执行本地文件，在连接中添加:--local-infile

• 服务端开启 load 指令支持：set grobal local_infile=1

• 语法：load data local infile '文件路径' into table '表名' fields teminated by '字段分割符号' lines teminated by '行分割符号'

主键优化

  数据组织方式:

  在 MySQL 的 InnoDB 引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(Index Organized Table IOT)，相关概念如下：

  页(Page)： 存放的就是具体的行数据

  特点： 页可以为空、也可以填充一半，或者填充 100%。每个页包含了 2-N 行数据(如果一行数据太大，会行溢出)，页中数据根据主键排序【InnoDB 中规则每个页中至少大于 2 行，如果只有一行，证明形成了链表，在 innodb 中是允许的】。页与也之间存在指针相互指向。

  页分裂：

  如果插入数据是数据的主键时乱序插入，因为 InnoDB 中数据是按照主键的顺序存放在页中的，它会找到本应该插入的数据页 50%的位置(该数据页因为乱序插入已经满了)，然后将之后的元素以及新插入的元素放到新申请的页中。然后指针重新指向的现象。

  页合并：

  注意： 在 InnoDB 中，当删除一个记录时，实际上记录并没有被物理删除，知识记录被标记(flaged)为删除，并且它的空间变得允许被其他记录声明使用。

  定义： 当页中数据被删除到 MERGE_THRESHOLD(默认是页的 50%)，InnoDB 会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

  MERGE_THRESHOLD 参数在创建表或者索引时可以进行指定，默认就是页的一半。

  主键设计原则：

• 满足业务需求情况下，尽量降低主键的长度(因为二级索引叶子节点存储的是主键值，主键值越长，占用的空间越大，在搜索时需要耗费磁盘 IO 的次数就越多)

• 插入数据时，尽量顺序插入，选择使用 AUTO_INCREMENT 自增主键(乱序插入可能导致页分裂，消耗性能)

• 尽量不要使用 UUID 做主键或者其他自然主键如身份证(因为它们是无序的，还是会存在页分裂，同时因为它们的长度也较长，在检索时会耗费大量的磁盘 IO)

• 业务操作时，尽量避免对逐渐的修改(修改了主键，需要重新维护对应的索引数据结构)

二、查询优化

1、Order by 优化

  使用 explain 关键字查看 SQL 语句的执行计划，注意：出现 Using index 的前提时走了覆盖索引，多字段排序时，也遵循最左前缀法则。

  1、Using filesort: 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓存区 sort buffer 中完成排序操作。所有不是通过索引直接返回排序结果的排序都叫 Filesort 排序。

  2、Using index: 通过有序索引顺序扫描直接返回有序数据，这种情况称为 using index，它不需要额外排序，操作效率高。

  3、Backward index scan;Using index: 没有进行额外排序，但是进行了反向扫描索引。

  4、Using index;Using filesort: 没有直接通过索引返回有序数据，需要走过 sort buff 进行排序，效率也是较低。

  Using filesort 优化方式：

• 给对应的字段创建联合索引(注意要根据排序的顺序或者倒叙指定索引的顺序)

• 如果不可避免出现 filesort，在对大数据量排序时，可以释放增加排序缓冲区大小 sort_buffer_size(默认是 256K)，查询方式:show variables like 'sort_buffer_size'。

• 如果排序缓冲区被占满，则会在磁盘进行排序操作，性能会降低

2、group by 优化

  分组操作中，主要是索引起了优化效果。使用 explain 关键字查看 SQL 语句的执行计划分组情况如下：

• Using temporary: 使用了临时表，性能较低

• Using index: 走了索引，性能提高(案例：group by 和 where 中字段满足最左前缀法则)

• Using index;Using temporary：案例如不遵循最左前缀法则，但是命中索引覆盖时，可能出现这个值

  优化技巧： 通过索引来提高效率，注意是否满足最左前缀法则

3、Limit 优化

  现象： 在大数量时分页时，越往后的数据，需要耗时越大，效率越大

  优化： 子查询(多表关联) + 覆盖索引

  方式： 先查询到需要筛选数据的主键，然后再进行数据子查询或者表关联查询到需要的具体数据

​

4、Count 优化

  这个话题已经是老生常谈了，但是总有人争论不休，其实，最优权威的是官方的说法，官方是推荐使用 count(*)而不是其他，下面来认识各种 count 用法的一个区别。

  MyISAM 引擎会把一个表中的总行数存储到磁盘中，在执行 count(*)不带 where 条件时，可以直接拿到该数据，效率很高。

  InnoDB 在 count 时，需要将数据一行行从引擎读取出来，然后累计计数(大数量的情况下是比较耗时的，主要是由存储引擎决定的)。

  优化思路：借助内存数据库手动维护总条数，插入时加 1，删除时减 1 等

  count 的用法：

• count(*): 对返回的数据进行计数。逻辑：引擎做了专门优化，不取值，服务层直接按行进行累加。

• count(主键)：主键不可能为 NULL,InnoDB 会遍历全表、将每行的主键 ID 取出来，返回给服务层进行累计操作，无需判断是否为 NULL。

• count(1)：对返回的每条数据都置 1，然后进行累计。逻辑：引擎遍历全表，但是不取值，服务层对返回的每一行都放一个数字"1"进去，直接进行累加操作。

• count(列)：统计字段值不为 NULL 的条数。统计逻辑：没有 not null 约束，idb 引擎会遍历全表的每一行的字段值取出来，返回给服务层，服务层会判断是否为 null，不为 null 则进行累加。如果有 not null 约束，则引擎会遍历全表返回每一行的字段值，返回给服务层，服务层直接进行累加操作。

  推荐使用：count(*)

Update 语句优化

  更新数据时 where 条件一定要使用索引字段，否则就会从行锁升级为表锁，并发情况下，性能降低。

删除语句优化

  跟插入语句类似，要利用批量删除的方式，最大程度减少数据库连接，事务提交的消耗。

写在最后

  道理千遍，不如实践。性能优化更多的是结合理论的基础上进行亲自实践，这样才能够在众多的方式中找到符合的方式，希望本篇文章能够给大家一些启发，如果文章对你有帮助，欢迎给博主点赞、关注。

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

  Gitee 地址： 轮子之王

  Github 地址： 轮子之王