Mysql数据库深入理解

目录

一、什么是数据库

二、Mysql基本架构图

1.Mysql客户端/服务器架构 

2.客户端与服务器的连接过程

3.服务器处理客户端请求

4.一条查询SQL执行顺序

4.1连接器

4.2查询缓存

4.3解析器

4.4执行器

4.4.1预处理阶段

4.4.2优化阶段

4.4.3执行阶段

5.一条记录如何存储（存储引擎层）

6.补充拓展——Compact 行格式

6.1varchar(n) 中 n 最大取值为多少？

6.2行溢出后，MySQL 是怎么处理的？

6.3MySQL 的 NULL 值是怎么存放的

6.4MySQL 怎么知道 varchar(n) 实际占用数据的大小

三、总结Mysql数据库常用命令行

---

一、什么是数据库

• 数据库（Database） ：存储的数据的集合，提供数据存储的服务
• 数据（Data） ：实际上指的是描述事物的符号记录
• 数据库管理系统（Database Management System，DBMS ） ： 数据库管理系统，是位于⽤户与操作系统之间的⼀层数据管理软件
• 数据库系统管理员（Database Anministrator，简称为DBA） :负责数据库创建、使⽤及维护的专⻔⼈员
• 数据库系统（Database System，DBS） ：数据库系统管理员、数据库管理系统及数据库组成整个单元

二、Mysql基本架构图

为了管理方便，我们把连接器、查询缓存、分析器、优化器、执行器这些并不涉及真实数据存储的功能划分为MySql Server的功能，把真实存取数据的功能划分为存储引擎的功能。

Server层负责建立连接，分析和执行SQL

存储引擎层负责数据的存储和提取，默认为InnoDB，默认索引类型是B+树

1.Mysql客户端/服务器架构 

MySQL的服务器程序直接和我们存储的数据打交道，可以有好多客户端程序连接到这个服务器程序，发送增删改查的请求，然后服务器响应这些请求，从而操作它所维护的数据。MySQL的每个客户端都需要提供用户名密码才能登录，登录之后才能给服务器发请求来操作某些数据。我们日常使用MySQL的情景一般是这样的：

• 启动MySQL服务器程序。
• 启动MySQL客户端程序并连接到服务器程序。
• 在客户端程序中输入一些命令语句作为请求发送到服务器程序，服务器程序收到这些请求后，会根据请求的内容来操作具体的数据并向客户端返回操作结果。

2.客户端与服务器的连接过程

运行着的服务器程序和客户端程序本质上都是计算机上的一个进程，所以客户端进程向服务器进程发送请求并得到回复的过程本质上是一个进程间通信的过程，MySQL支持下边三种客户端进程和服务器进程的通信方式：

• TCP/IP
• 命名管道和共享内存
• Unix域套接字文件

3.服务器处理客户端请求

首先MySQL是典型的c/s架构，即client/Server架构，不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送一段文本<SQL语句>，服务器进程处理后再向客户端进程发送一段文本<处理结果>

客户端可以向服务器发送增删改查各类请求，我们这里以比较复杂的查询请求为例来画个图展示一下大致的过程：

从上图中可以看到不同的存储引擎用的都是同一个server层

• 一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎
• 跨引擎相关的功能都在server层实现，不同的存储引擎共用一个server层 --> 存储引擎是可插拔式的server层

server层包含 连接器、查询缓存、分析器、优化器、执行器

server层覆盖了mysql的大多数核心服务功能，以及所有的内置函数<如日期、时间、数字和加密等函数>，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等

4.一条查询SQL执行顺序

4.1连接器

MySQL 客户端与服务器间进行 TCP 三次握手建立连接；
服务端校验客户端的用户名和密码，如果用户名或密码不对，则会报错；
如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限；

注意：定期断开长连接避免内存占用，最大连接用户限制默认151，空闲连接超过默认时间连接器自动断开

4.2查询缓存

连接成功，接收SQL语句，查看类型若为查询语句就在缓存中查找之前是否有缓存执行过这条语言的数据，如果有就直接返回，没有就继续执行，待执行完之后，查询语句与执行结果以key-value的形式加入缓存；

由于只要有一个表有更新操作，查询缓存就会被清空，对于更新比较频繁的情况下这个功能几乎无用且耗时，所以MySQL8.0开始就移除了server层的查询缓存。注意：Innodb 存储引擎中的 buffer pool并没有移除

4.3解析器

解析SQL语句，先进行词法分析，识别出关键字构建SQL语法树，然后进行语法分析，判断是否满足SQL语法，如果关键字错误，或者语法错误这里就会报错并返回。

这里只进行词法和语法分析，表或字段是否存在，这里并不判断

4.4执行器

根据解析器生成的语法树，进行以下三步走执行：
预处理阶段（prepare）–> 优化阶段（optimize ）–> 执行阶段（execute）

4.4.1预处理阶段

