MySQL知识点整理汇总_mysql语句的主要学习内容

---

前言

2020年暑假期间我开始接触 MySQL，通过阅读了几本相关书籍以及网上的一些教程，我逐渐了解了 MySQL 的入门级内容。在暑假期间学完 MySQL 的基础知识后，我便将其搁置在一旁，没有再进行进一步的探索。

如今，我想要再次拾起 MySQL 时，却发现其中的一些语法和规则已经差不多遗忘。对 MySQL 进阶的需求，再加上以前学过的每本书籍（或每个教程）的知识侧重点偶尔不同，因此我打算用这篇博客来记录我所学习到的 MySQL 重要知识点（包括基础知识与进阶内容）。

---

一、数据库与SQL

这一部分内容将介绍数据库与 SQL 的基本理论。

1. 数据库与数据库管理系统

数据库（Database，DB）是指将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合。

而用来管理数据库的计算机系统称为数据库管理系统（Database Management System，DBMS）。

数据库管理系统的种类有：

• 层次数据库（Hierarchical Database，HDB）：把数据通过层次结构（树形结构）的方式表现出来。
• 关系数据库（RDB）：采用由行和列组成的二维表来管理数据，类似于 Excel 表。它使用专门的 SQL（Structured Query Language，结构化查询语言）对数据进行操作，这种类型的 DBMS 称为关系数据库管理系统（Relational Database Management System，RDBMS）。
• 面向对象数据库（OODB）：把数据以及对数据的操作集合起来以对象为单位，面向对象数据库就是用来保存这些对象的数据库。
• XML数据库（XMLDB）：实现对 XML 形式的数据进行高速处理。
• 键值存储系统（KVS）：单纯用来保存查询所使用的主键（Key）和值（Value）的组合

2. 关系数据库

很明显，MySQL 属于关系数据库中的内容。

• RDBMS的常见系统结构是客户端 / 服务器类型（C/S类型），通过从客户端向服务器端发送SQL语句来实现数据库的读写操作：
  • 服务器指的是用来接收其他程序发出的请求，并对该请求进行相应处理的程序（软件），或者是安装了此类程序的设备（计算机）。
  • 客户端是指向服务器发出请求的程序（软件），或者是安装了该程序的设备（计算机）。
• 关系数据库采用由行和列组成的二维表来管理数据，我们习惯将表的行称之为记录， 将表的列称之为字段。根据 SQL 语句的内容返回的数据必须是二维表的形式 。
• 关系数据库必须以行为单位进行数据读写，假设我们将行和列交汇的方格称为单元格。 那么一个单元格中只能输入一个数据。

3. MySQL语句的种类

MySQL语句的种类主要分为以下 3 类：

• DDL（Data Definition Language，数据定义语言）
  用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL 包含以下几种指令：
  • CREATE ： 创建数据库和表等对象
  • DROP ： 删除数据库和表等对象
  • ALTER ： 修改数据库和表等对象的结构
• DML（Data Manipulation Language，数据操纵语言）
  用来查询或者变更表中的记录。DML 包含以下几种指令：
  • SELECT ：查询表中的数据
  • INSERT ：向表中插入新数据
  • UPDATE ：更新表中的数据
  • DELETE ：删除表中的数据
• DCL（Data Control Language，数据控制语言）
  用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令：
  • COMMIT ： 确认对数据库中的数据进行的变更
  • ROLLBACK ： 取消对数据库中的数据进行的变更
  • GRANT ： 赋予用户操作权限
  • REVOKE ： 取消用户的操作权限

4. MySQL语句的基本书写规则

• MySQL 语句以分号（;）为结尾，它的关键字、表名和列名不区分大小写，但是插入到表中的数据是区分大小写的。
• 数据库的名称只能使用小写字母。
• 字符串和日期常数需要使用单引号（'）括起来（MySQL 也可以使用双引号（"）），单词之间需要使用半角空格或者换行符进行分隔。
• 数字常数无需加注单引号，直接书写数字即可。

二、MySQL语句的两大顺序

在这里给出 MySQL 语句的书写顺序与执行顺序。关于语句中的各子句含义与用法，我们会在后续逐个解释。

1. MySQL 语句的书写顺序

①SELECT →②FROM →③WHERE →④GROUP BY →⑤HAVING →⑥ORDER BY →⑦LIMIT

2. MySQL 语句的执行顺序

①FROM →②WHERE →③GROUP BY →④HAVING →⑤SELECT →⑥ORDER BY →⑦LIMIT

三、表的创建、删除与定义更新

这一部分内容将介绍如何创建和删除数据库、表，同时对创建表的语法中的约束与数据类型进行详细解释，还会介绍如何对表定义进行更新。

1. 表的创建

①创建&使用数据库

-- 创建数据库
CREATE DATABASE <数据库名称>;
-- 可以在创建时指定默认编码
CREATE DATABASE <数据库名称> DEFAULT CHARACTER SET utf8; 

-- 选择要操作的数据库
USE <数据库名称>;
1
2
3
4
5
6
7

②创建表

CREATE TABLE <表名>
(<列名1> <数据类型> <该列所需的约束>,
 <列名2> <数据类型> <该列所需的约束>,
 <列名3> <数据类型> <该列所需的约束>,
 ...
 <该表的约束1>, <该表的约束2>, ...
 )ENGINE=<数据库引擎> COMMENT='<注释信息>';
1
2
3
4
5
6
7

1> 命名规范
只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称， 名称必须以半角英文字母开头且不能重复。

2> 数据类型
所有的列必须指明数据类型，数据类型主要分为：数字型、字符型和日期型。细分来说，有以下数据类型：

既然变长数据类型这样灵活，为什么还要使用定长数据类型？回答是因为性能，MySQL处理定长列远比处理变长列快得多。

3> 约束
在定义每一列的时候，我们可以在最后为该列加上相关的约束，例如：

注意：

• 我们也可以在表定义的最后加入约束，例如PRIMARY KEY(<列名1>, <列名2>, ...) 将设置用作主键的列，FULLTEXT(<列名1>, <列名2>, ...) 对相关列建立全文本搜索的索引。
• NOT NULL 约束只能在每一列的定义之后加，不能在表定义的最后加。不要把 NULL 值与空串相混淆。 NULL 值是没有值，而不是空串。如果指定 ''（两个单引号，其间没有字符），这在 NOT NULL 列中是允许的。空串是一个有效值，它不是无值。 NULL 值用关键字 NULL 而不是空串指定。
• 可以在定义表的最后，使用 COMMENT 关键字为表添加注释内容。
• 让 MySQL 通过自动增量生成主键，得知 AUTO_INCREMENT 列目前所增加到的具体数值的方法是：SELECT last_insert_id();，此语句返回最后一个 AUTO_INCREMENT 值。
• 所谓的键是指在指定特定数据时使用的列的组合，而主键指的是可以唯一确定一行数据的列，主键列不允许NULL值。
• 外键(foreign key)为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。
• ENGINE规定了创建该表所使用的数据库引擎，该参数可省略，以下是几个需要知道的引擎：
  • InnoDB 是一个可靠的事务处理引擎，它不支持全文本搜索；
  • MEMORY 在功能等同于 MyISAM ，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
  • MyISAM 是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
• 如果仅想在一个表不存在时创建它，应该在CREATE TABLE和表名之间添加IF NOT EXISTS。该语句在建表时会首先查看表名是否存在，并且仅在表名不存在时建表。

2. 表的删除

①删除数据库

DROP DATABASE <数据库名称>;
1

②删除表

DROP TABLE <表名称>;
1

还有一种删除表的方法，先判断表是否存在，如果存在便删除：

DROP TABLE IF EXISTS <表名>;
1

③删除视图

DROP VIEW <视图名称>;
1

注意，一旦删除便无法恢复。

3. 表的定义更新

当你定义了一个表之后，你可能会发现刚定义的表存在以下问题：少定义了一列、多定义了一列、某一列的列名写错了等，那这个时候怎么办？

我们可能会想到删除原来的表再重新定义，但其实不必这么大费周章，只需要运用 ALTER TABLE 语句即可解决以上问题。我们并没有删除原来表的定义，只是对它进行了更新，因此我们称之为表的定义更新。

• 添加列

  ALTER TABLE <表名> ADD [COLUMN] <列的定义> ;
  1

  这里列的定义就是像创建表时列的定义一样。

• 删除列

  ALTER TABLE <表名> DROP COLUMN <列名> ;
  1

• 重命名列名

  ALTER TABLE <表名> CHANGE COLUMN <旧列名> <新列的定义>;
  1

• 定义外键

  ALTER TABLE <表名1> ADD CONSTRAINT <约束名称> FOREIGN KEY (<表1列名>) REFERENCES <表名2> <表2列名>;
  1

  如果对单个表进行多个更改，可以使用单条 ALTER TABLE 语句，每个更改用逗号分隔。

• 重命名表名

  RENAME TABLE <旧表名> TO <新表名>;
  1

  可以同时对多个表进行重命名：

  RENAME TABLE <旧表名1> TO <新表名1>, <旧表名2> TO <新表名2>, <旧表名3> TO <新表名3>, ...;
  1

注意：表定义变更后无法恢复。

4. 信息查询

① 查看MySQL版本号

SELECT VERSION();
1

② 显示可用数据库&表&列信息

-- 显示可用数据库
SHOW DATABASES;

-- 显示可用表
SHOW TABLES;

-- 显示某个表中的列信息
-- 对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息
SHOW COLUMNS FROM <表名>;  or DESCRIBE <表名>; 
1
2
3
4
5
6
7
8
9

③ 其他显示信息语句

-- 用于显示广泛的服务器状态信息
SHOW STATUS;
-- 用来显示创建特定数据库的MySQL语句
SHOW CREATE DATABASE <数据库名>;
-- 显示创建特定表的MySQL语句
SHOW CREATE TABLE <表名>;
-- 显示授予用户（所有用户或特定用户）的安全权限
SHOW GRANTS;
-- 显示服务器错误消息
SHOW ERRORS;
-- 显示服务器警告消息
SHOW WARNINGS;
1
2
3
4
5
6
7
8
9
10
11
12

四、表的查询基础

这一部分主要介绍 SELECT 语句中最基本的语法，以及三大运算符——算术运算符、比较运算符和逻辑运算符。

1. SELECT语句

SQL 语句中最基础的语句就是 SELECT 语句，通过 SELECT 语句查询并选取出必要数据的过程称为匹配查询或查询（query）。

SELECT 语句的基本语法如下：

-- 这是一行注释，注意半角空格
/*多行注释
  这是多行注释*/
SELECT <列名1>, <列名2>, ...
FROM <表名>
WHERE <条件表达式>
LIMIT m, n;
1
2
3
4
5
6
7

子句（clause）说明：

• SELECT 子句中列举了希望从表中查询出的列的名称：
  • 该子句中的列名还可以写作<表名>.<列名>，这对于FROM子句中存在不止一个表时非常有用。
  • 查询结果中列的顺序和该子句中的顺序相同。SELECT子句中可以只用一个星号（ * ），星号代表全部列的意思。但是使用星号无法设定列的显示顺序，这时会按照 CREATE TABLE 语句的定义对列进行排序。
  • 可以使用 AS 关键字为列设定别名，同样也可以使用该关键字在 FROM 子句中对表设置别名。注意，允许设置汉字别名，设定汉语别名时需要使用双引号（ " ），使用双引号可以设定包含空格（空白）的别名。此外，AS 关键字可以省略。
  • 该子句中可以使用 DISTINCT 关键字删除重复行。注意两点：在使用 DISTINCT 时， NULL 也被视为一类数据；DISTINCT 关键字只能用在第一个列名之前，对多列数据同时进行去重。
  • SELECT 子句中还可以书写常数，主要包括字符串常数、数字常数和日期常数，查询结果有多少行，就会有多少行的常数。
• FROM 子句指定了选取出数据的表的名称：
  • FROM 子句中的表名还可以写成<数据库名>.<表名>，这有利于更容易观察到某个表属于哪个数据库，而且还可以跨库对表进行联结。
• WHERE 子句对数据进行筛选，只留下满足查询条件的记录：
  • WHERE 子句中可以包含多个查询条件，可以使用逻辑运算符组合多个查询条件。
• LIMIT 子句用于将得到的记录筛选成指定数量：
  • LIMIT 子句接一个参数时，直接返回对应参数值数量的记录；
  • LIMIT 子句接两个参数时，指定从行号为m值的位置开始，返回对应n值数量的记录；
  • LIMIT 中指定要检索的行数为检索的最大行数。如果没有足够的行，MySQL将只返回它能返回的那么多行；
  • LIMIT m, n可以写成LIMIT n OFFSET m。
• 注释是 SQL 语句中用来标识说明或者注意事项的部分，可以插入在子句之间，注释的书写方法有如下两种：
  • 一行注释：书写在--之后，只能写在同一行。注意，MySQL中需要在--之后加入半角空格（如果不加的话就不会被认为是注释）。
  • 多行注释：书写在 /*和*/之间，可以跨多行。

2. 三大运算符

运算符就是使用其两边的值进行四则运算或者字符串拼接、数值大小比较等运算，并返回结果的符号。

• 算术运算符

  • SQL 语句中可以使用的四则运算的主要运算符如下表所示：

    含义	运算符
    加法运算	+
    减法运算	-
    乘法运算	*
    除法运算	/

  • 可以像平常的运算表达式那样使用括号来提升运算优先级。
  • 所有包含 NULL 的算术运算，结果肯定是 NULL。
  • MySQL 中允许省略 FROM 子句，只利用 SELECT子句进行计算，但是通常不这么操作。

• 比较运算符

  • SQL 中主要的比较运算符如下表所示：

    运算符	含义
    =	等于
    <> or !=	不等于
    >=	大于等于
    >	大于
    <=	小于等于
    <	小于

  • 不等号（ < 、 > ）和等号（ = ）的位置不能颠倒。一定要让不等号在左，等号在右。
  • 等于就一个等号，不像程序那样写双等号。
  • 字符串类型的数据原则上按照字典顺序（以相同字符开头的单词比不同字符开头的单词更相近。）进行排序，不能与数字的大小顺序混淆。
  • 对于 NULL，我们无法对其使用比较运算符进行比较，应该使用IS NULL 和 IS NOT NULL运算符。

• 逻辑运算符

  • 使用逻辑运算符可以将多个查询条件进行组合。常用的逻辑运算符如下表所示：

    运算符	含义
    NOT	否定某一条件，MySQL支持使用 NOT 对 IN 、 BETWEEN 和EXISTS子句取反
    AND	在其两侧的查询条件都成立时整个查询条件才成立
    OR	在其两侧的查询条件有一个成立时整个查询条件都成立

  • 优先级：NOT > AND > OR，但是可以使用括号来改变优先级。
  • 含有 NULL 的逻辑运算结果不一定为 NULL，例如 A AND NULL，如果 A 已经确定为假（FASLE），那么 A AND NULL 一定为假。
  • 使用 AND运算符进行的逻辑运算称为逻辑积，使用 OR 运算符进行的逻辑运算称为逻辑和。
  • 正因为包含了 NULL，所以SQL 中是三值逻辑：TRUE、FALSE、UNKNOWN（不确定）。
  • 三个真值之间有下面这样的优先级顺序：对于 AND，false ＞ unknown ＞ true；对于 OR，true ＞ unknown ＞ false。

• 关于NULL

  • NULL 可以细分为两种，分别指的是“未知”（unknown）和“不适用”（not applicable, inapplicable）。未知”指的是“虽然现在不知道，但加上某些条件后就可以知道”；而“不适用”指的是“无论怎么努力都无法知道”，“无意义”。但是，现在所有的 DBMS 都将两种类型的 NULL 归为了一类并采用了三值逻辑。
  • NULL 既不是值也不是变量。 NULL 只是一个表示“没有值”的标记，而比较谓词只适用于值。因此，对并非值的 NULL 使用比较谓词本来就是没有意义的。
  • NULL使得排中律不成立：“把命题和它的否命题通过‘或者’连接而成的命题全都是真命题”，这个命题在二值逻辑中被称为排中律（Law of Excluded Middle）。排中律不认可中间状态。
  • 如果 NOT IN 子查询中用到的表里被选择的列中存在 NULL ，则 SQL 语句整体的查询结果永远是空。
  • 因为 EXISTS 谓词永远不会返回 unknown，只会返回 true 或者 false 。因此就有了 IN 和 EXISTS 可以互相替换使用，而 NOT IN 和 NOT EXISTS 却不可以互相替换的混乱现象。

五、表的聚合与排序

这一部分主要介绍使用 SQL 语句进行汇总操作以及排序操作的方法，主要包含：聚合函数、GROUP BY 子句、HAVING子句和 ORDER BY 子句。

1. 聚合函数

聚合函数（也称为“聚集函数”）主要用来对表中的数据进行某种汇总操作或计算。所谓聚合，就是将多行汇总为一行。SQL 中常见的聚合函数如下表所示：

• COUNT 函数的结果根据参数的不同而不同。COUNT (*)会得到包含 NULL 的数据行数，而COUNT (< 列名 >) 会得到 NULL 之外的数据行数。
• COUNT (*) 特性是 COUNT 函数所特有的，其他函数并不能将星号作为参数（如果使用星号会出错）。
• 聚合函数会将 NULL 排除在外（不同于“等价于0”）。但 COUNT (*)例外，并不会排除 NULL 。
• SUM / AVG 函数只能对数值类型的列使用，而 MAX/MIN 函数原则上可以适用于任何数据类型的列。
• 在聚合函数的参数中使用 DISTINCT ，可以删除重复数据。例如 COUNT(DISTINCT <列名>)用来计算某一列的种类，依旧会把 NULL 排除在外；
• MAX() 函数在用于文本数据时，如果数据按相应的列排序，则 MAX() 返回最后一行，而 MIN() 函数刚好相反（返回最前面一行）。

2. GROUP BY子句

使用 GROUP BY 子句可以先把表分成几组，然后再进行汇总处理。基本语法如下：

SELECT <列名1>, <列名2>, ...
FROM <表名>
WHERE <条件表达式>
GROUP BY <聚合键1>, <聚合键2>, ...;
1
2
3
4

• 聚合键中包含 NULL 时，在结果中会以“不确定”行（空行）的形式表现出来。
• 关于 SELECT 子句：
  • SELECT 子句中只能存在以下三种元素：常数、聚合函数、聚合键
  • SELECT 子句中不能出现聚合键之外的列名
  • 由于执行顺序的原因，在 GROUP BY 子句中不能使用 SELECT 子句中定义的别名
  • 只有 SELECT 子句、HAVING 子句以及 ORDER BY 子句中能够使用聚合函数
