02-MySQL数据存储和用户管理

原创

xurui522 2021-12-11 11:51:58 博主文章分类：MySQL ©著作权

文章标签 mysql 数据存储引擎 sql linux 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者xurui522的原创作品，请联系作者获取转载授权，否则将追究法律责任

1，CS架构及MySQL实例

2，一条SQL语句的处理过程

3，数据存储的物理及逻辑结构

4，InnoDB的段区页

5，用户管理

1，CS架构及MySQL实例

MySQL是客户端/服务端架构，支持两种连接方式，TCP/IP连接：使用IP地址加端口号的方式远程或本地登录，比如mysql -uroot -p123456 -h 192.168.0.200 -p3306；或是socket连接：仅本地登录，不需要IP地址和端口号，通过socket文件进行连接，比如mysql -uroot -p123456 -S /tmp/mysql.sock

MySQL服务器由MySQL实例对外提供服务，MySQL实例的组成：mysqld + master thread + 干活的thread + 预分配的内存，其中最重要的是mysqld这个守护进程，先有这个进程才会产生后面的组成部分

2，mysqld处理一条SQL语句的过程

结构化查询语言(SQL)，是MySQL内置的用于管理数据库的语言，常用的语句分为

DQL数据查询语言(select)

DDL数据定义语言(create、alter、drop、truncate、comment、rename)

DML数据操作语言(insert、update、delete、merge、explain plan、lock table)

DCL数据控制语言(grant、rollback、commit)

一条select语句的执行过程，mysqld分为三个层次组成：

02-MySQL数据存储和用户管理_linux

连接层：与用户或应用程序打交道。1，提供连接协议(TCP/IP，socket)，用户登录mysql在这一层完成。2，它会检查用户名、密码、IP等的合法性。3，然后开启专用的连接线程来接待用户，这个线程提供接收语句，返回数据结构的功能，使用show processlist;命令查看该线程。4，将语句交给下一层

SQL层：1，接收上一层的语句。2，语法和SQL_MODE检查，用于规范语句的准确性，5.7严格按照国际标准的SQL_MODE规则，不允许出现不合实际的执行语句。3，语义检查，检查该命令是做哪种类型的操作；权限检查，该用户能否执行该命令。4，预处理，提前评估该语句执行的代价，此时语句还未被执行。包含的操作有，解析，生成多种执行计划树(比如查找一个单词有多种不同的方法)。5，评估，优化器算法对每种执行计划进行评估。6，优化器算法选择最优的执行方法。7，按照最优的执行方法，语句真正被执行。8，MySQL提供了查询缓存，(已经执行过的语句)可以直接使用查询过的命令的执行方式，默认不开启。9，日志记录(默认未开启)，用于记录执行过的语句，包括审计日志、通用日志、二进制日志

存储引擎层：与磁盘打交道。Linux对磁盘规划出文件系统来使用磁盘，而MySQL的数据较为特殊，对磁盘使用存储引擎的方式管理磁盘上的数据。这一层根据SQL的执行结果去磁盘上找到相应的十六进制数据，再次返回给SQL层，在SQL层将数据变为二维表，再返回给连接层线程，最终显示出来

3，数据存储的物理及逻辑结构

MySQL的逻辑存储结构(类比Linux)

库 ---Linux目录

create database wordpress charset utf8mb4; ---mkdir /workpress

show databases; ---ls /

use wordpress; ---cd /wordpress

表 ---Linux的文件

列(字段)

列属性

数据行(记录) ---文件的行

表属性 ---文件属性

MySQL的物理存储结构

库：使用Linux文件系统的目录表示，在存放MySQL数据的目录下，每个库对应着一个目录，如果新建了一个目录，在数据库中show databases可以看到多了一个同名的库

02-MySQL数据存储和用户管理_sql_02

表：不同的存储引擎存储的表结构不同，以MyISAM和InnoDB为例，存储引擎类型相当于Linux的文件系统类型

查看mysql库下的user表，有user.frm(表结构——列、列属性)、user.MYD(数据记录)、user.MYI(存储索引)三个文件，user表的存储引擎为MyISAM(没有存储表属性)，较为低级，相当于ext2

02-MySQL数据存储和用户管理_数据_03

另一张表time_zone有time_zone.frm(表结构——列、列属性)和time_zone.ibd(数据记录和索引)两个文件。time_zone表使用InnoDB存储引擎，相当于xfs

02-MySQL数据存储和用户管理_linux_04

使用InnoDB存储引擎的表除了.frm和.ibd两个文件外，在存放的数据目录下还有一个ibdata1文件，专门存储数据字典信息，即表属性，相当于Linux下的inode

4，InnoDB的段区页

Linux中格式化磁盘的目的是：一开始磁盘有扇区和柱面这些分隔磁盘空间的单位，格式化为文件系统后如ext系列，会形成inode区和4K大小的数据区块，这样做是为了操作系统能够使用

MySQL使用InnoDB存储引擎存储一张表时，先分配一个.ibd文件，它相当于Linux中的磁盘文件如sdb，然后MySQL的存储引擎层对该文件进行了格式化，每个单位称为一个page(页)，大小为16K，每次为数据行分配空间时默认一次性分连续的64个page，即1M的大小空间，也被称为一个区。而为相对较小的索引数据分配时，至少分配一个页。所以MySQL的表根据其自身大小由相应数量的多个区构成，这些区被称为一个段，即一张表就是一个段，一个段由多个区构成，一个区由连续的64个页构成

5，用户管理

MySQL建立用户的目的是为了登录MySQL和管理MySQL，用户的定义使用'用户名'@'白名单'的方式，白名单即允许登录的主机来源，常见为%、localhost、127.0.0.1，或是10.0.0.%通配该网段的所有主机

创建用户并查询用户

02-MySQL数据存储和用户管理_存储引擎_05

理应是先建用户，然后再进行授权操作，但在8.0版本以前，可以直接通过授权命令创建并授权用户

02-MySQL数据存储和用户管理_mysql_06

修改用户密码

02-MySQL数据存储和用户管理_mysql_07

删除用户 02-MySQL数据存储和用户管理_数据_08

用户权限列表

ALL代表除去能给别人授权外的所有权限，即SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,RELOAD,SHUTDOWH,PROCESS,FILE,REFERENCES,INDEX,ALTER,SHOW DATABASES,SUPER,CREATE TEMPORARY TABLES,LOCK TABLES,EXECUTE,REPLICATIONS SLAVE,REPLICATION CLIENT,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,CREATE USER,EVENT,TRIGGER,CREATE TABLESPACE，加上with grant option(给其他用户授权)选项该用户就与管理员root一样，拥有所有的权限

授权命令格式为

grant 权限 on 作用范围 to 用户 identified by 密码 with grant option;，增删改查权限为SELECT,INSERT,UPDATE,DELETE，作用范围*.*表示所有库的所有表，若指定了库wordpress.*或wordpress.t1表示该库的所有表或t1表

创建用户wordpress，可以通过10网段登录，对wordpress库下所有表进行增删改查操作

02-MySQL数据存储和用户管理_mysql_09