万字详解，解锁MySQL性能优化新姿势

浅浅的花香味﹌ 2024-03-25 10:49 98阅读 0赞

## 1 MySQL总体架构介绍 ##

### 1.1 MySQL总体架构介绍 ###

**MySQL是一个关系型数据库**

*  应用十分广泛
 *  在学习任何一门知识之前
 *  对其架构有一个概括性的了解是非常重要的
 *  比如索引、sql是在哪个地方执行的
 *  流程是什么样的
 *  今天我们就先来学习一下MySQL的总体架构

**总的来说：MySQL架构是一个客户端-服务器系统。**

![format_png][]

**MySQL主要包括以下几部分:**

**Server 层**：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

**存储引擎：** 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自己的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了

**连接器：** 身份认证和权限相关(登录 MySQL 的时候)。

**查询缓存:** 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）mysql的server层增加一层缓存模块，类似一个内存的**kv层，k是sql，value是结果**

**分析器:** 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。

**优化器：** 按照 MySQL 认为最优的方案去执行。

**执行器:** 执行语句，然后从存储引擎返回数据。

### 1.2 MySQL存储引擎介绍 ###

*  和大多数的数据库不同, MySQL中有一个存储引擎的概念
 *  
 *  针对不同的存储需求可以选择最优的存储引擎。
 *  
 *  存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。
 *  
 *  存储引擎是基于表的，而不是基于库的,所以存储引擎也可被称为表类型。

MySQL提供了**插件式**的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

> MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等

可以通过指定 `show engines` ， 来查询当前数据库支持的存储引擎 ：

SHOW ENGINES;
    复制代码

![format_png 1][]

表含义:
      - support : 指服务器是否支持该存储引擎
      - transactions : 指存储引擎是否支持事务
      - XA : 指存储引擎是否支持分布式事务处理
      - Savepoints : 指存储引擎是否支持保存点（实现回滚到指定保存点）
    复制代码

*  查看MySQL数据库存储引擎配置
    
        SHOW VARIABLES LIKE '%storage_engine%';
        复制代码

![format_png 2][]

#### 1.2.1 如何更改数据库表引擎 ####

*  建表语句后面加入引擎赋值即可 ,命令举例如下 ,

CREATE TABLE t1(
    	id INT ,
        name VARCHAR(20)
    ) ENGINE = MyISAM;
    
    复制代码

*  修改已有的表引擎 , 命令举例如下 ,

ALTER TABLE t1 ENGINE = InnoDB;
    复制代码

#### 1.2.2 常用引擎及其特性对比 ####

*  常见的存储引擎 :
    
    **MyISAM存储引擎 :** 访问快,不支持事务和外键。表结构保存在.frm文件中，表数据保存在.MYD文件中，索引保存在.MYI文件中。
    
        [root@linux-141 itcast]# ll
        -rw-r-----. 1 mysql mysql       8630 9月  10 16:01 t_account_myisam.frm
        -rw-r-----. 1 mysql mysql         52 9月  10 16:06 t_account_myisam.MYD
        -rw-r-----. 1 mysql mysql       2048 9月  10 17:56 t_account_myisam.MYI
        [root@linux-141 itcast]#
        
        复制代码

![format_png 3][]

\*\*innoDB存储引擎(\*\*5.5版本开始默认) : 支持事务 ,占用磁盘空间大 ,支持并发控制。表结构保存在.frm文件中，如果是共享表空间，数据和索引保存在 innodb\_data\_home\_dir 和 innodb\_data\_file\_path定义的表空间中，可以是多个文件。如果是多表空间存储，每个表的数据和索引单独保存在 .ibd 中。

[root@linux-141 itcast]# ll
    -rw-r-----. 1 mysql mysql       8630 9月  10 16:02 t_account_innodb.frm
    -rw-r-----. 1 mysql mysql      98304 9月  14 15:50 t_account_innodb.ibd
    [root@linux-141 itcast]#
    复制代码

![format_png 4][]

**MEMORY存储引擎 :** 内存存储 , 速度快 ,不安全 ,适合小量快速访问的数据。表结构保存在.frm中。

![format_png 5][]

特性对比 :

<table> 
 <thead> 
  <tr> 
   <th>特点</th> 
   <th>InnoDB</th> 
   <th>MyISAM</th> 
   <th>MEMORY</th> 
   <th>MERGE</th> 
   <th>NDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>存储限制</td> 
   <td>64TB</td> 
   <td>有</td> 
   <td>有</td> 
   <td>没有</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td>事务安全</td> 
   <td>==支持==</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>锁机制</td> 
   <td>==行锁(适合高并发)==</td> 
   <td>==表锁==</td> 
   <td>表锁</td> 
   <td>表锁</td> 
   <td>行锁</td> 
  </tr> 
  <tr> 
   <td>B树索引</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>哈希索引</td> 
   <td></td> 
   <td></td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>全文索引</td> 
   <td>支持(5.6版本之后)</td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>集群索引</td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>数据索引</td> 
   <td>支持</td> 
   <td></td> 
   <td>支持</td> 
   <td></td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>索引缓存</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
  </tr> 
  <tr> 
   <td>数据可压缩</td> 
   <td></td> 
   <td>支持</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>空间使用</td> 
   <td>高</td> 
   <td>低</td> 
   <td>N/A</td> 
   <td>低</td> 
   <td>低</td> 
  </tr> 
  <tr> 
   <td>内存使用</td> 
   <td>高</td> 
   <td>低</td> 
   <td>中等</td> 
   <td>低</td> 
   <td>高</td> 
  </tr> 
  <tr> 
   <td>批量插入速度</td> 
   <td>低</td> 
   <td>高</td> 
   <td>高</td> 
   <td>高</td> 
   <td>高</td> 
  </tr> 
  <tr> 
   <td>支持外键</td> 
   <td>==支持==</td> 
   <td></td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

#### 1.2.3 如何选择不同类型的引擎 ####

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

以下是几种常用的存储引擎的使用环境。

*  InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包含更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
 *  MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
 *  MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供极快的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。

### 1.3 SQL的执行流程是什么样的 ###

*  客户端发送一条查询给服务器。
 *  服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
 *  服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
 *  MySQL根据优化器生成的执行计划，再调用存储引擎的API来执行查询。
 *  将结果返回给客户端。

![format_png 6][]

## 2 MySQL存储引擎调优 ##

### 2.1 MySQL**服务器硬件优化** ###

> tips
> 
> 硬件（cpu、内存等）相关
> 
> 了解即可

**关于提升硬件设备性能：**

例如选择尽量高频率的\*\*内存（\*\*频率不能高于主板的支持）、提升网络带宽、使用SSD高速磁盘、提升CPU性能等。

**CPU的选择:**

*  对于数据库并发比较高的场景，CPU的数量比频率重要。
 *  对于CPU密集型场景和频繁执行复杂SQL的场景，CPU的频率越高越好

**磁盘的选择**

