数据库：从认知到精通

前言

给自己定下一个目标：

• 阶段一：懂 SQL 语句，能够根据业务需求实现 CRUD 功能
• 阶段二：懂数据库的基本原理、索引原理，能够定位分析数据库性能瓶颈，知道如何优化数据库，如何合理创建高效索引，如何防范SQL注入
• 阶段三：趋向于架构、数据安全，能够合理设计数据库表结构，能够实现分布式数据库、分表分库、主从、读写分离、数据安全备份等工作

数据库的基本要求

可靠性、可用性、安全性是数据库系统的三条及格线

其次再考虑性能和成本

真正把一个系统做到成熟的指标是什么？

是易用性，即能完全容纳所有用户的诉求

对于客户来说，学习和开发成本要控制好

基础篇

一条SQL查询语句是如何执行的（上）

遇到一个事物或者问题，千万不要直接陷入细节，应该先鸟瞰全貌，从高维度理解问题

对于一句很简单的SQL：

select * from table where id = 10

看看这条语句的具体执行流程把

从上图可以看出，MySQL可以分为Server层和存储引擎层

Server层

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

存储引擎层

负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎

面试题：假设表中没有k这一字段，而你执行了select * from T where k=1，报错Unknown column ‘k’ in ‘where clause’，那么错误是在SQL执行的哪一阶段发现的。

答案：分析器阶段

《高性能mysql》里提到解析器和预处理器。

解析器

处理语法和解析查询, 生成一课对应的解析树。

预处理器

进一步检查解析树的合法。比如: 数据表和数据列是否存在, 别名是否有歧义等。如果通过则生成新的解析树，再提交给优化器。

一条SQL查询语句是如何执行的（下）

不知道你听没听说过，MySQL可以恢复到半个月内

任意一秒

的状态

我们从一条更新语句说起

create table T(ID int primary key, c int);

update T set c=c+1 where ID=2;

当一个表有更新时，跟这个表有关的查询缓存会失效，造成了相当程度的浪费，这也是不建议使用查询缓存的原因，MySQL8版本已经去掉了这一功能

与查询操作不一样，更新流程还涉及两个重要的日志模块：redo 重做模块和binlog 归档模块

redo-log

孔乙己在生意忙时，会将赊账记录先写到粉板上，晚上结账再记录到账本上

在MySQL的更新操作中，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高

粉板和账本配合的整个过程，其实就是MySQL里经常说到的WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本

mysql的粉板就是redo log，InnoDB引擎会将记录写到redo log里面，并更新内存，这个时候更新就算完成，同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事

redo log保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为

crash-safe

bin-log

最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档。而InnoDB是

另一个公司以插件形式引入

MySQL的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用另外一套日志系统——也就是redo log来实现crash-safe能力

更新操作与日志写入

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lLOMfBhY-1598586786775)(C:\Users\10764\AppData\Roaming\Typora\typora-user-images\image-20200815223039741.png)]

数据库恢复

binlog会记录所有的逻辑操作，并且是采用“追加写”的形式，如果你的DBA承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有binlog，同时系统会定期做整库备份

RedoLog两阶段提交

不使用两阶段提交，而是先写完其中一个日志，数据库的状态就有可能和用它的日志恢复出来的库的状态不一致

小结

redo log用于保证crash-safe能力

。innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1，这样可以保证MySQL异常重启之后数据不丢失

Redo log不是记录数据页“更新之后的状态”，而是记录这个页 “

做了什么改动

”

binlog用于回滚数据库

。sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数我也建议你设置成1，这样可以保证MySQL异常重启之后binlog不丢失

Binlog有两种模式，

statement 格式是记录sql语句

，

row格式会记录行的内容

，记两条，更新前和更新后都有

–数据库的事务机制

数据库的事务机制是必要的，否则会在事务并发执行时产生数据不一致问题。简单来讲，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中，事务是在

引擎层

实现的，而MySQL又是一个多引擎系统，原先的MyISAM就不支持事务，被InnoDB取代

事务的四大特性：原子性，一致性，隔离性，持久性。

当事务在并发执行时，可能会出现脏读，不可重复读，幻读等数据不一致问题，为了解决这些问题就有了相应的

隔离级别

的概念，对应的分别是：读未提交，读提交，可重复读和可串行化

• 读未提交：事务还未提交时，数据（可能变更）可被其他事务读取
• 读提交：在一个事务提交之后，它的数据变更才可被读取（脏读）
• 可重复读：事务在执行过程中读取到的数据，总是和事务开始时看到的数据是一样的
• 可串行化：会对一行记录的读写进行加锁操作，实现互斥访问

