极客时间 MySQL实战学习笔记 1-8讲

No.1 MySQL的基本架构

连接器：负责跟客户端建立连接、获取权限、维持和管理连接。
查询缓存：查询请求先访问缓存(key 是查询的语句，value 是查询的结果)。命中直接返回。不推荐使用缓存，更新会把缓存清除(关闭缓存：参数 query_cache_type 设置成 DEMAND)。
分析器：对 SQL 语句做解析，判断sql是否正确。
优化器：决定使用哪个索引，多表关联（join）的时候，决定各个表的连接顺序。
执行器：执行语句，先判断用户有无查询权限，使用表定义的存储引擎。
 

一条语句的执行过程

查询语句（select * from T where id = 2）

1. 通过连接器获取连接
2. 查看语句是存在于查询缓存中，如果存在则直接返回，不存在则进行 步骤3，Mysql 8.0版本之后已经去掉了查询缓存功能
3. 通过分析器判断SQL语句是否正确（要做什么）
4. 通过优化器判断使用哪个索引去查询（怎么做）
5. 执行器判断是否拥有该表权限，如果有则调用存储引擎的拿第一条数据的接口，判断id是否是等于2，如果不等则调用拿下一条数据的接口，如果等于则将结果放入到结果集中，继续判断下一条。

No.2 日志系统:一条SQL更新语句是如何执行的？

redo log

MySQL WAL 技术，先写日志，再写磁盘。保证掉电重启，数据不丢失（crash-safe）。
redo log 是 InnoDB 引擎特有的日志。
当记录更新时，Innodb 先记录 redo log 再更新内存，这时更新就算完成。引擎往往会在系统空闲时刷盘。

binlog（归档日志）

Server层日志。binlog 日志只能用于归档，没有crash-safe能力。
三个用途:

1. 恢复：利用binlog日志恢复数据库数据
2. 复制：主从同步
3. 审计：通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击

不同点:

    redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
    redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。binlog 文件到一定大小，会切换到下一个文件。
 

更新语句（update T set c = c+1 where id = 2）

1. 执行器找存储引擎拿到 id = 2 这一行记录，id是主键，存储引擎直接通过树搜索找到这一行记录，如果这一行所在的数据页在内存中则直接返回，如果不在则去读取磁盘到内存中再返回。
2. 执行器拿到这一行记录，将 c 列的值 +1 得到一行新数据，然后再调用存储引擎接口写入这行新数据
3. 存储引擎拿到这行数据写入到内存中，同时将这个更新操作记录到redo log日志中，此时redo log处于prepare状态，并告诉执行器可以提交事务了
4. 执行器生成这个操作的binlog，并把binlog写入到磁盘中。
5. 执行器调用存储引擎的提交事务的接口，存储引擎把刚刚写入的redo log日志状态更新为commit，这也就是两阶段提交

No.3 事务隔离：为什么你改了我还看不见？

事务隔离的特性

ACID：原子性、一致性、隔离性、持久性

事务的隔离级别

读未提交：一个事务还未提交，其他事务就可以看到它做的更改
读已提交：一个事务在提交之后，其他事务才能看到它的更改
可重复读：一个事务是启动和提交之间读到数据是一致的，它在未提交时，其他事务无法看到它做的更改
串行化：对同一行记录，写会加写锁，读会加读锁，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完才能继续执行。

修改隔离级别：设置参数：transaction-isolation

回滚日志

• 回滚日志什么时候删除?
  系统会判断当没有实物需要用到这些回滚日志的时候，回滚日志会被删除。
• 什么时候不需要？
  当系统中没有比这个回滚日志更早的read-view的时候

为什么尽量不使用长事务？

长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还会占用锁资源，可能会拖垮整个库。

 

No.4 深入浅出索引（上）

索引的常见模型

哈希表

将值放到数组里，然后用哈希函数将key换算为一个确定的位置 ，将value放到数组的这个位置，当哈希冲突时，会拉出一个链表进行保存。适用场景：只适合等值查询情况，不适合用于范围查询

有序数组

将值按顺序放入到数组中，可采用二分法查询，时间复杂度为O(lg(N))，但是插入比较麻烦，需要移动很多值。适用场景：不再变化的值。

二叉树搜索树

每个结点的左儿子小于父节点，右儿子大于父节点，平衡二叉树是搜索速度最快的数据结构，但是索引不仅存在于内存，也要存储到硬盘中，如果用平衡二叉树，那么100万的数据就是一个树高20的二叉树，对应磁盘就是20个数据块，要查询一个数据要访问20个数据块，这就很慢了。

N叉树

N叉树顾名思义就是每个节点有N儿子，儿子之间从左到右递增。它是为了解决二叉树占用数据块太多而产生的。

 

InnoDB 的索引模型 

Innodb是使用B+树来存储数据的。每一张表其实就是由多个B+树组成的，树结点的key值就是某一行的主键，value是该行的其他数据。每一个索引都会创建一个B+树。

索引的类型

索引分为主键索引和非主键索引，主键索引的叶子结点存放的是这一行的数据，而非主键索引的叶子结点存放的是主键索引的值。当使用主键索引去查询时可以直接获取到该行数据，而使用非主键索引去查询时，先拿到主键的值，再根据主键获取到该行数据，这个过程被称为回表 

 

索引维护

1. 当插入到索引树最后，只需直接插入。
2. 但当插入到索引树中间，需要逻辑上挪动后面的数据，空出位置，并且当数据页满时，需要申请一个新的数据页，然后挪动部分数据过去（页分裂）。
3. 当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。
4.  

No.5 深入浅出索引（下）

覆盖索引

当查询值已经在二级索引上时，不需要回表。

表 user，id（主键），name（普通索引）

select * from user where name = '张三'

