MySQL——索引语法及索引的原理、SQL优化和视图、复制架构/主从复制

第一章 MySQL逻辑架构

1、总述

和其它数据库有所不同，MySQL 的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，『插件式』的『存储引擎架构』将查询和其它的系统任务以及数据的存储提取相分离。

这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

2、连接层

最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP 的通信。

主要任务是连接处理、授权认证、及相关的安全方案等。

该层引入了线程池，为通过认证、安全接入的客户端提供线程处理任务。

同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

3、服务层

第二层架构主要完成大多数的核心服务功能。如 SQL 接口、并完成缓存的查询、SQL 的分析和优化及部分内置函数的执行。

所有跨存储引擎的功能也在这一层实现，如过程、函数等。

在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化：如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。

如果是 select 语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

模块名称	作用
Management Serveices & Utilities	系统管理和控制工具
SQL Interface	接受用户的 SQL 指令，并且返回查询的结果。比如 select from 就是调用SQL Interface
Parser	SQL 命令传递到解析器的时候会被解析器验证和解析。
Optimizer	查询优化器 SQL 语句在查询之前会使用查询优化器对查询进行优化。 例如： select uid,name from user where gender= 1; 查询优化器来决定先投影还是先过滤。
Cache和Buffer	查询缓存，存储 SELECT 语句以及相应的查询结果集 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存，权限缓存等

4、引擎层

存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要选取。后面介绍 MyISAM 和 InnoDB。

5、存储层

数据存储层，主要是将数据存储在运行于文件系统上，并完成与存储引擎的交互。

6、工作流程

①建立连接

MySQL 客户端通过协议与 MySQL 服务器建连接，发送查询语句。

②查询缓存

检查查询缓存中是否存在要查询的数据。

• 命中：直接返回结果。

  不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。

• 未命中：解析 SQL 语句。

③语法解析器和预处理

MySQL 首先根据语法规则验证 SQL 语句的语法正确性，然后通过关键字将 SQL 语句进行解析，并生成一棵对应的『解析树』。预处理器则根据一些 MySQL 规则进一步检查解析树是否正确。

④生成执行计划

当解析树被确认为正确，接下来就会由查询优化器将解析树转化成执行计划（profile）。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

⑤按计划执行并获取数据

MySQL 默认使用 B+Tree 索引，并且一个大致方向是：无论怎么调整 SQL，至少在目前来说，MySQL 最多只用到表中的一个索引。

第二章 MySQL存储引擎

1、查看存储引擎

①查看 MySQL 支持的存储引擎

②查看当前默认的存储引擎

2、引擎介绍

①InnoDB

MySQL 从 5.5 版本之后，就开始默认采用 InnoDB 引擎。

• InnoDB 是 MySQL 的默认事务型引擎。
• InnoDB 被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
• 如果除了增加和查询外，还需要更新和删除操作，那么应优先选择 InnoDB 存储引擎。
• 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑 InnoDB 引擎。

②MyISAM

MySQL 在 5.5 版本之前，默认使用 MyISAM 引擎。

MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等。但 MyISAM 不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

• 数据文件结构：
  • .frm 存储定义表
  • .MYD 存储数据
  • .MYI 存储索引

③Archive

• Archive 档案存储引擎只支持 INSERT 和 SELECT 操作，在 MySQL 5.1 之前不支持索引。
• Archive 表适合日志和数据采集（档案）类应用。
• 根据英文的测试结论来看，Archive 表比 MyISAM 表要小大约 75%，比支持事务处理的 InnoDB 表小大约83%。

④Blackhole

• Blackhole 引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。
• 但服务器会记录 Blackhole 表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

⑤CSV

• CSV 引擎可以将普通的 CSV 文件作为 MySQL 的表来处理，但不支持索引。
• CSV 引擎可以作为一种数据交换的机制，非常有用。
• CSV 存储的数据直接可以在操作系统里，用文本编辑器，或者 Excel 读取。

⑥Memory

• 如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用 Memory 表是非常有用。
• Memory 表至少比 MyISAM 表要快一个数量级。

⑦Federated

Federated 引擎是访问其他 MySQL 服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

3、巅峰对决[记住]

下面我们将最常见的 InnoDB 和 MyISAM 两个引擎详细对比一下：

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	支持表锁 即使操作一条记录也会锁住整个表， 不适合高并发操作	支持行锁 操作时只锁某一行，不对其它行有影响， 适合高并发操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据， 对内存要求较高， 而且内存大小对性能有决定性的影响
系统提供预创建数据库表 给用户使用	是	否
关注点	性能：节省资源、消耗少、简单业务	事务：并发写、事务、更大资源
默认安装	是	是
默认使用	否	是

第三章 MySQL索引语法

1、概念介绍

索引类型	索引特点
单列索引	即一个索引是只根据一个字段创建的，里面只包含单个列， 一个表可以有多个单值索引（也叫单列索引）
联合索引	即一个索引包含多个列
唯一索引	索引列的值必须唯一，但允许有空值，空值可以有多个
主键索引	设定为主键后数据库会自动建立索引，InnoDB 为聚簇索引

当我们平时谈到『主键』时，这个概念包含三个方面的具体含义：

• 主键字段和这个字段中具体的值
• 主键约束
• 主键索引

2、索引操作语法

相关语法可以参考 W3CSchool 教程：https://www.w3school.com.cn/sql/sql_create_index.asp

①准备工作

create database db_shop;

use db_shop;

create table t_customer(
pk_id int auto_increment primary key,
customer_no varchar(200),
customer_name varchar(200));

②创建单值索引

# create index 索引名称 on 要建立索引的字段所在的表(要建立索引的字段);
create index idx_customer_name on t_customer(customer_name);

③创建唯一索引

# create unique index 索引名称 on 要建立索引的字段所在的表(要建立索引的字段);
create unique index idx_customer_no on t_customer(customer_no);

④联合索引

# create index 索引名称 on 要建立索引的字段所在的表(要建立索引的字段,...,要建立索引的字段);
create index idx_customer_no_name on t_customer(customer_no,customer_name);

索引创建好可以如下图所示方式查看：

⑤删除索引

# drop index 要删除的索引名称 on 索引所在的表;
drop index idx_customer_no_name on t_customer;

3、最佳实践

①需要创建索引的情况

• 主键自动建立唯一索引

• 频繁作为查询条件的字段应该创建索引，也就是经常出现在 where 子句中的字段，尤其是数据表大的时候

• 关联查询

  • 不要涉及 3 张以上的表
  • 小表驱动大表，给大表的关联字段创建索引
  • 尽量先用 where 条件过滤数据
  • 关联字段在各个表中类型要一致

• 单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)

  说明：同样是 A、B、C 三个字段，是分别创建三个索引，还是创建一个组合索引？

  答案：此时通常来说我们更倾向于创建组合索引。因为基本上来说不管怎么优化，MySQL 里一条 SQL 语句中只有一个字段的索引能够生效。所以我们建立组合索引并参照最左原则能够让更多的索引起到作用。

• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

• 查询中统计或者分组的字段

②下列情况创建索引效果更好

• 字段的数值有唯一性限制
• 类型小的字段

③不要创建索引的情况

• MySQL 中，一张数据库表中记录数量小于 300 万条时，即使创建索引也不会让搜索速度有明显提升。
• 经常增删改的表，建立索引提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件，重新排布索引，这个操作需要对索引表做全表扫描。不仅索引表的全表扫描操作本身非常耗时，而且索引重新排布时不可用，此时执行查询操作没有索引可用，还是要回到原始数据库表做全表扫描。
• where 条件里用不到的字段
• 数据重复，过滤性不好的字段
• 无法排序的字段
• 不要创建冗余或重复的索引

④结论

索引并不是无条件创建、越多越好，而是要根据实际情况恰到好处的创建。不再使用或很少使用的索引要删除掉。

第四章 索引的原理

1、数据页

假设有一张数据库表：

emp_id(主键)	emp_age(非主键)	emp_name(非主键)
1	21	tom
2	22	jerry
3	23	bob
4	24	harry
5	25	lily
……	……	……

对于 InnoDB 存储引擎来说，最小的存储单位就是：页。那么存放原始数据的页就称为数据页。

一个数据页默认的大小是：16KB。如果我们假设一条记录所占空间的字节数是 1KB，那么这个数据页大致能够存储 16 条记录。那么如果这个数据库表总共有 100 万条记录，那么肯定是需要很多数据页来存放这些数据。

2、数据页内部

①主键排序

数据页内部对主键进行了排序，所以当我们在一个数据页内部根据主键查找记录时，会根据二分法进行查找。找到主键就能够很快找到这条记录。

②数据页编号

为了在众多数据页中，定位每一个数据页，数据页需要有编号。所以这个编号也相当于是这个页的地址。

3、数据页如何便于查找

①排序

根据主键值对所有数据页进行排序。

②双向链表

把所有数据页组成环状双向链表。

