MySQL基础知识

b、查询缓存：查询缓存中有 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。 c、解析器：在解析器中对 SQL 语句进行语法分析、语义分析。 d、优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引来检索等。 e、执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

2、基础架构：SQL查询语句执行流程

1.MySQL的逻辑架构图

a、MySQL 可以分为 Server 层和存储引擎层两部分。

b、Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等

c、存储引擎层负责数据的存储和提取，架构模式是插件式，事务支持是在引擎层实现的，支持 InnoDB（常见、默认的）、MyISAM（与InnoDB对比、不足）、Memory 等多个存储引擎。

2.连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接

3.查询缓存

查询命中缓存，MySQL 不需要执行后面的复杂操作；语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中 命中查询缓存，会在查询缓存返回结果的时候，做权限验证

3.分析器

a、“词法分析” ：识别出里面的字符串分别是什么，代表什么。

b、语法分析” ：根据语法规则，判断你输入的 SQL 语句是否满足 MySQL 语法 、

4.优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

5.执行器

a、MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

b、开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限

c、有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口

d、ID 字段没有索引，那么执行器的执行流程：

i.调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；

ii.调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

iii.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

iv.语句执行完成

e、ID 字段有索引，那么执行器的执行流程：

i.调用的是“取满足条件的第一行”这个接口

ii.之后循环取“满足条件的下一行”这个接口 (这些接口都是引擎中已经定义好的)

3、查询优化器是如何工作的

1.什么是查询优化器

事务和索引的使用是数据库中的两个重要核心，事务可以让数据库在增删查改的过程中，保证数据的正确性和安全性，而索引可以帮数据库提升数据的查找效率。

a、SQL语句执行流程：

b、分析器：

i.语法分析：检查 SQL 拼写和语法

ii.语义检查：检查 SQL 中的访问对象是否存在

c、查询优化器：

i.目标：找到执行 SQL 查询的最佳执行计划（查询树：一系列物理操作符按照一定的运算关系组成的查询执行计划）

ii.逻辑查询优化（代数语法级优化）：基于关系代数进行的查询重写，对 SQL 语句进行等价变换（对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等）

iii.物理查询优化（物理层面）：关系代数的每一步都对应着物理计算，物理计算往往存在多种算法，需要计算各种物理路径的代价，选择代价最小的作为执行计划 （对于单表和多表连接的操作，需要高效地使用索引，提升查询效）

2.两种优化方式

a、基于规则的优化器（RBO，Rule-Based Optimizer）：通过在优化器里面嵌入规则，来判断 SQL 查询符合哪种规则，就按照相应的规则来制定执行计划，同时采用启发式规则去掉明显不好的存取路径（规则：以往的经验或已被证明的有效的方式）

b、基于代价的优化器（CBO，Cost-Based Optimizer）：根据代价评估模型，计算每条可能的执行计划的代价，也就是 COST，从中选择代价最小的作为执行计划。

c、CBO 对数据更敏感，会利用数据表中的统计信息来做判断，针对不同的数据表，查询得到的执行计划可能不同，制定的执行计划也更符合数据表的实际情况。

d、SQL 是面向集合的语言，并没有指定执行的方式，在优化器中会存在各种组合的可能。需要通过优化器来制定数据表的扫描方式、连接方式以及连接顺序，从而得到最佳的 SQL 执行计划。

3.CBO是如何统计代价的

a、能调整的代价模型的参数：

server 层(mysql.server_cost：主要是CPU代价) ：

i.disk_temptable_create_cost：表示磁盘临时表文件（MyISAM 或 InnoDB）的创建代价，默认值为 20

ii.disk_temptable_row_cost：表示临时表文件（MyISAM 或 InnoDB）的行代价，默认值 0.5

iii.key_compare_cost：表示键比较的代价。键比较的次数越多，这项的代价就越大，这是一个重要的指标，默认值 0.05

iv.memory_temptable_create_cost：表示内存中临时表的创建代价，默认值 1

v.memory_temptable_row_cost：表示内存中临时表的行代价，默认值 0.1。

vi.row_evaluate_cost：统计符合条件的行代价，如果符合条件的行数越多，那么这一项的代价就越大，默认值 0.1

存储引擎层 (mysql.engine_cost ：主要统计页加载代价,主要是I/O代价) ：

i.io_block_read_cost：从磁盘中读取一页数据的代价，默认是 1。

ii.memory_block_read_cost：从内存中读取一页数据的代价，默认是 0.25。

b、代价模型如何计算

i.总代价 = I/O代价+CPU代价

ii.I/O代价：即PAGE FETCH，页面加载的代价（数据页和索引页）

iii.CPU代价：即W*(RSI CALLS) （W ：权重因子，CPU 到 I/O 之间转化的相关系数；CALLS 代表了 CPU 的代价估算（键比较（compare key）以及行估算（row evaluating））））

