SQL必知必会【笔记】

1.SQL概述

• DDL，英文叫做 Data Definition Language，数据定义语言
  • 它用来定义我们的数据库对象，包括数据库、数据表和列。通过使用 DDL，我们可以创建，删除和修改数据库和表结构。
• DML，英文叫做 Data Manipulation Language，数据操作语言，
  • 我们用它操作和数据库相关的记录，比如增加、删除、修改数据表中的记录。
• DCL，英文叫做 Data Control Language，数据控制语言
  • 我们用它来定义访问权限和安全级别。
• DQL，英文叫做 Data Query Language，数据查询语言
  • 我们用它查询想要的记录，它是 SQL 语言的重中之重。在实际的业务中，我们绝大多数情况下都是在和查询打交道，因此学会编写正确且高效的查询语句，是学习的重点。
• DBMS（数据库管理系统）则会按照指定的 SQL 帮我们提取想要的结果
  • SQL 是我们与 DBMS 交流的语言

tips: SQL 大小写的问题

1、表名、表别名、字段名、字段别名等都小写；

2、SQL 保留字（关键字）、函数名、绑定变量等都大写。

1.2.DBMS

• DBMS 的英文全称是 DataBase Management System，数据库管理系统
  • 实际上它可以对多个数据库进行管理，所以你可以理解为 DBMS = 多个数据库（DB） + 管理程序。
• DB 的英文是 DataBase，也就是数据库。
  • 数据库是存储数据的集合，你可以把它理解为多个数据表。
• DBS 的英文是 DataBase System，数据库系统。
  • 它是更大的概念，包括了数据库、数据库管理系统以及数据库管理人员 DBA。

1.3.NoSQL

泛指非关系型数据库 包括：键值型数据库、文档型数据库、搜索引擎和列存储等，除此以外还包括图形数据库。

• 键值型数据库
  • 通过 Key-Value 键值的方式来存储数据，其中 Key 和 Value 可以是简单的对象，也可以是复杂的对象。
  • Key 作为唯一的标识符，优点是查找速度快
  • 缺点也很明显：它无法像关系型数据库一样自由使用条件过滤（比如 WHERE），如果你不知道去哪里找数据，就要遍历所有的键，这就会消耗大量的计算
  • 键值型数据库典型的使用场景是作为内容缓存。Redis 是最流行的键值型数据库
• 文档型数据库
  • 用来管理文档，在数据库中文档作为处理信息的基本单位，一个文档就相当于一条记录，MongoDB 是最流行的文档型数据库。
• 搜索引擎
  • 常见的全文搜索引擎有 Elasticsearch、Splunk 和 Solr。
  • 虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。
  • 搜索引擎的优势在于采用了全文搜索的技术，核心原理是“倒排索引”。
• 列式数据库是相对于行式存储的数据库
  • Oracle、MySQL、SQL Server 等数据库都是采用的行式存储（Row-based），而列式数据库是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的 I/O，适合于分布式文件系统，不足在于功能相对有限。
• 图形数据库
  • 利用了图这种数据结构存储了实体（对象）之间的关系。最典型的例子就是社交网络中人与人的关系，数据模型主要是以节点和边（关系）来实现，特点在于能高效地解决复杂的关系问题。

2.SQL执行原理

2.1.Oracle

• 1、语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。
• 2、语义检查：检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。
• 3、权限检查：看用户是否具备访问该数据的权限。
• 4、共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。那软解析和硬解析又该怎么理解呢？
  • 在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，然后根据 Hash 值在库缓存（Library Cache）中查找，如果存在 SQL 语句的执行计划，就直接拿来执行，直接进入“执行器”的环节，这就是软解析。
  • 如果没有找到 SQL 语句和执行计划，Oracle 就需要创建解析树进行解析，生成执行计划，进入“优化器”这个步骤，这就是硬解析。
• 5、优化器：优化器中就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。
• 6、执行器：当有了解析树和执行计划之后，就知道了 SQL 该怎么被执行，这样就可以在执行器中执行语句了。

2.1.1.库缓存区

• 它主要缓存 SQL 语句和执行计划

库缓存这一个步骤，决定了 SQL 语句是否需要进行硬解析。

为了提升 SQL 的执行效率，我们应该尽量避免硬解析，因为在 SQL 的执行过程中，创建解析树，生成执行计划是很消耗资源的。

如何避免硬解析，尽量使用软解析呢

• 在 Oracle 中，绑定变量是它的一大特色。
  • 绑定变量就是在 SQL 语句中使用变量，通过不同的变量取值来改变 SQL 的执行结果。
  • 这样做的好处是能提升软解析的可能性
  • 不足之处在于可能会导致生成的执行计划不够优化，因此是否需要绑定变量还需要视情况而定。

这两个查询语句的效率在 Oracle 中是完全不同的。如果你在查询 player_id = 10001 之后，还会查询 10002、10003 之类的数据，那么每一次查询都会创建一个新的查询解析。而第二种方式使用了绑定变量，那么在第一次查询之后，在共享池中就会存在这类查询的执行计划，也就是软解析。

2.2.MySQL

首先 MySQL 是典型的 C/S 架构，即 Client/Server 架构，服务器端程序使用的 mysqld。

2.2.1.MySQL 由三层组成：

连接层：

• 客户端和服务器端建立连接，客户端发送 SQL 至服务器端；

SQL 层：

• 对 SQL 语句进行查询处理；

1、查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。

2、解析器：在解析器中对 SQL 语句进行语法分析、语义分析。

3、优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引来检索等。

4、执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

存储引擎层：

• 与数据库文件打交道，负责数据的存储和读取。

2.2.2.MySQL 存储引擎：

• InnoDB 存储引擎：它是 MySQL 5.5.8 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。

• MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。

• Memory 存储引擎：使用系统内存作为存储介质，以便得到更快的响应速度。不过如果 mysqld 进程崩溃，则会导致所有的数据丢失，因此我们只有当数据是临时的情况下才使用 Memory 存储引擎。

• NDB 存储引擎：也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的 RAC 集群。

• Archive 存储引擎：它有很好的压缩机制，用于文件归档，在请求写入时会进行压缩，所以也经常用来做仓库。

3.数据表设计

3.1.数据表的常见约束

