MySQL进阶：三、用户与权限管理 & 逻辑架构 & 存储引擎

最新推荐文章于 2023-06-08 09:49:55 发布
最新推荐文章于 2023-06-08 09:49:55 发布
阅读量196 收藏
点赞数
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44606630/article/details/124545797
版权

用户与权限管理

1. 用户管理
1.1 登录命令详解
mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"
  • -h 后面接主机名或主机IP
  • -P 后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL的默认服务端口是3306，不指定该参数使用默认端口。
  • -u 后接用户名
  • -p 会提示输入密码，也可以直接后接密码(不推荐)
  • DatabaseName 指明登录的数据库。不指定该参数需要登录后使用use选择数据库。
  • -e 后接SQL语句。这种方式执行该语句后自动退出SQL。

例如

root@e6c4aa557f23:/# mysql -uroot -pPwd -hlocalhost -P3306 mysql -e "select host,user from user"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
root@e6c4aa557f23:/#
1.2 创建用户

创建用户的基本语法形式如下：

CREATE USER 用户名 IDENTIFIED BY 密码 [, 用户名 IDENTIFIED BY 密码]
  • 用户名参数表示新建用户的账户，包括用户(user)和host(主机名)。
  • 可以在创建时不指定密码，这样的用户登录时无需输入密码(不推荐)。
  • 支持同时创建多个用户。

例如

create user 'chenx'@'%' identified by 'xing';
# @ 后的是IP限制，%表示允许任何IP登录。
CREATE USER 'liusheng'@'localhost' IDENTIFIED BY '123456';
1.3 修改用户

修改用户名：

update mysql.user set user='chen' where user='chenx';
# 刷新权限
flush privileges;
1.4 删除用户
Drop删除(推荐)

当前用户拥有drop user权限时，可以使用drop删除用户

DROP USER user, ...

例如

Drop user chenx;

mysql> select host, user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

可以使用DELETE语句删除mysql数据库中user表里的用户，不推荐使用，会出现残留信息的问题。

1.5 设置当前用户的密码
方式一
# MySQL5.7可用，MySQL8.0移除
SET PASSWORD = PASSWORD("123456")
方式二(推荐)

  1. 使用ALTER USER命令修改当前用户密码，语法如下：

    ALTER USER USER() IDENTIFIED BY 'new_pwd';

  2. 使用SET语句修改当前用户密码

    SET PASSWORD='new_pwd'

密码会经过加密后存入mysql库的user表中，对应的字段为authentication_string。

1. 6修改其他用户密码
  1. 使用ALTER语句修改普通用户的密码
ALTER USER user IDENTIFIED BY 'new_pwd', ...;
  1. 使用SET命令来修改普通用户的密码
SET PASSWORD FOR 'user'@'host'='new_pwd';

可以使用update进行操作，不推荐。

1.7 MySQL8的密码管理

目前使用5.7，此处仅作记录。

  1. 密码过期策略

    • 在MySQL中，数据库管理员可以 手动设置 账号密码过期，也可以建立一个 自动 密码过期策略。
    • 过期策略可以是 全局的 ，也可以为 每个账号 设置单独的过期策略。
    ALTER USER user PASSWORD EXPIRE;

    方式①：使用SQL语句更改该变量的值并持久化

    SET PERSIST default_password_lifetime = 180; # 建立全局策略，设置密码每隔180天过期

    方式②：配置文件my.cnf中进行维护

    [mysqld]
default_password_lifetime=180 #建立全局策略，设置密码每隔180天过期

    手动设置指定时间过期方式2：单独设置

    每个账号既可延用全局密码过期策略，也可单独设置策略。在 CREATE USER 和 ALTER USER 语句上加 入 PASSWORD EXPIRE 选项可实现单独设置策略。下面是一些语句示例。

    #设置kangshifu账号密码每90天过期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
#设置密码永不过期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
#延用全局密码过期策略：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;

  2. 密码重用策略

  • 手动设置密码重用方式1：全局

    • 方式①：使用SQL
    SET PERSIST password_history = 6; #设置不能选择最近使用过的6个密码
SET PERSIST password_reuse_interval = 365; #设置不能选择最近一年内的密码
    • 方式②：my.cnf配置文件
    [mysqld]
password_history=6
password_reuse_interval=365

  • 手动设置密码重用方式2：单独设置

    #不能使用最近5个密码：
CREATE USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;
ALTER USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;
#不能使用最近365天内的密码：
CREATE USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
#既不能使用最近5个密码，也不能使用365天内的密码
CREATE USER 'kangshifu'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'kangshifu'@'localhost
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;
  1. mysql.user表以host 和 user做复合主键，所以存在同名不同host用户。
2. 权限管理
2.1 权限列表
show privileges;
  • CREATE & DROP 可以创建新的数据库和表，或删除已有数据库和表。
  • SELECT INSERT UPDATE & DELETE 允许在一个数据库现有的表上进行操作。
    • select权限只有在他们真正从一个表中检索行时才被用到。
  • INDEX 允许创建或删除索引，INDEX适用于已有的表。如果同时具有CREATE权限，就可以在建表时包括索引定义。
  • CREATE ROUTINE 用来创建保存的程序，ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序。
  • GRANT权限 允许授权给其他用户，可用于数据库、表和保存的程序。
  • FILE权限 使用户可以使用LOAD DATA INFILE和SELECT … INTO OUTFILE语句读 / 写服务器上的文件，任何被授予FILE权限的用户都能读/写服务器上的任何文件(说明用户可以读任何数据库目录下的文件)。
