MySQL——进阶篇

2023-12-25 20:28:36

二、进阶篇🚩

1. 存储引擎🍆

1.1 MSQL体系结构

在这里插入图片描述

连接层: 连接处理,连接认证,每个客户端的权限

服务层: 绝大部分核心功能,可跨存储引擎

可插拔存储引擎: 需要的时候可以添加或拔掉,数据存储和提取的方式,数据库服务器通过API与其进行交互。**索引在这一层实现,不同的存储引擎有不同的索引实现方式。**InnoDB是MySQL5.5之后默认的存储引擎。

存储层: 数据存储在磁盘文件中

1.2 存储引擎

1.2.1 简介

发动机,不同的场景需要不同的引擎。**存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。**存储引擎是基于表的,不是基于库的,也可被称为表类型。

# 在创建表时指定存储引擎
creat table 表名(
 字段名 字段类型 [comment 字段注释],
 ……
 字段名 字段类型 [comment 字段注释]
)ENGINE = INNODB [comment 表注释];

# 查询当前数据库支持的存储引擎
SHOW ENGINES;
1.2.2 常见的存储引擎

InnoDB

  • 1)介绍:兼顾高可靠和高性能的通用存储引擎

  • 2)特点

    • DML操作遵循ACID模型,支持事务
    • 行级锁 ,提高并发访问性能
    • 支持外键 FOREIGN KEY约束,保证数据
  • 3)文件:文件名.ibd,每张表(参数:innodb_file_per_table)都会有一个表空间文件,存储表结构(frm,sdi)、数据和索引。

  • 在这里插入图片描述

  • 4)逻辑结构:表空间(Tablespace)-段(Segment)-区(Extent)-页(page)-行(Row);page是磁盘操作的最小单元

    在这里插入图片描述

MYISAM

  • 1)介绍:MYISAM早期MySQL的默认存储器
  • 2)特点:
    • 不支持事务
    • 表锁,不支持行锁
    • 不支持外键
  • 3)文件:
    • 文件名.sdi:表结构
    • 文件名.MYD:数据
    • 文件名.MDI:索引

MEMORY

  • 1)介绍:存储在内存中,会受到硬件、断电问题的影响。只能将这些表作为临时表或缓存使用
  • 2)特点:
    • 内存存放,速度快
    • hash索引
  • 3)文件:
  • 文件名.sdi
1.2.3 存储引擎的选择

根据特点选;对于复杂系统,可以使用多种存储引擎进行组合

InnoDB: 要求并发、数据操作有插入、查询外,还有很多更新、删除操作

MyISAM: 主要是读和插入数据——评论等——MongoDB代替

MEMORY: 访问速度快,太大的不能缓存在内存中,且无法保障数据的安全性——Redis代替

2. 索引🥕

2.1 索引概述

概念: 索引是一种数据结构,是帮助MySQL高效获取数据的

  • 无索引——全表扫描
  • 有索引——查找更高效

优点:

  • 提高数据检索的效率,降低数据库的IO成本
  • 通过索引列对数据进行排序,降低数据排序成本,降低CPU消耗

缺点:

  • 占用空间
  • 降低更新表的速度,对表进行插入、更新、删除时,效率低

2.2 索引结构

不同的存储引擎有不同的结构

  • B+Tree索引:最常见,大部分引擎都支持——InnoDB、MyISAM
  • Hash索引: 底层是哈希表实现,只有精确匹配索引列的查询才有效,不支持范围查询——Memory
  • R-tree(空间索引):MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型
  • Full-text(全文索引):通过建立倒排索引,快速匹配文档的方式

1)B+Tree:所有元素会出现在叶子节点,形成链表,非叶子节点只是起索引作用;MySQL中进行了优化,是双向循环链表

2)Hash:采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中

注:InnoDB中有自适应hash功能,自动根据B+Tree索引构建hash索引

2.3 索引分类

分类

分类含义特点关键字
主键索引针对表中主键创建的索引默认自动创建,只能有一个PRIMARY
唯一索引避免同一个表中某列数据中的值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引找文本中的关键词可以有多个FULLTEXT

