行锁、表锁、主键外键、表之间的关联关系

红太狼 2024-03-26 13:46 80阅读 0赞

> [Java知识点总结：想看的可以从这里进入][Java]

#### 目录 ####

*   *   *  2.4、行锁、表锁
         *  2.5、主键、外键
         *   *  2.5.1、主键
             *  2.5.2、外键
         *  2.6、表的关联关系

#### 2.4、行锁、表锁 ####

MyISAM默认采用表级锁，InnoDB默认采用行级锁。

1.  表锁：开销小，加锁快，不会出现死锁，但锁的粒度较大，容易发生锁冲突，并发度低
    
    MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁。（**读锁会阻塞写，写锁会阻塞读和写**）
    
     *  表独占写锁：会阻塞其它进程对同一表的读和写操作。
     *  表共享读锁：不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。
2.  行锁：开销大，加锁慢，会出现死锁，但是锁的粒度最小，不容易发生锁冲突，并发度高
    
    insert，delete，update在事务中都会自动默认加上排它锁，select语句后加上for update会被加上排它锁。其它事务可以读取，但不能进行更新和插入操作
    
    MySQL的行锁是通过索引加载的，行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁，此时其它事务无法对当前表进行更新或插入操作。
    
     *  对于主键索引：直接锁住主键索引即可。
     *  对于普通索引：先锁住普通索引，再锁住主键索引（因为一张表只有通过主键索引才能确保锁是唯一的）
3.  页面锁：介于表锁和行锁之间

<table> 
 <thead> 
  <tr> 
   <th align="center">MySQL锁类别</th> 
   <th align="center">资源开销</th> 
   <th align="center">加锁速度</th> 
   <th align="center">是否会出现死锁</th> 
   <th align="center">锁的粒度</th> 
   <th align="center">并发度</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">表级锁</td> 
   <td align="center">小</td> 
   <td align="center">快</td> 
   <td align="center">不会</td> 
   <td align="center">大</td> 
   <td align="center">低</td> 
  </tr> 
  <tr> 
   <td align="center">行级锁</td> 
   <td align="center">大</td> 
   <td align="center">慢</td> 
   <td align="center">会</td> 
   <td align="center">小</td> 
   <td align="center">高</td> 
  </tr> 
  <tr> 
   <td align="center">页面锁</td> 
   <td align="center">一般</td> 
   <td align="center">一般</td> 
   <td align="center">不会</td> 
   <td align="center">一般</td> 
   <td align="center">一般</td> 
  </tr> 
 </tbody> 
</table>

但实际上MySql的存储引擎真实的实现不是简单的行级锁，一般都是实现了多版本并发控制（MVCC）。MVCC是行级锁的变种，多数情况下避免了加锁操作，开销更低。MVCC是通过保存数据的某个时间点快照实现的。

#### 2.5、主键、外键 ####

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>主键</th> 
   <th>外键（约束）</th> 
   <th>索引</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>定义</td> 
   <td>唯一标识，不能有重复的，不允许为空</td> 
   <td>两张表有关联时，可设置外键，外键的字段存放的数据是另一张表的主键值，可重复 ，可为空</td> 
   <td>该字段没有重复值，但可以有一个空值</td> 
  </tr> 
  <tr> 
   <td>作用</td> 
   <td>保证数据的完整性，不重复，可加快数据库的操作速度</td> 
   <td>表示和其他表有关联（如：购物车表里要有商品id和购买用户的id）</td> 
   <td>提高查询排序的速度</td> 
  </tr> 
  <tr> 
   <td>个数</td> 
   <td>一个表只能有一个主键</td> 
   <td>一个表可以有多个外键</td> 
   <td>一个表可以有多个唯一的索引</td> 
  </tr> 
 </tbody> 
</table>

##### 2.5.1、主键 #####

主键类似于我们日常生活中的身份证，一个身份证号码代表一个人，身份证号码是不会重复的，所以我们经常用身份证去区分一个人，而主键大致的功能也是如此。