2.2 授权原则

权限控制主要是出于安全因素，因此需要遵循以下几个经验原则：

  1. 只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可 以了，不要给用户赋予update、insert或者delete权限。
  2. 创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
  3. 为每个用户设置满足密码复杂度的密码。
  4. 定期清理不需要的用户 ，回收权限或者删除用户。
2.3 授权方式

授权方式有两种，分别是直接赋权和角色赋权。角色为MySQL8引入，此文档仅作记录，当前未测试。

授权命令：

GRANT 权限1,权限2,... ON 数据库名.表名 TO 用户@主机 [IDENTIFIED BY PWD];

若该用户不存在，会直接新建一个用户。

例如

  • 给chenx用户赋予yshe库中所有表的增删改查权限
GRANT SELECT,INSERT,DELETE,UPDATE ON yshe.* TO 'chenx'@'%';
# 可以使用*.*表示全库全表
  • 授予lius用户全库全表权限，并新建该用户。这里不会包括GRANT权限
grant all privileges on *.* to 'lius'@'%' identified by 'xinru';

# 执行给出警告， 说明会直接创建不存在的用户，但是不建议使用
# Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement.
2.4 查看权限
  • 查看当前用户权限
SHOW GRANTS;
# 或
SHOW GRANTS FOR CURRENT_USER;
# 或
SHOW GRANTS FOR CURRENT_USER();

# root用户的权限
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
# 刚才赋予全库全表all privileges的用户lius的权限
mysql> show grants;
+-------------------------------------------+
| Grants for lius@%                         |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lius'@'%' |
+-------------------------------------------+

# 可以看到，用户lius的权限较root仍然少一些。
  • 查看其他用户的权限
mysql> show grants for 'root'@'localhost';
2.5 权限回收

==收回用户不必要的权限可以在一定程度上保证系统的安全性。==MySQL中使用 REVOKE语句 取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从 db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中 的账户记录使用DROP USER语句）。

  • 权限回收命令
REVOKE 权限1,权限2,... ON 数据库名.表名 FROM 用户@主机

例如

#收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'lius'@'%';
#收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM 'lius'@'%';

权限被回收的用户需要重新登录，回收操作才生效。

3. 权限表
3.1 user表

user表示mysql库中最重要的权限表，用于记录用户账号和权限信息，5.7版本中一共有45个字段。

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

  • 这些字段可分为四类：范围列(用户列)， 权限列，安全列，资源控制列

    • 范围列

      host：表示连接类型

      • %表示所有远程通过TCP方式的连接
      • IP地址：指定IP地址
      • 机器名：指定网络中的机器名
      • ::1： IPv6的本地ip地址，等同于IPv4的127.0.0.1
      • localhost：本地方式通过命令行方式连接

      user：表示用户名。

      password： 表示密码。

    • 权限列

      • Grant_priv: 表示是否拥有GRANT权限。
      • Shutdown_priv: 表示是否有停止MySQL服务的权限。
      • Super_priv: 表示是否拥有超级权限
      • Execute_priv: 表示是否拥有EXCUTE权限，即是否可以执行存储过程和存储函数
      • Select_priv, Insert_priv等: 该用户所拥有的权限。

    • 安全列

      ​ 安全列只有6个字段，其中两个是ssl相关的（ssl_type、ssl_cipher），用于 加密 ；两个是x509 相关的（x509_issuer、x509_subject），用于 标识用户 ；另外两个Plugin字段用于验证用户身份的插件， 该字段不能为空。如果该字段为空，服务器就使用内建授权验证机制验证用户身份。

    • 资源控制列

      资源控制列的字段用来 限制用户使用的资源 ，包含4个字段，分别为：

      • max_questions: 用户每小时允许执行的查询操作次数。
      • max_updates：用户每小时允许执行的更新操作次数。
      • max_connections：用户每小时允许执行的连接操作次数
      • max_user_connections：用户允许同时建立的连接次数
3.2 db表

表结构如下：

mysql> describe mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(32)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
  1. 用户列

​ db表用户列有3个字段，分别是Host、User、Db。这3个字段分别表示主机名、用户名和数据库 名。表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。

  1. 权限列

​ Create_routine_priv和Alter_routine_priv这两个字段决定用户是否具有创建和修改存储过程的权限。

3.3 tables_priv表和columns_priv表

tables_priv表用来对表设置操作权限，columns_priv表用来对表的 某一列设置权限 。tables_priv表和 columns_priv表的结构可以通过desc命令查看。

tables_priv表有8个字段，分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和 Column_priv，各个字段说明如下：

  • Host 、 Db 、 User 和 Table_name 四个字段分别表示主机名、数据库名、用户名和表名。
  • Grantor表示修改该记录的用户。
  • Timestamp表示修改该记录的时间。
  • Table_priv 表示对象的操作权限。包括Select、Insert、Update、Delete、Create、Drop、Grant、 References、Index和Alter。
  • Column_priv字段表示对表中的列的操作权限，包括Select、Insert、Update和References。
