MySQL 概况

目前，MySQL 是大多数公司最常使用的关系型数据库，开发人员基本上也经常使用。平时接触最多的就是，库、表、字段、索引、SQL 语句等。这次我将从逻辑架构、存储引擎、事务、并发控制、数据类型、索引这几方面来讲讲 MySQL 的故事。

1. MySQL逻辑架构

MySQL中逻辑结构设计关系模型_存储引擎


图一、MySQL 逻辑架构图

  1. 第一层负责连接管理、授权认证、安全等等。
    每个客户端的连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到 MySQL 服务器时，服务器对其进行认证。可以通过用户名和密码的方式进行认证，也可以通过 SSL 证书进行认证。登录认证通过后，服务器还会验证该客户端是否有执行某个查询的权限。
  2. 第二层负责解析查询(编译 SQL )，并对其进行优化(如调整表的读取顺序，选择合适的索引等)。对于 SELECT 语句，在解析查询前，服务器会先检查查询缓存，如果能在其中找到对应的查询结果，则无需再进行查询解析、优化等过程，直接返回查询结果。存储过程、触发器、视图等都在这一层实现。
  3. 第三层是存储引擎，存储引擎负责在 MySQL 中存储数据、提取数据、开启一个事务等等。存储引擎通过 API 与上层进行通信，这些API屏蔽了不同存储引擎之间的差异，使得这些差异对上层查询过程透明。存储引擎不会去解析 SQL。
2.存储引擎

讲完 MySQL 的逻辑架构，下面来讲讲 MySQL 的存储引擎，这一块相信大家都比较熟悉，InnoDB 是最最常见的 MySQL 存储引擎了。InnoDB 是事务性引擎，被用来处理大量短期事务。他的性能和自动崩溃恢复特性，使得他在非事务型存储的需求也很流行。
下面来简单介绍一下他
2.1InnoDB

  • InnoDB的数据存储在表空间，表空间是由InnoDB管理的一个黑盒子，有一些的数据文件组成，InnoDB 可讲每个表的数据和索引存放在单独的的文件中
  • InnoDB 采用 MVCC 来支持高并发，并实现了四个标准的隔离级别，其默认级别是 REPEATABLE READ 可重复读。通过间隙锁防止幻读的出现
  • InnoDB是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能，不过它的二级索引中必须包含主键列，所以主键应当尽可能的小
  • 内部优化，从磁盘读取数据时采用可预测性预读，能自动在内存中创建 hash 索引以加速读操作的自适应哈希索引，以及能加速插入操作的插入缓存区
  • 支持热备份

2.2 MyISAM
MyISAM 支持全文索引、压缩、空间函数（GIS）等，但不支持事务和行级锁，奔溃后无法安全恢复
特性

  • MyISAM 对整表加锁，但支持并发插入（在表友读取查询时，也可往表里插入新数据）
  • 可通过 REPAIR TABLE mytable 进行修复，可能导致一些数据丢失
  • 支持全文索引
  • DELAY_KEY_WRITE选项，可延迟更新索引
  • 压缩表

2.3 其他存储引擎

  • Archive引擎
  • Blackhole引擎
  • CSV引擎
  • Federated引擎
  • Memory引擎
  • Merge引擎
  • NDB集群引擎
  • ……

引擎选择，考虑的因素：
事务、备份、奔溃恢复、引擎特有优化等
大部分情况下InnoDB都是正确的选择，不要混合使用多种存储引擎

3. 事务

事务是一组原子性的 SQL 查询，或者说一个独立的工作单元，事务必须具备如下标准特征：ACID

  • Atomicity 原子性 undo log (回滚日志 )
  • Consistency 一致性 需要数据库和应用层面同时保障
  • Isolation 隔离行 锁机制，行锁与表锁 MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议
  • Durability 持久性 redo log（重做日志）
    3.1隔离级别
    SQL 中定义里四种隔离级别
  • READ UNCOMMITTED 未提交读 事务修改即使没提交，对其他事物也是可见的，事务可以读未提交的数据，也叫脏读 （Dirty Read）
  • READ COMMITTED 提交读 一个事务从开始到提交之前，所做的修改对其他事物都是不可见的，这个级别也叫不可重复读（nonrepeatable read）
  • REPEATABLE READ 可重复读 可能幻读，幻读指的是当某个事务在读取某个范围内的记录时，另外的事务在该范围内插入了新的记录，之前的事务在次读取时会产生幻读，InnoDB 的 MVCC 解决了幻读
  • SERIALIZABLE 可串行化 强制事务串行执行，在读取每行数据时都加锁，可能导致大量超时和锁争用问题

3.2 死锁
多事务在同一资源上相互占用，请求锁定对方占用的资源。
InnoDB 目前处理死锁的方法是，将持有最少级拍他锁的事务进行回滚
3.3 MySQL 中的事务
MySQL 默认采用自动提交模式，可通过 AUROCOMMIT 变量来启用或者禁用自动提交模式，当禁用时，所有查询都在一个事务中，直到显式执行 COMMIT 或 ROLLBACK，该事务结束。
InnoDB 采用两阶段锁定协议，在事务执行过程中，随时都可以执行锁定，锁只有在执行 COMMIT 或者 ROLLBACK 才会释放（隐式锁）
LOCK TABLES / UNLOCK TAVLES （显示锁）

4.并发控制

4.1读写锁
在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题，这两种类型的锁通常被称为共享锁（shared lock）和排他锁（excusive lock），也叫读锁（read lock）和写锁（write lock）
读锁式相互不阻塞的，写锁会阻塞其他锁
4.2 锁策略
在锁的开销和数据的安全性之间寻求平衡

  • 表锁，一个用户在对表进行写操作前，要获取写锁，会阻塞其他所有对该表的读写操作，只要没有写锁时，其他读取用户才能获取读锁，读锁之间不相互阻塞，写锁比读锁由更高的优先级。
  • 行锁
5.多版本并发控制（MVCC）

MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作
MVCC 通过保存数据在某个时间点的快照来实现，不管执行多长时间，每个事务看到的数据都是一致的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。
InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏列来实现的，一个保存了行的创建时间，一个保存了行的国旗时间，存储的不是时间时间，是系统版本号

  • SELECT
    InnoDB 会根据以下两个条件检查每行记录
    a. InnoDB 只查找版本早于当前事务版本的数据行，确保事务读取的 行在事务开始之前就存在，或是事务自身插入或修改的
    b. 行的删除版本要么未定义，要么大于当前事务版本，以确保事务读
    取到的行，在事务开始之前未被删除
  • INSERT
    InnoDB 为新插入的每一行保存当前系统版本号作为行版本号
  • DELETE
    InnoDB 为删除的每一行保存当前系统版本号作为行删除标志
  • UPDATE
    InnoDB 为插入一行新记录，保存当前系统版本号作为行版本号，同时保
    存当前系统版本号到原来的行作为行删除标志