mysql

Ksssss
216 阅读11分钟

Mysql

InnoDB和MyISAM的最大不同点有两个：

InnoDB支持事务(transaction)；MyISAM不支持事务 Innodb 默认采用行锁， MyISAM 是默认采用表锁。加锁可以保证事务的一致性，可谓是有人(锁)的地方，就有江湖(事务) MyISAM不适合高并发

事务的基本要素

  1. 原子性：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行
  2. 一致性：事务开始前和结束后，数据库的完整性约束没有被破坏。
  3. 隔离性：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。
  4. 持久性：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的并发问题

  1. 脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据
  2. 不可重复读:事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。（隔离性更高的事务来解决）
  3. 幻读: 幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行（新增的行）。即使把所有的记录都加上锁，还是阻止不了新插入的记录。(可重复读的默认加锁是间隙锁，间隙锁来解决，但是间隙锁增加了锁范围，所以一般都是设置 读提交隔离级别加binlog_format=row来解决)。

MySQL事务隔离级别

事务隔离级别脏读不可重复读幻读
读未提交是是是
不可重复读否是是
可重复读否否是
串行化否否否

在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决时不彻底的。 通过索引加锁，间隙锁，next key lock可以解决幻读的问题。

Mysql的逻辑结构

  • 最上层的服务类似其他CS结构，比如连接处理，授权处理。
  • 第二层是Mysql的服务层，包括SQL的解析分析优化，存储过程触发器视图等也在这一层实现。
  • 最后一层是存储引擎的实现，类似于Java接口的实现，Mysql的执行器在执行SQL的时候只会关注API的调用，完全屏蔽了不同引擎实现间的差异。比如Select语句，先会判断当前用户是否拥有权限，其次到缓存（内存）查询是否有相应的结果集，如果没有再执行解析sql，优化生成执行计划，调用API执行。

SQL执行顺序

SQL的执行顺序：from---where--group by---having---select---order by

MVCC,redolog,undolog,binlog

  • undoLog 也就是我们常说的回滚日志文件 主要用于事务中执行失败，进行回滚，以及MVCC中对于数据历史版本的查看。由引擎层的InnoDB引擎实现,是逻辑日志,记录数据修改被修改前的值,比如"把id='B' 修改为id = 'B2' ，那么undo日志就会用来存放id ='B'的记录”。当一条数据需要更新前,会先把修改前的记录存储在undolog中,如果这个修改出现异常,,则会使用undo日志来实现回滚操作,保证事务的一致性。当事务提交之后，undo log并不能立马被删除,而是会被放到待清理链表中,待判断没有事物用到该版本的信息时才可以清理相应undolog。它保存了事务发生之前的数据的一个版本，用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。
  • redoLog 是重做日志文件是记录数据修改之后的值，用于持久化到磁盘中。redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。由引擎层的InnoDB引擎实现,是物理日志,记录的是物理数据页修改的信息,比如“某个数据页上内容发生了哪些改动”。当一条数据需要更新时,InnoDB会先将数据更新，然后记录redoLog 在内存中，然后找个时间将redoLog的操作执行到磁盘上的文件上。不管是否提交成功我都记录，你要是回滚了，那我连回滚的修改也记录。它确保了事务的持久性。
  • MVCC多版本并发控制是MySQL中基于乐观锁理论实现隔离级别的方式，用于读已提交和可重复读取隔离级别的实现。在MySQL中，会在表中每一条数据后面添加两个字段：最近修改该行数据的事务ID，指向该行（undolog表中）回滚段的指针。读的时候必须用事务id和之前修改的事务id做对比。判断行的可见性，创建一个新事务时，copy一份当前系统中的活跃事务列表。意思是，当前不应该被本事务看到的其他事务id列表。
  • binlog由Mysql的Server层实现,是逻辑日志,记录的是sql语句的原始逻辑，比如"把id='B' 修改为id = ‘B2’。binlog会写入指定大小的物理文件中,是追加写入的,当前文件写满则会创建新的文件写入。 产生:事务提交的时候,一次性将事务中的sql语句,按照一定的格式记录到binlog中。用于复制和恢复在主从复制中，从库利用主库上的binlog进行重播(执行日志中记录的修改逻辑),实现主从同步。业务数据不一致或者错了，用binlog恢复。