如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。如果没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。

我们在建表的时候一般会创建一个主键，通常都是设置为自增（auto\_increment ）且不可为空，而且一个表中只能有一个primary key主键。

![创建主键索引][e839e4fd4f6f4b66279b983d9fbc89b6.png]

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果使用非自增主键，由于每次插入主键的值近似于随机，因此每次新记录都要被插到现有索引页的中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

主键虽然在语法上并非必须的，但是我们在数据库建表规定中必须给每张表添加一个主键，便于后续对数据库的维护和管理。

![阿里的Java开发手册上的建表规范][Java 1]

##### 2.5.2、外键 #####

关系型数据库是通过一个个表来存储数据的，而一些表和表之间会有某些关联，所以我们通过外键约束来将数据联系起来。使用外键与主键结合，即表的外键就是另一张表的主键，作用就是通过主外键的之间关系使对张表中的数据更好的关联。

外键就是你在表中的某一字段和另外一个表中的某个字段关联起来。比如把学生和班级关联起来，表示该学生是几班的。

![image-20230223121834446][]

1.  外键可以保证数据的完整性和一致性
2.  外键是某个表中的一列，它包含在另一个表的主键中
3.  为一张表创建一个外键，这个外键总是关联另一张表的主键。
4.  外键也是索引的一种，是通过一张表中的一列指向另一张表中的主键，来对两张表进行关联。
5.  一张表可以有一个外键，也可以存在多个外键，与多张表进行关联。

外键的作用是保证数据的一致性和完整性，并且减少数据冗余。

1.  当插入新数据时，其外键的字段在插入数据时，如果该关联表的主键不存在时，会阻止插入数据。
2.  当删除数据时，如果该外键字段所关联的表中该主键数据还在使用时，则阻止删除该数据。
3.  外联的主表修改数据时，添加为外键的字段会一起修改

--  添加外键
    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中表和表之间的关联分为四种：**一对一关联、一对多（或多对一）关联、多对多关联、自我关联**

1.  一对一：一张表的一条记录一定只能与另外一张表的一条记录进行对应，反正亦然。这时候主表的主键和从表的主键形成主外健关系，让一张表的主键同时作为另一张表的主键，同时这一张表的主键不能自增。
    
    比如：在建立数据表时，如果一张表的字段过多，那么为了提高效率，通常会把一张表分成两张表来存储
2.  多对一、一对多：一张表中有一条记录可以对应另外一张表中的多条记录，另外—张表的一条记录只能对应第—张表的一条记录
    
    比如学生关联班级，多个学生关联一个班级，一个班级关联多个学生
    
    ![image-20230223140606226][]
3.  多对多：表A的一条记录能够对应另外一张表B中的多条记录，同时表B中的一条记录也能对应表A中的多条记录。这种的对应关系通常是专门建立第三张表，这个表专门用来表示表A和表B的对应关系。
    
    比如说员工和职位，一个员工可以担任多个职位，一个职位也能由多个员工担任
    
    ![image-20230223140814348][]
4.  自我连接：在一张表内引用自己的字段
    
    比如常见的省市区，在一张表时，就是通过设置父级id来实现的
    
    ![image-20230223140105923][]

[Java]: https://blog.csdn.net/yuandfeng/article/details/114333525
[e839e4fd4f6f4b66279b983d9fbc89b6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/14e2513d8ae74684a74fa9cfdc852aaf.png
[Java 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/be70ef6c2e63475abe9e9af1489a1c5d.png
[image-20230223121834446]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/f5c9bd7e7b9c4a6095c2bd96ca721b08.png
[image-20230223140606226]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/aba3bdf30daa4edeac82ac11d09cee3a.png
[image-20230223140814348]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c809baa0a7ac4e4d9e9f476808ab8de9.png
[image-20230223140105923]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/47326eebbbe34c898499114694bdd32a.png