影响数据库最大的性能问题就是磁盘I/O 为提高数据库的IOPS性能，可使用SSD或PCIE-SSD高速磁盘设备

**磁盘IO的优化**

可以用RAID来进行优化

常用RAID（磁盘阵列）级别：

RAID0：也称为条带，就是把多个磁盘链接成一个硬盘使用，这个级别IO最好 RAID1：也称为镜像，要求至少有两个磁盘，每组磁盘存储的数据相同 RAID5：也是把多个（最少3个）硬盘合并成一个逻辑盘使用，数据读写时会建立奇偶校验信息，并且奇偶校验信息和相对应的数据分别存储在不同的磁盘上。当RAID5的一个磁盘数据发生损坏后，利用剩下的数据和响应的奇偶校验信息去恢复被损坏的数据

**RAID1+0（建议使用）**：就是RAID0和RAID1的组合。同时具备两个级别的优缺点，一般建议数据库使用这个级别。

### 2.2 MySQL数据库配置优化 ###

> tips：
> 
> 以下为生产环境中最常用的DB参数配置

*  表示缓冲池字节大小，大的缓冲池可以减少磁盘IO次数。 innodb\_buffer\_pool\_size = 推荐值为物理内存的50%~80%。
 *  用来控制redo log buffer刷新到磁盘的策略。 innodb\_flush\_log\_at\_trx\_commit=1
    
        select @@innodb_flush_log_at_trx_commit;
        复制代码
    
        0 : 提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件中，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。
        1 : 提交事务的时候，立即把 redo log buffer 里的数据刷入磁盘文件中，只要事务提交成功，那么数据就必然在磁盘里了。
        2 : 提交事务的时候，把 redo log buffer日志写入磁盘文件对应的系统缓存，而不是直接进入磁盘文件，这时可能1秒后才会把系统缓存里的数据写入到磁盘文件。
        复制代码
 *  每提交1次事务就同步写到磁盘中，可以设置为1。 sync\_binlog=1
    
        0：默认值。事务提交后，将二进制日志从缓冲写入操作系统缓冲，但是不进行刷新操作（fsync()），此时只是写入了操作系统缓冲而没有刷新到磁盘，若操作系统宕机则会丢失部分二进制日志。
        1：事务提交后，将二进制文件写入磁盘并立即执行刷新操作，相当于是同步写入磁盘，不经过操作系统的缓存。
        N：每写N次操作系统缓冲就执行一次刷新操作。
        复制代码
 *  脏页占innodb\_buffer\_pool\_size的比例，触发刷脏页到磁盘。 推荐值为25%~50%。 innodb\_max\_dirty\_pages\_pct=30
    
        脏页：内存数据页和磁盘数据页上的内容不一致
        复制代码
 *  后台进程最大IO性能指标。 默认200，如果SSD，调整为5000~20000
    
    PCIE-SSD可调整为5w左右
    
    默认：innodb\_io\_capacity=200
 *  指定innodb共享表空间文件的大小。 innodb\_data\_file\_path ＝ ibdata:1G:autoextend：默认10M，
    
    一般设置为1GB
 *  慢查询日志的阈值设置，单位秒。 long\_query\_time=0.3
    
    合理设置区间0.1s~0.5s，
 *  mysql复制的形式，row为MySQL8.0的默认形式。 binlog\_format=row
    
    建议binlog的记录格式为row模式
    
        STATEMENT模式：每一条会修改数据的sql语句都会记录到binlog中。
        ROW模式：不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。
        MIXED模式：以上两种模式的混合使用。
        复制代码
 *  降低interactive\_timeout、wait\_timeout的值。
    
    交互等待时间和非交互等待时间，值一致，建议300~500s，默认8小时
    
        在用mysql客户端对数据库进行操作时，打开终端窗口，如果一段时间（8小时）没有操作，再次操作时，会报错：当前的连接已经断开，需要重新建立连接
        复制代码
 *  数据库最大连接数max\_connections=200
 *  过大，实例恢复时间长；过小，造成日志切换频繁。 innodb\_log\_file\_size=默认
    
    redo log空间大小
 *  全量日志建议关闭。 默认关闭general\_log=0
    
        开启 general log 将所有到达MySQL Server的SQL语句记录下来，general_Log文件就会产生很大的文件，建议关闭
        复制代码

### 2.3 Mysql中查询缓存优化 ###

> tips：
> 
> 在MySQL 8.0之后废弃这个功能
> 
> 原理：复杂、实用性不高
> 
> 作为了解即可

1） 查询缓存概述

开启Mysql的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。

2） 操作流程

> 回顾

![format_png 7][]

1.  客户端发送一条查询给服务器；
2.  服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
3.  服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
4.  MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
5.  将结果返回给客户端。

3） 查询缓存配置

1.  查看当前的MySQL数据库是否支持查询缓存：
    
        SHOW VARIABLES LIKE 'have_query_cache';	
        复制代码

![format_png 8][]

2. 查看当前MySQL是否开启了查询缓存 ：

SHOW VARIABLES LIKE 'query_cache_type';
    复制代码

![format_png 9][]

3. 查看查询缓存的占用大小 ：

SHOW VARIABLES LIKE 'query_cache_size';
    复制代码

![format_png 10][]

4. 查看查询缓存的状态变量：

SHOW STATUS LIKE 'Qcache%';
    复制代码

![format_png 11][]

各个变量的含义如下：

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Qcache_free_blocks</td> 
   <td>查询缓存中的可用内存块数</td> 
  </tr> 
  <tr> 
   <td>Qcache_free_memory</td> 
   <td>查询缓存的可用内存量</td> 
  </tr> 
  <tr> 
   <td>Qcache_hits</td> 
   <td>查询缓存命中数</td> 
  </tr> 
  <tr> 
   <td>Qcache_inserts</td> 
   <td>添加到查询缓存的查询数</td> 
  </tr> 
  <tr> 
   <td>Qcache_lowmen_prunes</td> 
   <td>由于内存不足而从查询缓存中删除的查询数</td> 
  </tr> 
  <tr> 
   <td>Qcache_not_cached</td> 
   <td>非缓存查询的数量（由于 query_cache_type 设置而无法缓存或未缓存）</td> 
  </tr> 
  <tr> 
   <td>Qcache_queries_in_cache</td> 
   <td>查询缓存中注册的查询数</td> 
  </tr> 
  <tr> 
   <td>Qcache_total_blocks</td> 
   <td>查询缓存中的块总数</td> 
  </tr> 
 </tbody> 
</table>

4） 开启查询缓存

MySQL的查询缓存默认是关闭的，需要手动配置参数 query\_cache\_type ， 来开启查询缓存。query\_cache\_type 该参数的可取值有三个 ：

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

在 my.cnf 配置中，增加以下配置 ：

#开启查询缓存
    query_cache_type=1
    
    复制代码

配置完毕之后，重启服务既可生效 ；

