1、Explain

对MySQL语句进行优化前，需要获取查询语句的执行计划，通过执行计划我们可以掌握这条查询语句在实际执行过程中是怎样的过程，这对我们排查慢sql，优化sql语句提供了参考。
请参考：EXPLAIN，或者阅读Explain 详解（上）、Explain 详解（下）。

掌握Explain执行计划的每一列的含义，是优化MySQL语句的前提。

2、MySQL优化概述

2.1 优化的维度

说到一条查询语句的优化，不是仅局限在sql语句上的优化，还有一些非sql语句层面的优化，比如提高网络带宽、开启查询缓存功能、提升硬件性能都是优化的点，只不过sql语句的优化是成本最小、效果最明显、同时也是面试中最高频的点。
image.png

  • 优化成本：硬件>系统配置>数据库表结构>SQL及索引。
  • 优化效果：硬件<系统配置<数据库表结构<SQL及索引。

MySQL的整体优化一般遵循下面五个原则：

  1. 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO；
  2. 返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘IO及网络IO；
  3. 减少交互次数：批量DML操作，函数存储等减少数据库连接次数；
  4. 减少服务器CPU开销：尽量减少数据库排序操作以及全表查询，减少cpu 内存占用；
  5. 利用更多资源：使用表分区，可以增加并行操作，更大限度利用cpu资源。

总结到SQL优化中，就三点：

  • 最大化利用索引；
  • 尽可能避免全表扫描；

  • 减少无效数据的查询。

    2.2 MySQL逻辑架构

    了解Mysql的逻辑架构对于Mysql的优化有帮助，Mysql的架构分层如下图所示：
    image.png
    MySQL架构从上到下可以分为4层，分别是：

  • 客户端：各种开发语言都提供了连接MySQL数据库的框架，比如Java提供了JDBC以及Druid数据库连接池等，在实际SpringBoot项目中会在application.yml配置文件中配置好数据库ip和端口信息，以及数据库连接池的信息，程序中还会用到MyBatis等ORM框架间接地与MySQL数据库交互；

  • Server层：包括连接器、查询缓存、分析器、优化器、执行器、MySQL管理系统等，具体的模块在下面介绍；
  • 存储引擎层：负责从磁盘文件中存储和读取数据，是真正与底层物理文件打交道的组件。存储引擎被设计成可拔插的插件形式，常见的存储引擎有InnoDB、MyIASM等；
  • 物理文件层：真正存储MySQL数据库中表数据的地方，以及对应的日志。具体的MySQL中每张表以及对应的元数据是如何在Linux文件系统上存储的。

    这一部分可以参考我这篇文章的第一节：MySQL宏观知识点

2.3 MySQL查询过程

我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。
MySQL查询过程：

  1. 客户端向MySQL服务端发送一个查询语句的请求；
  2. MySQL服务端的Service层先进行处理，首先是连接器，建立客户端与服务端之间的连接，并鉴权；
  3. MySQL服务端会先查询缓存，如果缓存命中，则直接返回缓存中数据页中的数据结果；否则进入下一阶段；
  4. 分析器对sql文本进行解析，生成解析树；
  5. 优化器对解析树进行优化，比如调整where查询条件的顺序与索引列一致，生成一个执行计划；
  6. 执行器基于优化器生成的执行计划，调用存储引擎底层的api来执行，并将结果返回给客户端。

如下图所示：
image.png

3、非SQL语句的优化

3.1 Buffer Pool

如果MySQL实例部署的服务器内存空间比较大，我们可以将Buffer Pool的实例个数和每个Buffer Pool实例大小都调大，这样可以在内存中缓存更多的数据页，减少内存和磁盘的IO次数，来提高查询效率。

3.2 网络带宽

由于应用层与数据库层交互需要进行网络通信，即来自应用层的查询请求和来自数据库层返回的数据都要经过网络传输，因此保证高效的网络传输也是保证高效sql查询的重要因素。此外，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT * 以及加上LIMIT限制的原因之一。

3.3 查询缓存

注意：MySQL 8.0取消了查询缓存这一设置。
查询缓存就是在2.2节MySQL逻辑架构中的Server层做的，一般默认查询缓存功能是关闭的，如果需要打开，可以修改MySQL的这个配置参数query_cache_type ，query_cache_type参数可能的取值有三个：

值 含义
OFF 或 0 查询缓存功能关闭
ON 或 1 查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定 SQL_NO_CACHE，不予缓存
DEMAND 或 2 查询缓存功能按需进行，显式指定 SQL_CACHE 的SELECT语句才会缓存；其它均不予缓存

MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。
既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

  1. 任何的查询语句在开始之前都必须经过检查是否缓存命中，即使这条SQL语句永远不会命中缓存；
  2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗。

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

  1. SELECT SQL_CACHE ...

4、SQL语句的优化