3.1.1.主键约束

主键起的作用是唯一标识一条记录，不能重复，不能为空，即 UNIQUE+NOT NULL。一个数据表的主键只能有一个。主键可以是一个字段，也可以由多个字段复合组成（当一个字段无法确定唯一性时用多字段复合做为主键）

3.1.2.外键约束

外键确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的，也可以为空。

3.1.3.字段约束

• 唯一性约束

  • 唯一性约束表明了字段在表中的数值是唯一的，即使我们已经有了主键，还可以对其他字段进行唯一性约束。
  • 唯一性约束和普通索引（NORMAL INDEX）之间是有区别的。唯一性约束相当于创建了一个约束和普通索引，目的是保证字段的正确性，而普通索引只是提升数据检索的速度，并不对字段的唯一性进行约束。

• NOT NULL 约束。

  • 对字段定义了 NOT NULL，即表明该字段不应为空，必须有取值。

• DEFAULT，表明了字段的默认值。

  • 如果在插入数据的时候，这个字段没有取值，就设置为默认值。

• CHECK 约束

  • 用来检查特定字段取值范围的有效性，CHECK 约束的结果不能为 FALSE
  • 比如我们可以对身高 height 的数值进行 CHECK 约束，必须≥0，且＜3，即CHECK(height>=0 AND height<3)

3.2.设计数据表的原则

1.数据表的个数越少越好

• RDBMS 的核心在于对实体和联系的定义，也就是 E-R 图（Entity Relationship Diagram），数据表越少，证明实体和联系设计得越简洁，既方便理解又方便操作。

2.数据表中的字段个数越少越好

• 字段个数越多，数据冗余的可能性越大。设置字段个数少的前提是各个字段相互独立，而不是某个字段的取值可以由其他字段计算出来。当然字段个数少是相对的，我们通常会在数据冗余和检索效率中进行平衡

3.数据表中联合主键的字段个数越少越好

• 设置主键是为了确定唯一性，当一个字段无法确定唯一性的时候，就需要采用联合主键的方式（也就是用多个字段来定义一个主键）。联合主键中的字段越多，占用的索引空间越大，不仅会加大理解难度，还会增加运行时间和索引空间，因此联合主键的字段个数越少越好。

4.使用主键和外键越多越好

• 数据库的设计实际上就是定义各种表，以及各种字段之间的关系。这些关系越多，证明这些实体之间的冗余度越低，利用度越高。这样做的好处在于不仅保证了数据表之间的独立性，还能提升相互之间的关联使用率。

“三少一多”原则的核心就是简单可复用。简单指的是用更少的表、更少的字段、更少的联合主键字段来完成数据表的设计。可复用则是通过主键、外键的使用来增强数据表之间的复用率。因为一个主键可以理解是一张表的代表。键设计得越多，证明它们之间的利用率越高。

4.SQL操作

4.1.SQL 检索数据

4.1.1.SELECT

• 查询常数

​ SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。

• 语法

  • 如果常数是个字符串，那么使用单引号（‘’）就非常重要了，比如‘王者荣耀’。

  • 单引号说明引号中的字符串是个常数，否则 SQL 会把王者荣耀当成列名进行查询，但实际上数据表里没有这个列名，就会引起错误。

  • 如果常数是英文字母，比如’WZRY’也需要加引号。

  • 如果常数是个数字，就可以直接写数字，不需要单引号。

4.1.2. DISTINCT

去除重复行

• 注意

  • DISTINCT 需要放到所有列名的前面，如果写成SELECT name, DISTINCT attack_range FROM heros会报错。

  • DISTINCT 其实是对后面所有列名的组合进行去重

4.1.3. ORDER BY

排序检索数据

• 排序的列名：ORDER BY 后面可以有一个或多个列名，如果是多个列名进行排序，会按照后面第一个列先进行排序，当第一列的值相同的时候，再按照第二列进行排序，以此类推。
• 排序的顺序：ORDER BY 后面可以注明排序规则
  • ASC 代表递增排序，DESC 代表递减排序。
  • 如果没有注明排序规则，默认情况下是按照 ASC 递增排序
  • 如果排序字段类型为文本数据，就需要参考数据库的设置方式了，这样才能判断 A 是在 B 之前，还是在 B 之后。比如使用 MySQL 在创建字段的时候设置为 BINARY 属性，就代表区分大小写。
• 非选择列排序：ORDER BY 可以使用非选择列进行排序，所以即使在 SELECT 后面没有这个列名，你同样可以放到 ORDER BY 后面进行排序
• ORDER BY 的位置：ORDER BY 通常位于 SELECT 语句的最后一条子句，否则会报错。

4.1.5. LIMIT

• 约束返回结果的数量

​ 约束返回结果的数量，在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关键字，而且需要放到 SELECT 语句的最后面。如果是 SQL Server 和 Access，需要使用 TOP 关键字

​ 约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有 1 条，就可以使用LIMIT 1，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。

4.2. SQL数据过滤

学会使用 WHERE 子句，

WHERE 子句的基本格式是：

SELECT ……(列名) FROM ……(表名) WHERE ……(子句条件)

4.2.1.比较运算符

• 对字段的数值进行比较筛选；

4.2.2.逻辑运算符

进行多条件的过滤；

存在多个 WHERE 条件子句，可以使用逻辑运算符：

• 当 WHERE 子句中同时存在 OR 和 AND 的时候，AND 执行的优先级会更高，也就是说 SQL 会优先处理 AND 操作符，然后再处理 OR 操作符。

• 所以当 WHERE 子句中同时出现 AND 和 OR 操作符的时候，你需要考虑到执行的先后顺序，也就是两个操作符执行的优先级。一般来说 () 优先级最高，其次优先级是 AND，然后是 OR。

4.2.3.使用通配符进行过滤

刚才讲解的条件过滤都是对已知值进行的过滤，还有一种情况是我们要检索文本中包含某个词的所有数据，这里就需要使用通配符。通配符就是我们用来匹配值的一部分的特殊字符。这里我们需要使用到 LIKE 操作符。

如果我们想要匹配任意字符串出现的任意次数，需要使用（%）通配符。