然后就可以在命令行执行SQL语句进行验证 ，执行一条比较耗时的SQL语句，然后再多执行几次，查看后面几次的执行时间；获取通过查看查询缓存的缓存命中数，来判定是否走查询缓存。

-- 执行SQL语句进行验证 查询缓存
    SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';
    -- 将SELECT修改为小写，发现缓存失效
    SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';
    复制代码

5） 查询缓存SELECT选项

可以在SELECT语句中指定两个与查询缓存相关的选项 ：

SQL\_CACHE : 如果查询结果是可缓存的，并且 query\_cache\_type 系统变量的值为ON或 DEMAND ，则缓存查询结果 。

SQL\_NO\_CACHE : 服务器不使用查询缓存。它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。

例子：

SELECT SQL_CACHE id, name FROM xxx;
    SELECT SQL_NO_CACHE id, name FROM xxx;
    复制代码

6） 查询缓存失效的情况

> tips
> 
> 需要注意的问题

1） SQL 语句不一致的情况， 要想命中查询缓存，查询的SQL语句必须一致。

SQL1 : select count(*) from xxx;
    SQL2 : Select count(*) from xxx;
    复制代码

2） 当查询语句中有一些不确定的值，则不会缓存。如 ： now() , current\_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。

SQL1 : select * from xxx where updatetime < now() limit 1;
    SQL2 : select user();
    SQL3 : select database();
    复制代码

3） 不使用任何表查询语句。

select 'A';
    复制代码

4） 查询 mysql， information\_schema或 performance\_schema 数据库中的表时，不会走查询缓存。

select * from information_schema.engines;
    复制代码

5） 在存储的函数，触发器或事件的主体内执行的查询。

6） 如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用`MERGE`映射到已更改表的表的查询。一个表可以被许多类型的语句，如被改变 INSERT， UPDATE， DELETE， TRUNCATE TABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE 。

将查询缓存关闭，因为后面还需要进行索引的验证，所以不希望走查询缓存

[root@linux-141 itcast]# vi /etc/my.cnf
    [root@linux-141 itcast]# service mysql restart
    Shutting down MySQL.... SUCCESS!
    Starting MySQL. SUCCESS!
    复制代码

### 2.4. Mysql内存管理及优化 ###

1）内存优化原则

1） 将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序预留足够内存。

2） MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。

3） 排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽。

2） MyISAM 内存优化

MyISAM 存储引擎使用 key\_buffer 缓存索引块，加速myisam索引的读写速度。对于myisam表的数据块，mysql没有特别的缓存机制，完全依赖于操作系统的IO缓存。

key\_buffer\_size

key\_buffer\_size决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率。可以在MySQL参数文件中设置key\_buffer\_size的值，对于一般MyISAM数据库，建议至少将1/4可用内存分配给key\_buffer\_size。

在my.cnf 中做如下配置：

key_buffer_size=512M
    复制代码

read\_buffer\_size

如果需要经常顺序扫描MyISAM 表，可以通过增大read\_buffer\_size的值来改善性能。但需要注意的是read\_buffer\_size是每个session独占的，如果默认值设置太大，就会造成内存浪费。

read\_rnd\_buffer\_size

对于需要做排序的MyISAM 表的查询，如带有order by子句的sql，适当增加 read\_rnd\_buffer\_size 的值，可以改善此类的sql性能。

但需要注意的是 read\_rnd\_buffer\_size 是每个session独占的，如果默认值设置太大，就会造成内存浪费。

3） InnoDB 内存优化

innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块。

innodb\_buffer\_pool\_size

该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb\_buffer\_pool\_size 的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高。

innodb_buffer_pool_size=512M
    复制代码

innodb\_log\_buffer\_size

决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb\_log\_buffer\_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。

innodb_log_buffer_size=10M
    复制代码

### 2.5. Mysql并发参数调整 ###

从实现上来说，MySQL Server 是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。在Mysql中，控制并发连接和线程的主要参数包括 max\_connections、back\_log、thread\_cache\_size、table\_open\_cahce。

1） max\_connections

> 最大可支持的连接数

采用max\_connections 控制允许连接到MySQL数据库的最大数量，默认值是 151。如果状态变量 connection\_errors\_max\_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这时可以考虑增大max\_connections 的值。

Mysql 最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。

2） back\_log

> 积压请求栈大小

back\_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max\_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back\_log，如果等待连接的数量超过back\_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ， 之后的版本默认为 50 + （max\_connections / 5）， 但最大不超过900。

如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back\_log 的值。

3） table\_open\_cache

> 执行线程可打开表缓存个数

该参数用来控制所有SQL语句执行线程可打开表缓存的数量， 而在执行SQL语句时，每一个SQL执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数 max\_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定 ：

max_connections x N ；
    复制代码

4） thread\_cache\_size

> 缓存客户服务线程的数量

为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数 thread\_cache\_size 可控制 MySQL 缓存客户服务线程的数量。

5）lock\_wait\_timeout

> innodb\_lock\_wait\_timeout

> 事务等待行锁的时间

该参数是用来设置InnoDB 事务等待行锁的时间，默认值是50ms ， 可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起； 对于后台运行的批量处理程序来说， 可以将行锁的等待时间调大， 以避免发生大的回滚操作。

## 3 常用慢查询分析工具 ##

引言
    在日常的业务开发中
    MySQL 出现慢查询是很常见的
    大部分情况下会分为两种情况
    1、业务增长太快
    2、要么就是SQL 写的太xx了
    所以
    对慢查询 SQL 进行分析和优化很重要
    其中 mysqldumpslow 是 MySQL 服务自带的一款很好的分析调优工具
    复制代码

### 3.1 调优工具mysqldumpslow ###

#### 3.1.1 调优工具常用设置 ####

**1、什么是MySQL 慢查询日志**

MySQL提供的一种慢查询日志记录，用来记录在MySQL查询中响应时间超过阀值的记录 具体指运行时间超过long\_query\_time值的SQL，则会被记录到慢查询日志中

**2、如何查看慢查询设置情况**

> 慢查询的时间阈值设置

show variables like '%slow_query_log%';
    复制代码

![format_png 12][]

**解释**

*  slow\_query\_log //是否开启，默认关闭，建议调优时才开启
 *  slow\_query\_log\_file //慢查询日志存放路径

**3、如何开启慢查询日志记录**

**1) 命令开启**

set global slow_query_log =1; //只对当前会话生效，重启失效
    复制代码

执行成功

再次执行

show variables like '%slow_query_log%';
    复制代码

先关闭客户端连接，再进行重新连接，即可看到设置生效

发现开启了mysqldumpslow调优工具

mysql> show variables like '%slow_query_log%';
    +---------------------+-------------------------------------------+
    | Variable_name       | Value                                     |
    +---------------------+-------------------------------------------+
    | slow_query_log      | ON                                        |
    | slow_query_log_file | /opt/mysql-5.7.28/data/linux-141-slow.log |
    +---------------------+-------------------------------------------+
    2 rows in set (0.02 sec)
    
    mysql> 
    复制代码

