MySQL数据库

2022-05-14 08:30:50

java-MySQL数据库

MySQL服务器架构图

MySQL数据库

第一层：连接处理、授权登录、安全等。

第二层：核心服务功能。包括查询解析、分析、优化、缓存、以及所有的内置函数（例如，日期、时间、数字和加密函数）、所以的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。

第三层：包含了存储引擎。存储引擎负责数据库的数据的存储和提取。

数据库系统原理

事务

概念：事务就是满足ACID的一组SQL查询或者说是一个独立地工作单元，可以通过commit提交事务，也可以使用Rollback回滚。

ACID

  1. 原子性（Atomicity）

    定义：是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个 SQL 语句执行失败，则已执行的语句也必须回滚，数据库回退到事务开始前的状态。

    实现原理：undo log

    在说明原子性的实现原理之前，我们先来了解一下 MySQL 的事务日志。MySQL 的日志有很多种，如二进制日志、错误日志、查询日志、慢查询日志等，此外 InnoDB 存储引擎还提供了两种事务日志：redo log（重做日志）和undo log（回滚日志）。其中，redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础。

    实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的 SQL 语句。InnoDB 实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB 会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

    undo log属于逻辑日志，它记录的是 SQL 执行的相关信息。当发生回滚时，InnoDB 会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。

    以update操作为例：当事务执行update时，其生成的undo log中会包含被修改行的主键（以便知道修改了哪些行）、修改了哪些列、这些列在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态

  2. 一致性（Consistency）

    定义：是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）。

    实现方式 可以说，一致性是事务追求的最终目标：前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。实现一致性的措施包括：

    1. 保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证；

    2. 数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等；

    3. 应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致。

 

  1. 隔离性（Isolation）

    定义：与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响。隔离性，是指事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。严格的隔离性，对应了事务隔离级别中的Serializable（可串行化），但实际应用中出于性能方面的考虑很少会使用可串行化。

    隔离性追求的是并发情形下事务之间互不干扰。简单起见，我们仅考虑最简单的读操作和写操作（暂时不考虑带锁读等特殊操作），那么隔离性的探讨，主要可以分为两个方面：

    • （一个事务）写操作对（另一个事务）写操作的影响：锁机制保证隔离性

    • （一个事务）写操作对（另一个事务）读操作的影响：MVCC 保证隔离性

  2. 持久性（Durability）

    定义：是指事务一旦提交，它对数据库的改变就应该是永久性的，接下来的其他操作或故障不应该对其有任何影响。

    实现原理：redo log

    redo log和undo log都属于 InnoDB 的事务日志。下面先聊一下redo log存在的背景。

    InnoDB 作为 MySQL 的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘 IO，效率会很低。为此，InnoDB 提供了缓存（Buffer Pool），Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中，这一过程称为“刷脏”。

    Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果 MySQL 宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

    于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果 MySQL 宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是 WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因 MySQL 宕机而丢失，从而满足了持久性要求。

    既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘（即刷脏）要快呢？主要有以下两方面的原因：

    • 刷脏是随机 IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序 IO。

    • 刷脏是以数据页（Page）为单位的，MySQL 默认页大小是 16 KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效 IO 大大减少。

    我们知道，在 MySQL 中还存在binlog（二进制日志）也可以记录写操作并用于数据的恢复，但二者是有着根本的不同的：

    • 作用不同：redo log是用于crash recovery的，保证 MySQL 宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。

    • 层次不同：redo log是 InnoDB 存储引擎实现的，而binlog是 MySQL 的服务器层实现的，同时支持 InnoDB 和其他存储引擎。

    • 内容不同：redo log是物理日志，内容基于磁盘的Page；binlog的内容是二进制的，根据binlog_format参数的不同，可能基于 SQL 语句、基于数据本身或者二者的混合。

    • 写入时机不同：binlog在事务提交时写入；redo log的写入时机相对多元：

      • 前面曾提到当事务提交时会调用fsync对redo log进行刷盘，这是默认情况下的策略，修改innodb_flush_log_at_trx_commit参数可以改变该策略，但事务的持久性将无法保证。

      • 除了事务提交时，还有其他刷盘时机，如master thread每秒刷盘一次redo log等，这样的好处是不一定要等到commit时刷盘，commit速度大大加快。

    总结： 下面总结一下 ACID 特性及其实现原理：

    原子性：保证事务要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的，实现主要基于undo log日志； 持久性：保证事务提交后不会因为宕机等原因导致数据丢失，实现主要基于redo log日志； 隔离性：保证事务执行尽可能不受其他事务影响，InnoDB 默认的隔离级别是RR，RR的实现主要基于锁机制、数据的隐藏列、undo log日志和类next-key lock机制； 一致性：事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障。

