mysql高级学习笔记(一)

11 篇文章 1 订阅
10 篇文章 0 订阅

一、用户管理

用户的增删改
  • mysql登录方式
mysql -h localhost -P 3306 -p dbtest1 -e "select * from emp1"
mysql -uroot -p
  • 创建mysql用户
create user 'lhk' identified by '123456';
#@前面为用户名,@后面接连接mysql的IP地址
create user 'lhk'@'localhost' identified by '123456';
  • 修改mysql用户信息(不涉及到修改用户权限,一般使用比较少)
update mysql.user set user='lhk01' where user='lhk';
#修改完成后进行刷新后才会生效
flush privileges;
  • 删除mysql用户
#方式一:drop(推荐使用)
drop user 'lhk';
drop user '用户名'@'mysql数据库中user表的host字段的值'


# 方式二:使用delete
delete from mysql.user where user='用户名' and host='mysql数据库中user表的host字段的值'
#delete完成后进行刷新后才会生效
flush privileges;
用户密码的管理
  • 修改当前用户密码
#方式一:使用alter修改密码(推荐),user():获取当前用户名
alter user user() identified by '密码'

#方式二:set语句修改密码
set password='新密码'
  • 查看用户的加密密码
#authentication_string:user表中加密密码字段
select host,user,authentication_string from user;
  • 修改其他用户的密码
#方式一:alter语句修改
alter user '用户名'@'mysql数据库中user表的host字段的值' identified by '新密码';


# 方式二:set方式修改
set password for '用户名'@'mysql数据库中user表的host字段的值'='新密码';
  • 手动设置用户密码过期
#设置某个用户密码过期,设置密码过期后用户仍然可以登录数据库,但是无法进行查询,需要重新设置密码后才可以正常使用
alter user '用户名'@'mysql数据库中user表的host字段的值' password expire;
#手动设置全局指定时间密码过期:
# default_password_lifetime该全局变量默认值为0,表示禁用自动密码过期
set persist default_password_lifetime = 天数;

#手动设置全局指定时间密码过期方式二:
#在配置文件my.cnf中[mysqld]下建立去阿奴策略:
[mysqld]
default_password_lifetime = 180;
#手动设置单例指定时间密码过期:
#设置N天过期,将N改为指定数字即可
create user '用户名'@'mysql数据库中user表的host字段的值' password expire interval N day;
alter user '用户名'@'mysql数据库中user表的host字段的值' password expire interval N day;

#设置密码永不过期
create user '用户名'@'mysql数据库中user表的host字段的值' password expire never;
alter user '用户名'@'mysql数据库中user表的host字段的值' password expire never;

#使用全局密码策略设置单例密码策略
create user '用户名'@'mysql数据库中user表的host字段的值' password expire default;
alter user '用户名'@'mysql数据库中user表的host字段的值' password expire default;
  • 手动设置密码重用
#系统变量password_history和password_reuse_interval设置密码重用策略
#password_history:规定密码重用的数量,限制新密码不能与最近的多少个密码相同
#password_reuse_interval:规定密码重用的周期,限制新密码不能与最近的多长时间内的密码相同

#设置全局密码重用策略方式一,使用sql语句:
set persist password_history = N;  #设置不能与最近的N个密码相同
set persist password_reuse_interval = N;  #设置不能与最近的N天内的密码相同

#方式二,修改my.cnf配置文件
[mysqld]
password_history = N
password_reuse_interval = N
#手动设置单例用户密码重用策略:

#新密码不能使用最近的N个密码
create user '用户名'@'mysql数据库中user表的host字段的值' password history N;
alter user '用户名'@'mysql数据库中user表的host字段的值' password history N;

#新密码不能使用最近N天内的密码
create user '用户名'@'mysql数据库中user表的host字段的值' password reuse interval N day;
alter user '用户名'@'mysql数据库中user表的host字段的值' password reuse interval N day;