请你编写 SQL 语句，对英雄名称、主要定位、次要定位、最大生命和最大法力进行查询，筛选条件为：主要定位是坦克或者战士，并且次要定位不为空，同时满足最大生命值大于 8000 或者最大法力小于 1500 的英雄，并且按照最大生命和最大法力之和从高到底的顺序进行排序:

SELECT name,role_main, role_assist, hp_max, mp_max
FROM heros
WHERE role_main IN ('坦克' ,'战士') AND role_assist IS NOT NULL 
AND (hp_max > 8000 OR mp_max <1500)
ORDER BY (hp_max + mp_max) DESC
1
2
3
4
5
6
7

5. SQL执行顺序

1、关键字的顺序是不能颠倒的：

​ SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …

2、SELECT 语句的执行顺序

​ FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

3、示例

​ 在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

首先，你可以注意到，SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：

• 1、首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
• 2、通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
• 3、添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
• 4、当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。
• 5、当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1，就可以在此基础上再进行 WHERE 阶段。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2。
• 6、然后进入第三步和第四步，也就是 GROUP 和 HAVING 阶段。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表 vt3 和 vt4。
• 7、当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到 SELECT 和 DISTINCT 阶段。
• 8、首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表 vt5-1 和 vt5-2。
• 9、当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是 ORDER BY 阶段，得到虚拟表 vt6。
• 10、最后在 vt6 的基础上，取出指定行的记录，也就是 LIMIT 阶段，得到最终的结果，对应的是虚拟表 vt7。

6. SQL 函数

6.1.常用的 SQL 函数

SQL 提供了一些常用的内置函数

针对数值、字符串和日期类型的数据，我们可以对它们分别进行算术函数、字符串函数以及日期函数的操作。如果想要完成不同类型数据之间的转换，就可以使用转换函数。

6.1.1.算数函数

6.1.2.字符串函数

6.1.3.日期函数

DATE 日期格式必须是 yyyy-mm-dd 的形式。如果要进行日期比较，就要使用 DATE 函数，不要直接使用日期与字符串进行比较

6.1.4.转换函数

CAST 函数在转换数据类型的时候，不会四舍五入，如果原数值有小数，那么转换为整数类型的时候就会报错。不过你可以指定转化的小数类型，在 MySQL 和 SQL Server 中，你可以用DECIMAL(a,b)来指定，其中 a 代表整数部分和小数部分加起来最大的位数，b 代表小数位数，比如DECIMAL(8,2)代表的是精度为 8 位（整数加小数位数最多为 8 位），小数位数为 2 位的数据类型。所以SELECT CAST(123.123 AS DECIMAL(8,2))的转换结果为 123.12。

SELECT AVG(hp_max) as '最大生命平均值' FROM heros;

SELECT name FROM heros WHERE DATE(birthdate) < '2017-01-01' AND birthdate IS NOT NULL
1
2
3

6.2.聚集函数

它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。

需要说明的是

• AVG、MAX、MIN 等聚集函数会自动忽略值为 NULL 的数据行
• MAX 和 MIN 函数也可以用于字符串类型数据的统计，如果是英文字母，则按照 A—Z 的顺序排列，越往后，数值越大。如果是汉字则按照全拼拼音进行排列。
• 如果我们不使用 DISTINCT 函数，就是对全部数据进行聚集统计。如果使用了 DISTINCT 函数，就可以对数值不同的数据进行聚集。
  

6.3.对数据进行分组，并进行聚集统计

我们在做统计的时候，可能需要先对数据按照不同的数值进行分组，然后对这些分好的组进行聚集统计。对数据进行分组，需要使用 GROUP BY 子句。

使用 HAVING 过滤分组，它与 WHERE 的区别是什么

HAVING 的作用和 WHERE 一样，都是起到过滤的作用，只不过 WHERE 是用于数据行，而 HAVING 则作用于分组。

对于分组的筛选，我们一定要用 HAVING，而不是 WHERE。另外你需要知道的是，HAVING 支持所有 WHERE 的操作，因此所有需要 WHERE 子句实现的功能，你都可以使用 HAVING 对分组进行筛选。

筛选最大生命值大于 6000 的英雄，按照主要定位、次要定位进行分组，并且显示分组中英雄数量大于 5 的分组，按照数量从高到低进行排序。

SQL: SELECT COUNT(*) as num, role_main, role_assist FROM heros WHERE hp_max > 6000 GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC

先使用 WHERE 子句对最大生命值大于 6000 的英雄进行条件过滤，然后再使用 GROUP BY 进行分组，使用 HAVING 进行分组的条件判断，然后使用 ORDER BY 进行排序。


SELECT COUNT(*) AS num, role_main, role_assist, AVG(hp_max) AS '平均最大生命值' FROM heros WHERE hp_max > 6000 GROUP BY role_main HAVING num > 5 ORDER BY num DESC

SELECT 
COUNT(*) AS num, 
ROUND(AVG(hp_max+mp_max),2),
MAX(hp_max+mp_max), 
MIN(hp_max+mp_max),
FROM heros
WHERE (hp_max+mp_max)>7000
GROUP BY attack_range
ORDER BY num DESC

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

7.子查询和连接查询

按照子查询执行的次数，我们可以将子查询分成关联子查询和非关联子查询，

7.1.子查询

7.1.1.非关联子查询

​ 非关联子查询与主查询的执行无关，只需要执行一次即可，一般是表内部查询无需借助其他关联表

• 示例
  • 我们以 NBA 球员数据表为例，假设我们想要知道哪个球员的身高最高，最高身高是多少，就可以采用子查询的方式：

SQL: SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)
1

通过SELECT max(height) FROM player可以得到最高身高这个数值，结果为 2.16，然后我们再通过 player 这个表，看谁具有这个身高，再进行输出，这样的子查询就是非关联子查询。

7.1.2.关联子查询

子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。

• 示例

  • 比如我们想要查找每个球队中大于平均身高的球员有哪些，并显示他们的球员姓名、身高以及所在球队 ID。

  SELECT player_name, height, team_id 
  FROM player AS a 
  WHERE height > 
  (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)
  1
  2
  3
  4

7.1.3. EXISTS 子查询

关联子查询通常也会和 EXISTS 一起来使用，EXISTS 子查询用来判断条件是否满足，满足的话为 True，不满足为 False。

