MySQL基本架构

Server层

server层包括了连接器、查询缓存、分析器、优化器、执行器等，涵盖了MySQL的大多数核心服务功能，以及所有的内置函数（日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

连接器

连接器负责跟客户端建立连接、获取验证、维持和管理链接。

如果太长时间没有操作，连接器会自动断开链接，由wait_timeout参数控制，默认8小时。

建立连接的过程比较复杂，尽量使用长连接，但是有时候内存占用会涨得很快，长连接内存资源在断开时才会释放，解决办法：

1.定期断开长连接；

2.可以在每次执行一个比较大的操作后，执行mysql_reset_connection来重建初始化链接资源；

查询缓存

当拿到一个查询请求后，会去查询缓存看看是不是执行过这条语句。之前执行的语句会以key-value形式缓存在内存中。

但是大多数情况不建议使用查询缓存，因为只要有一个对表的更新，这个表上的所有查询缓存都会被清空。

分析器

如果查询没有命中查询缓存，则要开始执行语句，对SQL语句进行解析。

词法分析：识别出字符串分别是什么，代表什么；

语法分析：根据词法分析的结果，根据语法规则判断这个SQL语句是否满足MySQL的语法；

优化器

优化器作用：

1.在表有多个索引的时候，决定使用哪个索引；

2.有多表关联（join）的时候决定各个表连接的顺序；

执行器

开始执行的时候，先判断一下你对这个表T有没有执行查询的权限。如果有权限，执行器会根据表的引擎定义，去使用这个引擎提供的接口。

在慢查询日志中的rows_examined字段，表示这个语句执行过程中扫描了多少行，这个值就是在每次调用引擎获取数据行时候累加的，有时候执行器调用一次，引擎内部扫毛了多行，因此引擎扫描行数跟rows_examined并不是完全相同的。

存储引擎

存储引擎负责数据的存储和提取。架构是插件式的，支持InnoDB、MyISAM、Memory 等多个存储引擎。

redo log（重做日志）

  • WAL（Write-Ahead Logging）：先写日志，再写磁盘。

当一条记录需要更新的时候，InnoDB会把记录写到redo log中，并更新内存，这个时候就完成了更新。引擎会在适当的时候（比如空闲或者日志满），将记录更新到磁盘。

write pos是当前记录的位置，一边写一边后移；checkpoint是当前要擦除的位置，也是往后推移并循环的，擦除记录前要把记录更新到数据文件。

有了redo log，InnoDB可以保证数据库异常重启，提交的数据也不会丢失，称为crash-safe。

binlog（归档日志）

binlog是属于server层自己的日志。属于归档日志，没有crash-safe能力。

两种日志的不同点：

  1. redo log是InnoDB引擎特有的；binlog是MySQL的server层实现的，所有引擎都可以使用。
  2. redo log是物理日志，记录的是"在某个数据页上做了什么修改"；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如"给ID=2这一行的c字段加1"。
  3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。

执行器和InnoDB引擎在执行update语句的流程：

  1. 执行器找引擎获取ID=2这一行。如果这一行数据在内存中则直接返回给执行器；否则要先从磁盘读入内存。
  2. 执行器拿到这条数据，把这个值加一，得到新的一行数据，然后在调用引擎接口写入这行数据。
  3. 引擎将这行数据更新到内存中，同时记录redo log，此时redo log处于prepare状态，然后告知执行器执行完了，随时可以提交事务。
  4. 执行器生成这个操作的binlog，并把binlog写入磁盘。
  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成commit状态，更新完成。

两阶段提交：

redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致，类似事务。

索引

索引的出现就是为了提高查询效率，就像书的目录一样。

索引的常见模型

哈希表

是一种键值存储的数据结构，当哈希值冲突时可以用一个链表解决。

优点是新增数据时候执行很快，只需要追加就可以。但是由于不是有序的，对于区间查询是很慢的，必须要全表扫描。

有序数组

有序数组在等值查询和范围查询场景中的性能都非常优秀。

查询时可以用二分法，时间复杂度是 O(log(N))。

缺点是数据更新速度很慢，插入一条数据需要把此条记录后面的数据都往后挪动一位。

有序数组只适用于静态存储引擎。

二叉树

搜索速度是O(log(N))，当然为了维持查询复杂度就需要保持这是一颗平衡二叉树，更新的时间复杂度也是O(log(N))。

由于索引不只存储在内存中，还要写到磁盘上，所以大多数数据库使用多叉树。而这个多叉N取决于数据块的大小。