**2）配置文件开启**

vim my.cnf
    在[mysqld]下添加：
    slow_query_log = 1
    slow_query_log_file = /opt/mysql-5.7.28/data/linux-141-slow.log
    重启MySQL服务
     
    复制代码

修改并且重启后

发现开启了mysqldumpslow调优工具

**3）哪些 SQL 会记录到慢查询日志**

-- 查看阀值（大于），默认10s
    show variables like 'long_query_time%';
    复制代码

![format_png 13][]

> 默认值是10秒

4）**如何设置查询阀值**

*  命令设置

-- 设置慢查询阀值
    set global long_query_time = 1;
    复制代码

备注：另外开一个session或重新连接 ，才会看到变化

执行成功发发现慢sql的时间变成了1秒

![format_png 14][]

**配置文件设置**

vim my.cnf
    [mysqld]
    long_query_time = 1
    log_output = FILE
    重启MySQL服务
    复制代码

执行成功发发现慢sql的时间变成了1秒

![format_png 15][]

**5）如何把未使用索引的 SQL 记录写入慢查询日志**

-- 查看设置，默认关闭
    show variables like 'log_queries_not_using_indexes';
    复制代码

我们发现，未使用索引的sql默认是不记录到慢查询日志的

![format_png 16][]

开启配置

set global log_queries_not_using_indexes = on;
    复制代码

执行如下

![format_png 17][]

**6）模拟数据**

-- 睡眠2s再执行
    select sleep(2);
    -- 查看慢查询条数
    show global status like '%Slow_queries%';
    复制代码

我们发现，每执行一次select sleep(2)，之后，再通过show global status ...命令，他的值就会+1

![format_png 18][]

#### 3.1.2 调优工具常用命令 ####

**语法格式**

mysqldumpslow [ OPTS... ] [ LOGS... ] //命令行格式
    复制代码

常用到的格式组合

-s 表示按照何种方式排序
        c 访问次数
        l 锁定时间
        r 返回记录
        t 查询时间
        al 平均锁定时间
        ar 平均返回记录数
        at  平均查询时间
    -t 返回前面多少条数据
    -g 后边搭配一个正则匹配模式，大小写不敏感
    复制代码

**1、拿到慢日志路径**

show variables like '%slow_query_log%';
    复制代码

日志路径为：/opt/mysql-5.7.28/data/linux-141-slow.log

查看日志

**2、得到访问次数最多的10条SQL**