binlog和redolog的区别

  1. redolog是在InnoDB存储引擎层产生，而binlog是MySQL数据库的上层服务层产生的。
  2. redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑,比如“给ID=2这一行的c字段加1”。
  3. binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redolog是循环使用。
  4. binlog可以作为恢复数据使用，主从复制搭建，redolog作为异常宕机或者介质故障后的数据恢复使用。

io出现瓶颈

sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync
innodb_flush_log_at_trx_commit 设置为2的时候，表示每次事务提交时都只是把redo log写到page cache。（但是它会每秒刷新一次）

两阶段提交

主要是让两个状态保持一致。
更新的行先写repolog(处于parpare) ——>binlog->提交事务（处于commit）,并且二者通过xid关联;

Mysql如何保证一致性和持久性

MySQL为了保证ACID中的一致性和持久性，使用了WAL(Write-Ahead Logging,先写日志再写磁盘)。Redo log就是一种WAL的应用。当数据库忽然掉电，再重新启动时，MySQL可以通过Redo log还原数据。也就是说，每次事务提交时，不用同步刷新磁盘数据文件，只需要同步刷新Redo log就足够了。

InnoDB的行锁模式

  • 共享锁(S)：用法lock in share mode，又称读锁，允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
  • 排他锁(X)：用法for update，又称写锁，允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。在没有索引的情况下，InnoDB只能使用表锁。

为什么选择B+树作为索引结构

  • Hash索引：Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描
  • 平衡二叉树：通过旋转解决了平衡的问题，但是旋转操作效率太低，并且不支持范围查找。
  • B+树类似与跳表：在二叉树的基础上，将非叶节点改造为不存储数据纯索引节点，此外将叶节点使用链接成双链表，支持了范围查询。但是由于索引占的内存比较大，只把索引的根节点根节点会被存储在内存中。将索引存储到磁盘中，操作系统是按照页（4k）来读取的，如果超过一页，就会触发多次IO操作，对与b+树来说，子节点的数量是根据页面的大小计算出来的。插入和删除的时候，有可能超过一页。就会分裂和合并。

B+树的叶子节点都可以存哪些东西

可能存储的是整行数据，也有可能是主键的值。B+树的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引，然后通过主键的值回表。

索引优化

自增主键：一方面是主键是自增的，所以插入操作都是追加操作，不需要挪动其他记录。主键索引越小，普通索引存储的数据就越小，越节约空间。 覆盖索引：指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取，减少了回表次数。 最左原则：索引是按照定义索引的字段排序的（如果通过调整顺序，可以少维护一个索引，那这个顺序就是优先考虑的）。 索引下推：在遍历过程中，对包含索引的字段先判断，过滤掉不满足条件的记录。 change buffer：当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作（merge）。通过这种方式就能保证这个数据逻辑的正确性。主键索引为了判断唯一性(必须把数据业读到内存中)，所以不能使用change buffer，普通索引可以用上change buffer，所以对于写多读少的业务，使用普通索引，如果写完就伴随着查询，则数据先进入到change buff，然后再把数据页读到内存汇总 随后在触发merge过程，反而会增加维护代价。则可以关闭change buff。

查询在什么时候不走（预期中的）索引

  1. 模糊查询 %like
  2. 索引列参与计算,使用了函数
  3. 非最左前缀顺序
  4. where对null判断
  5. where不等于
  6. or操作有至少一个字段没有索引
  7. 需要回表的查询结果集过大（超过配置的范围）

数据库优化指南

  1. 创建并使用正确的索引
  2. 只返回需要的字段
  3. 减少交互次数（批量提交）
  4. 设置合理的Fetch Size（数据每次返回给客户端的条数）
avatar
文章
阅读
粉丝