iv.总代价 = I/O 代价 + CPU 代价 + 内存代价 + 远程代价

4、日志系统：SQL更新语句执行流程

1.SQL更新语句执行流程

a、MySQL 可以恢复到半个月内任意一秒的状态

b、更新语句的执行流程和查询语句的一致

c、在一个表上有更新的时候，跟这个表有关的查询缓存会失效，这条语句就会把表 T 上所有缓存结果都清空

d、分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，执行器负责具体执行，找到这一行，然后更新。

e、与查询流程不一样的是：更新流程还涉及两个重要的日志模块redo log（重做日志）和 binlog（归档日志）

2.redo log（InnoDB 引擎的重做日志）

a、（重做日志）：引擎存储层（InnoDB）生成的日志，主要是为了保证数据的可靠性，它是一个物理日志，记录对哪里进行了修改，比如页号xxx、偏移量yyy写入了zzz数据

b、WAL——Write Ahead Log 先写日志，再写磁盘（注意，这里更新的是操作记录）

c、redo log的基本结构（一个数据环），write pos和check point（双指针）在环上追加

d、有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

e、缓冲池：一部分内存，保存的是经常使用的数据页；更新数据，先修改缓冲池（内存）中页里面的记录信息，然后再刷新到磁盘上。

f、flush：把内存里的数据写入磁盘的过程

g、当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”

h、平时执行很快的更新操作，其实就是在写内存和日志， MySQL语句执行突然变得很慢，可能就是在刷脏页（flush）

i、引发flush的四种场景：

i.InnoDB 的 redo log 写满了。系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写

ii.系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘

iii.MySQL 认为系统“空闲”的时候

iv.MySQL 正常关闭的情况

3.binlog（Server 层的归档日志）

a、binlog 日志只能用于归档，bin是二进制的意思，即逻辑日志，存储的SQL语句

b、binlog（归档日志）和redo log（重写日志）的区别：

i.redo log 是 InnoDB 引擎特有的；

binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用

ii.redo log 是物理日志，记录的是“在某个数据页上做了什么修改”

binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”

iii.redo log 是循环写的，空间固定会用完

binlog 是可以追加写入的。“追加写”是指binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

4.执行器和 InnoDB 引擎在执行更新语句时的内部流程

a、执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回

b、执行器拿到引擎给的行数据，进行处理，得到新数据，调用引擎接口写入新数据

c、引擎将新数据更新到内存中，同时将这个操作记录到 redo log 里面，此时redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务

d、执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

e、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成

5.redo log的两阶段提交

a、原因：为了让两份日志之间的逻辑一致

b、怎样让数据库恢复到半个月内任意一秒的状态？

i.找到最近的一次全量备份，从这个备份恢复到临时库；

ii.从备份的时间点开始，将备份的 binlog 依次取出来，重放到误删表之前的那个时刻

iii.临时库就跟误删之前的线上库一样了，然后把表数据从临时库取出来，按需要恢复到线上库去

c、为什么日志需要“两阶段提交”

i.先写完 redolog 再写 binlog：写完 redolog 在写 binlog的时候发生crash，binlog 里没有记录更新语句，之后备份日志时，存起来的 binlog 里面就没有这条语句，需要用这个 binlog 来恢复临时库时，由于这个语句的binlog 丢失，这个临时库就会少了这一次更新，恢复出来的值就与原库的值不同

ii.先写 binlog 后写 redo log ： 在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效

iii.undo log（引擎层日志，用于事务回滚和MVCC）

4、设计表的原则

“三少一多”原则 ：

1.数据表的个数越少越好：RDBMS 的核心在于对实体和联系的定义，也就是 E-R 图，数据表越少，证明实体和联系设计得越简洁

2.数据表中的字段个数越少越好：字段个数越多，数据冗余的可能性越大。尽可能设置个数少的相互独立的字段

3.数据表中联合主键的字段个数越少越好：联合主键中的字段越多，占用的索引空间越大，加大理解难度，增加运行时间和索引空间，因此联合主键的字段个数越少越好

4.使用主键和外键越多越好：保证了数据表之间的独立性，还能提升相互之间的关联使用率

5、常用的建表基本操作

1.对数据库进行定义：

CREATE DATABASE nba; // 创建一个名为 nba 的数据库 DROP DATABASE nba; // 删除一个名为 nba 的数据库

2.对数据表进行定义：

CREATE TABLE table_name ；//创建一个table_name的数据表