[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s r -t  10 /opt/mysql-5.7.28/data/linux-141-slow.log
    -bash: ./bin/mysqldumpslow: /usr/bin/perl: 坏的解释器: 没有那个文件或目录
    [root@linux-141 mysql-5.7.28]# yum -y install perl perl-devel
    [root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s r -t  10 /opt/mysql-5.7.28/data/linux-141-slow.log
    
    复制代码

**3、按照时间排序的前10条里面含有左连接的SQL**

[root@linux-141 mysql-5.7.28]# ./bin/mysqldumpslow -s t -t 10 -g "left join"  /opt/mysql-5.7.28/data/linux-141-slow.log
    
    Reading mysql slow query log from /opt/mysql-5.7.28/data/linux-141-slow.log
    Died at ./bin/mysqldumpslow line 167, <> chunk 28.
    [root@linux-141 mysql-5.7.28]#
    复制代码

#### 3.1.3 慢日志文件分析 ####

**1、查看慢查询日志**

[root@linux-141 mysql-5.7.28]# cat /opt/mysql-5.7.28/data/linux-141-slow.log
    /opt/mysql-5.7.28/bin/mysqld, Version: 5.7.28-log (MySQL Community Server (GPL)). started with:
    Tcp port: 3306  Unix socket: /tmp/mysql.sock
    Time                 Id Command    Argument
    # Time: 2021-09-15T01:40:31.342430Z
    # User@Host: root[root] @  [192.168.36.1]  Id:     2
    # Query_time: 2.000863  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
    use itcast;
    SET timestamp=1631670031;
    -- 睡眠2s再执行
    select sleep(2);
    # Time: 2021-09-15T01:50:32.130305Z
    # User@Host: root[root] @  [192.168.36.1]  Id:     2
    # Query_time: 3.001904  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
    SET timestamp=1631670632;
    select sleep(3);
    # Time: 2021-09-15T01:50:55.064372Z
    # User@Host: root[root] @  [192.168.36.1]  Id:     2
    # Query_time: 4.008082  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
    SET timestamp=1631670655;
    select sleep(4);
    # Time: 2021-09-15T01:51:01.343463Z
    # User@Host: root[root] @  [192.168.36.1]  Id:     2
    # Query_time: 5.007035  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
    SET timestamp=1631670661;
    select sleep(5);
    # Time: 2021-09-15T01:51:07.737834Z                                     	###### 执行SQL时间
    # User@Host: root[root] @  [192.168.36.1]  Id:     2						###### 执行SQL的主机信息
    # Query_time: 6.009129  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0	###### SQL的执行信息
    SET timestamp=1631670667;													###### SQL执行时间
    select sleep(6);															###### SQL内容
    [root@linux-141 mysql-5.7.28]#
    
    复制代码

**属性解释**

# Time: 2021-09-15T01:51:07.737834Z                                     	###### 执行SQL时间
    # User@Host: root[root] @  [192.168.36.1]  Id:     2						###### 执行SQL的主机信息
    # Query_time: 6.009129  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0	###### SQL的执行信息
    SET timestamp=1631670667;													###### SQL执行时间
    select sleep(6);															###### SQL内容
    复制代码

### 3.2 调优工具show profile ###

> tips：
> 
> show profile，它也是调优工具
> 
> 也是MySQL服务自带的分析调优工具
> 
> 不过这款更高级
> 
> 比较接近底层硬件参数的调优。

**简介：**

show profile是MySQL服务自带更高级的分析调优工具

比较接近底层硬件参数的调优

**1、查看show profile设置**

-- 默认关闭，保存近15次的运行结果
    show variables like 'profiling%';
    复制代码

![format_png 19][]

通过上面我们发现，show profile工具默认是关闭状态，15表示保存了近15次的运行结果。

**2、开启调优工具**

执行下面的命令开启

SET profiling = ON;
    复制代码

再次查看状态

show variables like 'profiling%';
    复制代码

![format_png 20][]

**3、查看最近15次的运行结果**

-- 查看最近15次的运行结果
    show profiles;
    
    -- 可以显示警告和报错的信息
    show warnings;
    
    -- 慢查询语句
    SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';
    复制代码

显示最近15次的运行结果

![format_png 21][]

**4、诊断运行的SQL**

接下来，我们一起诊断一下query id为23的慢查询

-- 语法
    SHOW PROFILE cpu,block io FOR QUERY query id;
    -- 示例
    SHOW PROFILE cpu,block io FOR QUERY 129;
    复制代码

开始执行

![format_png 22][]

解释：
    通过Status一列，可以看到整条SQL的运行过程
    1. starting //开始
    2. checking permissions //检查权限
    3. Opening tables //打开数据表
    4. init //初始化
    5. System lock //锁机制
    6. optimizing //优化器
    7. statistics //分析语法树
    8. prepareing //预准备
    9. executing //引擎执行开始
    10. end //引擎执行结束
    11. query end //查询结束
    12. closing tables //释放数据表
    13. freeing items //释放内存
    14. cleaning up //彻底清理
    复制代码

查看类型选项
    SHOW PROFILE...后面的列，即：SHOW PROFILE ALL, BLOCK IO, ... FOR QUERY 209;
    ALL //显示索引的开销信息
    BLOCK IO //显示块IO相关开销
    CONTEXT SWITCHES  //上下文切换相关开销
    CPU //显示CPU相关开销信息
    IPC //显示发送和接收相关开销信息
    MEMORY //显示内存相关开销信息
    PAGE FAULTS //显示页面错误相关开销信息
    SOURCE //显示和source_function,source_file,source_line相关的开销信息
    SWAPS //显示交换次数相关开销的信息
    复制代码

> 重要提示
> 
>     如出现以下一种或者几种情况，说明SQL执行性能极其低下，亟需优化
>     * converting HEAP to MyISAM  //查询结果太大，内存都不够用了往磁盘上搬了
>     * Creating tmp table //创建临时表：拷贝数据到临时表，用完再删
>     * Copying to tmp table on disk //把内存中临时表复制到磁盘，危险
>     * locked //出现死锁
>     复制代码

## 4 如何定位不合理的SQL ##

引言
    在应用的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多SQL语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的SQL语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化，本章将详细介绍在MySQL中优化SQL语句的方法。
    
    当面对一个有SQL性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题SQL并尽快解决问题。
    复制代码

### 4.1 如何查看SQL执行频率 ###

MySQL 客户端连接成功后，通过

-- 服务器状态信息
    show [session|global] status;
    复制代码

命令可以提供服务器状态信息。show \[session|global\] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的统计结果和 global 级（自数据库上次启动至今）的统计结果。

如果不写，默认使用参数是“session”。

下面的命令显示了当前 session 中所有统计参数的值：

show status like 'Com_______';
    复制代码

![format_png 23][]

show status like 'Innodb_rows_%';
    复制代码

![format_png 24][]

Com\_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Com_select</td> 
   <td>执行 select 操作的次数，一次查询只累加 1。</td> 
  </tr> 
  <tr> 
   <td>Com_insert</td> 
   <td>执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。</td> 
  </tr> 
  <tr> 
   <td>Com_update</td> 
   <td>执行 UPDATE 操作的次数。</td> 
  </tr> 
  <tr> 
   <td>Com_delete</td> 
   <td>执行 DELETE 操作的次数。</td> 
  </tr> 
  <tr> 
   <td>Innodb_rows_read</td> 
   <td>select 查询返回的行数。</td> 
  </tr> 
  <tr> 
   <td>Innodb_rows_inserted</td> 
   <td>执行 INSERT 操作插入的行数。</td> 
  </tr> 
  <tr> 
   <td>Innodb_rows_updated</td> 
   <td>执行 UPDATE 操作更新的行数。</td> 
  </tr> 
  <tr> 
   <td>Innodb_rows_deleted</td> 
   <td>执行 DELETE 操作删除的行数。</td> 
  </tr> 
  <tr> 
   <td>Connections</td> 
   <td>试图连接 MySQL 服务器的次数。</td> 
  </tr> 
  <tr> 
   <td>Uptime</td> 
   <td>服务器工作时间。</td> 
  </tr> 
  <tr> 
   <td>Slow_queries</td> 
   <td>慢查询的次数。</td> 
  </tr> 
 </tbody> 
</table>

Com\_\*\*\* : 这些参数对于所有存储引擎的表操作都会进行累计。

Innodb\_\*\*\* : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

### 4.2 如何定位低效率SQL ###

以下两种方式：

*  **慢查询日志（重要） :** 通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries\[=file\_name\]选项启动时，mysqld 写一个包含所有执行时间超过 long\_query\_time 秒的 SQL 语句的日志文件。
    
    > tips：
    > 
    > 关于慢查询SQL如何获取
    > 
    > 参看上个章节
 *  **show processlist （重要） :**
    
    慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题。
    
    可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

![format_png 25][]

属性字段解释
    1） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
    2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
    3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
    4） db列，显示这个进程目前连接的是哪个数据库
    5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
    6） time列，显示这个状态持续的时间，单位是秒
    7） state列，显示使用当前连接的sql语句的状态，很重要的列。
    	state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
    8） info列，显示这个sql语句，是判断问题语句的一个重要依据
    复制代码

### 4.3 使用explain分析执行计划 ###

-- explain 分析执行计划
    explain SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';
    复制代码

![format_png 26][]

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>输出结果集的表</td> 
  </tr> 
  <tr> 
   <td>partitions</td> 
   <td>匹配的分区</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>表示表的连接类型，性能由好到差的连接类型为( system ---&gt; const -----&gt; eq_ref ------&gt; ref -------&gt; ref_or_null----&gt; index_merge ---&gt; index_subquery -----&gt; range -----&gt; index ------&gt; all )</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>表示查询时，可能使用的索引</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>表示实际使用的索引</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>索引字段的长度</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>扫描行的数量</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>按表条件过滤的行百分比</td> 
  </tr> 
  <tr> 
   <td>extra</td> 
   <td>执行情况的说明和描述</td> 
  </tr> 
 </tbody> 
</table>

#### 4.3.1 环境准备 ####

![format_png 27][]

CREATE TABLE `t_role` (
      `id` varchar(32) NOT NULL,
      `role_name` varchar(255) DEFAULT NULL,
      `role_code` varchar(255) DEFAULT NULL,
      `description` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `unique_role_name` (`role_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `t_user` (
      `id` varchar(32) NOT NULL,
      `username` varchar(45) NOT NULL,
      `password` varchar(96) NOT NULL,
      `name` varchar(45) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `user_role` (
      `id` int(11) NOT NULL auto_increment ,
      `user_id` varchar(32) DEFAULT NULL,
      `role_id` varchar(32) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_role_user` (`role_id`,`user_id`),
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
    insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
    insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
    insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
    insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
    insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');
    
    INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
    INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
    INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
    INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
    INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');
    
    INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;
    复制代码

#### 4.3.2 explain 之 id ####

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。

id 情况有三种 ：

1） id 相同表示加载表的顺序是从上到下。

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;
    复制代码

![format_png 28][]

2） id 不同id值越大，优先级越高，越先被执行。

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))
    复制代码

![format_png 29][]

3） id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = (select role.id from t_user, user_role role where role.id = 10) ;
    复制代码

![format_png 30][]

#### 4.3.3 explain 之 select\_type ####

表示 SELECT 的类型，常见的取值，如下表所示：

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));
    复制代码

