一般传统互联网公司很少接触到 SQL 优化问题，其原因是数据量小，大部分厂商的数据库性能能够满足日常的业务需求，所以不需要进行 SQL 优化，但是随着应用程序的不断变大，数据量的激增，数据库自身的性能跟不上了，此时就需要从 SQL 自身角度来进行优化，这也是我们这篇文章所讨论的。

1.SQL 优化步骤

当面对一个需要优化的 SQL 时，我们有哪几种排查思路呢？

1.1 通过 show status 命令了解 SQL 执行次数

看如下动图
GIF.gif

show status 命令查看服务器状态信息。show status 命令会显示每个服务器变量 variable_name 和 value，状态变量是只读的。如果使用 SQL 命令，可以使用 like 或者 where 条件来限制结果。like 可以对变量名做标准模式匹配

Com 为开头的参数Com_xxx 表示的是每个 xxx 语句执行的次数，我们通常关心的是 select 、insert 、update、delete 语句的执行次数 Innodb 为开头的参数主要执行查询返回的行数

  1. Innodb_rows_read：执行 select 查询返回的行数。
  2. Innodb_rows_inserted：执行 INSERT 操作插入的行数
  3. Innodb_rows_updated：执行 UPDATE 操作更新的行数。
  4. Innodb_rows_deleted：执行 DELETE 操作删除的行数

总结:我们能够大致了解到当前数据库是以更新（包括插入、删除）为主还是查询为主。除此之外，还有一些其他参数用于了解数据库的基本情况

  • Connections：查询 MySQL 数据库的连接次数，这个次数是不管连接是否成功都算上
  • Uptime：服务器的工作时间
  • Slow_queries：满查询次数
  • Threads_connected：查看当前打开的连接的数量。

所有几乎所有 show status 的参数，可以当作参考
https://blog.csdn.net/ayay_870621/article/details/88633092

2 定位执行效率较低的 SQL

2.1 通过慢查询日志分析执行效率低的sql语句

MySQL 中提供了一个慢查询的日志记录功能，可以把查询 SQL 语句时间大于多少秒的语句写入慢查询日志，日常维护中可以通过慢查询日志的记录信息快速准确地判断问题所在.
一般框架里面都会配置慢查询的，修改下慢查询的时间，
查询下慢查询日志

mysql 脚本中下执行命令查询慢查询日志位置show variables like ‘%slow_query_log%’; 就可以查看慢查询数据。直接

image.png
GIF33.gif
show processlist 命令查看当前 MySQL 正在进行的线程
State 列非常重要，关于这个列的内容比较多，读者可以参考一下这篇文章
https://blog.csdn.net/weixin_34357436/article/details/91768402
这里面涉及线程的状态、是否锁表等选项，可以实时的查看 SQL 的执行情况，同时对一些锁表进行优化。

2.2 通过 EXPLAIN 命令分析 SQL 的执行计划

重点是索引类型type和key索引生效的类型和生效的索引是那个？

  1. explain  select  * from sys_dept
  3. -- select_type 常见的 SELECT 类型
  4.     --         SIMPLE 简单sql语句
  5.     --         PRIMARY 父子查询
  6.     --         UNION   联合查询
  7.     --         SUBQUERY  子查询中首个SELECT
  8. --  table       表示输出结果集的表
  9. --  type，       这个选项表示表的连接类型        ===这个选项很有深入研究的价值===
  10. --      all          这个我们接触的最多了，就是全表查询，select * from xxx ，性能最差。
  11. --      system       表中仅有一条数据时，该表的查询就像查询常量表一样
  12. --      const        当表中只有一条记录匹配时，比如使用了表主键（primary key）或者表唯一索引（unique index）进行查询
  13. --      eq-ref       表示多表连接时使用表主键或者表唯一索引，比如
  14. --      ref          这个类型不如上面的 eq-ref 快，因为它表示的是因为对于表 A 中扫描的每一行，表 C 中有几个可能的行，C.ID 不是唯一的
  15. --      ref_or_null  与 ref 类似，只不过这个选项包含对 NULL 的查询
  16. --      index_merge  查询语句使用了两个以上的索引，比如经常在有 and 和 or 关键字出现的场景，但是在由于读取索引过多导致其性能有可能还不如 range（后面说
  17. --      unique_subquery  这个选项经常用在 in 关键字后面，子查询带有 where 关键字的子查询中，用 sql 来表示就是这样
  18. --      range       索引范围查询，常见于使用 =，<>，>，>=，<，<=，IS NULL，<=>，BETWEEN，IN() 或者 like 等运算符的查询中
  19. --      index       索引全表扫描，把索引从头到尾扫一遍
  20. --   possible_keys ：表示查询时，可能使用的索引。
  21. --   key ：表示实际使用的索引
  22. --   key_len ：索引字段的长度
  23. --   rows ：扫描行的数量
  24. --   filtered ：通过查询条件查询出来的 SQL 数量占用总行数的比例
  25. --   extra ：执行情况的描述

image.png

索引类型效率柱状图
image.png

3.索引

3.1 索引介绍

最常用也是最重要的手段，通过使用不同的索引可以解决大多数 SQL 性能问题。
索引的目的就是用于快速查找某一列的数据