#新密码既不能使用最近的N个密码,也不能使用最近M天内的密码
create user '用户名'@'mysql数据库中user表的host字段的值' password history N password reuse interval M day;
alter user '用户名'@'mysql数据库中user表的host字段的值' password history N password reuse interval M day;

#使用全局的密码重用策略
create user '用户名'@'mysql数据库中user表的host字段的值' password history default password reuse interval default;
alter user '用户名'@'mysql数据库中user表的host字段的值' password history default  password reuse interval default;

二、用户权限管理

  • 查看mysql中的权限
show privileges;
  • 给某个用户授权
grant 权限1,权限2,...权限n on 数据库名称.表名称 to '用户名'@'用户地址' [identified by '密码']
# 如:
grant select,insert,delete,update on dbtest1.* to 'lhk'@'localhost';

如果需要赋予包括GRANT的权限,添加参数WITH GRANT 0PTION 这个选项即可,表示该用户可以将自己拥有的权限授权给别人。经常有人在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不 能使用GRANT命令创建用户或者给其它用户授权。

  • 查看权限
#查看当前用户权限
show grants;
# 或
show grants for current_user;
# 或
show grants for current_user();
#查看某个用户的全局权限
show grants for '用户名'@'主机地址';
  • 回收权限
revoke 权限1,权限2...权限n on 数据库名称.表名称 from '用户名'@'主机地址';
# 如:
revoke all privileges on *.* form 'lhk'@'localhost';
# 回收权限后需要重新登录才会生效

三、权限表

MySQL服务器通过权限表控制用户对数据库的访问,权限表存放在mysq数据库中。MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表db表。 除此之外,还有table- priv表column_priv表roc_priv表等。在MySQL启动时,服务器将这些数据库表中权限信息的内容读入内存。
在这里插入图片描述

  • user表
    在这里插入图片描述
  • db表
    在这里插入图片描述
  • tables_priv表
    在这里插入图片描述
  • columns_priv表
    在这里插入图片描述
  • procs_priv表
    在这里插入图片描述

四、角色管理

角色是在MySQL 8.0中引入的新功能。在MySQL中 ,角色是权限的集合 ,可以为角色添加或移除权限。用户可以被赋予角色,同时也被授予角色包含的权限。对角色进行操作需要较高的权限。井且像用户账户一样,角色可以拥有授予和撤销的权限。
引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定,可以确保数据的安全性。这是至关重要的。

  • 创建角色
# host_name可省略,省略后默认为%
create role '角色名'@'host_name',['角色名'@'host_name'],...
# 如
create role 'manager'@'localhost';
  • 给角色赋予权限
grant 权限1,权限2,...,权限n on 表名 to '角色名'@'host_name'; 
#如
grant select,update on emp1 to 'manager'@'localhost';

-查看角色权限

show grants for '角色名'@'host_name';
  • 回收角色权限
revoke 权限1,权限2,...,权限n on 表名 from '角色名'@'host_name';
  • 删除角色
#如果你制除了角色,那么用户也就失去了通过这个角色所获得的所有权限。
drop role '角色名';
  • 给用户赋予角色
grant '角色名'@'host_name' [,'角色2',...] to user '用户名'@'host_name';
  • 激活角色

MySQL 中创建了角色之后,默认都是没有被激活,也就是不能用,必须要手动激活,激活以后用户才能拥有角色对应的权限。

#激活方式一:
set default role '角色名'@'host_name' to '用户名'@'host_name'[,'用户名'@'host_name',...];
#默认激活用户所有已拥有的角色如下:
set default role all to '用户名'@'host_name'[,'用户名'@'host_name',...];

#激活方式二:将系统变量activate_all_roles_on_login设置为on
set global activate_all_roles_on_login=on;

#激活角色后,用户需要重新登录,才能看到被赋予的角色
  • 查询用户当前角色
#如果角色末激活,结果将显示NONE
select current_role();
  • 撤销用户角色
revoke '角色名'@'host_name' from '用户名'@'host_name'
#撤销权限后,用户需要重新登录,撤销才会生效
  • 设置强制角色
