mysql数据库学习随记

（一）MYSQL逻辑架构图

  为了从宏观层面了解MYSQL内部模块，通过下面的逻辑架构图了解其全貌，其图如下：

image.png

通过上图可以看到，MYSQL可以分为Server层和存储引擎两部分：
   server层主要包括连接器、查询缓存、分析器、执行器等，涵盖了MYSQL的大多数核心服务功能，以及所有的内置函数，所有跨存储引擎的功能都在这一层实现，比如存储过程，触发器、视图等。

• 连接器

负责与客户端建立连接，并验证用户的身份、权限等,包括sock通信和大多数基于客服端/服务端工具实现的类似于tcp/ip的通信，主要完成一些类似于连接处理、授权认证及相关安全的方案，在该层上引入了线程池的概念，为通过认证安全接入的客服端提供线程，同样在该层上可以实现基于SSL的安全的连接，服务器也会为安全接入的每个客户端验证它所具有的操作权限。。

• 查询缓存

主要将之前执行过的语句结果以KEY-VALUE对的形式缓存在内存中，其KEY是查询语句，VALUE是查询结果，如果缓存存在，则直接返回给客户端,当执行UPDATE语句会清空缓存。

• 分析器

分析器主要工作是做词法分析，较验SQL的语法是否正确、表和字段是否存在等。

• 优化器

优化器的主要确定语句的执行方案，比如怎么选索引，确定最优执行方案。

• 执行器

  • 判断权限：判断对操作表是否有执行查询的权限
  • 写BINLOG，记录SQL的原始语句
  • 调用引擎接口获取据，并将结果返回客户端

2. 存储引擎：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有功能不同，它的架构是插件式的，支持INnoDB\MYISAM\Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它是从MYSQL5.5.5版本开始成为了默认存储引擎。

（二）MYSQL基础概念

• 日志模块
  在实际工作当中，经常听到DBA说，mysql可以恢复到半个月内任意一秒的状态，它是如何做到的呢？这里不得不提它的两个重要日志模块：重做日志(redo log)和归档日志(binlog),redo log是物理的，而binglog是逻辑的，由于redo log属于innodb引擎，所以必须要有binlog,其主要原因要何证数据的一致性，必须要保证2份日志一致，也就是2阶段式提交，不能让中间环节出现，也就是一个成功，一个失败，如果意外导致以数据库crash后而能够复。

  • redolog

      在 MySQL 中，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就采用了日志（redo log）来提升更新效率。它是innodb引擎特有的，且是物理日志，记录是数据页做了什么修改，另外redo log是循环写，而日志和磁盘配合的整个过程，其实就是 MySQL 里的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（redolog buffer）里面，并更新内存（buffer pool），这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候（如系统空闲时），将这个操作记录更新到磁盘里面（刷脏页）。redo log 是 InnoDB 存储引擎层的日志，又称重做日志文件，redo log 是循环写的，redo log 不是记录数据页更新之后的状态，而是记录这个页做了什么改动。
      在存储引擎写redo log，有两个点：checkpoint和write pos ，如果 write pos 和 checkpoint 相遇，说明 redolog 已满，这个时候数据库停止进行数据库更新语句的执行，转而进行 redo log 日志同步到磁盘中。checkpoint 到 write pos 之间的部分等待落盘（先更新内存页，然后等待刷脏页）。有了 redo log 日志，那么在数据库进行异常重启的时候，可以根据 redo log 日志进行恢复，也就达到了 crash-safe。
      redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

  • binlog

      MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。binlog 属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠 binlog 是没有 crash-safe 能力的。binlog 有两种模式，statement 格式的话是记 sql 语句，row 格式会记录行的内容，记两条，更新前和更新后都有。sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数也建议设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。
• 事务隔离级别
    mysql中，事务支持是在引擎层实现，mysql是一个支持多引擎的系统，但并不是所有的引擎都支持事务，如myisam引擎不不支持，这也是为什么它会被innodb取代的重要原因之一。
    一提到事务，大家都会想到ACID，即原子性，一致性，隔离性，持久性，这就是事务的特性。在这里重点说一下隔离性，当隔离级别越严实，效率就会越低，因此很多的时候要在二者之间寻找一个平衡点，SQL标准的事务隔离级别包括：读未提交、读提交、可重复读和串行化。
  • 读未提交：一个事务还没有提交时，它的变更就能被别的事务看到。
  • 读提交：一个事务提交之后，它做的变更才会被别的事务看到。
  • 可重复读：一个事务执行过程中看到的数据，总是跟这个事务启动时看到的数据是一致的，当然在可重读隔离级别下，未提交变更对其它事务也是不可见的。
  • 串行化：读写都会加锁，当出现读写锁冲突的时候，后访问的事务必须
    等前一个事务执行完成后才能继续执行。
    以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。

事务隔离级别的实现

  理解了事务的隔离级别，那么事务隔是怎么实现的呢，下面以可重复读为例，在mysql中，每次更新操作都会同时记录一条回滚操作，通过回滚操作可以得到前一个状态的值，如下图：

image.png

  假设一个值从1被按顺序改成2，3，4，在回滚日志里面就会有如上图的记录。当前值是4，但是在查询这条记录的时候，不同时刻启动事务有不同的read-view.如图中看到的，视图A、B、C里面，这条记录的值分别是1，2，4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制mvcc,对于read-view A ,要得到1，就必须将当前值依次执行图中所有的回滚操作才能得到。那么日志什么时候删除呢，一般是在不需要的时候才删除，就是说系统会判断，当没有事务再需要用这到这些回滚时，就会被删除，也就是说系统里没有比这个回滚日志更早的read-view的时候。

事务隔离级别启动方式

  事务的启动试有以下两种：
1、显示式启动事务语句，begin或start transaction,配套的提交语句是commit，回滚语句是rollback.
2、set autocommit=0,这个命令会将这个系统的自动提交关闭，意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行commit或rollbak语句或断开连接。

• 索引
    数据库索引，索引的出现其实就是为了提高数据查询效率，就像书的目录一样，他就像书的目录一样，一本书500页，如果你想快速找到其中的某个知识点，在不借助目录的情况下，那估计得找一会儿，同样对于数据库的表而言，索引其实就是书的目录。

索引的模型

  索引的出现是为了提高查询效率，但是实现索引的方式却有很多种，我们常见有几种，它们分别是哈希表，有序数组和搜索树。
1、哈希表:它是一种以键-值（key-value）存储的数据结构，我们只要输入待查找的键key，就可以找到其对应的value，哈希的思路很简单，把值放在数组里，用一个哈希函数换算成一个确定的位置，然后把value放在数组的这个位置。哈希表这种结构适用于只有等值查询的场景。
2、有序数组：有序数组在范围查询和等值查询场景中性能就非常优秀，但有序数组索引只适用于静态存储引擎，其原因是当更新数据的时候，往中间插入一条记录就必须挪动后面所有的记录，成本就太高。
3、

• 锁
    

（三）工作中常用