Mysql训练营笔记 有用 看1

Mysql架构与内部模块

演示环境：

MySQL 5.7

存储引擎：InnoDB

一、一条查询SQL是如何执行的？

程序或者工具要操作数据库，第一步跟数据库建立连接。

1、通信协议

首先，MySQL 必须要运行一个服务，监听默认的端口(3306)。

通信协议

MySQL 支持多种通信协议。

第一个就是 TCP/IP 协议，编程语言的连接模块都是用 TCP 协议连接到 MySQL 服务器的，比如 mysql

connector-java-x.x.xx.jar。

第二种是 Unix Socket。比如我们在 Linux 服务器，不用通过网络协议，也可以连接到 MySQL 的服

务器，它需要用到服务器上的一个物理文件(mysql.sock)。

mysql -uroot -p123456

show variables like 'socket';

另外还有命名管道(Named Pipes)和内存共享(Share Memory)的方式。通信方式

第二个是通信方式。

MySQL 使用半双工的通信方式。

半双工意味着要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能

同时发生。

所以客户端发送 SQL 语句给服务端的时候，(在一次连接里面)数据是不能分成小块发送的，不管

你的 SQL 语句有多大，都是一次性发送。

如果发送给服务器的数据包过大，我们必须要调整 MySQL 服务器配置 max_allowed_packet 参数的值

(默认是 4M)。

另一方面，对于服务端来说，也是一次性发送所有的数据，不能因为你已经取到了想要的数据就中

断操作。

所以，我们一定要在程序里面避免不带 limit 的这种操作。连接方式

第三个是连接这一块。

MySQL 既支持短连接，也支持长连接。短连接就是操作完毕以后，马上 close 掉。长连接可以保持

打开，后面的程序访问的时候还可以使用这个连接。

长时间不活动的连接，MySQL 服务器会断开。

默认是 28800 秒，8 小时。

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_interactive_timeo

ut

MySQL 默认的最大连接数是 151 个(5.7 版本)，最大是 16384(2^14)。

show variables like 'max_connections';

查看 3306 端口当前连接数

使用 SHOW FULL PROCESSLIST;查看查询的执行状态。

一些常见的状态：

show global variables like 'wait_timeout'; (非交互式超时时间，如 JDBC 程序)

show global variables like 'interactive_timeout'; (交互式超时时间，如数据库工具)

netstat -an|grep 3306|wc -l状态

含义

Sleep

线程正在等待客户端，以向它发送一个新语句

Query

线程正在执行查询或往客户端发送数据

Locked

该查询被其它查询锁定

Copying to tmp

table

临时结果集合大于 tmp_table_size。线程把临时表从存储器内部格式改变为磁

盘模式，以节约存储器

Sending data

线程正在为 SELECT 语句处理行，同时正在向客户端发送数据

Sorting for

group

线程正在进行分类，以满足 GROUP BY 要求

Sorting for

order

线程正在进行分类，以满足 ORDER BY 要求

2、查询缓存(Query Cache)

MySQL 内部自带了一个缓存模块。默认是关闭的。主要是因为 MySQL 自带的缓存的应用场景有

限，第一个是它要求 SQL 语句必须一模一样。第二个是表里面任何一条数据发生变化的时候，这张表所

有缓存都会失效。

在 MySQL 5.8 中，查询缓存已经被移除了。

3、语法解析和预处理(Parser & Preprocessor)

下一步我们要做什么呢？

假如随便执行一个字符串 fkdljasklf ，服务器报了一个 1064 的错：

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your

MySQL server version for the right syntax to use near 'fkdljasklf' at line 1

服务器是怎么知道我输入的内容是错误的？

或者，当我输入了一个语法完全正确的 SQL，但是表名不存在，它是怎么发现的？

这个就是 MySQL 的 Parser 解析器和 Preprocessor 预处理模块。

这一步主要做的事情是对 SQL 语句进行词法和语法分析和语义的解析。

词法解析

词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。

比如一个简单的 SQL 语句：

select name from user where id = 1;

它会打碎成 8 个符号，记录每个符号是什么类型，从哪里开始到哪里结束。语法解析

第二步就是语法分析，语法分析会对 SQL 做一些语法检查，比如单引号有没有闭合，然后根据 MySQL

定义的语法规则，根据 SQL 语句生成一个数据结构。这个数据结构我们把它叫做解析树。

预处理器(Preprocessor)

如果表名错误，会在预处理器处理时报错。

它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名

字和别名，保证没有歧义。

4、查询优化(Query Optimizer)与查询执行计划

什么优化器？

问题：一条 SQL 语句是不是只有一种执行方式？或者说数据库最终执行的 SQL 是不是就是我们发

送

的 SQL？

这个答案是否定的。一条 SQL 语句是可以有很多种执行方式的。但是如果有这么多种执行方式，这

些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？

这个就是 MySQL 的查询优化器的模块(Optimizer)。

查询优化器的目的就是根据解析树生成不同的执行计划，然后选择一种最优的执行计划，MySQL 里

面使用的是基于开销(cost)的优化器，那种执行计划开销最小，就用哪种。

使用如下命令查看查询的开销：

show status like 'Last_query_cost';

--代表需要随机读取几个 4K 的数据页才能完成查找。如果我们想知道优化器是怎么工作的，它生成了几种执行计划，每种执行计划的 cost 是多少，应该怎么

做？

优化器是怎么得到执行计划的？

https://dev.mysql.com/doc/internals/en/optimizer-tracing.html

首先我们要启用优化器的追踪(默认是关闭的)：

注意开启这开关是会消耗性能的，因为它要把优化分析的结果写到表里面，所以不要轻易开启，或

者查看完之后关闭它(改成 off)。

接着我们执行一个 SQL 语句，优化器会生成执行计划：

这个时候优化器分析的过程已经记录到系统表里面了，我们可以查询：

expanded_query 是优化后的 SQL 语句。

considered_execution_plans 里面列出了所有的执行计划。

记得关掉它：

SHOW VARIABLES LIKE 'optimizer_trace';

set optimizer_trace="enabled=on";

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

select * from information_schema.optimizer_trace\G

set optimizer_trace="enabled=off";

• SHOW VARIABLES LIKE 'optimizer_trace';优化器可以做什么？

MySQL 的优化器能处理哪些优化类型呢？

比如：

1、当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表。

2、select * from user where a=1 and b=2 and c=3，如果 c=3 的结果有 100 条，b=2 的结果有

200 条， a=1 的结果有 300 条，你觉得会先执行哪个过滤？

3、如果条件里面存在一些恒等或者恒不等的等式，是不是可以移除。

4、查询数据，是不是能直接从索引里面取到值。

5、count()、min()、max()，比如是不是能从索引里面直接取到值。

6、其他。

优化器得到的结果

优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。

