专属于小白的一份MySQL学习文档

MySQL

MySQL数据模型

• 建立在关系模型基础上,由多张相互链接的二维表组成的数据库

可以创建多个数据库,数据库是相互独立的,一个数据库里面可以创建多张表

SQL简介

通用语法

SQL分类

数据库设计-DDL语句

Data Definition Language: 数据定义语言,用来定义数据库对象(数据库,表,字段)

• 查询数据库:show databases;
• 创建数据库
  * 1.1 create database db02; 不能有重名的数据库
  * 1.2 create database if not exists db03; 在创建一个数据库前可以先添加一个判断
• 切换数据库 : use db01;
• 查询当前正在使用的数据库: select database();
• 删除对应的数据库: drop database db03;

上述语法中的database可以用schema替换

创建表

举例代码:

create table tb_user(
    id int comment'用户id',
    username varchar(20) comment '用户名',
    name varchar(10) comment  '姓名',
    gender char(1) comment  '性别'
)comment '用户信息表单'

约束:用于现在表中的数据,确保数据的完整性,正确性

约束需求:

带约束的代码:

create table tb_user(
    id int primary key auto_increment comment'用户id',
    username varchar(20) not null unique comment '用户名',
    name varchar(10) not null comment  '姓名',
    gender char(1) default '男' comment  '性别'
)comment '用户信息表单'

数据类型

重点关注红色标记的类型

• 数据类型
• 字符串类型
• 日期类型

数据类型,没有符号的数据要在后面加unsigned修饰

字符串类型
长度固定的字符串一般使用char(),如电话号码,不固定的一般使用varchar(),如输入的用户名

日期类型

• show tables查看当前数据库下面存在的表

• desc "表名"查看指定表的结构和内容

• show create table “表名”:查看数据库建表语句
  DDL操作语句
  

• 删除表 drop table if exists 表名

数据库操作-DML语句

Data Manipulation Languange :用来对数据库中的表的数据记录进行增. 删.改操作

insert-插入语法

• 为指定字段插入值: insert into 表名(字段名1,字段名2…) values (值1,值2,…);如果存在非空限制的字段必须插入值
• 为表中的所有字段插入值: insert into 表名 values(值1,值2,值3…);
• 批量为表中字段插入数据:insert into 表名(字段名1,字段名2…) values (值1,值2,…),(值1,值2…);

具体例子见下图

sql语句中获取当前时间的函数为now();

update-修改数据

• update 表名 set 字段名=‘值’ where id=1
  where 为限制条件,表示将id为1的name修改
• 如果要修改所有数据的某个字段的值,就不添加where限制

实例见下:

Delete -删除语句

• delete from 表名 where 限制条件如 id=1
  如果不添加where限制条件就会删除整张表;

实例见下图:

delete语句不能删除某一个字段的值,可以用update语句将其置为null

数据库操作-DQL语句

Data Query Language: 用于查询数据库中的数据

基本查询 select 字段名from表名

• 查询多个字段: select 字段1,字段2,字段3 from表名;,显示对应字段的数据
• 查询所有是的字段(通配符): select* from 表名;
  推荐先写select…from表名 再在中间(…)写字段名,将所有的字段名写出来
• 设置别名: select 字段1 [as别名1],字段2[as别名] from 表名;as可以省略,如果别名里面有空格或者其他符号,可以用’'将其看成一个整体
• 去处重复记录: select distinct 字段列表 from 表名;去处字段中重复的数据

不建议使用通配符* ,不直观,影响开发效率

条件查询: select 字段列表 from 表名 where 条件列表

查询条件:

一个’ _'代表一个字符,%s代表任意字符,具体如下图:

分组查询:~…+group by

聚合函数: select 聚合函数 (字段列表)from表名;

分类:

• count()括号内可以是字段,*和常量,*和常量意义一样都是统计表中的数据条数
• min()求最小
• max()求最大
• avg()求平均
• sum()求和

分组查询:select 字段列表 from [where条件] group by 分组字段名[having 分组后的过滤条件]

示例如下:

where和having的区别

执行顺序:where>聚合函数>having

排序查询order by 字段名 排序方式

默认为asc升序排序desc为降序排序

