mysql概览

InnoDB具有行级锁。具有外键。支持事务。支持mvcc。

mvcc有点类似于CAS,是一个多版本高并发控制,是一个乐观锁。实现的时候结合了排他锁。由于不用悲观地上锁让他完成了高并发。

 我们先理解mvcc,指的是多版本高并发控制,对于InnoDB来说,每一行有两个隐藏的数据,分别是系统版本即当前事务ID和删除标识,事务ID指的是你的语句所在的事务的ID,比如你的语句在第七个事务那就是7,删除标识指的是你要删除行的所在事务的事务ID,update会将所在事务ID作为原行的删除标识,原行不会删除,更新后的行作为insert的行,该行的系统版本为当前事务ID,我们在select的时候,select所在的事务ID要小于行的删除标识,要大于行的系统版本,也就是说删除标识和系统版本都是事务ID,我要查询得到那我查询所在事务ID肯定要大于你的系统版本也就是创建时间呀,也肯定要小于你的删除标识呀。对于RR来说,每个select事务都只会使用select的第一次快照,也就是说不管你的select是第几个事务,都只用那一个快照,也就是说你的select的事务ID很老即很小。其他事务做出的增删改等都不会影响到我的select,因为只要增删改的行的系统版本和删除标识都大于select所在的事务ID,都不会影响到select。而如果是RC,则每个select都新建一个快照。

mvcc无法实现未提交读和序列化读,因为是根据事务ID和系统版本、删除标识比较来的,因此肯定有未提交读不满足的情况,比如说你先开启查询事务,再开启修改事务,查询事务ID是一定小于行的系统版本和删除标志的,因此即使未提交,我也只能查询到修改前的数据。由于mvcc是模仿行级锁,但是并未上锁,也就是说不同的事务的读写操作是非阻塞的,也就说我开启事务增删查改的时候其他事务也可以开启增删查改,但是具体的实现上肯定有个先后顺序,只不过由于没有上锁,因此可以并发事务。假设现在先开启了A事务,然后开启B事务,那么A的事务ID为1,B的为2,但是B先执行查询操作,查到的记录为0行数据,然后在A里执行插入操作,该插入行的系统版本为1,删除标识为undifined,然后A里再执行查询操作,由于A的事务ID2大于行的系统版本1且小于行的删除标识或未定义,则查询成功,注意要等到A提交,不然系统版本和删除标识是不会改变的,这也解释了为什么不支持未提交读,由上可知也不支持序列化读。

快照的意义在于是否每次依照select所在的事务ID还是依照第一次select所在的事务ID来和经由其他事务修改后的行的删除标识、系统版本进行比较。

 

gbk类似于ASCII的编码方式,windows默认gbk,Linux默认utf-8,在insert等操作时,客户端会按照系统方式进行编码,然后MySQL再根据MySQL的字符集(默认为utf8)将字符转换,而select等操作则是服务端到客户端的字符转换。

这个MySQL客户端和服务端之间呢是字符流,因此客户端不用管是什么类型编码的,因为字符串到了我这边后是用我这边的来解码,因此我就可以存储该字符或操作。

而如果是文件的话就是I/O流可能是字节,字节就是我这边按照我的字符集解码为字节给你,你再按照你的字符集编码,肯定会出错(如果不一致的话)。

 

事务的隔离性,即事务之间互相不影响。

mvcc,虽然没有上锁,但是事务满足一定隔离级别在某种程度上也是一个锁,因此隔离级别必须为已提交读或可重复读。这样能保证数据的一个真实性。然后再进行版本的比较,也就是说并发的事务随便你begin,最后是否commit还得我版本是否相等来决定。

InnoDB默认是可重复读,是用next-key Lock锁实现的,但是和其他数据库不同,在MySQL里它同样实现了幻读的级别,因此完全足够。虽然大部分的数据库隔离级别为已提交读,但是和MySQL的可重复读相比无任何性能损失。

 