#强制角色是给每个创建账户的默认角色,不需要手动设置。强制角色无法被REVOKE或者DROP

#1.服务器启动前设置:进入/etc/my.cnf中设置
[mysqld]
mandatory_roles='角色名@host_name[,角色名@host_name,...]'

#2.运行时设置
set persist mandatory_roles='角色名@host_name[,角色名@host_name,...]';#系统重启后仍然有效

set global mandatory_roles='角色名@host_name[,角色名@host_name,...]';#系统重启后失效

五、sql执行流程

在这里插入图片描述

1.查询缓存

MySQL拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式,被直接缓存在内存中。key 是查询的语句,value 是查询的结果。mysql8.0之后抛弃了查询缓存这个功能

  • 开启查询缓存(mysql8.0之前)
#系统变量query_cache_type有三个值,
#0代表关闭查询缓存(off);
#1代表开启(on);
#2代表按需使用(demand),当sql语句中有sql_cache关键词是才进行缓存
[mysqld]
query_cache_type=1
  • 查询缓存命中率
show status like '%Qcache%';
2.解析器

分析器先做“词法分析"。你输入的是由多个字符串和空格组成的一条SQL语句,MySQL 需要识别出里面的字符串分别是什么,代表什么。
接着,要做“语法分析"。根据词法分析的结果,语法分析器(比如: Bison) 会根据语法规则,判断你输入的这个SQL语句是否满足MySQL语法.
SQL语句正确则会生成一颗语法树

  • 解析过程
    在这里插入图片描述
3.优化器

在优化器中会确定SQL语句的执行路径。比如是根据全表检索,还是根据索引检索等。 经过了解析器,MySQL 就知道你要做什么了。在开始执行之前,还要先经过优化器的处理。“*一 条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。”
优化器优化完成后,会生成一个执行计划

4.执行器

在执行之前需要判断该用户是否具备权限。如果没有,就会返回权限错误。如果具备权限,就执行SQL查询井返回结果。在MySQL8.0之前的版本,如果设置了查询缓存,这时会将查询结果进行缓存。
如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,调用存储引学API对表进行读写。存储引擎API只是抽象接口,下面还有个存储引擎层。具体实现还是变看表选择的存储引擎

六、SQL执行原理

  • 开启profiling(分析)
#查看profiling是否开启,默认等于0,关闭状态
select @@session.profiling;
# 或
show variables like '%profiling%';

#开启profiling
set profiling=1;#系统关闭失效
  • 查看最近执行的命令的执行时间
show Profiles;

在这里插入图片描述

  • 查看最近执行的某一条命令的执行过程及执行的时间
show profile for query 7;

#查看最后执行的一条命令的执行过程及执行的时间
show profile;

在这里插入图片描述
在这里插入图片描述

mysql8.0后没有查询缓存的功能,所以执行一样的SQL语句不会进行缓存查询。

  • 查看所有的开销信息
show profile all for query 6;

语法:
SHOW PROFILE [type [,type]...] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]
type:
ALL: 显示所有参数的开销
BLOCK IO:显示IO相关开销
CONTEXT SWITCHES:上下文切换相关开销
CPU:显示CPU相关开销信息
IPC:显示发送和接收相关开销信息
MEMORY:显示内存相关信息开销
PAGE FAULTS:显示页面错误相关开销信息
SOURCE:显示和Source_function,Source_file,Source_line相关的开销信息
SWAPS:显示交换次数相关的开销信息
在这里插入图片描述

七、数据库缓存池(buffer pool)

InnoDB存储引擎是以为单位来管理存储空间的,我们进行的增删改查操作其实本质上都是在访问页面(包括读页面、写页面、创建新页面等操作)。而磁盘I/0需要消耗的时间很多,而在内存中进行操作,效率则会高很多,为了能让数据表或者索引中的数据随时被我们所用,DBMS 会申请占用内存来作为数据缓冲池,在真正访问页面之前,需要把在磁盘上的页缓存到内存中的Buffer Pool 之后才可以访问。
这样做的好处是可以让磁盘活动最小化,从而减少与磁盘直接进行I/0 的时间。要知道,这种策略对提升SQL语句的查询性能来说至关重要。如果索引的数据在缓冲池里,那么访问的成本就会降低很多。