检查 SQL 查询语句中的表或者字段是否存在；如果不存在返回报错
将 select * 中的 * 符号，扩展为表上的所有列；

4.4.2优化阶段

基于查询成本考虑选择SQL语句的执行方案，比如在表里面有多个索引的时候使用什么索引？
最低效的就是全表扫描。优化可能为主键索引等

4.4.3执行阶段

根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

总结：

select * from test where id=10;        # 假设数据库使用的引擎为InnoDB

• 首先Mysql客户端发送查询请求到Mysql服务端，由连接器负责和客户端建立连接，验证用户身份并查询出该用户所拥有的权限；
• 访问查询缓存（是否可以命中缓存<存在缓存则直接返回，不存在则执行后续操作>，前提是开启了查询缓存）
• 分析器/解析器（对SQL进行词法分析和语法分析操作，生成一个语法树，将客户端发送过来的SQL文本解析为Mysql可以看懂的指令）
• 优化器（主要对执行的SQL进行优化，选择最优的执行计划）
• 执行器（执行时会先看用户是否对该表有执行权限，有权限才会根据执行计划去调用存储引擎提供的接口进行执行）

如果ID字段没有索引，那么执行步骤如下：

• 调用InnoDB引擎接口取这个表的第一行，在Server层判断ID是否为10，如果不是则跳过，如果是则将这行存到结果中
• 调用引擎接口取下一行记录，重复相同的判断逻辑，直到取到这个表的最后一行
• 执行器将上诉遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

注意：这里并不是在server层汇总所有的结果集然后一次性发送给客户端，其实server层发现一条符合条件的记录就会发送到本地的网络缓冲区，等缓冲区满了之后才发送给客户端。

如果ID字段存在索引，那么执行步骤是这样的：

执行逻辑与上方差不多，第一次调用的是“满足条件的第一行”这个接口，之后循环的去调用“满足条件的下一行”这个接口（这些接口都是在引擎中已经定义好的），最后将所有满足条件的行组成的记录集作为结果集返回给客户端

注意：在有些场景下，执行器调用一次，在引擎内部会扫描多行，因此引擎扫描的行数跟 rows_examined 并不是完全相同的。

5.一条记录如何存储（存储引擎层）

MySQL的数据由存储引擎实现保存在磁盘上的，InnoDB是默认也是常用的存储引擎，用SHOW VARIABLES LIKE 'datadir';可以查看MySQL数据库文件存储的目录，每创建一个数据库，这里就会多一个以该数据库名的目录，目录下的三个文件，分别为：

• 以.MYI的后缀文件存储当前数据库的索引文件
• 以.frm的后缀文件存储对应表的表结构，保存每个表的元数据信息
• 以.MYD的后缀文件存储对应表的表数据表，默认每张表存放在一个独占表空间文件

表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB存储引擎的逻辑存储结构大致如下图：

数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。
InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。默认每个页的大小为 16KB

总之，数据库表中的记录按行存储在「数据页」里。

6.补充拓展——Compact 行格式

6.1varchar(n) 中 n 最大取值为多少？

一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。

如果一张表只有一个 varchar(n) 字段，且允许为 NULL，字符集为 ascii。varchar(n) 中 n 最大取值为 65532。

计算公式：65535 - 变长字段字节数列表所占用的字节数 - NULL值列表所占用的字节数 = 65535 - 2 - 1 = 65532。

如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

6.2行溢出后，MySQL 是怎么处理的？

如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。如下图

6.3MySQL 的 NULL 值是怎么存放的

MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。

NULL值列表会占用 1 字节空间，当表中所有字段都定义成 NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间。

6.4MySQL 怎么知道 varchar(n) 实际占用数据的大小

MySQL 的 Compact 行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小。

三、总结Mysql数据库常用命令行