• GROUP BY 子句结果的显示是无序的
• DISTINCT 和 GROUP BY 子句，都能够删除后续列中的重复数据：在“想要删除选择结果中的重复记录”时使用 DISTINCT ，在“想要计算汇总结果”时使用 GROUP BY 。

3. HAVING子句

使用 GROUP BY 子句，可以得到将表分组后的结果，而 HAVING 子句可以通过指定条件来选取特定组。基本语法如下：

SELECT <列名1>, <列名2>, ...
FROM <表名>
WHERE <条件表达式>
GROUP BY <聚合键1>, <聚合键2>, ...
HAVING <分组结果对应的条件>;
1
2
3
4
5

• WHERE 子句只能指定记录（行）的条件，而 HAVING 子句用来指定分组的条件
• HAVING 子句中能够使用的 3 种要素：常数、聚合函数、聚合键
• 和 GROUP BY 子句对应的一样，HAVING 子句同样不能使用 SELECT 子句中定义的别名
• 聚合键所对应的条件既可以写在 HAVING 子句当中，又可以写在 WHERE 子句当中。但是，聚合键所对应的条件应该书写在 WHERE 子句当中。原因如下：
  • 首先，根本原因是 WHERE 子句和 HAVING 子句的作用不同。如前所述， HAVING 子句是用来指定“组”的条件的。因此，“行”所对应的条件还是应该写在 WHERE 子句当中。
  • 另一个原因与性能即执行速度有关系。通过 WHERE 子句指定条件时，由于排序之前就对数据进行了过滤，因此能够减少排序的数据量。但 HAVING 子句是在排序之后才对数据进行分组的，因此与在 WHERE 子句中指定条件比起来，需要排序的数据量就会多得多。

4. ORDER BY子句

通常在 SELECT 语句末尾（这是因为对数据行进行排序的操作必须在结果即将返回时执行）添加 ORDER BY 子句来明确指定记录的排列顺序。基本语法如下：

SELECT <列名1>, <列名2>, ...
FROM <表名>
WHERE <条件表达式>
GROUP BY <聚合键1>, <聚合键2>, ...
HAVING <分组结果对应的条件>
ORDER BY <排序键>;
1
2
3
4
5
6

• 关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义
• 默认升序ASC排列；降序排列时，在列名后面使用 DESC 关键字
• 由于 ASC 和 DESC 这两个关键字是以列为单位指定的，因此可以同时指定一个列为升序，指定其他列为降序
• 排序键中包含 NULL 时，会在开头或末尾进行汇总
• 在 ORDER BY 子句中可以使用 SELECT 子句中定义的别名，可以使用 SELECT 子句中未使用的列和聚合函数
• 在 ORDER BY 子句中，还可以使用在 SELECT 子句中出现的列所对应的编号。列编号是指 SELECT 子句中的列按照从左到右的顺序进行排列时所对应的编号（1, 2, 3, …）。但是不推荐使用。

六、数据更新

这一部分将会介绍更新表中数据的方法。数据的更新处理大体可以分为插入（ INSERT ）、删除（ DELETE ）和更新（ UPDATE ）三类。此外，还会介绍数据库中用来管理数据更新的重要概念——事务。

1. 数据的插入

SQL 中的 INSERT 语句用来向表中插入数据。基本语法如下：

INSERT INTO <表名> (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...);

-- 如果主键已经存在，就执行更新操作；否则执行插入操作
INSERT INTO <表名> (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...) ON DUPLICATE KEY UPDATE 列1 = 值1, 列2 = 值2, 列3 = 值3, ...;
1
2
3
4

• 将列名和值用逗号隔开，分别括在 () 内，这种形式称为清单，例如基本语法中的列清单和值清单。
• 原则上，执行一次 INSERT 语句会插入一行数据。但是 MySQL中支持多行插入数据：只要将值清单之间用逗号分隔即可。此外，对表进行全列 INSERT 时，可以省略表名后的列清单。
• 可以在 INSERT 语句中对 AUTO_INCREMENT 列指定一个值，只要值唯一即可，该值将被用来替代自动生成的值，后续的增量将开始使用该手工插入的值。
• INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES 子句的值清单中写入 NULL 。但是，想要插入 NULL 的列一定不能设置 NOT NULL 约束，否则会报错。
• 还可以向表中插入默认值（初始值）。可以通过在创建表的CREATE TABLE 语句中设置 DEFAULT 约束来设定默认值。插入默认值主要有以下两种方法：
  • 显示方法：在 VALUES 子句中指定 DEFAULT 关键字（推荐）
  • 隐式方法：在列清单和值清单中省略设定了默认值的列
• 省略 INSERT 语句中的某一列名，就会自动设定为该列的默认值（没有默认值时会设定为 NULL ）而如果省略的是设置了 NOT NULL 约束的列， INSERT 语句就会出错。
• 对于设定了自动增量的列，也可以插入 NULL，MySQL会自动补充。
• 如果数据检索是最重要的，则可以通过在 INSERT 和 INTO 之间添加关键字 LOW_PRIORITY，指示 MySQL 降低 INSERT 语句的优先级：INSERT LOW_PRIORITY INTO ...，这也适用于 UPDATE 和 DELETE 语句。

从其他表中复制数据插入，基本语法如下：

INSERT INTO <表名> (列1, 列2, 列3, ...) 
SELECT <列名> FROM <表名>;
1
2

• 该 INSERT 语句中的 SELECT 语句，也可以使用 WHERE 子句或者 GROUP BY 子句等，但是使用 ORDER BY 子句并不会产生任何效果，因为无法保证表内部记录的排列顺序。
• 不一定要求列名匹配：事实上，MySQL 甚至不关心 SELECT 返回的列名，因为它使用的是列的位置。

2. 数据的删除

• DELETE 语句用于删除表中的数据。基本语法如下：

  DELETE FROM <表名>;
  1

  DELETE 语句和 DROP 语句的区别：

  • DROP TABLE 语句的操作对象是表，可以将表完全删除（包括表的定义）
  • DELETE 语句的操作对象是记录（行），会留下表（容器），而只删除表中的全部数据（不删除表的定义）

• 想要删除部分数据行时，可以像 SELECT 语句那样使用 WHERE 子句指定删除条件。这种指定了删除对象的 DELETE 语句称为搜索型 DELETE 。它的基本语法如下：

  DELETE FROM <表名>
  WHERE <条件>;
  1
  2

  DELETE 语句中不能使用 GROUP BY 、HAVING 和 ORDER BY 三类子句，而只能使用 WHERE 子句，因为在删除表中数据时它们都起不到什么作用。

• MySQL中支持一种名为 TRUNCATE 语句的数据删除语句，它的功能与简单型 DELETE 语句相同，那就是只能删除整张表的数据。正是因为它不能具体地控制删除对象，所以其处理速度比 DELETE 要快得多。基本语法如下：

  TRUNCATE <表名>;
  1

  TRUNCATE 实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据。

3. 数据的更新

• 直接使用 UPDATE 语句就可以更新表中的数据。基本语法如下：

  UPDATE <表名>
  SET <列名> = <表达式>;
  1
  2

  该语句会将表中的该列均更新为表达式所对应的值。

• 更新数据时也可以像 DELETE 语句那样使用 WHERE 子句，这种指定更新对象的 UPDATE 语句称为搜索型 UPDATE 语句。基本语法如下：

  UPDATE <表名>
  SET <列名> = <表达式>
  WHERE <条件>;
  1
  2
  3

  • SET 子句中赋值表达式的右边不仅可以是单纯的值，还可以是包含列的表达式；
  • 使用 UPDATE 也可以将列更新为 NULL （俗称 NULL 清空）。此时只需要将赋值表达式右边的值直接写为 NULL 即可，但仅限于未设置 NOT NULL 或主键约束的列；
  • UPDATE 语句中可以使用子查询，使得能用 SELECT 语句检索出的数据更新列数据；

• 就像 MySQL 支持多列插入一样，它也支持多列更新，基本语法如下：

  UPDATE <表名>
  SET <列名1> = <表达式1>,
  	<列名2> = <表达式2>,
  		  ...
  WHERE <条件>;
  1
  2
  3
  4
  5

  • 在学习到这部分的时候，产生了一个疑惑：如果我在进行多列更新时，先对列 A 进行了更新，然后对列 B 的更新时表达式中用到了列 A，那么列 B 在更新时用的是列 A 的旧值还是新值？后经测试，我发现列 B 用的是列 A 的新值，这说明数据更新的时效性还蛮快的。
  • 如果用 UPDATE 语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个 UPDATE 操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。如果期望即使发生错误也继续进行更新，可使用 IGNORE 关键字，如下所示：UPDATE IGNORE <表名> ...

4. 事务

事务是需要在同一个处理单元中执行的一系列更新处理的集合。使用事务开始语句和事务结束语句，将一系列 DML 语句（ INSERT / UPDATE / DELETE 语句）括起来，就实现了一个事务处理。

事务并没有标准的开始指令存在，而是根据 DBMS 的不同而不同。实际上，几乎所有的数据库产品的事务都无需开始指令。这是因为大部分情况下，事务在数据库连接建立时就已经悄悄开始了，并不需要用户再明确发出开始指令。

事务处理用来管理 INSERT 、 UPDATE 和 DELETE 语句，不能回退 SELECT 语句，也不能回退 CREATE 或 DROP 操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

像这样不使用指令而悄悄开始事务的情况下，应该如何区分各个事务呢？通常会有如下两种情况：

• 自动提交模式：每条SQL语句就是一个事务（MySQL默认）
• 直到用户执行 COMMIT 或者 ROLLBACK 为止算作一个事务

事务基本的语法如下：

START TRANSACTION; -- 事务开始语句
	DML语句1(INSERT DELETE UPDATE);
	DML语句2;
	...
COMMIT 或者 ROLLBACK; -- 事务结束语句
1
2
3
4
5

• COMMIT——提交处理
  • COMMIT 是提交事务包含的全部更新处理的结束指令，相当于文件处理中的覆盖保存。
• ROLLBACK——取消处理
  • ROLLBACK 是取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存。

事务中存在保留点，它是指事务处理中设置的临时占位符（placeholder），可以对它发布部分回退（与回退整个事务处理不同）。

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符，这些占位符称为保留点。为了创建保留点，可使用 SAVEPOINT 语句：

SAVEPOINT <保留点名称>;
1

每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。为了回退到指定保留点，可使用如下语句：

ROLLBACK TO <保留点名称>;
1

保留点在事务处理完成（执行一条 ROLLBACK 或 COMMIT）后自动释放，也可以用RELEASE SAVEPOINT <保留点名称>;明确地释放指定保留点。

默认的 MySQL 行为是自动提交所有更改。换句话说，任何时候执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效，为指示 MySQL 不自动提交更改，需要使用以下语句：SET autocommit=0;。

autocommit 标志决定是否自动提交更改，不管有没有 COMMIT 语句。设置 autocommit 为 0 （假）指示MySQL不自动提交更改（直到 autocommit 被设置为真为止）。autocommit 标志是针对每个连接而不是服务器的。

事务的四种特性——ACID特性：

• 原子性（Atomicity）：原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行。
• 一致性（Consistency，也叫完整性）：一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者 NOT NULL 约束等。
• 隔离性（Isolation）：隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。
• 持久性（Durability，也叫耐久性）：在事务（不论是提交还是回滚）结束后，DBMS 能够保证该时间点的数据状态会被保存的特性。

七、复杂查询

这一部分主要介绍嵌套在 SELECT 语句中的视图和子查询等技术。

1. 视图

从SQL的角度来看，视图和表是相同的，两者的区别在于表中保存的是实际数据，而视图中保存的是从表中取出数据所使用的 SELECT 语句（视图本身并不存储数据）。

数据库中的数据实际上会被保存到计算机的存储设备（通常是硬盘）中，但是使用视图时并不会将数据保存到存储设备之中，而且也不会将数据保存到其他任何地方。实际上视图保存的是 SELECT 语句，我们从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出一张临时表。视图有以下几大优点：

• 视图无需保存数据，可以节省存储设备的容量
• 可以将频繁使用的 SELECT 语句保存成视图，这样就不用每次都重新书写
• 视图中的数据会随着原表的变化自动更新

视图的语法如下：

CREATE VIEW  <视图名称> (<视图列名1>, <视图列名2>, ...)
AS
<SELECT 语句 >
1
2
3

• AS 关键字不能省略
• 视图中的列名和 SELECT 语句中的列名一一对应
• 如果要定义的视图的列名称和 SELECT 中的列名一样，那么可以省略视图列名
• 使用视图的方法和使用表的方法一样
• 以视图为基础创建的视图称为多重视图，应该尽量避免在视图的基础上创建视图，因为对多数DBMS来说，多重视图会降低SQL的性能
• 视图不能索引，也不能有关联的触发器或默认值
• 视图可以和表一起使用，例如对表和视图进行联结
• 使用SHOW CREATE VIEW <视图名称>来查看创建视图的语句
• 更新视图时，可以先用 DROP 再用 CREATE ，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第 2 条更新语句会创建一个视图；如果要更新的视图存在，则第 2 条更新语句会替换原有视图。

视图存在以下限制：

• 定义视图时不能使用 ORDER BY 子句：因为视图和表一样，数据行都是没有顺序的。但是使用视图时可以使用 ORDER BY 子句，但如果从该视图检索数据 SELECT 中也含有 ORDER BY ，那么该视图中的 ORDER BY 将被覆盖。（这段话好像前后矛盾）

• 如果定义视图的 SELECT 语句能够满足某些条件，那么这个视图就可以被更新。举例说明一些具有代表性的条件：

  • SELECT 子句中未使用 DISTINCT
  • FROM 子句中只有一张表
  • 未使用 GROUP BY 和 HAVING 子句
  • 未使用联结、子查询、并集、聚合函数
  • 未导出计算列

  存在该限制的原因是：视图和表需要同时进行更新，因此通过汇总等操作得到的视图无法进行更新，因为无法将视图的更改反映到原表。

假设某视图是根据where 列1 = A的条件创建出来的，经测试MySQL中依旧可以向视图中插入列1 <> A的数据，但是该数据仅仅是通过该视图插入到了对应的数据表中，该视图由于限制条件依旧不包含该数据。

删除视图的语法如下：

DROP VIEW <视图名称>;
1

2. 子查询

概括来说，子查询就是一张一次性视图（SELECT 语句）。与视图不同，子查询在 SELECT 语句执行完毕之后就会消失。

子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中，作为内查询，它会首先执行。基本语法如下：

SELECT <列名1>, <列名2>, ...
FROM (
		SELECT <列名1>, <列名2>, ...
		FROM <表名>
		WHERE <条件>
		GROUP BY <聚合键>
) AS <子查询别名>;
1
2
3
4
5
6
7

• 子查询的层数原则上没有限制，但是随着子查询嵌套层数的增加，SQL 语句会变得越来越难读懂，性能也会越来越差。因此，应尽量避免使用多层嵌套的子查询。
• 原则上子查询必须设定名称，因此应该尽量从处理内容的角度出发为子查询设定恰当的名称。

标量子查询就是返回单一值的子查询，必须而且只能返回 1 行 1 列的结果，绝对不能返回多行结果：

• 由于聚合函数不能写在 WHERE 子句中，因此我们可以使用标量子查询来代替。
• 能够使用常数或者列名的地方，无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用标量子查询。

3. 关联子查询

关联子查询在子查询中添加的 WHERE 子句。基本语法如下：

SELECT <列名1>, <列名2>, ...
FROM <表名1> AS <表别名1>
WHERE <列名3> > (SELECT <对列名3进行聚合>
				 FROM <表名2> AS <表别名2>
				 WHERE <表别名1>.<列名1> = <表别名2>.<列名4>);
1
2
3
4
5

单看关联子查询的基本语法有些不容易理解，举例来说明：

SELECT product _type , product_name, sale_price
FROM Product AS P1
WHERE sale_price > (SELECT AVG(sale_price)
					FROM Product AS P2
					WHERE P1.product_type = P2.product_type);
1
2
3
4
5

对以上代码我的理解如下：

• 观察 SQL 语句我们发现 WHERE 子句中存在关联子查询，将表 P1 中的 product_type 列和表 P2 中的 product_type 列关联在了一起；
• 在关联的同时实际上将表 P2 按照 product_type 列进行了分组，子查询计算每一组中 sale_price 列的平均值；
• 执行外部的 SELECT 语句，利用 WHERE 子句对记录进行筛选：当我们取到某个记录时，我们首先判断该记录对应于子查询分组中的哪一组，然后将子查询中该组对应的平均值返回，最后再判断取到的记录中 sale_price 列的数值是否大于对应的平均值来决定是否返回该记录。

注意：

• 在使用关联子查询时，需要在表所对应的列名之前加上表的别名，以“ < 表名 >.< 列名 > ”的形式记述。
• 在细分的组内进行比较时（对表中某一部分记录的集合进行比较时），需要使用关联子查询。
• 关联子查询实际每次比较时只能返回一行结果。
• 结合条件一定要写在子查询中，原因与关联名称的作用域有关。关联名称是指像上例中 P1、P2 这样作为表别名的名称；而关联名称存在一个有效范围（或者叫生存范围）的限制，即为作用域。概括来说，子查询内部设定的关联名称，只能在该子查询内部使用，关联名称的作用域为内部可以看到外部，而外部看不到内部。

八、函数、谓词与表达式

这一部分将介绍具有代表性的函数以及特殊版本的函数（谓词和CASE 表达式）的使用方法。

1. 函数

SQL中的函数主要分为以下几种：

算术函数

字符串函数

• 字符与字节的区别：与半角英文字母占用 1 字节不同，汉字这样的全角字符会占用 2 个以上的字节，称为多字节字符。
• 对SOUNDEX函数进一步解释： SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。 SOUNDEX 考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。参数中的所有非字母字符将被忽略，A-Z范围内的所有国际字母字符被视为元音字母。举例：WHERE SOUNDEX(<列名>) = SOUNDEX(<字符串>)。

日期函数

• DATE_ADD() 函数中的 type 有如下取值：

  type 取值	说明
  YEAR、QUARTER、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND、MICROSECOND	年、季、月、周、日、时、分、秒、毫秒
  YEAR_MONTH、DAY_HOUR、DAY_MINUTE、DAY_SECOND、DAY_MICROSECOND、HOUR_MINUTE、HOUR_SECOND、HOUR_MICROSECOND、MINUTE_SECOND、MINUTE_MICROSECOND、SECOND_MICROSECOND	复合型，此时 < expr > 参数需要使用引号，中间使用任何非数字字符（且可以同时使用多种）作为间隔即可，并且不能使用负数。 时间间隔只指定了一个值也能正常工作，但是对应XXX_ YYY使用的单位为YYY，相当于单一单位的type，此时可以使用负数。 注意，XXX与YYY之间的单位量级必须逐层表示，不能跨级，例如DAY_SECOND：1:1:1:1

