mysql基础架构

MySQL中的查询语句执行分析

select * from T where id =1;

基本架构

示意图

MySQL大致可以分为Server层和存储引擎层
Server层包括：连接器、查询缓存、分析器、优化器、执行器等，Server层是通用的，可以配合不同的存储引擎使用。
存储引擎层负责数据的存储和提取，其支持：InnoDB(默认的存储引擎)、MyISAM、Memory等多个存储引擎。

连接器

负责客户端和服务端的连接，验证用户名和密码，获取拥有的权限信息，如果在获取连接之后做出权限变更，对于未关闭的连接，是感知不到权限的变化的。
连接关闭时间由参数wait_timeout控制，默认值是8小时。

连接分为短连接和长连接，区别在于，长连接存活时间比较长，可以供更多的查询使用，而短连接经过几次查询就会断开了，下次查询需要重新获取连接。

由于建立连接的过程比较耗时，建议使用长连接，但是过多的使用长连接会导致内存占用会变大。两个解决问题的办法：

1. 定期断开长连接释放内存资源.
2. 执行mysql_reset_connection来重新初始化连接资源，使其恢复到刚刚创建完连接的状态，且不需要重新连接和权限验证等。

查询缓存

MySQL在拿到一个查询语句后，首先会查询缓存中是否存在之前执行过的同样的sql，如果命中则直接返回缓存中过的查询结果，如果没有命中再继续后面的过程，在返回给客户端之前，MySQL会对当前查询结果进行一个缓存，key就是sql语句，value就是查询结果。

由于缓存的失效非常频繁，只要有表执行了更新语句，那么这个表的缓存就会立马失效，从而造成了，维护缓存成本极高维护的东西，却很少能被使用到。缓存只适合使用在静态表中，其维护频率极低。

可以通过设置参数：query_cache_type设置未：DEMAND,这样就不会使用缓存功能了，如果要使用缓存则可以在SQL中显示指定 SQL_CACHE，例如：
select SQL_CACHE * from T where id=10;

Mysql8开始，缓存功能被完全移除了。

分析器

如果缓存命中失败，下一步就是‘词法分析’， 分析执行语句的正确性，表明字段名是否存在，经常报错类似： ‘use near’…

优化器

在经过分析器之后，需要对sql语句本身进行优化，例如：在包含多个索引时使用哪个索引，关联表多个时，决定各个表的连接顺序

执行器

经过优化器，就要开始真正的执行查询语句了，在这里首先还是先判断当前登录用户是否有该表的相应才做权限，如果缓存命中，在返回缓存结果之前，也会进行权限验证。

执行器会根据表的引擎定义，使用相应的引擎提供的接口执行才做。如果查询中没有用到索引，查询引擎就会从第一行开始逐条取出记录，如果满足查询条件就放在结果集中，如果不满足则跳过，直到取完最后一行，查询结束，然后讲结果集返回到客户端，结束查询语句。
对于包含索引的查询，执行逻辑类似，第一次调用的是取满足条件的第一行这个接口’之后循环满足条件的下一行这个接口，这些接口都是引擎提供好的。

在慢查询中的row_examined表示这个语句执行过程中扫描了多少行，就是在执行器调用引擎获取数据行的时候累加的结果，这个和引擎扫描的行数并不是完全相同的，有时候可能一次调用引擎，引擎内部扫描了多行数据。

更新语句的执行分析

其大致流程和查询语句的执行过程类似，不同之处在于，跟新语句在建立连接之后会清空即将更新到的表的缓存，其还包括两个日志模块,redo log(重做日志),binlong(归档日志).

redo log

MySQL中常用的WAL（Write-Ahead Logging), 更新过程会先写日志，在写磁盘。
当有一条更新语句的时候，InnoDB引擎会先把记录写在redo log里面，并更新内存，这个时候更新就算完成了，InnoDB会在比较系统比较空闲的时候，将日志中的操作记录更新到磁盘中区。

由于InnoDB中的redo log大小是固定的，如果redo log写满了，就会先处理一部分的redo log到磁盘中去，然后将处理完成的redo log清楚，这样就可以继续在redo log中记录操作日志了。Mysql之所以有crash-safe能力，就是依赖redo log.

binlog

redo log是InnoDB特有的日志，而bin log是Mysql Server层的日志。
crash-safe是没有crash-sage能力的。
两种日志比较：