当然，这个执行计划是不是一定是最优的执行计划呢？不一定，因为 MySQL 也有可能覆盖不到所

有的执行计划。

MySQL 提供了一个执行计划的工具。我们在 SQL 语句前面加上 EXPLAIN，就可以看到执行计划的

信息。

5、存储引擎(Storage Engine)

我们的数据是放在哪里的？执行计划在哪里执行？是谁去执行？

存储引擎基本介绍

在关系型数据库里面，数据是放在表里面的。我们可以把这个表理解成 Excel 电子表格的形式。所

以我们的表在存储数据的同时，还要组织数据的存储结构，这个存储结构就是由我们的存储引擎决定

的，所以我们也可以把存储引擎叫做表类型。

在 MySQL 里面，支持多种存储引擎，他们是可以替换的，所以叫做插件式的存储引擎。为什么要

搞这么多存储引擎呢？一种还不够用吗？是因为我们在不同的业务场景中对数据操作的要求不同，这些

不同的存储引擎通过提供不同的存储机制、索引方式、锁定水平等功能，来满足我们的业务需求。

查看存储引擎

查看数据库表的存储引擎：

EXPLAIN select name from user where id=1;

show table status from `training`;在 MySQL 里面，我们创建的每一张表都可以指定它的存储引擎，它不是一个数据库只能使用一

个存储引擎。而且，创建表之后还可以修改存储引擎。

数据库存放数据的路径：

每个数据库有一个自己文件夹，以 trainning 数据库为例。

任何一个存储引擎都有一个 frm 文件，这个是表结构定义文件。

我们在数据库中建了三张表，使用了不同的存储引擎。

不同的存储引擎存放数据的方式不一样，产生的文件也不一样。

存储引擎比较

常见存储引擎

在 MySQL 5.5 版本之前，默认的存储引擎是 MyISAM，它是 MySQL 自带的。5.5 版本之后默认的

存储引擎改成了InnoDB，它是第三方公司为MySQL开发的。为什么要改呢？最主要的原因还是InnoDB

支持事务，支持行级别的锁，对于业务一致性要求高的场景来说更适合。

数据库支持的存储引擎

我们可以用这个命令查看数据库对存储引擎的支持情况：

其中有存储引擎的描述和对事务、XA 协议和 Savepoints 的支持。

show variables like 'datadir';

SHOW ENGINES ;官网对于存储引擎的介绍：

https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html

MyISAM(

3个文件)

These tables have a small footprint. Table-level locking limits the performance in read/write

workloads, so it is often used in read-only or read-mostly workloads in Web and data

warehousing configurations.

应用范围比较小。表级锁定限制了读/写的性能，因此在 Web 和数据仓库配置中，它通常用于只读或以

读为主的工作。

特点：

支持表级别的锁(插入和更新会锁表)。不支持事务。

拥有较高的插入(insert)和查询(select)速度。

存储了表的行数(count 速度更快)。

适合：只读之类的数据分析的项目。

InnoDB(2 个文件)

The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant)

storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect

user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle

style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB

stores user data in clustered indexes to reduce I/O for common queries based on primary keys.

To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.

mysql 5.7 中的默认存储引擎。InnoDB 是一个事务安全(与 ACID 兼容)的 MySQL 存储引擎，它

具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁(不升级为更粗粒度的锁)和 Oracle

风格的一致非锁读提高了多用户并发性和性能。InnoDB 将用户数据存储在聚集索引中，以减少基于 主

键的常见查询的 I/O。为了保持数据完整性，InnoDB 还支持外键引用完整性约束。

特点：

支持事务，支持外键，因此数据的完整性、一致性更高。

支持行级别的锁和表级别的锁。

支持读写并发，写不阻塞读。

特殊的索引存放方式，可以减少 IO，提升查询效率。

适合：经常更新的表，存在并发读写或者有事务处理的业务系统。

Memory(

1个文件)

Stores all data in RAM, for fast access in environments that require quick lookups of non

critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing;

InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep

most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge

distributed data sets.将所有数据存储在 RAM 中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被

称为堆引擎。其使用案例正在减少；InnoDB 及其缓冲池内存区域提供了一种通用、持久的方法来 将大

部分或所有数据保存在内存中，而 ndbcluster 为大型分布式数据集提供了快速的键值查找。

特点：

把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合 做临

时表。默认使用哈希索引。 将表中的数据存储到内存中。

CSV(

3个文件)

Its tables are really text files with comma-separated values. CSV tables let you import or dump

data in CSV format, to exchange data with scripts and applications that read and write that same

format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during

normal operation, and only use CSV tables during the import or export stage.

它的表实际上是带有逗号分隔值的文本文件。csv 表允许以 csv 格式导入或转储数据，以便与读写相同

格式的脚本和应用程序交换数据。因为 csv 表没有索引，所以通常在正常操作期间将数据保存在 innodb

表中，并且只在导入或导出阶段使用 csv 表。

特点：

不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之间导入导出。

Archive(2 个文件)

These compact, unindexed tables are intended for storing and retrieving large amounts of

seldom-referenced historical, archived, or security audit information.

这些紧凑的未索引表用于存储和检索大量很少引用的历史、存档或安全审计信息。

特点：

不支持索引，不支持 update delete。

6、执行引擎(Query Execution Engine)，返回结果

执行引擎，它利用存储引擎提供了相应的 API 来完成对存储引擎的操作。最后把数据返回给客户端，即

使没有结果也要返回。

二、MySQL 体系结构总结

架构分层

总体上，我们可以把 MySQL 分成三层。

模块详解

1.Connector：用来支持各种语言和 SQL 的交互，比如 PHP，Python，Java 的 JDBC

2.Management Serveices & Utilities：系统管理和控制工具，包括备份恢复、MySQL 复制、集群等等

3.Connection Pool：连接池，管理需要缓冲的资源，包括用户密码权限线程等等

4.SQL Interface：用来接收用户的 SQL 命令，返回用户需要的查询结果

5.Parser：用来解析 SQL 语句

6.Optimizer：查询优化器

7.Cache and Buffer：查询缓存，除了行记录的缓存之外，还有表缓存，Key 缓存，权限缓存等等。

8.Pluggable Storage Engines：插件式存储引擎，它提供 API 给服务层使用，跟具体的文件打交道。三、一条更新SQL 是如何执行的？

在数据库里面，我们说的 update 操作其实包括了更新、插入和删除。更新流程和查询流程有什么

不同呢？

基本流程也是一致的，也就是说，它也要经过解析器、优化器的处理，最后交给执行器。

区别就在于拿到符合条件的数据之后的操作。

首先，在 InnoDB 里面有个内存的缓冲池(buffer pool)。我们对数据的更新，不会每次都直接写

到磁盘上，因为 IO 的代价太大了，所以先写入到 buffer pool 里面。内存的数据页和磁盘数据不一致的