• AddTime() 函数中的 expr 参数特殊取值说明如下：

  expr 取值	说明
  [60, 99]	计算结果为 NULL
  100	增加60秒
  110	增加1分10秒
  [160, 199]	计算结果为 NULL
  210	增加2分10秒

• DATE_FORMAT() 函数中的 format 具体格式如下：

  格式	描述
  %a	缩写星期名
  %b	缩写月名
  %c	月，数值
  %D	带有英文后缀的月中的天
  %d	月的天，数值(00-31)
  %e	月的天，数值(0-31)
  %f	微秒
  %H	小时 (00-23)
  %h	小时 (01-12)
  %I	小时 (01-12)
  %i	分钟，数值(00-59)
  %j	年的天 (001-366)
  %k	小时 (0-23)
  %l	小时 (1-12)
  %M	月名
  %m	月，数值(00-12)
  %p	AM 或 PM
  %r	时间，12-小时（hh:mm:ss AM 或 PM）
  %S	秒(00-59)
  %s	秒(00-59)
  %T	时间, 24-小时 (hh:mm:ss)
  %U	周 (00-53) 星期日是一周的第一天
  %u	周 (00-53) 星期一是一周的第一天
  %V	周 (01-53) 星期日是一周的第一天，与 %X 使用
  %v	周 (01-53) 星期一是一周的第一天，与 %x 使用
  %W	星期名
  %w	周的天 （0=星期日, 6=星期六）
  %X	年，其中的星期日是周的第一天，4 位，与 %V 使用
  %x	年，其中的星期一是周的第一天，4 位，与 %v 使用
  %Y	年，4 位
  %y	年，2 位

转换函数
“转换”在 SQL 中主要有两层意思：一是数据类型的转换，简称为类型转换，在英语中称为 cast；另一层意思是值的转换。

2. 谓词

谓词就是返回值为真值（TRUE / FALSE / UNKNOWN）的函数。

LIKE谓词
当需要进行字符串的部分一致查询时需要使用该谓词。部分一致大体可以分为前方一致、中间一致和后方一致三种类型。

基本语法如下：

<列名> LIKE '<查询条件字符串>%';  -- 前方一致
<列名> LIKE '%<查询条件字符串>%'; -- 中间一致
<列名> LIKE '%<查询条件字符串>';  -- 后方一致
<列名> LIKE '<查询条件字符串>%<查询条件字符串';  -- 仅中间一致
1
2
3
4

• 中间一致包含了前方一致和后方一致；
• 不使用“ = ”来指定条件字符串，而以字符串中是否包含该条件的规则为基础的查询称为模式匹配。

对于 LIKE 谓词有两个特殊符号使用的比较频繁：

注意，即使在创建表时将某一列的数据类型设定为定长字符串char(n)时，自动填充的半角空格依旧无法利用下划线符号_识别出来，表显示什么就是什么。（可以类比等号模式进行思考）

BETWEEN谓词
使用 BETWEEN 可以进行范围查询。基本语法如下：

-- 该范围包含上下限的值
<列名> BETWEEN <下限值> AND <上限值>;
1
2

IS NULL、IS NOT NULL 谓词
为了选取出某些值为 NULL 的列的数据，不能使用 = ，而只能使用特定的谓词 IS NULL、IS NOT NULL。基本语法如下：

<列名> IS NULL;
<列名> IS NOT NULL;
1
2

IN、NOT IN 谓词
IN 谓词是 OR 的简便用法，例如以下 SQL 语句：

<列名> = <值1> OR <列名> = <值2> OR <列名> = <值3>;
1

可以写成：

<列名> IN (值1, 值2, 值3);
1

• NOT IN 是 IN 的否定形式；
• 使用 IN 和 NOT IN 时是无法选取出 NULL 数据的；
• 可以使用子查询作为 IN 谓词的参数；
• NOT IN 的参数中不能包含 NULL（结果均变成了不确定），不仅仅是指定 NULL 的情况，使用子查询作为 NOT IN 的参数时，该子查询的返回值也不能是 NULL。但是，IN 的参数中可以包含 NULL。

EXISTS谓词
该谓词的作用就是“判断是否存在满足某种条件的记录”。如果存在这样的记录返回 TRUE，否则返回 FALSE：

• 通常指定关联子查询作为 EXISTS 的参数；
• EXISTS 只会判断是否存在满足子查询中 WHERE 子句指定的条件的记录，不关心返回哪些列，因此作为 EXISTS 参数的子查询中经常会使用 SELECT *
• EXISTS 可以用来替换 IN，NOT EXISTS 可以用来替换 NOT IN。

3. 正则表达式

正则表达式的作用是匹配文本，将一个正则表达式与一个文本串进行比较。MySQL 用 WHERE 子句对正则表达式提供了初步的支持，允许指定正则表达式，过滤 SELECT 检索出的数据。基本语法如下：

SELECT *
FROM <表名>
WHERE <列1> REGEXP <正则表达式>;
1
2
3

MySQL 中正则表达式常见的字符如下：

注意：

• 正则表达式无需与列值完全匹配，列值中只要包含正则表达式中的内容即认为匹配。

• MySQL中的正则表达式匹配不区分大小写，为区分大小写，可使用 BINARY 关键字，如 WHERE <列1> REGEXP BINARY <正则表达式>。

• MySQL要求使用两个反斜杠转义特殊字符，\\ 也用来引用元字符（即具有特殊含义的字符），空白元字符如下：

  空白元字符	说明
  \\f	换页
  \\n	换行
  \\r	回车
  \\t	制表
  \\v	纵向制表

  重复元字符如下：

  空白元字符	说明
  *	0个或多个匹配
  +	1个或多个匹配（等于{1,}）
  ?	0个或1个匹配（等于{0,1}）
  {n}	指定数目的匹配
  {n,}	不少于指定数目的匹配
  {n,m}	匹配数目的范围（m不超过255）

• MySQL可以使用预定义的字符集（字符类），字符类如下：

  字符类	说明
  [:alnum:]	任意字母和数字（同a-zA-Z0-9）
  [:alpha:]	任意字符（同a-zA-Z）
  [:blank:]	空格和制表（同 \\t）
  [:cntrl:]	ASCII控制字符（ASCII 0到31和127）
  [:digit:]	任意数字（同0-9）
  [:graph:]	与[:print:]相同，但不包括空格
  [:lower:]	任意小写字母（同a-z）
  [:print:]	任意可打印字符
  [:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
  [:space:]	包括空格在内的任意空白字符（同\\f\\n\\r\\t\\v）
  [:upper:]	任意大写字母（同A-Z）
  [:xdigit:]	任意十六进制数字（同a-fA-F0-9）

• 可以在不使用数据库表的情况下用 SELECT 来测试正则表达式。 REGEXP 检查总是返回 0（没有匹配）或 1（匹配）。可以用带文字串的 REGEXP 来测试表达式，并试验它们，语法为：SELECT <文本字符串> REGEXP <正则表达式>;。

4. CASE表达式

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为条件分支。CASE 表达式分为简单 CASE 表达式和搜索 CASE 表达式两种。

简单 CASE 表达式的基本语法如下：

CASE < 表达式 >
	WHEN < 表达式 > THEN < 表达式 >
	WHEN < 表达式 > THEN < 表达式 >
	WHEN < 表达式 > THEN < 表达式 >
			...
	ELSE < 表达式 >
END
1
2
3
4
5
6
7

搜索 CASE 表达式的基本语法如下：

CASE
	WHEN < 求值表达式 > THEN < 表达式 >
	WHEN < 求值表达式 > THEN < 表达式 >
	WHEN < 求值表达式 > THEN < 表达式 >
			...
	ELSE < 表达式 >
END
1
2
3
4
5
6
7

• CASE 表达式最终只会返回一个值；
• CASE 表达式所有分支的返回值的类型必须一致，如果不这样的话，那么各个分支会分别返回不同类型的值，执行时就会发生语法错误；
• ELSE 子句指定了应该如何处理不满足 WHEN 子句中的条件的记录，可以省略不写，这时会被默认为 ELSE NULL；
• END 关键字绝对不能省略；
• 搜索CASE 表达式包含简单 CASE 表达式的全部功能；
• 简单 CASE表达式不能在 WHEN 子句中指定不同列；
• CASE表达式能够实现聚合结果行列互换。

九、集合运算

这一部分将会介绍使用 2 张以上的表的 SQL 语句，包括以行方向（竖）为单位的集合运算符和以列方向（横）为单位的联结。

1. 表的加减法

集合运算，就是对满足同一规则的记录进行的加减等四则运算，它以行方向（纵向） 为单位进行操作。用来进行集合运算的运算符称为集合运算符。基本语法如下：

SELECT <列1>, <列2>, <列3>
FROM <表1>
<集合运算符>
SELECT <列A>, <列B>, <列C>
FROM <表2>
1
2
3
4
5

常见的集合运算符如下表所示：

• MySQL暂时不支持集合运算符 INTERSECT 和 EXCEPT；
• UNION 中每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）；
• 作为运算对象的记录中列的类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）；
• 可以使用任何 SELECT 语句，但 ORDER BY 子句只能在最后使用一次，不存在用一种方式排序一部分，而又用另一种方式排序另一
  部分的情况；
• UNION 与 UNION ALL 的另一个不同之处是：UNION ALL 不会对结果进行排序，因此 UNION ALL 比 UNION 的性能更好；
• UNION 和 INTERSECT 同时使用时，INTERSECT 先执行（当然数据库必须支持 INTERSECT 运算符）。

2. 联结（JOIN）

联结（JOIN） 就是将其他表中的列添加过来，进行“添加列”的集合运算，它以**列方向（横向）**为单位进行操作。联结大体上可分为内联结、外联结和交叉联结。

内联结——INNER JOIN

内联结是只包含表内信息的联结，只能选取出同时存在于两张表中的数据。基本语法如下：

FROM <表名A> AS <别名A> INNER JOIN <表名B> AS <别名B>
ON <别名A>.<列1> = <别名B>.<列2>
1
2

• ON 中的联结键必不可少，并且 ON 必须书写在 FROM 和 WHERE 之间，需要指定多个键时，同样可以使用 AND 、 OR；
• 表的别名不是必需的；
• 使用联结时 SELECT 子句中的列需要按照“ < 表的别名 > . < 列名 > ”的格式进行书写，实际上，只有那些同时存在于两张表中的列必须使用这样的书写方式，其他的列直接书写列名也不会发生错误。但是为了避免混乱，在使用联结时还是尽量按照“ < 表的别名 > . < 列名 > ”的格式来书写 SELECT 子句中全部的列；
• 使用联结运算将满足相同规则的表联结起来时， WHERE、GROUP BY、 HAVING、 ORDER BY 等子句i都可以正常使用；
• 依旧可以像单个表那样使用 SELECT * 来获取联结表的所有列。

②外联结——OUTTER JOIN
外联结中包含原表中不存在（在原表之外）的信息，只要数据存在于某一张表中就能用方法读取出来。MySQL中外联结有左联结（LEFT OUTER JOIN，下方第一个图）和右联结（RIGHT OUTER JOIN，下方第二个图）。


基本语法如下：

-- 左联结
FROM <表名A> AS <别名A> LEFT OUTER JOIN <表名B> AS <别名B>
ON <别名A>.<列1> = <别名B>.<列2>

-- 右联结
FROM <表名A> AS <别名A> RIGHT OUTER JOIN <表名B> AS <别名B>
ON <别名A>.<列1> = <别名B>.<列2>
1
2
3
4
5
6
7

注意：

• 外联结中使用 LEFT、RIGHT 来指定主表。使用二者所得到的结果完全相同。
• OUTER 这一关键字可以省略。
• 原则上，联结的表的数量没有限制。

进阶：
1> A独有或B独有
我们可以利用外联结来实现只存在与表 A 或者表 B 的行，这在某种意义上也实现了表的差集运算。


基本语法如下：

-- A表独有
FROM <表名A> AS <别名A> LEFT OUTER JOIN <表名B> AS <别名B>
ON <别名A>.<列1> = <别名B>.<列2>
WHERE <别名B>.<列2> IS NULL;

-- B表独有
FROM <表名A> AS <别名A> RIGHT OUTER JOIN <表名B> AS <别名B>
ON <别名A>.<列1> = <别名B>.<列2>
WHERE <别名A>.<列1> IS NULL;
1
2
3
4
5
6
7
8
9

2> AB全有
MySQL 不支持外联结（FULL OUTER JOIN），但可以通过左联结 + UNION + 右联结实现。

基本语法如下：

-- AB全有
FROM <表名A> AS <别名A> LEFT OUTER JOIN <表名B> AS <别名B>
ON <别名A>.<列1> = <别名B>.<列2>
UNION
FROM <表名A> AS <别名A> RIGHT OUTER JOIN <表名B> AS <别名B>
ON <别名A>.<列1> = <别名B>.<列2>
1
2
3
4
5
6

3> A独有＋B独有
A、B独有并集，相当于A、B全有去掉AB的共有（交集）。

基本语法如下：

-- A表独有+B表独有
FROM <表名A> AS <别名A> LEFT OUTER JOIN <表名B> AS <别名B>
ON <别名A>.<列1> = <别名B>.<列2>
WHERE <别名B>.<列2> IS NULL;
UNION
FROM <表名A> AS <别名A> RIGHT OUTER JOIN <表名B> AS <别名B>
ON <别名A>.<列1> = <别名B>.<列2>
WHERE <别名A>.<列1> IS NULL;
1
2
3
4
5
6
7
8

交叉联结——CROSS JOIN

• 对满足相同规则的表进行交叉联结的集合运算符是 CROSS JOIN （笛卡儿积）。
• 进行交叉联结时无法使用内联结和外联结中所使用的 ON 子句，这是因为交叉联结是对两张表中的全部记录进行交叉组合，因此结果中的记录数通常是两张表中行数的乘积。
• 内联结是交叉联结的一部分，“内”也可以理解为“包含在交叉联结结果中的部分”。相反，外联结的“外”可以理解为“交叉联结结果之外的部分”；
• 在FROM子句中直接给出两个以逗号分隔的表：FROM <表1>, <表2>即为交叉联结。

十、高级用法

这一部分介绍的是 SQL 中的高级用法，包括窗口函数的用法、GROUPING 运算符等。

1. 窗口函数

窗口函数也称为 OLAP 函数，OLAP 是 OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。基本语法如下：

<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序列清单>)
-- []表示可选的意思
1
2

窗口函数大体可以分为以下两种：

关于 PARTITION BY 子句：

• PARTITION BY 能够设定排序的对象范围。
• PARTITION BY 在横向上对表进行分组。
• 窗口函数兼具之前我们学过的 GROUP BY 子句的分组功能以及 ORDER BY 子句的排序功能。但是， PARTITION BY 子句并不具备 GROUP BY 子句的汇总功能。因此，使用窗口函数并不会减少原表中记录的行数。
• 通过 PARTITION BY 分组后的记录集合称为窗口。此处的窗口并非“窗户”的意思，而是代表范围。
• 各个窗口在定义上绝对不会包含共通的部分。
• 可以不指定 PARTITION BY 子句，相当于将整个表作为一个大的窗口来使用，但是 ORDER BY 子句必不可少。

关于 ORDER BY 子句：

• ORDER BY 能够指定按照哪一列、何种顺序进行排序；
• ORDER BY 决定了纵向排序的规则
• OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。要想对结果进行排序，只能在 SELECT 语句的最后再次使用 ORDER BY 子句。

常用的专用窗口函数含义如下：

根据函数名称，我们可以轻易记住每个函数的功能。例如，RANK（排序）就是正常的排序，DENSE_RANK（密集排序）说明排名紧凑不会跳位，ROW_NUMBER就是单纯的行编号12345。

注意：

• 由于专用窗口函数无需参数，因此通常括号中都是空的。
• 原则上窗口函数只能在 SELECT 或者 ORDER BY 子句中使用。其理由是，在 DBMS内部，窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的结果进行的操作。可以说，在 SELECT 子句执行之前“使用窗口函数是没有意义的”。

聚合函数

• 与专用窗口函数不同的是，作为窗口函数使用的聚合函数，需要在括号内指定作为汇总对象的列。
• 将聚合函数作为窗口函数使用时，会以当前记录为基准来决定汇总对象的记录（顺序）。

计算移动平均
窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。其语法需要在 ORDER BY 子句之后使用指定范围的关键字：

ORDER BY <列名> ROWS <N行> PRECEDING; 
1

将框架指定为“截止到之前 N 行”，也就是将作为汇总对象的记录限定为“最靠近的 (N+1) 行”（包括自身行）。

-- 将框架指定为“截止到之后 N 行”
ORDER BY <列名> ROWS <N行> FOLLOWING; 

-- 将当前记录的前 M 行后 N 行作为汇总对象
ORDER BY <列名> ROWS <M行> PRECEDING AND <N行> FOLLOWING; 
1
2
3
4
5

容易混淆的一点
我在做题的时候碰见一种情况： 在使用窗口函数时，利用 ORDER BY 对注册日期进行排序并利用 SUM 函数求销售单价的累加值，SQL 语句如下：

SUM(sale_price) OVER(ORDER BY regist_date)
1

但是，regist_date 中有个重复值重复了三次，那么 SQL 会以怎样的顺序对该重复值进行排序呢？

我原本以为会以某种规则进行排序，然后逐渐累加。而实际运行发现，当遇到了这个重复值时，它会将三个重复值对应的汇总列加起来再与前面的值进行累加，也就是说三个重复值对应的累加值是一样的。

2. GROUPING运算符

只使用 GROUP BY 子句和聚合函数是无法同时得出小计和合计的。如果想要同时得到，可以使用 GROUPING 运算符。

GROUPING 运算符包含以下几种：ROLLUP、GROUPING 函数、CUBE、GROUPING SETS。除 ROLLUP 外，MySQL 目前不支持其他运算符。

ROLLUP基本语法如下：

GROUP BY <列1>, <列2>, <列3>, ... WITH ROLLUP
1

该运算符可以一次计算出不同聚合键组合的结果，组合的个数为 n+1（n 是聚合键的个数）。在上述语法中就是一次计算出了如下组合的汇总结果：

• GROUP BY ()
• GROUP BY (列1)
• GROUP BY (列1，列2)
• GROUP BY (列1，列2，列3)