以InnoDB的一个整数字段索引为例，这个N差不多是1200。这树高4的时候，可以存储1200的3次方个值，17亿。

其他模型

这些模型都是不断迭代不断优化的产物或者解决方案，跳表、LSM树等数据结构也被用于引擎设计中。

数据库的底层存储核心就是基于这些数据模型的，对于一个数据库，我们需要先关注他的数据类型，才能从理论上分析出这个数据库的适用场景。

InnoDB的索引模型

在MySQL中索引是存储引擎层实现的，并没有统一的索引标准，不同引擎的索引工作方式不一样。

InnoDB中表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

InnoDB使用B+树索引模型，数据都是存储在B+树种，每一个索引对应一颗B+树。

假设有一个主键为id，其中一个字段为k，并且k建立索引。那么在这两颗树中，主键索引的叶子节点存储的是整行数据，也被称为聚簇索引；而k索引中叶子节点只存储主键id，非主键所以也称为二级索引。

所以根据叶子节点的数据内容，索引的类型分为主键索引和非主键索引。

那么，基于主键索引和非主键索引的查询有什么区别呢？

  • 如果条件是id，那么只需要遍历id这棵树。
  • 如果条件是k，就需要先根据k树查询id，再根据id查询数据。这个过程称为回表。

索引维护

B+树为了维护有序性，插入元素时需要做必要的维护。

  • 插入数据在最后，追加就可以
  • 插入数据在中间，需要逻辑上移动后面的数据
  • 如果插入的数据页满了，需要新申请一个数据页，挪动部分数据过去，称为页分裂，整体空间利用率降低大约50%
  • 删除数据后可能需要做页合并，分裂的逆过程

自增主键的插入数据模式，符合了递增插入的场景，而且也不会触发叶子节点的分裂。

而自定义主键往往不容易保证有序插入，写入成本相对较高。

存储空间方面，长整型8字节，显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间就越小。

所以大多数场景中自增主键往往是更合理的选择。

自定义主键使用场景：只有一个索引且该索引唯一。这是典型的KV场景。

覆盖索引

如果执行select ID from T where k between 3 and 5，只需要查id的值，id已经在k索引树上了，就不需要回表，索引k已经覆盖了查询需求，称为覆盖索引。可以减少查询次数，显著提高性能。

最左前缀原则

不只是索引的全部定义，只要满足最左前缀，就可以利用索引加速。可以是联合索引的最左N个字段，也可以是字符索引的最左M个字符。

在建立联合索引的时候如何安排索引内字段顺序？

主要看的是索引的复用能力。如果通过调整顺序，可以少维护一个索引，那么这个顺序就是需要优先考虑采用的。

索引下推

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

普通索引和唯一索引的选择

查询过程

假设执行的语句是select id from T where k=5。

  • 对于普通索引来说，查到满足条件的记录后，需要查找下一个记录，直到不满足k=5条件的记录
  • 对于唯一索引来说，由于唯一性，找到后就停止

那么对性能的影响呢？微乎其微。

更新过程

change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新。如果不在内存中，不影响数据一致性的前提下，InnoDB会将这些操作缓存在change buffer中，就不需要从磁盘中读入这个数据页了。下次查询需要访问这个数据页时，将数据页读入内存，然后执行change buffer中与这个页相关的操作。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页触发外，系统后台线程会定期merge，数据库正常关闭也会执行。

什么条件下可以使用change buffer？

对于唯一索引来说，所有更新操作都需要判断这个操作是否违反唯一性，必须要将数据页读入内存中才能判断，如果已经到了内存中就没必要使用change buffer。

唯一索引的更新不能使用change buffer，实际上也只有普通索引可以使用。

chenge buffer使用场景

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是记录的变更动作缓存下来，所以在merge之前记录的变更越多收益越大。

反过来，假设一个业务的更新模式是写入后马上会做查询，会立即出发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。

索引的选择

这两类索引在查询能力上是没有差别的，主要考虑的是更新对性能呢的影响。

由于唯一索引用不上change buffer优化机制，因此如果业务可以接受的情况下，可以先考虑非唯一索引。

字符串加前缀索引

使用前缀索引，定义好长度，就可以做到既节省空间，又不用增加很多的查询成本。 可以使用下面的语句算出这个列上有多少个不同的值：

mysql> select count(distinct email) as L from SUser;

mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
from SUser;

给字符串加索引的方式

  • 直接创建完整索引，这样可能会比较占用空间。
  • 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引。
  • 使用倒序存储：即储存字符串的时候用reverse函数把字符串倒序多来存，用于绕过字符串本身前缀区分度不够的问题。
  • 使用hash字段：可以在表上再创建你一个整数字段，来保存字符串的hash，同时在这个字段上创建索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