好处是：假设搜索主键值大于 30 的记录，那么找到主键值为 30 的记录之后，后面所有的记录就都可以快速找到和返回了。

③扫描全表

就目前的数据存储方式来说，如果要找到某一个主键值所在的数据页，需要一页一页的查找，这无疑是非常缓慢的。所以肯定需要做进一步优化。

4、数据页设定目录

①第一层目录

[1]设置

• 选取每一个数据页中最小的主键值
• 最小主键值 + 当前数据页的页码 = 目录页中的一条记录
• 主键值 + 页码 组成的记录放在一起组成了目录页
• 不管是数据页还是目录页都是页，默认大小都是 16KB
• 估算目录页能够存储多少条记录？
  • 主键值占 8 字节
  • 页码占 8 字节
  • 16 KB / 16 B = 1024 条

[2]搜索

现在我们在 4 号目录页中有如下三个区间：

• 区间 1：[1,11)
• 区间 2：[11,21)
• 区间 3：[21, …)

情景一：搜索主键值为 11 的记录

select emp_id,emp_name,emp_age from t_emp where emp_id=11

• 11 落在了区间 2：[11,21)
• 根据目录页中的 11->2 这条记录，得知想要的数据在页码为 2 的数据页
• 进入页码为 2 的数据页
• 在数据页内部根据主键做二分法查找

情景二：搜索主键值为 15 的记录

select emp_id,emp_name,emp_age from t_emp where emp_id=15

• 15 落在了区间 2：[11,21)
• 根据目录页中的 11->2 这条记录，得知想要的数据在页码为 2 的数据页
• 进入页码为 2 的数据页
• 在数据页内部根据主键做二分法查找

②第二层目录

[1]设置

为了更进一步方便查找，命中我们要找的目录页，我们可以进一步给目录再设置目录。

[2]搜索

用户搜索的主键值是：60。现在 13 号目录页中包含的区间：

• [1,51)
• [51,150)
• [150,…)

所以 60 会落在 [51,150) 区间，所以继续查找页码为 8 的目录页：

• [51,81)
• [81,100)
• [100,…)

所以 60 会落在 [51,81) 区间，所以继续查找页码为 5 的数据页，根据主键执行二分法查找。

[3]扩展

如果有需要，就可以给目录的目录再设置目录……直到向上汇总到一个唯一的根节点。

③最终形成一个树形结构

[1]示意图

[2]对接概念

B+Tree：其实这就是 B+Tree。在我们这里例子中，数据的主键就是 B+Tree 里面的索引（或者说是用主键作为索引），页码值就是指向数据存储位置的指针。只不过指针有可能指向数据页，也有可能指向目录页。

B+Tree 节点：目录页或数据页都算是 B+Tree 中的节点。

• 页：是 InnoDB 存储引擎管理数据的最小单元。
• 节点：是从树形结构角度来说的。

聚簇索引：索引和原始数据存放在一起的存储形式。聚簇索引都是由主键作为索引值，所以一个表中只有一个聚簇索引。

这里我们可以说：主键索引其实就是聚簇索引，这两个名字是从不同的角度来说的。 ——> 主键索引：是指这个索引是根据主键字段来创建的 ——> 聚簇索引：是指这个索引的树形结构中是否同时包含了『原始数据』和『索引目录』 不仅『主键索引』和『聚簇索引』这个两个概念是等同的，而且它们其实也就是数据表本身。也就是说其实我们并没有在原始数据库表之外再另外创建索引表，而是应该说：数据库表本身就是按照 B+Tree 的结构组织起来的，底层就是这么存储的。

非聚簇索引：索引和原始数据没有存放在一起的存储形式。非聚簇索引都算由非主键字段创建的，所以可以有多个。

索引值：在当前例子中，我们是拿主键字段的值作为索引。

[3]估算 B+Tree 能够存储的记录数量

结论：根据下面的推算使用 B+Tree 的形式来组织数据库表中数据的存储方式，只需要 2~4 层就足够了。

• 一层：只有一个根节点
  • 这个根节点只能是数据页节点
  • 一个数据页默认大小是：16KB
  • 假设一条记录占空间：1K
  • 能够存储的是数据数量：16 条
• 两层：
  • 根节点：目录页
    • 主键：8 B
    • 页码：8 B
    • 目录页的每一条记录：16 B
    • 目录页能够存储的记录数量：16 KB / 16 B = 1024
    • 对目录页来说：内部存储了多少条记录，就会指向多少个子节点
  • 叶子节点：数据页
    • 能够存储的是数据数量：16 条
  • 总和：目录页容量 × 数据页容量 = 1024 × 16 = 16384 条记录
• 三层：
  • 根节点：1 个目录页
  • 第二层：1024 个目录页
  • 第三层：目录页的个数 × 目录页的容量 = 1024 × 1024 = 1048576 个数据页
  • 总和：数据页的个数 × 数据页的容量= 1048576 × 16 = 16,777,216 条记录
• 四层：
  • 根节点：1 个目录页
  • 第二层：1024 个目录页
  • 第三层：目录页的个数 × 目录页的容量 = 1024 × 1024 = 1048576 个目录页
  • 第四层：目录页的个数 × 目录页的容量 = 1048576 × 1024 = 1,073,741,824 个数据页
  • 总和：数据页的个数 × 数据页的容量= 1,073,741,824 × 16 = 17,179,869,184 条记录

[4]B+Tree 层次对性能的影响

• 根节点常驻内存。
• 访问下一层的节点会导致一次 I/O。
• 所以层数越少，I/O 的次数就越少，性能就越好。

[5]BTree 为什么高瘦？

• BTree 的体型：高瘦
  • BTree 每个节点都存原始数据。
  • 每个节点的默认大小 16 KB，存了数据，能够用来存『主键 + 页码』的空间就不够了
  • 每个节点容纳子节点的数量就很少，导致深度增加
  • 对于查询来说：深度增加 1 层，I/O 次数增加一次
  • 所以性能方面 BTree 不如 B+Tree
• B+Tree 的体现：扁平、矮胖

[6]BTree 为什么每个节点都存原始数据？

BTree 将所有主键排序，对于父节点来说：

• 节点内部：主键是排序的
• 左边子节点：存放比父节点中最小主键还要小的主键
• 右边子节点：存放比父节点中最大主键还要大的主键
• 中间子节点：存放在父节点中主键区间范围的主键

所以在 BTree 中，在父节点出现过的主键不会在子节点中出现。反过来说：每一个主键只在一个节点中出现。所以主键关联的数据只能在主键所在的节点中保存。

[7]BTree 和 B+Tree 的区别总结

• BTree 中的节点（页），没有目录页、数据页之分。
• BTree 的节点中既存储原始数据，又存储子节点的页码值。
• 在 BTree 中，每个主键值只能在一个节点中出现，所以每个节点都需要保存原始数据
• BTree 因为每个节点都保存原始数据，所以用来保存节点页码的空间变小
• BTree 每个节点能够容纳的子节点比 B+Tree 要少
• BTree 整体层数比 B+Tree 要多
• MySQL 每读取一层节点，就要做一次 I/O 操作
• I/O 操作越多，性能越差

[8]有数据表和索引表之分吗？

这要看索引表具体指的是哪种类型的索引。

• 聚簇索引：那就没有额外的数据表，数据本身就是按照 B+Tree 的形式组成了聚簇索引——以主键作为索引值。
• 非聚簇索引：非聚簇索引相对于聚簇索引来说可以称之为是另外一张表。

但其实我们应该更进一步，更精确的来说：数据库底层维护表中的数据其实并没有『表』这个概念，而全部都是以索引的形式保存的。所以说数据库底层存储数据并不是我们从逻辑上看到的『行』和『列』的形式，而是树形结构。

结论：

• 数据的逻辑结构：表、行、列
• 数据的物理结构：B+Tree

④横着展示

[1]BTree

[2]B+Tree

5、非主键字段创建索引

前面我们所论述的，都是从主键出发建立索引，方便 SQL 语句中根据主键查询数据。

而用户在 SQL 语句中很多时候不是根据主键查询的。所以非主键字段也应该在适合的情况下创建索引，提升查询效率。

①不同数据类型的搜索方式

• 数值类型：就拿这个数值字段本身作为索引值

select emp_id,emp_name,emp_age from t_emp where emp_age=20;

• 字符串类型：
  • 较短字符串：就拿这个字符串字段本身作为索引值

select emp_id,emp_name,emp_age from t_emp where emp_name='tom';

• 较长字符串：通常就拿这个字符串字段开头（最左边）的一部分本身作为索引值

select emp_id,emp_name,emp_age from t_emp where emp_decs like "I come from UK%";

②两种不同索引

对于目录页来说，它的结构固定就是下图所示：

那么我们用什么值做索引呢？

• 聚簇索引：主键值作为索引值
• 非聚簇索引：主键之外其它形式数据作为索引值

②单列索引

[1]概念

假设还是使用前面的数据库表：

emp_id(主键)	emp_age(非主键)	emp_name(非主键)
1	21	tom
2	22	jerry
3	23	bob
4	24	harry
5	25	lily
……	……	……