GROUP BY () 表示没有聚合键，也就相当于没有 GROUP BY子句（这时会得到全部数据的合计行的记录），该合计行记录称为超级分组记录，超级分组记录默认使用 NULL 作为聚合键。因此，ROLLUP能够同时得到小计和合计。

GROUPING 函数基本语法如下：

-- 直接对列名使用即可
GROUPING(<列名>);
1
2

GROUPING 函数是用来判断超级分组记录的 NULL 的特定函数，该函数在其参数列的值为超级分组记录所产生的 NULL 时返回 1 ，其他情况返回 0。因此，使用 GROUPING 函数能够简单地分辨出原始数据中的 NULL 和超级分组记录中的 NULL 。

CUBE（MySQL目前不支持）基本语法如下：

GROUP BY CUBE(<列1>, <列2>, ...)
1

将 GROUP BY 子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中。因此，组合的个数就是 2 ⁿ （n 是聚合键的个数）。在上述语法中就是一次计算出了如下组合的汇总结果：

• GROUP BY ()
• GROUP BY (列1)
• GROUP BY (列2)
• GROUP BY (列1，列2)

注意：

• ROLLUP 的结果一定包含在 CUBE 的结果中。
• 可以把 CUBE 理解为将使用聚合键进行切割的模块堆积成一个立方体。

GROUPING SETS（MySQL目前不支持）基本语法如下：

GROUP BY GROUPING SETS(<列1>, <列2>, ...)
1

该运算符可以用于从 ROLLUP 或者 CUBE 的结果中取出部分记录，与 ROLLUP 或者 CUBE 能够得到规定的结果相对， GROUPING SETS 用于从中取出个别条件对应的不固定的结果。在上述语法中就是一次计算出了如下组合的汇总结果：

• GROUP BY (列1)
• GROUP BY (列2)

3. 全文本搜索

数据库中两个最常使用的引擎为 MyISAM 和 InnoDB ，前者支持全文本搜索，而后者不支持。

• 启用全文本搜索支持（创建相关索引）：
  为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。常见的索引创建方法有两种：

  • 一般在创建表时启用全文本搜索。 CREATE TABLE 语句接受 FULLTEXT 子句：FULLTEXT(<列名1>, <列名2>, ...) ，注意数据库引擎应该为 MyISAM。在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。
  • 也可以在创表后续指定 FULLTEXT：ALTER TABLE <表名> ADD FULLTEXT [INDEX] <自定义索引名> (<列名1>, <列名2>, ...)或CREATE FULLTEXT INDEX <自定义索引名> ON <表名> (<列名1>, <列名2>, ...)。如果正在导入数据到一个新表，此时不应该启用 FULLTEXT 索引，应该首先导入所有数据，然后再修改表定义 FULLTEXT，这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）。

• 进行全文本搜索：
  全文本搜索的基本语法如下：

  SELECT *
  FROM <表名>
  WHERE Match(<FULLTEXT索引列>) Against('<搜索表达式>');
  1
  2
  3

  • Match() 指定被搜索的列， Against() 指定要使用的搜索表达式。
  • 传递给 Match() 的值必须与 FULLTEXT() 定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。
  • 否则全文本搜索不区分大小写，除非使用 BINARY 方式。
  • Match(<FULLTEXT索引列>) Against('<搜索表达式>')没有固定的位置要求（必须放在哪一子句中），其得到的是一个 全文本搜索计算出的等级值，等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来。
  • 在 WHERE 子句中使用全文本搜索可以对结果进行排序，具有较高等级的行先返回（按等级以降序排序）。
  • 忽略词中的单引号。例如， don’t 索引为 dont 。
  • 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
  • 邻近搜索是许多全文本搜索支持的一个特性，它能搜索相邻的词（在相同的句子中、相同的段落中或者在特定数目的词的部分中，等等）。但是，MySQL全文本搜索现在还不支持邻近操作符。

• 查询扩展：
  查询扩展用来设法放宽所返回的全文本搜索结果的范围，增加找到相关匹配的机会。全文本搜索的基本语法如下：

  SELECT *
  FROM <表名>
  WHERE Match(<FULLTEXT索引列>) Against('<搜索表达式>' WITH QUERY EXPANSION);
  1
  2
  3

  在使用查询扩展时，MySQL对数据和索引进行两遍扫描来完成搜索：

  • 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
  • 其次，MySQL检查这些匹配行并选择所有有用的词：
    • 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（这个数目可以更改）；
    • MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略（可以覆盖这个列表）；
    • 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%（含）以上的行中，则将它作为一个非用词忽略。
    • 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）
  • 再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。

  查询扩展极大地增加了返回的行数，但这样做也增加了实际上并不想要的行的数目。表中的行越多，这些行中的文本就越多，使用查询扩展返回的结果越好。

• 布尔文本搜索：
  布尔文本搜索是 MySQL 所支持的全文本搜索的另外一种形式，即布尔方式（boolean mode）。以布尔方式，可以提供关于如下内容的细节：

  • 要匹配的词；
  • 要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；
  • 排列提示（指定某些词比其他词更重要，更重要的词等级更高）；
  • 表达式分组；
  • 另外一些内容。

  基本语法如下：

  SELECT *
  FROM <表名>
  WHERE Match(<FULLTEXT索引列>) Against('<搜索表达式>' IN BOOLEAN MODE);
  1
  2
  3

  注意：

  • 50%规则不用于 IN BOOLEAN MODE 。
  • 即使没有定义 FULLTEXT 索引，也可以使用布尔文本搜索，但这是一种非常缓慢的操作，其性能将随着数据量的增加而降低。
  • 可以在搜索表达式中使用全文本布尔操作符，MySQL 支持的布尔操作符如下：

    布尔操作符	说明
    +	包含，词必须存在
    -	排除，词必须不出现
    >	包含，而且增加等级值
    <	包含，且减少等级值
    ()	把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
    ~	取消一个词的排序值
    *	词尾的通配符
    “”	定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

  • 如果没有指定操作符，则搜索匹配包含搜索表达式中的至少一个词的行。
  • 排列而不排序：在布尔方式中，不按等级值降序排序返回的行。

4. 存储过程&游标

存储过程

存储过程是指为以后的使用而保存的一条或多条MySQL语句的集合。使用存储过程比单独使用其中的SQL语句要快。

• 创建存储过程
  基本语法如下：

   CREATE PROCEDURE <存储过程名称>([参数1], [参数2], ...)
   COMMENT '<注释内容>'
   BEGIN
       <存储过程体>;
   END; 
  1
  2
  3
  4
  5

  • 存储过程实际上是一种函数，所以存储过程即使没有参数，后仍然需要跟括号。
  • MySQL支持 IN（传递给存储过程）、OUT（从存储过程传出）和 INOUT（对存储过程传入和传出）类型的参数，每个参数必须具有指定的数据类型（种类与列数据类型相同）。注意，不能通过一个参数返回多个行和列。
  • 对于 OUT 和 INOUT 类参数，我们通常使用变量来传递相关参数，所有MySQL变量都必须以@开始。在使用变量传递相关参数时，调用存储过程不再显示任何数据，可以通过使用```SELECT @<变量名>`;``显示相关返回结果。
  • BEGIN 和 END 语句用来限定存储过程体，可以在 BEGIN 子句之前使用 COMMENT 关键字来为存储过程添加注释内容，但不是必需的。
  • 在存储过程体中，可以通过使用 INTO 关键字将查询结果保存到相应的变量，INTO 在 SELECT 语句中的位置，既可以跟在 SELECT 子句之后，也可以跟在 SELECT 语句最后。
  • 默认的MySQL语句分隔符为;，如果命令行实用程序要解释存储过程自身内的;字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。解决办法是临时更改命令行实用程序的语句分隔符，如下所示：

    DELIMITER //
    CREATE PROCEDURE <存储过程名称>([参数1], [参数2], ...)
    BEGIN
        <存储过程体>;
    END //
    DELIMITER ;
    1
    2
    3
    4
    5
    6

    DELIMITER // 告诉命令行实用程序使用//作为新的语句结束分隔符，可以看到标志存储过程结束的 END 定义为END //而不是 END ;。这样，存储过程体内的;仍然保持不动，并且正确地传递给数据库引擎。最后，使用DELIMITER ;恢复为原来的语句分隔符。除\符号外，任何字符都可以用作语句分隔符。
  • 可以使用SHOW CREATE PROCEDURE <存储过程名称>;来显示创建一个存储过程的 CREATE 语句，为了获得包括何时、由谁创建、注释内容等详细信息的存储过程列表，可以使用SHOW PROCEDURE STATUS;列出所有存储过程，也可以使用SHOW PROCEDURE STATUS LIKE '<过滤模式>';指定一个过滤模式。

• 执行存储过程
  MySQL称存储过程的执行为调用，因此 MySQL 执行存储过程的语句为 CALL 。 CALL 接受存储过程的名字以及需要传递给它的任意参数。基本语法如下：

  CALL <存储过程名称>([参数1], [参数2], ...)
  1

  • 调用存储过程可以有结果返回，也可以不返回结果；

• 删除存储过程
  基本语法如下：

  DROP PROCEDURE <存储过程名称>;
  1

  • 注意删除时没有使用后面的括号，只给出存储过程名；
  • 如果指定的过程不存在，则DROP PROCEDURE将产生一个错误。当过程存在想删除它时（如果过程不存在也不产生错误），可使用DROP PROCEDURE IF EXISTS <存储过程名称>;

• 存储过程示例
  考虑如下场景：计算某一顾客的订单合计金额，并只针对某些顾客增加营业税。sql 语句如下：

  CREATE PROCEDURE ordertotal(
  	IN onumber INT, 
  	IN taxable BOOLEAN,
  	OUT ototal DECIMAL(8,2)
  ) COMMENT 'Obtain order total, optionally adding tax'
  BEGIN
  
  	-- Declare variable for total
  	DECLARE tota1 DECIMAL(8,2);
  	-- Declare tax percentage
  	DECLARE taxrate INT DEFAULT 6;
  
  	-- Get the order tota1
  	SELECT Sum(item_ price*quantity)
  	FROM orderitems
  	WHERE order_num = onumber
  	INTO total;
  
  	-- Is this taxable?
  	IF taxable THEN
  		-- Yes，so add taxrate to the tota 1
  		SELECT total+(tota1/100*taxrate) INTO total;
  	END IF;
  	
  	-- And finally, save to out variable
  	SELECT total INTO ototal;
  END;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27

  • 该存储过程中有一个布尔类型（BOOLEAN ）的变量 taxable，该变量值指定为非零值时表示真，指定为 0 时表示假。
  • 在存储过程体中，用 DECLARE 语句定义了两个局部变量。 DECLARE 要求指定变量名和数据类型，它也支持可选的默认值。
  • IF 语句检查条件 taxable 是否为真，以确定是否执行后续相关操作。
  • 最后，用另一 SELECT 语句将结果保存到 ototal 中。

游标

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

使用游标具有以下几个步骤：

1. 在使用游标之前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句。
   • 游标用 DECLARE 语句创建：DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和其他子句。基本语法如下：

   CREATE PROCEDURE <存储过程名称>()
   BEGIN
       DECLARE <游标名称> CURSOR
       FOR
       <SELECT语句>；
   END;
   1
   2
   3
   4
   5
   6

2. 一旦声明后，必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。游标用 OPEN CURSOR 语句来打开：OPEN <游标名称>;，在处理 OPEN 语句时执行查询，存储检索出的数据以供浏览和滚动。
3. 对于填有数据的游标，根据需要取出（检索）各行。
   • 游标被打开后，可以使用 FETCH 语句分别访问它的每一行。
   • FETCH 指定检索什么数据（所需的列），检索出来的数据存储在什么地方：FETCH <游标名称> INTO <变量名称>;。
   • FETCH 将自动从第一行开始检索，检索完毕后它将继续移动游标中的内部行指针，使下一条 FETCH 语句检索下一行（不重复读取同一行）。
4. 在结束游标使用时，必须关闭游标：CLOSE <游标名称>;

注意：

• MySQL游标只能用于存储过程（和函数）。
• 在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后，可根据需要频繁地执行取操作。
• 存储过程处理完成后，游标就消失（因为它局限于存储过程）。
• CLOSE 释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。如果你不明确地关闭游标，MySQL将会在到达 END 语句时自动关闭它。
• 在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次 DECLARE 声明，用 OPEN 语句打开它就可以了。

游标使用示例

CREATE PROCEDURE processorders()
BEGIN
	-- Declare 1ocal variables
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	
	-- Declare the cursor
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- Declare conti nue hand ler
	DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000' SET done=1;
	
	-- Open the cursor
	OPEN ordernumbers;
	
	-- Loop through a11 rows
	REPEAT
		-- Get order number
		FEICH ordernumbers INIO o;
	-- End of 1oop
	UNTIL done END REPEAT;
	
	-- Close the cursor
	CLOSE ordernumbers;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

• 这个例子中的 FETCH 是在 REPEAT 内，因此它反复执行直到 done 为真（由UNTIL done END REPEAT; 规定）。为使它起作用，定义变量 done 的初始值为 0，那么 done 怎样才能在结束时被设置为真呢？DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000' SET done=1;语句中定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。该语句中，它指出当SQLSTATE '02000' 出现时，设置done=1。SQLSTATE '02000'是一个未找到条件，当 REPEAT 由于没有更多的行供循环而不能继续时，出现这个条件。

• DECLARE 语句的发布存在特定的次序。用 DECLARE 语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义，不遵守此顺序将产生错误消息。（从广义上，能够从一个数值拎起一大堆数据的东西都可以叫做句柄（Handle）。）

5. 触发器

触发器是 MySQL 响应 DELETE、INSERT 或 UPDATE 语句而自动执行的一条 MySQL 语句（或位于 BEGIN 和 END 语句之间的一组语句）。

• 创建触发器
  在创建触发器时，需要给出以下4条信息：(1) 唯一的触发器名；(2) 触发器关联的表；(3) 触发器应该响应的活动（DELETE、 INSERT 或 UPDATE）；(4) 触发器何时执行（处理之前或之后）。基本语法如下：

  CREATE TRIGGER <触发器名称> AFTER/BEFORE INSERT/DELETE/UPDATE ON <表名>
  FOR EACH ROW <单条 SELECT 语句>;
  
  or
  
  CREATE TRIGGER <触发器名称> AFTER/BEFORE INSERT/DELETE/UPDATE ON <表名>
  FOR EACH ROW 
  BEGIN
      <单条或多条 SELECT 语句>;
  END;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

  注意：

  • 应该尽量保持每个数据库的触发器名唯一。
  • 只有表才支持触发器，视图不支持（临时表也不支持）。
  • 触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器。因此，每个表最多支持6个触发器（每条 INSERT、 UPDATE 和 DELETE 的之前和之后），单一触发器不能与多个事件或多个表关联。
  • 如果 BEFORE 触发器失败，则MySQL将不执行请求的 DELETE、INSERT 或 UPDATE 操作；如果 BEFORE 触发器或语句本身失败，MySQL 将不执行 AFTER 触发器（如果有的话）。
  • 触发器可以使用 BEGIN 和 END 语句标记触发器体，使用 BEGIN END 块的好处是触发器能容纳多条SQL 语句。

• 删除触发器
  基本语法如下：

  DROP TRIGGER <触发器名称>;
  1

  触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

• 触发器种类

  • INSERT 触发器
    • 在 INSERT 触发器代码内，可引用一个名为 NEW 的虚拟表，访问被插入的行，例如SELECT NEW.<列名>；
    • 在 BEFORE INSERT 触发器中， NEW 中的值也可以被更新（允许更改被插入的值）；
    • 对于 AUTO_INCREMENT 列， NEW 在 INSERT 执行之前包含 0 ，在 INSERT 执行之后包含新的自动生成值。
    • 通常，将 BEFORE 用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。这一规则也适用于 UPDATE 触发器。
  • DELETE 触发器
    • 在 DELETE 触发器代码内，你可以引用一个名为 OLD 的虚拟表，访问被删除的行；
    • OLD 中的值全都是只读的，不能更新。
    • 使用 BEFORE DELETE 触发器的优点（相对于 AFTER DELETE 触发器来说）为，如果由于某种原因，订单不能存档， DELETE 本身将被放弃。
  • UPDATE 触发器
    • 在 UPDATE 触发器代码中，你可以引用一个名为 OLD 的虚拟表访问以前（ UPDATE 语句前）的值，引用一个名为 NEW 的虚拟表访问新更新的值；
    • 在 BEFORE UPDATE 触发器中， NEW 中的值可能也被更新（允许更改将要用于 UPDATE 语句中的值）；
    • OLD 中的值全都是只读的，不能更新。

MySQL触发器中不支持 CALL 语句，这表示不能从触发器内调用存储过程，所需的存储过程代码需要复制到触发器内。

6. 字符集和校对顺序

数据库表被用来存储和检索数据，不同的语言和字符集需要以不同的方式存储和检索。因此，MySQL需要适应不同的字符集（不同的字母和字符），适应不同的排序和检索数据的方法。

在讨论多种语言和字符集时，会遇到以下术语：① 字符集：字母和符号的集合；② 编码：某个字符集成员的内部表示；③ 校对：规定字符如何比较的指令。

MySQL 支持众多的字符集。使用SHOW CHARACTER SET;语句显示所有可用的字符集以及每个字符集的描述和默认校对；使用SHOW COLLATION'语句显示所有可用的校对及其适用的字符集，部分字符集具有不止一种校对，而且许多校对出现两次，一次区分大小写（由 _cs 表示），一次不区分大小写（由 _ci 表示）。

通常系统管理在安装时定义一个默认的字符集和校对，也可以在创建数据库时，指定默认的字符集和校对。为了确定所用的字符集和校对，可以使用语句SHOW VARIABLES LIKE 'character%'; or SHOW VARIABLES LIKE 'collation%';

为了给表指定字符集和校对，可使用带子句的 CREATE TABLE（若未指定，则使用默认）：

CREATE TABLE <表名>
(
    <列的定义>
) DEFAULT CHARACTER SET <指定字符集>
  COLLATE <指定校对>;
1
2
3
4
5

MySQL还允许对每个列设置指定字符集和校对：

CREATE TABLE <表名>
(
    col1 varchar(10) DEFAULT CHARACTER SET <指定字符集> COLLATE <指定校对>,
    col2...
) DEFAULT CHARACTER SET <指定字符集>
  COLLATE <指定校对>;
1
2
3
4
5
6

校对在对用 ORDER BY子句检索出来的数据排序时起重要的作用。如果需要用与创建表时不同的校对顺序排序特定的 SELECT 语句，可以在 SELECT 语句自身中进行：