DROP TABLE IF EXISTS player; //如果存在一个名为player的数据表就删除它 CREATE TABLE player ( player_id int(11) NOT NULL AUTO_INCREMENT, //int(11) 代表整数类型,括号中的参数 11 代表的是最大有效显示长度; NOT NULL表明整个字段不能是空值 ;AUTO_INCREMENT代表主键自动增长 team_id int(11) NOT NULL, // player_name varchar(255) CHARACTER SET utf8 COLLATE // height float(3, 2) NULL DEFAULT 0.00, // PRIMARY KEY (player_id) USING BTREE, // UNIQUE INDEX player_name(player_name) USING BTREE // ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci //

3.删除表但是表空间却没变的原因：

delete 命令只是把记录的位置或者数据页标记为了“可复用”或者“已删除”，被delete的记录或者数据页会变成“空洞”

4.收缩表空间的方法：（重建表）

a、建立一个临时文件，扫描表 A 主键的所有数据页； b、用数据页中表 A 的记录生成 B+ 树，存储到临时文件中； c、生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态； d、临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态； e、用临时文件替换表 A 的数据文件。

6、如何使用 SQL 检索数据

一个数据表是由列（字段名）和行（数据行）组成。

1.SELECT 查询的基础语法

a、查询列：

SELECT name FROM heros //SELECT 一个or多个列字段名 FROM heros

SELECT * FROM heros //查询所有列

b、起别名：对原有名称进行简化

SELECT name AS n, hp_max AS hm, mp_max AS mm, attack_max AS am, defense_max AS dm FROM heros

c、查询常数：在 SELECT 查询结果中增加一列固定的常数列，整合不同的数据源，用常数列作为这个表的标记，就需要查询常数

SELECT '王者荣耀' as platform, name FROM heros //单引号说明引号中的字符串是个常数，否则 SQL 会把王者荣耀当成列名进行查询

d、去除重复行：从结果中去掉重复的行

SELECT DISTINCT attack_range FROM heros

note:

i. DISTINCT 需要放到所有列名的前面

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

2.如何排序检索数据

有时检索数据需要按照某种顺序进行结果的返回，需要使用 ORDER BY 子句

note：

排序的列名：ORDER BY 后面可以有一个或多个列名，如果是多个列名进行排序，会按照后面第一个列先进行排序，当第一列的值相同的时候，再按照第二列进行排序，以此类推。
排序的顺序：ORDER BY 后面可以注明排序规则，ASC 代表递增排序，DESC 代表递减排序。没有注明排序规则，默认按 ASC 递增排序。
非选择列排序：ORDER BY 可以使用非选择列进行排序，所以即使在 SELECT 后面没有这个列名，你同样可以放到 ORDER BY 后面进行排序。
ORDER BY 的位置：ORDER BY 通常位于 SELECT 语句的最后一条子句，否则会报错。

eg.SELECT name, hp_max FROM heros ORDER BY hp_max DESC

3.约束返回结果的数量

使用 LIMIT 关键字可以约束返回结果的数量

eg. SELECT name, hp_max FROM heros ORDER BY hp_max DESC LIMIT 5

4.SELECT 的执行顺序

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

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...
SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

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

7、子查询的种类和提高性能的方式

1.什么是子查询？

子查询就是查询的嵌套，有时候我们无法直接在数据表中得到查询结果，这时候将一个查询（这就是子查询）的结果集当做主查询的查询对象，这样就支持了比较复杂的查询，有点面向对象的意思。

2.子查询的分类

子查询分为关联子查询和非关联子查询
关联子查询和非关联子查询的判断依据是子查询是否执行多次
非关联子查询：子查询只执行一次，将结果集作为主查询的条件执行。这样的嵌套关系称作非关联子查询

SELECT player_name,MAX(height) FROM player;
关联子查询：子查询执行多次，循环查询方式，每次都传入子查询进行查询，这样的子查询被称作关联子查询

查询身高高于平均身高的球员，并显示球员姓名，身高，所在球队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);

子查询用到了主查询的表，并进行了条件关联这样每次都要将主查询表的id传给子查询，直到循环完主表。

3. EXISTS 子查询

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

eg.EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

4.集合比较子查询

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

IN	判断是否在集合中
ANY	需要与比较操作符一起使用，与子查询返回的任何值做比较
ALL	需要与比较操作符一起使用，与子查询返回的任何值做比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

IN 和 EXISTS 都可以得到相同的结果，需要判断表 A 和表 B 的大小，表 A 比表 B 大，那么 IN 子查询的效率要比 EXIST 子查询效率高；表 A 比表 B 小，那么使用 EXISTS 子查询效率会更高。

ANY:想要查询球员表中，比印第安纳步行者（对应的 team_id 为 1002）中任何一个球员身高高的球员的信息，并且输出他们的球员 ID、球员姓名和球员身高

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

ALL:比印第安纳步行者（对应的 team_id 为 1002）中所有球员身高都高的球员的信息，并且输出球员 ID、球员姓名和球员身高

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

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

子查询也可以作为主查询的计算字段：

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