现在我们为 emp_age 字段建立索引。此时因为我们只用到了这一个字段，所以叫单列索引。

[2]存储结构

和前面聚簇索引对比，也就是数据页存储的结构不同：

其实我们可以这么理解：用哪个字段创建索引，就拿这个字段的值作为索引值。

●根据『主键字段』创建索引，那就是用『主键字段的值』作为『索引值』

●根据『非主键字段』例如 emp_age，创建索引，那就是用这个『非主键字段的值』，例如 emp_age 字段的值作为『索引值』

下面咱们拿实际数据举个例子：

[3]字符串类型能排序吗？

B+Tree 要求作为索引值的数据必须经过排序，这样方便执行二分法查找。而字符串可以根据它底层的 Unicode 码进行排序。因为 Unicode 码是二进制数据，二进制数据（或十六进制数据）也是整数，所以当然可以比较大小，可以排序。

③多列索引

也叫：组合索引、联合索引。顾名思义，这种索引就是在数据页包含了多个字段的值。

多列索引是使用『多个字段值的组合』作为『索引值』。

④回表

我们根据这个以非聚簇索引的索引列大小排序的 B+ 树只能确定我们要查找记录的主键值，所以如果我们想根据非聚簇索引的索引列的值查找到完整的用户记录的话，仍然需要回到聚簇索引中再查一遍，这个过程称为回表。也就是根据非聚簇索引的索引列的值查询一条完整的用户记录需要使用到两棵B+树！

问题：为什么我们还需要一次回表操作呢？直接把完整的用户记录放到叶子节点不OK吗？

回答： 如果把完整的用户记录放到叶子节点是可以不用回表。但是太占地方了，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。

另外还有一个问题是：数据不一致。如果在数据保存两份的情况下，我们执行了 update 操作，那么就不能只改一边，应该两边都改，这样还是浪费性能。

我们取舍的依据：趋利避害。 回表操作所需要付出的代价小于在非聚簇索引中保存原始数据要付出的代价。此时回表操作性价比高。

因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引（英文名secondary index），或者辅助索引。如果我们使用的是 emp_age 列的大小作为 B+ 树的排序规则，那么我们也可以把这个B+树叫做：为 emp_age 列建立的索引。

非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。

⑤不回表的情况

如果最终需要使用的数据，在非聚簇索引中直接就能够拿到，那就不需要回表再查询聚簇索引。

create index idx_emp_name on t_emp(emp_name);

select emp_id,emp_name from t_emp where emp_name like 'tom%';

⑥小结

聚簇索引与非聚簇索引的原理不同，在使用上也有一些区别：

1. 聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置——主键。非聚簇索引不会影响数据表的物理存储顺序。
2. 一个表只能有一个聚簇索引，因为原始数据只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索。

6、索引的分类[了解]

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。

• 从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引和全文索引。
• 按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。
• 按照字段个数进行划分，分成单列索引和联合索引。

①普通索引

在创建普通索引时，不附加任何限制条件，主要用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在表t_student的字段stuid上建立一个普通索引，查询记录时就可以根据该索引进行查询。

②唯一性索引

使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引。

例如，在表t_student的字段name中创建唯一性索引，那么字段name的值就必须是唯一的。通过唯一性索引，可以更快速地确定某条记录。

③主键索引

主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE，一张表里最多只有一个主键索引。

Why？ 这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。

④单列索引

在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。

⑤多列(组合、联合)索引

多列索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段id、name和gender上建立一个多列索引idx_id_name_gender，只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循最左前缀集合。

⑥全文索引

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引只能创建在CHAR、VARCHAR或TEXT类型及其系列类型的字段上，**查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。**例如，表t_student的字段information是TEXT类型，该字段包含了很多文字信息。在字段information上建立全文索引后，可以提高查询字段information的速度。

[1]自然语言的全文索引

默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索。

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。**在整个索引中出现次数越少的词语，匹配时的相关度就越高。**相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。

这个机制也比较好理解，比如说，一个数据表存储的是一篇篇的文章，文章中的常见词、语气词等等，出现的肯定比较多，搜索这些词语就没什么意义了，需要搜索的是那些文章中有特殊意义的词，这样才能把文章区分开。

[2]布尔全文索引

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册。

限制： MySQL数据库从3.23.23版开始支持全文索引，但MySQL5.6.4以前只有Myisam支持，5.6.4版本以后innodb才支持，但是官方版本不支持中文分词，需要第三方分词插件。在5.7.6版本，MySQL内置了ngram全文解析器，用来支持亚洲语种的分词。测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

随着大数据时代的到来，关系型数据库应对全文索引的需求已力不从心，逐渐被 solr、elasticSearch等专门的搜索引擎所替代。

⑦补充：空间索引

使用参数SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

小结：不同的存储引擎支持的索引类型也不一样 **InnoDB ：**支持 B-tree、Full-text 等索引，不支持 Hash 索引； MyISAM ： 支持 B-tree、Full-text 等索引，不支持 Hash 索引； **Memory ：**支持 B-tree、Hash 等索引，不支持 Full-text 索引； **NDB ：**支持 Hash 索引，不支持 B-tree、Full-text 等索引； **Archive ：**不支持 B-tree、Hash、Full-text 等索引；

第五章 SQL 优化

第一节 准备数据

1、修改配置

为了能够看出 SQL 优化前后的性能差异，我们需要较为大量的数据。生成这些数据我们需要用到 MySQL 中的自定义函数。

但是 MySQL 默认关闭了自定义函数功能，所以我们需要通过修改配置文件来开启这项功能。

# 使用 vim 编辑器打开配置文件
vim /etc/my.cnf

在配置文件末尾增加如下内容：

# 设置为 1 表示开启这项功能
log_bin_trust_function_creators=1

然后重启 MySQL 服务：

systemctl restart mysqld.service

2、执行 SQL 语句生成数据

# 创建数据库
create database db_hr_sys;

# 使用数据库
use db_hr_sys;

# 创建数据库表：部门表
CREATE TABLE `dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 `ceo` INT NULL ,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 创建数据库表：员工表
CREATE TABLE `emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `empno` INT NOT NULL ,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 声明函数：生成随机字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO  
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$

# 声明函数：生成随机数字
DELIMITER $$
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
DECLARE i INT DEFAULT 0;  
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
RETURN i;  
END$$

