mysql进阶学习笔记（基础知识：基本架构，日志，事务，索引，锁概念）

基本架构

查询sql如何执行的

连接器

mysql -h$ip -P$port -u$user -p

mysql -u root -p 密码（最好不要写出来）

查询缓存

之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中
key 是查询的语句，value 是查询的结果

PS：对于更新压力大的操作，缓存命中率会非常低
可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存

显式指定使用查询缓存。
mysql> select SQL_CACHE * from T where ID=10；

MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

分析器

对 SQL 语句做解析
判断语句是否正确，判断表是否存在，列是否存在

优化器

在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

执行器

日志系统

更新sql如何执行的

重要的日志模块：redo log

WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。
InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

write pos和check point相遇时说明日志满了，则需要更新到磁盘后，再清除日志。

重要的日志模块：binlog

Server层的归档日志，

两阶段提交

两阶段提交能够保证单事务两个日志的内容一致，保证在写入其中一个日志时，如果发生了crash也能够保证主从一致和恢复数据正常。

多事务同时提交：

解决：

1. 锁
2. binlog组提交

数据恢复流程

不同阶段mysql后的恢复行为不同

MySQL重启后，提供服务前会先做的事 – 恢复数据的流程：

事务隔离

隔离级别

隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）

简单来说：

读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。
可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。(自己改的数据还是认的）
串行：我的事务尚未提交，别人就别想改数据。
这4种隔离级别，并行性能依次降低，安全性依次提高。

快照的概念

实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现。

快照就是给整个库照相了一次，依靠的是事务id和行id

实现上：

可重复读： 事务启动的时候会创建一个数组，记录当前活跃的所有事务id（启动了但还没提交）
读提交：每一个语句执行前都回创建数组

数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图的对比结果得到的。

可重复读隔离级别和读隔离级别差别：

重点

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

版本未提交，不可见；
版本已提交，但是是在视图创建后提交的，不可见；
版本已提交，而且是在视图创建前提交的，可见。

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

select语句也有当前读：
分别加了读锁（S 锁，共享锁）和写锁（X 锁，排他锁）
mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

例子

重复读隔离的情况下：

读提交隔离级别：

对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
对于读提交，查询只承认在语句启动前就已经提交完成的数据；

配置方式

set session transaction isolation level + 隔离类型
做个实验

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

事务的启动方式

1. 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。
   一致性视图read-view是在第执行第一个快照读语句时创建的

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
.....
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

2. 隐式启动事务语句，set autocommit = 1, 在 autocommit 为 1 的情况下，用 begin 显式启动的事务，如果执行 commit 则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务，这样也省去了再次执行 begin 语句的开销。同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中

3. 如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。
   一致性视图是在执行 start transaction with consistent snapshot 时创建的

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
建议使用方法一，如果考虑多一次交互问题，可以使用commit work and chain语法。在autocommit=1的情况下用begin显式启动事务，如果执行commit则提交事务。如果执行commit work and chain则提交事务并自动启动下一个事务。

查找持续时间超过 60s 的事务。

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

索引

常见模型 哈希 有序数组 搜索树

哈希

哈希冲突解决用链表
区间搜索的话需要全部扫描一遍
所以只合适做等值查询的场景
（举例：身份证号码）

有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀
但是不适合插入
序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。

搜索二叉树

索引不止存在内存中，还要写到磁盘上
所以基本不用二叉，而用N叉树

InnoDB 的索引模型

B+数

主键索引和非主键索引区别

如何判断索引

俩个角度考虑
1、存储角度
比如表中确实有唯一字段，身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？
由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。
显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
2、索引维护角度（性能）
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。页分裂页合并都会影响性能。

自增主键

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。

插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。

所以自增主键从存储和性能上看都是最优的
当然典型的KV场景，索引唯一且只有一个索引的时候也可以用业务字段

覆盖索引

看例子，俩种不同的方式查询，性能不同
频繁的回表影响性能
覆盖索引可以减少树的搜索次数，显著提升查询性能

最左前缀原则

B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。
最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

而查询条件是b时，就无法复用该索引，这时候
第二原则是，考虑存储空间，比如上面这个市民表的情况，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

索引下推

锁

锁机值用来解决并发问题

全局锁

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

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

如果不加锁，会因为时间顺序，导致备份不同表的时间不同，导致表的逻辑对不起来。
也可以通过事务隔离， 可重复读隔离级别下开启一个事务来实现。（但是必须要用支持事务的引擎）

表级锁

行锁

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

死锁策略

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。（默认50s）

• 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。（检查成本也很大，CPU利用率很高）