3.4 procs_priv表

procs_priv表可以对存储过程和存储函数设置操作权限，表结构如图：

4. 访问控制
4.1 连接核实阶段

​ 当用户试图连接MySQL服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确 定接受或者拒绝连接。即客户端用户会在连接请求中提供用户名、主机地址、用户密码，MySQL服务器 接收到用户请求后，会使用user表中的host、user和authentication_string这3个字段匹配客户端提供信 息。

​ 服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的密码时才接受 连接。如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待 用户请求。

4.2 请求核实阶段

​ 一旦建立了连接，服务器就进入了访问控制的阶段2，也就是请求核实阶段。对此连接上进来的每个请 求，服务器检查该请求要执行什么操作、是否有足够的权限来执行它，这正是需要授权表中的权限列发 挥作用的地方。这些权限可以来自user、db、table_priv和column_priv表。

​ 确认权限时，MySQL首先检查user表，如果指定的权限没有在user表中被授予，那么MySQL就会继续检查db表，db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指 定数据库的所有表中的数据；如果在该层级没有找到限定的权限，则MySQL继续 检查tables_priv表 以 及columns_priv表，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将 返回错误信息 ，用户请求的操作不能执行，操作失败。

提示： MySQL通过向下层级的顺序（从user表到columns_priv表）检查权限表，但并不是所有的权 限都要执行该过程。例如，一个用户登录到MySQL服务器之后只执行对MySQL的管理操作，此时只 涉及管理权限，因此MySQL只检查user表。另外，如果请求的权限操作不被允许，MySQL也不会继 续检查下一层级的表。

5. 角色管理

角色管理在MySQL中自8.0版本才引入，此处仅作记录。

引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

5.1 创建角色
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

角色名称的命名规则和用户名类似。如果 host_name省略，默认为% ，role_name不可省略，不可为空。

5.2 给角色赋权

新创建的角色默认没有权限，授权语法如下：

GRANT 权限名1，权限名2，... ON table_name TO 'role_name'[@'host_name'];

查看权限名称的命令

SHOW PRIVILEGES\G;
5.3 查看角色权限
SHOW GRANTS FOR role_name;

只要你创建了一个角色，系统就会自动给你一个“ USAGE ”权限，意思是连接登录数据库的权限。

5.4 回收权限

撤销权限语法如下

REVOKE 权限1，权限2，... ON tablename FROM 'role_name';

例如

REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write';
5.5 删除权限
DROP ROLE role [,role2]...
5.6 给用户赋予角色

角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。

GRANT role [,role2,...] TO user [,user2,...]
5.7 激活角色
  • 方式一： 使用set default role 命令激活角色
SET DEFAULT ROLE ALL TO 'chenx'@'localhost';
  • 方式二： 将activate_all_roles_on_login设置为ON
SET GLOBAL activate_all_roles_on_login=ON;

这条 SQL 语句的意思是，对所有角色永久激活。运行这条语句之后，用户才真正拥有了赋予角色的所有权限。

5.8 撤销用户的角色
REVOKE role FROM user;
5.9 设置强制角色(mandatory role)
  • 方式一： 配置文件设置
