MySQL
SQL优化:
插入数据:
如果插入数据量比较大的时候我们最好使用批量插入, 一次插入500 - 1000条, 如果插入的数据量非常大, 几百万条, 我们就使用load指令插入
- 使用load指令插入100万条数据耗时20s, 而使用insert语句插入100万条数据需要10分钟
主键优化:
在InnoDB存储引擎中, 表数据都是根据主键顺序组织存放的, 这种存储方式的表称之为索引组织表(index organized table IOT)
如果我们插入数据的主键是无序的, 那么就有可能发生页分裂(页是一个逻辑区域, 存放的是我们的索引和数据(叶子结点), 或者索引(非叶子结点))
- 页分裂:
- 页可以为空, 也可以填充一般, 也可以填充100%, 每个页包含了2 到 N行数据(如果一行数据过大, 就会行移除), 根据主键排列
order by:
- Using filesort : 通过表的索引或者全表扫描, 读取满足条件的数据行, 然后在排序缓冲区sort buffer中完成排序操作, 而不是通过索引直接返回排序结果的排序都叫做FileSort排序
- Using index : 通过有序索引顺序扫描直接返回有序数据, 这种情况即为using index, 不需要额外排序, 操作效率高
要知道select是在order by之前执行的, 也就是会先进行查询, 如果查询的时候发生了回表查询, 那么我们肯定是要通过回表查询的结果去排序, 只有是覆盖索引的时候, 也就是不发生回表查询, 这个时候select执行时没有回表, 并且排序的时候直接根据对应覆盖索引的索引树排序即可
- 但是如果索引树顺序和倒序不能推导出我们想要的顺序, 将会一半是Filesort, 一般是index ----> 这一点刚开始要好好理解
补充:
FROM
<表名> # 笛卡尔积
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join...>
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>
- 其实，引擎在执行上述每一步时，都会在内存中形成一张虚拟表，然后对虚拟表进行后续操作，并释放没用的虚拟表的内存，以此类推。
具体解释：（注：下面“VT”表示 → 虚拟表 virtual ）
- from：select * from table_1, table_2; 与 select * from table_1 join table_2; 的结果一致，都是表示求笛卡尔积；用于直接计算两个表笛卡尔积，得到虚拟表VT1，这是所有select语句最先执行的操作，其他操作时在这个表上进行的，也就是from操作所完成的内容
- on: 从VT1表中筛选符合条件的数据，形成VT2表；
- join: 将该 join 类型的数据补充到VT2表中，例如 left join 会将左表的剩余数据添加到虚表VT2中，形成VT3表；若表的数量大于2，则会重复1-3步；
- where: 执行筛选，（不能使用聚合函数）得到VT4表；
- group by: 对VT4表进行分组，得到VT5表；其后处理的语句，如select，having，所用到的列必须包含在group by条件中，没有出现的需要用聚合函数；
- having: 筛选分组后的数据，得到VT6表；
- select: 返回列得到VT7表；
- distinct: 用于去重得到VT8表；
- order by: 用于排序得到VT9表；
- limit: 返回需要的行数，得到VT10；
group by :
在分组操作是, 可以通过索引来提高效率
分组操作时, 索引的使用也是满足最左前缀法则的
- 注意: where执行是在group by之前, having执行是在group by之后
limit :
优化思路 : 一般分页查询时, 通过该创建覆盖索引能够比较好的提升性能, 可以通过覆盖索引加子查询的形式进行优化
- 但是注意: 因为我们的子查询中使用了limit, 在mysql中如果子查询使用了limit, 那么此时就不能放到in()中了
- 但是我们可以将子查询的结果作为一个虚拟表然后进行一个联合查询
select s.* from tb_sku t, (select id from tb_sku order by id limit 2000000, 10) a where s.id = a.id;
- 注意 : 我们是使用的主键查询, 即使查询的是*, 这个时候还是覆盖索引情况, 因为我们的主键索引上挂的值就是对应行的记录
count() :
MyISAM引擎把一个表的总行数存在了磁盘上, 因此执行count(*)的时候会直接返回这个数值, 效率很高
- 但是不能加where条件
InnoDB引擎就麻烦了, 它执行count(*)的时候, 需要把数据一行一行的从引擎里面读出来, 然后计数
优化思路 : 自己计数
count的几种用法:
count()是一个聚合函数, 对于返回的结果集, 一行一行的判断, 如果count函数的参数不是NULL, 累计值就加1, 否则不加, 最后返回累计值
用法: count(*), count(主键), count(字段), count(1)
- count(主键) : InnoDB引擎会遍历整张表, 把每一行的主键id值都取出来, 返回给服务层, 服务层拿到主键之后, 直接按照行进行累加 —> 主键不可能为null, 所以不用判断
- count(字段) : InnoDB引擎会遍历整张表把每一行的字段值都取出来, 返回给服务层, 服务层判断是否为NULL, 不为NULL, 计数加1 —> 如果有not null 约束 :InnoDB引擎会遍历整张表把每一行的字段值都取出来, 返回给服务层, 直接按行进行累加 —> 有not null约束之后就和count(主键差不多)
- count(1) : InnoDB引擎遍历整张表, 但是不取值. 服务层对于返回的每一行, 放一个数字"1"进去, 直接按行进行累加
- count(*) : InnoDB引擎并不会把全部字段取出来, 而是专门做了优化, 不取值, 服务层直接按行进行累加
count(*)和count(1)都是不会取字段值, 直接统计, 但是count(1)要给每一行都添加一个1
所以按照效率排序 :
count(字段) < count(主键id) < count(1) 约小于 count(*)
- 所以我们尽量使用count(*)
update :
首先这里我们要提到InnoDB引擎的三大特性:
- 事物
- 外键
- 行级锁
这里我们的update语句的优化就与行级锁有关:
InnoDB的行锁是针对索引加的锁, 不是针对记录加的锁, 并且该索引不能失效, 否则就会从行锁升级为表锁
- 也就是我们更新数据的时候如果where后面的条件(字段)有索引, 那么对应update的记录就会加上行级锁, 但是如果where后面的条件(字段)没有建立索引, 那么就会为操作的这张表加上表锁
- 加上表锁之后锁的粒度上去了, 那么效率自然就下来了
- 行级锁的时候只要不是操作同一个记录就可以多个客户端同时操作, 但是变化成表锁之后只要是操作同一个表这个时候就要等待了, 如果一个客户端执行操作之后迟迟不提交事物, 那么另一个客户端就要一直等待对方释放表锁
- 加上表锁之后锁的粒度上去了, 那么效率自然就下来了
接下来要介绍的就是一些常常使用或者是听到的数据库对象了
- 前面我们学过的数据库对象就只有数据库表, 接下来我们会学习 :
- 视图
- 存储过程
- 存储函数
- 触发器
- 索引(索引我们前面已经是学习过了)
视图:
视图介绍及基本语法:
视图(View)是一种虚拟存在的表, 视图中的数据并不在数据库中实际存在, 行和列数据来自定义视图的查询中使用的表, 并且是在使用视图时动态生成的
通俗的讲, 视图只保存了查询的SQL逻辑, 不保存查询结果, 所以我们在创建视图的时候, 主要的工作就落在创建这条SQL查询语句上
- 可以理解为视图就是一个SQL语句, 每次我们查询视图的时候都是执行了对应的SQL语句
- 视图其实就是查询的结果, 也就是一张虚拟表
创建:
create [or replace] view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]
- 后面的with cascaded check option 或者with local check option是检查选项, 在修改, 插入等时候起作用, 因为会修改基表, 要对数据存储与显示一致性进行判断
- cascaded : 级联
- local : 本地, 局部的(此处应该翻译为局部的)
查询:
查看视图结构(其实就是查看视图创建语句) show create view 视图名称;
查询视图数据 : select * from 视图名称...;
- 视图是一个虚拟表, 所以我们可以看到, 以上两条语句其实和表(table)的语句一样, show create view(table)表示的是查询创建语句, select表示查询
修改:
方式一 : create or replace view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]
方式二 : alter view 视图名称[(列名列表)] as select语句 [with [cascaded | local] check option]
删除:
drop view [if exists] 视图名称 [,视图名称]...
- 可以一次删除多个视图
视图的检查选项:
当使用with check option子句创建视图时, mysql会通过视图检查正在更改的每个行, 例如插入, 更新, 删除, 以使其符合视图的定义. MySQL允许基于另一个视图创建视图, 它还会检查依赖视图中的规则以保持一致性, 它还会检查依赖视图中的规则以保持一致性. 为了确定检查的范围, mysql提供了两个选项: cascaded 和 local, 默认值为cascaded
cascaded : 按照视图的依赖关系往上走, 如果某个视图创建的时候加了with cascaded check option, 那么会为该视图依赖的视图也加上with cascaded, 然后一直往上加, 那么后面依赖的都会进行检查, 也就是判断视图与基表(创建视图依赖的表称之为基表)数据是否是一致的
- 如果不验证, 那么可能对视图进行插入操作之后视图中的数据没有变化, 但是基表中确实是插入了, 这就是一种问题
local : 按照视图的依赖关系往上走, 如果某个视图创建的时候加了with local check option, 那么会为该视图进行检查与基表是否能满足一致性, 但是并不会为该视图依赖的视图也加上with cascaded
注意: 对视图进行修改的时候不管有没有加检查选项, 都会向前检查修改的视图所依赖的所有视图
- 视图A是基于视图B建立的, 此时就说视图A依赖于视图B
- 视图不仅仅可以基于表(table)建立, 还可以通过视图建立
视图的更新:
要使视图可更新, 视图中的行与基础表中的行之间必须存在一对一的关系.
如果视图包含以下任何一项, 则视图不可更新:
- 聚合函数或者窗口函数(sum(), min(), max(), count()等)
- distinct
- group by
- having
- union 或者 union all
如果视图中的行和基础表中的行之间不是一对一关系, 此时虽然不能修改视图, 但是是可以修改基础表的, 并且基础表修改之后视图的数据会变化 —> 这个是必然的, 因为视图其实就是查询语句, 是动态的从基础表中查询出来的, 所以基础表变化之后视图也是跟着会变化的
视图的作用:
- 简单:
- 视图不仅仅可以简化用户对数据的理解, 也可以简化它们的操作, 哪些被经常使用的查询可以定义为视图, 从而使得用户不必为以后的操作每次执行全部的条件
- 安全:
- 数据库可以授权, 但是只能是授权到表级别, 不能授权到数据库特定的行和特定的列上. 通过视图用户只能查询和修改它们所能见到的数据
- 数据独立:
- 视图可以帮助用户屏蔽真实表结构变化带来的影响
- 假如表的字段名改变了, 但是到了视图中我们可以给视图中该字段添加一个别名, 别名就是原本的名字, 这样用户就感知不到真实表的变化了
- 视图可以帮助用户屏蔽真实表结构变化带来的影响
存储过程:
存储过程的介绍以及特点:
存储过程是事先经过编译并存储在数据库中的一段SQL语句的结合, 调用存储过程可以简化应用开发人员的很多工作, 减少数据在数据库和引用服务器之间的传输, 对于提高数据处理的效率是由好处的
- 比如 : 如果我们这个时候要执行一个查询, 一个修改, 一个添加, 传统做法就是在后端发送三个数据库请求, 但是学习了存储过程之后我们可以将这三个操作直接封装到一个存储过程中即可, 这样我们调用这个存储过程即可
存储过程思想上很简单, 就是数据库SQL语言层面的代码的封装与重用:
- 封装就是指的将我们的多条SQL语句封装为一个存储过程, 重用就是用的时候就直接调用即可, 就和java中的方法一样
存储过程特点:
- 封装, 复用
- 可以接收参数, 也可以返回数据 (但是都是通过参数完成, 没有返回值)
- 减少网络交互, 效率提升
存储过程的基本语法:
创建:
create procedure 存储过程名称([参数列表])
begin
-- sql语句
end;
调用:
call 存储过程名称([参数]);
查看:
select * from information_schema.routines where routine_schema = "xxx"; -- 查询指定数据库的存储过程以及状态信息
show create procedure 存储过程名称; -- 查询某个存储过程的定义
- 前面我们讲过, 数据库表, 视图也是可以使用show create 数据库对象 的方式查看创建该对象的SQL语句
删除:
drop procedure [if exists] 存储过程名称;
注意: 在命令行转给你, 执行创建存储过程的SQL时, 需要通过关键字delimiter指定SQL语句的结束符
- 因为命令行中默认是认为;就是一个SQL的结尾了, 就会结束, 但是往往存储过程中都是很多条SQL语句, 所以知道最终的end后面的;才应该结束, 所以我们在命令行中一般可以将结束符设置为&&
- delimiter : 分隔符, 定界符
变量之系统变量:
系统变量是MySQL服务器提供的, 不是用户定义的, 属于服务器层面. 分为全局变量(GLOBAL), 会话变量(SESSION)
- 全局变量 : 多个会话中共享的变量
- 会话变量 : 只在当前会话中共享的变量
- 一个连接就是一个会话
查看系统变量:
show [session | global] variables; -- 查看所有系统变量
show [session | global] variables like '....'; -- 可以通过like模糊匹配的方式查找变量
select @@[session | global].系统变量名; -- 查看指定变量的值
- like模糊匹配中%表示匹配任意个数字符, _表示匹配一个任意字符
- @[session | global].系统变量名 时如果我们没有指定级别, 那么就不用加.(点)并且默认为会话级别
- variables : 变量
设置系统变量:
set [session | global] 系统变量名 = 值;
set @@[session | global].系统变量名 = 值;
注意: 如果没有指定session/global, 默认是session, 会话变量
mysql服务重新启动之后, 所有设置的全局参数会是失效, 要是想要不是失效, 可以在/etc/my.cnf中配置(linux)
变量之用户定义变量:
用户定义变量 : 是用户根据需要自己定义的变量, 用户变量不用提前声明, 在用的时候直接用"@变量名"使用就可以. 其作用域为当前连接 --> 也就是用户定义变量的作用域为session
用户定义变量赋值:
set @var_name = expr [,@var_name = expr]...;
set @var_name := expr [,@var_name := expr]...;
select @var_name := expr [,@var_name :=expr]...;
selec 字段名 into @var_name from 表名;
- 我们可以一次为多个用户定义变量赋值, 之间使用,(逗号)隔开即可
- 注意 : set中使用:=和使用=的作用是一样的
用户定义变量的使用:
select @var_name;
注意: 用户定义的变量无序对其进行声明或者初始化, 不声明和初始化只不过获取到的值为null而已
=和:=的区别:
=和:=在set和update中是一样的, 表示的是赋值
=在select中表示的是比较运算符, 而=:在select中表示的也是赋值
变量之局部变量:
局部变量是根据需要定义的在局部生效的变量, 访问之前, 需要declare声明, 可用作存储过程内的局部变量和输入参数, 局部变量的范围是在其内声明的begin … end块
声明:
declare 变量名 变量类型[default ...];
变量类型将就是数据库字段类型 : int, bigint , char, varchar, date, time, datetime等
赋值:
set 变量名 = 值;
set 变量模拟鬼 := 值;
select 字段名 into 变量名 from 表名...;
存储过程参数:
| 类型 | 含义 | 备注 |
|---|---|---|
| in | 该类参数作为输入, 也就是需要调用时传入值 | 默认 |
| out | 该类参数作为输出, 也就是该参数可以作为返回值 | |
| inout | 既可以作为输入参数, 也可以作为输出参数 |
- in参数位置可以直接传入一个字面量, 但是out和inout位置不可以, out和inout位置最终都要将值赋给对应的参数作为输出参数值
用法:
create procedure 存储过程名称([IN/OUT/INOUT] 参数名 参数类型)
begin
-- SQL语句
end;
if:
语法 :
if 条件1 then
...
elseif 条件2 then
...
else
...
end if;
mysql中每个流程控制块有一个开始, 比如if, 也会有一个结束, 比如end if, 都是这样
case :
语法一:
case case_value
when when_value1 then statement_list1
[when when_value2 then statement_list2]...
[else statement_list]
end case;
语法二:
case
when search_condition1 then statement_list1
[when search_condition2] then statement_list2]...
[else statement_list]
end case;
语法二的when后面是condition, 也就是条件表达式, 哪个分支的条件表达式的执行结果是true, 就会执行哪个分支
while:
while循环是由条件的循环控制语句, 满足条件后, 再执行循环体中的SQL语句, 具体语法为:
#先判断条件, 如果条件为true, 则执行逻辑, 否则, 不执行逻辑
while 条件 do
SQL逻辑...
end while;
repeat:
repeat是有条件的循环控制语句, 当满足条件的时候退出循环. 具体语法为:
#先执行一次逻辑, 然后判断逻辑是否满足, 如果满足, 则退出, 如果不满足, 则继续下一次循环
repeat
SQL逻辑...
until 条件
end repeat;
repeat像do while, 但是不是, dowhile其实和while一样都是条件正确的时候循环, 但是repeat是条件不满足的时候循环
loop :
loop实现简单的循环, 如果不在SQL逻辑中增加退出循环的条件, 可以用其来实现简单的死循环. loop可以配合以下两个语句使用:
- leave : 配置循环使用, 退出循环
- ITERATE : 必须用在循环中, 作用是跳过当前循环剩下的语句, 直接进入下一次循环.
[begin_label:] LOOP
SQL逻辑...
end loop[end_label];
- begin_label和end_label是一样的, 是循环标识, 我们就是要通过该循环标识退出loop循环
- loop循环是一个无限执行的循环, 需要通过循环标识退出
leave label; -- 退出指定标记的循环体
iterate label; -- 直接进入下一次循环
cursor(游标) :
游标(cursor)是用来存储查询的结果集的数据类型, 在存储过程和存储函数中可以使用游标对结果集进行循环的处理. 游标的使用包括游标的1声明(declare), 2打开(open),3获取游标记录(fetch),4关闭游标(close), 具体语法如下:
声明游标:
declare 游标名称 cursor for 查询语句;
打开游标:
open 游标名称;
获取游标记录:
fetch 游标名称 into 变量[,变量];
- fetch : 获取
关闭游标:
close 游标名称;
举例在存储过程中使用游标:
create procedure p1(in uage int)
begin
declare uname varchar(100);
declare upro varchar(100);
#1. 声明游标
#这里一定要注意: 声明游标一定要声明在局部变量的后面, 否则创建该存储过程的时候就会报错
declare u_cursor cursor for select name, profession from tb_user where age <= uage;
-- 如果有这个表就将这个表现删除掉, 因为我们接下来又要创建
drop table if exist tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
#2. 打开游标
open u_cursor;
while true do
#3. 获取游标记录,将值封装到指定变量中, 下次循环的时候游标自动会转到下一行数据, 其实游标就相当于一个集合, 但是在循环中可以自动迭代遍历
fetch u_cursor into uname, upro;
insert into tb_user_pro value(null, name, upro);
end while;
#4. 关闭游标
close u_cursor;
end;
-- 测试
call p11(40);
- 我们测试的时候会报一个警告如下:
.png)
要想解决上述问题需要学习一个新的知识点: 条件处理程序
条件处理程序:
条件处理程序(Handler) 可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤. 具体语法为:
declare hanlder_action handler for condition_value [,condition_value]... statement;
handler_action
continue : 继续执行当前程序
exit : 终止执行当前程序
condition_value
sqlstate sqlstate_value: 状态码, 如02000(02000就是上面游标测试中出现的警告)
sqlwarning: 所有以01开头的sqlstate代码的简写
not found : 所有以02开头的sqlstate代码的简写
sqlexception : 所有没有被sqlwarning或者not found捕获的sql state代码的简写
存储函数:
存储函数是由返回值的存储过程, 存储函数的参数只能是IN类型的. 具体语法如下:
-
其实存储函数就和我们编程语言中的函数(方法)是比较相似的, 就是通过参数将外界数据传递进来, 然后通过返回值将最终结果返回回去
-
注意: 存储函数的类型是in类型的, 但是注意: 这个in是不需要我们指定的, mysql中如果我们指定函数的参数类型为in时会报错
注意: 在MySQL中如果定义存储函数就要给如下全局变量设置为true
SET GLOBAL log_bin_trust_function_creators = TRUE;
- log_bin_trust_function_creators 这个是MySQL的一个内置变量，从字面来说就是在binlog开启的状态下，系统需要信任生成函数，毕竟函数可以做得事情太多啦，系统默认在binlog开启的时候是关闭生成函数的使用的，万一，某天需要通过binlog日志回滚，而所有事务里就带有某些损害系统的函数，后果可能是灾难性的，因此，有这么一个变量。
- MySQL8.0二进制日志默认是开启的
创建存储函数:
create function 存储函数名称([参数列表])
return type [characteristic...]
begin
-- SQL语句
return ...;
end;
characteristic说明 :
deterministic: 相同的输入参数总是产生相同的结果
no sql: 不包含sql语句
reads sql data: 包含读取数据的语句, 但是不包含写入数据的语句
- 可以看到存储函数是要指定返回值类型的, 就和java中定义一个方法是一样的, 对于返回值是要定义返回值类型的
- 还有一个可选值[characteristic] --> 特性(特征行为)
- 但是注意: 如果我们使用的是MySQL8.0以上版本, 那么二进制日志是默认开启的, 二进制日志开启的时候创建存储函数必须要指定特性, 如果不指定创建存储函数时会直接报错
存储函数的调用:
存储函数不是使用call来调用的, 存储函数也不是像java方法一样调用, 因为存储函数是一定有返回值的, 所以我们需要接受该返回值, 比如以下格式:
select fun1(); -- 这里的fun1()就是一个存储函数
- 注意: 调用函数的规范方式其实应该是select fun1() from dual; 但是在mysql中我们使用上述不加虚拟表的方式也是可以的, 但是oracle中是不支持直接select fun1();的, 因为查找的时候肯定是要从一个表中查询
触发器:
注意: 触发器和对触发器表执行对应的触发操作是在同一个事物中执行的
- 触发器表 : 一个表添加了触发器之后这个表就被称之为触发器表
触发器可以用于数据变更
- 变更和修改不同, 修改不同备份原数据, 但是变更需要备份原数据
触发器是与表有关的数据库对象, 指在insert/update/delete之前或者之后, 触发并执行触发器中定义的SQL语句集合. 触发器的这种特性可以协助应用在数据库端保证数据的完整性, 日志记录, 数据校验等操作
- 就是一旦达到触发某个触发器的条件, 这个时候就会执行触发器中的SQL语句
使用别名 old 和 new 来引用(调用)触发器中发生变化的记录内容, 这与其他数据库是相似的. 现在MySQL中触发器还只支持行级触发, 不支持语句级触发
- oracle中是支持语句级触发器的
- 行级触发器 : 比如一个update语句修改了6行数据, 这个时候如果是行级触发器就会执行6次
- 语句级触发器 : 比如一个update语句修改了6行数据, 这个时候如果是语句级触发器就只会执行一次, 因为只执行了一个SQL语句
| 触发器类型 | new 和 old |
|---|---|
| insert型触发器 | new表示将要或者已经新增的数据(insert没有old, 想一想就能明白, insert的old如果有能是什么? 插入之前的什么?) |
| update型触发器 | old表示修改之前的数据, new表示将要或者已经修改后的数据 |
| delete型触发器 | old表示将要或者已经删除的数据(delete中没有new, 也是想一想就能明白, 不要死记硬背, 删除之后能有什么?) |
触发器语法:
创建:
create trigger trigger_name
before/after insert/update/delete
on tbl_name for each row -- for each row : 行级触发器(MySQL中只支持行级触发器, 所以这里直接写死)
begin
trigger_stmt;
end;
- 注意: delete也是可以指定after的, 不是只能指定before, 不要认为delete只有old就要指定为after, 同理insert也是一样的
查看:
show triggers;
- 查看的是当前数据库中有哪些触发器, 触发器的执行逻辑是什么, 监控的是哪张表, 是之前还是之后触发等等
删除:
drop trigger [schema_name.]trigger_name;
- schema_name指定数据库, 如果没有只递进schema_name, 默认为当前数据库
锁 :
锁的介绍:
锁是计算机协调多个进程或线程并发访问某一资源的机制, 在数据库中, 除传统的计算机资源(CPU, RAM, I/O)的争用以外, 数据也是一种供许多用户共享的资源. 如何保证数据并发访问的一致性, 有效性是所有数据库必须解决的一个问题, 锁冲突也是影响数据库并发访问性能的一个重要因素. 从这个角度来说, 锁对数据库而显得尤其重要, 也更加复杂
锁的分类:
mysql中的锁, 按照锁的粒度分, 分为以下三类:
- 全局锁 : 锁定数据库中的所有表
- 表级锁 : 每次操作锁住整张锁
- 行级锁 : 每次操作锁住对应的行数据
注意 : 表锁不完全等于表级锁, 表锁只是表级锁的一种, 行锁不完全等于行级锁, 行锁只是行级锁的一种
全局锁 :
全局锁就是对整个数据库实例加锁, 加锁后整个实例就处于只读状态, 后序的DML的写语句, DDL语句, 已经更新操作的事物提交语句都将被阻塞
其典型的使用场景是做全库的逻辑备份, 对所有的表进行锁定, 从而获取一致性视图, 保证数据的完整性
上图就是全库逻辑备份的一个案例, 上图是没有加全局锁的情况, 这个时候明显是有问题的, 我们可以看到备份的库存没有扣减, 但是生成了对应的订单, 并且也插入了订单日志, 并且备份到了订单备份和订单日志备份中, 所以备份的数据是不一致的
加了全局锁之后, 加锁之后我么使用mysql提供的mysqldump工具进行数据备份, 备份期间只能对库中数据进行DQL(查询), 不能DML(操作), 也不能DDL(修改数据库逻辑)
最终的时候我们拿到备份的xxx.sql文件就是没有问题的
语法:
加全局锁 :
- 注意 : MySQL为我们提供了一个全局读锁, 语法如下:
flush tables with read lock;
- 加了读锁之后其他客户端只能进行读操作, 不能进行写操作
解全局锁:
unlock tables;
注意: 加了这个全局读锁之后就所有客户端都只能进行读操作, 包括加锁的客户端也是只能进行读操作不能进行修改的操作(包括DML和DDL)
但是如果我们使用上述全局锁其实也是有缺点的:
数据库中加全局锁, 是一个比较重的操作, 存在以下问题:
- 如果在主库上备份, 那么在备份期间都不能执行更新, 业务基本就要停摆
- 如果在从库上备份, 那么在备份期间从库不能执行主库同步过来的二进制日志(binlog), 会导致主从复制延迟
- 如果想要了解主从复制可以看我关于主从复制的笔记
在InnoDB引擎中, 我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份
mysqldump --single-transaction -u账号 -p密码 ffyc > ffyc.sql
- ffyc是要备份的数据库名称, ffyc.sql就是备份文件名称
- 加了 --single-transaction 参数之后是通过快照读来完成数据备份的
表级锁:
表级锁介绍:
表级锁, 每次操作锁住整张锁. 锁定粒度大, 发生锁冲突的概率最高, 并发度最低, 应用在MyISAM, InnoDB, BDB等存储引擎中
对于表级锁, 主要分为以下三类:
- 表锁
- 元数据锁(meta data lock, MDL)
- 意向锁
表锁:
对于表锁分为两类:
-
表共享读锁(read lock)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-itMgrc81-1687791541435)(E:\非凡英才\数据库笔记\进阶篇\锁\图解\表共享读锁.png)]
- 可以看到表共享读锁的特点就是所有客户端(包括建立锁的客户端)都不能修改(DML, DDL)锁住的表, 只能执行读操作
-
表独占写锁(write lock)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZxCnIUDk-1687791541436)(E:\非凡英才\数据库笔记\进阶篇\锁\图解\表独占写锁.png)]- 可以看到表独占写锁的特点是只有加锁的客户端可以读操作也可以写操作, 但是其他客户端即不能读也不能写
语法:
-
加锁:
lock tables 表名... read/write -
释放锁:
unlock tables /(或者) 客户端断开连接
元数据锁:
元数据锁 : meta data lock, 简称MDL
MDL加锁过程是系统自动控制, 无需显示使用, 在访问一张表的时候会自动加上. MDL锁主要作用是维护表元数据的数据一致性 (其实就是维护表结构的数据一致性), 在表上有活动事物的时候, 不可以对元数据进行写入操作 (DML会开启事物, 也就是不用需DML与DDL一起执行, 防止结构与数据不一致). 其实就是为了避免DML与DDL冲突, 保证读写的正确性
在MySQL5.5中引入MDL, 当对一张表进行增删改查的时候, 加MDL读锁(共享); 当对表结构进行变更操作的时候, 加MDL写锁(排它锁)
| 对应SQL | 锁类型 | 说明 |
|---|---|---|
| lock tables xxx read / write | shared_read_only / shared_no_read_write | |
| select, select … lock in share mode | shared_read | 与shared_read, shared_write兼容, 与exclusive互斥 |
| insert, update, delete, select … fro update | shared_write | 与shared_read, shared_write兼容, 与exclusive互斥 |
| alter table … | exclusive | 与其他的MDL都互斥 |
注意: shared_write是共享写, 但是它是一个共享锁, 而大多数人喜欢将共享锁称之为读锁, 这一点刚刚开始可能不是很容易接受
- 这里为什么是读锁? 因为他是一个表锁, 所以就有可能两次修改, 两个写操作不是针对同一行数据, 所以它没有必要互斥的好吧
- 但是修改同一行数据的时候会互斥, 是因为修改的时候不仅仅加了元数据共享锁(表级锁), 还会添加行级互斥锁, 所以已经有了行级互斥锁了, 对于同一记录就不能加互斥锁了, 对于同一张表也就不能加互斥锁了, 这里比较难以理解
意向锁:
- 意向锁是加行锁的时候顺便就会加, 就是为了保证行锁和表锁的分离, 但是作为表级锁的意向锁自身可以和行锁一起使用, 就是为了优化行锁和表锁的关系, 方便管理
- 意向共享锁(IS) : 与表锁共享锁(read)兼容, 与表锁排它锁(write)互斥
- 意向排它锁(IX) : 与表锁共享锁(read)和表锁排它锁(write)都互斥
意向所之间不互斥
可以通过如下SQL, 查看意向锁以及行锁的加锁情况:
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;
行级锁:
行级锁, 每次操作锁住对应的行数据. 锁定粒度最小, 发生锁冲突的概率最低, 并发度最高. 应用在InnoDB存储引擎中
InnoDB的数据是基于索引组织的, 行锁是通过对索引上的索引项加锁来实现的, 而不是对记录加的锁. 对于行级锁, 主要分为以下三类:
- 行锁(Record Lock) : 锁住单个行记录的锁, 防止其他事物对此进行update和delete. 在RC(读已提交), RR(可重复读)隔离级别下都支持
- lock_rec_not_gap
- 间隙锁(Gap Lock) : 锁定索引记录间隙(不含该记录), 确保索引记录间隙不变, 防止其他事物在这个间隙进行insert, 产生幻读. 在RR(可重复读)隔离级别下支持
- lock_gap
- 临键锁(Next-Key Lock) : 行锁和间隙锁的组合, 同时锁住数据, 并锁住数据前面的间隙Gap. 在RR(可重复读)隔离级别下支持
- lock_ordinary
行锁:
innoDB实现了以下两种类型的行锁:
- 共享锁(S) : 允许一个事物去读一行, 阻止其他事物获得相同数据集的排它锁
- 排它锁(X) : 允许获取排它锁的事物更新数据,阻止其他事物获得相同数据集的共享锁和排它锁
也就是事物A获取到这一行的共享锁之后另一个事物B中也是可以获取到共享锁, 但是获取不到排它锁
select是不加行锁的
select … lock in share mode加共享行锁
其他关于DML的都是加排他行锁
- 那么也就是如果一个事物对一行数据修改的时候另一个事物不能修改这一行数据
默认情况下, InnoDB在repeatable read事物隔离级别运行, innoDB使用next-key锁进行搜索和索引扫描, 以防止幻读
- 针对唯一索引进行检索时, 对已存在的记录进行等值匹配时, 将会自动优化为行锁
- InnoDB的行锁是针对于索引加的锁, 不通过索引条件检索数据, 那么InnoDB将对表中的所有记录加锁, 此时就会升级为表锁
- 不使用索引列作为条件, 那么就加不了行锁, 因为行锁是加到索引上的
可以通过如下SQL, 查看意向锁以及行锁的加锁情况:
select object_schema, object_name,index_name, lock_type,lock_mode,lock_data from performance_schema.data_locks;
间隙锁 & 临键锁:
默认情况下, InnoDB在repeatable read事物隔离级别运行, InnoDB使用next_key锁进行搜索和索引扫描, 以防止幻读
- 索引上的等值查询(唯一索引), 给不存在的记录加锁时, 优化为间隙锁
- 索引上的等值查询(普通索引), 向右遍历时最后一个值不满足查询需求时, next-key lock退化为间隙锁
- 索引上的范围查询(唯一索引) – 会访问到不满足条件的第一个值为止
注意: 间隙锁唯一目的是防止其他事物插入间隙. 间隙锁可以共存, 一个事物采用的间隙锁不会阻止另一个事物在同一间隙上采用间隙锁
MySQL中开启事物的时候默认是会提交上一个事物的, 也就是MySQL中是不支持事物嵌套的
最后, 本篇文章对于内容描述比较简化, 不够详细, 如果想详细了解, 可以查看我关于对应某个专题的详细笔记, 比如数据库锁专门的笔记
363
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