• 多字段排序,当第一个字段排序字段值相同时才会进行第二个排序
  举例如下:
  

分页查询(limit)

~+limit 起始索引,查询记录数
注意事项:

• 起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数
• 不同数据库有不同的实现,mysql中是limit

判断函数

当字段值为数字或其他符号,用于判断对应的内容,我们可以使用函数进行判断

• if(表达式,tvalue,fvalue):当表达式为true是,取值为tvalue;当表达式为false时,取值fvalue
  举例如下
  

• case 表达式 when值1 then 结果1 [when值2 then 结果2 …][else result] end;
  举例如下
  

在判断语句后可以直接添加中文别名,这样就可以得到的查询数据字段名为中文别名

多表设计

一对多

将两张表通过外键链接
可以直接使用图形化界面来添加外键来关联

白雪: 通过foreign key定义的外键关联另外一张表现现已在开发中被很多禁止使用,推荐使用逻辑外键

一对一

多对多

多表查询

逻辑外键查询
简单举例

select * from tb_emp,tb_dept where tb_dept.id=tb_emp.id;/*逻辑外键*/

查询两张表中id字段值相同的数据

内连接

• 隐式查询:select 字段名1,字段名2,from 表名1,表名2,where 判断语句

select tb_emp.name,tb_dept.name from tb_emp,tb_dept where tb_dept.id=tb_emp.id;

• 显示查询

select 字段列表 from 表名 [inner] join 表二 on 链接条件
例子如下:

• 注意:如果查询的字段值为null则查询不到该条数据

外连接

外连接与内连接的不同就是外连接可以查看左或右表中的所有数据,包括null值

• 左外连接
  select 字段列表 from 左表名 left join 右表名 on 联查条件(两张表的关联条件)其他判断查询用where+判断语句查询
  也可以直接on+各种判断条件不用where

select e.name 姓名, d.name 部门 from tb_emp e left join tb_dept d on d.id=e.id;

• 右外连接与左外链接差不多,left换成right 右边放右表,查询右表中的所有,两种连接可以相互转换

子查询

查询语句作为另外一条查询包含在另外一条查询语句,被包含的查询语句被称为子查询

简单举例

子查询返回结果多个时举例

多表联查总结

• 找出涉及的数据表
• 弄清逻辑关系
• 找到需要返回的数据字段

事务

一组操作的集合,所有的sql只要有一条语句错误事务操作就失败

事务控制流程

• 开启事务start transaction;/begin;
• 操作的所有sql语句
• 提交事务 commit 所有的操作都成功再提交
• 回滚事务 rollback 有操作不成功就将数据回归原样

操作中的sql语句 ,操作完没commit在表中直接看数据没有变化,但是用DQL查询语句就会看到数据的操作

索引

帮助数据库高效获取数据的数据结构

优缺点

优点远大于缺点

底层结构(B+ tree)

语法

• 创建索引
  creat [unique] index 索引名 on 表名 (字段名)
• 查看索引
  show index from 表名 查看表中的所有索引
• 删除索引
  drop index 索引名 on 表名

注意事项:

• 主键字段,在建表是,会自动创建主键索引(primary)
• 添加唯一约束是,数据库实际上会添加唯一索引(unique)

事务

事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败

事务操作

• 查看/设置提交方式
  SELECT @@autocommit
  SET @@autocommit=0
  手动提交事务@@autocommit=0,自动提交为1

• 提交事务
  COMMIT

• 回滚事务
  ROLLBACK

• 开启事务
  START TRANSTION 或BEGIN
  设置@@autocommit=1,开启事务,表示那一段事务人为控制,与设置@@autocommit=0相比,
  START TRANSTION开启事务后中间的sql操作执行成功的语句不会对数据库产生数据的改变,但是出现异常也需要回滚事务
  设置@@autocommit=0成功的sql会产生数据的改变,但不是实质性的改变,ROLLBACK回滚事务又会恢复如初.

事务流程一般为开启事务的操作,如下图

事务的四大特性

• 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败
• 一致性(Consistency): 事务完成时,必须使所有的数据都保持一致状态.
• 隔离性(Isolate):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
• 持久性(Durability):事务一旦提交或者回滚,它对数据库中的数据的改变就是永久的

并发事务问题

多个事务同时操作产生的问题