[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'
  • 方式二： 运行时设置
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后仍然
有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后失效

逻辑架构

1. 逻辑架构概述
1.1 服务器处理客户端请求

请求图如下所示

image-20220501223556368

具体展开图如下：

image-20220501223643588

1.2 Connectors

​ 指的是不同语言与SQL的交互。MySQL是一个网络程序，在TCP之上定义了自己的应用层协议。所以要使用MySQL，可以通过编写代码，和MySQL Server建立TCP连接，之后按协议规则进行交互。或者通过调用SDK，例如Native C API、JDBC等各个语言提供的MySQL Connector。但本质上都是在TCP连接上通过MySQL协议与其进行交互。

结构可以分为下面的三层

1.3 连接层

客户端访问MySQL服务器第一件事是建立TCP连接。

建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。

  • 用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行

  • 用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依 赖于此时读到的权限

TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互，所以还会有个线程池，每一个连接从线程池中获取线程，节省创建和销毁线程的开销。

1.4 服务层

  • SQL Interface：SQL接口

    • 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQL Interface
    • MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定 义函数等多种SQL语言接口

  • Parser：解析器

    • 在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构 传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。
    • 在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还 会对SQl查询进行语法上的优化，进行查询重写。

  • Optimizer: 查询优化器

    • SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个 执行计划。

    • 这个执行计划表明应该 使用哪些索引 进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。

    • 它使用“ 选取-投影-连接 ”策略进行查询。例如：

      SELECT id,name FROM student WHERE gender = '女';

      这个SELECT查询先根据WHERE语句进行选取，而不是将表全部查询出来以后再进行gender过滤。 这个SELECT查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。

  • Caches & Buffers： 查询缓存组件

    • MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。
    • 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
    • 这个查询缓存可以在不同客户端之间共享 。
    • 从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。因为这种缓存的命中率很低。
1.5 引擎层

插件式存储引擎层（ Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别 维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样 我们可以根据自己的实际需要进行选取。

MySQL5.7.36支持的引擎如下：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
1.6 存储层

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在文件系统上，以文件的方式存在，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用 DAS、NAS、SAN等各种存储系统。

1.7 小结

架构简图如下：

image-20220501233016168

简化为三层结构：

  1. 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
  2. SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
  3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。
2. SQL执行流程
2.1 MySQL中的SQL执行流程图

image

MySQL的执行流程

  1. 查询缓存： Server如果在查询缓存中找到这条SQL，就会直接返回给客户端；如果没有找到，就进入到解析器阶段。

​ 关于为什么查询缓存效率低，并且在8.0版本被移除

​ 因为MySQL查询缓存缓存的是对应的结果，而不是执行计划，所以查询匹配的鲁棒性(即健壮性)大大降低，只有相同的查询才会命中，即使只是有空格的区别，也会导致无法命中。而类似NOW函数等，每次执行的结果都是不同的，极易导致无法命中的情况。

​ 同时，因为存在缓存失效的情况。只要表结构或表数据被修改，那么该表的所有高速缓存都会被从缓存中删除。对于更新操作频繁的数据库，命中率会非常低。

  1. 解析器： 解析器会对SQL语句进行语法分析，语义分析。

image

​ 解析器(分析器)先做词法分析，即分析SQL语句的内容，识别其字符串分别是什么，代表什么。MySQL从输入的select关键字分析出这是一条查询语句，同时需要能够把字符串t识别为表名，id识别为列名。

​ 接着，解析器会做语法分析。根据词法分析的结果，语法分析器会根据语法规则，判断输入的SQL语句是否满足MySQL语法。

​ 如果语法没有问题，就会生成类似如下的语法树。

image

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

​ 例如

select * from test1 join test2 using(ID)
where test1.name='zhangwei' and test2.name='mysql高级课程';

方案1：可以先从表 test1 里面取出 name='zhangwei'的记录的 ID 值，再根据 ID 值关联到表 test2，再判
断 test2 里面 name的值是否等于 'mysql高级课程'。
方案2：可以先从表 test2 里面取出 name='mysql高级课程' 的记录的 ID 值，再根据 ID 值关联到 test1，
再判断 test1 里面 name的值是否等于 zhangwei。
这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。
关于优化器如何选择索引，可见后续章节。

查询优化器，可以分为逻辑查询优化阶段和物理查询优化阶段。

  1. 执行器： 截止到优化器，都只是产出了执行计划，没有真正读写表。读写操作由执行器执行。

image

在执行器执行之前，需要判断该用户权限，如果没有，会返回权限错误。否则就可以继续执行SQL查询并返回查询结果。如果在5.7版本中开启了查询缓存，那么同时还会将查询结果缓存到查询缓存中。

例如

select * from yshe where id=1;

如果ID没有索引，执行器执行流程如下

# 调用InnoDB的引擎接口取这个表的第一行，判断ID值是否符合要求(为1)，不是则跳过，是则加入结果集；调用引擎接口取下一行，重复上面的逻辑，直到最后一行。
# 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

SQL 语句在 MySQL 中的流程是：SQL语句→查询缓存→解析器→优化器→执行器 。

2.2 查看执行过程
  1. 到配置文件开启查询缓存并重启服务

​ 在 /etc/my.cnf 中新增一行，并重启mysql服务，本机测试失败。添加后仍未开启。

query_cache_type=1
  1. 查看并打开profiling
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
或
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
# 0 和 OFF都表示关闭

mysql> set profiling=1;
+---------+------+----------------------------------------------------------------------+
| Level   | Code | Message                                                              |
+---------+------+----------------------------------------------------------------------+
| Warning | 1287 | '@@profiling' is deprecated and will be removed in a future release. |
+---------+------+----------------------------------------------------------------------+
# 它说以后将要移除@@profiling了
  1. 重复执行相同SQL查询
mysql> select * from test01.human;
  1. 查看profiling
mysql> show profiles;
+----------+------------+----------------------------+
| Query_ID | Duration   | Query                      |
+----------+------------+----------------------------+
|        1 | 0.00009625 | show warnings              |
|        2 | 0.00041400 | show databases             |
|        3 | 0.00007200 | show tables for test01     |
|        4 | 0.00042125 | show tables from test01    |
|        5 | 0.00089800 | desc test01.human          |
|        6 | 0.00032575 | select * from test01.human |
|        7 | 0.00041625 | select * from test01.human |
|        8 | 0.00006400 | show profiling             |
|        9 | 0.00006575 | show profilings            |
+----------+------------+----------------------------+
  1. 查看第一次查询和第二次查询的内容

这里因为没能开启缓存，仅作记录。
据描述，开启后，一模一样的查询语句，除第一次执行外，执行流程少了很多步骤。
MySQL8中已经移除缓存。

2.3 SQL语法顺序

底层的优化器会分析不同的执行顺序产生的性能消耗，动态的调整执行顺序。

例如：查询每个部门年龄高于20岁的人数且高于20岁人数不能少于2人，显示人数最多的第一名部门信息 下面是经常出现的查询顺序：

image

3. 数据库缓冲池

InnoDB存储引擎以页为单位管理存储空间，增删改查操作本质上都是在访问页面（包括读页面、写页面、创建页面等）。磁盘I/O需要的时间开销很多，在内存中操作，效率就会高很多，DBMS会申请占用内容来作为数据缓冲池，真正访问页面前，会把磁盘上的页面缓存到内存中的Buffer Pool中。对提升性能起到极大作用。

3.1 逻辑结构

缓冲池与查询缓存不同，查询缓存缓存的是查询结果(不是执行计划)。

在 InnoDB 存储引擎中，有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存，包括数据页、索引页、插入缓存、锁信息、自适应索引哈希、数据字典信息等。

缓冲池的重要性

缓存原则

位置 * 频次 该原则可以优化I/O访问效率。

首先，位置决定效率，提供缓冲池就是为了在内存中直接访问数据。

其次，频次决定优先级顺序。因缓冲池大小有限，无法加载所有数据进入，所以会优先加载使用频次高的数据。

缓冲池的预读特性

缓冲池缓存数据时，还会提前加载该数据周围的一些数据，以减少未来可能的磁盘I/O操作。

3.2 缓冲池如何读取数据

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

内存中的缓存结构与作用如下图：

image

3.3缓冲池更新问题

当用户修改数据库中的记录的时候，实际修改的时缓冲池中的记录，数据库会以一定的频率刷新到磁盘上，不是每次操作都会同步到磁盘。缓冲池采用checkpoint机制同步磁盘上的数据，好处是可以提升数据库整体性能。

缓冲池不够用时，会释放部分不常用的页，此时就可以强行采用checkpoint方式，将不常用的脏页（发生修改操作的页）写到磁盘，然后将缓冲池中对应的页释放。

对于防止未同步到磁盘持久化前发生系统故障，采用Redo Log & Undo Log。

3.4 缓冲池命令

  1. 查看 / 设置缓冲池大小

    InnoDB引擎，可以通过查看innodb_buffer_pool_size变量查看缓冲池大小。

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)