SELECT *
FROM <表名>
ORDER BY <列1>, <列2> COLLATE <指定校对>;
1
2
3

除了在 ORDER BY 子句中使用以外， COLLATE 还可以用于 GROUP BY 、 HAVING 、聚集函数、别名等。此外，如果绝对需要，串可以在字符集之间进行转换。为此，使用Cast(<列名> AS <指定字符集>)或Convert(<列名> USING <指定字符集>)函数。

7. 安全管理

需要给用户提供所需的访问权，且仅提供所需的访问权。这就是所谓的访问控制，管理访问控制需要创建和管理用户账号。

可使用如下代码获得所有用户账号列表：

USE mysql;
SELECT user FROM user;
1
2

可使用 CREATE USER 语句创建一个新用户账号：CREATE USER <用户名> IDENTIFIED BY '<口令>'，IDENTIFIED BY 指定的口令为纯文本，MySQL将在保存到 user 表之前对其进行加密，在创建用户账号时不一定需要口令。

使用RENAME USER <旧用户名称> TO <新用户名称>;语句重新命名一个用户账号，使用DROP USER <用户名>:语句删除一个用户账号和所有相关的账号权限

使用SHOW GRANTS FOR <用户名>;查看赋予用户账号的权限，如果输出结果显示用户只有一个权限GRANT USAGE ON *.* TO '<用户名>'@'%';，表示用户在任意数据库和任意表上对任何东西没有权限。MySQL的权限用用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名% （授予用户访问权限而不管主机名）。

为设置权限，使用 GRANT 语句。 GRANT 要求至少给出以下信息：

• 要授予的权限；
• 被授予访问权限的数据库或表；
• 用户名。

例如使用GRANT SELECT ON <数据库>.* TO <用户名>;语句允许用户在指定数据库的所有表上使用 SELECT，通过只授予 SELECT 访问权限，用户对指定数据库中的所有数据具有只读访问权限。每条 GRANT 语句添加（或更新）用户的一个权限，也可通过列出各权限并用逗号分隔，将多条 GRANT 语句串在一起。

GRANT 的反操作为 REVOKE ，用它来撤销特定的权限：REVOKE SELECT ON <数据库>.* FROM <用户名>;，被撤销的访问权限必须存在，否则会出错。

GRANT 和 REVOKE 可以在以下几个层次上控制访问权限：

• 整个服务器，使用GRANT ALL 和 REVOKE ALL；
• 整个数据库，使用ON database.*；
• 特定的表，使用ON database.table；
• 特定的列；
• 特定的存储过程。

下表列出了可以授予或撤销的每个权限：

在使用 GRANT 和 REVOKE 时，用户账号必须存在，但对所涉及的对象（数据库和表）没有这个要求。这允许管理员在创建数据库和表之前设计和实现安全措施。这样做的副作用是，当某个数据库或表被删除时（用 DROP 语句），相关的访问权限仍然存在。而且，如果将来重新创建该数据库或表，这些权限仍然起作用。

使用SET PASSWORD FOR <用户名> = Password('<新口令>')语句更新用户口令，新口令必须传递到 Password() 函数进行加密。还可以通过SET PASSWORD = Password('<新口令>')设置当前登录用户口令。

8. 数据库维护

• 数据备份
  由于MySQL数据库是基于磁盘的文件，普通的备份系统和例程就能备份MySQL的数据。但是，由于这些文件总是处于打开和使用状态，普通的文件副本备份不一定总是有效。下面列出了这个问题的可能解决方案：

• 使用命令行实用程序 mysqldump 转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。

  [root@]> mysqldump -help
  
  Usage: mysqldump [OPTIONS] database_name [tables]
  OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
  OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
  For more options, use mysqldump --help
  
  1.登录选项：
      -u user：指定用户
      -h host：指定主机
      -p：表示要使用密码
      -E, --events: 备份事件调度器
      -R, --routines: 备份存储过程和存储函数
  2.备份选项：
      --all-databases：备份所有数据库
      --databases db1 db2：备份指定的数据库
      --single-transaction：对事务引擎执行热备
      --flush-logs：更新二进制日志文件
      --master-data=2
          1：每备份一个库就生成一个新的二进制文件(默认)
          2：只生成一个新的二进制文件
      --quick：在备份大表时指定该选项
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22

• 可用命令行实用程序 mysqlhotcopy 从一个数据库复制所有数据，mysqlhotcopy 只是简单的缓存写入和文件复制的过程，备份速度比 mysqldump 快很多很多，特别适合大的数据库，但 mysqlhotcopy 只支持 MyISAM 引擎。

  mysqlhotcopy [option] db_name1 db_name2 <备份目标目录>
  1

• 可以使用MySQL的BACKUP TABLE <表名> TO '<文件存储路径>';或SELECT * INTO '<文件存储路径或表名>' FROM <表名>;转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE <表名1>, <表名2>, ... FROM '<文件路径>';语句来复原。

  为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句关闭所有的表（包括正在使用的表）。

• 数据库维护
  ANALYZE TABLE <表名>;用来检查表键是否正确，CHECK TABLE <表名1>, <表名2>, ... [option] ;用来针对许多问题对表进行检查（在 MyISAM 表上还对索引进行检查）。

  CHECK TABLE中的option选项支持一系列的用于 MyISAM 表的方式：CHANGED 检查自最后一次检查以来改动过的表。 EXTENDED 执行最彻底的检查， FAST 只检查未正常关闭的表， MEDIUM 检查所有被删除的链接并进行键检验， QUICK 只进行快速扫描。

  如果 MyISAM 表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE <表名>;来修复相应的表；如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE <表名>;来收回所用的空间，从而优化表的性能。

• 诊断启动
  MySQL 服务器自身通过在命令行上执行 mysqld 启动。下面是几个重要的 mysqld 命令行选项：

  • –help 显示帮助——一个选项列表；
  • –safe-mode 装载减去某些最佳配置的服务器；
  • –verbose 显示全文本消息（为获得更详细的帮助消息与 --help 联合使用）；
  • –version 显示版本信息然后退出。

• 建议

  • 可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间），还可以用 KILL 命令终结某个特定的进程。

十一、MySQL系统

1. MySQL的配置相关文件

MySQL 中有以下主要配置文件：

• 二进制日志 log-bin：用于主从复制，记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为 hostname-bin ，位于 data 目录内。此名字可以用 --log-bin 命令行选项更改。
• 错误日志 log-error：默认关闭，记录严重的警告和错误信息，每次启动和关闭的详细信息等。此日志通常名为 hostname.err ，位于 data 目录中。此日志名可用 --log-error 命令行选项更改。
• 查询日志 show-log：默认关闭，记录查询的 sql 语句，如果开启会降低 mysql 的整体性能，因为记录日志也是需要消耗系统资源的。此日志通常名为 hostname.log ，位于 data 目录中。此名字可以用 --log 命令行选项更改。
• 缓慢查询日志：此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为 hostname-slow.log ， 位于 data 目录中 。此名字可以用 --log-slow-queries 命令行选项更改。
• frm 文件：存放表结构。
• myd 文件：存放表数据。
• myi 文件：存放表索引。

注意：

• 特别提出 MySQL 中的重要配置文件：Windows 下名为 my.ini，Linux下为 /etc/my.cnf。对于服务器的调优相关过程都在改配置文件中，需要特别掌握。
• my.ini 文件在隐藏文件夹里，一开始挺难找的，我的电脑此文件是在 “C:\ProgramData\MySQL\MySQL Server 8.0\my.ini” 路径下。
• InnoDB存储引擎下，未必有 frm、myd 和 myi 文件，MyISAM 才有这三个文件。
• 在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

2. MySQL逻辑架构

MySQL的逻辑框架主要分为四层：

• 连接层；
• 服务层（主要进行sql语句相关的操作）；
• 引擎层（注意引擎层是可拔插的）；
• 存储层。
  
  MySQL 架构非常优良，主要体现在存储引擎上。MySQL是插件式的存储引擎，它可以将查询处理和其他的系统任务以及数据的存储提取相分离。通过分层和可插拔式的架构，可以根据不同的生产环境构建最优的系统。

3. MySQL存储引擎

可以通过 "show engines"命令查看MySQL支持的存储引擎：

也可以通过 “show variables like ‘%storage_engine%’” 查看MySQL的当前默认存储引擎：

这里主要对MyISAM和InnoDB进行比较，主要区别如下表：

十二、索引

索引在 sql 调优部分占据着重要的位置，了解并深入索引对我们来说也是非常重要的。

1. 基本概念

MySQL官方对索引的定义如下：索引（Index）是帮助 MySQL 高效获取数据的数据结构。因此索引的本质就是数据结构。索引的目的在于提高查询效率，可类比字典、书籍的目录等这种形式。

简单来说，索引是关系数据库中对某一列或多个列的值进行预排序的快速查找数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

平常所说的索引，如果没有特别指明，都是B树索引。其中聚集索引、次要索引、覆盖索引、前缀索引、唯一索引默认都是用B树。

通过 “show index from <表名>” 可以查看表的索引情况：

2. 索引的优缺点

优点：

• 类似图书馆的书目索引，提高数据的检索效率，降低数据库的IO成本；
• 通过索引列对数据进行排序，降低数据的排序成本，从而降低CPU的消耗。

缺点：

• 索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间；
• 虽然索引大大提高了查询效率，但是降低了更新表的速度，如 INSERT、UPDATE 和 DALETE 操作。因为更新表时，MySQL不仅要保存数据，还要保存索引文件每次更新的索引列字段，并且在更新操作后，会更新相应字段索引的信息；
• 索引只是提高查询效率的一个因素，如果你的 MySQL 有大量的数据表，就需要花时间研究建立最优秀的索引或优化查询语句。

3. 索引分类

索引主要分为以下三类：

• 单值索引：一个索引只包含单个列，一个表可以有多个单值索引。
• 唯一索引：索引列的值必须唯一，但允许有空值，主键就是唯一索引。
• 复合索引：一个索引包含多个列。

索引的结构：

• BTREE 索引；
• Hash 索引；
• Full-Text 索引；
• R-Tree 索引。

4. 基本语法

①创建索引

create index <索引名称> on <表名> (<列名>(长度));

alter table <表名> add index <索引名称> (<列名>(长度));
1
2
3

注意：

• 语法中长度的含义是：指定使用列前的 length 个字符来创建索引。

②删除索引

drop index <索引名称> on <表名>;
1

③查看索引

show index from <表名>;
1

④其他索引的创建方式

1> 添加主键索引

ALTER TABLE <表名> ADD PRIMARY KEY (<列名>);
1

2> 添加唯一索引

ALTER TABLE <表名> ADD UNIQUE (<列名>);
1

也可以只对某一列添加一个唯一约束而不创建唯一索引：

ALTER TABLE <表名> ADD CONSTRAINT <约束名称> UNIQUE (<列名>);
1

这种情况下，列并没有索引，但仍然具有唯一性保证。

3> 添加全文索引

ALTER TABLE <表名> ADD FULLTEXT (<列名>);
1

4> 添加普通索引

ALTER TABLE <表名> ADD INDEX <索引名称> (<列名>);
1

5> 添加组合索引

ALTER TABLE <表名> ADD INDEX <索引名称> (<列名1>, <列名2>, <列名3>, ...);
1

5. 何时该建立索引？

需建立索引的情况：

• 主键自动建立唯一索引
• 频繁作为查询条件的字段
• 查询中与其他表关联的字段，外键关系建立索引
• 高并发下趋向创建组合索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或分组字段

不需要创建索引的情况：

• 表记录太少，数据量太少MySQL自己就可以搞定
• 经常增删改的表
• 数据重复且平均分配的字段，不适合创建索引。
  索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，如国籍、性别，那么对该列创建索引就没有意义。
• 频繁更新的字段不适合建立索引
• WHERE 条件里用不到的字段不创建索引

十三、执行计划

使用 EXPLAIN（执行计划）关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。explain主要用于分析查询语句或表结构的性能瓶颈。

1. EXPLAIN 作用

EXPLAIN 的基本语法很简单：

EXPLAIN + sql 语句;
1

通过 EXPLAIN + sql 语句可以知道如下内容：

• 执行 select 子句或表的顺序（对应 id ）
• 数据读取操作的查询类型（对应 select_type ）
• sql 操作属于哪张表（对应 table）
• table 所在的分区（对应 partitions）
• 查询所使用的访问类型（对应 type）
• 哪些索引可能被应用（对应 possible_keys）
• 哪些索引被实际使用（对应 key）
• 查询中使用的索引长度（对应 key_len）
• 显示关联的字段（对应 ref）
• 每张表有多少行被优化器查询（对应 rows）
• 存储引擎返回的数据经过滤后的保留比例（对应 filtered）
• 显示十分重要的额外信息（对应 Extra）

2. EXPLAIN 结果字段解释

我们假设创建了如下两个表：

• tb_emp 表（其中 id 字段为主键自增字段）

• tb_dept 表（其中 id 字段为主键自增字段）

我们执行如下 sql 语句：

explain select * from tb_emp;
1

得到的结果如下所示：

① id
id 代表 select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序，该字段通常与 table 字段搭配来分析。

1> id相同，执行顺序从上到下

2> id 不同，如果是子查询 id 的序号会递增，id 值越大执行优先级越高

3> id 同时存在相同和不同

id 如果相同，可以认为是同一组，执行顺序从上到下。在所有组中，id 值越大执行优先级越高。所以执行顺序为：t3 -> derived2（衍生表，也叫临时表） -> t2。

总结：id 的值表示 select 子句或表的执行顺序。id相同，执行顺序从上到下，id不同，值越大的执行优先级越高。

② select_type
select_type 代表查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：

1> SIMPLE

简单的 select 查询，查询中不包含子查询或 union 查询。

2> PRIMARY

查询中若包含任何复杂的子部分，最外层查询为 PRIMARY，也就是最后加载的就是 PRIMARY。

3> SUBQUERY

在 select 或 where 子句中包含了子查询，就为被标记为 SUBQUERY。

4> DERIVED

在 from 子句中包含的子查询会被标记为 DERIVED（衍生），MySQL会递归执行这些子查询，将结果放在临时表中。

5> UNION

若第二个 select 出现在 union 后，则被标记为 UNION。
若 union 包含在 from 子句的子查询中，union 中的前一个 select 将被标记为 DERIVED。

6> UNION RESULT

从 union 表获取结果的 select。

③ table

显示 sql 操作属于哪张表。

④ partitions

官方定义为 The matching partitions（匹配的分区），该字段应该是看 table 所在的分区（NULL 表示表未被分区）。

⑤ type

表示查询所使用的访问类型，我的理解是：type 字段表示你使用的 select 语句在对表进行检索行（筛选行）操作时使用了哪种类型的访问方式。

type 的值表示使用的查询 sql 语句的好坏，从最好到最差依次为：system-> const -> eq_ref -> ref -> range -> index -> ALL。
有一些不常用的 type 值没有给出。一般来说，需保证查询至少达到 range 级别，最好能达到 ref。

1> system
表只有一行记录（等于系统表），是 const 的特例类型，平时不会出现，可以忽略不计。在 MySQL 8.0 版本时，不会出现该字段值，只能出现 const，但是在 MySQL 5.5.48 版本可以出现该情况。猜测 MySQL 8.0 版本可能是进行了优化。

5.5.48：

8.0：

注：两个引擎的执行信息不一样，5.5.4 8执行过程中产生了临时表（DERIVED），8.0 为简单查询。

2> const

表示通过一次索引就找到了结果，常出现于 primary key 或 unique 索引。因为只匹配一行数据，所以查询非常快。如将主键置于 where 条件中，MySQL 就能将查询转换为一个常量。

注意：

• 对于 system 和 const，实际意义并不是很大，因为单表单行查询本来就快。

3> eq_ref

唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描。

4> ref

非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某个条件的多行值，属于查找和扫描的混合体。由于是非唯一性索引扫描，所以我们在这里对 tb_emp 表的 deptid 字段创建索引：

create index idx_tb_emp_deptid on tb_emp(deptid);
1

5> range
只检索给定范围的行，使用一个索引（必须是索引）来检索行，一般出现在 where 语句的条件中，例如使用 between、>、<、in、and、or 等查询。这种索引的范围扫描比全索引扫描要好，因为索引的开始点和结束点都固定，范围相对较小。

6> index
全索引扫描，index 和 ALL 的区别：index 只遍历索引树，通常比 ALL 快，因为索引文件通常比数据文件小。虽说 index 和 ALL 都是全表扫描，但是 index 是从索引中读取，ALL 是从磁盘中读取。

7> ALL
全表扫描。

⑥ possible_keys、key 和 key_len

possible_keys：显示可能应用在表中的索引，可能一个或多个。查询涉及到的字段若存在索引（如下图所示，deptid 列和 name 列均设置了单值索引），则该索引将被列出，但不一定被查询实际使用。

key：实际中使用的索引，如果为 NULL，则表示未使用索引。若查询中使用了覆盖索引，则该索引和查询的 select 字段重叠。覆盖索引的定义：select 的数据列只从索引中就能取得数据，不必读取数据行。

key_len：表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，并不是通过表内检索得出的。

简单来说，possible_keys 表示理论上可能用到的索引，key 表示实际中使用的索引。

possible_keys 为 NULL 表示可能未用到索引，但 key=idx_tb_emp_deptid_name 表示在实际查询的过程中进行了索引的全扫描。

在使用索引查询时，当条件越精确，key_len 的长度可能会越长，所以在不影响结果的情况下，key_len的值越短越好。

⑦ ref

显示关联的字段。如果使用常数等值查询，则显示 const；如果是连接查询，则会显示关联的字段。如果使用的列不是索引列，那么 ref 会显示为 NULL。

注意：

• 由于id相同，因此从上到下执行；
• tb_emp 表为非唯一性索引扫描，实际使用的索引列为 idx_tb_emp_name，由于tb_emp.name=‘rose’ 为一个常量，所以 ref 为 const；
• tb_dept 为唯一索引扫描，从 sql 语句可以看出，实际使用了 PRIMARY 主键索引，ref 为 shop.tb_emp.deptid 表示关联了 shop 数据库中 tb_emp 表的 deptid 字段。

⑧ rows

根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数，即每张表有多少行被优化器查询，当然该值越小越好。

⑨ filtered

百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例。

⑩ Extra

显示十分重要的额外信息。其取值有以下几个：

1> Using filesort

Using filesort 表明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql 中无法利用索引完成的排序操作称为“文件排序”。

出现 Using filesort 就非常危险了，在数据量非常大的时候几乎“九死一生”。出现Using filesort 尽快优化 sql 语句。

