王宇涛的博客

MySQL-事务-锁-索引-树

 
  • 2022年 04月01日
  • 0 阅读

事务

事务(Transaction)：指要做的或所做的事情。MySQL事务是由一条或者多条sql语句构成，它们形成一个逻辑的工作单元，这些sql语句要么全部执行成功要么全部执行失败。事务是保证数据完整性和一致性的重要手段。

事务类型

  • DML事务：由一条或者多条DML语句构成
  • DDL事务：总是由一条DDL语句构成
  • DCL事务：总是有一条DCL语句构成

事务开始和结束

系统变量@@autocommit自动提交默认打开，所以执行任何一条sql语句都会开始一个事务，语句执行完，事务自动结束。

mysql>show variables like 'autocommit'; #变量查看自动提交，ON表示自动提交打开
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
mysql>select @@autocommit; #1默认打开
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

mysql>set @@autocommit = 0; #关闭自动提交
  • 对于DDL和DCL事务，在执行每一条语句之前和之后，MySQL会自动执行一条COMMIT语句，因此事务是自动开始和结束的，自动提交打开或者关闭对这些事务没有影响。
  • 对于DML事务，在自动提交关闭的情况下，事务的开始分为隐式开始和显式开始
  • 隐式就是自动，程序在第一条DML语句执行时，自动开始一个新事务。

显式就是手动，发出START TRANSACTION语句。该语句会关闭自动提交，事务结束后，autocommit变量恢复到原来的值。

事务的结束

  • COMMIT语句：成功提交，将数据库在内存中的数据落盘，事务所做的全部工作被永久地保存到磁盘上
  • ROLLBACK语句：失败回滚，将内存中的数据撤销，事务所做的全部工作被撤销，表中的数据不受事务操作的影响

COMMIT或ROLLBACK语句之前数据的状态

  • 数据的修改都是在内存中进行的
  • 通过查询表，当前用户（事务）能够查看DML操作的结果
  • 其他用户（事务）不能查看当前用户（事务）所做的DML操作的结果。这叫做不允许脏读(dirty read)。脏读：一个事务读到了另一个事务未提交的数据。已修改但未提交的数据叫做脏数据。
  • 表中受影响的行被锁定，其他用户（事务）不能在受影响的行上修改数据。

COMMIT或ROLLBACK语句之后数据的状态

COMMIT之后

  • 数据改变被写到数据库中
  • 所有用户可以查看事务的结果
  • 表中受影响行上的锁被释放，这些行可以被其他用户（事务）修改
  • 事务中所有的保存点被删除

ROLLBACK之后

  • 数据改变被撤销
  • 数据先前的状态被恢复
  • 表中受影响行上的锁被释放

数据库读现象

并发事务的四个问题

高并发场景下，并发的多个事务去操作同一份数据，存在的问题

  • 脏读(dirty read)：一个事务读到了另一个事务未提交的数据。

  • 不可重复读(nonrepeatable read)：同一个事务在读取某个数据后，隔一段时间再次读取该条数据，在这中间该数据被另一个事务给修改，导致两次读取的数据不一致。

  • 幻读：不可重复读的一种现象，同一个事务使用相同的查询条件读取以前检索的数据，却查询到了其他事务插入的符合条件的数据。幻读和不可重复读两者都是表现为两次读取的结果不一致，区别：不可重复读的重点是修改，幻读的重点是新增或删除。

  • 丢失更新(lost update)：一个事务的修改覆盖了另一个事务所做的修改。（顺序执行，事务不是同时发生）

事务的四种隔离级别

读未提交：一个事务可以读取另一个未提交事务的数据。

读已提交：一个事务要等另一个事务提交后才能读取数据。

可重复读：事务开启时，不再允许修改操作

可串行化：事务串行化顺序执行

事务隔离级别 脏读 不可重复读 幻读 丢失更新
Read uncommitted（读未提交） 是 是 是 是
Read committed（读已提交） 否 是 是 是
Repeatable read（可重复读） 否 否 否 是
Serializable（可串行化） 否 否 否 否

MySQL默认隔离级别：可重复读

mysql>show variables like 'tx_isolation'; #变量查看默认隔离级别
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

mysql>select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

事务的四个特性（ACID）

原子性(Atomicity)：指事物包含的所有操作要么全部成功，要么全部回滚

一致性(Consistency)：事物必须是数据库从一个一致性状态到另一个一致性状态。也就是说一个事物执行之前和执行之后都必须处于一致性状态。

隔离性(Isolation)：当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

持久性(Durability)：一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

锁

颗粒度划分

行锁 ：Innodb默认，锁定行操作

表锁：锁定整个表

兼容性划分

读锁：共享锁，当前会话和其他会话可以读但无法修改。

写锁：排他锁，只有当前会话可以读写，其他会话无法读写。

特征划分

悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，操作之后解锁。保证数据的独占性和正确性但是一直重复上锁解锁导致性能低。

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据。多并发性能高。

死锁活锁

死锁：两个或者多个线程互相持有对方所需要的资源，导致这些线程处于等待状态，无法前往执行。

活锁：任务没有被阻塞，由于某些条件没有满足，导致一直等待重复尝试，无法前往执行，产生活锁。

行级锁定的优缺点

  • 优势：1.当在许多线程中访问不一样的行时只存在少许锁定冲突；2.回滚时只有少许的更改 3.能够长时间锁定单一的行
  • 缺点：1.比页级或表级锁定占用更多的内存；2.当在表的大部分中使用时，比页级或表级锁定速度慢，由于你必须获取更多的锁；3.若是你在大部分数据上常常进行GROUP BY操做或者必须常常扫描整个表，比其它锁定明显慢不少；4.用高级别锁定，经过支持不一样的类型锁定，你也能够很容易地调节应用程序，由于其锁成本小于行级锁定

key和index（主键和索引）的区别

  • key是数据库的物理结构，它包含两层意义和做用，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key，unique key，foregin key等
  • index是数据库的物理结构，它只是辅助查询的，它建立时会在另外的表空间（mysql中的innodb表空间）以一个相似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等

索引

索引

MySQL索引的优势

索引类似目录

  • 索引大大减小了服务器须要扫描的数据量
  • 索引能够帮助服务器避免排序和临时表
  • 索引能够将随机I/O变为顺序I/O

什么状况下应不建或少建索引：

  • 表记录太少
  • 常常插入、删除、修改的表
  • 数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每一个值的分布几率大约为50%，那么对这种表A字段建索引通常不会提升数据库的查询速度。
  • 常常和主字段一块查询但主字段索引值比较多的表字段

为什么MySQL InnoDB 存储引擎要用B+树做索引，而不用B树

B树和B+树的区别

  • B树，每一个节点都存储key和data，全部的节点组成这可树，而且叶子节点指针为null，叶子节点不包含任何关键字信息
  • B+树，全部的叶子节点中包含所有关键字的信息，及指向含有这些关键字记录的指针，且叶子节点自己依关键字的大小自小到大的顺序连接，全部的非终端节点能够当作是索引部分，节点中仅含有其子树根节点中最大（或最小）关键字
搜索