上图表名缓冲池大小为134217728/1024/1024=128M。

  1. 修改缓冲池大小的命令：
set global innodb_buffer_pool_size = 268435456;

​ 或者可以通过配置文件进行配置。

[server]
innodb_buffer_pool_size = 268435456
3.5 多个Buffer Pool实例
[server]
innodb_buffer_pool_instances = 2  
# 表示创建两个缓冲池实例

查看缓冲池数量命令

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+
1 row in set (0.00 sec)

每个BufferPool的大小是根据总大小除以缓冲池个数

存储引擎

1. 查看存储引擎
mysql> show engines\G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)
2. 设置系统默认存储引擎
  • 查看默认存储引擎
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.01 sec)
# 或 select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)
  • 修改默认存储引擎

如果创建表时没有显式指明存储引擎，会使用系统默认的存储引擎，可以在创建的时候指定存储引擎。也可以通过命令修改：

SET DEFAULT_STORAGE_ENGINE=MyISAM;

或修改my.cnf文件

default-storage-engine=MyISAM
# 重启服务
systemctl restart mysqld.service
3. 设置表的存储引擎

​ 存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

3.1 创建表时指定存储引擎
CREATE TABLE 表名(
建表语句;
) ENGINE = 存储引擎名称;
3.2 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称;
4. 引擎介绍
4.1 InnoDB：支持外键的事务存储引擎

  • MySQL从3.23.34a开始就包含InnoDB存储引擎。 大于等于5.5之后，默认采用InnoDB引擎 。

  • InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。

  • 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。

  • 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

  • 数据文件结构：

    • 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）
    • 表名.ibd 存储数据和索引

  • InnoDB是为处理巨大数据量的最大性能设计。

    • 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除 了。比如： .frm ， .par ， .trn ， .isl ， .db.opt 等都在MySQL8.0中不存在了。

  • 对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较 高 ，而且内存大小对性能有决定性的影响。

4.2 MyISAM 引擎：主要的非事务处理存储引擎

  • MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级 锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。

  • 5.5之前默认的存储引擎

  • 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用

  • 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高

  • 数据文件结构：

    • 表名.frm 存储表结构 表名.MYD 存储数据 (MYData)
    • 表名.MYI 存储索引 (MYIndex)

  • 应用场景：只读应用或者以读为主的业务

4.3 Archive 引擎：用于数据存档

image

4.4 Blackhole 引擎：丢弃写操作，读操作会返回空内
4.5 CSV 引擎：存储数据时，以逗号分隔各个数据项

​ 创建CSV表还会创建相应的 元文件 ，用于 存储表的状态 和 表中存在的行数 。此文件的名称与表的名称相 同，后缀为 CSM 。如图所示

4.6 Memory 引擎：置于内存的表

Memory采用的逻辑介质是内存， 响应速度很快 ，但是当mysqld守护进程崩溃的时 数据会丢失 。另外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。

特征

  • Memory同时 支持哈希（HASH）索引 和 B+树索引 。
  • Memory表至少比MyISAM表要快一个数量级。
  • MEMORY 表的大小是受到限制 的。表的大小主要取决于两个参数，分别是 max_rows 和 max_heap_table_size 。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默 认为16MB，可以按需要进行扩大。
  • 数据文件与索引文件分开存储。
  • 缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。

使用场景

  1. 目标数据比较小 ，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数 max_heap_table_size 控制Memory表的大小，限制Memory表的最大的大小。 如果数据是临时的，而且必须立即可用得到，那么就可以放在内存中。
  2. 存储在Memory表中的数据如果突然间 丢失的话也没有太大的关系 。