命令行	含义	备注
show databases；	查看数据库
show tables in database_name;	查看数据表
show tables;	查看数据表
describe table_name;	查看数据表结构
create database database_name;	创建数据库	DDL数据定义语言
create table table_name（字段）;	创建数据表
drop database database_name;	删除数据库
drop table table_name;	删除数据表
insert into table_name（字段） values （字段）;	插入数据	DML数据操纵语言
insert into table_name values（字段）;	插入数据
update table_name set 字段=指定要修改的内容	修改数据为指定值
update table_name set 字段=指定要修改的内容 where 字段=指定字段;	指定字段修改内容
delete from table_name;	删除指定数据表
delete from table_name where 字段=指定字段;	删除指定数据表的指定字段
select * from table_name;	查询指定数据表所有数据	DQL数据查询语言
select 字段1,字段2... from table_name;	查询指定数据表指定字段
select 字段1,字段2... from table_name where 字段=指定字段;	查询指定数据表指定字段匹配到指定的字段的数据信息
select 字段1,字段2... from table_name limit 指定行(可以指定从第几行显示多少行);	查询指定数据表指定行的数据
select * from table_name \G;	查询指定表以竖向显示数据
alter table table_name rename new_table_name;	修改指定数据表变更新的数据表名	DCL数据控制语言
alter table table_name add 字段(字段结构类型);	扩展表结构，新增字段
alter table table_name change 字段 新字段(字段结构类型);	修改表结构
alter table table_name drop 字段;	删除表结构
delete from table_name;	删除指定数据表，可自增（auto_increment）写入 可回滚，可带where条件判断 表结构在，表内容看删除内容 支持删除部分数据 建议用	DML数据操纵语言
truncate table table_name;	删除指定数据表，不支持自增写入 不可回滚，不可带where条件判断 表结构在，删除数据表内容 想删除表内容保留表，不和事务相关可用	DDL数据定义语言
drop from table_name;	删除指定数据表 不可回滚，不可带where条件判断 删除表结构和表内容 想删除表可以用	DDL数据定义语言
create temporary table table_name 字段;	创建临时表	临时表是临时存在的无法show tables查看，退出数据库再登入即消失
create table new_table_name like table_name; insert into new_table_name select * from table_name;	克隆表结构; 克隆表数据。	like方法（适用于数据迁移）
create table new_table_name(select * from table_name);	直接创建新表的时候克隆	适用于只备份数据
create user 'user_name'@'指定地址' identified by '指定密码';	新建用户	指定地址可以为localhost 也可以为指定的IP（IP可以指定网段，其中可以填入%来使用）
rename user 'user_name'@'指定地址' to 'new_user_name'@'新指定地址';	用户重命名
drop user 'user_name'@'指定地址';	删除用户
set password = password('new_password');	修改当前密码	如果是此命令指定，那么authentication_string字段就是显示密文
set password for 'user_name'@'指定地址' = password('new_password');	修改指定用户密码
skip_grant_tables	跳过授权表	用于忘记密码后插入到服务端的配置文件（/etc/my.cnf）
update mysql.user set authentication_string=password ('new_password') where user=user_name;	修改指定用户密码	操作该命令行后需要刷新权限（flush privileges）
grant 权限1,权限2... on database_name.table_name 'user_name'@'指定地址' identified by 'password';	将某个数据库中的数据表授予指定权限给指定用户	该用户只能通过授权获得的密码登入数据库，对指定数据库中的数据表进行指定权限的操作
show grants for 'user_name'@'指定地址';	查看用户权限
revoke 权限1,权限2... on database_name.table_name from 'user_name'@'指定地址';	撤销用户权限
create index index_name on table_name(索引列);	直接创建普通索引	普通索引
alter table table_name add index index_name(索引列)	修改表结构时创建索引
create table table_name (字段 index index_name(索引列));	创建表时创建索引
create unique index index_name on table table_name(索引列);	直接创建唯一索引	唯一索引 unique
alter table table_name add unique index_name (索引列);	修改表结构创建唯一索引
create table table_name (字段,unique index index_name(索引列));	创建表时指定唯一索引
create table table_name (字段,primary key(索引列));	创建表时创建主键索引	主键索引 primary key
alter table table_name add primary key(索引列));	修改表时指定主键索引
create table table_name (字段,index index_name(索引列))	创建表时指定组合索引	组合索引 （可以单列索引，可以多列索引）
create fulltext index index_name on table_name(索引列);	直接创建全文索引	全文索引 fulltext 可作用在char varchar text类型列上
alter table table_name add fulltext index_name(索引列);	修改表方式创建全文索引
create table table_name(字段,fulltext index_name(索引列));	创建表时指定全文索引
show index from table_name;	查看索引	查看索引
show keys from table_name;	查看索引
drop index index_name on table_name	直接删除索引	删除索引
alter table table_name drop index index_name;	修改表方式删除索引
alter table table_name drop primary key;	删除主键索引
select @@global.tx_isolation;	查看全局事务隔离级别
show global variables like '%isolation%';
select @@session.tx_isolation;	查看当前会话隔离级别
show session variables like '%isolation%';
begin;	开启事务
start transaction
commit;	提交事务
commit work;
rollback;	回滚至最原始未结束的事务	修改表结构后无法回滚事务
rollback work;
rollback to savepoint S1/S2...	回滚至事务中的标记点
savepoint S1	事务中增加标记点
set autocommit=0或1	修改是否提交事务	Mysql数据库默认的是自动提交事务，默认为1；如果没有开启自动提交，当前会话所有操作直到输入rollback或者commit才算结束
show variables like 'autocommit'	查看Mysql中的AUTOCOMMIT值
show engines;	查看数据库支持的存储引擎
show table status from database_name where name='table_name';	查看指定数据表的引擎
alter table table_name engine=引擎;	直接修改存储引擎
default-storage-engine=引擎;	修改配置文件修改引擎	配置文件位置在/etc/my.cnf，修改完配置文件后需要重启MySQL数据库，一般不建议使用
create table table_name(字段1,字段2...)engine=引擎;	创建表时指定引擎