sql语句的查询是成本最低、效果提升最明显的一种方式。具体优化的sql语句可以分为三类：

  1. 查询语句：
    1. 尽量使用索引，避免全表扫描；
    2. 其他非索引方式的查询语句优化。
  2. 增、删、改语句；
  3. 建表语句。

    4.1 查询语句

    所谓的八二定理，大部分的业务场景都是读请求，因此查询语句的优化也是重点。一提起查询语句的优化我们最容易也是效果提升最明显的方式就是恰当地使用索引，此外也有一些其他操作可以优化查询。

    4.1.1 索引

    就一句话：最大化地使用索引查询，尽可能避免全表扫描。但有些场景下我们以为查询语句使用到了索引，但实际检查执行计划却没有按预期使用索引而是走的全表扫描，这一点可以从执行计划里的type字段和possible_keys && key字段来确认。
    以下是基于索引这个维度的优化点，如下：

    4.1.1.1 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描

    ```sql — 不推荐 SELECT * FROM t WHERE username LIKE ‘%陈%’

— 推荐 SELECT * FROM t WHERE username LIKE ‘陈%’

  1. 说明：
  3. - 如果单表中数据量几千条，无需整花里胡哨的，直接用like '%xx%'即可；
  4. - 如果需求就是要求在查询条件前面模糊查询，尽量走索引覆盖，此时也是可以使用到索引的。
  5. <a name="lfvoz"></a>
  6. #### 4.1.1.2 尽量避免使用in和not in，可能会导致优化器选择全表扫描
  7. ```sql
  8. -- 不推荐
  9. SELECT * FROM t WHERE id IN (2,3)
  11. -- 推荐，如果是连续数值，可以用between代替
  12. SELECT * FROM t WHERE id BETWEEN 2 AND 3

当in或者not in的范围小时，还是会使用索引查询，但当范围较大查询超过阈值时，会放弃使用索引而直接全表扫描。而使用in或者not in很容易使范围扩大，因此不建议使用。

4.1.1.3 尽量避免使用or，可能会导致数据库引擎放弃索引进行全表扫描

  1. -- id列是主键索引，user_id列没加索引，整个查询会走全表扫描
  2. SELECT * FROM order WHERE id=780 OR user_id=12;

这里or是指：只要其中一个OR条件没有使用索引，其他字段有索引也不会使用。优化器基于成本分析后还是会选择全表扫描。

4.1.1.4 尽量避免进行null值的判断，可能会导致优化器进行全表扫描

  1. -- 不推荐
  2. SELECT * FROM t WHERE score IS NULL；
  4. -- 推荐，0是一个score列的default值
  5. SELECT * FROM t WHERE score = 0

如果列中数据可能为null，可以给列一个默认值，避免列中数据直接出现null。原因：MySQL难以优化可以为NULL的列的查询，它会使索引、索引统计更加复杂，且可NULL列需要更多的存储空间。

4.1.1.5 尽量避免查询条件中出现<>或者!=

查询条件中使用<>，很容易使返回的结果集占总数据量的比例超过一个阈值，超过阈值后会直接走全表扫描，如果不超过阈值，查询条件有索引的话还是可以走索引的。

4.1.1.6 尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致全表扫描

4.1.1.7 尽量避免where条件仅包含复合索引非前置列

这个其实就是查询时要遵循“最左匹配原则”才能使用上索引，所谓的“最左匹配原则是”：

  • 如果是多个列等值查询，查询条件用到的列必须依次是索引列的顺序，比如创建联合索引（a, b, c），想要索引生效，查询条件只能使用以下组合：a，ab，ac，abc；

  • 如果是范围查询时，索引只能用到索引列中最左边的列的范围查询。

    4.1.1.8 尽量使order by的列与where中的列一致，否则order by不会利用索引排序

    说明：

  • 只有当order by字段出现在where条件中时，才会利用索引排序而非文件排序，其他都不行；

  • 多个order by条件，有升序也有降序，也不会使用索引排序而走文件排序。

    4.1.2 其他（非索引）

    4.1.2.1 避免出现select * ，仅返回需要的列

    出于两点考虑：

  • 尽量返回有用的列的信息，减少网络中传输的数据量；

  • 尽量使用索引覆盖，如果*就不会用到索引覆盖的优化了。

    4.1.2.2 连接查询，小表作为驱动表，大表作为被驱动表

    当左连接查询时，尽量将小表放左边（驱动表），大表放右边（被驱动表），这样驱动表扇出的记录较少，再次查询被驱动表的次数也会少。

    4.1.2.3 分组查询中尽量用where子句代替having子句

    where是先过滤，再分组；having是先分组，再过滤。肯定是先进行过滤，过滤后的数据量会少一些，再进行后续分组联表查询的啥的效率会高一些。

    4.1.2.4 分页查询避免返回大量数据

    能分页查询的就走分页查询，避免从数据库中全量查询，再到业务代码中stream去过滤，这样既省了一些内存，有能减少查数据库返回的数据量（减少网络传输包大小）。

    4.1.3 分页查询优化

    关于分页查询的优化我单独在我这篇文章中总结：单表分页查询优化

    4.2 增、删、改语句

    4.2.1 批量删除优化

  1. 多个删除语句尽量整合成一个批量删除语句，尽量仅通过一个数据库链接删除多条数据，数据库链接的建立耗资源；
  2. 如果表中有索引，删除大批量数据时会很慢；可以在删除记录前先删除索引，然后再删除记录，删除完成后再重新建立索引，此时数据库中记录相对较少，创建索引不会花太多时间。

具体数据信息参考：大量删除mysql优化_MySQL 数据库删除大批量数据的优化

4.2.2 清除表数据选择truncate而非delete

当我们需要清除表中的所有记录时，选择truncate语句而非delete。用法如下：

  1. TRUNCATE 表名

truncate是DDL语句，为了实现高性能，它绕过了删除数据的DML方法，因此truncate语句不能回滚。

truncate、drop和delete的区别：

  1. truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚；
  2. truncate只能作用于表；delete，drop可作用于表、视图等；
  3. truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；drop会删除表的结构及其所依赖的约束、索引等；
  4. truncate会重置表的自增值；delete不会；
  5. truncate不会激活与表有关的删除触发器；delete可以；
  6. truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。

MySQL中DDL语句和DML语句的概念：