时候，我们把它叫做脏页。

InnoDB 里面有专门的把 buffer pool 的数据写入到磁盘的线程，每隔一段时间就一次性地把多个修

改写入磁盘，这个就叫做刷脏。

这里面就有一个问题，如果在脏页还没有写入磁盘的时候，服务器出问题了，内存里面的数据丢失

了。或者是刷脏刷到一半，甚至会破坏数据文件。所以我们必须要有一个持久化的机制。

redo log

InnoDB 引入了一个日志文件，叫做 redo log(重做日志)，我们把所有对内存数据的修改操作写

入日志文件，如果服务器出问题了，我们就从这个日志文件里面读取数据，恢复数据——用它来实现事

务的持久性。

redo log 有什么特点？

1.记录修改后的值，属于物理日志

2.redo log 的大小是固定的，前面的内容会被覆盖，所以不能用于数据回滚/数据恢复。

3.redo log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有。binlog

MySQL Server 层也有一个日志文件，叫做 binlog，它可以被所有的存储引擎使用。

binlog 以事件的形式记录了所有的 DDL 和 DML 语句(因为它记录的是操作而不是数据值，属于逻

辑日志)，可以用来做主从复制和数据恢复。

主从复制

数据恢复

跟 redo log 不一样，它的文件内容是可以追加的，没有固定大小限制。

有了这两个日志之后，我们来看一下一条更新语句是怎么执行的：例如一条语句：update teacher set name='jim' where name =‘666’

1、先查询到这条数据，如果有缓存，也会用到缓存。

2、把 name 改成jim，然后调用引擎的 API 接口，写入这一行数据到内存，同时记录 redo log。这

时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，可以随时提交。

3、执行器收到通知后记录 binlog，然后调用存储引擎接口，设置 redo log 为 commit 状态。

4、更新完成。

问题：为什么要用两阶段提交(XA)呢？

举例：

如果我们执行的是把 name 改成jim，如果写完 redo log，还没有写 bin log 的时候，MySQL 重启

了。

因为 redo log 可以恢复数据，所以写入磁盘的是jim。但是 bin log 里面没有记录这个逻辑日志，所

以这时候用 binlog 去恢复数据或者同步到从库，就会出现数据不一致的情况。

所以在写两个日志的情况下，binlog 就充当了一个事务的协调者。通知 InnoDB 来执行 prepare 或

commit 或者 rollback。

简单地来说，这里有两个写日志的操作，类似于分布式事务，不用两阶段提交，就不能保证都成功

或者都失败。

MySQL 索引原理详解

一、 索引是什么？

1.1. 索引是什么

当一张表有 500 万条数据，在没有索引的 name 字段上执行一个查询：

如果 name 字段上面有索引呢？

select * from user_innodb where name ='jim';索引的创建是需要消耗时间的。

有索引的查询和没有索引的查询相比，效率相差几十倍。

索引到底是什么呢？为什么可以对我们的查询产生这么大的影响？创建索引的时候做了什么事情？

1.1.1.索引图解

定义：数据库索引，是数据库管理系统(DBMS)中一个排序的数据结构，以协助快速查询、更新数据

库表中数据。

数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，我们

要从 500 万行数据里面检索一条数据，只能依次遍历这张表的全部数据(循环调用存储引擎的读取下一

行数据的接口)，直到找到这条数据。

但是我们有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来

快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。

这个很容易理解，就像我们从一本 500 页的书里面去找特定的一小节的内容，肯定不可能从第一页

开始翻。

这本书会有专门的目录，它可能只有几页的内容，它是按页码来组织的，可以根据拼音或者偏旁部

首来查找，我们只要确定内容对应的页码，就能很快地找到我们想要的 内容。

1.1.2.索引类型

那在数据表上面，怎么创建一个索引？建表的时候指定，或者 alter table，也可以使用工具。

第一个是索引的名称，第二个是索引的列，比如我们是要对 id 创建索引还是对 name创建索引。后

面两个很重要，一个叫索引类型。

在 InnoDB 中，索引类型有三种，普通索引、唯一索引(主键索引是特殊的唯一索引)、全文索

引。

普通(Normal)：也叫非唯一索引，是最普通的索引，没有任何的限制。

唯一(Unique)：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索

引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key创建。

ALTER TABLE user_innodb DROP INDEX idx_name;

ALTER TABLE user_innodb ADD INDEX idx_name (name);全文(Fulltext)：针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数据的这种情况，

如果要解决 like 查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比

如 char、varchar、text。

在 5.6 的版本之后，MyISAM 和 InnoDB 都支持全文索引。但是 MySQL 自带的全文索引功能使用

限制还是比较多，建议用其他的搜索引擎方案。

我们说索引是一种数据结构，那么它到底应该选择一种什么数据结构，才能实现数据的高效检索

呢？

二、 索引存储模型推演

2.1. 二分查找

抖音很火的猜数字游戏，

猜你现在是100以内的几，

最后通过不断缩小范围，

锁定数字

这个就是二分查找的一种思想，也叫折半查找，每一次，我们都把候选数据缩小了一半。如果数据

已经排过序的话，这种方式效率比较高。

所以第一个，可以考虑用有序数组作为索引的数据结构。

有序数组的等值查询和比较查询效率非常高，但是更新数据的时候会出现一个问题，

可能要挪动大量的数据(改变 index)，所以只适合存储静态的数据。

为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链表，它的查找效

率还是不够高。

所以，有没有可以使用二分查找的链表呢？

为了解决这个问题，BST(Binary Search Tree)也就是我们所说的二叉查找树诞生了

2.2. 二叉查找树(BST Binary Search Tree)

二叉查找树的特点是什么？

左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有

序的线性表。

create table m3 (

name varchar(50),

fulltext index(name)

);

select * from fulltext_test where match(content) against('马士兵教育' IN NATURAL

LANGUAGE MODE);二叉查找树既能够实现快速查找，又能够实现快速插入。

但是二叉查找树有一个问题：

就是它的查找耗时是和这棵树的深度相关的，在最坏的情况下时间复杂度会退化成O(n)。

什么情况是最坏的情况呢？

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

还是刚才的这一批数字，如果我们插入的数据刚好是有序的，5、7、12、14、17、 25。

这个时候二叉查找树变成了什么样了呢？

它会变成链表(我们把这种树叫做“斜树”)，这种情况下不能达到加快检索速度的目的，和顺序查

找效率是没有区别的。造成它倾斜的原因是什么呢？

因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。

所以，有没有左右子树深度相差不是那么大，更加平衡的树呢？

这个就是平衡二叉树，叫做 Balanced binary search trees，或者 AVL 树(AVL 是发明这个数据结

构的人的名字缩写)。

2.3. 平衡二叉树(AVL Tree)(左旋、右旋)

平衡二叉树的定义：左右子树深度差绝对值不能超过 1。