将子查询SELECT count(*) FROM player WHERE player.team_id = team.team_id作为了计算字段，通常我们需要给这个计算字段起一个别名，这里我用的是 player_num，因为子查询的语句比较长，使用别名更容易理解。

8、SQL表的连接操作

1.常用的SQL标准：SQL92和SQL99

2.SQL92中的连接表的操作

a、笛卡尔积

概念：笛卡尔乘积是一个数学运算；两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合

笛卡尔积的调用方式： SELECT * FROM player, team

特点：也称为交叉连接（CROSS JOIN），可以把任意表进行连接

b、等值连接

概念：两张表的等值连接就是用两张表中都存在的列进行连接 （通过相同列将多张表合并）

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

连接调用方式：SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id

c、非等值连接

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

连接调用方式：SELECT p.player_name, p.height, h.height_level FROM player AS p, height_grades AS h WHERE p.height BETWEEN h.height_lowest AND h.height_highest

d、外连接（左连接、右连接）

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

两张表的外连接，会有一张是主表，另一张是从表。

多张表的外连接，第一张表是主表显示全部的行，剩下的表则显示对应连接的信息。在SQL92 中采用（+）代表从表所在的位置，

在 SQL92中，只有左外连接和右外连接，没有全外连接

左外连接：左边的表是主表，需要显示左边表的全部行，右侧的表是从表，（+）表示哪个是从表

SELECT * FROM player, team where player.team_id = team.team_id(+)

右外连接：右边的表是主表，需要显示右边表的全部行，左侧的表是从表

SELECT * FROM player, team where player.team_id(+) = team.team_id

note：LEFT JOIN 和 RIGHT JOIN 只存在于SQL99 及以后的标准中，在 SQL92 中不存在，只能用（+）表示

e、自连接

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

调用方式：SELECT b.player_name, b.height FROM player as a , player as b WHERE a.player_name = '布雷克 - 格里芬' and a.height < b.height

等同于两次SQL 查询： SELECT height FROM player WHERE player_name = '布雷克 - 格里芬'

SELECT player_name, height FROM player WHERE height > 2.08

3.SQL99中的连接表的操作

a、交叉连接（CROSS JOIN）

概念：交叉连接实际上就是 SQL92 中的笛卡尔乘积

调用方式：SELECT * FROM player CROSS JOIN team

b、自然连接

概念：可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

调用方式：

99：SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team

92：SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id

c、ON 连接

概念：ON 连接用来指定我们想要的连接条件。

调用方式：SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id

d、USING 连接

概念：可以用 USING 指定数据表里的同名字段进行等值连接。

调用方式：SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id)

e、外连接

i. 左外连接：LEFT JOIN 或 LEFT OUTER JOIN

调用方式：

92：SELECT * FROM player, team where player.team_id = team.team_id(+)

99：SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id

ii. 右外连接：RIGHT JOIN 或 RIGHT OUTER JOIN

调用方式：

92：SELECT * FROM player, team where player.team_id(+) = team.team_id

99：SELECT * FROM player RIGHT JOIN team ON player.team_id = team.team_id

iii. 全外连接：FULL JOIN 或 FULL OUTER JOIN

概念：全外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

调用方式：SELECT * FROM player FULL JOIN team ON player.team_id = team.team_id

f、自连接

调用方式：

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

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

4.SQL99 和 SQL92 的区别

连接操作基本上可以分成三种情况：

a、内连接：将多个表之间满足连接条件的数据行查询出来。它包括了等值连接、非等值连接和自连接

b、外连接：会返回一个表中的所有记录，以及另一个表中匹配的行。它包括了左外连接、右外连接和全连接

c、交叉连接：也称为笛卡尔积，返回左表中每一行与右表中每一行的组合。在 SQL99 中使用的 CROSS JOIN

SQL99 和 SQL92 的区别：

a、SQL92 中的 WHERE 和 SQL99 中的 JOIN：

SQL92： FROM 所有需要连接的表，WHERE 连接的条件

SQL99 ：不需要一次性把所有需要连接的表都放到 FROM 之后，FROM Table 1 JOIN Table2 ON 连接条件

b、SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 和 JOIN USING。USING省略了 ON 后面的等值条件判断

5.连接性能

a、控制连接表的数量 ：多表连接就相当于嵌套 for 循环一样，非常消耗资源

b、在连接时不要忘记 WHERE 语句

c、使用自连接而不是子查询：自连接的处理速度要比子查询快得多

9、视图在SQL中的作用和工作方式

1.视图是什么？

概念：视图是一个虚拟表，本身是不具有数据的。虚拟表的创建连接了一个或多个数据表，不同的查询应用都可以建立在虚拟表之上。

作用：

a、视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。