# 创建存储过程：插入员工数据
DELIMITER $$
CREATE PROCEDURE  insert_emp(  START INT ,  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    #设置手动提交事务
 REPEAT  #循环
 SET i = i + 1;  #赋值
 INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i),rand_string(6),rand_num(30,50),rand_num(1,10000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  #提交事务
END$$

# 创建存储过程：插入部门数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`(  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END$$

# 调用存储过程，向部门表插入1万条数据
CALL insert_dept(10000);

# 调用存储过程，向员工表插入50万条数据
CALL insert_emp(100000,500000);

第二节 慢查询日志

1、需求

在实际开发和项目运行的过程中，需要尽量准确的把查询时间消耗较大的 SQL 语句给找出来。然后有针对性的建立索引，再使用 explain 技术进行分析，找到性能瓶颈，最后调整 SQL 语句。

2、慢查询日志介绍

由 MySQL 负责以日志的形式记录那些执行时间超过阈值的 SQL 语句。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

3、查看及开启

慢查询日志记录功能默认关闭。

#默认情况下slow_query_log的值为OFF
SHOW VARIABLES LIKE '%slow_query_log%';  

命令行开启：

set global slow_query_log=1; 

慢查询日志记录long_query_time时间

SHOW VARIABLES LIKE '%long_query_time%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SET GLOBAL long_query_time=0.1; 

注意: 运行时间正好等于long_query_time的情况，并不会被记录下来。

如果希望修改后永久生效，那就修改my.cnf文件，[mysqld]下增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。也即将如下四行配置进my.cnf文件

slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log 
long_query_time=3
log_output=FILE

4、查看捕获到的记录

①执行一条慢 SQL

select DISTINCT * from emp UNION (SELECT * from emp) UNION (SELECT * from emp)

②查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%'; 

显示结果是：

③查看日志信息内容

④使用mysqldumpslow命令导出

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

-a: 不将数字抽象成N，字符串抽象成S

-s: 是表示按照何种方式排序；

c: 访问次数

l: 锁定时间

r: 返回记录

t: 查询时间

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

-t: 即为返回前面多少条的数据；

-g: 后边搭配一个正则匹配模式，大小写不敏感的；

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

第三节 Explain分析

1、概念

①MySQL 内优化器

MySQL 体系结构中，包含 SQL 解析器、优化器等组件。SQL 解析器解析 SQL 之后，生成解析树。经过验证，解析树正确后，由优化器进一步优化解析树，最终生成一个执行计划（profile）。

②Explain 分析

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。 分析你的查询语句或是表结构的性能瓶颈。

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

2、准备测试数据

create database db_hr;

use db_hr;

CREATE TABLE t1
(
    id      INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL,
    PRIMARY KEY (id)
);
CREATE TABLE t2
(
    id      INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL,
    PRIMARY KEY (id)
);
CREATE TABLE t3
(
    id      INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL,
    PRIMARY KEY (id)
);
CREATE TABLE t4
(
    id      INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL,
    PRIMARY KEY (id)
);
INSERT INTO t1(content)
VALUES (CONCAT('t1_', FLOOR(1 + RAND() * 1000)));
INSERT INTO t2(content)
VALUES (CONCAT('t2_', FLOOR(1 + RAND() * 1000)));
INSERT INTO t3(content)
VALUES (CONCAT('t3_', FLOOR(1 + RAND() * 1000)));
INSERT INTO t4(content)
VALUES (CONCAT('t4_', FLOOR(1 + RAND() * 1000)));

3、id 字段

①id 字段含义

• 一个 select 对应一个 id 唯一的值
• id 值每出现一次，就代表执行 SQL 语句的一个大步骤
• 总体的 SQL 语句执行的『大步骤』越少越好
• 多个 id 值
  • id 值相同：相同 id 值中包含的多条记录可以理解为执行这个大步骤时的各个『小步骤』。按照从上到下的顺序依次执行
  • id 值不同：代表执行这条 SQL 语句需要有多个大步骤，这些大步骤按照 id 值从大到小的顺序值。

②举例

[1]id 值相同的情况

SQL 本身：

select t1.id,t2.id,t3.id,t4.id from t1,t2,t3,t4

应用 Explain 分析：

explain select t1.id,t2.id,t3.id,t4.id from t1,t2,t3,t4

部分执行结果：

[2]id 值不同的情况

EXPLAIN
SELECT t1.id
FROM t1
WHERE t1.id = (SELECT t2.id FROM t2 WHERE t2.id = (SELECT t3.id FROM t3 WHERE t3.content = 't3_354'))

[3]相同的 id 值和不同的 id 值都有

EXPLAIN
SELECT t1.id, (select t4.id from t4 where t4.id = t1.id) id4
FROM t1,
     t2

[4]有子查询但是 id 值相同

explain
select t1.id
from t1
where t1.id in (select t2.id from t2);

这是因为查询优化器将子查询转换为了连接查询。

4、select_type 字段

①字段含义介绍

一条 SQL 语句总体来看：其中可能会包含很多个 select 关键字。每一个 select 代表整个 SQL 语句执行计划中的一次小的查询，而每一个 select 关键字的每一次查询都有可能是不同类型的查询。

select_type 字段就是用来描述每一个 select 关键字的查询类型，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。

而通过查看各个小查询部分扮演的角色，我们可以了解到整体 SQL 语句的结构，从而判断当前 SQL 语句的结构是否存在问题。

②总体介绍字段取值含义

取值	含义
SIMPLE	简单的 select 查询，查询中不包含子查询或者 UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为 primary
SUBQUERY	在 SELECT 或 WHERE 列表中包含了子查询
DEPENDENT SUBQUERY	在 SELECT 或 WHERE 列表中包含了子查询，子查询基于外层
UNCACHEABLE SUBQUREY	表示这个 subquery 的查询要受到外部表查询的影响
DERIVED	在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)。 MySQL 会递归执行这些子查询，把结果放在临时表里
UNION	这是 UNION 语句其中的一个 SQL 元素
UNION RESULT	从 UNION 表获取结果的 SELECT，也就是在 UNION 合并查询结果的基础上，不使用全部字段，选取一部分字段。

具体分析如下：

③SIMPLE

查询语句中不包含UNION、不包含子查询的查询都算作是SIMPLE类型，比方说下边这个单表查询的select_type的值就是SIMPLE：

mysql> EXPLAIN SELECT * FROM t1;

当然，连接查询也算是SIMPLE类型，比如：

mysql> EXPLAIN SELECT * FROM t1 INNER JOIN t2;

④PRIMARY

对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY，比方说：

mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM t2;

从结果中可以看到，最左边的小查询SELECT * FROM s1对应的是执行计划中的第一条记录，它的select_type值就是PRIMARY。

⑤union

对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION，可以对比上一个例子的效果，这就不多举例子了。

⑥union result

MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT，例子上边有。

⑦subquery

如果包含子查询的查询语句不能够转为对应的semi-join的形式（不用管什么是 semi-join，只需要知道这是进一步优化），并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY，比如下边这个查询：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

可以看到，外层查询的select_type就是PRIMARY，子查询的select_type就是SUBQUERY。需要大家注意的是，由于 select_type 为 SUBQUERY 的子查询会被物化（将子查询结果作为一个临时表来加快查询执行速度），所以只需要执行一遍。

⑧DEPENDENT SUBQUERY

如果整体 SQL 语句执行的顺序是：

• 先执行外层查询
• 再执行内层子查询
• 然后外层查询结果中的每一条再去匹配内层子查询结果的每一条

这样，内外两层的查询结果就是相乘的关系。相乘就有可能导致总的查询操作次数非常大。所以经过 explain 分析后，如果发现查询类型是 DEPENDENT SUBQUERY 就需要引起各位注意了——这是一个危险的信号，通常是需要修复的一个问题！

当然，就实际工作中来说：别说 DEPENDENT SUBQUERY，就连 SUBQUERY 都不应该出现。

EXPLAIN
SELECT t1.id, (select t4.id from t4 where t4.id = t1.id) id4
FROM t1,
     t4;

5、table 字段

显示当前这一步查询操作所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是别名。不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，所以 MySQL 规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

6、partitions 字段

代表分区表中的命中情况。如果是非分区表，该项为 null。逻辑上是一个整体的数据，可以在物理层保存时，拆分成很多个分片。分片在分区中保存。数据分片的好处是：

• 方便在很多个不同分区之间方便的移动分片，分摊负载，弹性扩容。
• 给主分片创建复制分片可以增强数据安全性。

7、type 字段 [重要]

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。具体取值参见下表（从上到下，性能越来越好）：

取值	含义
ALL	全表扫描
index	在索引表（聚簇索引、非聚簇索引都算）中做全表扫描
range	在一定范围内查询索引表
ref	通过普通的二级索引列与常量进行等值匹配时来查询某个表
eq_ref	在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref
const	根据主键或者唯一二级索引列与常数进行等值匹配
system	表仅有一行记录，这是const类型的特例，查询起来非常迅速
null	MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

在上述查询方式中，从 eq_ref 开始，条件就很苛刻了，不容易达到。所以实际开发时要求，至少能达到 range 水平，最好能达到 ref。

下面是可以参考的例子：

# 创建数据库表
create table t_emp
(
    emp_id     int auto_increment primary key,
    emp_name   char(100),
    emp_salary double(10, 5),
    dept_id    int
);

create table t_dept
(
    dept_id   int auto_increment primary key,
    dept_name char(100)
);

# emp_id 主键索引
# emp_name 唯一索引
create unique index idx_emp_name_unique on t_emp (emp_name);
# dept_name 普通索引
create index idx_dept_name on t_dept (dept_name);

# 情况一：type 的值是 all
# 原因：由于没有用到任何索引，所以执行全表扫描
explain
select emp_salary
from t_emp;

# 情况二：type 的值是 index
# 原因：查询的是建立了索引的字段，而且没有指定 where 条件。
# 在执行查询时扫描索引的整个 B+Tree
explain
select emp_id
from t_emp;

explain
select emp_name
from t_emp;

explain
select dept_name
from t_dept;

# 情况三：type 的值是 range
# 原因：在一定范围内访问索引 B+Tree

# emp_salary 普通索引
create index idx_emp_salary on t_emp (emp_salary);

explain
select emp_id, emp_name
from t_emp
where emp_salary between 1000 and 5000;

# 情况四：type 的值是 ref
# 原因：通过普通的二级索引列与常量进行等值匹配时来查询
explain
select dept_name
from t_dept
where dept_name = '研发部';

# 情况五：对 t_dept 表的查询中，type 的值是 eq_ref
# 原因：在进行关联查询时，被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问
explain
select emp_id, emp_name, emp_salary
from t_emp e
         left join t_dept td on e.dept_id = td.dept_id;

# type 的值是 const
# 原因：使用常量值查询一个唯一索引，返回唯一一条记录
# 此时需要数据库表中确实有这条对应的记录才能够测试出 const 这个效果
insert into t_emp(emp_name,emp_salary,dept_id) values('aaa',2000.00,1);

explain
select emp_id, emp_name, emp_salary
from t_emp
where emp_name = 'aaa';

8、possible_keys 字段

在查询中有可能会用到的索引列。如果没有任何索引显示 null。

9、key 字段

key 列显示 MySQL 实际决定使用的键（索引），包含在 possible_keys 中。

10、key_len 字段[重要]

key_len 表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要，值越大索引的效果越好——因为值越大说明索引被利用的越充分。

字节数计算方式：