这条语句的执行过程为：先去索引树name中找到张三拿到张三的id，再去主键索引树中根据id拿到这条记录，而我们只是需要它的id的，使用这条语句会进行一次回表操作，所以我们可以改为下面语句：

select id from user where name = '张三'

这种方式就叫做覆盖索引，我们可以通过一些联合索引的方式去避免进行二次回表操作。

 

索引最左前缀

表 user，id（主键），gender（性别），name（姓名），age（年龄）
联合索引（name，age）

当我们查询姓张并且年龄为10岁的男孩时：

select * from user where name like '张%' and age = 10 and gender = 1

它会先找到第一个姓张的记录，然后再向后依次遍历，这种就避免了全表扫描。

 

No.6 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

mysql锁大致可以分成全局锁、表级锁和行锁三类

全局锁

全局锁的典型使用场景是，做全库逻辑备份。
FTWRL命令：

flush table with read lock

加完全局读锁之后，数据库整个的更新，删除，添加语句都会被阻塞，这个使用场景就是数据库备份。但是让数据库处于只读状态，这种方式就会让所有更新被阻塞，整个业务就会停摆。这时我们可以使用官方为我们提供的数据库备份工具mysqldump，通过--single-transaction参数来确保拿到一致性视图:
 

备份：mysqldump --single-transaction -u用户名 -p密码 数据库名 > back.sql
恢复：mysql -u用户名 -p密码 -f 数据库名< back.sql 

这样在备份数据库之前就会启动一个事务，来确保拿到一致性视图，采用这种方式数据库也可以正常更新的。但这种方式有种局限性，那就是必须支持事务，而myisam存储引擎就不支持事务，所以还是得采用全局锁的方式。

问 : 让数据库处于只读状态为什么不用set global readonly=true ?
这是因为使用readonly的话，一旦客户端出现异常，那么整个数据库都处于不可用状态了，而使用 FTWRL一旦客户端出现异常，那么就会自动释放这个锁，整个数据库即可恢复到正常状态（可读可写）。
 

 

表级锁

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

mysql添加表锁可采用下面语句

lock tables 表名 read,表名 write

释放锁和FTWRL类似，当客户端出现异常后也会自动释放锁。也可手动释放：

unlock tables 表名

元数据锁（MDL）

元数据锁是隐式锁，当访问某一张表时，数据库自动加的锁。

• 当对表增删改查时：加MDL读锁
• 当更改表结构时：加MDL写锁

读锁之间不互斥，也就是多个线程可以同时对一个表进行增删改查，但是读写锁和写写锁之间互斥，也即是当更改表结构时要等待读锁或写锁释放后才能进行更改。

问：为什么我就给表加个字段，数据库就崩了？

如果在更改表结构之前有一个长事务在操作表（MDL读锁），当我们去添加表中一个字段时那么这个操作会添加一个MDL写锁，由于读写锁互斥，那么这个MDL写锁就会被阻塞，以至于后面的增删改查操作要加MDL读锁的都会被阻塞下去。

为了更加安全的更改表结构我们可以使用下面语句：

alert table 表名 wait 5 add colunm 列名

 

这个会等待5秒，如果5秒钟拿不到MDL写锁，那么就不再继续阻塞，也就可以后面的操作继续进行下去。
 

No.7 行锁功过：怎么减少行锁对性能的影响？

行锁是在引擎层实现的，但不是每个引擎都支持行锁，像MyISAM引擎就不支持行锁，它想控制并发就只能加表锁。

行锁是有需要时才添加的，但释放是在事务提交之后才进行释放的（两阶段锁），根据这个特性，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

举个例子：用户A在电影院B买一张电影票（3元）需要下面三个过程：

1.     从用户A账户中扣掉 3 元
2.     在电影院B的余额中增加 3 元
3.     记录一条交易日志

这个三个过程是放在一个事务中的，但是 2 过程是最可能造成锁冲突的，因为其他用户买了票之后也要在电影院B的余额中增加 3 元，所以我们要把最可能造成锁冲突的放在后面，这样电影院B余额这一行的锁时间就最少，我们调整顺序为：3 、 1 、 2

死锁和死锁检测

 

事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁，事务A和事务B互相等待对方的资源释放，这就造成了死锁。 

死锁解决

1. 超时释放

设置超时时间，通过参数innodb_lock_wait_timeout设置，当超过这个时间之后将自动释放锁资源。默认是50s

     2. 死锁检测

设置innodb_deadlock_detect为on来开启死锁检测，但它会造成额外的负担，每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。
 

No.8 事务到底是隔离的还是不隔离的？ 

快照”在 MVCC 里是怎么工作的？

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向InnoDB 的事务系统申请的，是按申请顺序严格递增的。
而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id赋值给这个数据版本的事务 ID，记为 row trx_id。
也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。

上图中的三个虚线箭头就是undo log。
某个事务建立快照，只需根据transaction id。只认事务启动时小于数据版本的数据，除自己更新的数据。

快照实现

InnoDB在每个事务启动瞬间，构造了数组保存了当前启动但未提交的事务ID。
数组ID最小值为低水位，当前系统最大事务ID+1为高水位。
数组和高水位，组成了当前事务的一致性事务（read-view）。

黄色部分需分为以下两种情况，因为有可能大于低水位的某个事务已经提交：

•     若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
•     若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。
如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

不同隔离级别：

•     对于可重复读，查询只承认在事务启动前就已经提交完成的数据
•     对于读提交，查询只承认在语句启动前就已经提交完成的数据
•     而当前读，总是读取已经提交完成的最新版本。

 

本篇转载自

https://blog.csdn.net/zxcc1314/article/details/84842650

https://blog.csdn.net/junmoxi/article/details/85758284