b、视图作为一张虚拟表，帮我们封装了底层与数据表的接口（对SQL语句进行封装）。它相当于是一张表或多张表的数据结果集 。

c、帮我们简化复杂的 SQL 查询，帮助我们把经常查询的结果集放到虚拟表中，提升使用效率

d、用户角度来：一个视图是从一个特定的角度来查看数据库中的数据。

数据库系统内部：一个视图是由SELECT语句组成的查询定义的虚拟表。视图是由一张或多张表中的数据组成的

视图是存储在数据库中的查询的SQL 语句

2.如何创建，更新和删除视图

a、创建视图：CREATE VIEW

语法：CREATE VIEW view_name AS SELECT column1, column2 FROM table WHERE condition

// view_name：视图名称 column1, column2：筛选的列（字段）名 condition ：过滤条件

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

CREATE VIEW player_above_avg_height AS

SELECT player_id, height

FROM player

WHERE height > (SELECT AVG(height) from player)

SELECT * FROM player_above_avg_height （运行结果和上面一样）

b、嵌套视图

当我们创建好一张视图之后，还可以在它的基础上继续创建视图。

我们想在虚拟表 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)

c、修改视图：ALTER VIEW

修改视图的语法：

ALTER VIEW view_name AS

SELECT column1, column2

FROM table

WHERE condition

d、删除视图：DROP VIEW

删除视图的语法：

DROP VIEW view_name

3.如何使用视图简化 SQL 操作

视图就是对 SELECT 语句进行了封装，方便我们重用它们

a、利用视图完成复杂的连接

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

SELECT * FROM player_height_grades WHERE height >= 1.90 AND height <= 2.08

b、利用视图对数据进行格式化

输出某个格式的内容，比如我们想输出球员姓名和对应的球队，对应格式为 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

c、使用视图与计算字段

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

CREATE VIEW game_player_score AS

SELECT game_id, player_id, (shoot_hits-shoot_3_hits)2 AS shoot_2_points, shoot_3_hits3 AS shoot_3_points, shoot_p_hits AS shoot_p_points, score FROM player_score

SELECT * FROM game_player_score

5.视图和临时表

视图是虚拟表，临时表是实体表；临时表只为当前连接存在，关闭连接后就会自动释放。

10、存储过程优缺点

1、优点

a、存储过程一次编译多次使用，提升SQL执行效率，减少开发工作量

b、存储过程安全性强，设定存储过程的时候可以设置对用户的使用权限

c、减少网络传输量

2、缺点

a、可移植性差，存储过程不能跨数据库移植

b、调试困难

c、存储过程版本管理也很困难

d、不适合高并发的场景

11、事务

1.事务定义及其特性

定义：将多个数据库操作组成一个基本单元。事务由事务开始与事务结束之间执行的全部数据库操作组成。

特性：ACID

a、A，也就是原子性（Atomicity）。原子的概念就是不可分割，你可以把它理解为组成物质的基本单位，也是我们进行数据处理操作的基本单位。

b、 C，就是一致性（Consistency）。一致性指的就是数据库在进行事务操作后，会由原来的一致状态，变成另一种一致的状态。也就是说当事务提交后，或者当事务发生回滚后，数据库的完整性约束不能被破坏。

c、I，就是隔离性（Isolation）。它指的是每个事务都是彼此独立的，不会受到其他事务的执行影响。也就是说一个事务在提交之前，对其他事务都是不可见的

d、D，指的是持久性（Durability）。事务提交之后对数据的修改是持久性的，即使在系统出故障的情况下，比如系统崩溃或者存储介质发生故障，数据的修改依然是有效的。因为当事务完成，数据库的日志就会被更新，这时可以通过日志，让系统恢复到最后一次成功的更新状态

四个特性中，原子性是基础，隔离性是手段，一致性是约束条件，而持久性是我们的目的

2.事务的控制语法

a、事务的两种类型：隐式事务和显式事务

b、MySQL：隐式事务（默认） Oracle：显式事务（默认）

c、事务的常用控制语句：

i. START TRANSACTION 或者 BEGIN：显式开启一个事务。

ii. COMMIT：提交事务。当提交事务后，对数据库的修改是永久性的。

iii. ROLLBACK 或者 ROLLBACK TO [SAVEPOINT]：回滚事务。撤销正在进行的所有没有提交的修改，或者将事务回滚到某个保存点。

iv. SAVEPOINT：在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。

v. RELEASE SAVEPOINT：删除某个保存点。

vi. SET TRANSACTION，设置事务的隔离级别。

vii.set autocommit=0：将这个线程的自动提交关掉

viii.set autocommit=1：用 begin 显式启动的事务

viiii.commit work and chain：提交事务并自动启动下一个事务，省略begin

3.事务并发异常

a、脏读（dirty read）：读到了其他事务还没有提交的数据