比如左子树的深度是 2，右子树的深度只能是 1 或者 3。

这个时候我们再按顺序插入 1、2、3、4、5、6，一定是这样，不会变成一棵“斜树”。那它的平衡是怎么做到的呢？怎么保证左右子树的深度差不能超过 1 呢？

https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

插入 5、7、14。

注意看：当我们插入了 5、7 之后，如果按照二叉查找树的定义，14 肯定是要在 7 的右边的，这个

时候根节点 1 的右节点深度会变成 2，但是左节点的深度是 0，因为它没有子节点，所以就会违反平衡

二叉树的定义。

那应该怎么办呢？因为它是右节点下面接一个右节点，右-右型，所以这个时候我们要把 7提上去，

这个操作叫做左旋。

同样的，如果我们插入 14、7、5，这个时候会变成左左型，就会发生右旋操作，把 7提上去。所以为了保持平衡，AVL 树在插入和更新数据的时候执行了一系列的计算和调整的操作。

平衡的问题我们解决了，那么平衡二叉树作为索引怎么查询数据？

在平衡二叉树中，一个节点，它的大小是一个固定的单位，作为索引应该存储什么内容？

它应该存储三块的内容：

第一个是索引的键值。比如我们在 id 上面创建了一个索引，我在用 where id =1 的条件查询的时候

就会找到索引里面的 id 的这个键值。

第二个是数据的磁盘地址，因为索引的作用就是去查找数据的存放的地址。

第三个，因为是二叉树，它必须还要有左子节点和右子节点的引用，这样我们才能找到下一个节

点。比如大于 26 的时候，走右边，到下一个树的节点，继续判断。

如果是这样存储数据的话，我们来看一下会有什么问题。

首先，对于 InnoDB 来说，索引的数据，是放在硬盘上的。查看数据和索引的大小：

select

CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS data_len,

CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024),2),'MB') as index_len

from information_schema.TABLES

where table_schema='yteaher' and table_name='user_innodb';

访问一个节点就要跟磁盘之间发生一次 I/O。InnoDB 操作磁盘的最小的单位是一页(或者叫一个磁

盘块)，大小是 16K(16384 字节)。

那么，一个树的节点就是 16K 的大小。

如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个或者几十个字

节，它远远达不到 16384 字节的容量，所以访问一个树节点，进行一次 IO 的时候，浪费了大量的空

间。

所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多的节点，意味着

跟磁盘交互次数就会过多，消耗的时间也越多。

比如上面这张图，我们一张表里面有 6 条数据，当我们查询 id=66 的时候，要查询两个子节点，就

需要跟磁盘交互 3 次，如果我们有几百万的数据呢？这个时间更加难以估计。

所以解决方案是什么呢？

第一个，就是让每个节点存储更多的数据。

这样的话，就会极大地降低树的深度。我们的树就从原来的高瘦高瘦的样子，变成了矮胖矮胖的样

子。

这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路。

2.4. 多路平衡查找树(B Tree)(分裂、合并)

Balanced Tree

这个就是我们的多路平衡查找树，叫做 B Tree(B 代表平衡)。

跟 AVL 树一样，B 树在枝节点和叶子节点存储键值、数据地址、节点引用。

它有一个特点：分叉数(路数)永远比关键字数多 1。比如我们画的这棵树，每个节点存储两个关

键字，那么就会有三个指针指向三个子节点。

当我们用树的结构来存储索引的时候，因为拿到一块数据就要在 Server 层比较是不是需要的数据，如

果不是的话就要再读一次磁盘。B Tree 的查找规则是什么样的呢？

比如我们要在这张表里面查找20。

• 搜索key = 20

• 20>15,排除0X01

• 20<35,排除0X03

• 那么他在15到35之间，

• 命中0X02

• 走磁盘块3

• 20=20

• 命中

只用了 3 次 IO，这个是不是比 AVL 树效率更高呢？

那 B Tree 又是怎么实现一个节点存储多个关键字，还保持平衡的呢？跟 AVL 树有什么区别？

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

比如 Max Degree(路数)是 3 的时候，我们插入数据 1、2、3，在插入 3 的时候，本来应该在第

一个磁盘块，但是如果一个节点有三个关键字的时候，意味着有 4 个指针， 子节点会变成 4 路，所以这

个时候必须进行分裂(其实就是 B+Tree)。把中间的数据 2提上去，把 1 和 3 变成 2 的子节点。

如果删除节点，会有相反的合并的操作。

注意这里是分裂和合并，跟 AVL 树的左旋和右旋是不一样的。

我们继续插入 4 和 5，B Tree 又会出现分裂和合并的操作。从这个里面也能看到，在更新索引的时候会有大量的索引的结构的调整，所以解释了为什么不要在

频繁更新的列上建索引，或者为什么不要更新主键。

节点的分裂和合并，其实就是 InnoDB 页(page)的分裂和合并。

2.5. B+树(加强版多路平衡查找树)

B Tree 的效率已经很高了，为什么 MySQL 还要对 B Tree 进行改良，最终使用了B+Tree 呢？

总体上来说，这个 B 树的改良版本解决的问题比 B Tree 更全面。

我们来看一下 InnoDB 里面的 B+树的存储结构：

MySQL 中的 B+Tree 有两个特点：

1、它的关键字的数量是跟路数相等的；

2、B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。

目前的认知：我们这要存放的数据是什么？是不是真实数据的地址？

搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索 id=28，虽然在第一层直接

命中了，但是数据地址在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。

3、B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个

叶子节点的第一个数据，形成了一个有序链表的结构。

InnoDB 中的 B+Tree 这种特点带来的优势：

1)它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题是什么？(每

个节点存储更多关键字；路数更多)

2)扫库、扫表能力更强(如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍

历整棵 B+Tree 拿到所有的数据)

3) B+Tree 的磁盘读写能力相对于 B Tree 来说更强(根节点和枝节点不保存数据区，所以一个节点

可以保存更多的关键字，一次磁盘加载的关键字更多)

4)排序能力更强(因为叶子节点上有下一个数据区的指针，数据形成了链表)

5)效率更加稳定(B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的)

2.6. 索引方式：真的是用的 B+Tree 吗？

在 Navicat 的工具中，创建索引，索引方式有两种。

HASH：以 KV 的形式检索数据，也就是说，它会根据索引字段生成哈希码和指针，指针指向数据。

哈希索引有什么特点呢？

第一个，它的时间复杂度是 O(1)，查询速度比较快。但是哈希索引里面的数据不是按顺序存储的，

所以不能用于排序。

第二个，我们在查询数据的时候要根据键值计算哈希码，所以它只能支持等值查询(= IN)，不支

持范围查询(> < >= <= between and)。

第三个：如果字段重复值很多的时候，会出现大量的哈希冲突(采用拉链法解决)，效率会降低。