• 示例

  • 比如我们想要看出场过的球员都有哪些，并且显示他们的姓名、球员 ID 和球队 ID。在这个统计中，是否出场是通过 player_score 这张表中的球员出场表现来统计的，如果某个球员在 player_score 中有出场记录则代表他出场过，这里就使用到了 EXISTS 子查询

  SQL：SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
  1

7.1.4.集合比较子查询

集合比较子查询的作用是与另一个查询结果集进行比较，我们可以在子查询中使用 IN、ANY、ALL 和 SOME 操作符

• IN

​ 还是通过上面那个例子，假设我们想要看出场过的球员都有哪些，可以采用 IN 子查询来进行操作

SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
1

• ANY 和 ALL

​ ANY 和 ALL 都需要使用比较符，比较符包括了（>）（=）（<）（>=）（<=）和（<>）等

• 示例

  • 如果我们想要查询球员表中，比印第安纳步行者（对应的 team_id 为 1002）中任意一个球员身高高的球员信息，并且输出他们的球员 ID、球员姓名和球员身高，该怎么写

  SQL: SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)
  1

  • 同样，如果我们想要知道比印第安纳步行者（对应的 team_id 为 1002）中所有球员身高都高的球员的信息，并且输出球员 ID、球员姓名和球员身高，该怎么写

  SQL: SELECT player_id, player_name, height FROM player WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002)
  1

7.1.5.将子查询作为计算字段

​ 子查询也可以作为主查询的计算字段。比如我想查询每个球队的球员数，也就是对应 team 这张表，我需要查询相同的 team_id 在 player 这张表中所有的球员数量是多少。

SQL: SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team
1

7.2.连接查询

7.2.1. 五种连接方式

它们分别是笛卡尔积、等值连接、非等值连接、外连接（左连接、右连接）和自连接。

7.2.1.1.笛卡尔积

• 笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。
• 笛卡尔积也称为交叉连接，英文是 CROSS JOIN，它的作用就是可以把任意表进行连接，即使这两张表不相关。

7.2.1.2.等值连接

两张表的等值连接就是用两张表中都存在的列进行连接。我们也可以对多张表进行等值连接。

针对 player 表和 team 表都存在 team_id 这一列，我们可以用等值连接进行查询。

SQL: SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id
1

7.2.1.3. 非等值连接

当我们进行多表查询的时候，如果连接多个表的条件是等号时，就是等值连接，其他的运算符连接就是非等值查询。

7.2.1.4. 外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。两张表的外连接，会有一张是主表，另一张是从表。如果是多张表的外连接，那么第一张表是主表，即显示全部的行，而第剩下的表则显示对应连接的信息。

7.2.1.5.自连接

自连接可以对多个表进行操作，也可以对同一个表进行操作。也就是说查询条件使用了当前表的字段。

SQL92:

比如我们想要查看比布雷克·格里芬高的球员都有谁，以及他们的对应身高：

SQL：SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克-格里芬' and a.height < b.height
1

SQL99:

SELECT b.player_name, b.height FROM player as a JOIN player as b ON a.player_name = '布雷克-格里芬' and a.height < b.height
1

8.视图

视图作为一张虚拟表，帮我们封装了底层与数据表的接口。它相当于是一张表或多张表的数据结果集。视图的这一特点，可以帮我们简化复杂的 SQL 查询，比如在编写视图后，我们就可以直接重用它，而不需要考虑视图中包含的基础查询的细节。相当于对select语句的封装

8.1.创建视图：CREATE VIEW

创建视图的语法是：

CREATE VIEW view_name ASSELECT column1,column2 FROM tableWHERE condition
1

实际上就是我们在 SQL 查询语句的基础上封装了视图 VIEW，这样就会基于 SQL 语句的结果集形成一张虚拟表。其中 view_name 为视图名称，column1、column2 代表列名，condition 代表查询过滤条件。

• 示例

我们以 NBA 球员数据表为例。我们想要查询比 NBA 球员平均身高高的球员都有哪些，显示他们的球员 ID 和身高。假设我们给这个视图起个名字 player_above_avg_height，那么创建视图可以写成：


CREATE VIEW player_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player)
1
2
3
4
5
6
7

当视图创建之后，它就相当于一个虚拟表，可以直接使用：

SELECT * FROM player_above_avg_height
1

运行结果和上面一样。

8.2. 嵌套视图

当我们创建好一张视图之后，还可以在它的基础上继续创建视图，比如我们想在虚拟表 player_above_avg_height 的基础上，找到比这个表中的球员平均身高高的球员，作为新的视图 player_above_above_avg_height，那么可以写成：

CREATE VIEW player_above_above_avg_height AS
SELECT player_id, height
FROM player
WHERE height > (SELECT AVG(height) from player_above_avg_height)
1
2
3
4

8.3.修改视图：ALTER VIEW

修改视图的语法是：

ALTER VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition
1
2
3
4

你能看出来它的语法和创建视图一样，只是对原有视图的更新。比如我们想更新视图 player_above_avg_height，增加一个 player_name 字段，可以写成：

ALTER VIEW player_above_avg_height AS
SELECT player_id, player_name, height
FROM player
WHERE height > (SELECT AVG(height) from player)
1
2
3
4

8.4.删除视图：DROP VIEW

删除视图的语法是：

DROP VIEW view_name
1

比如我们想把刚才创建的视图删除，可以使用：

DROP VIEW player_above_avg_height
1

8.5.使用视图简化 SQL 操作

8.5.1.利用视图完成复杂的连接

NBA 球员和身高等级连接的例子，有两张表，分别为 player 和 height_grades。其中 height_grades 记录了不同身高对应的身高等级。这里我们可以通过创建视图，来完成球员以及对应身高等级的查询。

• 首先我们对 player 表和 height_grades 表进行连接，关联条件是球员的身高 height（在身高等级表规定的最低身高和最高身高之间），这样就可以得到这个球员对应的身高等级，对应的字段为 height_level。
• 然后我们通过 SELECT 得到我们想要查询的字段，分别为球员姓名 player_name、球员身高 height，还有对应的身高等级 height_level。然后把取得的查询结果集放到视图 player_height_grades 中