InnoDB中的两种索引

聚集索引: 索引结构的叶子节点保存了行数据——必须有,而且只有一个

二级索引: 数据与索引分开存放,索引结构的叶子节点关联的是对应的主键——可以存在多个

回表查询——根据查询条件,在二级索引找到查询的索引值,回到聚集索引中找到整行数据的信息

2.4 索引语法

# 创建索引
CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name, ……);  -- 索引列可以是单列也可以是多列,多列就是联合索引
-- 索引名称:idx_表名_列名

# 查看索引
SHOW INDEX FROM table_name;  -- 指定表的所有索引

# 删除索引
DROP INDEX index_name ON table_name;

2.5 SQL索引性能分析

主要是查询

1)SQL执行频率——查看表中哪种操作的频率高

show global status like 'Com_______';

在这里插入图片描述

2)慢查询日志

定位哪些SQL语句的执行效率低,从而对这类型语句进行优化

在这里插入图片描述

慢查询日志在/var/lib/mysql/localhost-slow.log

慢查询日志配置:

  • 慢查询日志默认没有开启,配置文件存放在/etc/my.cnf中

  • systemctl restart mysqld

  • # 开启MySQL慢日志查询开关
    slow_query_log=1
    # 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
    long_query_time=2
    

注:Ubuntu中

启动MySQL:service mysqld start

停止MySQL:service mysqld stop

重启MySQL:service mysqld restart

开启慢查询:set global slow_query_log=ON;——临时开,避免在配置文件中进行修改

3)profile详情

# 在做SQL优化的时候帮助了解时间都耗费在哪里
show profiles;  
# 查看当前MySQL是否支持profiles
SELECT @@have_profiling;

SELECT @@profiling;  # 0没有开关

# 开启
set profiling = 1;
SELECT @@profiling;  # 1

# 查看执行的SQL语句的耗时情况
show profiles;

# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile fpr query query_id;

# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

4)explain执行计划

explain/desc命令获取MySQL如何执行SELECT语句的信息,包括select语句执行过程中表如何连接和连接的顺序

# 直接在select语句之前加上关键字explain / desc
explain select 字段列表 from 表名 where 条件;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

2.6 索引使用

-----where之后的-----

1)最左前缀法则——联合索引(复合索引)

查询从索引的最左列开始,并且不跳过索引中的列;如果跳过某一列,索引将部分失效(后面的字段索引失效)——创建的时候最左边的字段,只要存在就会启动索引,不考虑在selecet中字段排列的前后顺序

2)范围查询

出现>,<这样的范围查询,后面的列索引将会失效。可以使用>=或<=

3)索引列运算

不要再索引列上进行运算操作,否则索引将失效

4)字符串不加引号

字符串不加单引号,存在隐式类型转换——自动类型转换之后,索引就失效了

5)迷糊查询

如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效

6)or连接的条件

or前后连接的都有索引,索引才会被用到;否则涉及的索引都不会被用到

7)数据分布影响

如果MySQL评估使用索引比全表更慢,则不使用索引

-----from之后的-----

8)SQL提示——操作者提示SQL

在SQL语句中加入一些人为的提示来达到优化操作的目的

# 使用索引
explain select * from 表名 use index(索引名) where 条件判断
# 忽略索引
explain select * from 表名 ignore index(索引名) where 条件判断
# 强制索引
explain select * from 表名 force index(索引名) where 判断条件

-----select之后的-----

9)覆盖索引

查询使用了索引,并且需要返回的列在该索引中已经全部能够找到,减少select*

会在explain的最后一列Extra中显示——正常没有使用条件中包含索引的列,会显示Using where; Using index,性能比较高(不需要回表查询);否则出现Using index condition性能比较低(回表查询了);NULL是回表查询

select * 减少使用,极易出现回表查询,降低性能

10)前缀索引——空间和效率的平衡

如果字符串类型很长的时候,直接建立索引会变得非常大,查询得时候会浪费大量得磁盘IO,影响查询效率。——只将字符串得一部分前缀建立索引,节约索引空间,提高索引效率