b、不可重复读（unrepeatable read）：同一事务执行期间读到的数据内容前后不一致（不可重复读的重点是修改内容）

c、幻读（phantom read）：同一事务执行期间读到的数据个数前后不一致（幻读的重点在于新增或者删除个数）

4.事务隔离的级别

a、读未提交（READ UNCOMMITTED）：允许读到未提交的数据

b、读已提交（READ COMMITTED）: 只能读到已提交的内容

c、可重复读（REPEATABLE READ）:事务在执行期间看到的数据前后必须是一致的。（一个事务启动的时候，能够看到事务执行之前所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。）

d、可串行化（SERIALIZABLE）:对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行

e、隔离得越严实，效率就会越低。很多时候都要在二者之间寻找一个平衡点*

5.事务隔离实现

a、读未提交（READ UNCOMMITTED）：不加锁，没有隔离

b、可串行化（SERIALIZABLE）:读加共享锁，其他事务可以并发读，但是不能写；写加排它锁，其他事务不能并发写也不能并发读。

c、读已提交（READ COMMITTED）和可重复读（REPEATABLE READ）的实现：借助于MVCC

i.MVCC概念：多版本并发控制技术，通过数据行的多版本实现数据库的并发控制

ii.存储数据行的多版本（快照）：

1）事务ID：判断事务开启时间顺序

2）Undo Log（回滚日志）：记录回滚需要的操作（就是箭头）

3）历史版本数据物理上不存在，是根据当前版本和Undo Log计算出来的

iii.视图：

1）视图（view）：查询语句定义的虚拟表；调用的时候执行查询语句并生成结果

2）一致性视图（view）：RR和RC中的视图，当前事务视图数组：[当前活跃事务ID最小值，当前事务ID]

iv.解决一致性读的问题：

1）当前读（包括加锁的读取和 DML 操作）：当前读就是读取最新数据，而不是历史版本的数据。（加锁的 SELECT或者对数据进行增删改都会进行当前读）

2）快照读（普通的读操作）：快照读读取的是快照数据（即历史数据）。（不加锁的简单的 SELECT 都属于快照读）

v.并发写问题：

1）快照的方式实现了可重复读。在一个事务里非锁读请求，使用的是快照读，读取到的数据总是一致的。

2）事务中为SELECT .. FOR UPDATE或者LOCK IN SHARE MODE等这种锁读请求或对数据进行增删改（UPDATE和DELETE）使用的是当前读，读取数据行最新的数据

3）两个事务，对同一条数据做修改，操作时数据行会加锁，采用当前读

6.InnoB中的MVCC

a、InnoDB 是如何存储记录的多个版本：

i.事务版本号 :每开启一个事务，我们都会从数据库中获得一个事务 ID（也就是事务版本号）,事务ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序

ii.行记录的隐藏列 :InnoDB 的叶子段存储了数据页，数据页中保存了行记录，而在行记录中有一些重要的隐藏字段

1)db_row_id：隐藏的行 ID ,用来生成默认聚集索引。如果我们创建数据表的时候没有指定聚集索引，这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚集索引的方式可以提升数据的查找效率

2)db_trx_id：操作这个数据的事务 ID（最后一个对该数据进行插入或更新的事务ID）

3)db_roll_ptr：回滚指针，也就是指向这个记录的 Undo Log 信息。

iii.Undo Log：InnoDB 将行记录快照保存在了 Undo Log（回滚日志）里，我们可以在回滚段中找到它们

iv. 回滚指针将数据行的所有快照记录都通过链表的结构串联了起来，每个快照的记录都保存了当时的 db_trx_id，也是那个时间点操作这个数据的事务 ID。想要找历史快照，就可以通过遍历回滚指针的方式进行查找

7.Read View是如何工作的

a、MVCC 机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在 Undo Log 里。一个事务想要查询这个行记录，就需要用到 Read View

b、Read View 解决了行的可见性问题。Read View 保存了当前事务开启时所有活跃（还没有提交）的事务列表，换个角度你可以理解为 Read View保存了不应该让这个事务看到的其他的事务 ID 列表

c、Read VIew的属性：

i.trx_ids：系统当前正在活跃的事务 ID 集合

ii.low_limit_id：活跃的事务中最大的事务 ID

iii.up_limit_id：活跃的事务中最小的事务 ID。

iv.creator_trx_id：创建这个 Read View 的事务 ID

v.视图数组和(low_limit_id+1)，组成了当前事务的一致性视图（read-view）

d、Read VIew的原则：

i.事务 creator_trx_id 想要读取事务 ID 为trx_id的行记录：

ii.trx_id < up_limit_id，此行记录在活跃的事务创建前就已提交，此行记录对该事务是可见的

iii.trx_id > low_limit_id，说明该行记录还未创建，此行记录对当前事务不可见