MySQL启动参数：transaction-isolation 事务隔离级别

情景题：

当你在做账户数据校对时，希望即使这个过程中发生了新的用户交易，也不影响你的校对结果，这时你就可以使用

可重复读

为什么建议你尽量不要使用长事务

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间

事务的启动方式

1.显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback

2.set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行commit 或 rollback 语句，或者断开连接

建议总是使用set autocommit=1, 通过显式语句的方式来启动事务

–索引

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

索引是为了提高数据的查找效率，实现索引的数据模型有很多，比较常见的有哈希表，有效数组，搜索树

• 哈希表：键——值形式存储的数据结构，查找时间O(1)，关键是解决哈希冲突，并且哈希表只适合做等值查询，在一些NoSQL数据库如Memcached有应用
• 而有序数组就是常见的列表，链表
• 搜索树，这里就是真正的索引实现结构

先从最简单的二叉搜索树讲起：

二叉搜索树的叶子节点存放数据，非叶子节点存放索引，这样查找的时间复杂度为O(logN)，为了维持树的高度，保证二叉平衡树，更新的时间复杂度也是O(logN)

由于每次查询只能读取一个节点数据的限制，于是又发现了==“N叉树”

是指一个节点可以存放多个数据，形成大小N的

数据块==，这样访问磁盘的次数就会减少，而且支持了范围查询，效率很高哦

以InnoDB的一个整数字段为例，这个N差不多是1200，树高4时，就可以存1200

³

个值，接近17亿，想一想一个十亿级的表，查找一个值最多只需要访问3次磁盘。

不过，数据库技术发展到今天，跳表，LSM树等数据结构也都被应用于引擎设计中。

InnoDB的B+树索引

在InnoDB中，表都是根据主键顺序以索引的形式存放的，每一个索引在InnoDB里面对应一棵B+树，数据内容是存放在叶子节点中的

索引分为主键索引和非主键索引：

主键索引的叶子节点存放的是整行数据，因此也被称为

聚簇索引

；

非主键索引的叶子节点的内容是主键的值，需要二次索引查找，因此非主键索引也叫作

二级索引

举个例子：

• 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
• 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表

数据的更新插入还要进行索引维护，（直接插入，向后挪动，页分裂）

由于主键是递增插入的，每插入一条新记录都是追加操作，不会挪动其他记录或者页分裂

什么是回表

在查询过程中由其他索引回到主键索引搜索的过程，我们称之为回表。

select * from T where k between 3 and 5
select id from T where k between 3 and 5

第一句在查询时会回表查询主键索引树的数据，第二句由于只需要查询ID值，而ID值已经在K索引树上了，因此不需要回表，这种操作叫做

覆盖索引

联合索引

身份证是公民的唯一标识，我们只需要在身份证号字段建立索引就够了，而再建立一个身份证号——姓名索引是不是有点浪费空间呢？

当然，维护索引字段是要付出代价的，但是如果有高频请求就是要根据市民的身份证号查询他的姓名，这个联合索引就有意义了，这时候体现出了覆盖索引的好处：不需要回表查询整行记录，减少语句的执行时间

最左前缀匹配原则

在建立联合索引的时候，如何安排索引内的字段顺序，一般的原则是将查询频率最高的字段放在最左

如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、(b) 这两个索引。

设计索引的重要原则之一就是：


在满足语句需求的情况下， 尽量少地访问资源

问题：通过两个alter 语句重建索引k，以及通过两个alter语句重建主键索引是否合理？

重建索引k的做法是合理的，可以达到省空间的目的

这是因为索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间

但是重建主键索引会将整张表重建，如果要更新所有索引，可以使用：

alter table T engine = InnoDB

–数据库的锁机制

锁机制是为了解决并发问题。根据加锁的范围，MySQL的锁大致分为

全局锁，表级锁和行锁

，锁的涉及比较复杂，这里不作讲解，主要介绍碰到锁的现象和背后的原理

全局锁

对整个数据库实例加锁，数据库处于只读状态，具体的方法是：

Flush tables with read lock (FTWRL)

具体使用场景可能是做

全库逻辑备份

表级锁

表级别的锁有两种，一种是表锁，一种是元数据锁Meta Data Lock

（表锁）举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表

（MDL）MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁

行锁

行锁是在各个引擎层自己实现的，不支持行锁意味着并发控制只能使用表锁，这也是MyISAM被INnoDB替代的原因之一。