5.MyISAM 对比 InnoDB

image

6. InnoDB的优势

​ InnoDB存储引擎在实际应用中拥有诸多优势，比如操作便利、提高了数据库的性能、维护成本低等。如果由于硬件或软件的原因导致服务器崩溃，那么在重启服务器之后不需要进行额外的操作。InnoDB崩溃恢复功能自动将之前提交的内容定型，然后撤销没有提交的进程，重启之后继续从崩溃点开始执行。

​ InnoDB存储引擎在主内存中维护缓冲池，高频率使用的数据将在内存中直接被处理。这种缓存方式应用于多种信息，加速了处理进程。

​ 在专用服务器上，物理内存中高达80%的部分被应用于缓冲池。如果需要将数据插入不同的表中，可以设置外键加强数据的完整性(开发中一般在应用层限制，不使用外键)。更新或者删除数据，关联数据将会被自动更新或删除。如果试图将数据插入从表，但在主表中没有对应的数据，插入的数据将被自动移除。如果磁盘或内存中的数据出现崩溃， 在使用脏数据之前，校验和机制会发出警告。当每个表的主键都设置合理时，与这些列有关的操作会被自动优化。插入、更新和删除操作通过做改变缓冲自动机制进行优化。 InnoDB不仅支持当前读写，也会缓冲改变的数据到数据流磁盘 。

​ InnoDB的性能优势不只存在于长时运行查询的大型表。在同一列多次被查询时，自适应哈希索引会提高查询的速度。使用InnoDB可以压缩表和相关的索引，可以在不影响性能和可用性的情况下创建或删除索引 。对于大型文本和BLOB数据，使用动态行形式，这种存储布局更高效。通过查询 INFORMATION_SCHEMA库中的表可以监控存储引擎的内部工作。在同一个语句中，InnoDB表可以与其他存储引擎表混用。即使有些操作系统限制文件大小为2GB，InnoDB仍然可以处理。 当处理大数据量时， InnoDB兼顾CPU，以达到最大性能 。

InnoDB和ACID模型

​ ACID模型是一系列数据库设计规则，这些规则着重强调可靠性，而可靠性对于商业数据和任务关键型应用非常重要。MySQL包含类似InnoDB存储引擎的组件，与ACID模型紧密相连，这样出现意外时，数据不会崩溃，结果不会失真。如果依赖ACID模型，可以不使用一致性检查和崩溃恢复机制。如果拥有额外的软件保护，极可靠的硬件或者应用可以容忍一小部分的数据丢失和不一致，可以将MySQL设置调整为只依赖部分ACID特性，以达到更高的性能。下面讲解InnoDB存储引擎与ACID模型相同作用的四个方面。

  1. 原子方面 ACID的原子方面主要涉及InnoDB事务，与MySQL相关的特性主要包括： 自动提交设置。 COMMIT语句。 ROLLBACK语句。 操作INFORMATION_SCHEMA库中的表数据。
  2. 一致性方面 ACID模型的一致性主要涉及保护数据不崩溃的内部InnoDB处理过程，与MySQL相关的特性 主要包括： InnoDB双写缓存。 InnoDB崩溃恢复。
  3. 隔离方面 隔离是应用于事务的级别，与MySQL相关的特性主要包括： 自动提交设置。 SET ISOLATION LEVEL语句。 InnoDB锁的低级别信息。
  4. 耐久性方面 ACID模型的耐久性主要涉及与硬件配置相互影响的MySQL软件特性。由于硬件复杂多样 化，耐久性方面没有具体的规则可循。与MySQL相关的特性有： InnoDB双写缓存，通过innodb_doublewrite配置项配置。 配置项innodb_flush_log_at_trx_commit。 配置项sync_binlog。 配置项innodb_file_per_table。 存储设备的写入缓存。 存储设备的备用电池缓存。 运行MySQL的操作系统。 持续的电力供应。 备份策略。 对分布式或托管的应用，最主要的在于硬件设备的地点以及网络情况。