缓冲池可以帮助我们消除CPU和磁盘之间的鸿沟。所以InnoDB存储引擎在处理客户端的请求时,当需要访问某个页的数据时,就会把完整的页的数据全部加载到内存中,也就是说即使我们只需要访问一个页的一条记录,那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了,在进行完读写访问之后并不着急把该页对应的内存空间释放掉,而是将其缓存起 来,这样将来有请求再次访问该页面时,就可以省去磁盘I0的开销了。

实际上,当我们对数据库中的记录进行修改的时候,首先会修改缓冲池中页里面的记录信息,然后数据库会以一定的频率刷新到磁盘上。注意并不是每次发生更新操作,都会立刻进行磁盘回写。缓冲池会采用一种叫做checkpoint的机制将数据回写到感盘上,这样做的好处就是提升了数据库的整体性能。

  • 查看/设置缓冲池的大小

如果使用的是MySQL MyISAM存储引擎,它只缓存索引,不缓存数据,对应的键缓存参数为key_buffer_size,可以用它进行查看。.

如果使用的是InnoDB存储引擎,可以通过查看innodb_buffer_pool_size变量来查看缓冲池的大小。

#查看缓冲池的大小
show variables like 'innodb_buffer_pool_size'; # 默认128M

#设置缓冲池的大小
set global innodb_buffer_pool_size = 大小;
#或者在/etc/my.cnf中的[server]下进行设置:
[server]
innodb_buffer_pool_size = 大小 #修改后重启mysql服务 
  • 设置多个Buffer Pool实例

Buffer Pool本质是InnoDB向操作系统申请的一块连续的内存空间,在多线程环境下,访问Buftr Pool中的数据都需要加锁处理。在Buffer Pool特别大而且多线程并发访问特别高的情况下,单一的Buffer Pool可能会影响请求的处理速度。所以在Bufter Pool特别大的时候,我们可以把它拆分成若干个小的Buffer Pool, 每个Buffer Pool都称为一个实例,它们都是独立的,独立的去申请内存空间,独立的管理各种链表。所以在多线程并发访问时并不会相互影响,从而提高并发处理能力。

#在/etc/my.cnf中的[server]下进行设置innodb_buffer_pool_instances的值来修改Buffer Pool实例的数量:
[server]
innodb_buffer_pool_instances = 数量
#查看Buffer Pool实例的个数
show variables like 'innodb_buffer_pool_instances';

八、存储引擎

MySQL中提到了存储引擎的概念。简而言之,存储引擎就是指表的类型。 其实存储引擎以前叫做表处理器,后来改名为存储引擎,它的功能就是接收上层传下来的指令,然后对表中的数据进行提取或写入操作

  • 查看存储引擎
show engines;
#或
show engines /G;
  • 查看系统默认的存储引擎
#mysql5.5之前默认存储引擎为MyISAM,5.5后改为InnoDB
show variables like '%storage_engines';
#或
select @@default_storage_engines;
  • 修改默认存储引擎
set default_storage_engine = 存储引擎名; #l临时修改,服务重启后失效

#修改my.cnf文件,修改后需要重启服务
default_storage_engine = 存储引擎名

  • 查看某个表的存储引擎
show create table 表名;
  • 修改某个表的存储引擎
alter table 表名 engine = 存储引擎名;
1. InnoDB存储引擎:具备外键支持功能的事务存储引擎
  1. mysql5.5后默认innodb引擎
  2. innodb是默认事务型引擎,支持事务
  3. 除了增加和查询外,还需要更新、删除操作,那么,应优先选择InnoDB存储引擎。
  4. InnoDB是为处理巨大数据量的最大性能设计.
  5. InnoDB支持行锁,操作时只锁某一行,不对其它行有影响。适合高并发的操作
  6. InnoDB写的处理效率差一些,井且会占用更多的磁盘空间以保存数据和索引。
  7. InnoDB不仅缓存索引还要缓存真实数据对内存要求较高,而且内存大小对性能有决定性的影响。
