Day05-CSDN博客

本文链接：https://blog.csdn.net/qq_43069447/article/details/135455132

19.用户与权限管理

19.1 用户管理

MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。

MySQL提供了许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。

MySQL数据库的安全性需要通过账户管理来保证。

19.1.1 创建用户

在MySQL数据库中，官方推荐使用CREATE USER语句创建新用户。

使用CREATE USER语句来创建新用户时，必须拥有CREATEUSER权限。每添加一个用户，CREATE USER语句会在MySQL.user表中添加一条新记录，但是新创建的账户没有任何权限。如果添加的账户已经存在，CREATE USER语句就会返回一个错误。

CREATE USER 用户名 [ IDENTIFIED BY ‘密码' ] [ ,用户名 [ IDENTIFIED BY ‘密码']];
CREATE USER zhangsan IDENTIFIED BY '111111';

用户名参数表示新建用户的账户，由用户(User)和主机名(Host)构成。
"[]"表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用IDENTIFIED BY指定明文密码值。
CREATE USER语句可以同时创建多个用户。

19.1.2 修改用户(修改用户名)

UPDATE mysql.user SET USER='114' WHERE USER='wang5'; FLUSH PRIVILEGES;

19.1.3 删除用户

在MySQL数据库中，可以使用DROP USER语句来删除普通用户，也可以直接在mysql.user表中删除用户。

使用DROP方式删除(推荐)：使用DROP USER语句来删除用户时，必须用于DROP USER权限。DROP USER语句的基本语法形式如下:DROP USER user[,user]...;其中，user参数是需要删除的用户，由用户的用户名(User)和主机名(Host)组成，DROP USER语句可以同时删除多个用户，各用户之间用逗号隔开。

19.1.4 设置当前用户密码

适用于root用户修改自己的密码，以及普通用户登录后修改自己的密码。

root用户拥有很高的权限，因此必须保证root用户的密码安全。root用户可以通过多种方式来修改密码，使用ALTER USER修改用户密码是MySQL官方推荐的方式。此外，也可以通过SET语句修改密码，由于MySQL8.0中已移除了PASSWORD函数，因此不再使用UPDATE语句直接操作用户表修改密码。

使用ALTER USER命令来修改当前用户密码：用户可以使用ALTER命令来修改自身密码，如下语句代表修改当前登录用户的密码。基本语法如下:
ALTER USER USER() IDENTIFIED BY 'new_password' ;
使用SET语句来修改当前用户密码：使用root用户登录MySQL后，可以使用SET语句来修改密码，具体SQL语句如下:SET PASSWORD='new_password';。该语句会自动将密码加密后再赋给当前用户。

19.1.5 修改其它用户密码

root用户不仅可以修改自己的密码，还可以修改其它普通用户的密码。root用户登录MySQL服务器后，可以通过ALTER语句和SET语句来修改普通用户的密码。