CREATE VIEW player_height_grades AS
SELECT p.player_name, p.height, h.height_level
FROM player as p JOIN height_grades as h
ON height BETWEEN h.height_lowest AND h.height_highest
1
2
3
4

8.5.2.利用视图对数据进行格式化

我们经常需要输出某个格式的内容，比如我们想输出球员姓名和对应的球队，对应格式为 player_name(team_name)，就可以使用视图来完成数据格式化的操作：

CREATE VIEW player_team AS 
SELECT CONCAT(player_name, '(' , team.team_name , ')') AS player_team 
FROM player JOIN team 
WHERE player.team_id = team.team_id
1
2
3
4

8.5.3.使用视图与计算字段

我们在数据查询中，有很多统计的需求可以通过视图来完成。正确地使用视图可以帮我们简化复杂的数据处理。

如果我想要统计每位球员在每场比赛中的二分球、三分球和罚球的得分，可以通过创建视图完成：

CREATE VIEW game_player_score AS
SELECT game_id, player_id, (shoot_hits-shoot_3_hits)*2 AS shoot_2_points, shoot_3_hits*3 AS shoot_3_points, shoot_p_hits AS shoot_p_points, score  FROM player_score
1
2

8.6.视图的作用：

1、视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。

2、视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限），从而加强了安全性，使用户只能看到视图所显示的数据。

3、视图还可以被嵌套，一个视图中可以嵌套另一个视图。

注意：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。

9.存储过程

就是 SQL 语句的封装。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。

9.1.定义一个存储过程：

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    需要执行的语句
END    
1
2
3
4

删除已经创建的存储过程，使用的是 DROP PROCEDURE。如果要更新存储过程，我们需要使用 ALTER PROCEDURE。

• 实现一个简单的存储过程

  • 累加运算，计算 1+2+…+n

    CREATE PROCEDURE `add_num`(IN n INT)
    BEGIN
           DECLARE i INT;
           DECLARE sum INT;
           
           SET i = 1;
           SET sum = 0;
           WHILE i <= n DO
                  SET sum = sum + i;
                  SET i = i +1;
           END WHILE;
           SELECT sum;
    END
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13

9.2.存储过程的 3 种参数类型

IN 参数必须在调用存储过程时指定，而在存储过程中修改该参数的值不能被返回。而 OUT 参数和 INOUT 参数可以在存储过程中被改变，并可返回。

• 创建一个存储类型 get_hero_scores，用来查询某一类型英雄中的最大的最大生命值，最小的最大魔法值，以及平均最大攻击值

CREATE PROCEDURE `get_hero_scores`(
       OUT max_max_hp FLOAT,
       OUT min_max_mp FLOAT,
       OUT avg_max_attack FLOAT,  
       IN s VARCHAR(255)
       )
BEGIN
       SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END

从 heros 数据表中筛选主要英雄定位为 s 的英雄数据，即筛选条件为 role_main=s，提取这些数据中的最大的最大生命值，最小的最大魔法值，以及平均最大攻击值，分别赋值给变量 max_max_hp、min_max_mp 和 avg_max_attack。

然后我们就可以调用存储过程，使用下面这段代码即可：

CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

​

9.3.流控制语句

• BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。

• DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。

• SET：赋值语句，用于对变量进行赋值。

• SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

• IF…THEN…ENDIF：条件判断语句，我们还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。

• CASE：CASE 语句用于多条件的分支判断

  • CASE 
      WHEN expression1 THEN ...
      WHEN expression2 THEN ...
      ...
        ELSE 
        --ELSE语句可以加，也可以不加。加的话代表的所有条件都不满足时采用的方式。
    END
    
    1
    2
    3
    4
    5
    6
    7
    8

• LOOP、LEAVE 和 ITERATE：LOOP 是循环语句，使用 LEAVE 可以跳出循环，使用 ITERATE 则可以进入下一次循环。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 BREAK，把 ITERATE 理解为 CONTINUE。

• REPEAT…UNTIL…END REPEAT：这是一个循环语句，首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。

• WHILE…DO…END WHILE：这也是循环语句，和 REPEAT 循环不同的是，这个语句需要先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环。

9.4. DELIMITER 定义语句的结束符

DELIMITER 定义语句的结束符

• 如果直接使用 MySQL需要用 DELIMITER 来临时定义新的结束符。（navicat不需要此操作）
• 默认情况下 SQL 采用（；）作为结束符，这样当存储过程中的每一句 SQL 结束之后，采用（；）作为结束符，就相当于告诉 SQL 可以执行这一句了。
• 但是存储过程是一个整体，我们不希望 SQL 逐条执行，而是采用存储过程整段执行的方式，因此我们就需要临时定义新的 DELIMITER
• 新的结束符可以用（//）或者（$$）

如果你用的是 MySQL，那么上面这段代码，应该写成下面这样：

<--用DELIMITER关键字重新定义结束符为 // -->
DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;
       
       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
     
END //   <-- 整个存储过程结束后采用了（//）作为结束符号，告诉 SQL 可以执行了 -->

<--将结束符还原为 (;) -->
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

9.5.存储过程好处

• 存储过程只在创造时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。
• 其次它可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。
• 存储过程的安全性强，我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。
• 最后它可以减少网络传输量，因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。
• 同时在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。

9.6.缺点

• 它的可移植性差，存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
• 其次调试困难，只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。
• 此外，存储过程的版本管理也很困难，比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
• 最后它不适合高并发的场景，高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

9.7.比较视图

视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表

，用来得到某一类型英雄（主要定位为某一类型即可）的最大生命值的总和。

CREATE PROCEDURE `get_sum_score`(OUT max_max_hp_total FLOAT,IN type varchar(255))
BEGIN 
SELECT SUN(max_hp) FROM heros WHERE role_main = type INTO max_max_hp_total 

END	
1
2
3
4
5
6
7

10.三范式

• 一张数据表的设计结构需要满足的某种设计标准的级别叫做范式

• 数据库的范式设计越高阶，冗余度就越低，同时高阶的范式一定符合低阶范式的要求

• 一般来说数据表的设计应尽量满足 3NF。但也不绝对，有时候为了提高某些查询性能，我们还需要破坏范式规则，也就是反规范化。

10.1. 数据表中的那些键