• 脏读
  一个事务读到另外一个事务还没有提交的数据
• 不可重复读
  一个事务先后读取同一条记录,但是两次读取的数据不同,称之为不可重复读
• 幻读
  一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了幻影

事务的隔离级别

Repeatable Read为mysql默认的隔离级别

操作

一般使用默认级别Repeatable Read

为什么mysql默认使用Repeatable Read 隔离级别?

答案链接

存储引擎

存储引擎就是存储数据.建立索引/更新/查询数据等技术的实现方式.存储引擎是基于表的,而不是基于库的,所以存储引擎也可以被称为表类型

建表时指定存储引擎,默认为INNODB

INNODB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在MySql5.5之后,InnoDB是默认的MYSQL存储引擎.

特点:

• DML操作遵循ACID模型,支持事务
• 行级锁,提高并发访问性能
• 支持外键FOREIGN KEY约束,保证数据的完整性的正确性

表空间文件
xxx.ibd:xxx代表的是表名,innoDB引擎的每张表都会对应这样一个表空间文件,储存该表的表结构(frm,sdi). 数据和索引 参数:innodb_file_per_table

innodb逻辑存储结构

MyISAM

MyISAM是MySQL早期的默认存储引擎
特点:

• 不支持事务,不支持外键
• 支持表锁,不支持行锁
• 访问速度快

文件:
.sdi:存储表结构信息
.MYD:存储数据
.MYI : 存储索引

Memory

Memory 引擎的表数据是存储在内存中的,由于受到硬件问题.或断电问题的影响.只能将这些表作为临时表或者缓存使用
特点:

• 内存存放
• hash索引(默认)
  文件:.sdi:存储表结构信息

三个引擎的特点和区别表

索引

索引是帮助MySQL高效获取数据的数据结构(有序)

优缺点

优点

• 提高数据检索的效率,降低数据库的IO成本
• 降低排序成本,降低CPU消耗

缺点

• 索引是占空间的,但是现在的磁盘价格…
• 降低了更新表的速度,进行增删改操作后需要重新维护数据的结构. 但是项目查询操作会更多,增删改较少

索引结构

主要是B+tree索引


二叉树的缺点:
顺序插入时,会形成一个链表,查询性能大大降低.大数据量的情况下,层级越深,检索速度越慢.

红黑树的缺点:
大数据量的情况下,层级越深,检索速度越慢

B-Tree(多路平衡查找树)
B+tree

与B-Tree的区别

• 所有的数据都会出现在叶子结点
• 叶子结点会形成一个单向链表

为什么InnoDB存储引擎选择使用B+tree索引结构?

• 相对于二叉树,层级更少,搜索效率高
• 对于B-tree,无论是叶子结点还是非叶子结点,都会保存数据,这样导致一页存储的键值减少,指针跟着减少,同样保存大量数据,只能增加数的高度,导致性能降低
• 相对于Hash索引,B+tree支持范围匹配和排序操作

索引分类

第二种分类
分为聚集索引和二级索引
聚集索引下面挂的是行数据,二级索引下面挂的是主键值

InnoDB主键索引的B+tree高度为多高呢?

16*1024的意思是每页的大小为16K,1k为1024个字节,箭头所指为错误

索引语法

索引名的命名规则

一般为idx_表名_指定字段名
联合索引:idx_表名_指定字段名1_指定字段名2_…

性能分析

查看数据表执行频率
show global status like “Com_______”, 七个下划线

慢日志查询

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志,MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息
#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢查询的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
配置完毕之后,通过以下指令重新启动MySQL服务器进行测试
重启指令:systemctl restart mysqld
查看慢查询日志指令:show variable like ‘slow_query_log’
查看慢日志查询文件尾部查询情况指令:tail -f localhost-slow.log
执行的sql语句超过预设时间的慢查询情况就会实时出现,如下图

profiling详情

执行一系列的业务SQL的操作,然后通过如下指令查看指令的执行耗时

• 打开profiling开关指令
  set profiling=1;
• 查看profiling的开关情况
  select @@profiling
• 查看指定query_id的SQL语句各个阶段的耗时情况
  show profile for query query_id;
  
• 查看指定query_id的SQL语句CPU使用情况
  show profile cpu for query query_id;