# 前缀的长度可以根据索引的选择性来决定,而选择性是指不重复的索引值和数据表的记录总数的比值,索引的选择性越高则查询效率越高
# 不重复数/总数=选择性
# 唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
create index 索引名 on 表名(column(n));

11)单列索引和联合索引的选择

看select需要的数据,如果只要一列,那单列索引就可以,否则还需要回表查询,就转战联合索引。

2.7 索引设计原则

1)针对数据量大(百万级),且查询比较频繁的表建立索引

2)针对常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引

3)尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高

4)如果是字符串类型的字段,字段的长度较长,可以针对字段的特点,建立前缀索引

5)尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表

6)控制索引的数量,数量越多,维护索引结构的代价就越大,会影响增删改的效率

7)如果索引列不能存储NULL,要在见表的时候使用NOT NULL进行约束。优化器知道每列是否包含NULL,可以更好地切丁哪个索引最有效地用于查询

3. SQL优化🌽

3.1 插入数据

传统一条一条插入

1)批量插入:500-1000条一次

2)手动提交事务

3)主键顺序插入

4)大批量插入数据——insert性能较低,可以使用MySQL数据库提供地load指令进行插入

# 客户端连接服务器时,加上参数--local-infile
mysql --local-infile -u root -p;

# 设置全局参数local-infile为1,开启从本地加载文件导入数据地开关
set global local_infile = 1;

# 执行load指令将准备好地数据,加载到表结构中
load data local infile '/root/sql/tb_sku1.sql(数据源地址)' into table `tb_sku(表明)` fields terminated by ',' lines terminated by '\n';

3.2 主键优化

顺序插入比乱序插入(页分裂,删除数据时页合并)快

InnoDB存储引擎中数据组织方式:按主键顺序组织存放,这种存储方式地表称为索引组织表(index organized table, IOT)

主键设计原则:

1)在满足业务需求的情况下,尽量降低主键的长度

  • 主键作为二级索引的叶子节点,如果太长会占用空间
  • 搜索时耗费磁盘IO

2)插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键

3)尽量不要使用UUID(JAVA中生成随机字符串的一个类方法)做主键或者其他自然主键

4)业务操作时,避免对主键的修改

3.3 order by 优化

分类:

1)Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作——通过索引不直接返回排序结果——>创建联合索引,防止索引失效——>创建索引的时候可以指定排序方式

2)Using index:通过有序索引顺序扫描直接返回有序数据——不需要额外排序

优化:

1)根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则

2)尽量使用覆盖索引

3)多字段排序,一个升序一个降序,可以在联合索引创建时建立规则(ASC/DESC)

4)如果不可避免出现filesort,大量数据排序时,可以适当增大排序缓冲区的大小sort_buffer_size(默认256K)

3.4 group by 优化

在分组操作时,可以通过索引来提高效率

分组操作时,索引的使用也满足最左前缀法则

3.5 limit 优化

问题: 大数据量的时候,越往后,效率越低

优化:

覆盖查询——避免回表

+

子查询形式——其实也是在回表阶段给简化了

3.6 count 优化

问题: InnoDB引擎执行count(*)的时候,需要把数据一行一行地从引擎中读出来,然后累积计数

注: MyISAM引擎把一个表地总行数存在了磁盘上,没有where条件地时候,就会直接返回这个数,效率很高。

优化思路: 自己计数,在Redis中保存一个数来记录

count的几种用法:

count()会一行一行数据进行判断,如果count函数的参数不是NULL,累计值就加1,否则不加

用法:

count(*) ——总记录数,不取值直接累加

count(主键) ——直接累加,不用判断null

count(字段) ——没加NOT NUL约束,如果NULL不加

count(1) ——InnoDB引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字“1”,直接按行进行累加。

优化: count(*) ~ count(1) > count(主键id) > count(字段)

3.7 update 优化

没有索引就会锁住整张表

优化: 根据索引字段进行更新,并且索引字段不能失效,否则行锁就会升级为表锁,影响并发性能