数据库引擎InnoDB就四种锁,行级锁record lock(非常消耗资源,但是锁的粒度最小,会发生死锁),间隙锁gap lock(不包括记录行)、record+gap包括行记录的范围,表级锁(消耗资源少,但是锁的粒度最大,不会发生死锁)。而事务的隔离级别我们是直接可以设置的(基于session设置)。现在的MySQL的隔离级别是基于mvcc实现的,例如已提交读,每次提交都会创建一个新的readview,当你的事务id号在readview里去找,因此每次都是新的。而可重复读一直都用你第一次读的时候创建的readview,所以你后面的所有事务的id号去readview里找的时候都是原来那一个,结果就是找不到咯。

 

可重复读和已提交读都可以依靠mvcc实现,注意这里不需要结合上述提到的锁来实现,假设现在有两个session为a和b,a里面先查后改某行记录信息,a可以正确执行,b里面的select在a提交修改事务之前执行的话那么b后续所有的select事务都是该结果。而如果在a提交修改事务之后执行select,那么b后续的所有select都是修改后的结果,也就是说可重复读是在session里第一次执行select事务的时候就确定好了readview,即使其他session的修改事务(注意这里会回滚,就是说只要我b开始select了,a做的事情都是不会提交的,就相当于比如b查询了某范围的记录,那么就会利用gap+record加锁,这就是典型的乐观锁,即b里面的事务开启并不会给资源上锁,而是在执行select的时候才上相应的锁),我还是只能查询到我第一次查询的东西,这期间如果我修改的话是起作用的。只要间隙够大,就可以达到可串行化读防止幻读。

而已提交读则无锁,是指我查询的时候不上锁,谁改了我查到的都会变。

注意上面的思想,修改和删除不上锁,一到查询的时候就上锁。严格上来说不叫上锁,因为只有查询的时候才会产生readview即快照,这个快照的作用相当于锁。

 

show processlist展示子线程例如select、insert等。

insert into user(class) values ('22班'),('33班')同时插入两条数据。

数据库里没有{}和[],表示内容统统用()。

有一种更简单的添加约束alter table yue add index suoyin(yue_age)适用于其他任何约束,这里的suoyin(yue_age)类似于添加数据时的表名(列名)。

在修改表来添加一些约束的时候统统用add constraint 约束名的方式,后面跟约束(字段),注意括号里一定要跟上列的字段。

修改表添加约束要加列名字段,放到()里,例如alter table user add constraint primary key(id)、...add unique(id)、alter table user add constraint ee unique(class)、alter table user add constraint fr check(class<>'ds'),而加不加constraint 字段名(不能用'',直接写例如dwa)都可以。

而在创建表时则不需要加列字段,直接写约束关键字就可以了。check要写字段。

上述两种方式添加约束(例如主键、外键、unique、check)时加与不加constraint关键字都可以。

而在撤销约束时,其实也就是修改表的时候,不能使用alter table user drop constraint dd的方式,因为constraint只是一个笼统的概念,具体就不知道是删除该列上的哪一个约束了(注意这里我们讨论的是列上的不同约束,这是因为不可能有两列上的constraint字段重名,这和数据库的第一范式有关,第一范式规定数据库不允许有两个相同的列,也就是不允许两个列字段重名,这样就能保证每一行由且仅由一系列独特的字段确定。)。

所以删除约束直接drop index ee、drop check fr、 drop primary key、alter table yue drop foreign key ss。对于default和null这种没法用()跟上列字段的约束,我们在修改表这种方式下添加该类型约束的时候要修改列,例如alter table user alter column set default='sd' not null。删除则alter table user alter column uname drop default,和primary ley在一个表里只有一个一样,指定表后不用指定名字。这里指定列后,一列里只有一个默认值,可以不加default约束名字。

注意,在修改表添加约束的时候要在约束字段后面的()里加上列字段名,删除约束不需要列字段名,只需要constraint提供的约束字段名就ok了,因为约束字段和列字段一样,能确定一个列(通俗一点就是不允许有两个列的相同约束有相同名字)。而primary和default都是处于已确定好列或唯一性的条件下。因此只有在修改表+添加约束的条件下才需要列字段名。

 create table(id int)comment="表的备注" data directory=“数据目录” index directory="索引目录"。