• 索引对应字段类型：
  • 数值类型：最终结果就是数值类型字段宽度（我们关心的是字节数）本身
    • int(11)：我们关心的是 int 类型占 4 个字节，而不关心 11
    • double(10,5)：我们关心的是 double 类型占 8 个字节，而不关心 10,5
  • 字符串类型：查看字符集类型
    • UTF-8：需要给字段长度 × 3
    • GBK：需要给字段长度 × 2
  • 如果是 varchar 这样的变长字符串类型：再 + 2
  • 如果是允许为空的字段：再 + 1

举例：customer_name 字段声明的类型是 varchar(200)，允许为空。

200 × 3 + 2 + 1 = 603

举例：

# 下面分析结果的 key_len 字段的值是 310，我们来看看是怎么算出来的
# 先看 emp_name
# emp_name 是字符串类型，它的字段宽度是 100，字符集是 UTF-8 需要乘 3，是定长字段不需要 +2，允许为空需要 +1，所以：100×3+1 = 301
# emp_salary 是数值类型，本身占 8 个字节，允许为空需要 + 1，所以：8 + 1 = 9
# 总和：301 + 9 = 310
explain select emp_name,emp_salary from t_emp
where emp_name = '李四' or emp_salary = 1000;

11、ref 字段

表示查询条件中，我们的索引列和谁去比较，是常量还是另一张表的字段。

①const

explain select emp_id,emp_name,emp_salary from t_emp where emp_id=1;

②某个字段

explain
select emp_id, emp_name, emp_salary
from t_emp e
         left join t_dept d on d.dept_id = e.dept_id
where emp_id = d.dept_id;

12、rows 字段

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。从优化 SQL 语句的角度来说，这个值通常越小越好。

13、filtered 字段

通过存储引擎从硬盘加载数据到服务层时，受限于内存空间，有可能只能加载一部分数据。filtered 字段显示的值是：已加载数据 / 全部数据 的百分比。只是不显示百分号。

14、extra 字段

顾名思义，Extra 列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑比较重要的额外信息介绍给大家。

下面橙色字体需要适当留意一下：

取值	含义
using where	不用读取表中所有信息，仅通过索引就可以获取所需数据。 言外之意是 select 查询的字段都带有索引。 不管 select 查询多少个字段，这些字段都在索引中。
Using temporary	表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort	当语句中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序” 这里的文件指的是保存在硬盘上的文件。 之所以会用到硬盘，是因为如果查询的数据量太大，内存空间不够，需要在硬盘上完成排序。 如果确实是很大数据量在硬盘执行排序操作，那么速度会非常慢。
Using join buffer	buffer 指缓冲区，该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。 举例来说：where t_name like “%xxx%”，这个条件中的 t_name 字段没有加索引
Impossible where	where 子句中指定的条件查询不到数据的情况
Select tables optimized away	这个值表示目前的查询使用了索引，然后经过优化器优化之后，最终执行的是一个聚合函数，从而让最终的查询结果只返回一行
No tables used	查询语句中使用 from dual 或不含任何 from 子句

15、使用建议

• EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN 不考虑各种 Cache
• EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划

第四节 索引失效

1、取消缓存功能

为了便于测试，避免 MySQL 内的缓存机制干扰分析结果，我们在分析 SQL 语句时取消 SQL 语句的缓存功能。

使用 SQL_NO_CACHE 关键字：

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30;

当然，实际开发时肯定是不会加的。

2、最左原则 [重要]

①有可能用到索引的子句

所有有过滤功能的子句都会将相关字段去和索引尝试匹配：

• ON 子句
• WHERE 子句
• GROUP BY 子句
• HAVING 子句
• LIMIT 子句

②最左原则

简单来说就是：MySQL 在决定是否要应用索引时，会对照 SQL 语句中要过滤的字段的顺序和索引中字段的顺序。那么具体是怎么对照的呢？请看下面的细节：

[1]创建索引

CREATE INDEX idx_age_deptid_name ON emp(age, deptid, NAME);

按照这个索引创建方式，索引中字段的顺序是：age、deptid、NAME

[2]仅查询 age

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30;

索引生效！

key_len 的值为什么是 5 ？

因为我们现在用到的索引字段就是 age 这一个字段，int 类型的字段占 4 个字节，可以为空再 +1。所以是 5。

[3]查询 age 和 deptId

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30
  and deptid = 4;

索引生效！

key_len 的值为什么是 10 ？

因为我们现在用到的索引字段是 age和deptId 两个字段都是int类型，int 类型的字段占 4 个字节，可以为空再 +1，所以是 10

[4]查询 age、deptId 和 name

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30
  and deptid = 4
  AND emp.name = 'abcd';

索引生效！

单值索引和复合索引的选择

在实际开发中，一个数据库表包含多个字段，其中有若干个字段有很大几率出现在 where 或其他有可能触发索引的子句中。那么我们倾向于创建『复合索引』，而不是『单值索引』。

因为一个复合索引能够涵盖到其中包含的每个字段；而给每一个字段分别创建单值索引会生成更多的索引表，增加切换、磁盘存储、I/O 等方面的开销。

key_len 的值为什么是 10：使用到三个索引， age和deptId 两个字段都是int类型，int字段占4字节，可以为空加1，那么是varchar类型，宽度20，变长字符串类型+2，可以为空加1，总就是（4+1）+（4+1）+（20*3+2+1）= 73

[5]查询 deptId、name 和 age

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE
  deptid = 4
  AND emp.name = 'abcd'
  and  emp.age = 30;

看上去索引是生效的，但是很明显顺序不一致，不满足最左原则，本来是不应该生效的：

• 查询顺序：deptId、name、age
• 索引顺序：age、deptid、NAME

但是为什么索引生效了呢？其实原本是不应该生效的，但是此时是 MySQL 的 SQL 优化器调整了解析树，使查询字段符合了索引顺序，这才让索引生效了。

但是尽管优化器能够帮助我们进行调整，但是调整本身也需要付出代价，产生系统开销。所以我们开发时还是要尽量和索引中字段顺序一致。

[6]仅查询 deptId

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE deptId = 5;

索引没有生效！

但是很奇怪，deptId 这个字段明明是在索引中呀？这是因为本次查询没有满足最左原则。

在索引中，age 字段在最左边，现在查询的 deptId 作为第一个查询的字段不是 age，这就违背了最左原则。

为什么 MySQL 会如此执着于『最左』字段？ 这是因为生成索引所在的 B+Tree 的时候，需要对索引值进行排序。那么如果我们指定的是联合索引，那么将涉及到多个字段的排序。例如：age、deptId、name这三个字段要排序的话，肯定优先根据 age 排序；然后在 age 值有相同数据时对 deptId 排序，以此类推。 所以我们在实际查询时，需要首先根据 age 字段在索引 B+Tree 中进行二分法查找。此时如果没有提供 age 字段，那将无法使用索引。

[7]查询 deptId 和 name

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE deptId = 5 and name = "aaa";

索引没有生效！同样是因为违背了最左原则。

[8]查询 deptId 和 name 并按 age 排序

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE deptId = 5 and name = "aaa" order by age
;

索引没有生效！这是因为 order by 没有过滤功能，不会触发索引。相当于查询过程中没有 age 字段参与。

[9]查询 age 和 name

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30
  AND emp.name = 'abcd';

此时联合索引生效，但是 key_len 字段的值是 5，而不是 68，说明 name 字段并没有按照索引去查询。对 name 字段来说，索引没有生效。

[10]结论

要遵循最左原则，查询字段中至少要有索引中的最左字段作为过滤条件存在。而且就最左原则本身来说，它要求索引最左字段在查询顺序中也最左。只不过只要最左字段出现，优化器会帮我们调整到查询顺序中的最左。而且还有一个要求是：中间不能断。中间一旦断开，后面的就都无法使用索引了。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nLwZWEaE-1647351323988)(img/TP46.png)]

where 子句部分和最左原则对照，看是否生效的口诀：带头大哥不能死，中间兄弟不能断

3、索引失效的其他情况

①范围查询

分析查询：

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30
  AND emp.deptId > 20
  AND emp.name = 'abc';

分析结果：

看起来仍然是生效的，但是我们再另外创建一个索引，把范围查询对应的 deptId 放在后面：

CREATE INDEX idx_age_deptid_name_2 ON emp(age, NAME, deptid);

把查询顺序也改一下：

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30
  AND emp.name = 'abc'
  AND emp.deptId > 20;

分析结果：

请大家注意两点变化：

• 变化1：MySQL 会优先选择新创建的索引 idx_age_deptid_name_2
• 变化2：key_len 数值变大了很多。

分析：

• key_len 的计算角度：age 字段贡献了 5，deptId 字段贡献了 5，而 name 字段如果应用了索引，那么它应该贡献 63。但是最终 key_len 是 10，说明索引生效的字段是 age 和 deptId
• type 的取值角度：range，说明查询的类型是范围查询，在当前 SQL 语句中 deptId 做的就是范围查询
• 解决方案角度：把 deptId 放在后面，三个字段的索引就都生效了。说明范围查询并不是祸害自己，而是祸害后面的查询条件。

