【数据库学习笔记】进阶篇

存储引擎

MySQL体系结构

• 连接层：接收客户端的连接，完成连接的处理、认证授权、校验客户端权限等
• 服务层：完成绝大部分的核心工作，跨存储引擎的工作也在服务层完成
• 引擎层：不同的存储引擎，索引结构是不一样的，控制数据库数据的存取和组织结构
• 存储层：实际存储

MySQL是客户端／服务器架构

1. macOS操作系统上的默认安装目录：/usr/local/mysql/
   　　在MySQL的安装目录下有一个bin目录，这个目录下存放着许多可执行文件。
2. 将该bin目录的路径加入到环境变量PATH中
   　　如果我们觉得每次执行一个文件都要输入一串长长的路径名太麻烦的话，可以把该bin目录所在的路径添加到环境变量PATH中。
3. 启动MySQL服务器程序
   在类UNIX系统中用来启动MySQL服务器程序的可执行文件有很多，大多在MySQL安装目录的bin目录下

mysqld
# mysqld这个可执行文件就代表着MySQL服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。

mysqld_safe
# mysqld_safe是一个启动脚本，它会间接的调用mysqld，而且还顺便启动了另外一个监控进程，这个监控进程在服务器进程挂了的时候，可以帮助重启它。另外，使用mysqld_safe启动服务器程序时，它会将服务器程序的出错信息和其他诊断信息重定向到某个文件中，产生出错日志，这样可以方便我们找出发生错误的原因。

mysql.server
# mysql.server也是一个启动脚本，它会间接的调用mysqld_safe，在调用mysql.server时在后边指定start参数就可以启动服务器程序了，就像这样：
mysql.server start
# 需要注意的是，这个 mysql.server 文件其实是一个链接文件，它的实际文件是 ../support-files/mysql.server。我使用的macOS操作系统会帮我们在bin目录下自动创建一个指向实际文件的链接文件，如果你的操作系统没有帮你自动创建这个链接文件，那就可以自己创建一个
# 另外，我们还可以使用mysql.server命令来关闭正在运行的服务器程序，只要把start参数换成stop就好了： 
mysql.server stop

mysqld_multi
# 其实我们一台计算机上也可以运行多个服务器实例，也就是运行多个MySQL服务器进程。mysql_multi可执行文件可以对每一个服务器进程的启动或停止进行监控。这个命令的使用相对比较复杂 。

4. 启动MySQL客户端程序
   mysql -h主机名 -P端口 -u用户名 -p密码
   断开连接 quit exit \q

一个select语句处理过程会涉及哪些部分

1. 连接管理（连接层）
   客户端进程采用TCP/IP来与远程服务器进程建立连接，每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程来专门处理与这个客户端的交互，当该客户端退出时会与服务器断开连接，服务器并不会立即把与该客户端交互的线程销毁掉，而是把它缓存起来，在另一个新的客户端再进行连接时，把这个缓存的线程分配给该新客户端。MySQL服务器会为每一个连接进来的客户端分配一个线程，但是线程分配的太多了会严重影响系统性能，所以我们也需要限制一下可以同时连接到服务器的客户端数量。
   当连接建立后，与该客户端关联的服务器线程会一直等待客户端发送过来的请求，MySQL服务器接收到的请求只是一个文本消息，该文本消息还要经过各种处理。
2. 解析与优化（服务层）
   a. 查询缓存
   　　虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除
   b. 语法解析
   　　MySQL服务器程序首先要对这段文本做分析，判断请求的语法是否正确，然后从文本中将要查询的表、各种查询条件都提取出来放到MySQL服务器内部使用的一些数据结构上来。涉及词法解析、语法分析、语义分析等阶段。
   c. 查询优化
   　　语法解析之后，服务器程序获得到了需要的信息，比如要查询的列是哪些，表是哪个，搜索条件是什么等等。我们写的MySQL语句执行起来效率可能并不是很高，MySQL的优化程序会对我们的语句做一些优化。优化的结果就是生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的。我们可以使用EXPLAIN语句来查看某个语句的执行计划 。