4. 视图/存储过程/触发器🥬——存储对象

4.1 视图

概念: 虚拟存在的表(可以像操作表一样对视图进行操作),使用视图时是动态生成的——视图只保存了查询的SQL逻辑,不保存查询结果。

操作:

# 创建视图
create or replace view 视图名[(列名列表)] as select …… [with[cascaded|local] check option];
# [with[cascaded|local] check option]
# 如果加了检查语句,通过检查语句的记录才会被添加;如果不加检查语句,任意一条记录都可以被添加

# 查询
-- 查看创建视图语句
show create view 视图名称;
-- 查看视图数据
select * from 视图名称;

# 修改
-- 新建或者替换
create or replace view 视图名称[(列名列表)] as select语句 [with[cascaded|local] check option];
-- 真正的修改
alter view 视图名称[(列名列表)] as select语句 [with[cascaded|local] check option];

# 删除
drop view [if exists] 视图名称 [,视图名称] ……

视图的检查选项:

  • cascaded(级联): 创建视图时如果指定了cascaded选项,这个视图基于的视图都会被检查;基于创建了检查选项的视图创建的心视图,如果没有设置检查选项,本条不会被检查。传递的——with cascaded check option == with check option
  • local: 创建视图时如果指定了local选项,这个视图基于的视图如果定义了检查选项,那就检查一下,如果没有定义那就不检查了——不传递,不会把自己的带给自己基于的视图

视图的更新:——修改视图会对原表的数据进行修改

  • 条件: 视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项,视图不可更新:①聚合函数/窗口函数(SUM()、MIN()、MAX()、COUNT());②Distinct;③Group by;④having;⑤union或union all

视图作用:

  • 简单: 经常使用的查询可以定义为视图,之后的操作每次就不用指定全部的条件了
  • 安全: 数据库不能授权到特定的行/列上。视图可以让用户看到只让他们查询或修改的数据
  • 数据独立: 屏蔽基表的结构变化对查询带来的影响

4.2 存储过程

概念: 多次操作数据库,涉及多次网络请求——>在MySQL中将多个操作放在一个集合中,用的时候去调这个集合(数据库中可以有很多个这样的集合),减少数据在数据库和应用服务器之间的传输,提高数据处理的效率。

缺点: 存储过程难以调用和扩展,更没有移植性

特点:

  • 封装、复用
  • 可以接收参数,也可以返回数据
  • 可以见减少应用服务和数据库之间的网络交互,提升效率

存储过程:

# 创建
create procedure 存储过程名称([参数列表])
begin
	--SQL语句
end;

# 在命令行下创建存储过程时,要通过关键字delimiter指定SQL语句的结束符
-- 因为SQL语句中包含分号,命令行会以为输入结束,但实际创建指令还没输入完成
delimiter $$  -- 会以$$为结束符
create procedure cout_user ()
begin
    select count(*) from user;
end$$

# 调用
call 名称([参数]);

# 存储过程——计算user表中的总记录数
create procedure cout_user ()
begin
    select count(*) from user;
end;

-- 调用
call cout_user();

-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'test';

-- 查看定义存储过程时的SQL语句
show create procedure cout_user;

-- 删除
drop procedure if exists cout_user;

存储过程中的语法

1)变量

系统变量: MySQL服务器提供的,不是用户定义的。分为全局变量(GLOBAL)、会话变量(SESSION)

# 查看
show [session|global] variables;  -- 查看所有系统变量,默认是session。session就是本次会话,global就是其全部会话,但是重启后就回复默认,永久修改要修改配置文件
show [session|global] variables like '……';  -- 可以通过LIKE模糊匹配的方式查找变量
select @@[session|global] 系统变量名;  -- 查看指定变量的值

# 设置系统变量
set [session|global] 系统变量名 = 值;
set @@[session|global]系统变量名 = 值;

用户定义变量: 不用提前声明,在用的时候直接’@变量名‘使用,作用域为当前连接

# 赋值
-- 赋值
set @myname = 'xy';
set @myage := 18;  # 推荐使用,与比较运算符==区分
set @mygender = '女', @myhobby := 'java';