结论：

• 注意点1：创建索引时应该把容易做范围查询的字段往后放
• 注意点2：实际编写 SQL 语句时，做范围查询的字段参照索引中的顺序，也是往后放

进一步的问题：如果范围查询有多个呢？

创建索引：

CREATE INDEX idx_age_deptid_name_3 ON emp(NAME, age, deptid);

执行分析：

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.name = 'abc'
  AND emp.age > 30
  AND emp.deptId > 20;

查看分析结果，发现 key_len 是 68，68 = 63 + 5。说明生效的字段是 name、age，deptId 还是没有生效。这就说明范围查询即使放在后面也只有第一个生效。

②在 where 子句条件中使用函数

首先删除上例索引：

# 删除上例的索引
drop index idx_age_deptid_name on emp;
drop index idx_age_deptid_name_2 on emp;
drop index idx_age_deptid_name_3 on emp;

创建新索引：

create index idx_name on emp(name);

分析查询（left 函数表示取 name 字段的前三个字符）：

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE left(name, 3) = "abc"
;

结论：使用了 left() 函数导致了索引失效。

③不等于

所有不等于操作都会导致索引失效：

• !=
• <>
• not

测试的语句：

# 只针对 name 这一个字段创建一个新的索引（上例已建）
# create index idx_name on emp(name);

# 分析对 name 查询的 SQL 语句
explain select sql_no_cache * from emp where name != 'aaa';
explain select sql_no_cache * from emp where name <> 'bbb';
explain select sql_no_cache * from emp where name is not null;

分析结果：

④like

并不是所有 like 查询都会导致索引失效：

• 生效：左边是确定的
• 失效：左边不确定

[1]情况一：like 条件没有 %

explain select sql_no_cache * from emp where name like 'aaa';

索引生效！

[2]情况二：like 条件把 % 放右边

explain select sql_no_cache * from emp where name like 'aaa%';

索引生效！

[3]情况三：like 条件把 % 放中间

explain select sql_no_cache * from emp where name like 'aaa%bbb';

索引生效！

[4]情况四：like 条件把 % 放左边

explain select sql_no_cache * from emp where name like '%bbb';

索引没有生效！

当然，左右都有 % 的情况和这里一样。

⑤涉及类型转换

所谓类型转换就是指：我们给查询条件传入的参数和原本的类型不一致。但是这种情况不是必然会导致索引失效。

• 失效情况举例：让 char 类型的字段和 123 比较
• 有效情况举例：让 int 类型的字段和 ‘123’ 比较

[1]失效情况

explain select sql_no_cache * from emp
where name=123;

分析结果：

[2]仍然有效的情况

#创建索引
CREATE INDEX idx_age ON emp(age);
#explain分析
explain
select sql_no_cache *
from emp
where age = '123';

分析结果：

[3]结论

在这种情况下，加上引号肯定没错。

4、小练习

假设目前我们有索引的情况是：index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 where b = 3 and c = 4 where c = 4	N，违背最左原则
where a = 3 and c = 5	使用到a， 但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，c 被 b 断给了
where a is null and b is not null	is null 支持索引，但是is not null 不支持。所以 a 可以使用索引，但是 b不可以使用
where a <> 3	不能使用索引
where abs(a) =3	不能使用索引，因为使用了函数
where a = 3 and b like ‘kk%’ and c = 4	Y,使用到a,b,c。虽然用到了 like，但是左边是确定的。
where a = 3 and b like ‘%kk’ and c = 4	Y,只用到a, %不能在最左边
where a = 3 and b like ‘%kk%’ and c = 4	Y,只用到a,%不能在最左边
where a = 3 and b like ‘k%kk%’ and c = 4	Y,使用到a,b,c

5、小结

• 对于单键索引，尽量选择过滤性更好的字段来创建索引（例如：手机号，邮件，身份证）。这样更容易过滤掉大量数据，为后面操作减轻负担。
• 在选择组合索引的时候，过滤性最好的字段在索引字段顺序中，位置越靠前越好。这样可以更早过滤掉大量数据。
• 选择组合索引时，尽量包含where中更多字段的索引，这是为了让 where 条件尽可能更多的享受到索引带来的福利。
• 组合索引出现范围查询时，尽量把这个字段放在索引次序的最后面
• 尽量避免造成索引失效的情况

第五节 关联查询优化

1、数据准备

#分类
CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
#图书
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);
 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

2、left join

①测试

开始是没有加索引的情况。下面开始explain分析：

EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;

分析结果：

②添加索引优化：

ALTER TABLE book ADD INDEX Y (card); 
ALTER TABLE class ADD INDEX X (card); 

重新分析的结果：

看这个分析结果发现：在 class 表上添加的索引起的作用不大。

③结论

• 小表驱动大表
  • 小表：相对来说记录较少的表
  • 大表：相对来说记录较多的表
• 驱动方式识别
  • left join：左边驱动右边（此时把小表放在左边）
  • right join：右边驱动左边（此时把小表放在右边）
• 加索引的方式：通常建议在大表（被驱动）的表加索引，效率提升更明显。
• 原因：
  • 原因1：被驱动表加了索引之后，收益更大。从 ALL -> ref
  • 原因2：外连接首先读取驱动表的全部数据，被驱动只读取满足连接条件的数据。

3、inner join

换成inner join（MySQL自动选择驱动表）

# 特意将 book 放在 from 子句，去对 class 表做内连接
EXPLAIN
SELECT SQL_NO_CACHE *
FROM book
         inner JOIN class ON class.card = book.card;

分析结果：

MySQL 还是选择了 class 作为驱动表。

此时需要给 book 表增加较多条记录，让两个表数据量差异明显才有这个效果。

4、小结

• 保证被驱动表的 join 字段被索引。join 字段就是作为连接条件的字段。
• left join 时，选择小表作为驱动表（放左边），大表作为被驱动表（放右边）
• inner join 时，mysql 会自动将小结果集的表选为驱动表。
• 子查询尽量不要放在被驱动表，衍生表建不了索引
• 能够直接多表关联的尽量直接关联，不用子查询

第六节 子查询优化

1、结论

在实际开发中，能够不用子查询就尽量不用。

2、实际测试

①测试子查询效果

添加索引：

create index idx_ceo on dept(ceo);

分析语句：

explain SELECT *
FROM emp a
WHERE a.id NOT IN
      (SELECT b.ceo FROM dept b WHERE b.ceo IS NOT NULL);

分析结果：

②测试关联查询效果

分析语句：

explain SELECT a.*
FROM emp a
         LEFT JOIN dept b
                   ON a.id = b.ceo;

分析结果：

③综合对比

• 从执行顺序来说：关联查询的 id(1,1) 比 子查询的 id(1,2) 要好
• 从查询的类型来说：ref 比 index 要好两个级别

第七节 排序分组优化

1、排序

①结论

• order by 子句需要配合 limit 子句才能让索引生效。
• 如果涉及多个字段的排序，那么这些字段排序的方向要一致（要升序都升序，要降序都降序），否则索引失效。

②测试基本情况

索引情况：

[1]只有 order by 的情况

分析语句：

EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER  BY age;

分析结果：

[2]配合 limit 子句的情况

分析语句：

EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age limit 10;

③测试排序方向的影响

删除上例索引：

drop index idx_age on emp;

创建新索引：

create index idx_age_deptId on emp(age, deptId);

分析语句：排序方向一致的情况

EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age desc,deptId desc limit 10;

分析结果：

分析语句：排序方向不一致的情况

EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age desc,deptId asc limit 10;

分析结果：

2、双路排序和单路排序[了解]

①filesort

如果 order by 指定的字段没有建立索引，此时 MySQL 就无法在内存完成排序了，而是执行 filesort——也就是操作硬盘完成排序。

执行 filesort 又分两种不同情况：

• 双路排序：
  • 特征：两批 I/O
  • 对应的 MySQL 版本：4.1 之前
  • 大致工作机制：
    • 第一次读取硬盘：读取『行指针』以及『order by 子句指定的字段』
    • 排序：对已读取的『order by 子句指定的字段』进行排序
    • 第二次读取硬盘：根据『行指针』读取 select 子句中指定的其他字段
  • I/O方式：随机 I/O
• 单路排序：
  • 特征：一批 I/O
  • 对应的 MySQL 版本：4.1 之后
  • 大致工作机制：
    • 读取硬盘：读取 select 子句指定的所有列
    • 排序：按照 order by 列在 buffer 中对它们进行排序
  • I/O方式：顺序I/O

②单路排序需要注意的问题

单路排序在内存的缓冲区中执行排序，所以需要更大的内存空间。我们管这个缓冲区叫：sort_buffer。此时需要注意：如果为了排序而取出的数据体积大于 sort_buffer，这就会导致每次只能取 sort_buffer 容量大小的数据。所以这种情况下，数据的加载和排序是分段完成的。在这个过程中，MySQL 会创建临时文件，最后再把每段数据合并到一起。