1. redo log: InnoDB特有， binlog是MySQL server层实现的，所有引擎都可以使用。
2. redo log是物理日志，记录的是 ‘在某个数据页上做了什么修改”； binlog 是逻辑日志，记录的这个语句的原始逻辑，比如： ‘给id=xx的这一条记录的某个字段b执行一个xx操作‘
3. redo log是循环写入的，空间用完就会开始执行擦除动作，而binlog是可以追加写入的，不会删除和被覆盖。

更新语句执行流程：

1. 执行器先通过引擎查找到要修改的数据记录，优先在内存中查找，如果找到直接返回，如果没找到就从磁盘中找，找到后先读入到内存中，然后在返回给执行器。
2. 然后对引擎返回的数据执行更新操作，然后再调用引擎把更新后的数据写入到内存中。
3. 引擎拿到更新后的数据，首先更新到内存中区， 然后把操作记录记录到redo log中，此时redo log 处于prepare状态，等待事务提交。
4. 执行器生成binlog,并把binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口， 然后引擎把redo log的状态更新为：commit状态，完成更新过程。

示意图

两阶段提交

redo log的两阶段：

1. prepare
2. commit
   为了保证redo log和binlog的逻辑一致，所以才有了’两阶段提交‘。
   如果在记录两次日志中间发生了crash。会导致通过日志恢复出来的库的状态不一致，而且这不仅仅是用在数据恢复中，还会用在系统扩容，如果日志有问题就会导致库的集群中出现主从不一致的现象。

两个重要参数：

innodb_flush_log_at_trx_commit为1 ，表示每次事务的redo log都直接持久化到磁盘。保证日志的完整性。
sync_binlog为1，表示每次事务的binlog都持久化到磁盘，保证日志的完整性。

Isolation

保证一组操作的完整性产生了事务，包含四个特性：ACID（Atomicity, Consistency, Isolation, Durability）
在数据库中有多个事务同时执行的时候，可能会出现：dirty read, nonrepeatable read, phantom read, 所有产生了隔离级别的概念。
SQL标准的事务隔离级别包含：read uncommitted, read committed, repeatable read, serializable.

TA	TB
开启事务	开启事务
查询值1
	查询到值1
	将1改为2
查询得到值V1
	提交事务
查询得到值V2
提交事务
查询得到V3

隔离级别参数设置：transaction-isolation
列举不同隔离级别下上表中各个查询得到的结果情况。
case read uncommitted: v1,v2,v3: 2
case read commit: v1:1 v2,v3: 2
case repeatable read: v1,v2: 1 v3: 2 (辅助理解： 一个事务内读同一个值时相同的)
case serializable: v1,v2: 1 v3: 2 （辅助理解：TB，在修改的时候会被暂停执行，知道TA结束才继续执行）.
不同的隔离级别实现：
在MySQL中，每条记录在更新的时候都会记录一条回滚操作。同一条记录在系统中可以存在多个版本，数据库的多版本并发控制（MVCC)。
由于多版本的存在，如果过多使用长事务会导致记录过多回滚版本信息，占用大量的存储空间。还是长时间占用锁资源。

MySQL事务的启动方式

1. 显示启动事务语句， begin 或者 start transaction. 提交语句： commit, 回滚语句： rollback.
2. set autocommit=0,这个命令用于关掉线程自动提交，直到手动提交或者回滚事务，或者断开连接。

在不使用长事务情况下避免过多的交互，可以使用： commit work and chain， 在提交事务的时候会自动开启下一个事务，省去了下一次 begin的交互。

在infomation_schema库的innodb_trx这个表中查询长事务，
example：

## 查询大于60秒事务
select * from infomation_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;

INDEX

索引为了优化查询效率而存在。

常见的所有模型

哈希表，有序数组，搜索树。
哈希表： 只适用于只有等值查询的场景，范围模糊查询等都不适合。
有序数组：查询速度快，但是修改成本高，只适用于静态存储引擎。
搜索树：N 叉树

InnoDB 的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为‘索引组织表’，InnoDB中使用了B+树索引模型。所以数据都是存在B+树中的。
每一个索引在InnoDB里面对应一颗B+树。

在 user 表中添加数据： （1，小明，18），（2，小明，19），（3，小明，20），（4，小明，21），（5，小明，22）包含两个索引.
两个索引结构如图：

只有主键索引(聚簇索引clustered index)会包含整个数据行的内容，非主键索引(二级索引secondary index)只保存了相应的主键信息。

