2.4、行锁、表锁
MyISAM默认采用表级锁,InnoDB默认采用行级锁。
-
表锁:开销小,加锁快,不会出现死锁,但锁的粒度较大,容易发生锁冲突,并发度低
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁。(读锁会阻塞写,写锁会阻塞读和写)
- 表独占写锁:会阻塞其它进程对同一表的读和写操作。
- 表共享读锁:不会阻塞其它进程对同一表的读请求,但会阻塞对同一表的写请求。
-
行锁:开销大,加锁慢,会出现死锁,但是锁的粒度最小,不容易发生锁冲突,并发度高
insert,delete,update在事务中都会自动默认加上排它锁,select语句后加上for update会被加上排它锁。其它事务可以读取,但不能进行更新和插入操作
MySQL的行锁是通过索引加载的,行锁是加在索引响应的行上的,要是对应的SQL语句没有走索引,则会全表扫描,行锁则无法实现,取而代之的是表锁,此时其它事务无法对当前表进行更新或插入操作。
-
对于主键索引:直接锁住主键索引即可。
-
对于普通索引:先锁住普通索引,再锁住主键索引(因为一张表只有通过主键索引才能确保锁是唯一的)
-
-
页面锁:介于表锁和行锁之间
MySQL锁类别 | 资源开销 | 加锁速度 | 是否会出现死锁 | 锁的粒度 | 并发度 |
---|---|---|---|---|---|
表级锁 | 小 | 快 | 不会 | 大 | 低 |
行级锁 | 大 | 慢 | 会 | 小 | 高 |
页面锁 | 一般 | 一般 | 不会 | 一般 | 一般 |
但实际上MySql的存储引擎真实的实现不是简单的行级锁,一般都是实现了多版本并发控制(MVCC)。MVCC是行级锁的变种,多数情况下避免了加锁操作,开销更低。MVCC是通过保存数据的某个时间点快照实现的。
2.5、主键、外键
主键 | 外键(约束) | 索引 | |
---|---|---|---|
定义 | 唯一标识,不能有重复的,不允许为空 | 两张表有关联时,可设置外键,外键的字段存放的数据是另一张表的主键值,可重复 ,可为空 | 该字段没有重复值,但可以有一个空值 |
作用 | 保证数据的完整性,不重复,可加快数据库的操作速度 | 表示和其他表有关联(如:购物车表里要有商品id和购买用户的id) | 提高查询排序的速度 |
个数 | 一个表只能有一个主键 | 一个表可以有多个外键 | 一个表可以有多个唯一的索引 |
2.5.1、主键
主键类似于我们日常生活中的身份证,一个身份证号码代表一个人,身份证号码是不会重复的,所以我们经常用身份证去区分一个人,而主键大致的功能也是如此。
如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引。如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。如果没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。
我们在建表的时候一般会创建一个主键,通常都是设置为自增(auto_increment )且不可为空,而且一个表中只能有一个primary key主键。
如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如果使用非自增主键,由于每次插入主键的值近似于随机,因此每次新记录都要被插到现有索引页的中间某个位置,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
主键虽然在语法上并非必须的,但是我们在数据库建表规定中必须给每张表添加一个主键,便于后续对数据库的维护和管理。
2.5.2、外键
关系型数据库是通过一个个表来存储数据的,而一些表和表之间会有某些关联,所以我们通过外键约束来将数据联系起来。使用外键与主键结合,即表的外键就是另一张表的主键,作用就是通过主外键的之间关系使对张表中的数据更好的关联。
外键就是你在表中的某一字段和另外一个表中的某个字段关联起来。比如把学生和班级关联起来,表示该学生是几班的。
- 外键可以保证数据的完整性和一致性
- 外键是某个表中的一列,它包含在另一个表的主键中
- 为一张表创建一个外键,这个外键总是关联另一张表的主键。
- 外键也是索引的一种,是通过一张表中的一列指向另一张表中的主键,来对两张表进行关联。
- 一张表可以有一个外键,也可以存在多个外键,与多张表进行关联。
外键的作用是保证数据的一致性和完整性,并且减少数据冗余。
- 当插入新数据时,其外键的字段在插入数据时,如果该关联表的主键不存在时,会阻止插入数据。
- 当删除数据时,如果该外键字段所关联的表中该主键数据还在使用时,则阻止删除该数据。
- 外联的主表修改数据时,添加为外键的字段会一起修改
-- 添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 外表表名(主键字段名) -- 限制外表中的外键改动(默认值,也是最安全的设置)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
需要主要:在建表规范中不建议使用外键约束,外键的概念在应用层解决,即java代码中解决,过多的使用外键约束,在开发过程中会导致开发的繁琐,测试数据很不方便
2.6、表的关联关系
在MySQL中表和表之间的关联分为四种:一对一关联、一对多(或多对一)关联、多对多关联、自我关联
-
一对一:一张表的一条记录一定只能与另外一张表的一条记录进行对应,反正亦然。这时候主表的主键和从表的主键形成主外健关系,让一张表的主键同时作为另一张表的主键,同时这一张表的主键不能自增。
比如:在建立数据表时,如果一张表的字段过多,那么为了提高效率,通常会把一张表分成两张表来存储
-
多对一、一对多:一张表中有一条记录可以对应另外一张表中的多条记录,另外—张表的一条记录只能对应第—张表的一条记录
比如学生关联班级,多个学生关联一个班级,一个班级关联多个学生
-
多对多:表A的一条记录能够对应另外一张表B中的多条记录,同时表B中的一条记录也能对应表A中的多条记录。这种的对应关系通常是专门建立第三张表,这个表专门用来表示表A和表B的对应关系。
比如说员工和职位,一个员工可以担任多个职位,一个职位也能由多个员工担任
-
自我连接:在一张表内引用自己的字段
比如常见的省市区,在一张表时,就是通过设置父级id来实现的