iv.up_limit_id < trx_id < low_limit_id，说明该行记录所在的事务 trx_id 在目前creator_trx_id 这个事务创建的时候，可能还处于活跃的状态；在 trx_ids 集合中遍历，如果 trx_id 存在于 trx_ids 集合中，证明这个事务 trx_id 还处于活跃状态，不可见。如果 trx_id 不存在于 trx_ids 集合中，证明事务 trx_id 已经提交了，该行记录可见

e、系统如何通过MVCC查询记录：

i.首先获取事务自己的版本号，也就是事务 ID；

ii.获取 Read View；

iii.事务ID与 Read View 中的事务版本号进行比较；

iv.不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；

v.最后返回符合规则的数据

f、隔离级别为读已提交（Read Commit）时，一个事务中的每一次SELECT 查询都会获取一次 Read View （只读已提交的数据行，如果Read View 不同，就可能产生不可重复读（修改）或者幻读（新增和删除））

g、隔离级别为可重复读时，就避免了不可重复读，一个事务只在第一次SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View

8.一致性视图（read veiw）和视图（veiw）

a、在RR和RC中提到的的视图，和虚拟表视图不是一回事。

b、view：是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。

c、InnoDB 在实现 MVCC 时用到的一致性读视图（consistent read view）：没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

d、视图数组：保存了当前事务开启时所有活跃（还没有提交）的事务ID

e、高水位：当前事务 ID 加 1 记为高水位

f、视图数组和高水位，就组成了当前事务的一致性视图（read-view）

9.InnoDB 是如何解决幻读的

a、可重复读隔离级别下，InnoDB 可以通过 Next-Key 锁 +MVCC来解决幻读问题

b、读已提交隔离级别下，采用了 MVCC 方式也会出现幻读 事务 A 和事务 B同时开启，先在事务 A 中进行某个条件范围的查询，读取的时候采用排它锁（只锁定查询范围内的单个行记录而不锁定范围），在事务 B 中增加一条符合该条件范围的数据，并进行提交，在事务 A 中再次查询该条件范围的数据，就会发现结果集中多出一个符合条件的数据，这样就出现了幻读

c、出现幻读的原因：在读已提交的情况下，InnoDB 只采用记录锁（Record Locking）

d、InnoDB 三种行锁：

i.记录锁：对表中的记录加锁（对索引加锁），行锁。

ii.间隙锁（Gap Locking）：可以帮我们锁住一个范围（索引之间的空隙），但不包括记录本身。采用间隙锁的方式可以防止幻读情况的产生

iii.Next-Key 锁：帮我们锁住一个范围（空隙），同时锁定记录本身，相当于间隙锁 + 记录锁，可以解决幻读的问题

e、隔离级别为可重复读时，InnoDB 会采用 Next-Key 锁的机制，帮我们解决幻读问题 （事务 A 和事务 B同时开启，先在事务 A 中进行某个条件范围的查询，读取的时候采用Next-Key 锁（将 height>2.08 的范围都进行锁定，就无法插入符合这个范围的数据），事务B 会超时，无法插入该数据，事务 A 中再次查询该条件范围的数据，就不会出现幻读的情况。）

15、Python连接操作MySQL

1、使用mysql-connector驱动连接和使用数据库

a、import mysql.connector 
b、db = mysql.connector.connect(

       host="IP地址",

       user="root",

       passwd="XXX", # 写上你的数据库密码

       database='wucai', 

       auth_plugin='mysql_native_password'

)# 打开数据库连接
c、cursor = db.cursor()# 获取操作游标
d、cursor.execute("SELECT VERSION()")# 执行 SQL 语句
e、data = cursor.fetchone()# 获取一条数据
f、cursor.close()# 关闭游标
g、db.close() #关闭数据库连接

h、# 插入新球员
sql = "INSERT INTO player (team_id, player_name, height) VALUES (%s, %s, %s)"
val = (1003, " 约翰 - 科林斯 ", 2.08)
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, " 记录插入成功。")

i、# 查询身高大于等于 2.08 的球员
sql = 'SELECT player_id, player_name, height FROM player WHERE height>=2.08'
cursor.execute(sql)
data = cursor.fetchall()
for each_player in data:
  print(each_player)
  
j、# 修改球员约翰 - 科林斯
sql = 'UPDATE player SET height = %s WHERE player_name = %s'
val = (2.09, " 约翰 - 科林斯 ")
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, " 记录被修改。")

k、#删除数据
sql = 'DELETE FROM player WHERE player_name = %s'
val = (" 约翰 - 科林斯 ",)
cursor.execute(sql, val)
db.commit()
print(cursor.rowcount, " 记录删除成功。")