3. 存储引擎（引擎层）
   　　截止到服务器程序完成了查询优化为止，还没有真正的去访问真实的数据表，MySQL服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里。我们知道表是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。为了实现不同的功能，MySQL提供了各式各样的存储引擎，不同存储引擎管理的表具体的存储结构可能不同，采用的存取算法也可能不同。

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。各种不同的存储引擎向上边的MySQL server层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像"读取索引第一条内容"、“读取索引下一条内容”、"插入记录"等等。
所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。最常用的就是InnoDB和MyISAM 。

存储引擎简介

存储引擎决定了存储数据、建立索引、更新/查询数据等实现。
存储引擎是基于表的，而不是基于数据库的。因此同一数据库下的多个表可以用不同的存储引擎。（存储引擎又被称为表类型）

默认存储引擎：InnoDB

存储引擎相关操作

1. 查看当前服务器程序支持的存储引擎
   SHOW ENGINES;

• InnoDB：默认存储引擎
• MyISAM：原默认存储引擎
• MEMORY：内存，暂时存储
  
• Support列表示该存储引擎是否可用，DEFAULT值代表是当前服务器程序的默认存储引擎。
• Comment列是对存储引擎的一个描述。Transactions列代表该存储引擎是否支持事务处理。
• XA列代表着该存储引擎是否支持分布式事务。
• Savepoints代表着该存储引擎是否支持部分事务回滚。

2. 创建表时指定存储引擎

CREATE TABLE 表名(
    建表语句;
) ENGINE = 存储引擎名称;

3. 修改表的存储引擎

ALTER TABLE 表名 ENGINE = 存储引擎名称;

4. 查看表结构

SHOW CREATE TABLE engine_demo_table ;

存储引擎特点

1. InnoDB
   
   
   在InnoDB中，page是磁盘操作的最小单元

mac下查找mysql的数据文件存储位置
sudo ls /usr/local/mysql/data/itcast
itcast: 数据库名称

使用命令查找数据库文件存储路径
mysql> SHOW VARIABLES LIKE 'datadir';

如何查看ibd文件中的表结构？
ibd2sdi 表名得到json结构文件

2. MyISAM
   
3. Memory
   

三种存储引擎区别

存储引擎的选择

MyISAM➡️mangoDB
MEMORY➡️Redis

小结

索引

索引概述

索引是帮助MySQL高效获取数据的数据结构

• 优缺点
  
• 磁盘便宜
• 增删改操作较少，查操作较多

索引结构

B树

B树可视化

B+树索引

MySQL在普通B+树的基础上加入了一个链表指针

hash索引

小结

索引分类

• 添加了唯一约束的字段自动创建唯一索引
  
  例子：
• 聚集索引：索引中数据存储的是整一行的数据
• 二级索引：索引中存储的是对应的主键
  
  回表查询：现根据select字段的二级索引找到对应的主键值，再根据主键值的聚集索引找到行数据
  

3层的B+树能存储的数据量就已经很大了

索引语法

创建索引

create [unique/fulltext] index index_name on table_name (index_col_name, ...);

-一个索引可以关联多个字段：联合索引/组合索引；只关联一个字段：单列索引

查看索引

删除索引

SQL性能分析

SQL的执行频率

为SQL的优化提供理论支撑（是以查询为主的数据库？还是以增删为主的数据库？）

慢查询日志

定位哪些sql语句执行效率比较低、需要进行优化

profile详情

explain执行计划

1. id
   
2. type ：NULL表示不访问任何表；const根据主键或者唯一性索引；ref表示根据非唯一性索引
3. filter：越大越好
   

索引使用

• 验证索引对查询效率的提升
  

最左前缀法则（联合索引）

注意：与select查询的字段顺序无关

范围查询

注意：为了避免后续的索引查询失效，尽量使用≤≥，而不是<>

索引失效的情况

注意：此时检索的条目占数据总量的大多数。

SQL提示

覆盖索引

• NULL查询就需要回表

using index condition需要回表查询
避免使用select *，因为很容易遇到回表查询

前缀索引

针对字符串等较长的索引字段，可以借助前缀索引降低索引体积、提高效率

单列索引与联合索引

创建联合索引时，需要考虑索引的顺序

索引设计原则

小结

SQL优化

插入数据

1. insert优化
   
2. 大批量插入数据
   

• --local-infile表示可以从本地加载数据

主键优化

• 主键设计原则
  

order by优化

order by小结

group by优化

limit分页查询优化

在大数据量情况下进行分页查询时，查询页数越大，耗时越多
解决思路：覆盖查询 + 子查询