![format_png 31][]

<table> 
 <thead> 
  <tr> 
   <th>select_type</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIMPLE</td> 
   <td>简单的select查询，查询中不包含子查询或者UNION</td> 
  </tr> 
  <tr> 
   <td>PRIMARY</td> 
   <td>查询中若包含任何复杂的子查询，最外层查询标记为该标识</td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>在SELECT 或 WHERE 列表中包含了子查询</td> 
  </tr> 
  <tr> 
   <td>DERIVED</td> 
   <td>在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED</td> 
  </tr> 
  <tr> 
   <td>UNION RESULT</td> 
   <td>从UNION表获取结果的SELECT</td> 
  </tr> 
 </tbody> 
</table>

#### 4.3.4 explain 之 table ####

展示这一行的数据是关于哪一张表的

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));
    复制代码

![format_png 32][]

#### 4.3.5 explain 之 type ####

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));
    复制代码

![format_png 33][]

type 显示的是访问类型，是较为重要的一个指标，可取值为：

<table> 
 <thead> 
  <tr> 
   <th>type</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NULL</td> 
   <td>MySQL不访问任何表，索引，直接返回结果</td> 
  </tr> 
  <tr> 
   <td>system</td> 
   <td>表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现</td> 
  </tr> 
  <tr> 
   <td>const</td> 
   <td>表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。const会将 "主键" 或 "唯一" 索引的所有部分与常量值进行比较</td> 
  </tr> 
  <tr> 
   <td>eq_ref</td> 
   <td>类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）</td> 
  </tr> 
  <tr> 
   <td>range</td> 
   <td>只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， &lt; , &gt; , in 等操作。</td> 
  </tr> 
  <tr> 
   <td>index</td> 
   <td>index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。</td> 
  </tr> 
  <tr> 
   <td>all</td> 
   <td>将遍历全表以找到匹配的行</td> 
  </tr> 
 </tbody> 
</table>

结果值从最好到最坏依次是：

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    
    
    system > const > eq_ref > ref > range > index > ALL
    复制代码

一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。

#### 4.3.6 explain 之 key ####

possible_keys : 显示可能应用在这张表的索引， 一个或多个。 
    key ： 实际使用的索引， 如果为NULL， 则没有使用索引。
    key_len : 表示索引中使用的字节数。len=3*n+2（n为索引字段的长度）
    
    EXPLAIN select * from t_role where role_name = '超级管理员'; 
    select 255 * 3 + 2; -- role_name VARCHAR(255)
    复制代码

![format_png 34][]

#### 4.3.7 explain 之 rows ####

扫描行的数量。

#### 4.3.8 explain 之 extra ####

其他的额外的执行计划信息，在该列展示 。

EXPLAIN select u.username from t_user u order by u.username desc;
    复制代码

![format_png 35][]

<table> 
 <thead> 
  <tr> 
   <th>extra</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>using filesort</td> 
   <td>说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。</td> 
  </tr> 
  <tr> 
   <td>using temporary</td> 
   <td>使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低</td> 
  </tr> 
  <tr> 
   <td>using index</td> 
   <td>表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。</td> 
  </tr> 
 </tbody> 
</table>

## 5 如何合理使用索引加速 ##

> tips：
> 
> 500万条建表sql参照网盘sql脚本

[root@linux-141 bin]# ./mysql -u root -p itcast < product_list-5072825.sql
    复制代码

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

### 5.1 验证索引提升查询效率 ###

在我们准备的表结构product\_list 中， 一共存储了 500多万记录；

mysql> select count(1) from product_list;
    +----------+
    | count(1) |
    +----------+
    |  5072825 |
    +----------+
    1 row in set (1.71 sec)
    
    mysql> 
    复制代码

1） 根据ID查询

SELECT * FROM product_list WHERE id = 121926;
    复制代码

![format_png 36][]

查询速度很快， 接近0s ， 主要的原因是因为id为主键， 有索引；

2). 根据store\_name进行精确查询

执行用时4分钟

SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';
    复制代码

![format_png 37][]

查看SQL语句的执行计划 ：

explain SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';
    复制代码

![format_png 38][]

**处理方案 ， 针对store\_name字段， 创建索引 ：**

create index product_list_stname on product_list(store_name);
    复制代码

![format_png 39][]

索引创建完成之后，再次进行查询 ：

SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';
    复制代码

![format_png 40][]

通过explain ， 查看执行计划，执行SQL时使用了刚才创建的索引

-- 查看SQL语句的执行计划
    explain SELECT * FROM product_list WHERE store_name = '联想北达兴科专卖店';
    复制代码

![format_png 41][]

### 5.2 索引的使用 ###

#### 5.2.1 准备环境 ####

create table `tb_seller` (
    	`sellerid` varchar (100),
    	`name`  varchar (100) not null,
    	`nickname` varchar (50),
    	`password` varchar (60),
    	`status`  varchar (1) not null,
    	`address`  varchar (100) not null,
    	`createtime` datetime,
        primary key(`sellerid`)
    )engine=innodb default charset=utf8; 
    
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
    insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
    
    
    create index idx_seller_name_sta_addr on tb_seller(name,status,address);
    复制代码

#### 5.2.2 避免索引失效 ####

**组合索引(name,status,address)**

1) 全值匹配

对索引中所有列都指定具体值。

-- 全值匹配
    explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
    ken_len = 3 * N + 2;
    -- name varchar(100)  	==302
    -- status varchar(1)  	==5
    -- address varchar(100) ==302
    复制代码

![format_png 42][]

2) 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

匹配最左前缀法则，走索引：

explain select * from tb_seller  where name='小米科技';  
    复制代码

![format_png 43][]

违反最左前缀法则 ， 索引失效：

explain select * from tb_seller  where status='1';
    explain select * from tb_seller  where status='1'  and  address='北京市';
    复制代码

![format_png 44][]

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

explain select * from tb_seller  where name='小米科技'  and  address='北京市';
    复制代码

![format_png 45][]

3) 范围查询右边的列

-- 使用范围查询的情况，右边的列失效 
    explain select * from tb_seller  where name='小米科技' and status='1'  and  address='北京市';
    explain select * from tb_seller  where name='小米科技' and status>'1'  and  address='北京市';
    复制代码

![format_png 46][]

根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。

4) 禁止列运算

-- 不要在索引列上进行运算操作， 索引将失效。
    explain select * from tb_seller  where substring(name,3,2) ='科技';
    复制代码

![format_png 47][]

5) 字符串不加单引号

造成索引失效。

-- 字符串不加单引号,造成索引失效。
    explain select * from tb_seller  where name='科技' and status='0';
    explain select * from tb_seller  where name='科技' and status=0;
    复制代码

![format_png 48][]

由于，在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