范式的定义会使用到主键和候选键（因为主键和候选键可以唯一标识元组），数据库中的键（Key）由一个或者多个属性组成

• 超键：能唯一标识元组的属性集叫做超键。
• 候选键：如果超键不包括多余的属性，那么这个超键就是候选键。
• 主键：用户可以从候选键中选择一个作为主键。
• 外键：如果数据表 R1 中的某属性集不是 R1 的主键，而是另一个数据表 R2 的主键，那么这个属性集就是数据表 R1 的外键。
• 主属性：包含在任一候选键中的属性称为主属性。
• 非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性。

我们之前用过 NBA 的球员表（player）和球队表（team）。
	这里我可以把球员表定义为包含球员编号、姓名、身份证号、年龄和球队编号；球队表包含球队编号、主教练和球队所在地。
	对于球员表来说，超键就是包括球员编号或者身份证号的任意组合，比如（球员编号）（球员编号，姓名）（身份证号，年龄）等。
	候选键就是最小的超键，对于球员表来说，候选键就是（球员编号）或者（身份证号）。
	主键是我们自己选定，也就是从候选键中选择一个，比如（球员编号）。
	外键就是球员表中的球队编号。
	在 player 表中，主属性是（球员编号）（身份证号），其他的属性（姓名）（年龄）（球队编号）都是非主属性。
1
2
3
4
5
6
7

10.2. 1NF 到 3NF

1NF 指的是数据库表中的任何属性都是原子性的，不可再分。

• 这很好理解，我们在设计某个字段的时候，对于字段 X 来说，就不能把字段 X 拆分成字段 X-1 和字段 X-2。事实上，任何的 DBMS 都会满足第一范式的要求，不会将字段进行拆分。

2NF 指的数据表里的非主属性都要和这个数据表的候选键有完全依赖关系。

• 2NF 告诉我们一张表就是一个独立的对象，也就是说一张表只表达一个意思
• 所谓完全依赖不同于部分依赖，也就是不能仅依赖候选键的一部分属性，而必须依赖全部属性。

2NF示例

​ 这里我举一个没有满足 2NF 的例子，比如说我们设计一张球员比赛表 player_game，里面包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等属性，这里候选键和主键都为（球员编号，比赛编号），我们可以通过候选键来决定如下的关系：

• (球员编号, 比赛编号) → (姓名, 年龄, 比赛时间, 比赛场地，得分)

• 上面这个关系说明球员编号和比赛编号的组合决定了球员的姓名、年龄、比赛时间、比赛地点和该比赛的得分数据。

• 但是这个数据表不满足第二范式，因为数据表中的字段之间还存在着如下的对应关系：

  • (球员编号) → (姓名，年龄)
  • (比赛编号) → (比赛时间, 比赛场地)
  • 也就是说候选键中的某个字段决定了非主属性。你也可以理解为，对于非主属性来说，并非完全依赖候选键。
  • 这样会产生怎样的问题呢？
    • 数据冗余：如果一个球员可以参加 m 场比赛，那么球员的姓名和年龄就重复了 m-1 次。一个比赛也可能会有 n 个球员参加，比赛的时间和地点就重复了 n-1 次。
    • 插入异常：如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没法插入。
    • 删除异常：如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删除掉。
    • 更新异常：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调整，否则就会出现一场比赛时间不同的情况。
  • 为了避免出现上述的情况，我们可以把球员比赛表设计为下面的三张表。
    • 球员 player 表包含球员编号、姓名和年龄等属性；
    • 比赛 game 表包含比赛编号、比赛时间和比赛场地等属性；
    • 球员比赛关系 player_game 表包含球员编号、比赛编号和得分等属性。
  • 这样的话，每张数据表都符合第二范式，也就避免了异常情况的发生。

3NF 在满足 2NF 的同时，对任何非主属性都不传递依赖于候选键。

• 也就是说不能存在非主属性 A 依赖于非主属性 B，非主属性 B 依赖于候选键的情况。
  

10.3. BCNF（巴斯范式）

确认造成异常的原因：主属性仓库名对于候选键（管理员，物品名）是部分依赖的关系，这样就有可能导致上面的异常情况。

在 3NF 的基础上进行了改进，提出了 BCNF，也叫做巴斯 - 科德范式，它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。

10.4. 反范式设计

越高阶的范式得到的数据表越多，数据冗余度越低。但有时候，我们在设计数据表的时候，还需要为了性能和读取效率违反范式化的原则。反范式就是相对范式化而言的，换句话说，就是允许少量的冗余，通过空间来换时间。

• 在实际生活中，我们在显示商品评论的时候，通常会显示这个用户的昵称，而不是用户 ID，因此我们还需要关联 product_comment 和 user 这两张表来进行查询。
• 当表数据量不大的时候，查询效率还好，但如果表数据量都超过了百万量级，查询效率就会变低。
• 这是因为查询会在 product_comment 表和 user 表这两个表上进行聚集索引扫描，然后再嵌套循环，这样一来查询所耗费的时间就有几百毫秒甚至更多。对于网站的响应来说，这已经很慢了，用户体验会非常差。
• 如果我们想要提升查询的效率，可以允许适当的数据冗余，也就是在商品评论表中增加用户昵称字段，在 product_comment 数据表的基础上增加 user_name 字段，就得到了 product_comment2 数据表。

当冗余信息有价值或者能大幅度提高查询效率且读多写少的场景的时候，我们就可以采取反范式的优化。

11. 索引

索引的本质目的是帮我们快速定位想要查找的数据

11.1.种类

• 普通索引

  • 基础的索引，没有任何约束，主要用于提高查询效率

• 唯一索引

  • 在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引

• 主键索引

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

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

11.1.1.物理实现方式，聚集索引和非聚集索引：

我们也把非聚集索引称为二级索引或者辅助索引。

（建立索引就是生成一张包含所有索引的树，聚集索引的叶子节点就包含了想要的数据，而非聚集索引的叶子节点存放的是数据的位置）

• 非聚集索引
  • 在数据库系统会有单独的存储空间存放非聚集索引，维护单独的索引表（只维护索引，不维护索引指向的数据），因此索引项是按照顺序存储的，但索引项指向的内容是随机存储的。
  • 系统会进行两次查找，第一次先找到索引，第二次找到索引对应的位置取出数据行。