所以 sort_buffer 容量太小时会导致排序操作产生多次 I/O。单路本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

③单路排序的优化

调整下面的三个参数：

参数名称	参数含义	调整建议
sort_buffer_size	单路排序缓冲区的大小	适当增大
max_length_for_sort_data	select 子句要查询的所有字段的总宽度和该参数比较： 大于该参数：使用双路排序 小于等于该参数且排序字段不是TEXT、BLOB类型：使用单路排序	适当增大
select 子句中查询的字段		尽量减少

对 sort_buffer_size 的补充说明： 不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的 1M-8M之间调整。 MySQL5.7，InnoDB 存储引擎默认值是 1048576 字节，1MB。 对 max_length_for_sort_data 的补充说明： max_length_for_sort_data 不能设的太高，否则数据总容量超出 sort_buffer_size 的概率就增大。明显症状是高的磁盘 I/O 活动和低的处理器使用率。建议设置在 1024-8192 字节之间。

最终目标：

• 尽量能够基于索引执行排序
• 如果确实要执行 filesort，那么尽量使用单路排序，而且尽量让单路排序只做一次 I/O。

④select * 会影响排序

在实际业务功能开发过程中，禁止在 select 子句中使用 * 号代表全部字段。如果确实需要查询全部字段，那就把全部字段都写明。其实这个时候更要注意的是：是不是真的要查全部字段。

具体从 SQL 优化的角度来说，select * 会导致我们加载很多没有创建索引的字段到内存中，增加了数据体积超过 sort_buffer_size 的风险。有可能会导致单路排序变成双路排序，性能下降。

3、GROUP BY优化

Group by 分组优化原则如下：

• group by 先排序再分组，同样遵照最左原则
• 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
• where 高于 having，能写在 where 限定的条件就不要写在 having 中了

举个例子帮助大家理解：

• 假设有 100W 条记录待筛选
• 有一个 X 条件能够过滤掉 90W 条
  • X 条件用在 where 子句：后续 GROUP BY 操作针对 10W 条数据操作
  • X 条件用在 having子句：后续 GROUP BY 操作还是针对原来的 100W 条数据操作，操作完了还要再干掉 90W 条

所以在整个 SQL 查询语句中，能够将数据过滤掉的条件在不影响查询结果的前提下都要尽早使用，尽早过滤数据，缩小要操作的数据量，让后续操作减轻负担。

关闭 ONLY_FULL_GROUP_BY 模式：

• 查看当前 SQL 模式

select @@GLOBAL.sql_mode;

• 关闭 ONLY_FULL_GROUP_BY 模式

  修改 /etc/my.cnf 配置文件，在配置文件末尾增加一行：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

• 重启 MySQL 服务
• 查看修改完成后的效果

select @@GLOBAL.sql_mode;

第六章 视图

实际开发时，现在越来越多『长、难、复杂』SQL。这种 SQL 语句编写、维护较为困难。所以我们可以将这一的 SQL 语句创建为『视图』，这个视图生成了一张虚拟的表。下次再有需要时，执行这个视图即可得到相同的结果。

视图是将一段查询 SQL 封装为一个虚拟的表。 这个虚拟表只保存了 SQL 逻辑，不会保存任何查询结果。

主要作用:

封装复杂sql语句，提高复用性 逻辑放在数据库上面，更新不需要发布程序，面对频繁的需求变更更灵活 常用场景:

共用查询结果 报表 语法:

创建：

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name  WHERE condition  

使用：

#查询
select * from view_name 
#更新
CREATE OR REPLACE VIEW view_name 
AS SELECT column_name(s) FROM table_name WHERE condition

第七章 MySQL复制架构

第一节 概述

1.1 数据拓展

• 热备份：数据库在运行的过程中，对数据进行备份操作。相对的，还有冷备份，冷备份需要停机，然后对数据进行备份操作。
• 多活：所谓的多活，就是让数据库机器节点会存在多个，避免单点情况的出现。
• 故障切换：当一台数据库物理机出现异常状况时，可以自动的切换到其他物理机上。
• 读写分离：当存在多台数据库物理机，将读写操作分别交给不同的机器完成。
• 负载均衡：假设当存在多台数据库物理机接收读请求时，多个请求会均匀的分配到不同的机器上，避免大量请求压在某一台机器上。

1.2 常见架构

没有百分百的完美架构，只有适合的架构

理解mysql的分库分表，先了解mysql的架构设计,在mysql架构中，经常会使用到的就是读写分离，此设计理念的基础上常见架构有: 一主一从或多从、主主复制、级联复制、主主与级联复制结合。

一主一从或多从: 一个mysql数据库主节点,一个或者多个从节点.主节点与从节点进行数据同步

主主复制: 两个mysql主节点,主节点与主节点之间进行数据同步

级联复制: 类似一主多从架构,但是从节点分级同步

主主与级联复制结合: 双主节点同步,同时从节点分级同步

第二节 主从模式

1、主从简介

主从模式是使用的最多的MySQL高可用架构。

存在一台master作为写机，一个或多个slave作为读机,实现读写分离。之所以这么设计是因为在实际的情况下，读的请求量一般是远远大于写请求。架构图如下

优点:

读与写的节点分离，数据写入master节点后，再由master节点将数据复制到slave节点上

缺点:

• master是单点存在的，如果要对master进行停机维护，无法接收写请求
• master需要将写入数据复制到各个slave节点，复制是有一定的时间延迟的，因此有可能出现查询数据不一致
• 对master进行停机维护，需将某一个slave提升为新的master节点，选举规则需要进行自定义
• 当slave被提升为新的master后，可能会造成新的master节点与旧master的数据不一致

2、 主从搭建

①创建 docker 容器

# 搜索 MySQL 镜像
docker search mysql

# 下载 MySQL 镜像
docker pull docker.io/mysql:5.7

# 安装 MySQL 容器
# 服务一
# -p 参数格式：宿主机端口号:容器内程序的端口号
# -e MYSQL_ROOT_PASSWORD 给 MySQL 的 root 用户设置密码
# -v 参数指定宿主机内的目录，用来给 docker 容器设定配置文件等等
docker run --name mysqlm1 
-p 10000:3306 
--privileged=true 
-it 
-e MYSQL_ROOT_PASSWORD=123456 
-e MYSQL_USER=user
-e MYSQL_PASSWORD=pass 
-v /lyruse/mysqlm1/conf:/etc/mysql/conf.d 
-v /lyruse/mysqlm1/data/:/var/lib/mysql 
-v /lyruse/mysqlm1/logs/:/var/log/mysql 
-d mysql:5.7

#服务二
docker run --name mysqls1 
-p 20000:3306 
--privileged=true 
-it 
-e MYSQL_ROOT_PASSWORD=123456 
-e MYSQL_USER=user  
-e MYSQL_PASSWORD=pass 
-v /lyruse/mysqls1/conf:/etc/mysql/conf.d 
-v /lyruse/mysqls1/data/:/var/lib/mysql 
-v /lyruse/mysqls1/logs/:/var/log/mysql 
-d mysql:5.7

②配置 docker 容器

在两台机器上新增配置文件

[1]主机

由于我们映射了目录，所以在宿主机中编辑配置文件即可。my.cnf 这个配置文件原本没有，需要我们自己手动创建。

宿主机中的路径为 /lyruse/mysqlm1/conf/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

character_set_server=utf8
init_connect='SET NAMES utf8'

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

lower_case_table_names=1
#指定主机号，不允许出现重复
server-id=134
#开启binlog
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=1

#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=10000

[2]从机

/lyruse/mysqls1/conf/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

character_set_server=utf8
init_connect='SET NAMES utf8'

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

lower_case_table_names=1
#指定主机号，不允许出现重复
server-id=130
#开启binlog
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=1

#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=10000

❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤

修改 my.cnf 后记得重启 MySQL 容器！否则配置不会生效！

[root@demo conf]# docker restart mysqlm1
mysqlm1
[root@demo conf]# docker restart mysqls1
mysqls1

❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤

③配置 master

在master的docker容器中添加MySQL权限，开启备份机复制，并且设置备份用户信息

#在134服务中进入 MySQL 容器
docker exec -it mysqlm1 /bin/bash

#登录到 MySQL
mysql -u root -p
EnterPassword:123456

#==================== 在 MySQL 中的操作 ===========================
#添加权限
GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'repluser'@'%' IDENTIFIED BY '123456';

#刷新权限
FLUSH PRIVILEGES;

#退出MySQL
quit;
#================================================================

#退出docker容器
exit

#重启容器
docker restart mysqlm1

#查看m1的binlog信息（需要进入 docker 容器，然后再登录 MySQL 执行）
show master status;

④配置 slave

在slave中进入到MySQL容器，设置master信息，用于标注当前slave的master是谁