explain执行计划

真正分析的工具,前面了解(白雪)
语法:直接在select语句之前加上关键字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

各字段的含义
主要通过type的类型进行判断

id字段

索引的使用规则

最左前缀法则

如果索引了多列(联合索引),要遵守最左前缀法则,最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列,如果跳跃某一列,索引将部分失效(后面的字段索引失效)

注意:

• 最左顺序的字段是创建索引的时候指定的顺序,不是查询判断的顺讯,即使1放到最后面依旧遵循最左前缀法则
• 如果2号字段不存在,1号字段和3号字段存在就只会根据1号字段进行查询
  

索引失效

范围查询

联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效
在业务允许的条件下尽量使用>=符号

索引列运算失效

不要在索引列上面进行运算操作,不然索引会失效进行全表扫描

不加引号索引失效

字符串类型字段使用时,不加索引,索引将失效

模糊查询

如果知识后面模糊查询索引不会失效,只要前面加%进行模糊查询就会使得索引失效

or连接的条件

用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引(与其他字段的联合索引不算),那么所涉及的索引不会用到

数据分布影响

如果MySQL评估使用索引比全表更慢,则不使用索引(查询条件的结果如果是少部分数据mysql会使用索引,如果结果是大部分数据或者是全表,mysql就不会使用索引而是直接进行全表扫描)

SQL提示

SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的

覆盖索引

尽量使用覆盖索引(查询使用了索引,并且需要返回列,在该索引中已经能全部找到),减少select*

如果通过索引查询返回的字段只包含索引所包含的字段和主键id则不要回表查询,速度快(因为二级索引下挂着主键id,可以直接获取),如果还需获取其他索引外的其他字段就需要进行回表查询

前缀索引