使用聚集索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚集索引低。

11.1.2.字段个数进行划分，分成单一索引和联合索引。

索引列为一列时为单一索引；多个列组合在一起创建的索引叫做联合索引。

创建联合索引时，我们需要注意创建时的顺序问题，因为联合索引 (x, y, z) 和 (z, y, x) 在使用的时候效率可能会存在差别。

这里需要说明的是联合索引存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

• 示例

  • 比如刚才举例的 (x, y, z)，如果查询条件是 WHERE x=1 AND y=2 AND z=3，就可以匹配上联合索引；如果查询条件是 WHERE y=2，就无法匹配上联合索引。

  • 当我们使用了联合索引 (user_id, user_name) 的时候，在 WHERE 子句中对联合索引中的字段 user_id 和 user_name 进行条件查询，或者只对 user_id 进行查询，效率基本上是一样的。当我们对 user_name 进行条件查询时，效率就会降低很多，这是因为根据联合索引的最左原则，user_id 在 user_name 的左侧，如果没有使用 user_id，而是直接使用 user_name 进行条件查询，联合索引就会失效。

11.3.注意事项

• 如果想要定位的数据有很多，那么索引就失去了它的使用价值

  • 比如对100万个人按着性别建立索引，那么男女索引树上就分别会有50w个节点，扫描索引树找到主键后还需要回表再查一次就很慢。
  • 假设有一个女儿国，人口总数为 100 万人，男性只有 10 个人，也就是占总人口的 10 万分之 1。女儿国的人口数据表 user_gender 其中数据表中的 user_gender 字段取值为 0 或 1，0 代表女性，1 代表男性。（此时建立的索引树男性树上就只有 10条数据扫描非常快

11.4.创建索引的规律

1、字段的数值有唯一性的限制，比如用户名

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。

2、频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下

在数据量大的情况下，某个字段在 SQL 查询的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。创建普通索引就可以大幅提升数据查询的效率。

3、需要经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。

4、UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

where字段:有索引，有利于更新或删除。set字段:无索引，有利于更新，因为无需维护索引。

5、DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率

6、做多表 JOIN 连接操作时，创建索引需要注意以下的原则

• 首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
• 其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
• 最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 user_id 在 product_comment 表和 user 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

11.5.什么时候不需要创建索引

• 如果表记录太少，比如少于 1000 个，那么是不需要创建索引的。我之前讲过一个 SQL 查询的例子（第 23 篇中的 heros 数据表查询的例子，一共 69 个英雄不用索引也很快），表记录太少，是否创建索引对查询效率的影响并不大。
• 字段中如果有大量重复数据，也不用创建索引，比如性别字段。不过我们也需要根据实际情况来做判断
• 频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

11.6.索引失效

11.6.1、如果索引进行了表达式计算，则会失效

我们可以使用 EXPLAIN 关键字来查看 MySQL 中一条 SQL 语句的执行计划，比如：

EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id+1 = 900001
1

运行结果：

+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | product_comment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996663 |   100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1
2
3
4
5

你能看到如果对索引进行了表达式计算，索引就失效了。这是因为我们需要把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式，运行时间也会慢很多，最终运行时间为 2.538 秒。

为了避免索引失效，我们对 SQL 进行重写：

SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900000
1

运行时间为 0.039 秒

11.6.2、如果对索引使用函数，也会造成失效

比如我们想要对 comment_text 的前三位为 abc 的内容进行条件筛选，这里我们来查看下执行计划：

EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE SUBSTRING(comment_text, 1,3)='abc'
1

运行结果

+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | product_comment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996663 |   100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+	
1
2
3
4
5

你能看到对索引字段进行函数操作，造成了索引失效，这时可以进行查询重写：

SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE 'abc%'	
1

使用 EXPLAIN 对查询语句进行分析：

+----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table           | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | product_comment | NULL       | range | comment_text  | comment_text | 767     | NULL |  213 |   100.00 | Using index condition |
+----+-------------+-----------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1
2
3
4
5

普通索引和唯一索引在查询效率上有什么不同？

• 唯一索引就是在普通索引上增加了约束性，也就是关键字唯一，找到了关键字就停止检索。
• 而普通索引，可能会存在用户记录中的关键字相同的情况，根据页结构的原理，当我们读取一条记录的时候，不是单独将这条记录从磁盘中读出去，而是将这个记录所在的页加载到内存中进行读取。InnoDB 存储引擎的页大小为 16KB，在一个页中可能存储着上千个记录，因此在普通索引的字段上进行查找也就是在内存中多几次“判断下一条记录”的操作，对于 CPU 来说，这些操作所消耗的时间是可以忽略不计的。所以**对一个索引字段进行检索，采用普通索引还是唯一索引在检索效率上基本上没有差别。

11.6.3、在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。

在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。

EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_id = 900001 OR comment_text = '462eed7ac6e791292a79'
1

运行结果

+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | product_comment | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 996663 |    10.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1
2
3
4
5

如果我们把 comment_text 创建了索引会是怎样的呢？

+----+-------------+-----------------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table           | partitions | type        | possible_keys        | key                  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+-----------------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | product_comment | NULL       | index_merge | PRIMARY,comment_text | PRIMARY,comment_text | 4,767   | NULL |    2 |   100.00 | Using union(PRIMARY,comment_text); Using where |
+----+-------------+-----------------+------------+-------------+----------------------+----------------------+---------+------+------+----------+------------------------------------------------+
1
2
3
4
5

你能看到这里使用到了 index merge，简单来说 index merge 就是对 comment_id 和 comment_text 分别进行了扫描，然后将这两个结果集进行了合并。这样做的好处就是避免了全表扫描。

11.6.4、当我们使用 LIKE 进行模糊查询的时候，前面不能是 %

EXPLAIN SELECT comment_id, user_id, comment_text FROM product_comment WHERE comment_text LIKE '%abc'
1

+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | product_comment | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996663 |    11.11 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1
2
3
4
5

这个很好理解，如果一本字典按照字母顺序进行排序，我们会从首位开始进行匹配，而不会对中间位置进行匹配，否则索引就失效了。