比如 deptname 字段未建索引的情况：

为 deptname 字段创建索引后：

2> Using temporary

使用了临时表保存中间结果，常见于排序 order by 和分组查询 group by。非常危险，“十死无生”，急需优化。

例如，将 tb_emp 中 name 的索引先删除，出现如下图 Using temporary 结果，非常烂，“十死无生”：

为 name 字段创建索引后：

3> Using index

表明相应的 select 操作中使用了覆盖索引，避免访问表的额外数据行，效率不错。如果同时出现了 Using where，表明存在索引被用来执行索引键值的范围查询（where id>1）；如果没有同时出现 Using where，表明索引未用来执行范围查询动作。

例如，删除 tb_emp 表中 name 和 deptid 字段的单独索引，创建复合索引。

上面的例子中，我们创建了（name, deptid）的复合索引，查询的时候也使用复合索引或部分，这就形成了覆盖索引。简记：查询使用复合索引，并且查询的列就是索引列，不能多，个数需对应。

使用优先级 Using index > Using filesort > Using temporary，也就说出现后面两项表明 sql 语句是非常烂的，急需优化！！！

3. 总结

EXPLAIN（执行计划）包含的信息十分的丰富，着重关注以下几个字段信息：

• id + select子句或表：代表执行顺序，id 相同，从上到下执行，id 不同，id 值越大，执行优先级越高。
• type：type 表示查询的 sql 语句的好坏程度（由好到差排序）：system>const>eq_ref>ref>range>index>ALL。保证 range，最好到 ref。
• key：实际被使用的索引列。
• ref：表示关联的字段，常量等值查询显示为 const，连接查询则显示关联的字段。
• Extra：额外信息，使用优先级 Using index > Using filesort（九死一生）> Using temporary（十死无生）。

着重关注上述五个字段信息，对日常生产过程中调优十分有用。

十四、索引分析与优化

1. 索引分析

数据准备

我们假设创建了如下三个表：

• tb_emp 表（其中 id 字段为主键自增字段）

• tb_dept 表（其中 id 字段为主键自增字段）

• tb_desc 表（其中 id 字段为主键自增字段）
  
  这里强行将员工表与部门表不直接关联，通过第三张表（描述表）进行关联，主要为了进行 join 的分析。

LEFT JOIN 和 RIGHT JOIN

①首先对员工表和描述表执行 LEFT JOIN 操作：

select e.id, e.username, d.empid from tb_emp e left join tb_desc d on e.id=d.empid;
1

执行结果如下：

②对上述语句通过explain进行分析：

explain select e.id, e.username, d.empid from tb_emp e left join tb_desc d on e.id=d.empid;
1

结果如下：

从 explain 执行结果可以看出对两表都是用了全表扫描（ALL），并且在 tb_desc 表中还使用了 join 连接缓存，需要进行优化。但是如何优化？是在左表建立索引还是右表建立索引呢？

因为左连接左表是全有，所以左表的每一行数据都会包含，因此建立索引没有太大意义，而右表不一定包含所有的数据行，因此应该在右表建立索引实现快速查找。

③右表创建索引：

create index idx_empid on tb_desc(empid);
1

通过 explain 执行可以看到，在创建索引后，获得了比较不错的结果（type=ref，Extra=Using index）。

结论：left join（左连接）情况下，应该在右表创建索引。

④ RIGHT JOIN

我们直接交换两表位置，并将 left join 改变成 right join：

explain select e.id, e.username, d.empid from tb_desc d right join tb_emp e on e.id=d.empid;
1

结果如下：

与 left join 进行对比，可以得到如下结论：

• 在 left join 下，首先执行tb_emp（左表），type=ALL，因为左连接情况下左表全有，因此我们在 tb_desc（右表）创建索引，得到比较理想的效果。
• 在right join下（我们交换了tb_emp和tb_desc的位置），执行顺序：tb_emp（右表）→ tb_desc（左表）。右表 type=ALL，因为右连接情况下右表全有，因此在左表（tb_desc）创建索引，效果和 left join 一样。

总结

• left join（左连接）：右表创建索引。
• right join（右连接）：左表创建索引。
• 简记：左右外连接，索引相反建（left：右表建，right：左表建）。

2. 索引优化

索引优化的目的主要是让索引不失效。

数据准备

我们假设创建了如下表格：

• tb_emp 表（其中 id 字段为主键自增字段）
  

索引优化规则

①最佳左前缀法则

法则：在利用多列创建了复合索引的情况下，查询从索引的最左列开始且不能跳过索引中的列。

简单来说，如果利用多个列创建了复合索引，在使用索引时要按照创建索引的顺序来使用，不能缺少或跳过，当然只依次使用左边的索引列是可以的。通俗理解：“带头大哥不能死，中间兄弟不能断”。要点：“头不能掉”。

Case 1
下面创建组合索引，并执行 explain：

create index idx_nameagegender on tb_emp (name,age, gender);

explain select * from tb_emp where name= 'Tom' ;
1
2
3

explain select * from tb_emp where name='Tom' and email="1@qq.com";
1

分析：

• 索引的创建顺序为 name，age，gender；
• 直接使用 name（带头大哥）作为条件，可以看到 type=ref，key_len=82，ref=const，效果还不错；
• 查询条件中出现了非索引列时不影响 type、key_len 和 ref 等字段。

Case 2

explain select * from tb_emp where age=22;
1

分析：

• 没使用带头大哥（name），直接用兄弟，type=ALL，为全表扫描。

Case 3

explain select * from tb_emp where name='Tom' and age=22;

explain select * from tb_emp where name='Tom' and gender= 'male';
1
2
3

分析：

• 对比上面两句 sql 语句可发现：我们分别使用了火车头（name）和中间车厢（age）、火车头（name）和车尾（gender）。
• 虽然 type=ref，但是观察 key_len 和 ref 两项，并对比 Case1 中的结果，可得出在使用火车头（name）和车尾（gender）时，只使用了部分索引也就是火车头（name）的索引。
• 通俗理解：火车头单独跑没问题，火车头与直接相连的车厢一起跑也没问题，但是火车头与车尾，如果中间没有车厢，只能火车头自己跑。
• 注意，在执行常量等值查询时，改变索引列的顺序并不会更改 explain 的执行结果，例如本例中第一条语句完全可以写成 explain select * from tb_emp where age=22 and name='Tom';

Case 4

explain select * from tb_emp where name='Tom'and age=22 and gender= 'male';
1

分析：

• 火车头加车厢加车尾，三者串联，就变成了奔跑的小火车。
• type=ref，key_len=128，ref=const, const, const。

②不要在索引列上做任何操作

在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效从而转向全表扫描。

Case 1

explain select * from tb_emp where left(name,3)='Tom';
1

结果如下：

分析：

• left 函数的作用就是取 name 字符串最左边的三个字符；
• 这里使用了函数计算，type=ALL，导致索引失效。

Case 2

explain select * from tb_emp where name=123;
1

结果如下：

分析：

• 我们将 name=‘Tom’ 的值修改成了‘123’；
• 在使用 sql 时，我们将 name 的值与数123进行比较，由于 name 列本来是字符串类型，因此在进行比较时会自动进行类型转换，type=ALL，导致全表扫描。

③范围右边全失效

存储引擎不能使用索引中范围右边的列，也就是说范围右边的索引列会全部失效。这里的右边指的是创建复合索引时的顺序，而不是 where 子句中的顺序。

Case 1

explain select * from tb_emp where name='Jack';
1

结果如下：

Case 2

explain select * from tb_emp where name='Jack' and age=27;
1

结果如下：

Case 3

explain select * from tb_emp where name='Jack' and age=27 and gender='male';
1

结果如下：

Case 4

explain select * from tb_emp where name='Jack' and age>27 and gender='male';
1

结果如下：

对以上4个case进行分析：

• 条件单独使用name时，type=ref，key_len=82，ref=const。
• 条件加上age时（使用常量等值），type=ref，key_len=86，ref=const，const。
• 当全值匹配时，type=ref，key_len=128，ref=const，const，const。说明索引全部用上，从 key_len 与 ref 可以看出这一点。
• 当使用范围时（age>27），type=range，key_len=86，ref=Null，与Case 1、Case2和Case3可知，使用了部分索引，但gender索引没用上（与Case 3对比）。

④尽量使用覆盖索引

尽量使用覆盖索引（查询列和索引列尽量一致，通俗说就是对 A、B 列创建了索引，然后查询中也使用 A、B 列），减少 select * 的使用。

Case 1

explain select * from tb_emp where name='Jack' and age=27 and gender='male';
1

结果如下：

Case 2

explain select name, age, gender from tb_emp where name='Jack' and age=27 and gender='male';
1

结果如下：

分析：

• 对比 Case1 和 Case2，Case1 使用 select *，Case2 使用覆盖索引（查询列与条件列对应），可看到 Extra 从 Null 变成了 Using index，提高检索效率。

⑤使用不等于（！=或<>）会使索引失效

Case 1

explain select * from tb_emp where name !='Jack';
1

结果如下：

分析：

• 本来在 MySQL5.x 版本使用 != 会使 type=ALL，key=Null，导致全表扫描，并且索引失效；
• 但是在 MySQL8.0 版本使用 != 时，type=range，key 也存在，即索引未失效，因此该规则不再适用，应该是 MySQL 进行了优化。

⑥ is null 或 is not null也无法使用索引

Case 1

explain select * from tb_emp where name is null;
1

结果如下：

Case 2

explain select * from tb_emp where name is not null;
1

结果如下：

分析：

• 在使用 is null 的时候，索引完全失效，使用 is not null 的时候，type=ALL 全表扫描，key=Null 索引失效。
• 这里的例子可能有点特殊，具体情况可能和 case1 的有所不同，但是还是要注意 is null 和 is not null 的使用。

⑦ like 谓词以 % 开头会使索引失效

Case 1

explain select * from tb_emp where name like '%JACK%';
1

结果如下：

Case 2

explain select * from tb_emp where name like '%JACK';
1

结果如下：

Case 3

explain select * from tb_emp where name like 'JACK%';
1

结果如下：

分析：

• like 的 % 位置不同，所产生的效果不一样，当 % 出现在左边的时候，type=ALL，key=Null（全表扫描，索引失效），当%出现在右边的时候，type=range，索引未失效。
• like 查询为范围查询：% 出现在左边，则索引失效；% 出现在右边，索引未失效。

在实际生产环境中，% 仅出现在右边可能不能够解决我们的问题，所以解决 % 出现在左边导致索引失效的方法是使用覆盖索引：

Case 4

explain select name from tb_emp where name like '%JACK%';
1

结果如下：

分析：

• 对比 Case1 可知，通过覆盖索引 type=index，并且使用了 Using index，从全表扫描变成了全索引扫描。

Case 5

explain select id from tb_emp where name like '%JACK%';
1

结果如下：

分析

• 这里出现了 type=index，因为主键自动创建唯一索引。

Case 6

explain select age from tb_emp where name like '%Jack%';

explain select name, age from tb_emp where name like '%Jack%';

explain select name, age, gender from tb_emp where name like '%Jack%';

explain select id, name, age, gender from tb_emp where name like '%Jack%';
1
2
3
4
5
6
7

结果如下：

分析：

• 上面四组 explain 执行的结果相同，表明都使用了索引，从这里可以深刻地体会到覆盖索引：完全吻合或者沾边（age），都可以使 type=index。

Case 7

explain select id, name, age, gender, email from tb_emp where name like '%Jack%';
1

结果如下：

分析：

• 由于只在（name，age，gender）上创建索引，当包含email时，导致结果集偏大（email未建索引），“锅大，锅盖小，不能匹配”，所以 type=ALL。

⑧字符串不加单引号导致索引失效

varchar类型的字段，在查询的时候不加单引号会导致索引失效，转向全表扫描。其实这种情况我们在前面情况②中已经讨论过了，相当于在进行比较时索引列发生了类型的自动转换，导致索引失效。

⑨少用 or，因此用 or 连接会使索引失效

Case 1

explain select * from tb_emp where name='Jack'or name='Mary ';
1

结果如下：

分析：

• 在 MySQL5.x 版本时候，使用 or 连接时 type=ALL，key=Null，索引失效，并全表扫描。
• 但是在 MySQL8.0 版本时，使用 or 连接时 type=range，索引也未失效，猜想可能是进行了版本优化，因此该规则不再适用。

索引优化总结

• 最佳左前缀法则：带头大哥不能死，中间兄弟不能断
• 索引列上不进行任何操作
• 范围右边的索引列全部失效
• 尽量使用覆盖索引
• 不等于（！=或<>）会使索引失效（已不适用）
• is null、is not null 会导致索引失效
• like 百分号加右边，加左边导致索引失效，解决方法是使用覆盖索引
• 字符串不加单引号导致索引失效
• 少用or，用or导致索引失效（已不适用）

3. 索引题分析

数据准备

我们假设创建了如下表格：

• test 表（其中 id 为主键自增字段）：
  

我们对表的 c1、c2、c3 和 c4 字段创建复合索引：

 create index idx_test_c1234 on test(c1,c2,c3,c4);
1

题目分析

Case 1：常量等值查询索引列的顺序

explain select * from test where c1='a1' and c2='a2' and c3='a3' and c4='a4';

explain select * from test where c1='a1' and c3='a3' and c2='a2' and c4='a4';

explain select * from test where c1='a1' and c4='a4' and c3='a3' and c2='a2';

explain select * from test where c4='a4' and c3='a3' and c2='a2' and c1='a1';
1
2
3
4
5
6
7

结果如下：

分析：

• 创建复合索引的顺序为 c1，c2，c3，c4。
• 上述四组explain执行的结果都一样：type=ref，key_len=132，ref=const, const, const, const。

结论：

• 在执行常量等值查询时，改变索引列的顺序并不会更改explain的执行结果，因为 mysql 底层优化器会进行优化，但是推荐按照索引顺序列编写sql语句。

Case 2：范围右边索引列

explain select * from test where c1='a1' and c2='a2';
1

结果如下：

explain select * from test where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
1

结果如下：

分析：

• 当出现范围的时候，type=range，key_len=99，比不用范围 key_len=66 增加了，说明使用上了索引，但对比 Case1 中执行结果，说明 c4 上索引失效。

结论：

• 范围右边（这里的顺序是指创建索引时列的顺序）索引列失效，但是范围当前位置（c3）的索引是有效的，从 key_len=99 可证明。

explain select * from test where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
1

结果如下：

分析：

• 与上面 explain 执行结果对比，key_len=132 说明索引用到了4个，因为对此 sql 语句 mysql 底层优化器会进行优化：范围右边索引列失效（c4右边已经没有索引列了）；
• 注意索引的顺序（c1,c2,c3,c4），所以c4右边不会出现失效的索引列，因此4个索引全部用上。

结论：

• 范围右边索引列失效，是有顺序的：c1,c2,c3,c4，如果c3有范围，则c4失效；如果c4有范围，则没有失效的索引列，从而会使用全部索引。

Case 3：order by 中的索引列

explain select * from test where c1='a1' and c2='a2' and c4='a4' order by c3;
1

结果如下：

分析：

• 利用最佳左前缀法则：中间兄弟不能断，因此用到了 c1 和 c2 索引（查找），key_len=66，ref=const,const，c3索引列用在排序过程中。

explain select * from test where c1='a1' and c2='a2' order by c3;
1

结果如下：

分析：

• 从explain的执行结果来看：key_len=66，ref=const,const，从而查找只用到 c1 和 c2 索引，c3 索引用于排序。

explain select * from test where c1='a1' and c2='a2' order by c4;
1

结果如下：

分析：

• 从explain的执行结果来看：key_len=66，ref=const,const，查询使用了 c1 和 c2 索引，由于用了 c4 进行排序，跳过了c3，出现了 Using filesort。

explain select * from test where c1='a1' and c5='a5' order by c2,c3;
1

结果如下：

分析：

• 查找只用到索引 c1，c2 和 c3 用于排序，无 Using filesort

explain select * from test where c1='a1' and c5='a5' order by c3,c2;
1

结果如下：

分析：

• 和上一 sql 语句中 explain 的执行结果一样，但是出现了 Using filesort，因为索引的创建顺序为 c1,c2,c3,c4，但是排序的时候c2和c3颠倒位置了。

explain select * from test where c1='a1' and c2='a2' order by c2,c3;

explain select * from test where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
1
2
3

结果如下：

分析：

• 第二句 sql 语句在查询时增加了 c5，但是 explain 的执行结果相同，因为 c5 并未创建索引。

explain select * from test where c1='a1' and c2='a2' order by c3,c2;
1

结果如下：

分析：

• 在 Extra 中并未出现 Using filesort，因为 c2 为常量，在排序中被优化，所以索引未颠倒，不会出现 Using filesort。

Case 4：group by 中的索引列

explain select * from test where c1='a1' and c4='a4' group by c2,c3;
1

结果如下：

分析：

• 只用到 c1 上的索引，因为 c4 中间间断了，根据最佳左前缀法则，所以key_len=33，ref=const，表示只用到一个索引。

explain select * from test where c1='a1' and c4='a4' group by c3,c2;
1

结果如下：

分析：

• 对比上一句 sql 语句，在 group by 时交换了 c2 和 c3 的位置，结果出现 Using temporary。原因是 c3 和 c2 在 group by 子句中的顺序和索引创建顺序相反。

总结

通过以上 Case 的分析，进行如下总结：

• 在等值查询时，更改索引列顺序，并不会影响 explain 的执行结果，因为 mysql 底层会进行优化。
• 在使用 order by 时，注意索引顺序、常量，以及可能会导致 Using filesort 的情况。
• group by 容易产生Using temporary。

十五、 ORDER BY 与 GROUP BY 的索引优化

1. ORDER BY 子句中的索引

在使用 order by 时，经常出现 Using filesort，因此对于此类 sql 语句需尽力优化，使其尽量使用 Using index。

数据准备

我们假设创建了如下表格：

• test 表（其中 id 为主键自增字段）：
  

我们对表的 c1、c2、c3 和 c4 字段创建复合索引：

 create index idx_c1234 on test(c1,c2,c3,c4);
1

ORDER BY 分析

Case 1

explain select * from test where c1>'a1' order by c1;
1

结果如下：

分析：

• 在 c1, c2, c3, c4 上创建了复合索引，在 c1 上使用范围并利用 c1 进行排序，因为复合索引创建时以 c1 为头，所以 Extra 中未出现 Using filesort。

explain select * from test where c1>'a1' order by c2;
1

结果如下：

分析：

