MySQL优化（四）：性能优化总结

淡淡的烟草味﹌ 2023-02-23 07:47 254阅读 0赞

### 目录 ###

*  一、优化思路
 *  二、连接——配置优化
 *   *  服务端
     *  客户端
 *  三、缓存——架构优化
 *   *  3.1 缓存
     *  3.2 主从复制
     *  3.3 分库分表
 *  四、优化器——SQL 语句分析与优化
 *   *  4.1 慢查询日志 slow query log
     *   *  4.1.1 打开慢日志开关
         *  4.1.2 慢日志分析
     *  4.2 SHOW PROFILE
     *   *  4.2.1 查看是否开启
         *  4.2.2 查看 profile 统计
         *  4.2.3 其他系统命令
     *  4.3 EXPLAIN 执行计划
     *   *  4.3.1 id
         *  4.3.2 select type 查询类型
         *  4.3.3 type 连接类型
         *  4.3.4 possible\_key、 key
         *  4.3.5 key\_len
         *  4.3.6 key\_len
         *  4.3.7 filtered\`在这里插入代码片\`
         *  4.3.8 ref
         *  4.3.9 Extra
         *  4.3.5 小结
     *  4.4 SQL 与索引优化
 *  五、存储引擎
 *   *  5.1 存储引擎的选择
     *  5.2 字段定义
     *   *  5.2.1 整数类型
         *  5.2.2 字符类型
         *  5.2.3 不要用外键、 触发器、 视图
         *  5.2.4 大文件存储
         *  5.2.5 表拆分或字段冗余
 *  六、总结：优化体系

# 一、优化思路 #

作为架构师或者开发人员，说到数据库性能优化，你的思路是什么样的？  
或者具体一点，如果在面试的时候遇到这个问题：你会从哪些维度来优化数据库，你会怎么回答？

我们说到性能调优，大部分时候想要实现的目标是让我们的查询更快。

一个查询的动作又是由很多个环节组成的，每个环节都会消耗时间，我们在 [SQL 语句的  
执行流程][SQL _]已经分析过了。

我们要减少查询所消耗的时间，就要从每一个环节入手。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70]

# 二、连接——配置优化 #

第一个环节是客户端连接到服务端。连接这一块有可能会出现什么样的性能问题？  
有可能是服务端**连接数不够**导致应用程序获取不到连接。  
比如报了一个 Mysql: error1040: Too many connections 的错误。

我们可以从两个方面来解决连接数不够的问题：

## 服务端 ##

从服务端来说，我们可以增加服务端的可用连接数。

如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以：  
（1）修改配置参数,增加 可用连接数  ，即修改 max\_connections 的大小：

show variables like 'max_connections'; -- 修改最大连接数，当有多个应用连接的时候

（2）或者，及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间 都是 28800 秒，8 小时，我们可以把这个值调小。

show global variables like 'wait_timeout'; --及时释放不活动的连接， 注意不要释放连接池还在使用的连接

## 客户端 ##

从客户端来说，可以减少从服务端获取的连接数，如果我们不想每一次执行SQL 都创建一个新的连接，应该怎么做？

这个时候我们可以引入连接池，实现连接的重用。

我们可以在哪些层面使用连接池？**ORM 层面**（MyBatis 自带了一个连接池）；或者使用专用的连接池工具（阿里的 Druid、Spring Boot 2.x 版本默认的连接池 Hikari、老牌的 DBCP 和 C3P0）。

我们这里说到了从数据库**配置**的层面去优化数据库。不管是数据库本身的配置，还是安装这个数据库服务的操作系统的配置，对于配置进行优化，最终的目标都是为了更好地发挥硬件本身的性能，包括 CPU、内存、磁盘、网络。

在不同的硬件环境下，操作系统和 MySQL 的参数的配置是不同的，没有标准的配置。

之前的文章里也介绍了很多的 MySQL 和 InnoDB 的配置参数，包括各种开关和数值的配置，大多数参数都提供了一个默认值，比如默认的 buffer\_pool\_size，默认的页大小，InnoDB 并发线程数等等。

这些默认配置可以满足大部分情况的需求，除非有特殊的需求，在清楚参数的含义的情况下再去修改它。修改配置的工作一般由专业的 DBA 完成。

至于硬件本身的选择，比如使用固态硬盘，搭建磁盘阵列，选择特定的 CPU 型号这些，更不是我们开发人员关注的重点，这个我们就不做过多的介绍了。

除了合理设置服务端的**连接数**和客户端的**连接池**大小之外，我们还有哪些**减少客户端跟数据库服务端的连接**数的方案呢？

我们可以引入缓存！

# 三、缓存——架构优化 #

## 3.1 缓存 ##

在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题：一方面是会给数据库带来很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到影响。

我们可以用第三方的缓存服务来解决这个问题，例如 Redis。

![在这里插入图片描述][20200705223632790.png]  
运行独立的缓存服务，属于架构层面的优化。

为了减少单台数据库服务器的读写压力，在架构层面我们还可以做其他哪些优化措施？

## 3.2 主从复制 ##

如果单台数据库服务满足不了访问需求，那我们可以做数据库的**集群**方案。

集群必然会面临一个问题，就是不同的节点之间数据一致性的问题。如果同时读写多台数据库节点，怎么让所有的节点数据保持一致？

这个时候我们需要用到复制技术（replication），被复制的节点称为 master，复制的节点称为 slave。

![在这里插入图片描述][20200705223737223.png]  
主从复制是怎么实现的呢？在上文我们说过，更新语句会记录 **binlog**，它是一种逻辑日志。

有了这个 binlog，从服务器会获取主服务器的 binlog 文件，然后解析里面的 SQL语句，在从服务器上面执行一遍，保持主从的数据一致。

这里面涉及到三个线程，连接到 master 获取 binlog，并且解析 binlog 写入中继日志，这个线程叫做 I/O 线程。  
Master 节点上有一个 log dump 线程，是用来发送 binlog 给 slave 的。  
从库的 SQL 线程，是用来读取 relay log，把数据写入到数据库的。

这个是主从复制涉及到的三个线程。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 1]  
做了主从复制的方案之后，我们只把数据写入 master 节点，而读的请求可以分担到slave 节点。我们把这种方案叫做读写分离。

![在这里插入图片描述][20200705224158953.png]  
读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要特别注意主从**数据一致性**的问题。

我们在做了主从复制之后，如果单个 master 节点或者单张表存储的数据过大的时候，比如一张表有上亿的数据，单表的查询性能还是会下降，我们要进一步对单台数据库节点的数据进行拆分，这个就是分库分表。

## 3.3 分库分表 ##

垂直分库，减少并发压力。  
水平分表，解决存储瓶颈。

垂直分库的做法是，**把一个数据库按照业务拆分成不同的数据库**：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 3]  
水平分库分表的做法，把单张表的数据按照一定的规则分布到多个数据库。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 4]  
以上是架构层面的优化，可以用缓存，主从，分库分表。

# 四、优化器——SQL 语句分析与优化 #

优化器会对我们的 SQL 语句进行分析，生成执行计划。

问题：在我们做项目的时候，有时会收到 DBA 的邮件，里面列出了我们项目上几个耗时比较长的查询语句，让我们去优化，这些语句是从哪里来的呢？

我们的服务层每天执行了这么多 SQL 语句，它怎么知道哪些 SQL 语句比较慢呢？

第一步，我们要把 SQL 执行情况记录下来。

## 4.1 慢查询日志 slow query log ##

### 4.1.1 打开慢日志开关 ###

因为开启慢查询日志是有代价的（跟 bin log、optimizer-trace 一样），所以它默认是关闭的：

show variables like 'slow_query%';

除了这个开关，还有一个参数，控制执行超过多长时间的 SQL 才记录到慢日志，默认是 10 秒。

show variables like '%long_query%';

可以直接动态修改参数（重启后失效）。

set @@global.slow_query_log=1; -- 1 开启， 0 关闭， 重启后失效
    set @@global.long_query_time=3; -- mysql 默认的慢查询时间是 10 秒， 另开一个窗口后才会查到最新值
    
    show variables like '%long_query%';
    show variables like '%slow_query%';

或者修改配置文件 my.cnf。  
以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。

slow_query_log = ON
    long_query_time=2
    slow_query_log_file =/var/lib/mysql/localhost-slow.log

模拟慢查询：

select sleep(10);

查询 user\_innodb 表的 500 万数据（检查是不是没有索引）:

SELECT * FROM `user_innodb` where phone = '136';

### 4.1.2 慢日志分析 ###

*  日志内容

show global status like 'slow_queries'; -- 查看有多少慢查询
    show variables like '%slow_query%'; -- 获取慢日志目录

![在这里插入图片描述][20200705233557573.png]  
![在这里插入图片描述][20200705233609767.png]  
有了慢查询日志，怎么去分析统计呢？比如哪条 SQL 语句的出现的慢查询次数最多，平均每次执行了多久？人工肉眼分析显然不可能。

*  mysqldumpslow  
    [官方文档][Link 1]  
    MySQL 提供了 mysqldumpslow 的工具，在 MySQL 的 bin 目录下。

mysqldumpslow --help

例如：查询用时最多的 20 条慢 SQL：

mysqldumpslow -s t -t 20 -g 'select' /var/lib/mysql/localhost-slow.log

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 5]  
Count 代表这个 SQL 执行了多少次；  
Time 代表执行的时间，括号里面是累计时间；  
Lock 表示锁定的时间，括号是累计；  
Rows 表示返回的记录数，括号是累计。

除了慢查询日志之外，还有一个 SHOW PROFILE 工具可以使用。

## 4.2 SHOW PROFILE ##

[官方文档][Link 2]  
SHOW PROFILE 是谷歌高级架构师 Jeremy Cole 贡献给 MySQL 社区的，可以查看SQL 语句执行的时候使用的资源，比如 CPU、IO 的消耗情况。

在 SQL 中输入 help profile 可以得到详细的帮助信息。

### 4.2.1 查看是否开启 ###

select @@profiling;
    set @@profiling=1;

### 4.2.2 查看 profile 统计 ###

（命令最后带一个 s）

show profiles;

![在这里插入图片描述][20200706001709343.png]  
查看最后一个 SQL 的执行详细信息，从中找出耗时较多的环节（没有 s）。

show profile;

![在这里插入图片描述][20200706001743788.png]  
6.2E-5，小数点左移 5 位，代表 0.000062 秒。  
也可以根据 ID 查看执行详细信息，在后面带上 for query + ID。

show profile for query 1;

除了慢日志和 show profile，如果要分析出当前数据库中执行的慢的 SQL，还可以通过查看运行线程状态和服务器运行信息、存储引擎信息来分析。

### 4.2.3 其他系统命令 ###

*  show processlist 运行线程

show processlist;

这是很重要的一个命令，用于显示用户运行线程。可以根据 id 号 kill 线程。  
也可以查表，效果一样：（可以 group order by 了）

select * from information_schema.processlist;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 6]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 7]

*  show status 服务器运行状态

说明： [说明][Link 3]  
详细参数：[详细参数][Link 4]  
SHOW STATUS 用于查看 MySQL 服务器运行状态（重启后会清空），有 session和 global 两种作用域，格式：参数-值。  
可以用 like 带通配符过滤。

SHOW GLOBAL STATUS LIKE 'com_select'; -- 查看 select 次数

*  show engine 存储引擎运行信息

show engine 用来显示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件 IO 请求；buffer pool 统计信息。

例如：

show engine innodb status;

如果需要将监控信息输出到错误信息 error log 中（15 秒钟一次），可以开启输出。

show variables like 'innodb_status_output%';
    -- 开启输出：
    SET GLOBAL innodb_status_output=ON;
    SET GLOBAL innodb_status_output_locks=ON;

我们现在已经知道了这么多分析服务器状态、存储引擎状态、线程运行信息的命令，如果让你去写一个数据库监控系统，你会怎么做？

其实很多开源的慢查询日志监控工具，他们的原理其实也都是读取**系统的变量和状态**。

现在我们已经知道哪些 SQL 慢了，为什么慢呢？慢在哪里？

MySQL 提供了一个执行计划的工具（在架构中我们有讲到，优化器最终生成的就是一个执行计划），其他数据库，例如 Oracle 也有类似的功能。

通过 **EXPLAIN** 语句们可以模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是怎么处理一条 SQL 语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。

通过 **EXPLAIN** ，MySQL 5.6.3 以前只能分析 SELECT，MySQL5.6.3 以后就可以分析update、delete、insert 了。

## 4.3 EXPLAIN 执行计划 ##

[官方文档][Link 5]  
我们先创建三张表。一张课程表，一张老师表，一张老师联系方式表（没有任何索引）。

-- 课程表
    DROP TABLE IF EXISTS course;
    CREATE TABLE `course` (
     `cid` int(3) DEFAULT NULL,
     `cname` varchar(20) DEFAULT NULL,
     `tid` int(3) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- 教师表
    DROP TABLE IF EXISTS teacher;
    CREATE TABLE `teacher` (
     `tid` int(3) DEFAULT NULL,
     `tname` varchar(20) DEFAULT NULL,
     `tcid` int(3) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    -- 教师联系方式表
    DROP TABLE IF EXISTS teacher_contact;
    CREATE TABLE `teacher_contact` (
     `tcid` int(3) DEFAULT NULL,
     `phone` varchar(200) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    INSERT INTO `course` VALUES ('1', 'mysql', '1');
    INSERT INTO `course` VALUES ('2', 'jvm', '1');
    INSERT INTO `course` VALUES ('3', 'juc', '2');
    INSERT INTO `course` VALUES ('4', 'spring', '3');
    
    INSERT INTO `teacher` VALUES ('1', 'qingshan', '1');
    INSERT INTO `teacher` VALUES ('2', 'jack', '2');
    INSERT INTO `teacher` VALUES ('3', 'mic', '3');
    
    INSERT INTO `teacher_contact` VALUES ('1', '13688888888');
    INSERT INTO `teacher_contact` VALUES ('2', '18166669999');
    INSERT INTO `teacher_contact` VALUES ('3', '17722225555');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 8]  
explain 的结果有很多字段，我们详细地分析一下。  
先确认一下环境：

select version();
    show variables like '%engine%';

![在这里插入图片描述][20200706160617245.png]

### 4.3.1 id ###

id 是查询序列编号。

*  id值不同  
    id 值不同的时候，先查询 id 值大的（**先大后小**）。

-- 查询mysql课程的老师手机号
    EXPLAIN SELECT tc.phone
    	FROM teacher_contact tc WHERE tcid = (
    	SELECT tcid FROM teacher t WHERE t.tid = (
    	SELECT c.tid FROM course c WHERE c.cname = 'mysql'
    	)
    );

查询顺序：course c——teacher t——teacher\_contact tc。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 9]  
先查课程表，再查老师表，最后查老师联系方式表。子查询只能以这种方式进行，只有拿到内层的结果之后才能进行外层的查询。

*  id值相同（从上往下）

-- 查询课程 ID 为 2， 或者联系表 ID 为 3 的老师
    EXPLAIN
    SELECT t.tname,c.cname,tc.phone
    FROM teacher t, course c, teacher_contact tc
    WHERE t.tid = c.tid
    AND t.tcid = tc.tcid
    AND (c.cid = 2
    OR tc.tcid = 3);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 10]  
id 值相同时，表的查询顺序是从上往下顺序执行。例如这次查询的 id 都是 1，查询的顺序是 teacher t（3 条）——course c（4 条）——teacher\_contact tc（3 条）。

*  既有相同也有不同  
    **如果 ID 有相同也有不同，就是 ID 不同的先大后小，ID 相同的从上往下。**

### 4.3.2 select type 查询类型 ###

这里并没有列举全部（其它还有：DEPENDENT UNION、DEPENDENT SUBQUERY、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION）。

下面列举了一些常见的查询类型：

*  SIMPLE  
    简单查询，不包含子查询，不包含关联查询 union。

EXPLAIN SELECT * FROM teacher;

![在这里插入图片描述][20200706164000913.png]  
再看一个包含子查询的案例：

-- 查询 mysql 课程的老师手机号
    EXPLAIN SELECT tc.phone
    FROM teacher_contact tc WHERE tcid = (
    SELECT tcid FROM teacher t WHERE t.tid = (
    SELECT c.tid FROM course c WHERE c.cname = 'mysql'
    ))
    ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 11]

*  PRIMARY  
    子查询 SQL 语句中的主查询 ，也就是最外面的那层查询。
 *  SUBQUERY  
    子查询中所有的 内层查询 都是 SUBQUERY 类型的。
 *  DERIVED  
    衍生查询，表示在得到最终查询结果之前会用到**临时表**。例如：

-- 查询 ID 为 1 或 2 的老师教授的课程
    EXPLAIN SELECT cr.cname
    FROM (
    SELECT * FROM course WHERE tid = 1
    UNION
    SELECT * FROM course WHERE tid = 2
    ) cr;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 12]  
对于关联查询，先执行右边的 table（UNION），再执行左边的 table，类型是 DERIVED。

*  UNION  
    用到了 UNION 查询。同上例。
 *  UNION RESULT  
    主要是显示哪些表之间存在 UNION 查询。<union2,3>代表 id=2 和 id=3 的查询存在 UNION。同上例。

### 4.3.3 type 连接类型 ###

[官方文档][Link 5]

type意味着类型，这里的type官方全称是“join type”，意思是“连接类型”,这样很容易给人一种错觉觉得必须需要俩个表以上才有连接类型。事实上这里的连接类型并非字面那样的狭隘，它更确切的说是一种 数据库引擎查找表的一种方式，在《高性能mysql》一书中作者更是觉得称呼它为**访问类型**更贴切一些。

mysql5.7中，type的类型达到了14种之多，这里只记录和理解最重要且经常遇见的六种类型，它们分别是system > const > eq\_ref > ref > range > index > all。从左到右，它们的效率依次是降低的。

撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠左（下面的排序），但实际运用中还是要综合考虑各个方面的。

这 里 并 没 有 列 举 全 部 （ 其 他 ： fulltext 、 ref\_or\_null 、index\_merger 、unique\_subquery、index\_subquery）。  
以上访问类型除了 all，都能用到索引。

*  **all**

这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。

为什么这么说呢？因为all是一种非常暴力和原始的查找方法，非常的耗时而且低效。用all去查找数据就好比这样的一个情形：S学校有俩万人，我告诉你你给我找到小明，然后你怎么做呢！你当然是把全校俩万人挨个找一遍，即使你很幸运第一个人便找到了小明，但是你仍然不能停下，因为你无法确认是否有另外一个小明存在，直到你把俩万人找完为止。所以，基本所有情况，我们都要避免这样类型的查找，除非你不得不这样做。

如果没有索引或者没有用到索引，type 就是 ALL。

*  **index**

这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。

*  **range**

range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是**出现了range，则一定是基于索引**的。同时除了显而易见的between，and以及’>’,’<'外，in和or也是索引范围扫描。

*  **ref**

出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

例如：使用 tcid 上的普通索引查询：

explain SELECT * FROM teacher where tcid = 3;

![在这里插入图片描述][20200707135002830.png]

*  eq\_ref  
    通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果。一般是唯一性索引的查询（UNIQUE 或 PRIMARY KEY）。

ref\_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。

eq\_ref 是除 const 之外最好的访问类型。  
先删除 teacher 表中多余的数据，teacher\_contact 有 3 条数据，teacher 表有 3条数据。

DELETE FROM teacher where tid in (4,5,6);
    commit;
    
    -- 备份
    INSERT INTO `teacher` VALUES (4, 'james', 4);
    INSERT INTO `teacher` VALUES (5, 'tom', 5);
    INSERT INTO `teacher` VALUES (6, 'seven', 6);
    commit;

为 teacher\_contact 表的 tcid（第一个字段）创建主键索引。

-- ALTER TABLE teacher_contact DROP PRIMARY KEY;
    ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);

为 teacher 表的 tcid（第三个字段）创建普通索引。

-- ALTER TABLE teacher DROP INDEX idx_tcid;
    ALTER TABLE teacher ADD INDEX idx_tcid (tcid);

执行以下 SQL 语句：

select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;

此时的执行计划（teacher\_contact 表是 eq\_ref）：  
![在这里插入图片描述][20200707140314562.png]

*  **const**

通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

DROP TABLE IF EXISTS single_data;
    CREATE TABLE single_data(
    id int(3) PRIMARY KEY,
    content varchar(20)
    );
    insert into single_data values(1,'a');
    EXPLAIN SELECT * FROM single_data a where id = 1;

![在这里插入图片描述][20200706170323275.png]

*  system  
    system 是 const 的一种特例，只有一行满足条件。例如：只有一条数据的系统表。

EXPLAIN SELECT * FROM mysql.proxies_priv;

![在这里插入图片描述][20200706170445881.png]

### 4.3.4 possible\_key、 key ###

可能用到的索引和实际用到的索引。如果是 NULL 就代表没有用到索引。

possible\_key 可以有一个或者多个，可能用到索引不代表一定用到索引。

反过来，possible\_key 为空，key 可能有值吗？

试验：  
表上创建联合索引：

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
    ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);

查看执行计划（改成 select name 也能用到索引）：

explain select phone from user_innodb where phone='126';

![在这里插入图片描述][20200707141450959.png]  
结论：是有可能的（这里是覆盖索引的情况）。

目的：如果通过分析发现没有用到索引，就要检查 SQL 或者创建索引。

### 4.3.5 key\_len ###

索引的长度（使用的字节数）。跟索引字段的类型、长度有关。

### 4.3.6 key\_len ###

MySQL 认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。

### 4.3.7 filtered`在这里插入代码片` ###

这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比。

### 4.3.8 ref ###

使用哪个列或者常数和索引一起从表中筛选数据。

### 4.3.9 Extra ###

执行计划给出的额外的信息说明。

*  using index  
    用到了覆盖索引，不需要回表。

EXPLAIN SELECT tid FROM teacher ;

*  using where  
    使用了 where 过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在 server 层进行过滤（跟是否使用索引没有关系）。

EXPLAIN select * from user_innodb where phone ='13866667777';

![在这里插入图片描述][20200709001549228.png]

*  using filesort  
    在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序。（跟磁盘或文件没有关系）。**需要优化**。  
    （复合索引的前提）

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
    ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
    EXPLAIN select * from user_innodb where name ='青山' order by id;

![在这里插入图片描述][20200709001737946.png]

*  using temporary  
    用到了临时表。例如（以下不是全部的情况）

1、distinct 非索引列

EXPLAIN select DISTINCT(tid) from teacher t;

2、group by 非索引列

EXPLAIN select tname from teacher group by tname;

3、使用 join 的时候，group 任意列

EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid;

这种情况也需要优化，例如创建复合索引。

### 4.3.5 小结 ###

通过模拟优化器执行 SQL 查询语句的过程，可以知道 MySQL 是怎么处理一条 SQL 语句的。  
通过这种方式我们可以分析语句或者表的性能瓶颈。  
分析出问题之后，就是对 SQL 语句的具体优化。

## 4.4 SQL 与索引优化 ##

SQL 语句的优化的目标，就是使SQL执行时，大部分时候都能用到索引。

# 五、存储引擎 #

## 5.1 存储引擎的选择 ##

为不同的业务表选择不同的存储引擎，例如：查询插入操作多的业务表，用 MyISAM。临时数据用 Memory。常规的并发大更新多的表用 InnoDB。

## 5.2 字段定义 ##

原则：使用可以正确存储数据的最小数据类型。  
依据这个原则来为每一列选择合适的字段类型。

### 5.2.1 整数类型 ###

![在这里插入图片描述][20200709132908734.png]  
INT 有 8 种类型，不同的类型的最大存储范围是不一样的。

### 5.2.2 字符类型 ###

变长情况下，varchar 更节省空间，但是对于 varchar 字段，需要一个字节来记录长度。  
固定长度的用 char，不要用 varchar。

### 5.2.3 不要用外键、 触发器、 视图 ###

降低了可读性；  
影响数据库性能，应该把把计算的事情交给程序，数据库专心做存储；  
数据的完整性应该在程序中检查。

### 5.2.4 大文件存储 ###

不要用数据库存储图片（比如 base64 编码）或者大文件；  
把文件放在 NAS 上，数据库只需要存储 URI（相对路径），在应用中配置 NAS 服务器地址。

### 5.2.5 表拆分或字段冗余 ###

将不常用的字段拆分出去，避免列数过多和数据量过大。  
比如在业务系统中，要记录所有接收和发送的消息，这个消息是 XML 格式的，用 blob 或者 text 存储，用来追踪和判断重复，可以建立一张表专门用来存储报文。

# 六、总结：优化体系 #

所以，如果在面试的时候再问到这个问题“你会从哪些维度来优化数据库”，你会怎么回答？  
可以从以下几个维度来回答：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 13]  
除了对于代码、SQL 语句、表定义、架构、配置优化之外，业务层面的优化也不能忽视。举两个例子：  
1）在某一年的双十一，为什么会做一个充值到余额宝和余额有奖金的活动，例如充300 送 50？  
因为使用余额或者余额宝付款是记录本地或者内部数据库，而使用银行卡付款，需要调用接口，操作内部数据库肯定更快。

2）在去年的双十一，为什么在凌晨禁止查询今天之外的账单？  
这是一种降级措施，用来保证当前最核心的业务。

3）最近几年的双十一，为什么提前个把星期就已经有双十一当天的价格了？预售分流。

在应用层面同样有很多其他的方案来优化，达到尽量减轻数据库的压力的目的，比如限流，或者引入 MQ 削峰，等等等等。

为什么同样用 MySQL，有的公司可以抗住百万千万级别的并发，而有的公司几百个并发都扛不住，关键在于怎么用。所以，用数据库慢，不代表数据库本身慢，有的时候还要往上层去优化。

当然，如果关系型数据库解决不了的问题，我们可能需要用到搜索引擎或者大数据的方案了，并不是所有的数据都要放到关系型数据库存储。

[SQL _]: https://blog.csdn.net/z714405489/article/details/106958033
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200705221834612.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[20200705223632790.png]: https://img-blog.csdnimg.cn/20200705223632790.png
[20200705223737223.png]: https://img-blog.csdnimg.cn/20200705223737223.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200705223853759.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[20200705224158953.png]: https://img-blog.csdnimg.cn/20200705224158953.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200705224456295.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200705224524881.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200705224707144.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[20200705233557573.png]: https://img-blog.csdnimg.cn/20200705233557573.png
[20200705233609767.png]: https://img-blog.csdnimg.cn/20200705233609767.png
[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200705233813882.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[Link 2]: https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
[20200706001709343.png]: https://img-blog.csdnimg.cn/20200706001709343.png
[20200706001743788.png]: https://img-blog.csdnimg.cn/20200706001743788.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200706145733704.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200706150041840.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[Link 3]: https://dev.mysql.com/doc/refman/5.7/en/show-status.html
[Link 4]: https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html
[Link 5]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200706161724724.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[20200706160617245.png]: https://img-blog.csdnimg.cn/20200706160617245.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200706162251261.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20200706163022133.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[20200706164000913.png]: https://img-blog.csdnimg.cn/20200706164000913.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20200706164440914.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 12]: https://img-blog.csdnimg.cn/2020070616500517.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70
[20200707135002830.png]: https://img-blog.csdnimg.cn/20200707135002830.png
[20200707140314562.png]: https://img-blog.csdnimg.cn/20200707140314562.png
[20200706170323275.png]: https://img-blog.csdnimg.cn/20200706170323275.png
[20200706170445881.png]: https://img-blog.csdnimg.cn/20200706170445881.png
[20200707141450959.png]: https://img-blog.csdnimg.cn/20200707141450959.png
[20200709001549228.png]: https://img-blog.csdnimg.cn/20200709001549228.png
[20200709001737946.png]: https://img-blog.csdnimg.cn/20200709001737946.png
[20200709132908734.png]: https://img-blog.csdnimg.cn/20200709132908734.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk_size_16_color_FFFFFF_t_70 13]: https://img-blog.csdnimg.cn/20200709133343162.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3o3MTQ0MDU0ODk=,size_16,color_FFFFFF,t_70