drop table if exists test3;if exists少用,很多情况下无效会报错。
create table test3 like yue;

if exists使用范围很窄。

create table test3 as select * from user

delete from test3 where righ is null删除空值的行用is

alter table test3 rename test4重命名

datetime、timestamp都是年月日时分秒的形式,time是时分秒、date是年月日。也就是说Java的Date类型可以被时间戳类型保存。

MySQL的不区分大小写是指不区分关键字和字段名的大小写,而字段的值是区分的。表名列名都叫做字段名例如alter table test3 rename test4;select current_Time from tesT4正确。

集合和枚举类型,和int等类型一样,是一个字段的值类型,表示该值只能为集合或枚举里面的元素的组成。

create table test7(name set('B','A','D'))
insert into test7 values('B,A')
select * from test7使用enum或set的好处是有效节省空间,因为虽然表现为字符串形式,但存储为字节。

alter table test7 engine=InnoDB charset=utf8修改引擎和字符集。

foreign key (外键字段) references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]

此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下,可以设置为null.前提是该外键列,没有not null。

可以不指定主表记录更改或更新时的动作,那么此时主表的操作被拒绝。

如果指定了 on update 或 on delete:在删除或更新时,有如下几个操作可以选择:

1. cascade,级联操作。主表数据被更新(主键值更新),从表也被更新(外键值更新)。主表记录被删除,从表相关记录也被删

2. set null,设置为null。主表数据被更新(主键值更新),从表的外键被设置为null。主表记录被删除,从表相关记录外键被设置成null。但注意,要求该外键列,没有not null属性约束。

3. restrict,拒绝父表删除和更新。

注意,外键只被InnoDB存储引擎所支持。其他引擎是不支持的。

select now() from user、select current_time from user。

truncate是删除表后再创建,delete是逐条删除,delete知道删除到了第几行,前者不知道。

select返回的其实是一个表。

视图有啥作用呢,因为视图是靠as实现的,而as后面的select语句往往复杂,那我们得到视图后是对视图查询而且客户端也看不到表的结构,就保证了业务逻辑性和安全性。

临时表temporary table。视图没有数据文件,只有数据结构。

触发器。

是基于表的某些操作,不可以给视图和临时表弄触发器。

create user gou@localhost identified by '123456'创建用户。

非超级用户(root)可以创建用户,但不能分配权限grant select on user to gou无效,没权利。

撤销权限revoke from。

可重复读。

我这个线程先利用record+gap获得了修改、查询、添加、删除的机会,我就开始操作了,比如说我insert、delete等的时候会争取并获得锁,然后如果之后其他事务开启了读,其他事务就会利用mvcc使我的后续修改无效,它读的也是那一个数据。可以结合我交卷老师读卷的例子。而幻读只不过把间隙锁也就是gap的范围扩大的很大。

如果建表时列名和关键字重名,用tab上面的那个点号括起来。

数据库里的时间。

time_stamp比date_time好,因为前者会随着时区的改变而自动改变时间,后者不会。

create table info_time(
`date_time` datetime default null,注意这里default的值不可以是current_Timestamp或current_time。类型不符。但可以是now()。
`time_stamp` timestamp default current_Timestamp
)engine=InnoDB default charset=utf8;

insert into info_time values(now(),now());插入时间后数据库里就一直是这个时间数据,只不过类型为时间类型,也就是说该事件不会随着你不断select而不断更新。
select * from info_time;一直查询到的都是插入时的时间,不管你后续有没有执行更新操作都是查询到这个时间。
set time_zone='-8:00';我测试的是查询到的时间比现在的时间要晚八个小时,且设置时区后无任何变化。

 

创建表千万不要用单引号或双引号圈住字段名或表名或约束名,要用tab键上的点号。

posted @ 2020-07-01 14:58  千与千寻_如是  阅读(120)  评论(0编辑  收藏  举报
价值决定认知,认知决定价值观