select @mycolor := 'blue';
select count(*) into @mycount from user;

-- 使用
select @myname, @myage, @mygender, @myhobby;

select @mycolor, @mycount;

局部变量: 根据需要定义的在局部生效的变量,访问前,需要declare声明。可用作存储过程内的局部变量和输入参数,作用范围是begin……end

create procedure p1()
begin
    # 声明
    declare stu_count int default 0;
    # 赋值
    set stu_count := 100;
    select count(*) into stu_count from user;
    select stu_count;
end;

call p1();

2)if判断

if 条件1 then
	……
elseif 条件2 then
	……
else
	……
end if;

3)参数

# 参数类型
-- IN 该类参数作为输入,也就是需要调用时传入值
-- OUT 该类参数作为输出,也就是该参数可以作为返回值
-- INOUT 既可以作为输入参数,也可以作为输出参数

# 用法
create procedure 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
begin
	-- SQL语句
end;

4)case

# 语法一
case case_value
	when 条件一 then ……
	else ……
end case;

# 语法二
case
	when 条件一 then ……
	else ……
end case;

5) while

# 语法
# 先判断条件,如果条件为true,则执行逻辑,否则,不执行逻辑
WHILE 条件 DO
	SQL逻辑
END WHILE;
-- 计算1-n的和
create procedure p(in n int)
begin
    declare total int default 0;
    while n > 0 do
        set total := total + n;
        set n := n - 1;
    end while;
    select total;
end;
call p(100);

6)repeat——满足条件则退出循环

repeat
	SQL逻辑
	until 条件
end repeat;

7)loop

[begin_label:] loop

	SQL逻辑

end loop [end_label];
LEAVE label;  -- 退出指定标记的循环体(相当于break)
ITERATE label;  -- 直接进入下一次循环(相当于continue)

8)游标

概念: 用来存储查询结果集的数据类型 ,在存储过程和函数中可以使用游标对结果集进行循环处理。(类似集合、list)

内容: 声明、OPEN、FETCH、CLOSE

# 声明游标 注:先声明普通变量,再声明游标
declare 游标名称 cursor for 查询语句;  -- 将查询语句的结果存在游标中

# 打开游标
open 游标名称;

# 获取游标记录
fetch 游标名称 into 变量 [, 变量];  -- 将游标中包含的多条数据记录,每次提取一条(配合while使用)

# 关闭游标
close 游标名称;

9)条件处理程序

用来定义再流程控制结构执行过程中遇到问题时相应的处理步骤

# 声明
declare handler_action handler for condition_value [, condition_value] … statement;

/*
handler_action
	continue 继续执行当前程序
	exit 终止执行当前程序
condition_value
	sqlstate '状态码'
	sqlwarning 所有以01开头的状态码的简写
	not found 所有以02开头的状态码的简写
	sqlexception 没有被以上两种捕获的状态
*/

10)存储函数——存储过程的特殊情况,参数为in,必须有返回值,独特的是他有自己的语法

create function 存储函数名称([参数列表])
returns type [characteristic…]
begin
	SQL语句
	return……;
end;

/*
characteristic说明——必须有,否则会报错
deterministic:相同的输入参数总是产生相同的结果
no sql:不包含sql语句
reads sql data:包含读取数据的语句,但不包含写入数据的语句
*/

4.3 触发器

1)概念——数据库管理员可以监控到所有用户操作数据库的每一条操作信息

触发器是与有关的数据库对象,在insert/update/delete之前或之后,触发并执行触发器中定义的SQL语句集合

作用: 在数据库端确保数据的完整性、日志记录、数据校验等操作

使用别名OLD和NEW来引用触发器中发生变化的记录内容

注: 现在触发器只支持行级触发器(执行一个SQL语句,影响了多少行就执行多少次),不支持语句级触发器(执行一个SQL语句,不管影响了多少行,只执行一次)

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

before——在操作数据前 ,对数据进行合法性检查

2)语法