i、#捕捉异常信息
import traceback
try:
  sql = "INSERT INTO player (team_id, player_name, height) VALUES (%s, %s, %s)"
  val = (1003, " 约翰 - 科林斯 ", 2.08)
  cursor.execute(sql, val)
  db.commit()
  print(cursor.rowcount, " 记录插入成功。")
except Exception as e:
  # 打印异常信息
  traceback.print_exc()
  # 回滚  
  db.rollback()
finally:
  # 关闭数据库连接
  db.close()

2、采用ORM框架操作MySQL

ORM 的英文是 Object Relation Mapping，中文叫对象关系映射。它是 RDBMS 和业务实体对象之间的一个映射。ORM 的作用就是建立了对象关系映射。

1.ORM的三种框架

a、Django是 Python 的 WEB 应用开发框架，采用了 MTV 的框架模式，包括了 Model（模型），View（视图）和 Template（模版）。Model 模型只是 Django 的一部分功能，我们可以通过它来实现数据库的增删改查操作。 一个 Model 映射到一个数据表，模型的类映射到数据表，实例对象映射到数据行，属性映射到字段。 通过操作类实例对象，对数据表中的数据行进行增删改查等操作。

b、SQLALchemy，是 Python 中常用的 ORM 框架。提供了 SQL 工具包及 ORM 工具，是支持 ORM 和支持原生 SQL 两种方式的工具

c、peewee，是一个轻量级的 ORM 框架。peewee 采用了 Model类、Field 实例和 Model 实例来与数据库建立映射关系

2.使用 SQLAlchemy 来操作 MySQL

a、from sqlalchemy import create_engine #初始化数据库连接
engine = create_engine('mysql+mysqlconnector://root:admin@127.0.0.1:3306/demo')# 初始化数据库连接create_engine 的使用方法类似我们在上篇文章中提到的 mysql.connector，都需要提供数据库 + 数据库连接框架，即对应的是mysql+mysqlconnector，后面的是用户名:密码@IP地址:端口号/数据库名称。

b、#创建模型
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Float
Base = declarative_base() # 创建对象的基类
class Player(Base): # 定义 Player 对象 ##数据表
    __tablename__ = 'player'  # 表的名字
 
    # 表的结构:
    player_id = Column(Integer, primary_key=True, autoincrement=True)
    team_id = Column(Integer)
    player_name = Column(String(255))
    height = Column(Float(3,2))
    
#对数据表进行增删改查
c、增
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Float
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine('mysql+mysqlconnector://root:admin@127.0.0.1:3306/demo')
# 创建对象的基类:
Base = declarative_base()
# 定义 Player 对象:
class Player(Base):
    # 表的名字:
    __tablename__ = 'player'
    # 表的结构:
    player_id = Column(Integer, primary_key=True, autoincrement=True)
    team_id = Column(Integer)
    player_name = Column(String(255))
    height = Column(Float(3, 2))
# 创建 DBSession 类型: ##属性
DBSession = sessionmaker(bind=engine)
# 创建 session 对象:
session = DBSession()
# 创建 Player 对象:  ##数据行
new_player = Player(team_id=1007, player_name=" 约 - 科林斯 ", height=2.08)
# 添加到 session:
session.add(new_player)
# 提交即保存到数据库:
session.commit()
# 关闭 session:
session.close()
d、查
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Float
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine('mysql+mysqlconnector://root:admin@127.0.0.1:3306/demo')
# 创建对象的基类:
Base = declarative_base()
# 定义 Player 对象:
class Player(Base):
    # 表的名字:
    __tablename__ = 'player'
    # 表的结构:
    player_id = Column(Integer, primary_key=True, autoincrement=True)
    team_id = Column(Integer)
    player_name = Column(String(255))
    height = Column(Float(3, 2))
# 创建 DBSession 类型:
DBSession = sessionmaker(bind=engine)
# 创建 session 对象:
session = DBSession()  
# 增加 to_dict() 方法到 Base 类中
def to_dict(self):
    return {c.name: getattr(self, c.name, None)
            for c in self.__table__.columns}
# 将对象可以转化为 dict 类型
Base.to_dict = to_dict
# 查询身高 >=2.08 的球员有哪些
rows = session.query(Player).filter(Player.height >= 2.08).all()
print([row.to_dict() for row in rows])
session.close()

HHHHYN

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础知识

目录1、MySQL中SQL语句的执行i. 整体的 MySQL 流程：ii. SQL 语句在 MySQL 中的流程2、基础架构：SQL查询语句执行流程1.MySQL的逻辑架构图2.连接器3.查询缓存3.分析器4.优化器5.执行器3、查询优化器是如何工作的1.什么是查询优化器2.两种优化方式3.CBO是如何统计代价的4、日志系统：SQL更新语句执行流程1.SQL更新语句执行流程2.redo log（InnoDB 引擎的重做日志）3.bin
复制链接

扫一扫