6) 尽量使用覆盖索引

避免select \*

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select \* 。

-- 尽量使用覆盖索引
    explain select * from tb_seller  where name='科技' and status='0'  and  address='西安市';
    explain select name from tb_seller  where name='科技' and status='0'  and  address='西安市';
    explain select name ,status  from tb_seller  where name='科技' and status='0'  and  address='西安市';
    explain select name ,status,address  from tb_seller  where name='科技' and status='0'  and  address='西安市';
    复制代码

![format_png 49][]

如果查询列，超出索引列，也会降低性能。

explain select status,address ,password  from tb_seller  where name='科技' and status='0'  and  address='西安市';
    复制代码

TIP : 
    	
        using index ：使用覆盖索引的时候就会出现
    
        using where：在查找使用索引的情况下，需要回表去查询所需的数据
    
        using index condition：查找使用了索引，但是需要回表查询数据
    
        using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
    复制代码

7) 合理使用or条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00';	
    复制代码

![format_png 50][]

8) 合理使用like查询

以%开头的Like模糊查询，索引失效。

-- 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
    explain select * from tb_seller  where name like '黑马程序员%';
    explain select * from tb_seller  where name like '%黑马程序';
    explain select * from tb_seller  where name like '%黑马程序员%';
    复制代码

![format_png 51][]

解决方案 ： 通过覆盖索引来解决

explain select sellerid from tb_seller  where name like '%科技%';
    explain select sellerid,name from tb_seller  where name like '%科技%';
    explain select sellerid,name,status,address  from tb_seller  where name like '%科技%';
    复制代码

![format_png 52][]

9) 合理评估索引执行

如果MySQL评估使用索引比全表更慢，则不使用索引。

-- 如果MySQL评估使用索引比全表更慢，则不使用索引。
    create index idx_seller_addr on tb_seller(address);
    explain select * from tb_seller  where address='北京市';
    explain select * from tb_seller  where address='西安市';
    复制代码

![format_png 53][]

10) is NULL和 is NOT NULL

有时索引失效。

-- is  NULL和 is NOT NULL 
    explain select * from tb_seller  where name  is null;
    explain select * from tb_seller  where name  is not null;
    复制代码

![format_png 54][]

解决方案：把null值设置一个默认值

11) in和not in

in 走索引， not in 索引失效。

-- in 走索引， not in 索引失效。
    explain select * from tb_seller  where sellerid in('oppo','xiaomi','sina');
    explain select * from tb_seller  where sellerid not in  ('oppo','xiaomi','sina');
    复制代码

![format_png 55][]

12) 单列索引和复合索引

尽量使用复合索引，而少使用单列索引 。

创建复合索引

create index idx_name_sta_address on tb_seller(name, status, address);
    
    就相当于创建了三个索引 ： 
    	name
    	name + status
    	name + status + address
    复制代码

创建单列索引

create index idx_seller_name on tb_seller(name);
    create index idx_seller_status on tb_seller(status);
    create index idx_seller_address on tb_seller(address);
    复制代码

数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。

### 5.3 查看索引使用情况 ###

show status like 'Handler_read%';	
    show global status like 'Handler_read%';	
    复制代码

mysql> show status like 'Handler_read%';	
    +-----------------------+---------+
    | Variable_name         | Value   |
    +-----------------------+---------+
    | Handler_read_first    | 18      |
    | Handler_read_key      | 19      |
    | Handler_read_last     | 0       |
    | Handler_read_next     | 5072825 |
    | Handler_read_prev     | 0       |
    | Handler_read_rnd      | 0       |
    | Handler_read_rnd_next | 269     |
    +-----------------------+---------+
    7 rows in set (0.02 sec)
    
    mysql> 
    复制代码

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。
    
    Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
    
    Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
    
    Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
    
    Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
    
    Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
    复制代码

## 6 SQL优化的一些建议 ##

### 6.1 优化insert语句 ###

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

*  如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句
    
    这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。
    
    使得效率比分开执行的单个insert语句快。
    
    示例， 原始方式为：
    
        insert into xxx values(1,'Tom');
        insert into xxx values(2,'Cat');
        insert into xxx values(3,'Jerry');
        复制代码
    
    优化后的方案为 ：
    
        insert into xxx values(1,'Tom'),(2,'Cat')，(3,'Jerry');
        复制代码
 *  数据有序插入
    
        insert into tb_test values(4,'Tim');
        insert into tb_test values(1,'Tom');
        insert into tb_test values(3,'Jerry');
        insert into tb_test values(5,'Rose');
        insert into tb_test values(2,'Cat');
        复制代码
    
    优化后
    
        insert into tb_test values(1,'Tom');
        insert into tb_test values(2,'Cat');
        insert into tb_test values(3,'Jerry');
        insert into tb_test values(4,'Tim');
        insert into tb_test values(5,'Rose');
        复制代码

### 6.2 优化order by语句 ###

6.2.1 环境准备