事务隔离

隔离性与隔离级别

ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）。

SQL标准的事务隔离级别：

  • 读未提交（read uncommitted）：一个事务还没提交时，它做的变更就能被其他的事务看到。
  • 读提交（read committed）：一个事务提交后，它做的变更才能被其他事务看到。
  • 可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
  • 串行化（serializable）：对于同一行记录，"写"会加"写锁"，"读"会加"读锁"。后一个事务必须等前一个事务执行完才能继续执行。

配置方式是，将启动参数transaction-isolaation的值设置成READ-COMMITTED，查看方式：


mysql> show variables like 'transaction_isolation';

+-----------------------+----------------+

| Variable_name | Value |

+-----------------------+----------------+

| transaction_isolation | READ-COMMITTED |

+-----------------------+----------------+

事务隔离的实现

在实现上，数据库会创建一个视图，访问的时候以视图的逻辑为准。

  • 可重复读：在事务开始时候创建视图，整个事务期间都用这个视图。
  • 读提交：视图是在每个SQL语句开始执行的时候创建的。
  • 读未提交：直接返回记录上的最新值，没有视图概念。
  • 串行化：直接用枷锁的方式来避免并行访问。

MVCC：同一条记录在系统中可以存在多个版本，称为数据库的多版本并发控制。

实际上每条记录在更新的时候都会记录一条回滚操作，记录上最新你的值，通过回滚操作，都可以得到以前的一个状态。

事务的启动方式

启动方式：

  1. 显式的启动事务，begin或start transaction。配套的提交语句是commit，回滚是rollback。
  2. set autocommit=0，这个命令会将这个县城的自动提交关闭。任何语句都可以启动事务，事务会持续存在直到你主动执行commit或rollback语句，或者断开链接。

在autocommit为1的情况下，用begin显式启动的事务，如果执行commit则提交事务。如果执行commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行begin语句的开销。

可以在information_schema库的innodb_trx这个表中查询长事务：

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

事务快照在MVCC里是怎么工作的

每个事务有一个唯一的事务ID，transaction id。是在事务开始时候向事务系统申请的，按申请顺序严格递增。而数据也是有多个版本的，每次事务更新数据时，都会生成一个新的版本，并把transaction id赋值给这个数据版本的事务ID，记row trx_id。也就是，数据表中每一行都可能有多个不同的版本，每个版本都有自己的row trx_id。

图中的三个虚线箭头，就是undo log；而V1、V2、V3并不是并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。

因此，一个事务在启动时，以启动时刻为准，如果一个数据版本是在我启动之前生成的，就承认；如果是启动以后才生成的，就不承认，并继续找到上一个版本。

更新的逻辑

当事务B去更新数据的时候，就不能再在历史版本上更新了，否则事务C的更新就丢失了。因此，事务B此时的set k=k+1是在（1,2）的基础上进行的操作。

当前读：更新数据都是先读后写的，而这个读，只能是读当前的值。除了update语句外，select语句如果加锁，也是当前读。

全局锁

根据加锁的范围，MySQL的锁大致可以分为全局锁、表锁和行锁三类。

全局加锁的方法命令是Fllush tables with read lock（FTWRL），当你想让整个库处于只读状态时候可以使用，之后其他线程的一下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改你表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本。

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数-single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

DML：增删改数据

DDL：修改表结构的操作

表级锁

表级锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）。

表锁的语法是lock tables ... read/write。

另一类表级锁MDL，不需要显式使用，在访问一个表的时候会自动加上。作用是保证读写的正确性。

当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更的时候，加MDL写锁。

  • 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查；
  • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性；

MDL锁可能会导致：给一个小表加个字段，导致整个库挂了。

行锁

行锁就是针对数据表中行记录的锁。比如事务A更新一行，事务B也更新同一行，必须等事务A操作完成后才能进行。

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致无限的等待状态。

当出现死锁以后，有两种策略：

  • 直接进入等待，直到超时。可以通过参数innodb_lock_wait_timeout来设置。
  • 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以执行。参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

死锁检测时，每个新来的被堵住的线程，都会判断会不会由于自己的加入导致了死锁，这是一个时间复杂度O(n)的操作。假设有1000个并发线程要更新同一行，那么死锁检测操作就是100万这个量级的。虽然最终检测结果是没有死锁，但是这期间需要消耗大量的CPU资源，因此，你会看到CPU利用率很高，但是每秒却执行不了几个事务。