2.MyISAM引擎:主要的非事务处理存储引擎
  1. MyISAM提供了大量的特性,包括全文索引.压缩、空间函数(GIS)等, 但MyISAM 不支持事务、行级锁、外键,有一个毫无疑问的缺陷就是崩溃后无法安全恢复
  2. mysql5.5之前默认MyISAM存储引擎
  3. 优势是访向的速度快,对事务完整性没有要求或者以SELECT,INSERT为主的应用
  4. 针对数据统计有额外的常数存储。故而count(*)的查询效率很高
3.Archive引擎:用于数据存档
  1. archive是归档的意思,仅仅支持插入和查询两种功能(行被插入后不能再修改)
  2. 在MySQL5.5以后支持索引功能。
  3. 拥有很好的压缩机制,使用zlib压缩库。在记录请求的时候实时的进行压缩,经常被用来作为仓库使用
  4. 创建ARCHIVE表时,存储引擎会创建名称以表名开头的文件。数据文件的扩展名为.ARZ
  5. 根据英文的测试结论来看,同样数据量下,Archive表比MyISAM表要小大约75%,比支持事务处理的InnoDB表小大约83%。
  6. ARCHIVE存储引擎采用了行级锁。支持AUTO_INCREMENT列属性。 AUTO_INCREMENT列可以具有唯一索引或非唯一索引。尝试在任何其他列上创建索引会导致错误。
  7. Archive表适合日志和数据采集(档案)类应用:适合存储大量的独立的作为历史记录的数据。拥有很高的插入速度,但是对查询的支持较差
4.Blackhole引擎:丢弃写操作,读操作会返回空内容
  1. Blackhole引擎没有实现任何存储机制,它会丢弃所有插入的数据不做任何保存
  2. 但服务器会记录Blackhole表的日志,所以可以用于复制数据到备库,或者简单地记录到日志。但这种应用方式会碰到很多问题,因此并不推荐。
5.CSV引擎:存储数据时,一逗号分割各个数据项
  1. CSV引擎可以将普通的CSV文件作为MySQL的表来处理,但不支持索引
  2. CSV引擎可以作为一种数据交换的机制,非常有用。
  3. CSV存储的数据直接可以在操作系统里,用文本编辑器,或者excel读取。
  4. 对于数据的快速导入、导出是有明显优势的。
6.Memory引擎:置于内存的表

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

  1. Memory同时支持哈希(HASH) 索引B+树索引
  2. Memory表至少比MyISAM表要快一个数量级.
  3. MEMORY 表的大小是受到限制的。表的大小主要取决于两个参数, 分别是max_rowsmax_heap_table_size , 其中max_ rows可以在创建表时指定; max_heap_table_size的大小默认为16MB,可以按需要进行扩大。
  4. 数据文件与索引文件分开存储
  5. 数据易丢失,生命周期短。基于这个缺陷,选择MEMORY存储引擎时需要特别小心。
  • 使用场景
  1. 目标数据比较小, 而且非常频繁的进行访问,在内存中存放数据,如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小,限制Memory表的最大的大小。
  2. 如果数据是临时的,而且必须立即可用得到,那么就可以放在内存中。
  3. 存储在Memory表中的数据如果突然间丢失的话也没有太大的关系.
7. Federated引擎:访问远程表

Federated引擎是访问其他MySQL服务器的一个代理,尽管该引擎看起来提供了一种很好的跨服务器的灵活性,但也经常带来问题,因此默认是禁用的。

8. Merge引擎:管理多个MyISAM表构成的表集合
9. NDB引擎 :MySQL集群专用存储引擎

也叫做NDB Cluster存储引擎,主要用于MySQL Cluster 分布式集群环境,类似于Oracle的RAC集群。

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

The Mutents

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值