# 创建
create trigger 触发器的名字
before/after insert/update/delete
on 表名 for each row  -- 行级触发器
begin
	trigger_stmt;
end;

# 查看
show trigger;

# 删除
drop trigger [schema_name.]trigger_name;  -- 如果没有指定schema_name,默认是当前数据库

3)应用场景

  • 记录操作日志
  • 数据校验

5. 锁🥦

5.1 概述

计算机协调多个进程或线程并发访问某一资源的机制,数据库中的锁也是想让共享数据能保证并发访问的一致性和有效性

锁冲突也是影响数据库并发访问性能的一个重要因素

分类——粒度

  • 全局锁: 锁住数据库中的所有表
  • 表级锁: 每次操作锁住整张表
  • 行级锁: 每次操作锁住对应的行数据

5.2 全局锁

应用场景: 全库的逻辑备份,对所有表进行锁定,从而获取一致性视图,保证数据的完整性

# 加锁
flush tables with read lock;
# 备份操作
mysqldump -u 用户名 -p 密码 数据库名 > 备份文件名  -- 不是sql语句,命令行就行,不用在mysql中执行
# 释放锁
unlock tables;

问题:

  • 如果在主库上备份,备份期间都不执行更新,业务停摆
  • 如果在从库上备份,在备份期间,从库不能执行主库同步过来的二进制日志,导致主从延迟

InnoDB中不加锁实现一致性数据备份

# 在备份时加入参数--single-transaction
mysqldump --single-transaction -uroot -p 123456 数据库名 > 备份文件名

5.3 表级锁

锁定粒度大,发生锁冲突的概率最高,并发度最低

分类

1)表锁

  • 表共享读锁(read lock):只能读不能写,不会阻塞其他客户端的读,会阻塞其他客户端的写,释放掉锁之后,就可以执行写
  • 表独占写锁(write lock):能读能写,其他客户端不能读也不能写
# 加锁
lock tables 表名(可以是多个表) read/write;
# 释放锁
unlock tables; / 客户端断开连接

2)元数据锁(meta data lock,MDL)

系统自己加的,在有事务的时候,事务中进行查询,系统就会自动加上共享读锁SHARED_READ,另一个事务进行插入、更新、删除操作的时候,系统会自动加上共享写锁SHARED_WRITE,他俩是兼容的,事务提交后就释放掉。但是修改表结构也就是alter table的时候,系统会加上EXCLUSIVE锁,是排他锁,可以理解为修改表结构,那么增删改查都不能同时进行的。

# 查看元数据锁的语法
select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks;

3)意向锁

解决:在加入行锁的时候对表加上意向锁,再来一个线程想要对该表进行加锁,那么就检查想要加的锁和意向锁列表能不能是兼容的,能兼容就加,互斥的情况就阻塞等待着

意向锁分类

  • 意向共享锁(IS):与表锁共享锁(read)兼容,与表锁排他锁(write)互斥
  • 意向排他锁(IX):与表锁共享锁(read)及排他锁(write)都互斥。意向锁之间不会互斥
# 查看意向锁及行锁的加锁情况
select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.data_locks;

5.4 行级锁

概念: 每次操作锁住对应的行数据。锁粒度最小,发生锁冲突的概率最低,并发度最高。应用在InnoDB存储引擎中

InnoDB数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁。——如果不通过索引条件检索数据,那么InnoDB将对表中的所有记录加锁,此时会升级为表锁