• 在 c1 上使用范围并利用 c2 进行排序，我们在前面学到过，范围右边索引失效，因此虽然 c1 和 c2 是相邻的，但是因为 c1 使用了范围，因此 c2 索引失效，Extra 中出现了 Using filesort。
• 即使使用覆盖索引也不能解决该问题。

explain select * from test where c1 in ('a1','a2','a3') order by c2,c3;
1

结果如下：

分析：

• c1 in ('a1','a2','a3')使用了范围查询，这相当于 c1 使用了范围，因此 c2 索引失效，Extra 中出现了 Using filesort。

explain select * from test where c1='a1' and c2>'a2' order by c2;
1

结果如下：

分析：

• c1 使用了常量等值，c2 使用了范围，因此 c2 右边的索引失效，但是 c2 未失效，因此 Extra 中未出现 Using filesort。

Case 2

explain select * from test where c1>'a1' order by c1,c2;
1

结果如下：

分析：

• c1 使用了范围，因此 c1 右边的索引失效，但是 c1 未失效，因此在利用 c1、c2 进行排序时，Extra 中未出现 Using filesort。

explain select * from test where c1>'a1' order by c2,c1;
1

结果如下：

分析：

• c1 使用了范围，因此 c1 右边的索引失效，同时在利用 c2、c1 进行排序时，排序的索引列与索引创建的顺序相反，因此 Extra 中出现了 Using filesort。

explain select * from test where c2>'a2' order by c1,c2;
1

结果如下：

分析：

• c2 使用了范围，利用 c1、c2 进行排序时，Extra 中竟然出现了 Using filesort。猜想出现这种情况的原因是因为 WHERE 子句中未按照索引创建的顺序进行条件查询。
• 解决方法是使用覆盖索引explain select c2 from test where c2>'a2' order by c1,c2;
  

explain select * from test where c1='a1' and c2>'a2' order by c2,c5;
1

分析：

• 排序中出现了非索引列也会使 Extra 出现 Using filesort。

Case 3

explain select * from test order by c2;

explain select c1 from test order by c2;

explain select c1 from test where c2>'a2' order by c2;
1
2
3
4
5

结果如下：

分析：

• 不管是否使用覆盖索引、是否使用查询条件，因为 order 子句中的顺序与索引创建顺序不同，因此 Extra 均会出现 Using filesort。

explain select c1 from test order by c1 asc,c2 desc;
1

结果如下：

分析：

• 虽然排序的字段列与索引顺序一样，但是 order by 默认升序，这里 c2 desc 变成了降序，导致与索引的排序方式不一致，索引失效，从而 Extra 出现了 Using filesort。

explain select c3 from test order by c1 desc,c2 desc;
1

结果如下：

分析：

• 如果 order by 子句中的索引列顺序与创建顺序相同，且所有的列均为降序排序，那么这种情况下索引的排序方式一致，因此索引仍然是有效的，Extra 不会出现 Using filesort。

Case 4

explain select * from test order by c1,c2;

explain select c3 from test order by c1,c2;
1
2
3

结果如下：

分析：

• 仅使用索引创建字段并按顺序来进行排序但是 SELECT 了非索引字段时（未使用 WHERE 子句），Extra 会出现 Using filesort；
• 使用索引创建字段并按顺序来进行排序同时使用覆盖索引时（未使用 WHERE 子句），Extra 不会出现 Using filesort。

filesort 排序算法

具体来说，filesort 有两种排序算法，分别是双路排序和单路排序。

双路排序

在 MySQL4.1 之前使用双路排序，就是两次磁盘扫描，得到最终数据。从磁盘中读取行指针（rowid）和 order by 排序列，对它们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从磁盘中读取对应的数据输出。即从磁盘读取排序字段，在 buffer 进行排序，再从磁盘取其他字段。

双路排序步骤如下：

• 读取 固定长度的排序列 + rowid（行指针）组成元组，放入 sort buffer；
• 如果 sort buffer 满， 根据排序列执行一次 quicksort，将其写入临时文件；
• 重复上述步骤直到文件读取结束；
• 对临时文件执行归并排序；
• 根据排序好的 rowid 再次从磁盘读取查询需要的行。

如果使用双路排序，取一批数据要对磁盘进行两次扫描，众所周知，I/O 操作是很耗时的，因此在 MySQL4.1 以后，出现了改进的算法：单路排序。

单路排序

从磁盘中查询所需的列，按照 order by 列在 buffer 中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机 I/O 变成了顺序 I/O，但是会使用更多的空间，因为它把每一行都保存在内存中了。

单路排序步骤如下：

• 读取 <固定长度的排序列, 需要返回的列> 组成元组，放入 sort buffer；
• 如果 sort buffer 满， 根据排序列执行一次 quicksort，将其写入临时文件；
• 重复上述步骤直到文件结束；
• 对临时文件执行归并排序；
• 从排好序的临时文件中读取需要返回的列即可。

两种排序算法的区别：

• 双路算法会读取两次表， 且第二次是随机读取的；
• 单路算法的元组比双路算法要长，导致它需要多次向临时文件写入内容，增加 I/O 操作，当需要返回的列的总长度很长时尤其明显。

MySQL根据 max_length_for_sort_data 变量来确定使用哪种算法，默认值是1024字节，如果需要返回的列的总长度大于 max_length_for_sort_data，使用第一种算法，否则使用第二种算法。

解决方式有：

• 增加 sort_buffer_size 参数的设置；
• 增大 max_length_for_sort_data 参数的设置。

提升 order by 速度的方式有：

• 在使用 order by 时，不要用 select *，只查询所需的字段。
  • 因为当查询字段过多时，会导致 sort_buffer 不够，从而使用多路排序或进行多次 I/O 操作。
• 尝试提高 sort_buffer_size。
• 尝试提高 max_length_for_sort_data。

总结

• MySQL 支持两种方式的排序 filesort 和 index ，Using index 是指 MySQL 扫描索引本身完成排序。index 效率高，filesort 效率低。
• order by 满足两种情况会使用 Using index：
  • order by 语句使用索引最左前列且使用覆盖索引（一旦使用覆盖索引不管有没有 where 子句一定有 Using index）；
  • 使用 where 子句与 order by 子句条件列组合满足索引最左前列（此时是 Using index condition）。
• 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最佳左前缀法则。
• 如果 order by 的条件不在索引列上，就会产生 Using filesort。
• Using index 和 Using index condition 的区别：
  • 当使用覆盖索引时，Extra 就会出现 Using index，当完成 SELECT 操作时需要读取除索引外的其他列时，即需要从非索引列获取数据时，就会出现 Using index condition。

2. GROUP BY 子句中的索引

group by 与 order by 很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候，也要对 sort_buffer_size 和 max_length_for_sort_data 参数进行调整。注意 where 高于 having，能写在 where 中的限定条件就不要去 having 限定了。

在使用 group by 时，经常出现 Using temporary，因此对于此类 sql 语句需尽力优化，使其尽量使用 Using index。

数据准备

我们假设创建了如下表格：

• test 表（其中 id 为主键自增字段）：
  

我们对表的 c1、c2、c3 和 c4 字段创建复合索引：

 create index idx_c1234 on test(c1,c2,c3,c4);
1

GROUP BY 分析

Case 1

explain select * from test where c1>'a1' group by c1;
1

结果如下：

分析：

• 在 c1, c2, c3, c4 上创建了复合索引，在 c1 上使用范围并利用 c1 进行分组，因为复合索引创建时以 c1 为头，所以 Extra 中未出现 Using temporary。

explain select * from test where c1>'a1' group by c2;
1

结果如下：

分析：

• 在 c1 上使用范围并利用 c2 进行分组，我们在前面学到过，范围右边索引失效，因此虽然 c1 和 c2 是相邻的，但是因为 c1 使用了范围，因此 c2 索引失效，Extra 中出现了 Using temporary。
• 即使使用覆盖索引也不能解决该问题。

explain select * from test where c1='a1' and c2>'a2' group by c2;
1

结果如下：

分析：

• c1 使用了常量等值，c2 使用了范围，因此 c2 右边的索引失效，但是 c2 未失效，因此 Extra 中未出现 Using temporary。

Case 2

explain select * from test where c1>'a1' group by c1,c2;
1

结果如下：

分析：

• c1 使用了范围，因此 c1 右边的索引失效，但是 c1 未失效，因此在利用 c1、c2 进行分组时，Extra 中未出现 Using temporary。

explain select * from test where c1>'a1' group by c2,c1;
1

结果如下：

分析：

• c1 使用了范围，因此 c1 右边的索引失效，同时在利用 c2、c1 进行分组时，分组的索引列与索引创建的顺序相反，因此 Extra 中出现了 Using temporary。

explain select * from test where c2>'a2' group by c1,c2;
1

结果如下：

分析：

• 与 order by 不同的是，c2 使用了范围，利用 c1、c2 进行分组时，Extra 中没有出现 Using temporary。

explain select * from test where c1='a1' and c2>'a2' group by c2,c5;
1

分析：

• 分组时出现了非索引列也会使 Extra 出现 Using temporary。

Case 3

explain select * from test group by c2;

explain select c1 from test group by c2;

explain select c1 from test where c2>'a2' group by c2;
1
2
3
4
5

结果如下：

分析：

• 不管是否使用覆盖索引、是否使用查询条件，因为 group 子句中的顺序与索引创建顺序不同，因此 Extra 均会出现 Using temporary。

Case 4

explain select * from test group by c1,c2;

explain select c3 from test group by c1,c2;
1
2
3

结果如下：

分析：

• 仅使用索引创建字段并按顺序来进行分组但是 SELECT 了非索引字段时（未使用 WHERE 子句），Extra 会出现 NULL；
• 使用索引创建字段并按顺序来进行排序同时使用覆盖索引时（未使用 WHERE 子句），Extra 会出现 Using index。

十六、慢查询日志

慢查询日志是 MySQL 提供的一种日志记录，它记录 MySQL 中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的sql语句，该 sql 语句会被记录到慢查询日志中。慢查询日志主要与 explain 进行联合分析。

1. 如何开启慢查询日志

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要，一般不建议开启该参数，因为开启慢查询日志或多或少会带来一定的性能影响。

① 首先查看慢查询日志是否开启

show variables like '%slow_query_log%';
1

结果如下：

② 使用命令开启慢查询日志
当 slow_query_log 的 Value 为 OFF 时，代表慢查询日志处于关闭状态，可用以下命令来开启：

set global slow_query_log=1;
1

注意：

• 使用该命令开启慢查询只对当前数据库生效，MySQL 重启失效，如果要配置长期有效，请在 my.cnf 文件中进行配置。

2. 慢查询的相关参数

① 查看慢查询的阈值时间
该值由 long_query_time 控制。默认情况下为 10 秒。

show variables like '%long_query_time%';
1

注意：

• 假如 sql 语句的运行时间正好等于 long_query_time 时，sql 并不会被记录下来，因此判断慢查询是 sql 的执行时间大于 long_query_time，而不是大于等于。

② 设置 long_query_time 的值

set global long_query_time=8;
1

当设置 long_query_time 值后，查看其值并没有变化，解决方式：

• 重新打开一个新的窗口；
• 使用如下命令查询：show global variables like '%long_query_time%';
  

③ 查看慢查询 sql 的数目

-- 在MySQL中执行select sleep(N)可以让此语句运行N秒钟
SELECT sleep(9);

SELECT sleep(9);

show global status like '%Slow_queries%';
1
2
3
4
5
6

查看慢查询日志文件：

从文件中可看到两条 select sleep(9) 语句。

3. 使用 mysqldumpslow 分析慢查询日志文件

因为直接分析日志文件是个体力活，因此 mysql 为我们提供了相关工具 mysqldumpslow 来对慢查询日志文件进行分析。具体使用方式可用 mysqldumpslow --help 命令查看具体参数。

十七、函数与存储过程

1. 数据准备

我们假设创建了如下表格：

• tb_dept_bigdata 部门表（其中 id 为主键自增字段）：

• tb_emp_bigdata 员工表（其中 id 为主键自增字段）：

由于在创建函数时，可能会报错：This function has none of DETERMINISTIC.....。因此我们需开启函数创建的信任功能，这一功能由 log_bin_trust_function_creators 参数来开启：

show variables like '%log_bin_trust_function_creators%';
1

Value 值为 OFF，说明未开启：

可通过 set global log_bin_trust_function_creators=1的形式开启该功能，也可通过在 my.cnf 中永久配置的方式开启该功能，在 [mysqld] 下配置 log_bin_trust_function_creators=1。

2. 函数

基本语法

MySQL 中函数编写的基本语法如下：

create function <函数名称>([参数1 参数1类型],[参数2 参数2类型],...)
returns <返回值类型>
begin
	<语句;>
return 返回值;
end;
1
2
3
4
5
6

注意：

• MySQL 中默认以分号作为命令结束标志，而在创建函数时我们要在 begin 和 end 之间写入多个以分号为结尾的语句，那么我们怎么告诉 MySQL 我们还没有结束函数的创建呢？我们可以使用 delimiter 关键字来修改命令的结束标志，例如 delimiter $$将结束标志改成 “$$”，但是在创建完函数后记得将命令结束标志改回分号。

创建函数

我们接下来创建两个函数：随机生成字符串的函数和随机生成编号的函数，以满足后续向表中插入不同的数据。

①创建随机生成字符串的函数

delimiter $$
drop function if exists rand_string;  -- 如果函数存在就先删除
create function rand_string(n int)
returns varchar(255)
begin
	declare chars_str varchar(52) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	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 $$
1
2
3
4
5
6
7
8
9
10
11
12
13
14

注意：

• declare 语句的作用是在复合语句中声明变量，包括变量的名称、类型和默认值等；
• set 语句可以为变量赋值；
• rand() 函数包括0，不包括1；
• 这个函数使用了 while 循环，每循环一次会产生一个随机字符，i 也会累加1，因此该函数的作用就是随机产生 n 个字符组成一个字符串；
• 最后在末尾加上 $$ 命令结束标志。

②创建随机生成编号的函数

delimiter $$
drop function if exists rand_num;
create function rand_num()
returns int(5)
begin
	declare i int default 0;
	set i=floor(100+rand()*100);
	return i;
end $$
delimiter ;
1
2
3
4
5
6
7
8
9
10

注意：

• 该函数会随机生成一个[100, 199] 之间的整数；
• 创建完函数后记得将命令结束标志改成分号。

查看函数是否创建成功

show function status;
1

3. 存储过程

基本语法

MySQL 中存储过程编写的基本语法如下：

create procedure <存储过程名称>(<IN 或 OUT 或 INOUT> 参数1 参数1类型, <IN 或 OUT 或 INOUT> 参数2 参数2类型, ...)
begin
	<语句>;  -- 过程体
end
1
2
3
4

注意：

• 存储过程的名称，默认在当前数据库中创建。如果需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称。需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。
• 存储过程没有像函数那样将返回值直接 return，而是用 OUT 参数表露出来。
• MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。需要注意的是，参数的取名不要与数据表的列名相同。
• 若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。

创建存储过程

我们接下来创建两个存储过程，分别用于向部门表和员工表中批量插入数据。

①创建往 tb_dept_bigdata 表中插入数据的存储过程

delimiter $$
drop procedure if exists insert_dept;
create procedure insert_dept(in max_num int(10))
begin
	declare i int default 0;
	set autocommit=0;
	repeat
		set i=i+1;
		insert into tb_dept_bigdata (deptno,dname,loc) values(rand_num(),rand_string(10),rand_string(8));
		until i=max_num
	end repeat;
	commit;
end $$
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

注意：

• 创建存储过程同样需要先更改默认的命令结束标志；
• set autocommit=0;将禁止自动提交，只有当出现 commit;时才会提交更改，保证了原子性（在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行）；
• 这里用了 repeat 来重复往表格里插入数据，直到 i 值达到了最大值 max_num，和循环过程类似；
• 该存储过程将实现向部门表中插入 max_num 条数据。

②创建往 tb_emp_bigdata 表中插入数据的存储过程

delimiter $$
drop procedure if exists insert_emp;
create procedure insert_emp(in start int(10),in max_num int(10))
begin
	declare i int default 0;
	set autocommit=0;
	repeat
		set i=i+1;
		insert into tb_emp_bigdata (empno,empname,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'developer',0001,curdate(),2000,400,rand_num());
		until i=max_num
	end repeat;
	commit;
end $$
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

注意：

• 该存储过程实现了向员工表中插入 max_num 条数据，其中员工编号从参数 start+1 开始，逐渐递增到 start+max_num。

查看存储过程是否创建成功

show procedure status;
1

4. 批量插入数据的具体执行过程

1. 执行创建随机生成字符串的函数的 sql 语句。

2. 执行创建随机生成编号的函数的 sql 语句。

3. 查看函数是否创建成功。

4. 执行创建往 tb_dept_bigdata 表中插入数据的存储过程的 sql 语句。

5. 执行创建往 tb_emp_bigdata 表中插入数据的存储过程的 sql 语句。

6. 查看存储过程是否创建成功。

7. 执行存储过程，插入数据：
   ①首先执行 insert_dept 存储过程

   call insert_dept(100);
   
   select count(*) from tb_dept_bigdata;
   1
   2
   3

   
   说明：deptno 的范围为 [100, 200)，因为 deptno 的值使用了 rand_num() 函数。

   ②然后执行 insert_emp 存储过程

   call insert_emp(100,300);
   
   select count(*) from tb_emp_bigdata;
   1
   2
   3

   
   注：对于部门表的 deptno 和员工表中 deptno 的数据都使用了 rand_num() 函数进行赋值，确保两边的值能对应。

8. 删除函数与存储过程

   -- 删除函数
   drop function rand_num;
   drop function rand_string;
   
   -- 删除存储过程
   drop procedure insert_dept;
   drop procedure insert_emp;
   1
   2
   3
   4
   5
   6
   7

5. 小表驱动大表

在学习了数据批量插入后，我们接下来介绍小表驱动大表。首先给出结论：在查询的优化中，永远小表驱动大表，即小的数据集驱动大的数据集。

为什么是小表驱动大表

类似循环嵌套：

for(int i=5;.......)
{
     for(int j=1000;......)
     {}
}
1
2
3
4
5

如果小的循环在外层，对于数据库连接来说就只连接5次，进行5000次操作，如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。

数据准备

在 tb_dept_bigdata 表中插入100条数据，在 tb_emp_bigdata 表中插入5000条数据：

call insert_dept(100);

call insert_emp(100,5000);

select count(*) as count_dept from tb_dept_bigdata;

select count(*) as count_emp from tb_emp_bigdata;
1
2
3
4
5
6
7

注意：

• 100个部门，5000个员工。
• tb_dept_bigdata（小表），tb_emp_bigdata（大表）。