InnoDB架构
  1. 缓冲池 缓冲池是主内存中的一部分空间，用来缓存已使用的表和索引数据。缓冲池使得经常被使用的 数据能够直接在内存中获得，从而提高速度。
  2. 更改缓存 更改缓存是一个特殊的数据结构，当受影响的索引页不在缓存中时，更改缓存会缓存辅助索 引页的更改。索引页被其他读取操作时会加载到缓存池，缓存的更改内容就会被合并。不同于集群索 引，辅助索引并非独一无二的。当系统大部分闲置时，清除操作会定期运行，将更新的索引页刷入磁 盘。更新缓存合并期间，可能会大大降低查询的性能。在内存中，更新缓存占用一部分InnoDB缓冲池。 在磁盘中，更新缓存是系统表空间的一部分。更新缓存的数据类型由innodb_change_buffering配置项管 理。
  3. 自适应哈希索引 自适应哈希索引将负载和足够的内存结合起来，使得InnoDB像内存数据库一样运行， 不需要降低事务上的性能或可靠性。这个特性通过innodb_adaptive_hash_index选项配置，或者通过-- skip-innodb_adaptive_hash_index命令行在服务启动时关闭。
  4. 重做日志缓存 重做日志缓存存放要放入重做日志的数据。重做日志缓存大小通过 innodb_log_buffer_size配置项配置。重做日志缓存会定期地将日志文件刷入磁盘。大型的重做日志缓存 使得大型事务能够正常运行而不需要写入磁盘。
  5. 系统表空间 系统表空间包括InnoDB数据字典、双写缓存、更新缓存和撤销日志，同时也包括表和索引 数据。多表共享，系统表空间被视为共享表空间。
  6. 双写缓存 双写缓存位于系统表空间中，用于写入从缓存池刷新的数据页。只有在刷新并写入双写缓存 后，InnoDB才会将数据页写入合适的位置。
  7. 撤销日志 撤销日志是一系列与事务相关的撤销记录的集合，包含如何撤销事务最近的更改。如果其他 事务要查询原始数据，可以从撤销日志记录中追溯未更改的数据。撤销日志存在于撤销日志片段中，这 些片段包含于回滚片段中。
  8. 每个表一个文件的表空间 每个表一个文件的表空间是指每个单独的表空间创建在自身的数据文件中， 而不是系统表空间中。这个功能通过innodb_file_per_table配置项开启。每个表空间由一个单独的.ibd数 据文件代表，该文件默认被创建在数据库目录中。
  9. 通用表空间 使用CREATE TABLESPACE语法创建共享的InnoDB表空间。通用表空间可以创建在MySQL数 据目录之外能够管理多个表并支持所有行格式的表。
  10. 撤销表空间 撤销表空间由一个或多个包含撤销日志的文件组成。撤销表空间的数量由 innodb_undo_tablespaces配置项配置。
  11. 临时表空间 用户创建的临时表空间和基于磁盘的内部临时表都创建于临时表空间。 innodb_temp_data_file_path配置项定义了相关的路径、名称、大小和属性。如果该值为空，默认会在 innodb_data_home_dir变量指定的目录下创建一个自动扩展的数据文件。
  12. 重做日志 重做日志是基于磁盘的数据结构，在崩溃恢复期间使用，用来纠正数据。正常操作期间， 重做日志会将请求数据进行编码，这些请求会改变InnoDB表数据。遇到意外崩溃后，未完成的更改会自 动在初始化期间重新进行。

过程中使用的版本为MySQL5.7.36
参考资料： 尚硅谷MySQL2022–宋红康
如有错误，请评论区指正，万分感谢。