分类

  1. 行锁(Record Lock):锁定单个行记录,防止其他事务对此行进行update和delete。在RC和RR隔离级别下都支持——事务!!开始事务自动加锁,事务提交自动释放锁。

    共享锁(S):允许一个事务读一杠,其他事务不能来排他锁

    排他锁(X):这一行不能有共享锁和排他锁啦,其他行请随意

    在这里插入图片描述

  2. 间隙锁(Gap Lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持

    索引上的等值查询(唯一索引),给不存在的记录加锁时,优化为间隙锁

    索引上的等值查询(普通索引),最后一个值不满足查询需求,退化为间隙锁——前后都加,本条记录也加

    索引上的范围查询(唯一索引),访问到第一个不满足条件的值为止

    目的:防止其他事务插入间隙

  3. 临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。在RR隔离级别下支持——避免幻读现象

6. InnoDB引擎🌶?

6.1 逻辑存储结构

在这里插入图片描述

6.2 架构

擅长事务处理,具有崩溃恢复特性

InnoDB架构

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

内存

Buffer Pool: 缓冲池,缓存磁盘上经常操作的真实数据,在执行增删改查操作时,先操作缓冲池中的数据(如果缓冲池中没有,就从磁盘加载并缓存),然后再以一定的频率刷新到磁盘,从而减少磁盘IO,加快处理速度

缓冲池是以page页为单位,底层采用链表数据结构管理Page(不同小块的颜色表示不同的Page类型:free page表示空闲page,未被使用;clean page表示被使用page,数据没有被修改过;dirty page表示脏页,是被使用过的,数据被修改过,并且与磁盘数据产生了不一致)

Change Buffer: 更改缓冲区(针对于非唯一二级索引页——>插入顺序是相对随机的,删除和更新可能会影响索引树中不相邻的二级索引页)也就是如果操作的数据没在Buffer pool那就先修改到change Buffer中,等到bufferpool中有了,再一起合并到Buffer pool,然后等待机会刷新到磁盘中

Adaptive Hash Index: 自适应哈希索引,InnoDB是不支持这个索引的,他是用来优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询,如果察觉到hash可以提升速度没那就建立hash索引,无需人工干预。参数是:adaptive_hash_index

log buffer: 日志缓冲区,用来保存要写入到磁盘中的log日志数据(redo log、undo log),默认大小是16MB,日志缓冲区的日志会定期刷新到磁盘中。如果更新、插入、删除操作比较多,增加日志缓冲区的大小可以节省磁盘I/O。参数innodb_log_buffer_size:缓冲区大小;innodb_flush_log_at_trx_commit:日志刷新到磁盘的时机

磁盘

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

在这里插入图片描述

内存中的数据如何刷入磁盘——后台线程

在这里插入图片描述

6.3 事务原理

也就是如何保证的事务的四个特性

在这里插入图片描述

6.4 MVCC——高频面试

基本概念

当前读: 读取的是记录的最新版本,读取时要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁(select…lock in share mode、select …for update\update\insert\delet,都是一种当前读)——如果不是当前读,另一个事务修改了数据,是读不到最新数据的

快照读: 简单的select(不加锁)就是快照读,读取的是记录数据的可见版本,有可能是历史数据,不加锁,是非阻塞读

  • Read Committed:每次select,都生成一个快照读
  • Repeatable Read:开启事务后第一个select语句是快照读的地方
  • Serializable:快照读会退化为当前读

**MVCC(Multi-Version Concurrency Control, 多版本并发控制):**维护一个数据的多个版本,使读写操作没有冲突,快照读为MySQL实现MVCC提供了一个非阻塞读功能。实现依赖于数据库中的三个隐式字段、undo log日志、readView

1)三个隐藏字段——每一条记录

DB_TRX_ID: 最近修改事务的ID,记录插入或最后一次修改该记录的事务ID

DB_ROLL_PTR: 回滚指针,指向该记录的上一个版本,用于配合undo log

DB_ROW_ID: 隐藏主键,如果表结构没有指定主键,将会生成该隐藏字段——如果表存在主键,就不会生成该隐藏字段

2)undo log日志

回滚日志是在insert、update、delete的时候产生的便于数据回滚的日志。insert的时候,产生的undo log日志只在回滚时需要,事务提交之后,可被立即删除;在update、delete的时候,产生的undo log日志不仅在回滚时需要,在快照读的时候也需要,不会被立即删除 (快照读时读取的是旧的数据)

undo log 版本链 :链表

3)readview(读视图)

是快照读SQL执行时MVCC提取数据的依据,记录并维护系统当前活跃的事务(未提交的)id