CREATE TABLE `emp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(100) NOT NULL,
      `age` int(3) NOT NULL,
      `salary` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    
    insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
    insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
    
    create index idx_emp_age_salary on emp(age,salary);
    复制代码

6.2.2 两种排序方式

1). 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序

> tips
> 
> 不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

explain  select  *  from emp  order by  age  desc;
    explain  select  *  from emp  order by  age  asc;
    复制代码

![format_png 56][]

2). 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，

不需要额外排序，操作效率高。

explain  select  id from emp  order by  age  asc;
    explain  select  id,age  from emp  order by  age  asc;
    explain  select  id,age,salary  from emp  order by  age  asc;
    复制代码

![format_png 57][]

多字段排序

-- 多字段排序
    explain  select  id,age,salary  from emp  order by  age, salary;
    explain  select  id,age,salary  from emp  order by  age desc, salary desc;
     
    explain  select  id,age,salary  from emp  order by  salary desc, age desc;
    explain  select  id,age,salary  from emp  order by  age desc, salary asc ;
    复制代码

**总结：**

了解了MySQL的排序方式，优化目标就清晰了：

尽量减少额外的排序，通过索引直接返回有序数据。

where 条件和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同， 并且Order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort。

6.2.3 Filesort 的优化原理

> tips
> 
> 了解即可

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作。对于Filesort ， MySQL 有两种排序算法：

1） 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。

2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max\_length\_for\_sort\_data 的大小和Query语句取出的字段总大小， 来判定使用哪种排序算法，如果max\_length\_for\_sort\_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort\_buffer\_size 和 max\_length\_for\_sort\_data 系统变量，来增大排序区的大小，提高排序的效率。

show  variables like 'max_length_for_sort_data';
    show  variables  like 'sort_buffer_size';
    复制代码

### 6.3 优化group by 语句 ###

> tips
> 
> 延续使用6.2 优化order by语句中创建的表emp

**创建索引 （准备工作）：**

create index idx_emp_age_salary on emp(age,salary); 
    复制代码

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。

当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ：

drop index idx_emp_age_salary on emp;
    explain select age,count(*) from emp group by age;
    复制代码

![format_png 58][]

优化后

explain select age,count(*) from emp group by age order by null;
    复制代码

![format_png 59][]

从上面的例子可以看出，第一个SQL语句需要进行"filesort"，而第二个SQL由于order by null 不需要进行 "filesort"， 而上文提过Filesort往往非常耗费时间。

### 6.4 优化嵌套查询 ###

> tips
> 
> 延续使用4.3 使用explain分析执行计划中创建的表

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

示例 ，查找有角色的所有的用户信息 :

explain select * from t_user where id in (select user_id from user_role );
    复制代码

执行计划为 :

![format_png 60][]

优化后 :

explain select * from t_user u , user_role ur where u.id = ur.user_id;
    复制代码

![format_png 61][]

连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

### 6.5 使用SQL提示 ###

> tips：
> 
> 延续使用5.2.1 准备环境中创建的表

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

6.5.1 USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

create index idx_seller_name on tb_seller(name);
    
    explain select  *  from tb_seller  where name='小米科技'
    explain select  *  from tb_seller use index(idx_seller_name)  where name='小米科技'
    复制代码

![format_png 62][]

6.5.2 IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

explain select * from tb_seller ignore index(idx_seller_name_sta_addr) where name = '小米科技';
    复制代码

![format_png 63][]

6.5.3 FORCE INDEX

为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint 。

create index idx_seller_address on tb_seller(address);
    explain select * from tb_seller force index(idx_seller_address) where address = '北京市';
    复制代码

![format_png 64][]

至此，MySQL数据库调优就干完了。

[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/3b16d0b9653a4795b20fd90c183823b9.png
[format_png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/06128d21721f4c858ca4ed2128d4f8c9.png
[format_png 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/5848c4e9f8e44e2b9b79d6b60dbaa45c.png
[format_png 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/e1db9a86f64f48e5b480167b0c163ec3.png
[format_png 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/79eb636e06ee47daa95f9c4f149a513c.png
[format_png 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/941f06ff8ecd40ac94fac4c92d32fb0a.png
[format_png 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/2a7f46e9d7e345fe88d30ed448253a38.png
[format_png 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/d56db220993a43de82fa39f14bb02cd4.png
[format_png 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/20cfc8df09f54f12892f129a1c8e2a3f.png
[format_png 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/ff6f254226cb46b9991be35c977e84c1.png
[format_png 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/db5c76b0172c44f7b749f9c2269bf464.png
[format_png 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/92b8010ae480445483756e8bbad17023.png
[format_png 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/2f968878e3b04b5f8ff87a3b6b5b6344.png
[format_png 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/7b028721f44b469aa3d55af2f3e5ce09.png
[format_png 14]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/20a89275c84740fd903a6cdc4fa4d255.png
[format_png 15]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/318596b3cac64148bef60b60c6199b76.png
[format_png 16]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/c1db2bfdfa8441989bb28d11339504bb.png
[format_png 17]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/2da4de38320b4c41a5a5de031ffae95f.png
[format_png 18]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/02a1350f99ab4b6d8e1e904be89beef2.png
[format_png 19]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/f1d8542de6054c83bf0f48303ab58c93.png
[format_png 20]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/667d2973a9f543aba5c4f172373b2da3.png
[format_png 21]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/a288d50859d5480ab8c1f86e651149d0.png
[format_png 22]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/3d4d3cd3bb404391b446166649a4d9eb.png
[format_png 23]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/a4dafae1e3ce4fd9a3f21903121b37bb.png
[format_png 24]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/a22fa97693ea46b59ebed76508027ee9.png
[format_png 25]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/9a8f907e63634736ac495a7fd3aa2639.png
[format_png 26]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/345df2036d6b45bfa19858b02b192679.png
[format_png 27]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/cda0887901ca49b4bb7ef7f1f4b5571f.png
[format_png 28]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/3fbbdb9be534480f817fbc0e2dc78a1b.png
[format_png 29]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/56cc20f7698d4f09989e336e2f15b9b9.png
[format_png 30]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/472c9bbf4fbe4663bcf21f862a467aaf.png
[format_png 31]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/5dd72c673d8a440c9863bc08ceac09af.png
[format_png 32]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/cf4e19d7aaaa4792959ae8c8ab0f15b5.png
[format_png 33]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/2541db9486de431ca426803d3a251ee3.png
[format_png 34]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/916333ef82784e6f857f9113707da54d.png
[format_png 35]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/825c62501f6b4ebc9b6cc3e6f10a0383.png
[format_png 36]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/acfc0db66bf1483bbc5e3aa07e1baf04.png
[format_png 37]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/1d3f0a2f5c2c47d591bd34c801bb0e15.png
[format_png 38]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/1e93f03894d44ed381319ae30f74f040.png
[format_png 39]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/437c582c24334c29b46a450f8796ec7c.png
[format_png 40]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/cc317fbe8381433b95c938d07b78b17a.png
[format_png 41]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/cc1cd492602841b38b0011d67d4ff8f2.png
[format_png 42]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/59c7e2ba39fb4d7c8b6662a2de86020f.png
[format_png 43]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/4e97da9b939b45499084d39bfc962b9e.png
[format_png 44]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/fe9f5b78feb848e3ad91a8a0160bbbfb.png
[format_png 45]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/ddf98995ec3646e49ea87a573913dc84.png
[format_png 46]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/770b10153a1140348d13a4d1a70a8ab5.png
[format_png 47]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/dff7d01bd722417faafe35ef377baba5.png
[format_png 48]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/c30fead536f043c5b48a4b9231a65400.png
[format_png 49]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/e8cf840c99d543c4916305e6e81ab028.png
[format_png 50]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/c5d6f3c8868c4377a747a0403b446cbb.png
[format_png 51]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/ec18d7e4012c4a289a8a0ad03af0d42a.png
[format_png 52]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/5e915e6283de4ca9979c7df67b7f5d34.png
[format_png 53]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/16e16ef3f2ff48539d178fb9ef5e6943.png
[format_png 54]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/201606129e6e414e86c0260dd377534a.png
[format_png 55]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/0649fe52dce449e2a8d65179571380dc.png
[format_png 56]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/626d7d79f3b24e06b57b52fea2a7a4a6.png
[format_png 57]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/2af7791ecb5945718591474826cb0e52.png
[format_png 58]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/0455593ea6ae41f6a08b4b6790f60ca9.png
[format_png 59]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/4626023930d745408e3db656d9121573.png
[format_png 60]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/c68b9406b27c463780fd8ec330cfd6cd.png
[format_png 61]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/c511764ede794c0498e4b131092f00f2.png
[format_png 62]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/6b12f13f56ef484fa1ea7f913f6d6f19.png
[format_png 63]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/cee9472931964fdda1fa0e68a04d916f.png
[format_png 64]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/a882111e67644ae79bc591c99c30326e.png