使用ALTER语句来修改普通用户的密码：可以使用ALTER USER语句来修改普通用户的密码。基本语法形式如下:
ALTER USER user [ IDENTIFIED BY ‘新密码'] [, user[ IDENTIFIED BY '新密码']] ...;。其中，user参数表示新用户的账户，由用户名和主机名构成；IDENTIFIED BY关键字用来设置密码。
使用SET命令来修改普通用户的密码：使用root用户登录到MySQL服务器后，可以使用SET语句来修改普通用户的密码。SET语句的代码如下：SET PASSWORD FOR 'username' @ ' hostname ' = 'new_password';。其中,username参数是普通用户的用户名; hostname参数是普通用户的主机名; new_password是新密码。

19.2 权限管理

19.2.1 权限列表

在这里插入图片描述

19.2.2 授予权限

给用户授权的方式有2种，分别是通过把角色赋予用户给用户授权和直接给用户投权。用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全隐患。

授权命令：GRANT 权限1,权限2，...权限n ON 数据库名称.表名称 TO 用户名@用户地址 [ IDENTIFIED BY ‘密码口令' ];

该权限如果发现没有该用户，则会直接新建一个用户。

19.2.3 查看权限

查看当前用户权限：
SHOW GRANTS;
查看某用户的全局权限：
SHOW GRANTS FOR 'user' @ '主机地址';

19.2.4 收回权限

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

注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。
REVOKE 权限1，权限2，...，权限n ON 数据库名称.表名称 FROM 用户名@用户地址;

19.3 权限表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中。MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表。除此之外，还有table_priv表、 column_priv表和proc_priv表等。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。

19.4 角色管理

角色是在MySQL8.0中引入的新功能。在MySQL中，角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色，同时也被授予角色包含的权限。对角色进行操作需要较高的权限。并且像用户账户一样，角色可以拥有授予和撤消的权限。

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

19.4.1 创建角色

在实际应用中，为了安全性，需要给用户授予权限。当用户数量较多时，为了避免单独给每一个用户授予多个权限，可以先将权限集合放入角色中。再赋予用户相应的角色。
创建角色使用CREATE ROLE语句，语法如下:
CREATE ROLE 'role_name ' [ @ ' host_name ' ] [ , ' role_name'[ @ 'host_name'] ]...

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

19.4.2 给角色赋予权限

创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。给角色授权的语法结构是:
GRANT privileges ON table_name TO 'role_name ' [ @ ' host_nane ' ] ;

上述语句中privileges代表权限的名称，多个权限以逗号隔开。可使用SHOW PRIVILEGES查询权限名称。

19.4.3 查看角色的权限

赋予角色权限之后，我们可以通过 SHOW GRANTS语句，来查看权限是否创建成功。

19.4.4 回收角色的权限

角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句。修改了角色的权限，会影响拥有该角色的账户的权限。撤销角色权限的SQL语法如下:
REVOKE privileges ON tablename FROM 'rolename';

19.4.5 删除角色

当需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色。语法格式如下：
DROP ROLE role [,rolc2] ...
注意，如果删除了角色，那么用户也就失去了通过这个角色所获得的所有权限。

19.4.6 给用户赋予角色

角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。给用户添加角色可使用GRANT语句，语法形式如下:
GRANT role [ ,role2, ...] TO user [ , user2,... ] ;

注意：MySQL中创建了角色之后，默认都是没有被激活的，必须手动激活后用户才能拥有角色对应的权限。

19.4.5 激活角色

使用set default role命令激活角色。用户需要退出重新登录，才能看到赋予的角色。
将active all roles on login设置为on：SET GLOBAL activate_all_roles_ib_login=ON;，即对所有角色永久激活。

19.4.6 撤销用户的角色

REVOKE role FROM user;

19.4.7 设置强制角色(mandatory role)

给每个创建账户的默认角色，不需要手动设置，强制角色无法被REVOKE或DROP。

服务启动前设置：mandatory_roles=‘role1,role2’
运行时设置：
- SET PERSIST mandatory_roles='role1,role2'; 系统重启后仍然有效。
- SET GLOBAL mandatory_roles='role1,role2'; 系统重启后失效。

20.逻辑架构

20.1 逻辑架构剖析

20.1.1 服务器处理客户端请求

首先 MySQL是典型的C/S架构，即Client/Server架构，服务器端程序使用的mysqld。
不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送一段文本(SQL语句)，服务器进程处理后再向客户端进程发送一段文本(处理结果）。

20.1.2 Connectors

Connectors，指的是不同语言中与SQL的交互。MySQL首先是一个网络程序，在TCP之上定义了自己的应用层协议。所以要使用MySQL，我们可以编写代码，跟MySQL Server建立TCP连接，之后按照其定义好的协议进行交互。或者比较方便的办法是调用SDK，比如Native C API、JDBC、PHP等各语言MySQL Connector，或者通过ODBC。但通过SDK来访问MySQL，本质上还是在TCP连接上通过MySQL协议跟MySQL进行交互。

20.1.3 第一层：连接层

系统(客户端)访问MySQL服务器前，做的第一件事就是建立TCP连接。经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。

用户名或密码不对，会收到一个Access denied for user错误。客户端程序结束执行。
用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限。
为了解决TCP无限创建与TCP频繁创建销毁带来的资源耗尽、性能下降问题。MySQL服务器里有专门的TCP连接池限制连接数，采用长连接模式复用TCP连接。

20.1.4 第二层：服务层

第二层架构主要完成大多数的核心眼务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化∶如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。

如果是SELECT语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

20.1.5 第三层：引擎层

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

20.1.6 存储层

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在文件系统上，以文件的方式存在的。并完成与存储引擎的交互。

20.2 SQL执行流程

在这里插入图片描述

查询缓存：Server如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端;如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在MySQL8.0之后就抛弃了这个功能。
解析器：在解析器中对SQL语句进行语句分析、语义分析。
优化器：在优化其中会确定SQL语句的执行路径，比如是根据全表检索还是根据索引检索等。经过了解析器，MySQL就知道你要做什么了，在开始执行之前，还要先经过优化器的处理，一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
执行器：在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就执行SQL查询并返回结果。在MySQL8.0以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

20.3 数据库缓冲池（Buffer Pool）

InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面（包括读页面、写页面、创建新页面等操作)。而磁盘I/O需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为数据缓冲法，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。

这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行I/O的时间。要知道，这种策略对提升SQL语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。

20.3.1 缓冲池VS查询缓存

缓冲池和查询缓存不是一个东西。

缓冲池
InnoDB缓冲池中包括了数据页、索引页、插入缓存、锁信息、自适应Hash和数据字典信息等。
查询缓存
查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在MySQL中的查询缓存，不是缓存查询计划，而是查询对应的结果。因为命中条件苛刻，而且只要数据表发生变化，直询缓存就会失效，因此命中率低。’

缓冲池和查询缓存都服务于数据库整体的I/O操作，它们的共同点都是通过缓存的机制来提升效率。

21.存储引擎

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端即可。

MySQL中提到了存储引擎的慨念。简而言之，存储引擎就是指表的类型。它的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

21.1 查看存储引擎

show engines;
查询结果显示MySQL共存在9中存储引擎。即MEMORY、MRG_MYISAM、FEDERATED、InnoDB（默认使用）、CSV、BLACKHOLE、ARCHIVE、MyISAM、PERFORMANCE_SCHEMA。

21.2 设置系统默认的存储引擎

查看默认的存储引擎：
show variables like '%storage_engine%';或SELECT @%default_storagr_engine;
修改默认的存储引擎
如果在创建表的语句中没有显式指定表的存储引导的话，那就会默认使用InnoDB作为表的存储引擎。如果我们想改变表的默认存储引擎的话，可以这样写启动服务器的命令行:
SET DEFAULT_STORAGE_ENGINE=MyISAM;

21.3 设置表的存储引擎

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

21.3.1 创建表时指定存储引擎

我们之前创建表的语句都没有指定表的存储引擎，那就会使用默认的存储引擎InnoDB。如果我们想显式的指定一下表的存储引擎，那可以这么写: CREATE TABLE 表名( 建表语句; ENGINE=存储引擎名称;

21.3.2 修改表的存储引擎

如果表已经创建完成，我们也可以使用该语句来修改表的存储引擎：ALTER TABLE 表名 ENGINE=存储引擎名称;

21.4 引擎介绍

21.4.1 InnoDB引擎：具备外键支持功能的事务存储引擎

MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后，默认采用InnoDB引擎。
lnnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期事务。可以确保事务的完整提交(Commit)和回滚(Rolltack)。
除了增加和查询外，还需要更新、删除操作操作，应优先选择InnoDB存储引擎。
除非有非常特别的原因要使用其他的存储引擎，否则应该优先考虑InndDB引擎。
数据文件结构：
- 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中)
- 表名.ibd 存储数据和索引
lnnoDB是为处理巨大数据量的最大性能设计。
- 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除了。比如: .frm,.par,.trn ,.isl,.db. opt等都在MySQL8.0中不存在了。
对比MyISAM的存储引擎，InnoDB写的处理效率差些，并且会占用更多的磁盘空间以保存数据和索引。
MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。

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

MylSAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
5.5之前默认的存储引擎。
优势是访问的速度快，对事务完整性没有要求或者SELECT、INSERT为主的应用。
针对数据统计有额外的常数存储，故而count(*)的查询效率很高。
数据文件结构：
- 表名.frm 存储表结构
- 表名.MYD 存储数据(MYData)
- 表名.MYI 存储索引(MYIndex)
应用场景：只读应用或者以读为主的业务。

21.4.3 Archive引擎：用于数据存档

archive是归档的意思，仅仅支持插入和查询两种功能〔行被插入后不能再修改）。
在MySQL5.5以后支持索引功能。
拥有很好的压缩机制，使用zlib压缩库，在记录请求的时候实时的进行压缩，经常被用来作为仓库使用。
创建ARCHIVE表时，存储引擎会创建名称以表名开头的文件。数据文件的扩展名为.ARZ。
根据英文的测试结论来看，同样数据量下，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。
ARCHIVE存储引擎采用了行级锁。该ARCHIVE引擎支持AUTO_INCREMENT列属性。AUTO_INCREMENT列可以具有唯一索引或非唯一索引。尝试在任何其他列上创建索引会导致错误。
Archive表适合日志和数据采集（档案）类应用；适合存储大量的独立的作为历史记录的数据。拥有很高的插入速度，但是对查询的支持较差。

21.4.4 Blackhole引擎：丢弃写操作，读操作会返回空内容

Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。
服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

21.4.5 CSV引擎：存储数据时，以逗号分隔各个数据项

CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
CSV引擎可以作为一种数据交换的机制，非常有用。
CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。
对于数据的快速导入、导出是有明显优势的。

创建CSV表时，服务器会创建一个纯文本数据文件，其名称以表名开头并带有.CSV扩展名。当你将数据存储到表中时，存储引擎将其以逗号分隔值格式保存到数据文件中。

21.4.6 Memory引擎：置于内存的表

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

Memory同时支持哈希（HASH）索引和B+树索引。
- 哈希索引相等的比较快，但是对于范围的比较慢很多。
- 默认使用哈希（HASH）索引，其速度要比使用B型树(BTREE）索引快。
- 如果希望使用B树索引，可以在创建索引时选择使用。
Memory表至少比MyISAM表要快一个数量级。
MEMORY表的大小是受到限制的。表的大小主要取决于两个参数，分别是max_rows和max_heap_table_size。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大。
数据文件与索引文件分开存储。
- 每个基于MEMORY存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为frm类型，该文件中只存储表的结构，而其数据文件都是存储在内存中的。
- 这样有利于数据的快速处理，提供整个表的处理效率。
缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。
使用Memory存储引擎的场景：
- 目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。
- 如果数据是临时的而且必须立即可用得到，那么就可以放在内存中。
- 存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。