四个核心字段

  • m_ids: 当前活跃的事务ID集合
  • min_trx_id: 最小活跃事务ID
  • max_trx_id: 预分配事务ID,当前最大事务ID+1(因为事务ID是自增的)
  • creatoe_trx_id: readview创建者的事务ID

版本链数据访问规则

trx_id:代表当前事务ID

①trx_id == creator_trx_id? 可以访问该版本——>成立,说明数据是当前这个事务更改的

②trx_id < min_trx_id? 可以访问该版本——> 成立,说明数据已经提交了

③trx_id > max_trx_id ? 不可访问该版本——>成立,说明该事务时在Readview生成后才开启的

④min_trx_id <= trx_id <= max_trx_id ? 如果trx_id不在m_ids中是可以访问该版本的——> 成立,说明数据已经提交

不同的隔离级别,生成readview的时机不同

  • read commited :在事务中每一次执行快照读时生成readview;在读取的时候保证读已提交
  • repeatable read:尽在事务中第一次执行快照读的时候生成,后续复用该readview

7. MySQL管理🌰

7.1 系统数据库

1)mysql:存储MySQL服务器正常运行所需要的各种信息(时区、主从、用户、权限等)

2)information_schema:提供了访问数据库元数据的各种表和视图,包括数据库、表、字段类型及访问权限等

3)performance_schema:为MySQL服务器运行时状态提供了一个底层监控功能,主要用于收集数据库服务器性能参数

4)sys:包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优和诊断的视图

7.2 常用工具

客户端工具

# 1. mysql
# 语法
mysql [options] [database]
# 选项
-u --user=name  # 指定用户名
-p --password[=name]  # 指定密码
-h --host=name  # 指定服务器IP或域名
-P --port=port  # 指定连接端口
-e --execute=name  # 执行sql语句并退出,连接数据库的时候处理一些脚本,很方便

# 2. mysqladmin  是一个执行管理操作的客户端程序,可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

# 3. mysqlbinlog 由于服务器生成的二进制日志文件以二进制格式保存,如果想要检查这些文本的文本格式,就会用到mysqlbinlog日志管理工具
mysqlbinlog [options] log-files1 log-files2...

-d --database=name  # 指定数据库名称,只列出指定的数据库相关操作
-o --offset  # 忽略掉日志中前n行命令
-r --result-file=name  # 将输出的文本格式日志输出到指定文件
-s --short-form  # 显示简单格式,省略掉一些信息
--start-datatime=date1  --stop-datetime=date2  # 指定日期间隔内的所有日志
--start-position=pos1  --stop-position=pos2  # 指定位置间隔内的所有日志

# 4. mysqlshow 客户端对象查找工具,用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引 
mysqlshow [options][db_name[table_name[col_name]]]
--count  # 显示数据库及表的统计信息(数据库,表均可以不指定)
-i  # 显示指定数据库或指定表的状态信息

# 5. mysqldump客户端工具用来备份数据库或在不同的数据库之间进行数据迁移。备份内容包含创建表以及插入表的SQL语句
# 语法
mysqldump [options] db_name[tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A
# 连接选项
-u --user=name  # 指定用户名
-p --password[=name]  # 指定密码
-h --host=name  # 指定服务器ip或域名
-P --port  # 指定连接端口
# 输出选项
--add-drop-database  # 在每个数据库创建语句前面加上drop database语句
--add-drop-table  # 在每个表创建语句前加上drop table语句,默认开启;不要开的话(--skip -add-drop-table)
-n --no-create-info  # 不包含表的创建语句
-t --no-create-db  # 不包含数据库的创建语句
-d --no-data  # 不包含数据
-T --tab=name  # 自动生成两个文件,一个.sql文件,创建表结构的语句;一个.txt文件,数据文件

# 6. mysqlimport/source数据导入工具。用于导入mysqldump加-T参数后导出的文本文件
# 语法
mysqlimport [options] db_name textfiles [textfiles2...]
# 如果需要导入sql文件,可以使用mysql中的source指令
source/root/xxx.sql

总结

在这里插入图片描述

文章来源:https://blog.csdn.net/qq_44689664/article/details/135207194
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。