11.6.5、索引列尽量设置为 NOT NULL 约束。

MySQL 官方文档建议我们尽量将数据表的字段设置为 NOT NULL 约束，

这样做的好处是可以更好地使用索引，节省空间，甚至加速 SQL 的运行。

判断索引列是否为 NOT NULL，往往需要走全表扫描，因此我们最好在设计数据表的时候就将字段设置为 NOT NULL 约束比如你可以将 INT 类型的字段，默认值设置为 0。将字符类型的默认值设置为空字符串 (‘’)。

11.6.6、我们在使用联合索引的时候要注意最左原则

最左原则也就是需要从左到右的使用索引中的字段，一条 SQL 语句可以只使用联合索引的一部分，但是需要从最左侧开始，否则就会失效。我在讲联合索引的时候举过索引失效的例子。

12.数据库中的存储结构

记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。因此在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页（Page）。

一个页中可以存储多个行记录（Row），同时在数据库中，还存在着区（Extent）、段（Segment）和表空间（Tablespace）。行、页、区、段、表空间的关系如下图所示：

从图中你能看到一个表空间包括了一个或多个段，一个段包括了一个或多个区，一个区包括了多个页，而一个页中可以有多行记录

• 区（Extent）

  • 是比页大一级的存储结构，在 InnoDB 存储引擎中，一个区会分配 64 个连续的页。因为 InnoDB 中的页大小默认是 16KB，所以一个区的大小是 64*16KB=1MB。

• 段（Segment）

  • 由一个或多个区组成，区在文件系统是一个连续分配的空间（在 InnoDB 中是连续的 64 个页），不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。

• 表空间（Tablespace）

  • 是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。
  • 在 InnoDB 中存在两种表空间的类型：共享表空间和独立表空间。如果是共享表空间就意味着多张表共用一个表空间。如果是独立表空间，就意味着每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间可以在不同的数据库之间进行迁移。

12.1.数据页内的结构

页（Page）如果按类型划分的话，常见的有数据页（保存 B+ 树节点）、系统页、Undo 页和事务数据页等。数据页是我们最常使用的页。

表页的大小限定了表行的最大长度，不同 DBMS 的表页大小不同。比如在 MySQL 的 InnoDB 存储引擎中，默认页的大小是 16KB

数据库 I/O 操作的最小单位是页，与数据库相关的内容都会存储在页结构里。数据页包括七个部分，分别是文件头（File Header）、页头（Page Header）、最大最小记录（Infimum+supremum）、用户记录（User Records）、空闲空间（Free Space）、页目录（Page Directory）和文件尾（File Tailer）。

12.1.1.从数据页的角度看 B+ 树是如何进行查询的

在一棵 B+ 树中，每个节点都是一个页，每次新建节点的时候，就会申请一个页空间。同一层上的节点之间，通过页的结构构成一个双向的链表（页文件头中的两个指针字段）。非叶子节点，包括了多个索引行，每个索引行里存储索引键和指向下一层页面的页面指针。最后是叶子节点，它存储了关键字和行记录，在节点内部（也就是页结构的内部）记录之间是一个单向的链表，但是对记录进行查找，则可以通过页目录采用二分查找的方式来进行。

• B+ 树是如何进行记录检索的？

​ 如果通过 B+ 树的索引查询行记录，首先是从 B+ 树的根开始，逐层检索，直到找到叶子节点，也就是找到对应的数据页为止，将数据页加载到内存中，页目录中的槽（slot）采用二分查找的方式先找到一个粗略的记录分组，然后再在分组中通过链表遍历的方式查找记录。

13.数据库缓冲池

必要性

磁盘 I/O 需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲池，这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间。要知道，这种策略对提升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。

工作方式

缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进行读取。

checkpoint 的机制

对数据库中的记录进行修改的时候，并不是每次发生更新操作，都会立刻进行磁盘回写，首先会修改缓冲池中页里面的记录信息，然后数据库会以一定的频率刷新到磁盘上。

提升了数据库的整体性能。

数据页加载的三种方式

• 内存读取

• 随机读取
  • 如果数据没有在内存中，就需要在磁盘上对该页进行查找，整体时间预估在 10ms 左右，这 10ms 中有 6ms 是磁盘的实际繁忙时间（包括了寻道和半圈旋转时间），有 3ms 是对可能发生的排队时间的估计值，另外还有 1ms 的传输时间，将页从磁盘服务器缓冲区传输到数据库缓冲区中。这 10ms 看起来很快，但实际上对于数据库来说消耗的时间已经非常长了，因为这还只是一个页的读取时间。

• 顺序读取
  • 顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的，顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其他页面单独进行磁盘 I/O 操作了。如果一个磁盘的吞吐量是 40MB/S，那么对于一个 16KB 大小的页来说，一次可以顺序读取 2560（40MB/16KB）个页，相当于一个页的读取时间为 0.4ms。采用批量读取的方式，即使是从磁盘上进行读取，效率也比从内存中只单独读取一个页的效率要高。

影响数据库加载的因素（页加载角度）

• 位置决定效率。

  • 如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内存或者磁盘中进行读取，当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。

• 批量决定效率。

  • 如果我们从磁盘中对单一页进行随机读，那么效率是很低的（差不多 10ms），而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

缓冲池与查询缓存的区分

缓冲池：

在访问物理硬盘和在内存中进行访问速度相差很大，为了尽可能弥补这中间的IO效率鸿沟，我们就需要把经常使用的数据加载到缓冲池中，采用“预读”的机制来减少未来的磁盘IO操作，进行提前加载。避免每次访问都进行磁盘IO，从而提升数据库整体的访问性能。

查询缓存

查询缓存是提前把查询结果缓存起来，这样下次就不需要执行可以直接拿到结果。

区别总结

• 共同的特点
  • 就是都是通过缓存的机制来提升效率。

缓冲池是服务于数据库整体的IO操作，通过建立缓冲池机制来弥补存储引擎的磁盘文件与内存访问之间的效率鸿沟，同时缓冲池会采用“预读”的机器提前加载一些马上会用到的数据，以提升整体的数据库性能。

而查询缓存是服务于SQL查询和查询结果集的，因为命中条件苛刻，而且只要当数据表发生了变化，查询缓存就会失效，因此命中率低。