案例演示

①当 B 表的数据集小于 A 表数据集时，用 in 优于 exists：

select * from tb_emp_bigdata A where A.deptno in (select B.deptno from tb_dept_bigdata B);
1

B 表为 tb_dept_bigdata：100条数据，A 表 tb_emp_bigdata：5000条数据。

用 in 的查询时间为：

将上面 sql 转换成 exists：

select * from tb_emp_bigdata A where exists(select B.deptno from tb_dept_bigdata B where B.deptno=A.deptno);
1

用exists的查询时间：

经对比可看到，在B表数据集小于A表的时候，用 in 要优于exists，当前的数据集并不大，所以查询时间相差并不多。

②当 A 表的数据集小于 B 表的数据集时，用 exists 优于 in：

select * from tb_dept_bigdata A where A.deptno in (select B.deptno from tb_emp_bigdata B);
1

用 in 的查询时间为：

将上面 sql 转换成 exists：

select * from tb_dept_bigdata A where exists(select 1 from tb_emp_bigdata B where B.deptno=A.deptno);
1

用 exists 的查询时间：

由于数据量并不是很大，因此对比并不是那么强烈。

总结

select * from A where id in (select id from B)
等价于:
for select id from B
for select * from A where A.id = B.id
1
2
3
4

当 B 表的数据集必须小于 A 表的数据集时，用 in 优于 exists。

select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select * from A
for select * from B where B.id = A.id
1
2
3
4

对于 exists：

select ... from table where exists(subquery);
1

可以理解为：将主查询的数据放入子查询中做条件验证，根据验证结果（True 或 False）来决定主查询的数据是否得以保留。

十八、Show Profile 和全局查询日志

1. Show Profile 分析步骤

Show Profile 是 MySQL 提供的可以用来分析当前会话中 sql 语句执行的资源消耗情况的工具，可用于 sql 调优的测量。默认情况下处于关闭状态，开启后默认保存最近15次的运行结果。Show Profile 的分析步骤如下：

• 开启 Show Profile 功能，默认该功能是关闭的，使用前需开启。开启后只存活于当前会话，也就说每次使用前都需要开启。

  show variables like 'profiling' ;
  1

  

  set profiling=on ;
  
  show variables like 'profiling' ;
  1
  2
  3

  

• 向十七章中创建的 tb_emp_bigdata 表中插入 50w 条数据。然后执行如下查询语句：

  select * from tb_emp_bigdata group by id%10 limit 150000;
  select * from tb_emp_bigdata group by id%20 order by 5;
  1
  2

• 通过 show profiles 查看结果：
  

• 使用 show profile 对 sql 语句进行诊断：

  -- cpu、block io 为查询参数
  -- Query_ID 为 show profiles 列表中的 Query_ID
  show profile cpu,block io for query <Query_ID>;
  1
  2
  3

  比如执行show profile cpu,block io for query 15;，可得到：
  

因此，通过 show profiles 查看 sql 语句的耗时时间，然后通过 show profile 命令对耗时时间长的 sql 语句进行诊断。

2. Show Profile 常用查询参数

Show Profile 语句中的常用查询参数有：

3. 日常开发需注意的结论

如果在 show profile 诊断结果中出现了以上4条结果中的任何一条，则 sql 语句需要优化。

4. 全局查询日志

全局查询日志用于保存所有的 sql 执行记录，该功能主要用于测试环境，在生产环境中永远不要开启该功能。

开启全局查询日志的方法有两种：

• 通过 my.cnf 配置开启该功能：
  
  对 my.cnf 文件配置后，需重启 mysql。可以通过命令查看全局查询日志是否开启成功：

  show variables like '%general%';
  1

  
  查看 log_globalquery.log 文件中的内容，该 log 文件记录执行过的 sql 语句：
  
• 通过命令行的命令开启该功能：

  set global general_log=1;
  
  set global log_output='TABLE';
  1
  2
  3

  通过以上配置，执行过的 sql 语句将会记录到 mysql 库中 general_log 表里。
  
  注意，通过命令行的命令方式开启该功能，重启 mysql 后失效。

十九、表锁和行锁

我们在第十一章介绍 MySQL 存储引擎时，曾经对两大常用的存储引擎 MyISAM 和 InnoDB 进行了如下表的比较：

在上表中有一个比较项叫行表锁，我们当时简单地列出了 MyISAM 和 InnoDB 所支持的锁的类型，这一章我们将详细探讨表锁和行锁。

1. 锁的分类

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算机资源（如 CPU、RAM、I/O 等）的争用外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

从对数据操作的类型来分，锁可以分为读锁（共享锁）和写锁（排它锁）；从对数据操作的粒度来分，锁可以分为表锁和行锁。

2. 表锁

特点：偏向 MyISAM 存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度低。

数据准备

• 我们创建如下所示的表：

  • mylock 表（其中 id 为主键自增字段）
    

• 增加表锁命令的基本语法如下：

    lock table <表名1> <read 或 write>, <表名2> <read 或 write>;
  1

• 查看表是否被加锁：

  show open tables;
  1

  
  如果 In_use 显示不为0，则表示表被加锁。

• 释放表锁命令：

  unlock tables；
  1

  不需要加表名。

表锁（read）案例分析

• 在 mylock 表上加读锁，并假设当前会话命名为 A，在 A 会话中查询 mylock 中的数据：

  lock table mylock read;
  
  select * from mylock;
  1
  2
  3

  结果如下：
  
  数据查询正常，没有任何问题。

• 再开一个会话，命名为 B，查询 mylock 中的数据：
  
  数据查询依旧正常，没有任何问题。

• 在 A 会话中进行更新操作：

  update mylock set name='a1' where id=1;
  1

  
  提示 mylock 表被加锁，不能进行更新操作。原因是 mylock 正被读锁锁住，未解锁不能进行更新操作。

• 在 B 会话中读其他表：

  select * from tb_emp;
  1

  

  A 会话 mylock 表的读锁，并不影响 B 会话对 mylock 表的读操作和其他表的读写操作。

• 在 A 会话中读其他表：

  select * from tb_emp;
  1

  
  由于 A 会话对 mylock 表加了读锁，在未解锁前，不能操作其他表。

• 在 B 会话中修改 mylock 表中的内容：

   update mylock set name='a1' where id=1;
  1

  
  出现了阻塞情况。原因是由于 A 会话对 mylock 表加锁，在锁未释放时，其他会话是不能对 mylock 表进行更新操作的。

• 在 A 会话中对 mylock 表进行解锁操作，注意观察 B 会话中的变化：

  unlock tables;
  1

  
  
  在 A 会话中对 mylock 表解锁后，B 会话更新操作成功，可看到 B 会话中的更新操作等待了4分钟。

表锁（write）案例分析

• 在 A 会话中对 mylock 表加写锁：

  lock table mylock write;
  
  show OPEN TABLES where In_use >0;
  1
  2
  3

  

• 在 A 会话中对 mylock 表进行读写操作：

  update mylock set name='a1' where id=1;
  
  select * from mylock;
  1
  2
  3

  
  由于是 A 会话对 mylock 表加的写锁，所以读写操作都执行正常。

• 在 A 会话中对其他表进行操作：

  select * from tb_emp;
  
  insert into tb_emp (name , age, gender, email) values ( 'lock ',1, 'male', 'lock@qq.com');
  1
  2
  3

  
  在 A 会话中对其他表进行读写操作都失败，因为 A 会话中 mylock 表的写锁并未被释放。

• 在 B 会话中对 mylock 表进行读操作：

  select * from mylock;
  1

  
  由于 mylock 表已经加写锁，而写锁为排它锁，因此在 B 会话中对 mylock 表进行读操作阻塞。由于 B 会话中对 mylock 的读操作都阻塞，所以其他操作也是阻塞的。

• 在 B 会话中对其他表进行读写操作：

  select * from tb_emp;
  
  insert into tb_emp (name , age, gender, email) values ( 'lock ',1, 'male', 'lock@qq.com');
  1
  2
  3

  
  A 会话 mylock 表的写锁，并不影响 B 会话对其他表的读写操作。

表锁定分析

1. 使用如下命令查看是否有表被锁定：

   show open tables where In_use>0;
   1

2. 使用如下命令分析表锁：

   show status like 'table%';
   1

   
   主要注意两个变量的值：

   • Table_locks_immediate：产生表级锁定的次数，表示可立即获取锁的查询次数，每立即获取锁一次该值加1。
   • Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁该值加1），此值高则说明存在较严重的表级锁争用情况。

总结

注意数据库引擎为 MyISAM。

1. 对 MyISAM 表加读锁，不会阻塞其他进程对同一表的读操作和其他表的读写操作，但是会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进程的写操作。
2. 在加读锁并未释放锁时，该进程不能对同一表进行写操作，并且也不能对其他表进行操作。
3. 对 MyISAM 表加写锁，会阻塞其他进程对同一表的读和写操作，但是不会阻塞其他进程对其他表的读写操作，只有当写锁释放后，才会执行其他进程对同一表的写操作。
4. 在加写锁并未释放锁时，该进程不能对其他表进行操作。

简而言之，读锁会阻塞写，但是不会阻塞读，而写锁会把读和写都阻塞。此外，MyISAM 的读写锁调度是写优先，因此 MyISAM 不适合做以写为主的表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成长时间阻塞。

3. 行锁

行锁偏向 InnoDB 存储引擎，开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率低，但并发度高。

数据准备

• 我们创建如下所示的表：
  • test_innodb_lock 表
    
• 对 a 和 b 分别创建单值索引：

  create index idx_lock_a on test_innodb_lock(a);
  create index idx_lock_b on test_innodb_lock(b);
  1
  2

行锁案例

• 打开 A、B 两个会话，并在 A 会话中关闭数据库的自动提交：

  set autocommit=0;
  1

• 在 A 会话中做更新操作：

  update test_innodb_lock set b='a1' where a=1;
  
  select * from test_innodb_lock;
  1
  2
  3

  
  从查询结果可知，在 A 会话中更新数据成功。

• 在 B 会话中做查询操作：

  select * from test_innodb_lock;
  1

  
  B 会话中并没有读取到 A 会话中更新后的值。（读己知所写：自己更改的数据自己知道，但是如果未提交，其他人是不知道的。）

• 在 A 会话中执行 commit 命令，然后在 B 会话中再次查询：
  

• 在 A 会话中做更新操作，然后在 B 会话中也做更新操作：

  -- A 会话
  update test_innodb_lock set b='a2' where a=1;
  
  -- B 会话
  update test_innodb_lock set b='a3' where a=1;
  1
  2
  3
  4
  5

  
  
  可以看出此时 B 会话发生了阻塞。

• 在 A 会话中 commit 操作，可看到B会话中发生了更新操作：
  
  因为我们操作的是同一行数据，而由于 InnoDB 为行锁，在 A 会话未提交时，B 会话只有阻塞等待。如果操作不同行，则不会出现阻塞情况。

索引失效导致行锁升级为表锁

• 首先将表中的数据更新为 b=‘1000’, ‘2000’, ‘3000’, ‘4000’, ‘5000’

  update test_innodb_lock set b='1000' where a=1;
  update test_innodb_lock set b='2000' where a=2;
  update test_innodb_lock set b='3000' where a=3;
  update test_innodb_lock set b='4000' where a=4;
  update test_innodb_lock set b='5000' where a=5;
  1
  2
  3
  4
  5

  
• 在 A、B 会话中执行如下更新语句：

  -- A 会话
  update test_innodb_lock set a=110 where b=1000;
  
  -- B 会话
  update test_innodb_lock set b='5001' where a=5;
  1
  2
  3
  4
  5

  
  
  在 A 会话中操作的第一行数据，但是 where 中使用了 b=1000，发生了自动转换导致索引失效，从而使锁的级别从行锁升级为表锁，因此 B 会话中操作第五行数据出现阻塞的情况。

间隙锁的危害

• 间隙锁的定义：
  当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但不存在的记录，叫作“间隙（GAP）”。 InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

• 间隙锁的危害：
  因为在 Query 执行过程中通过范围查找的话，会锁定整个范围内的所有索引键值，即使这个索引不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定值范围内的任何数据。在某些场景下这个可能会对性能造成很大的危害。

• 间隙锁演示
  我们需要对 test_innodb_lock 中的数据进行修改，修改后的数据如下：
  
  在 A 会话中执行如下语句：

  update test_innodb_lock set b='test' where a>1 and a<6;
  1

  

  在 B 会话中执行如下语句：

  insert into test_innodb_lock values (2,'2000');
  1

  
  只有在 A 会话 commit 后，B 会话才能进行插入操作：

锁定某一行

我们通常使用 for update来锁定某一行。

• 在 A 会话中执行如下语句：

  select * from test_innodb_lock where a=7 for update;
  1

  
  此时就锁定了 a=7 这一行。

• 在B会话中对该行进行更新操作：

  update test_innodb_lock set b='xxx' where a=7;
  1

  

• 只有在 A 会话中进行了 commit 后，B 会话的更新操作才能执行：
  

行锁分析

使用如下命令：
sql show status like 'innodb_row_lock%';

各个状态量说明：

这个五个状态量中，比较重要的是：Innodb_row_lock_time、Innodb_row_lock_time_avg 和 Innodb_row_lock_waits。尤其是等待次数很高，而且每次等待时长不小时，就需要分析优化了。可以看出，Innodb_row_lock_waits * Innodb_row_lock_time_avg = Innodb_row_lock_time 。

优化建议

1. 尽可能让所有数据都通过索引来完成，避免无索引行升级为表锁。
2. 合理设计索引，尽量缩小锁的范围。
3. 尽可能使用较少的检索条件，避免间隙锁。
4. 尽量控制事务大小，减少锁定资源量和时间长度。
5. 尽可能降低事务隔离级别。

页锁

还有一种锁叫页锁，它的开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度介于表锁和行锁之间，并发度一般（了解即可）。

总结

1. InnoDB 存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些（多个锁，一个锁），但是在整体并发处理能力方面要远远优于 MyISAM 的表级锁定。当系统处于高并发量的时候，InnoDB 的整体性能和 MyISAM 相比就会有比较明显的优势了。
2. InnoDB 的行锁定同样有其脆弱的一面（间隙锁危害），当使用不当时可能会让 InnoDB 的整体性能表现不仅不能比 MyISAM 高，甚至可能更差。

二十、主从复制

本章主要讲解 MySQL 主从复制的操作步骤。主机使用 Windows 环境，从机使用 Linux 环境。注意，MySQL 的版本最好一致。

1. 主从复制原理

slave（从机）会从 master（主机）读取 binlog 来进行数据同步。主要有以下三个步骤：

• master 将改变记录到二进制日志（binary log），这些记录过程叫做二进制日志事件（binary log events）；
• slave 将 master 的 binary log events 拷贝到中继日志（relay log）；
• slave 重做中继日志中的事件，将改变应用到自己的数据库中。

MySQL 的复制是异步且串行化的。

2. 主从复制规则

主从复制的规则如下：

• 每个 slave 只能有一个 master。（一对一）
• 每个 slave 只能有一个唯一的服务器ID。
• 每个master可以有多个slave。（一对多）

在主从复制过程中，最大的问题就是延时。

3. 一主一从的常见配置

• 要求：
  MySQL 版本最好一致且后台以服务运行。并且保证主机与从机互相 ping 通。主从配置都在 [mysqld] 结点下，都是小写。

• 主机修改 my.ini 配置文件

  • server-id=1，主机服务器 id。（必须）
  • 必须启用二进制文件：log-bin = "E:\devSoft\mysql-5.7.22-winx64\data\mysql-bin"。配置该项后，重新启动 mysql 服务，可看到如下内容：
    

    • 启用错误日志（可选）：

      log_error = "E:\devSoft\mysql-5.7.22-winx64\data\log\errorlog\log_error.log"
      1

    • 根目录、数据目录（可选）:

      # mysql安装根目录
      basedir = "E:\devSoft\mysql-5.7.22-winx64"
      
      # mysql数据文件所在位置
      datadir = "E:\devSoft\mysql-5.7.22-winx64\data"
      1
      2
      3
      4
      5

    • 临时目录（可选）：

      tmpdir  = "E:\devSoft\mysql-5.7.22-winx64\"
      1

    • read-only=0，表示主机读写都可以。

    • 可以设置不需要复制的数据库（可选）：

      binlog-ignore-db = mysql
      1

    • 可以设置需要复制的数据库（可选）：

      binlog-do-db=databasename
      1

• 从机修改 my.cnf 配置文件

  • 从服务器ID。（必须）
  • 启用二进制日志。（可选）

• 主机与从机都关闭防火墙，其实也可配置 ip 规则，但关闭防火墙更快速。

• 在 Windows 主机上建立账户并授权给 slave

  • 首先在主机上创建账户：

    # 字符%表示任何客户端都可以连接
    grant all privileges on *.* to slaveaccount（用户名）@"%(或者指定ip)" identified by '你想设置的密码' with grant option;
    1
    2

  • 然后刷新权限表：

    flush privileges;
    1

  • 然后授权给 slave：

    GRANT REPLICATION SLAVE ON *.* TO 'slaveaccount（上面创建的用户名）'@'从机数据库ip' IDENTIFIED BY '你想设置的密码';
    1

  • 再次刷新权限表。

• 查询 master 的状态：

  show master status;
  1

  
  File 和 Position 这两个字段非常重要，File 告诉从机需要从哪个文件进行复制，Position 告诉从机从文件的哪个位置开始复制，在从机上配置时需用到。执行完此操作后，尽量不要在操作主服务器 MySQL，防止主服务器状态变化（ File 和 Position 状态变化）。

• 在 Linux 从机上配置需要的主机

  CHANGE MASTER TO MASTER_HOST='主机IP',MASTER_USER='salveaccount',MASTER_PASSWORD='主机授权的密码',MASTER_LOG_FILE='File名字',MASTER_LOG_POS=Position数字;
  1

• 启动从服务器复制功能

  start slave；
  1

  启动复制功能后，需要查看主从复制配置是否成功：
  
  注意，只有当 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes 的时候，主从复制配置才成功。

• 进行测试

  • 首先在主机上建立数据库并插入数据：
    
    
  • 在从机中查看是否有相应数据库
    
    
    通过在从机上查看相应数据，可知主从复制配置成功。

• 停止从服务复制功能：

  stop slave；
  1

4. 总结

1. 主从复制的配置，大部分都在主机上，注意查看相关步骤。
2. 这里将主从机的防火墙都关闭是为了更好的演示，实际生产环境中一般不会出现 windows 主机和 linux 从机这种情况，因此不应该关闭防火墙，而是根据具体情况配置防火墙规则。