count优化

• count函数的用法
  
  

update优化

避免行锁升级为表锁

小结

MySQL数据库的存储对象

视图

视图简介

视图语法

• 视图的检查项：cascaded（级联）
  
  cascaded之前的插入时都检查
• 视图的检查项：local（本地）
  

视图更新

视图的作用

存储过程

介绍

特点

1. 封装，复用
2. 可以接收参数，也可以返回数据
3. 减少网络交互，效率提升

基本语法

变量

1. 系统变量
   
2. 用户自定义变量
   
3. 局部变量
   

if判断

参数传递

示例：

case流程控制

循环

1. while循环
   
2. repeat循环（相当于do while循环）
   
3. loop循环
   
   leave相当于break；iterate相当于continue
   示例：
   

游标（光标）

示例：

游标需要在普通变量之后声明

条件处理程序

提示满足02000时，执行退出操作，并关闭游标

错误提示码的官方文档

存储函数

函数特性在MySQL8.0版本后是必选参数
存储函数是特殊的存储过程，所有的存储函数都可以用存储过程来替代

触发器

• 简介
  

通过触发器，可以在数据库端实现数据库的完整性保障，例如变更日志等。目前MySQL支持行级触发器，不支持语句级触发器。

• 触发器语法
  
  

视图/存储过程/触发器小结

锁

🏴‍☠️锁和事务密切相关，如果没有并行的事务，也就不会有锁的冲突

概述

锁的分类：

• 全局锁
• 表级锁
• 行级锁

全局锁

加锁后：

加锁语法：全局备份示例

全局锁的特点

表级锁

1. 表锁

表锁的分类：

1. 表共享读锁（read lock）：不会阻塞其他客户端的读，但会阻塞其他客户端的写操作
2. 表独占写锁（write lock）：既会阻塞读，也会阻塞写
   

2. 元数据锁（meta data lock, MDL）

如果某张表中存在未提交的事务，就不能修改表结构
自动控制，不需要命令

3. 意向锁

意向锁主要为了解决行锁与表锁的冲突问题

加表锁时不必逐行检查行级锁，只需要根据意向锁的类型判断

行级锁

在InnoDB中，行级锁是针对索引加的，而不是针对每一数据项加的

InnoDB默认的是next-key锁，锁住当前数据和该数据之前的间隙。但是很多情况下会退化为行锁和间隙锁。
临键锁 = 行锁 + 间隙锁

行锁

增删改查的加锁情况：

行锁示例：

间隙锁

临键锁

锁小结

InnoDB存储引擎

逻辑存储结构

• 数据库ibd文件存储路径：/usr/local/mysql/data
  
  

架构

内存结构

1. 缓冲池
   
2. 更改缓冲区
   
3. 自适应哈希
   InnoDB默认不支持hash索引，支持B+树索引、
   
4. 日志缓冲区
   

磁盘结构

1. 系统表空间：如果2关闭，每张表的独立表空间也会在此处存放
2. 独立表空间：存放每张表的独立表空间：开关默认开启
   
3. 通用表空间：需要create table space手动创建
4. 撤销表空间：
5. 临时表空间：
   
6. 双写缓冲区
7. 重做日志：用于异常时的数据恢复，维护事务的持久性。事务提交后就可以删除。
   

后台线程

作用：将InnoDB存储引擎的缓冲数据刷新到磁盘中

事务的实现原理

1. 重做日志redo log——持久性
   在从内存到磁盘数据刷新时出现错误，则使用redo log进行错误回滚
   
2. 回滚日志undo log——原子性

• 事务执行失败时，需要依赖undo log进行回滚
• 支持MVCC
  

MVCC

MVCC：多版本并发控制

基本概念

1. 当前读
2. 快照读
3. MVCC
   

实现原理

1. 隐藏字段
   
2. undo log日志
   
   undo log版本链
   
3. read view读视图
   

例子（读已提交RC）

例子（可重复读RR）

MVCC小结

小结

MySQL管理

系统数据库

mysql安装后，自带四个系统数据库：

常用工具

1. mysql
   
2. mysqladmin
   
3. mysqlbinlog
   
4. mysqlshow
   
5. mysqldump：用于数据迁移和备份
   
6. mysqlimport/source：导入文件
   

小结

进阶篇小结

小技巧

把变形的列表变为行展示

\G