隔离级别

未提交读（READ UNCOMMITTED）

提交读（READ COMMITTED）

可重复读（REPEATABLE READ）

可串行化（SERIALIZABLE）

 

  脏读 不可重复读 幻读
未提交读 1 1 1
提交读 0 1 1
可重复读 0 0 1
可串行化 0 0 0

1：可能发生 0：不可能发生

实现隔离机制的方法主要有两种：

  • 加读写锁

  • 一致性快照读，即MVCC

 

InnoDB MVCC 机制

具体详见博客：InnoDB MVCC 机制，看这篇就够了

MVCC : 多版本并发控制，是InnoDB存储引擎基于乐观锁理论用来实现提交读和可重复读这两种隔离机制的具体实现方式。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

MVCC在InnoDB中的实现方式

在InnoDB中，给每条记录加两个隐藏列DATA_TRX_ID 、 DATA_ROLL_PTR （如果没有主键，则还会多一个隐藏的主键列）。这两列分别保存了行的创建时间，另一个保存了行的删除时间。这里存储的并不是实际的时间值，而是系统版本号（可以理解为事务的ID），每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID。

MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。

已提交读和可重复读的区别就在于它们生成ReadView的策略不同。ReadView中主要就是有个列表来存储我们系统中当前活跃着的读写事务，也就是begin了还未提交的事务。通过这个列表来判断记录的某个版本是否对当前事务可见。

如果你是可重复读隔离级别，这时候你的ReadView还是第一次select时候生成的ReadView,也就是列表的值还是[100]。所以select的结果是小明1。所以第二次select结果和第一次一样，所以叫可重复读！

也就是说已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

这就是Mysql的MVCC,通过版本链，实现多版本，可并发读-写，写-读。通过ReadView生成策略的不同实现不同的隔离级别。

 

快照读和当前读

快照读：简单的select操作(不包括 select ... lock in share mode, select ... for update),即不加锁的非阻塞读；快照读的前提是隔离级别不是未提交读和串行化级别，因为未提交读总是读取最新的数据行，而不是符合当前事务版本的数据行。而串行化则会对所有读取的行都加锁.

不加锁，不安全

当前读：像select lock in share mode(共享锁),select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

 

MySQL中的乐观锁和悲观锁

乐观锁

总是认为不会产生并发问题，每次去取数据的时候总认为不会有其他线程对数据进行修改，因此不会上锁，但是在更新时会判断其他线程在这之前有没有对数据进行修改，一般会使用版本号机制或CAS操作实现。

version方式：一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

核心SQL代码

update table set x=x+1, version=version+1 where id=#{id} and version=#{version};  

CAS操作方式：即compare and swap 或者 compare and set，涉及到三个操作数，数据所在的内存值，预期值，新值。当需要更新时，判断当前内存值与之前取到的值是否相等，若相等，则用新值更新，若失败则重试，一般情况下是一个自旋操作，即不断的重试

悲观锁

总是假设最坏的情况，每次取数据时都认为其他线程会修改，所以都会加锁（读锁、写锁、行锁等），当其他线程想要访问数据时，都需要阻塞挂起。可以依靠数据库实现，如行锁、读锁和写锁等，都是在操作之前加锁，在Java中，synchronized的思想也是悲观锁。

 

Next-Key Locks

Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。

MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。

Record Locks

锁定一个记录上的索引，而不是记录本身。

如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。

Gap Locks

锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

Next-Key Locks

它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。它锁定一个前开后闭区间，例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

 

InnoDB 和MyISAM的区别

  1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

  2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

  3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

    MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
    ?
    也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

  1. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

那么为什么InnoDB没有了这个变量呢？

因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。
如果索引并没有完全处于InnoDB维护的缓冲区（Buffer Pool）中，count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样，如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS

  1. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

  2. MyISAM表格可以被压缩后进行查询操作

  3. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

    InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

8、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

    Innodb：frm是表定义文件，ibd是数据文件
?
    Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

如何选择：

  1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

  1. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。

  2. 系统奔溃后，MyISAM恢复起来更困难，能否接受；

  3. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

 

InnoDB为什么推荐使用自增ID作为主键？

答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

 

innodb引擎的4大特性

   插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

 

参考资料

 

 

 

MySQL数据库

上一篇：Maya制作性感可爱兔女郎教程


下一篇：oracle基础~ash报告管理