陈新新zZ
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
在数据库中用MySQL语句批量创建100个用户和密码和批量删除已经创建的100个用户
m0_61927474的博客
11-05 414
在数据库中用MySQL语句批量创建100个用户和密码和批量删除已经创建的100个用户
【数据库】Mysql 创建用户与授权
热门推荐
shgh_2004的专栏
04-16 2万+
Mysql 创建用户与授权 1. 创建用户： CREATE USER 'username'@'host' [IDENTIFIED BY 'password']; username:要创建的用户名； host:代表地址；任何地址可以使用% IDENTIFIED BY 'password':设置密码，如果不写则为空密码 eg： CREATE USER 'root'@'localhost'...
mysql给用户赋予权限
计算机毕业论文源码，学生个人网页制作html源码。贴近用户做网络推广和互联网优化。
11-17 6672
如何设置mysql的权限为所有的用户权限 这个设置只要进入Linux系统的超级用户状态#下面，即可以使用chmod命令对MySQL数据库系统下面的所有文件进行权限设置。具体的就看你想设置成什么权限，你就可以设置成什么权限了。chmod的基本用法如下： #chmod750myfile 该命令对myfile这个文件设置成...
数据库用户权限设计逻辑(配数据库模型图）
傲宇苍穹
01-04 2289
权限设计初级如下 1.设计角色 （包含id,名称，描述，后台用户数量，创建时间，是否禁用，种类） 2.权限表（包含id,父级权限，权限值，图标，权限类型，前端资源路径，启用状态，创建时间，排序） 3.用户角色与权限关联表（id，角色id，权限id) ...
数据库设计 概念结构设计（以权限系统为例）
weixin_30807779的博客
04-23 488
转载来自http://yiyihuazi.blog.163.com/blog/static/151874052010637254799/小小宇梦 转载请注明出处尊重别人也是尊重自己 一、数据模型 以下概念在一些教科书中都会有讲到，比如：《数据库原理与应用》。这里作了一下总结。 1.1、概念 模型，是对现实世界的抽象，数据模型，就是描述数据结构（静态特征）、数据操作（动态特征...
MySQL进阶：优化查询性能与高可用性的实践
最新发布
08-24
MySQL进阶：优化查询性能与高可用性的实践 随着互联网技术的快速发展，数据量呈现爆炸式增长，数据库作为存储和管理数据的关键工具，其性能和稳定性对企业的运营至关重要。MySQL作为一款开源的关系型数据库管理系统...
MySQL高级学习笔记（三）：Mysql逻辑架构介绍、mysql存储引擎详解
09-09
主要介绍了Mysql逻辑架构介绍、mysql存储引擎，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
MySQL 进阶：提高数据库性能和优化查询.md
08-11
在本篇文章中，我们介绍了一些MySQL进阶技巧，包括索引优化、查询优化和数据库配置调整。
MySQL高级：存储引擎&索引&执行计划.md
02-23
MySQL高级：存储引擎&索引&执行计划.md
关于用户权限的数据库设计
枫林残叶 的专栏
07-08 6699
最近项目的项目很奇怪，一个大项目(系统)里包含了很多小的子系统，而这些子系统中都有权限控制的部分，这件事情挺让我头痛的，记得一年前在沈阳，我曾经有一段时间也因因这个问题而疲于奔命，为什么说疲于奔命呢？由于当时项目进度不允许，导致最终系统权限模块草草了事，每个模块都是由读权限字符串来控制用户ACL，当用户无法访问时，提示权限不够。这么做对用户是很不负责任的，既然让用户看到了操作的方式和界面，为什么又
Mysql逻辑架构
xiaowanziddd的博客
07-09 817
一、逻辑架构剖析 Mysqi是典型的C/S架构，服务端程序使用的mysqld. 不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送一点文本（sql语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。 mysql简化为三层结构： 1.连接层：客户端和服务器建立连接，客户端发送SQL至服务器端； 2.SQL(服务层)：对DQL语句进行查询处理；与数据库文件的存储方式无关 3.存储引擎层：与数据库文件打交道，负责数据的存储和读取。 二、SQL执行流程SQ
权限控制功能数据库表结构设计思路
rengn的博客
07-22 1019
1、权限控制功能，其核心思路是控制某“用户”所属“角色”可以访问哪些“角色”，可以访问哪些“功能”。 在前后端分离架构模式下，功能实现是基于“接口”进行的，所以“功能”和“页面”会分别和“接口”所关联。 2、所以数据库中，至少有以上5张表，分别为：用户信息表、角色表、页面信息表，接口信息表，功能信息表，并且分别建立彼此之间的关联表 创建表结构sql语句如下： CREATE TABLE `api_info` ( `api_id` int(11) NOT NULL, ...
MySQL赋予用户的各种数据库操作权限
心静自然凉zc的博客
01-28 1万+
如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“MySQL 赋予用户权限命令的简单格式可概括为：grant 权限 on 数据库对象 to 用户。grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。一、grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。二、grant 数据库开发人员，创建表、索引、视图、存储过程、函数。四、grant 高级 DBA 管理 MySQL 中所有数据库的权限。
Mysql数据库设置表权限
派大星球
11-03 4187
Mysql数据库设置表权限 1、使用管理员账号进入数据库。 mysql -uroot -p 如下图所示，进入数据库。 2、创建用户，已创建用户可忽略。 create user 'username'@'%' identified by 'password'; username：创建的用户名。 %：匹配所有主机，该地方还可以设置成‘localhost’，代表只能本地访问，设置指定ip代表只允许该ip能访问 password：创建用户密码 例如： create user 'newuser'@'local
MySQL授予用户权限
qq_43079350的博客
05-13 1万+
使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句，在将用户账户从user表删除之前，应该收回相应用户的所有权限。给用户授权的方式有 2 种，分别是通过把角色赋予用户给用户授权（mysql8.0的特性） 和 直接给用户授权。用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制用户对数据库的访问，消除安全隐患。
Mysql用户创建以及权限赋予操作
lx2wenhui的博客
06-08 1万+
其中，privileges表示授权给用户的权限，object表示将权限授予的对象(比如database_name.*)，user表示被授权的用户，IDENTIFIED BY 'password’表示设置用户密码(这一步可以省略)。该查询语句返回三个列：user表示用户名，host表示用户所在的主机，authentication_string表示该用户的加密密码或者密码哈希值。其中，new_user是新用户的名称，localhost是该用户的登录主机，user_password是该用户的密码。
Django实战1-权限管理功能实现-08：组织架构修改和删除
weixin_30693683的博客
06-18 199
1 组织架构的修改 实现思路： 1、请求：前台通过修改按钮，请求修改页面，同时传递修改数据的id到后台，后台通过reques.GET方法获取id，返回数据，并将数据渲染到修改页面 2、执行：前台修改完数据后，提交保存，后台通过ID获取数据实例，通过form.is_valid()成功验证数据想信息后，调用form.save()方法将数据保存到数据库。 1.1 组织架构修改视图 修改和添加操...
Mysql 基本操作
月球上的博客
08-12 2517
此处记录下mysql 的一些常用命令。 一. Mysql 命令行远程连接 mysql -u 用户名 -p密码 -h 服务器IP地址 -P 服务器端MySQL端口号 -D 数据库名 mysql -u username -p20180812 -h 120.0.0.0 -P 3360 -D user 注意： 密码紧跟在-p后面， 不加密码的话，应该会再问的 二. Mysql更改密码 直...
mysql 授权命令
dichi4045的博客
07-15 1662
MySQL清空数据库的操作：truncate table tablename; MySQL赋予用户权限命令的简单格式可概括为：grant权限on数据库对象to用户一、grant普通数据用户，查询、插入、更新、删除数据库中所有表数据的权利。 1 grant select on testdb.* to common_user@'%' grant insert on ...
mysql 列级权限授予用户_MySQL用户权限管理
06-02
MySQL 用户权限管理包括两个方面：全局级别的权限和对象级别的权限。全局级别的权限控制用户对整个 MySQL 系统的操作，对象级别的权限控制用户对数据库、表、列等对象的操作。 在 MySQL 中，授权命令是 GRANT，撤销...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值