两阶段锁

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放！

两阶段锁给我们使用事务提供了启发：

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

避免事务提交才解锁带来的时间损耗，值得注意的是，调整语句顺序并不能解决死锁问题：

死锁与死锁检测

事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，有两种策略：

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

正常情况下我们要使用第二种策略：主动死锁检测，当然检测也是有代价的（耗费大量CPU资源），每当一个事务被锁住时，就要看看它的线程有没有和其它线程相互锁住，这样导致的问题是：

​ 假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是

100万量级

的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的CPU资源。因此，你就会看到CPU利用率很高，但是每秒却执行不了几个事务

一种思路是你确定这个业务不会出现死锁，可以

暂时关闭死锁检测

另一种是

控制并发度

，控制一行最多只有10个线程在更新，那么死锁检测的成本很低，另外，控制并发度要在服务端做，如果有中间件可以使用中间件，如果有能修改MySQL源码得人，可以在MySQL里面做。基本思路是，对于相同行的更新，在进入引擎之前排队，类似于

RabbitMQ削峰限流

如果团队里暂时没有数据库方面的专家，不能实现这样的方案，能不能从设计上优化这个问题呢？

这是可以的，你可以考虑将数据的一行改成逻辑上的多行，拿银行账户举例，你可以将账户余额拆分成10行，总额等于10行之和，这样在修改余额时选择一条记录修改就行，冲突概率是不是减少了呢

事务是隔离还是不隔离

数据库在可重复读级别下，事务T起订是会创建一个视图read-view，之后事务执行期间，及时有其他事务修改了数据，事务T看到的数据仍然和启动时看到的一样，不受外界影响

MVCC

Multi-Version Concurrency Control 多版本并发控制，在数据库中用来实现对数据库的并发访问

在Mysql的InnoDB引擎中就是指在

已提交读

(READ COMMITTD)和

可重复读

(REPEATABLE READ)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程。

这就使得别的事务可以修改这条记录，反正每次修改都会在版本链中记录。SELECT可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，提升了系统的性能。

那么什么是版本链呢？

在InnoDB引擎表中，它的聚簇索引记录中有两个必要的隐藏列：

trx_id

和

roll pointer

trx_id存放每次对某条聚簇索引进行修改的事务id，roll pointer则指向这条聚簇索引上一个版本的位置，而老版本在undo日志里

ReadView

说了版本链我们再来看看ReadView。

已提交读和可重复读的区别就在于它们生成ReadView的策略不同

Mysql的MVCC,通过版本链，实现多版本，可并发读-写，写-读。通过ReadView生成策略的不同实现不同的隔离级别

普通索引和唯一索引

在不同的场景下，我们应该选择普通索引还是唯一索引

假设你在维护一个市民系统，每个人都有一个唯一的身份证号，那么你一定会考虑在id号上建索引，

由于身份证号长度较大，不建议把身份证号当做主键，现在有两种选择，一种是给id键唯一索引，一种是建普通索引

从性能上考虑，我们应该选哪一种呢，其实我们应该从底层的执行原理说起

查询过程

：在计算这两个索引的平均搜索性能差异时，可以认为是忽略不计的

**更新过程：**区别在于唯一索引不能使用change buffer，而普通索引可以使用，因此，如果要更新的记录的目标页不在内存中，对于唯一索引会从磁盘中读取数据页，判断有没有冲突，

小结

由于唯一索引用不上change buffer的优化机制，因此如果业务可以接受，从性能角度出发我建议你优先考虑非唯一索引

为什么我的MySQL会抖一下

一条SQL语句正常执行速度很快，但是会在一定时间下变得特别慢，这种现象是随机且时许很短的

那么，是什么原因导致数据库“抖了”一下

在开头介绍了WAL机制，InnoDB引擎在处理更新语句时，只做了写日志这一个磁盘操作，这个日志叫做redo log(重做日志)，也就是孔乙己用来记账的粉板，

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”

flush操作就是将内存中的数据写入磁盘的过程

回到开头的抖动问题，平时执行很快的更新操作，其实就是在写内存和日志，而MySQL偶尔抖一下的那个瞬间可能就是在刷脏页 flush，那么什么时候会引发flush操作呢

• 粉板满了（redo log满），在清除之前必须将日志里的正确数据更新到磁盘中

• 系统内存不足，就要淘汰一些数据页，如果要淘汰的是脏页，就要将脏页写到磁盘

• 系统空闲时，MySQL也会执行刷脏页操作。

• MySQL正常关闭的情况

实践篇

提高篇