如果是根据主键查询，只需要搜索ID这一颗B+树即可得到查询结果，
如果是根据age索引搜索，需要先搜索age索引，得到id,然后再根据主键索引得到查询结果，这个过程称为回表。

索引的维护

因为要保证索引的有序性，在添加新值的时候需要维护索引，如果涉及到申请新数据页，会产生页分裂，这时候性能就会受到影响，占用空间也会变大，整体空间利用率也会变小。为了避免这种事情，尽量在建表时选择自增主键。防止从中间插入数据导致整体移动数据块。

如果是删除数据，也可能会产生页合并。

因为普通索引的叶子节点会存储主键信息，索引主键越小，索引占用的空间也就越小。

回表

select * from user where age between 19 and 20;

1. age树上找到19，获取id:2
2. id树上找到2，获取R2.
3. age树上找到20，获取id:3
4. id树上找到3，获取R3
5. age树上找下一个值，21，不满足条件，结束循环。

其中回到主键索引树上搜索的过程称为回表，可以通过避免回表优化查询效率。

覆盖索引

如果执行的语句是select id from user where age between 19 and 20;
其中id已经在k索引树上保存了，可以直接返回查询结果不需要查询主键树，不需要回表，索引k满足了查询需求。称为覆盖索引。这是一个常用的优化手段。

最左前缀原则

只有查询条件满足最左前缀原则才能用索引来加速检索，所以在创建联合索引的时候要合理安排索引内的字段顺序。联合索引最左侧的字段不需要单独建立索引。

索引下推

index condition pushdown, 这是在mysql 5.6开始引入的。其可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足的条件记录，减少回表次数。

全局锁和表锁

全局锁

全局锁会对整个数据库实例加锁，可以使用： Flush tables with read lock(FTWRL)命令，然后整个库就处于只读状态了，然后出了读以外的任何DDL,DML都会被阻塞。

应用于全库的逻辑备份。避免出现逻辑不一致的状态。

MySQL提供的官方备份工具：mysqldump，当使用参数-single-transaction的时候，在开始备份数据的时候会开启一个事务，确保能拿到一个一致性的视图，也就是事务隔离级别中的： 可重复读。 这备份过程中数据库可以正常更新使用，由MVCC支持。但是这个依赖于引擎支持事务，在MyISAM引擎中是不支持这么用的情况下只能使用FTWRL命令了。

set global readonly=true也可以让数据库处于只读状态，但是其作用范围过大，可能会导致依赖这个参数判断的条件，会出现判断错误，例如主从库判断等等。而且可能会出现，由于人为或者系统异常导致系统一直处于readonly状态。

表级锁

1. 表锁
   加锁： lock tables ... read/write.， 释放锁：unlock tables,或者客户端连接断开。
2. 元数据锁（meta data lock, MDL)
   MDL不需要显示使用，在访问表的时候会被默认的加上。用于保证读写的正确性。在执行DML时，会加MDL读锁，在执行DDL时，会加MDL写锁。
   读锁之间是不互斥的。读锁和写锁，写锁和写锁之间是互斥的。
   如果在表中有长事务存在，一直占用MDL锁，导致互斥锁无法获取，如果无限重试，严重情况可能导致不可用。所以在申请锁时最好自带超时机制。

行锁

行锁是由引擎层实现的。MyISAM引擎是不支持行锁的。在控制并发只能用表锁，意味着同一时刻只能有一个更新操作在表上执行。

两阶段锁

在InnoDB中，行锁是在需要的时候才加上，但在不需要的时候，未提交事务还是不会释放锁，只有提交事务才会释放锁。
在需要多个行锁时，把最可能造成冲突的锁获取行为放在最后。

死锁和死锁检测

在两个事务中互相等待对方持有的锁资源。出现这种资源循环依赖，并进入无限等待的状态称为：死锁。
出现死锁以后的两种策略：

1. 直接进入等待，等到超时，通过参数：innodb_lock_wait_timeout来设置超时时间。默认是50s。
2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务。通过参数innodb_deadlock_detect设置为on，表示开启这个功能。这个是默认策略。

如果同一时刻有大量的请求都要更新同一行数据，如果发生了阻塞，会导致每一个请求都会进行死锁检测。从而导致大量的CPU资源被消耗，这种热点资源更新的情况，一种解决办法是：关闭死锁检测。
另外一种就是：减少并发量或者将热点资源分散，在需要的时候再汇总。这样就可以让行锁变成多个，能支持更高的并发量。

参考：《極客時間：MySQL實戰》