MySQL进阶(二)

MySQL基础知识

InnoDB架构

  • 这是官方文档中的架构图

innodb-architecture

划重点

MySQL涉及到的知识太庞大了,对与业务开发人员来说掌握一部分经常使用的知识即可。

  • 索引
  • 数据库隔离

索引

目前工作中主要使用的数据库引擎就是InnoDB,所以关于索引的知识必不可少。

  • 在了解索引之前,大概理解下mysql存储数据的大小划分表空间 -> 段 -> 区 -> 数据页

  • 索引的优点:1. 天生排序。2. 快速查找;索引的缺点:1. 占用空间。2. 降低更新表的速度。

  • 注意点:

    • 小表使用全表扫描更快,中大表才使用索引。超级大表索引基本无效。

    • 索引实现,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引)

    • 功能区分,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引。

      1、普通索引:最基本的索引,没有任何约束。
      2、唯一索引:与普通索引类似,但具有唯一性约束。
      3、主键索引:特殊的唯一索引,不允许有空值
      4、复合索引:将多个列组合在一起创建索引,可以覆盖多个列。
      5、外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作。
      6、全文索引:MySQL 自带的全文索引只能用于 InnoDB、MyISAM ,并且只能对英文进行全文检索,一般使用全文索引引擎(ES,Solr)。

  • 注意:主键就是唯一索引,但是唯一索引不一定是主键,唯一索引可以为空,但是空值只能有一个,主键不能为空。

事物隔离

ACID模式是一组数据库设计原则强调的是,对于业务数据和关键任务应用重要的可靠性方面。MySQL包含诸如的组件InnoDB存储引擎与ACID模型紧密结合,因此数据不会损坏,并且不会因软件崩溃和硬件故障等特殊情况而导致结果失真。当您依赖符合ACID的功能时,您无需重新发明一致性检查和崩溃恢复机制。如果您有其他软件安全措施,超可靠硬件或可以容忍少量数据丢失或不一致的应用程序,您可以调整MySQL设置以交换一些ACID可靠性以获得更高的性能或吞吐量。

  • 原子性 Atomicity :一个事务(transaction)中的所有操作,或者全部完成,或者全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被恢复(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。即,事务不可分割、不可约简。
  • 一致性 Consistency :在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
  • 隔离性 Isolation :数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
  • 持久性 Durability :事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

  • 四个特性,最重要的就是一致性。而一致性由原子性,隔离性,持久性来保证。

    • 原子性由 Undo log 保证。Undo Log 会保存每次变更之前的记录,从而在发生错误时进行回滚。
    • 隔离性由 MVCC 和 Lock 保证。这个后面说。
    • 持久性由 Redo Log 保证。每次真正修改数据之前,都会将记录写到 Redo Log 中,只有 Redo Log 写入成功,才会真正的写入到 B+ 树中,如果提交之前断电,就可以通过 Redo Log 恢复记录。
事务隔离级别 脏读 不可重复读 幻读
读未提交(read-uncommitted) 1
读已提交(read-committed) 2
可重复读(repeatable-read) 3 是(x)
串行化(serializable) 4
  • 读未提交,一个事物未提交的时候,它做的数据变更就能被其它食物看到。
  • 读提交,一个事物提交后,它做的数据变更才能被其它事物看到。
  • 可重复读,一个事物执行的过程中看到的数据,总是跟这个事物在启动时候看的数据一致。
  • 串行化,对同一行数据 会加写锁,会加读锁。当出现读写冲突的时候,必须按数据执行事务。

并发事务的问题

脏读

一个事务正在对一条记录进行修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态。这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。

时间 事务A 事务B
T1 开启事务 开启事务
T2 查询账户余额为1000
T3 充值500,余额修改为1500
T4 查询余额为1500
T5 撤销事务,余额改回1000
T6 汇入500,余额修改为2000
T7 提交事务

不可重复读

一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了变更、或者某些记录已经被删除了。

时间 事务A 事务B
T1 开启事务 开启事务
T2 select * from user where user_id=100 假设为小明的用户信息
T3 将user_id=100的用户信息对应的年龄修改为18
T4 提交事务
T5 再次查询发现用户的年龄变更
T6
T7 提交事务

幻读

一个事务按相同的查询条件重新读取以前检索过的数据,却发现其它事务插入了满足其查询条件的新数据。

时间 事务A 事务B
T1 开启事务 开启事务
T2 select * from user where age=18 假设得到两条记录
T3 向user表插入一条age=18的新记录
T4 提交事务
T5 再次查询得到三条记录
T6 ..
T7 提交事务

幻读和不可重复读的区别

  • 不可重复读的重点是修改:在同一事务中,相同的条件,第一次和第二次读到的数据不一致(中间有其它事务提交了修改)。
  • 幻读的重点是新增或者删除:在同一事务中,相同的条件,第一次和第二次读到的记录数不一样(中间有其它事务提交了新增或者删除)。

这很重要不管是面试工作,还是排查异常,出现问题的时候需要发现、定位、解决能力。也是开发人员必须掌握的一项能力。

理解下锁几种维度

1. 类型维度

  • 共享锁(读锁 / S 锁)

  • 排它锁(写锁 / X 锁)
    类型细分:

    • 意向共享锁
    • 意向排他(互斥)锁
  • 悲观锁(使用锁,即 for update)

  • 乐观锁(使用版本号字段,类似 CAS 机制,即用户自己控制。缺点:并发很高的时候,多了很多无用的重试)

2. 锁的粒度(粒度维度)

  • 表锁
  • 页锁(Mysql BerkeleyDB 引擎)
  • 行锁(InnoDB)

3. 锁的算法(算法维度)

  • Record Lock(单行记录)
  • Gap Lock(间隙锁,锁定一个范围,但不包含锁定记录)
  • Next-Key Lock(Record Lock + Gap Lock,锁定一个范围,并且锁定记录本身, MySql 防止幻读,就是使用此锁实现)