MySQL概述

MySQL概述

• 1、MySQL逻辑架构

  

• 最上层是连接/线程处理，即连接处理、授权认证、安全等等

  • 每个客户端连接多会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行
  • 服务器会负责缓存线程，不需要为每一个新建的连接创建或者销毁线程
  • 认证基于用户名、原始主机信息、密码

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

  • MySQL会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询、决定标的读取顺序，以及选择合适的索引等
  • 用户可以通过特殊的关键字提示（hint）优化器，影响它的决策过程
  • 用户可以请求优化器解释（explain）优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置，使应用尽可能高效运行
  • 优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的

• 第三层是存储引擎，负责MySQL数据的存储和提取，服务器通过API与存储引擎进行通信，这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明

• 2、并发控制

• 多个查询需要在同一时刻修改数据，都会产生并发控制的问题

  • 经典问题：邮箱

• 读写锁

  • 共享锁（shared lock），即读锁（read lock）：读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一个资源，而互不干扰
  • 排它锁（exclusive lock），即写锁（write lock）：写锁是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，只有这个，才能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源

• 锁粒度

  • 一种提高共享资源并发行的方式就是让锁定对象更有选择性，仅两只锁定需要修改的部分数据，而不是所有的资源。在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可
  • 加锁也需要消耗资源，锁的各种操作，包括获得锁、检查所是否已经解除、释放锁等，都会增加系统的开销
  • 锁策略就是在锁的开销和数据的安全性之间寻求平衡，每种MySQL存储引擎都可以实现自己的锁策略和锁粒度

• 表锁（table lock）

  • 开销最小
  • 一个用户在对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作
  • 读锁之间是不互相阻塞的
  • MySQL本身也会使用各种有效的表锁来实现不同的目的，如服务器会为alter table之类的语句使用表锁，而忽略存储引擎的所机制

• 行锁（row lock）

  • 最大程度地支持并发
  • 最大的所开销
  • 行级锁只在存储引擎层实现，二MySQL服务器层没有实现

• 3、事务

• 事务就是一组原子性的SQL查询，或者说一个独立的工作单元，事务内的语句，要么全部执行成功，要么全部执行失败

  • 经典问题：银行

• 事务的操作

  • start transaction语句开始一个事务
  • commit提交事务
  • rollback回滚事务

• 事务的特性（ACID）

  • 原子性（atomicity）：一个事务是一个不可分割的最小工作单元，要么全部提交成功，要么全部失败回滚
  • 一致性（consistency）：数据库总是从一个一致性的状态转换到另外一个一致性的状态
  • 隔离性（isolation）：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的
  • 持久性（durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中

• 事务的开销

  • 事务处理过程中额外的安全性，也会需要数据库系统做更多的额外工作
  • 一个实现了ACID的数据库，通常会需要更强的CPU处理能力、更大的内存和更多的磁盘空间
  • 用户可以根据业务是否需要事务处理，选择合适的存储引擎

• 事务的隔离级别

  • read uncommitted（未提交读）：事务中的修改，即使没有提交，对其他事务也都是可见的，成为脏读（dirty read）
  • read committed（提交读）：一个事务从开始知道提交之前，所作的任何修改对其他事务都是不可见的，是大多数数据库系统的默认隔离级别
  • repeatable read（可重复读）：在同一个事务中多次读取同样记录的结果是一致的。但是，当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生换行，称为幻读，InnoDB通过多版本并发控制MVCC解决了幻读的问题
  • serializable（可串行化）：强制事务串行执行，在读取的每一行数据都加锁，但可能导致大量的超时和锁争用的问题，是最高的隔离级别

  隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁读 read uncommitted Yes Yes Yes No read committed No Yes Yes No repeatable read No No Yes No serializable No No No Yes

• 死锁

  • 两个或者多个事务在统一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的想象
  • 死锁检测：检测死锁的循环依赖，并立即返回一个错误，这种解决方式很有效，In弄DB目前处理死锁的方法是，将持有最少行级排它锁的事务进行回滚
  • 死锁超时机制：当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太好
  • 死锁的产生有双重原因：有些是因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由于存储引擎的实现方式导致的

• 事务日志

  • 事务日志可以帮助提高事务的效率
  • 存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
  • 事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多
  • 事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘
  • 目前大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘
  • 如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据

• MySQL中的事务

  • 自动提交（autocommit）
  • MySQL默认采用自动提交模式，即不是显示地开始一个事物，则每个查询都被当作一个事务执行提交操作
    -可以通过设置autocommit变量来启用或者禁用自动提交模式：
    mysql> show variables like 'autocommit'; mysql> set autocommit=1;

    1或者ON表示启用，0或者OFF表示禁用。当autocommit=0时，所有的查询都是在一个事务中，直到显示地执行commit提交或者rollback回滚，该事务结束，同时又开始了另一个新事务

• 多版本并发控制（MVCC）

  • MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
  • InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间），存储的不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。食物开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较