3.2 索引分类

数据结构分类

  • 全局索引 全局索引，目前只有 MyISAM 引擎支持全局索引，它的出现是为了解决针对文本的模糊查询效率较低的问题，并且只限于 CHAR、VARCHAR 和 TEXT 列
  • 哈希索引 哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构，很适合作为索引,MEMORY 存储引擎使用 HASH 索引，但也支持 BTREE 索引
  • B-Tree 索引 BTree 是一种平衡树，它有很多变种，最常见的就是 B+ Tree，它被 MySQL 广泛使用
  • R-Tree 索引 仅支持 geometry 数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对于 B-Tree 来说，R-Tree 的优势在于范围查找

逻辑分类

  • 普通索引 最基础的索引类 创建方式create index normal_index on cxuan003(id); 删除方式drop index normal_index on cxuan003;
  • 唯一索引 唯一索引列的值必须唯一，允许有空值，如果是组合索引，则列值的组合必须唯一，创建方式如下 create unique index normal_index on cxuan003
  • 主键索引 是一种特殊的索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引
  • 组合索引 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则，下面我们就会创建组合索引
  • 全文索引 主要用来查找文本中的关键字，而不是直接与索引中的值相比较，目前只有 char、varchar，text 列上可以创建全文索引，创建表的适合添加全文索引

  • 当然也有全局索引

    3.3 索引使用

    索引可以在创建表的时候进行创建，也可以单独创建
    我们使用 explain 进行分析，可以看到 cxuan004 使用索引的情况

    3.4.索引使用细则

    1.先创建索引再使用执行计划排查
    2.索引失效

  • 最简单的，如果使用索引后比不使用索引的效率还差，那么 MySQL 就不会使用索引

  • 果 SQL 中使用了 OR 条件，OR 前的条件列有索引，而后面的列没有索引的话，那么涉及到的索引都不会使用，比如 cxuan005 表中，只有 id 和 hash 字段有索引，而 info 字段没有索引，那么我们使用 or 进行查询。
  • 我们从 explain 的执行结果可以看到，虽然 possible_keys 选项上仍然有 id_hash_index 索引，但是从 key、key_len 可以得知，这条 SQL 语句并未使用索引
  • 在带有复合索引的列上查询不是第一列的数据，也不会使用索引
  • 如果 where 条件的列参与了计算，那么也不会使用索引
  • 索引列使用函数，一样也不会使用索引
  • 索引列使用了 like ，并且 % 位于第一个字符，则不会使用索引
  • 在 order by 操作中，排序的列同时也在 where 语句中，将不会使用索引
  • 当数据类型出现隐式转换时，比如 varchar 不加单引号可能转换为 int 类型时，会使索引无效，触发全表扫描。比如下面这两个例子能够显而易见的说明这一点
  • 在索引列上使用 IS NOT NULL 操作
  • 在索引字段上使用 <>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

    3.5 查看索引的使用情况

    查询show status like ‘Handler_read%’;在 MySQL 索引的使用过程中，有一个 Handler_read_key 值，这个值表示了某一行被索引值读的次数
    image.png

5.MySQL 分析表、检查表和优化表

5.1 MySQL 分析表

分析表用于分析和存储表的关键字分布，分析的结果可以使得系统得到准确的统计信息，使得 SQL 生成正确的执行计划.
语句analyze table cxuan005;
image.png
Table：表示表的名称；
Op：表示执行的操作，analyze 表示进行分析操作，check 表示进行检查查找，optimize 表示进行优化操作；
Msg_type：表示信息类型，其显示的值通常是状态、警告、错误和信息这四者之一；
Msg_text：显示信息。
因为通过更新表的索引信息对表进行分析，可改善数据库性能。

5.2 MySQL 检查表

数据库经常可能遇到错误:比如数据写入磁盘时发生错误，或是索引没有同步更新，或是数据库未关闭 MySQL 就停止了
我们可以使用 Check Table 语句来检查表及其对应的索引
检查表的主要目的就是检查一个或者多个表是否有错误。Check Table 对 MyISAM 和 InnoDB 表有作用。
Check Table 也可以检查视图的错误
image.png

5.3 MySQL 优化表

MySQL 优化表适用于删除了大量的表数据，或者对包含 VARCHAR、BLOB 或则 TEXT 命令进行大量修改的情况。MySQL 优化表可以将大量的空间碎片进行合并，消除由于删除或者更新造成的空间浪费情况。它的命令如下
image.png
我的存储引擎是 InnoDB 引擎，但是从图可以知道，InnoDB 不支持使用 optimize 优化，建议使用 recreate + analyze 进行优化。optimize 命令只对 MyISAM 、BDB 表起作用

ExecutorService threadPool = Executors.newCachedThreadPool();
for(int 1=1;i<4;1++){
threadPool.execute(()->{调用远成方法 ); });// A、B、C
}

  1. publicstaticintfoo(int i){<br />        if(i<=0)<br />            return0;<br />        elseif(i>0 && i<=2)<br />            return1;<br />        return foo(i-1) + foo(i-2);<br />    }

springboot自带注解实现@WebFilter + @ServletComponentScan 注解@Order(int) 指定执行顺序