当字段类型为字符串(varchar,text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率.此时可以只将字符串的一部分前缀作为索引,这样可以大大节约索引空间,从而提高索引效率

语法
create index idx_xxx on table_name(字段名(n))

前缀长度
可以根据索引的选择性即不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高查询效率越高,唯一索引的选择性是1,越接近一越好

SQL优化

插入数据优化

insert优化

• 批量插入
  当数据量较多的时候500~1000的时候建议一次批量插入
  
• 手动提交事务
  
• 主键顺序插入
  
• load插入
  当数据有几百万条的时候就需要考虑使用load进行插入
  使用load进行操作的相关操作
  
  

主键优化

逻辑存储结构

主键设计原则

主键自增和uuid的优缺点详情可以见下方连接

• 自增主讲和uuid主键的优缺点

order by优化

默认所有的字段都是升序

出现联合索引的字段排序的规则不一样时的解决方法

根据排序字段进行建立合适的索引,多字段排序时,也遵循最左前缀法则.并且order by后面的顺序要与建索引时的顺序一样

group by优化

• 在分组操作时,可以通过索引来提高效率
• 分组操作时,索引的使用也是满足最左前缀法则的

limit 优化

count优化

update优化

加索引且不失效就是更行时是行锁,不加索引的字段更新是表锁,表锁对并发事务不是很友好

视图

简介
视图是从一个或几个基本表（或视图）导出的表。
它与基本表不同，是一个虚表。数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。
所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。
从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。
视图只供查询，数据不可更改
视图的作用：

• 1、视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。
• 2、视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）
• 3、从而加强了安全性，使用户只能看到视图所显示的数据。
• 4、视图还可以被嵌套，一个视图中可以嵌套另一个视图。

基本操作
or replace可以在创建视图的时候可选性添加,但是在更新视图的时候必须添加

#创建视图
create view competition_c1 as select id,name from competition_table;

#查询视图
select * from competition_c1;

select * from competition_c1 where name='java1';

#修改视图

create or replace view competition_c1 as  select id,name,major from competition_table;

alter view competition_c1 as select id,name from competition_table;

# 删除视图
drop view if exists competition_c1;

视图的检查选项
cascade级联检查选项

local与cascade检查选项的区别:
如果v2表添加的是local检查,那么它所依赖的v1如果添加了检查就会执行检查,没有添加检查v1的条件对数据的添加没有影响,如果是cascade(级联)检查,就算v1没有添加检查v1的条件也会在插入数据的时候有影响.依赖v2的v3在两种检查选项下都会使用v2的检查.

视图的更新

视图的作用

个人总结视图的作用

• 可以屏蔽某些字段,提高数据的安全
• 可以将多表的联查的结果写在视图内,下次查询,和修改就会变得更加方便

存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理得效率是有好处的
特点:

• 封装,复用
• 可以接收参数,也可以返回数据
• 减少网络交互,效率提升

语法

• 创建
  create procedure 存储过程名称(参数列表)
  begin
  —sql语句
  end;

• 调用
  call 名称(参数)

• 查看

查询指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema=‘xxx’;

查询某个存储过程的定义

show create procedure 存储过程名称

• 删除
  drop procedure if exists 存储过程名称

• 指定结束符
  在命令行中,执行创建过程的SQL时,需要通过关键字delimiter指定SQL 语句的结束符

变量

global:全局变量
session:当前会话变量

• 设置系统变量
  默认为session
  set [session|global] 系统变量名=值
  set @@[session|global] 系统变量名=值

• 查看系统变量
  

global全局变量执行的sql对所有的会话都生效,session:当前会话变量仅当前会话生效

用户自定义变量

用户自定义变量是用户根据需要自己定义的变量,用户变量不用提前声明,在用的时候直接使用"@变量名"使用就可以,其作用域为当前连接.

局部变量

局部变量是根据需要定义的在局部生效的变量,在访问之前,需要DECLARE声明.可以用作存储过程内的局部变量和输入参数,局部变量的范围是在其内部声明的BEGIN…END块

代码示例

create procedure p3()
begin
    #定义变量
    declare stu_count int default 0;
    #赋值
    select count(*) into stu_count from competition_table;
    #查询变量
    select stu_count;
end;

#调用存储过程
call p3();

if条件语句

结构如红框所示

参数

• in 输入
• out 输出
• inout 即可以是输入也可以是输出
• in和out举例
  
  inout参数
  
  case语法结构
  

循环存储结构

while 条件 do
SQL 逻辑…
END WHILE

repeat
sql操作
until 结束判断条件
end repeat;
这种循环最少会执行一次

loop循环
iterate相当于continue跳过此次循环,执行下一次循环

游标cursor

游标是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果队结果集进行循环的处理.游标的使用包括游标的声明.OPEN FETCH和CLOSE
语法

举例

条件处理程序Handeler

举例

报错码的来源
如下图循环条件为TRUE,所以当游标中的数据循环完的时候就会报错
这时就可以使用条件处理程序,当循环报错的时候就会进入条件处理程序来处理

存储函数

了解一下(白雪)可以用存储过程来代替存储函数,多用存储过程

从0加到n的代码举例

# 0加到n
#存储函数
create function fun_1(n int)
returns int deterministic
begin
    declare total int default 0;
    while n>0 do
        set total:=total+n;
        set n:=n-1;
        end while;
    return total;
end;

select fun_1(50);

触发器

触发器是与表有关的数据库对象,指在insert/update/delete之前或之后,触发并执行触发器中定义的SQL语句集合.触发器这种特性可以协助应用在数据库确保数据的完整性,日志记录,数据校验等操作.
使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的,现在触发器还只支持行级触发,不支持语句级触发

触发器类型

操作步骤

• 1.创建日志表
  

• 2.创建触发器
  

• 插入数据,同时触发触发器,日志表中添加记录
  
  日志表
  

update操作语句触发器

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制.在数据库中,除了传统的计算资源(CPU RAM I/O)的争用意外,数据也是一种供许多用户共享的资源.如何保证数据并发访问的一致性,有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个总要因素.从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂.

分类
按照锁的粒度来分,分为以下三类

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

全局锁

全局锁就是对整个数据库实例加锁,加锁后整个案例就处于只读状态,后续的DML的写语句,DDL语句,已经更新操作的事务提交语句都将被阻塞.
其典型的使用场景就是做数据库的逻辑备份,对所有的表进行锁定,从而获取一致性的视图,保证数据的完整性.

备份操作要在磁盘目录下进行操作,不能在mysql目录下操作

在InnoDB引擎中,我们可以在备份时加上参数–single-transaction参数来完成不加锁的一致性数据备份

表级锁

语法
1.加锁:lock tables 表名… read / write
2.释放锁: unlock tables /客户端断开连接

分为两类

• 1.表共享读锁(read lock)
  所有的的客户端都可以对这张表进行数据的读取,但是不能进行写入操作,写入和修改操作会处于阻塞状态.释放锁之后操作会执行
• 2.表独占写锁
  当前加锁的客户端可以进行读写操作,其他的客户端不能进行读写操作

元数据锁(meta data lock , MDL)

MDL加锁过程是系统自动控制,无需显示使用,在访问一张表的 时候会自动加上,MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作,为了避免DML和DDL冲突,保证读写的正确性
当对一张表进行增删改查的时候加MDL读锁(共享);当对表结构进行变更操作的时候,加MDL写锁(排他)

CRUD操作加的是共享锁,不会影响其他事务的操作,改变表结构加的是排他锁,要等其他事务提交后才能改变表结构的操作才能执行,不然一直都是阻塞状态

查看元数据锁

意向锁

为了避免DML在执行时,加的行锁与表锁的冲突,在InnoDB引入了意向锁,使得表锁不用检查每行数据是否加锁,使用意象锁来减少表的检查

• 意象共享锁(IS):与表锁共享锁(read)兼容,与其他表锁排他锁(WRITE)互斥
• 意象排他锁(IX):与表锁共享锁(read)及排他锁(write)都互斥.意象锁之间不会互斥
  查看意向锁及行锁的加锁情况sql
  

下图添加的是意向共享锁(IS)

如果不加lock in share mode会自动加上行锁和意向排他锁

行级锁

行锁

主要分为共享锁(S)和排他锁(X)

常见的sql语句的行锁

两种情况

间隙锁

默认情况下,InnoDB在RR事务隔离级别运行,InnoDB使用next-key锁进行搜索和索引扫描,以防止幻读

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

下图为演示对主键id=5的数据进行操作的案例:
其他客户端不能再对这个3~8这个间隙的数据进行操作,不包含3和8

间隙锁唯一的目的是防止其他事务插入间隙,间隙锁可以共存,一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

注意:只是不能在这个间隙执行insert语句进行插入数据

• 2.索引上的等值查询(普通索引),向右遍历时最后一个不满足查询需求时,next-key lock退化为间隙锁(因为普通索引不是主键,可以不唯一,为防止插入多个相同的索引字段数据,所以需要将前后的间隙都加上间隙锁)
  

• 3.索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止
  就是会在这个值和正无穷加上加上临键锁

InnoDB存储引擎

逻辑存储结构

架构

分为内存架构和磁盘架构两大类

内存架构

• Buffer Pool
  

• Change Buffer
  

• Log Buffer
  

• 自适应hash索引(Adaptive Hash Index)
  

磁盘架构

• 系统表空间
  

• File-Per-Table-Tablespace
  

• 通用表空间 撤销表空间 临时表空间
  

• 双写缓冲区 重做日志(Redo Log)
  

后台线程

• 了解就行
  

事务原理

redolog buffer

主要维持事务的持久性
重做日志,记录的是事务提交时数据页的物理修改,是用来实现事务的持久性
该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做日志文件(redo log file),前者在内存中,后者在磁盘中,当事务提交之后欧会把锁修改的信息提交到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用

undolog

主要维持事务的原子性,用于回滚事务

MVCC(多版本控制)

MVCC基本概念

三个隐藏字段

• DB_TRX_ID
  最近修改事务ID,记录插入这条记录或最后一次修改该记录的事务ID
• DB_ROLL_PTR
  回滚指针,指向这条记录的上一个版本,用于配合undo log,指向上一个版本
• DB_ROW_ID
  隐藏主键,如果表结构没有指定主键,将会生成该隐藏字段

undo Log版本链

当insert的时候,产生的undo log日志只在回滚时需要,在事务提交后,可被立即删除
而update delete的时候,产生的undo log 日志不仅在回滚时需要,在快照读时也需要,不会立即被删除

ReadView

• ReadView中的四个核心字段
  
• 版本链数据访问规则
  
• 在Read Commit级别下的快照读
  
• 在RR级别下的可重复读
  
  

MySQL管理

MySQL自带的四个数据库

常用工具