#语法
change master to master_host='通过宿主机访问master的ip',master_port=docker对外暴露的宿主机master的端口号,master_user='repluser',master_password='123456',master_log_file='master中的binlob文件',master_log_pos=master中的position位置信息;
#命令
change master to master_host='192.168.119.129',master_port=10000,master_user='repluser',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=154;

完成后，还需要开启slave中的IO和SQL线程，这两个线程主要用于slave中进行数据备份。

• IO 线程：从日志文件中读取数据，其中最关键的就是用来同步数据的 SQL 语句
• SQL 线程：执行从日志文件中读取到的 SQL 语句

可以先查看slave中这两个线程的状态

show slave status\G;

我们发现在slave中，这两个线程是关闭的，需要将这两个线程进行开启

#开启（在 docker容器中登录 MySQL 后执行）
start slave;

到此，mysql主从复制就已经搭建完毕

⑤测试

[1]查看主从相关信息

查看slave中的binlog是否已经开启

show global variables like "%log%";

查看master、slave中的进程信息

# m1和s1分别输入
show processlist;

m1节点内容

s1节点内容

[2]数据库同步测试

在m1节点创建一个数据库

在s1节点中查看数据库

[3]表同步测试

在m1中创建一张表

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

在s1中查看表

[4]数据同步测试

在m1中新增一条数据

INSERT INTO `t_user` VALUES ('1', 'zhangsan', '123', '北京');

在s1中查看数据

[5]反向同步数据

在s1中新增一条数据

INSERT INTO `t_user` VALUES ('2', 'lisi', '123', '上海');

在m1中查看数据

结论：从服务中新增数据无法同步到主服务中去，主从同步是单向的！

[6]数据修复

如果因为各种原因，发现 slave 中的数据和 master中不一致，可以参考下面的步骤恢复（下面的步骤相当于在 slave 中推倒重来）：

• 停止 slave 相关复制线程

  stop slave;

• 删除 slave 中同步过来的数据库

  drop database db_hr;

• 重新设定主从关系

  change master to master_host=‘192.168.119.129’,master_port=10000,master_user=‘repluser’,master_password=‘123456’,master_log_file=‘mysql-bin.000002’,master_log_pos=154;

• 重新启动 slave 中的相关复制线程

  start slave;

3、主从复制原理

• 异步操作：不需要等待
• 同步操作：需要等待

①异步复制

[1]流程

• 在主服务器写操作举例：insert 语句。
• 事务提交到master，master确认修改已保存。
• master接收到应用事务提交请求后，更新内部的binlog日志，让mysql引擎执行事务操作，并返回给客户端执行结果信息。同时在master中会存在一个事件监听，其会一直监听着master中binlog日志文件的改变，一旦发现日志文件发生改变，触发dump线程
• dump线程被触发后，通知slave中的IO线程：现在有事务操作要进行同步
• slave中IO线程接收到通知后，会从slave中relay-log.info文件中获取slave中的binlog日志文件和pos位置信息。接着会把这部分信息发送给master的dump线程。发送信息的含义是：告诉master，我上次读取到了哪里，这次还从上次读取到的位置继续读取。
• master的dump线程收到这些信息后，会根据slave发送的binlog日志文件和pos位置，将最新的binlog日志和pos位置后面的内容同步给slave的IO线程
• slave的IO线程接收到这些信息后，会将这部分内容同步到slave中的relay-bin文件中
• 当relay-bin文件发生改变后，触发slave线程执行sql操作【异步】
• 当slave向relay-bin写入完成后，会向master返回一个ACK消息，同步成功。
• 对于这一系列的操作，可以发现master和slave在进行同步时是以异步的方式完成的，master写入完binlog后，会马上通过引擎进行事务提交并向客户端返回响应，对于与slave同步的操作，则是异步完成的。

[2]评价

• 优点
  • 效率高
• 缺点
  • 可能出现数据不一致

②半同步复制

[1]简介

半同步复制与异步复制的工作流程大体相似

不同点：当master中的binlog日志写入完成后，其不会马上通过引擎进行事务提交，而会处于等待，等到slave同步完成向master返回ACK通知后，才会唤醒等待，继续向下执行。

等待的时长，默认为10秒，但该时间可以配置。尽量的避免了主从数据不一致，但造成吞吐量的降低。

兜底方案：使用半同步复制进行备份时slave节点挂掉了，那么当master等待10秒后，仍然会进行引擎提交，同时会将半同步复制切换为异步复制。等到slave节点重启后，又会自动的从异步复制切换到半同步复制。

[2]搭建

进入MySQL容器，加载lib，主从节点都要配置，因为主从节点间会存在切换。

# 这两条 install 命令需要在 m1 中都执行一遍，然后在 s1 中也都执行一遍
install plugin rpl_semi_sync_master soname 'semisync_master.so';
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
#查看插件信息
show plugins;

启动半同步：

#先启用从库，再启用主库

从库：set global rpl_semi_sync_slave_enabled= 1;   # 1：启用，0：禁止

主库：
     set global rpl_semi_sync_master_enabled= 1;   # 1：启用，0：禁止
     set global rpl_semi_sync_master_timeout=10000;   # 单位为ms

从库重启IO Thread

stop slave io_thread;
start slave io_thread;

主库查看启动状态

#查询状态信息
show global status like "%sync%";

#查询参数信息
show global variables like '%sync%';

半同步复制效果测试

• 正常的向master中添加数据，slave可以进行正常数据更新

• 关闭slave的IO,再次向master中添加数据

# 从库执行操作：关闭 I/O 线程
stop slave io_thread;

# 主库插入数据
INSERT INTO `t_user` VALUES ('3', 'ding', '111', '武汉');

此时复制机制会由半同步复制转换为异步复制，当再次向master中添加数据，不会再次出现等待

• slave中重新开启IO Thread

start slave io_thread;

异步复制会再次转换为半同步复制，可以查看master中打印日志信息：

在slave IO Tthread关闭这段时间内的数据，会同步到slave中，不会出现数据丢失

第三节 主主复制

1、简介

对于主从复制来说，其内部会存在一台master以及一台或多台slave。但有一个非常明显的问题，master是单点存在。一旦master宕机，则无法进行数据的写入。为了解决这个问题，可以使用主主复制架构。

主主复制架构两个实例互为主从。并进行读写分离，架构图如下：

在此架构中，两台master会进行双向复制，为什么这么做呢？ 因为假设现在负责写的master宕机了，那么写的工作则会交给之前负责读的服务器来完成，相当于它即负责写又负责读。等到原先负责写的master恢复了，其在继续负责写工作。 反之亦然。因此才需要两者间进行双向复制。

缺点: 读请求的并发量过大，服务可能产生宕机, 主主复制架构直接使用的情况较少。

2、主主搭建

①创建容器

docker run --name mysqlm2 -p 30000:3306 --privileged=true -it -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_USER=user  -e MYSQL_PASSWORD=pass -v /lyruse/mysqlm2/conf:/etc/mysql/conf.d -v /lyruse/mysqlm2/data/:/var/lib/mysql -v /lyruse/mysqlm2/logs/:/var/log/mysql -d mysql:5.7

docker run --name mysqlm3 -p 40000:3306 --privileged=true -it -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_USER=user  -e MYSQL_PASSWORD=pass -v /lyruse/mysqlm3/conf:/etc/mysql/conf.d -v /lyruse/mysqlm3/data/:/var/lib/mysql -v /lyruse/mysqlm3/logs/:/var/log/mysql -d mysql:5.7

②配置

/lyruse/mysqlm2/conf/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

character_set_server=utf8
init_connect='SET NAMES utf8'

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

lower_case_table_names=1
#指定主机号，不允许出现重复
server-id=1341
#开启binlog
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=1

#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=10000

/lyruse/mysqlm3/conf/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

character_set_server=utf8
init_connect='SET NAMES utf8'

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

lower_case_table_names=1
#指定主机号，不允许出现重复
server-id=666
#开启binlog
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=1

#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=10000

❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤

修改 my.cnf 后记得重启 MySQL 容器！

❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤

添加slave的相关配置, 虽然是主主模式,也要添加从用户（供从机访问的用户）。由于 m2 和 m3 都有可能作为从机访问对方（此时对方是主机），所以在 m2 和 m3 都需要创建 repluser 用户。

#添加权限
GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *.* TO 'repluser'@'%' IDENTIFIED BY '123456';
#刷新权限
FLUSH PRIVILEGES;

#在m2/m3服务器上运行
show master status;

#执行主主关联（参考 m2 的状态设置 m3；参考 m3 的状态设置 m2）
#设置m2。因为m2要去连m3，所以这里要设置m3的IP地址和端口号。
change master to master_host='192.168.119.129',master_port=40000,master_user='repluser',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=629;

#设置m3。因为m3要去连m2，所以这里要设置m2的IP地址和端口号。
change master to master_host='192.168.119.129',master_port=30000,master_user='repluser',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=629;

#主主同步生效：也就是说m2和m3都需要执行
start slave;

查看master 129的进程列表：show processlist;

slave131的进程列表：show processlist;