需要注意的是，在 InnoDB 中，不能显示地创建一个哈希索引(所谓的支持哈希索引指的是

Adaptive Hash Index)。

https://dev.mysql.com/doc/refman/5.7/en/create-index.html

memory 存储引擎可以使用 Hash 索引。

如果说面试的时候问到了为什么不用红黑树：

CREATE TABLE `user_memory` (

`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,

`name` VARCHAR ( 255 ) DEFAULT NULL,

`gender` TINYINT ( 1 ) DEFAULT NULL,

`phone` VARCHAR ( 11 ) DEFAULT NULL,

PRIMARY KEY ( `id` ),

KEY `idx_name` ( `name` ) USING HASH

) ENGINE = MEMORY AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;

红黑树的种种约束保证的是什么？最长路径不超过最短路径的二倍。不太适合于数据库索引。适合

内存的数据机构，例如实现一致性哈希。

因为B Tree 和B+Tree 的特性，它们广泛地用在文件系统和数据库中，例如Windows的 HPFS 文件

系统，Oracel、MySQL、SQLServer 数据库。

三. B+Tree 落地形式

3.1. MySQL 数据存储文件

上一节课我们知道了不同的存储引擎文件不一样。

每 张 InnoDB 的 表 有 两 个 文 件 ( .frm 和 .ibd ) ， MyISAM 的 表 有 三 个 文 件

(.frm、.MYD、.MYI)。

有一个是相同的文件，.frm。 .frm 是 MySQL 里面表结构定义的文件，不管你建表的时候选用任何

一个存储引擎都会生成，我们就不看了。

我们主要看一下其他两个文件是怎么实现 MySQL 不同的存储引擎的索引的。

3.2.1.MyISAM

在 MyISAM 里面，另外有两个文件：

一个是.MYD 文件，D 代表 Data，是 MyISAM 的数据文件，存放数据记录，比如我们的

user_myisam 表的所有的表数据。

一个是.MYI 文件，I 代表 Index，是 MyISAM 的索引文件，存放索引，比如我们在id 字段上面创建

了一个主键索引，那么主键索引就是在这个索引文件里面。

也就是说，在 MyISAM 里面，索引和数据是两个独立的文件。

那我们怎么根据索引找到数据呢？

MyISAM 的 B+Tree 里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI 中找

到键值后，会到数据文件.MYD 中获取相应的数据记录。

show VARIABLES LIKE 'datadir';如果是辅助索引，有什么不一样

ALTER TABLE user_innodb DROP INDEX index_user_name;

ALTER TABLE user_innodb ADD INDEX index_user_name (name);

在 MyISAM 里面，辅助索引也在这个.MYI 文件里面。

辅助索引跟主键索引存储和检索数据的方式是没有任何区别的，一样是在索引文件里面找到磁盘地

址，然后到数据文件里面获取数据。

这个就是 MyISAM 里面的索引落地的形式。但是在 InnoDB 里面是不一样的。我们来看一下。3.2.2.InnoDB

InnoDB 只有一个文件(.ibd 文件)，那索引放在哪里呢？

在 InnoDB 里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文

件，都在.ibd 文件里面。

在 InnoDB 的主键索引的叶子节点上，它直接存储了我们的数据。

所以，为什么说在 InnoDB 中索引即数据，数据即索引，就是这个原因。

但是这里会有一个问题，一张 InnoDB 的表可能有很多个多索引，数据肯定是只有一份的，那数据

在哪个索引的叶子节点上呢？

这里要给大家介绍一个叫做聚集索引(聚簇索引)的概念。

就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。(比如字典的目录是按拼音排序

的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引)。

InnoDB 组织数据的方式就是(聚集)索引组织表(clustered index organize table)。如果说一

张表创建了主键索引，那么这个主键索引就是聚集索引，决定数据行的物理存储顺序。

问题来了，那主键索引之外的索引，他们存储什么内容，他们的叶子节点上没有数据怎么检索完整

数据？比如在 name 字段上面建的普通索引。

InnoDB 中，主键索引和辅助索引是有一个主次之分的。刚才我们讲了，如果有主键索引，那么主

键索引就是聚集索引。其他的索引统一叫做“二级索引”或者辅助索引。二级索引存储的是辅助索引的键值，例如在 name 上建立索引，节点上存的是 name的值，bobo，

jim 等等。

而二级索引的叶子节点存的是这条记录对应的主键的值。比如 bobo id=1，jim id=4……

所以，二级索引检索数据的流程是这样的：

当 我 们 用 name 索 引 查 询 一 条 记 录 ， 它 会 在 二 级 索 引 的 叶 子 节 点 找 到name=bobo，

拿到主键值，也就是 id=，然后再到主键索引的叶子节点拿到数据。

从这个角度来说，因为主键索引比二级索引少扫描了一棵 B+Tree，它的速度相对会快一些。

但是，如果一张表没有主键怎么办？那完整的记录放在哪个索引的叶子节点？或者，这张表根本没

有索引呢？数据放在哪里？

https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

1、如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引。

2、如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索

引。

3、如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，

它会随着行记录的写入而主键递增。

四、 索引使用原则

我们容易有一个误区，就是在经常使用的查询条件上都建立索引，索引越多越好，那到底是不是这

样呢？

4.1. 列的离散(sàn)度

第一个叫做列的离散度，我们先来看一下列的离散度的公式：

count(distinct(column_name)) : count(*)，列的全部不同值和所有数据行的比例。数据行数相同的

情况下，分子越大，列的离散度就越高。

select _rowid name from t2;简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。

我们不建议大家在离散度低的字段上建立索引。

没有索引的时候查一遍：

SELECT * FROM `user_innodb` WHERE gender = 0;

建立索引之后再查一遍：

ALTER TABLE user_innodb DROP INDEX idx_user_gender;

ALTER TABLE user_innodb ADD INDEX idx_user_gender (gender); -- 耗时比较久

SELECT * FROM `user_innodb` WHERE gender = 0;发现消耗的时间更久了。

4.2. 联合索引最左匹配

前面我们说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立联合索

引，举例：查询成绩的时候必须同时输入身份证和考号。

单列索引可以看成是特殊的联合索引。

比如我们在 user 表上面，给 name 和 phone 建立了一个联合索引。

联合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的(name 在左

边，phone 在右边)。

从这张图可以看出来，name 是有序的，phone 是无序的。当 name 相等的时候，phone 才是有

序的。

这个时候我们使用 where name= 'jim' and phone = '136xx '去查询数据的时候，B+Tree 会优先比

较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name相同的时候再比较 phone。但是如

果查询条件没有 name，就不知道第一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较

因子，所以用不到索引。

4.2.1.什么时候用到联合索引

所以，我们在建立联合索引的时候，一定要把最常用的列放在最左边。

比如下面的三条语句，大家觉得用到联合索引了吗？

1)使用两个字段，用到联合索引：

2)使用左边的 name 字段，用到联合索引：

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= 'jim' AND phone = '150000000000';

EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮'3)使用右边的 phone 字段，无法使用索引，全表扫描：

4.2.2.如何创建联合索引

有一天我们的 DBA 找到我，说我们的项目里面有两个查询很慢，按照我们的想法，一个查询创建一

个索引，所以我们针对这两条 SQL 创建了两个索引，这种做法觉得正确吗？

当我们创建一个联合索引的时候，按照最左匹配原则，用左边的字段 name 去查询的时候，也能用

到索引，所以第一个索引完全没必要。

相当于建立了两个联合索引(name),(name,phone)。

如果我们创建三个字段的索引 index(a,b,c)，相当于创建三个索引：

index(a)

index(a,b)

index(a,b,c)

用 where b=? 和 where b=? and c=? 是不能使用到索引的。

这里就是 MySQL 里面联合索引的最左匹配原则。

4.3. 覆盖索引

什么叫回表：

非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没

有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

例如：

EXPLAIN SELECT * FROM user_innodb WHERE phone = '15200000000'

CREATE INDEX idx_name on user_innodb(name);

CREATE INDEX idx_name_phone on user_innodb(name,phone);

select * from user_innodb where name = 'bobo';在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引中就能够取得，

不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

Extra 里面值为“Using index”代表使用了覆盖索引。

我们先来创建一个联合索引：

这三个查询语句都用到了覆盖索引：

select * ，此处用不到覆盖索引。

如果改成只用 where phone = 查询呢？大家自己试试。按照我们之前的分析，它是用不到索引的。

实际上可以用到覆盖索引！覆盖索引跟是否可能使用索引没有直接关系。

很明显，因为覆盖索引减少了 IO 次数，减少了数据的访问量，可以大大地提升查询效率。

五. 索引的创建与使用

因为索引对于改善查询性能的作用是巨大的，所以我们的目标是尽量使用索引。

5.1. 在什么字段上索引？

1、在用于 where 判断 order 排序和 join 的(on)字段上创建索引

2、索引的个数不要过多。

——浪费空间，更新变慢。

3、区分度低的字段，例如性别，不要建索引。

——离散度太低，导致扫描行数过多。

4、频繁更新的值，不要作为主键或者索引。

——页分裂

5、随机无序的值，不建议作为主键索引，例如身份证、UUID。

——无序，分裂

6、创建复合索引，而不是修改单列索引

-- 创建联合索引

ALTER TABLE user_innodb DROP INDEX comixd_name_phone;

ALTER TABLE user_innodb add INDEX `comixd_name_phone` (`name`,`phone`);

EXPLAIN SELECT name,phone FROM user_innodb WHERE name= 'jim' AND phone = '

13666666666';

EXPLAIN SELECT nameFROM user_innodb WHERE name= 'jim' AND phone = '

13666666666';

EXPLAIN SELECT phone FROM user_innodb WHERE name= 'jim' AND phone = '

13666666666';5.2. 什么时候索引失效？

1、索引列上使用函数(replace\SUBSTR\CONCAT\sum count avg)、表达式

计算(+ - * /)：https://www.runoob.com/mysql/mysql-functions.html

2、字符串不加引号，出现隐式转换

3、like 条件中前面带%

where 条件中 like abc%，like %2673%，like %888 都用不到索引吗？为什么？

过滤的开销太大。这个时候可以用全文索引。

4、负向查询

NOT LIKE 不能：

!= (<>)和 NOT IN 在某些情况下可以：

注意跟数据库版本、数据量、数据选择度都有关系。

其实，用不用索引，最终都是优化器说了算。

优化器是基于什么的优化器？

基于 cost 开销(Cost Base Optimizer)，它不是基于规则(Rule-Based Optimizer)，也不是基

于语义。怎么样开销小就怎么来。

https://docs.oracle.com/cd/B10501_01/server.920/a96533/rbo.htm#38960

explain SELECT * FROM `t2` where id+1 = 4;

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 = 136;

explain SELECT * FROM `user_innodb` where name = '136';

explain select *from user_innodb where name like 'wang%';

explain select *from user_innodb where name like '%wang';

explain select *from employees where last_name not like 'wang'

explain select *from employees where emp_no not in (1) explain select *from

employees where emp_no <> 1https://dev.mysql.com/doc/refman/5.7/en/cost-model.html

使用索引有基本原则，但是没有具体细则，没有什么情况一定用索引，什么情况一定不用索引的规

则

MySQL 性能优化思路和工具

一、优化思路

作为架构师或者开发人员，说到数据库性能优化，你的思路是什么样的？

或者具体一点，如果在面试的时候遇到这个问题：你会从哪些维度来优化数据库，你会怎么回答？

我们在第一节课开始的时候讲了，这四节课的目标是为了让大家建立数据库的知识体系，和正确的

调优的思路。

我们说到性能调优，大部分时候想要实现的目标是让我们的查询更快。一个查询的动作又是由很多

个环节组成的，每个环节都会消耗时间，我们在第一节课讲 SQL 语句的执行流程的时候已经分析过了。

我们要减少查询所消耗的时间，就要从每一个环节入手。

二、 连接——配置优化

第一个环节是客户端连接到服务端，连接这一块有可能会出现什么样的性能问题？有可能是服务端

连接数不够导致应用程序获取不到连接。比如报了一个 Mysql: error1040: Too many connections 的错

误。

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

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

如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以：

(

1)修改配置参数增加可用连接数，修改 max_connections 的大小：

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

2)或者，或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是 28800

秒，8 小时，我们可以把这个值调小。

2、从客户端来说，可以减少从服务端获取的连接数，如果我们想要不是每一次执行SQL 都创建一

个新的连接，应该怎么做？

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

我们可以在哪些层面使用连接池？ORM 层面(MyBatis 自带了一个连接池)；或者使用专用的连接

池工具(阿里的 Druid、Spring Boot 2.x 版本默认的连接池 Hikari、老牌的 DBCP 和 C3P0)。

我们这里说到了从数据库配置的层面去优化数据库。不管是数据库本身的配置，还是安装这个数据

库服务的操作系统的配置，对于配置进行优化，最终的目标都是为了更好地发挥硬件本身的性能，包括

CPU、内存、磁盘、网络。

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

在我们这几天的课程里面也接触了很多的 MySQL 和 InnoDB 的配置参数，包括各种开关和数值的

配置，大多数参数都提供了一个默认值，比如默认的 buffer_pool_size，默认的页大小，InnoDB 并发线

程数等等。

这些默认配置可以满足大部分情况的需求，除非有特殊的需求，在清楚参数的含义的情况下再去修

改它。修改配置的工作一般由专业的 DBA 完成。

至于硬件本身的选择，比如使用固态硬盘，搭建磁盘阵列，选择特定的 CPU 型号这些，更不是我们

开发人员关注的重点，这个我们就不做过多的介绍了。

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

端的连接数的方案呢？

我们可以引入缓存。

三、缓存——架构优化

3.1 缓存

在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题：一方面是会给数据库带来

很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到影响。

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

运行独立的缓存服务，属于架构层面的优化。

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

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

在使用的连接3.2 主从复制

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

集群的话必然会面临一个问题，就是不同的节点之间数据一致性的问题。如果同时读写多台数据库

节点，怎么让所有的节点数据保持一致？

这个时候我们需要用到复制技术(replication)，被复制的节点称为 master，复制的节点称为

slave。

主从复制是怎么实现的呢？在第一节课我们说过，更新语句会记录 binlog，它是一种逻辑日志。

有了这个 binlog，从服务器会获取主服务器的 binlog 文件，然后解析里面的 SQL 语句，在从服务

器上面执行一遍，保持主从的数据一致。

这里面涉及到三个线程，连接到 master 获取 binlog，并且解析 binlog 写入中继日志，这个线程叫

做 I/O 线程。

Master 节点上有一个 log dump 线程，是用来发送 binlog 给 slave 的。

从库的 SQL 线程，是用来读取 relay log，把数据写入到数据库的。

这个是主从复制涉及到的三个线程。

做了主从复制的方案之后，我们只把数据写入 master 节点，而读的请求可以分担到slave 节点。我

们把这种方案叫做读写分离。读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要特别注意主从数据一致性的问

题。

我们在做了主从复制之后，如果单个 master 节点或者单张表存储的数据过大的时候，比如一张表

有上亿的数据，单表的查询性能还是会下降，我们要进一步对单台数据库节点的数据进行拆分，这个就

是分库分表。

3.3 分库分表

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

垂直分库的做法，把一个数据库按照业务拆分成不同的数据库：水平分库分表的做法，把单张表的数据按照一定的规则分布到多个数据库。以上是架构层面的优化，可以用缓存，主从，分库分表

第三个环节：

解析器，词法和语法分析，主要保证语句的正确性，语句不出错就没问题。由 Sever 自己处理，跳

过。

第四步：优化器

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

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

问题：在我们做项目的时候，有时会收到 DBA 的邮件，里面列出了我们项目上几个耗时比较长的查

询语句，让我们去优化，这些语句是从哪里来的呢？

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

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

4.1 慢查询日志 slow query log

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

4.1.1 打开慢日志开关

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

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

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

或者修改配置文件 my.cnf。

以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。

模拟慢查询：

查询 user_innodb 表的 500 万数据(检查是不是没有索引)。

4.1.2 慢日志分析

1、日志内容

有了慢查询日志，怎么去分析统计呢？比如 SQL 语句的出现的慢查询次数最多，平均每次执行了多

久？人工肉眼分析显然不可能。

2、mysqldumpslow

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%';

slow_query_log = ON

long_query_time=2

slow_query_log_file =/var/lib/mysql/localhost-slow.log

select sleep(10);

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

show global status like 'slow_queries'; -- 查看有多少慢查询

show variables like '%slow_query%'; -- 获取慢日志目录

cat /var/lib/mysql/ localhost-slow.loghttps://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

MySQL 提供了 mysqldumpslow 的工具，在 MySQL 的 bin 目录下。

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

Count 代表这个 SQL 执行了多少次；

Time 代表执行的时间，括号里面是累计时间；

Lock 表示锁定的时间，括号是累计；

Rows 表示返回的记录数，括号是累计。

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

4.2 SHOW PROFILE

https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

SHOW PROFILE 是谷歌高级架构师 Jeremy Cole 贡献给 MySQL 社区的，可以查看SQL 语句执行的

时候使用的资源，比如 CPU、IO 的消耗情况。

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

4.2.1 查看是否开启

4.2.2 查看 profile 统计

(命令最后带一个 s)

mysqldumpslow --help

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

select @@profiling;

set @@profiling=1;

show profiles;查看最后一个 SQL 的执行详细信息，从中找出耗时较多的环节(没有 s)。

6.2E-5，小数点左移 5 位，代表 0.000062 秒。

也可以根据 ID 查看执行详细信息，在后面带上 for query + ID。

除了慢日志和 show profile，如果要分析出当前数据库中执行的慢的 SQL，还可以通过查看运行线

程状态和服务器运行信息、存储引擎信息来分析。

4.2.3 其他系统命令

show processlist 运行线程

https://dev.mysql.com/doc/refman/5.7/en/show-processlist.html

这是很重要的一个命令，用于显示用户运行线程。可以根据 id 号 kill 线程。

也可以查表，效果一样：(可以 group order by 了)

show profile;

show profile for query 1;

show processlist;

select * from information_schema.processlist;show status 服务器运行状态

说明：https://dev.mysql.com/doc/refman/5.7/en/show-status.html

详细参数：https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html

SHOW STATUS 用于查看 MySQL 服务器运行状态(重启后会清空)，有 session和 global 两种作

用域，格式：参数-值。

可以用 like 带通配符过滤。

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

show engine 存储引擎运行信息

https://dev.mysql.com/doc/refman/5.7/en/show-engine.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-standard-monitor.html

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 语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。MySQL 5.6.3以前只能分析 SELECT; MySQL5.6.3以后就可以分析update、delete、insert 了。

4.3 EXPLAIN 执行计划

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

我们先创建三张表。一张课程表，一张老师表，一张老师联系方式表(没有任何索引)。

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', 'bobo', '1' );

INSERT INTO `teacher`

VALUES

( '2', 'jim', '2' );

INSERT INTO `teacher`explain 的结果有很多的字段，我们详细地分析一下。

先确认一下环境：

4.3.1 id

id 是查询序列编号。

id 值不同

id 值不同的时候，先查询 id 值大的(先大后小)。

查询顺序：course c——teacher t——teacher_contact tc。

先查课程表，再查老师表，最后查老师联系方式表。子查询只能以这种方式进行，只有拿到内层的结

果之后才能进行外层的查询。

id 值相同(从上往下)

VALUES

( '3', 'dahai', '3' );

INSERT INTO `teacher_contact`

VALUES

( '1', '13688888888' );

INSERT INTO `teacher_contact`

VALUES

( '2', '18166669999' );

INSERT INTO `teacher_contact`

VALUES

( '3', '17722225555' );

select version();

show variables like '%engine%';

-- 查询 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' ) );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。

再看一个包含子查询的案例：

-- 查询课程 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 );

EXPLAIN SELECT * FROM teacher;

-- 查询 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' ) );PRIMARY

子查询 SQL 语句中的主查询，也就是最外面的那层查询。

SUBQUERY

子查询中所有的内层查询都是 SUBQUERY 类型的。

DERIVED

衍生查询，表示在得到最终查询结果之前会用到临时表。例如：

对于关联查询，先执行右边的 table(UNION)，再执行左边的 table，类型是DERIVED

UNION

用到了 UNION 查询。同上例。

UNION RESULT

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

上例。

4.3.3 type 连接类型

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types

所有的连接类型中，上面的最好，越往下越差。

在常用的链接类型中：system > const > eq_ref > ref > range > index > all

这 里 并 没 有 列 举 全 部 (

其 他 ： fulltext 、 ref_or_null 、 index_merger 、

unique_subquery、index_subquery)。

以上访问类型除了 all，都能用到索引。

const

主键索引或者唯一索引，只能查到一条数据的 SQL。

-- 查询 ID 为 1 或 2 的老师教授的课程

EXPLAIN SELECT

cr.cname

FROM

( SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid =

2 ) cr;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;

system

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

EXPLAIN SELECT * FROM mysql.proxies_priv;

eq_ref

通常出现在多表的 join 查询，表示对于前表的每一个结果,，都只能匹配到后表的一行结果。一般是

唯一性索引的查询(UNIQUE 或 PRIMARY KEY)。

eq_ref 是除 const 之外最好的访问类型。

先删除 teacher 表中多余的数据，teacher_contact 有 3 条数据，teacher 表有 3条数据。

DELETE

FROM

teacher

WHERE

tid IN ( 4, 5, 6 );

COMMIT;

-- 备份

INSERT INTO `teacher`

VALUES

( 4, 'jim', 4 );

INSERT INTO `teacher`

VALUES

( 5, 'bobo', 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)：

小结：

以上三种 system，const，eq_ref，都是可遇而不可求的，基本上很难优化到这个状态。

ref

查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。

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

explain SELECT * FROM teacher where tcid = 3;

range

索引范围扫描。

如果 where 后面是 between and 或 <或 > 或 >= 或 <=或 in 这些，type 类型就为 range。

不走索引一定是全表扫描(ALL)，所以先加上普通索引。

-- ALTER TABLE teacher DROP INDEX idx_tid;

ALTER TABLE teacher ADD INDEX idx_tid (tid);

执行范围查询(字段上有普通索引)：

EXPLAIN SELECT * FROM teacher t WHERE t.tid <3;

-- 或

EXPLAIN SELECT * FROM teacher t WHERE tid BETWEEN 1 AND 2;

IN 查询也是 range(字段有主键索引)

index

Full Index Scan，查询全部索引中的数据(比不走索引要快)。

all

Full Table Scan，如果没有索引或者没有用到索引，type 就是 ALL。代表全表扫描。

小结：

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

ALL(全表扫描)和 index(查询全部索引)都是需要优化的。

4.3.4 possible_key、key

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

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

反过来，possible_key 为空，key 可能有值吗？

表上创建联合索引：

执行计划(改成 select name 也能用到索引)：

结论：是有可能的(这里是覆盖索引的情况)。

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

4.3.5 key_len

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

表上有联合索引：KEY comidx_name_phone ( name , phone )

EXPLAIN SELECT * FROM teacher_contact t WHERE tcid in (1,2,3);

EXPLAIN SELECT tid FROM teacher;

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;

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

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

explain select * from user_innodb where name ='jim';4.3.6

rows

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

4.3.7 filtered

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

一个百分比。

4.3.8 ref

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

4.3.9 Extra

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

using index

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

using where

使用了 where 过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在 server 层进

行过滤(跟是否使用索引没有关系)。

using filesort

不能使用索引来排序，用到了额外的排序(跟磁盘或文件没有关系)。需要优化。(复合索引的前

提)

(order by id 引起)

using temporary

用到了临时表。例如(以下不是全部的情况)：

1、distinct 非索引列

2、group by 非索引列

EXPLAIN SELECT tid FROM teacher ;

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

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 ='jim' order by id;

EXPLAIN select DISTINCT(tid) from teacher t;3、使用 join 的时候，group 任意列

需要优化，例如创建复合索引。

总结一下：

模拟优化器执行 SQL 查询语句的过程，来知道 MySQL 是怎么处理一条 SQL 语句的。通过这种方式

我们可以分析语句或者表的性能瓶颈。

分析出问题之后，就是对 SQL 语句的具体优化。

4.4 SQL 与索引优化

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

我们在第二节课里面也讲到了索引创建的原则，和什么情况会用到索引，什么情况不会用到索引。

五、存储引擎

5.1 存储引擎的选择

为不同的业务表选择不同的存储引擎，例如：查询插入操作多的业务表，用 MyISAM。临时数据用

Memory。常规的并发大更新多的表用 InnoDB。。

5.2 字段定义

原则：使用可以正确存储数据的最小数据类型。

为每一列选择合适的字段类型。

5.2.1 整数类型

TINYINT 1 个字节

SMALLINT 2 个字节

MEDIUMINT 3 个字节

INT, INTEGER 4 个字节

BIGINT 8 个字节

EXPLAIN select tname from teacher group by tname;

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

t.tid;INT 有 8 种类型，不同的类型的最大存储范围是不一样的。

性别？用 TINYINT，因为 ENUM 也是整数存储。

5.2.2 字符类型

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

固定长度的用 char，不要用 varchar。

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

降低了可读性；

影响数据库性能，应该把把计算的事情交给程序，数据库专心做存储；

数据的完整性应该在程序中检查。

5.2.4 大文件存储

不要用数据库存储图片(比如 base64 编码)或者大文件；

把文件放在 NAS 上，数据库只需要存储 URI(相对路径)，在应用中配置 NAS 服务器地址。

5.2.5 表拆分或字段冗余

将不常用的字段拆分出去，避免列数过多和数据量过大。

比如在业务系统中，要记录所有接收和发送的消息，这个消息是 XML 格式的，用blob 或者 text 存

储，用来追踪和判断重复，可以建立一张表专门用来存储报文。

六 总结：优化体系

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

除了对于代码、SQL 语句、表定义、架构、配置优化之外，业务层面的优化也不能忽视。、

举两个例

子：

1)在某一年的双十一，为什么会做一个充值到余额宝和余额有奖金的活动，例如充300 送 50？因为使用余额或者余额宝付款是记录本地或者内部数据库，而使用银行卡付款，需要调用接口，操

作内部数据库肯定更快。

2)在去年的双十一，为什么在凌晨禁止查询今天之外的账单？

这是一种降级措施，用来保证当前最核心的业务。

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

预售分流。

4)公安局的同名查询，不是实时返回结果(不是实时查询数据库)，而是通过公众号推送。

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

入 MQ 削峰，等等等等。

为什么同样用 MySQL，有的公司可以抗住百万千万级别的并发，而有的公司几百个并发都扛不住，

关键在于怎么用。所以，用数据库慢，不代表数据库本身慢，有的时候还要往上层去优化。

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

是所有的数据都要放到关系型数据库存储。