MySQL知识梳理(一)

MySQL知识梳理(一)

Scroll Down
cropped-1366-768-598786
cropped-1366-768-598786

知识总结来源于极客时间专栏《MySQL实战45讲》,此文仅为本人日后复习所用,不用再去一篇篇文章翻阅。

MySQL的基本架构图

img
img

MySQL总体分为两个层次结构,分别为Server层和引擎层。Server层分为连接器、 查询缓存、分析器、优化器、执行器。具体作用如图所示。需要注意的是:查询缓存并不是一个靠谱的玩意,只要对表的数据进行更新,那么这个缓存就会失效,建议关闭这个缓存功能,MySQL8.0以后,这个功能就被完全删除了。

与MySQL建立连接的命令为

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

MySQL客户端与服务端的连接,默认保持8小时。这个属于长连接,要知道MySQL在连执行过程临时使用的内存,是管理在连接对象里的,长时间积累下来,会存在内存消耗过大的问题。解决方法是定期断开长连接,或者如果MySQL版本在5.7.4以上可以执行完大查询后,执行下mysql_reset_connection命令。

MySQL里面的重要日志

redo log

redo log是有记录需要更新时候,InnoDB 引擎会先把记录写入到 redo log 里面,然后更新内存,而不是直接去操作磁盘,这样就提高了 MySQL 的执行效率,磁盘 I/O 的成本是比较高的。有个这个日志,InnoDB 可以保证即使数据库异常重启,之前提交的记录也不会丢失,也就是所谓的崩溃恢复能力。

需要特别注意,redo log 是 InnoDB 独有的功能。redo log 大小是有限,如果写满后,就需要把记录刷回到磁盘。redo log 是记录在数据页''做了什么改动''。

binlog

binlog 是 Server层的功能,与引擎无关,它作用是用来归档,比较常见的用法是定期同步数据到binlog 里面。binlog 有两种模式,statement 格式记录的是 sql 语句,row 格式记录行的内容。有了这两个日志,我们可以来看看两阶段提交

两阶段提交

img
img

可以看到写完 redo log 后,整个事务还处在 prepare 阶段,写完 binlog 后,才会去提交事务。两阶段提交是为了让两份日志间的逻辑保持一致,这样崩溃后,执行未完成的事务时候,才有判断依据,如果一致,事务可以提交,如果不一致,事务回滚。

事务

InnoDB 支持事务,MySIAM 不支持事务。事务的特性是ACID,分别为 Atomicity、Consistency、Isolation、Durability,也就是原子性、一致性、隔离性、持久性。

事务隔离级别

SQL 标准的事务隔离级别为:读未提交、读已提交、可重复读、串行化。详细意思如下:

  • 读未提交:一个事务还没有提交,它所做的改变就能被其他事务所看到。
  • 读已提交:一个事务提交后,它所做的改变才能被其他事务所看到。
  • 可重复读:一个事务在执行过程中看到的数据,总是跟这个事务在启动前后看到的数据一致。
  • 串行化:对同一行数据,写会加写锁,读会加读锁,当出现读写冲突时候,后访问的事务必须等前面一个事务执行完成,才能继续执行。

这里要注意下读已提交与可重复读的差别,今天写的时候,脑海中就不能马上意识到这两者的不同点。读已提交场景,举个例子,事务A先读了一条数据,事务B更新了这条事务并提交,A再去读事务,发现值会变。可重复读场景,举个例子,事务A先读了一条数据,事务B更新了这条事务并提交,A再去读事务,发现值没变。

具体为什么会这样,可以看事务隔离的原理,晚点会写到。可以简单理解一下,可重复读级别事务在启动时候,会读取一个视图,视图就相当与这一时刻的最新数据了,事务里面看到的数据也是以这个视图为准,读已提交则是只读取最新的数据。

隔离级别可以通过修改 transaction_isolation 参数的值来设置

show variables like 'transaction_isolation'#查看隔离级别
1599719158499
1599719158499

事务隔离级别的原理

可以参考 MySQL历险-MVCC相关知识

不建议在 MySQL 中执行长事务,弊端有一下两个方面:

  • 长事务意味着系统里面存在很老的事务视图。前面说过,事务启动前,会生成一个事务视图,这视图保存了以前的事务数据记录,数据库里面它可能用到的回滚段都必须保留,这就会导致大量占用存储空间。
  • 长事务还会占用锁资源,严重时可能会影响整个库的运行。

可以通过以下sql查询数据库是不是有长事务

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

索引

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

索引常见模型

常见的有这三种,分别为哈希表、有序数组和搜索树。

哈希表很简单,就是一个 key-value 结构,加入位置冲突那就拉个链表,这种结构只适合等值查询的场景。

有序数组在等值查询和范围查询的场景中的性能都非常优秀,关键是因为它支持二分法查找。但是它插入数据时候,要往后挪动所有记录,成本太高。

搜索树有二叉和多叉,实际情况使用的是多叉,因为多叉树高低,访问磁盘的次数就少,整体的效率也会提高。

不管接触什么数据库,首先要关注的就是数据库的数据模型,数据模型决定了数据库的适用场景。

InnoDB 的索引模型

InnoDB 使用的是 B+树索引模型,数据都是保存在 B+ 树中。下图有两颗B+树

img
img

根据叶子节点的内容个,索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据,在InnoDB 里,主键索引也被成为聚簇索引。

非主键索引的叶子节点存放的是主键的值。在InnoDB 里,非主键索引也被成为二级索引。

这两种索引的查询是有区别的,普通索引查询如果查询语句包含了索引没有的列,那就得拿到主键值后再去主键索引中拿出数据,这个过程也被成为“回表”了,所以查询有限使用主键索引会更好。

索引维护

建立主键索引时候,经常可以看到这样的语句 NOT NULL PRIMARY KEY AUTO_INCREMENT,这是有科学根据的。

顺序递增插入的主键,都是追加操作,不会触发叶子节点的分裂。以前的公司真的是太猪头了,用uuid 做主键,目测这样会经常页分裂,影响性能,现在的公司的规范就好很多,大公司就是好,广大同志能去大公司还是去公司吧。

主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小

496f9330e74836bbcd6afb5e29db5383
496f9330e74836bbcd6afb5e29db5383

有道面试题,"N叉树"的N值在MySQL中是可以被人工调整的么?

  1. 通过改变key值来调整
    N叉树中非叶子节点存放的是索引信息,索引包含Key和Point指针。Point指针固定为6个字节,假如Key为10个字节,那么单个索引就是16个字节。如果B+树中页大小为16K,那么一个页就可以存储1024个索引,此时N就等于1024。我们通过改变Key的大小,就可以改变N的值。
  2. 改变页的大小
    页越大,一页存放的索引就越多,N就越大。

覆盖索引

如果一个索引覆盖了我们的查询要求,这个索引可以成为"覆盖索引"。

由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段

最左前缀原则

覆盖索引虽然好,但我们也不可能为了每一种查询都去建立覆盖索引的,这里就需要使用最左前缀原则了,假如你建了个联合索引(name,age),现在你想查询名字叫张三,身高1米8的小伙,难道我们又建一个(name, height)的索引吗,没必要了,我们可以利用name这个最左前缀来定位记录,然后向后遍历,判断记录是否合格就行了。

既然前缀索引也是这么有用,那么联合索引内的字段顺序就显得尤为关键了。这里有两个原则可以考虑:

  1. 最少索引原则:如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。
  2. 空间原则:这里举个例子方便理解,有一个很大的字段name,需要根据name和age做联合条件去查询,还有需求是年龄单独作为条件去查询、名称单独作为条件去查询,这样情况该怎么办,索引要怎么选?正确答案是 (name、age)和单独的 age,因为这样可以减少空间的占用。

索引下推

索引下推到底是个什么东西?有什么用这个玩意?我第一反应看到这个词,真有点蒙,推的什么,还下推呢?吓唬谁。

首先索引下推也是针对联合索引来说的,现在要查名字叫张三,年龄为10岁的 little boy,我们有个联合索引(name, age),如果没有索引下推这个优化,匹配到张三时候,就拿主键去会表查询,在判断年龄是否符合,如果有了索引下推,会现在查询联合索引时候,再加上age这个条件,先过滤掉不符合的数据,这样会表的数量就少了。

这里插播一个小技巧,索引可能因为删除,或者页分裂等原因,导致数据页有空洞。重建索引的过程会创建一个新的索引,把数据按顺序插入,这样页面的利用率最高,也就是索引更紧凑、更省空间。如何才能正确的重建索引呢?

alter table T engine=InnoDB

MySQL中的锁

MySQL的锁分别为全局锁、表锁、行锁。

全局锁

全局锁,我平时比较少见有使用,全局锁打开后,就意味着任何删除,更新操作都会被阻。全局锁的典型使用场景是,做全局逻辑备份

设置全局锁的命令如下:

Flush tables with read lock

但一般不会这么用,弊端很明显了,不能进行任何更新操作,这还玩个der,现在官方自带的逻辑备份工具是mysqldump。原理就是备份前,开启一个事务,会得到一个一致性视图,配合MVCC,数据就可以正常更新。

但这不是说全局锁就一无是处,使用事务的前提是引擎要支持,MySIAM 这个引擎就不支持了,只能用全局锁。

表锁

MySQL 里面表锁分为两种,分别为表锁、元数据锁(MDL)。

表锁我们可以主动添加,命令为

lock tables … read/write

假如是 read 的话,就意味着只能读了,不能写了,如果是 write 的话,就意味着读写都不行了。

MDL锁不需要显示使用,在访问一个表的时候会被自动加上。当对一个表做增删改查操作时候,加的是 MDL 读锁,对表的结构做变更时候,加的是 MDL 写锁。

  • 读锁之间不冲突,就是说可以有多个线程对表进行增删改查操作。
  • 读写锁之间、写锁之间是互斥的。

线上事故有可能是这里引发的,场景还不算少见,假如你在执行一个长事务,久久都不提交,现在又执行一个 MDL 语句,比如加索引、加字段之类,那这个 MDL 语句是百分百被阻塞住的,因为读写冲突,后续的任何对表读写语句都会被阻塞,很危险。像下面图一样

img
img

表锁一般是行锁用不上,才会考虑。

行锁

InnoDB 支持行锁,渣渣 MySIAM 不支持,现在的引擎基本不会选择 MySIAM 了。与行锁相关的有个叫两阶段锁的协议。即在 InnoDB 事务中,行锁是在需要的时候才加上,但不是不需要了就马上释放,而是要等到事务结束以后才释放

这个两阶段锁协议给我们一个启发,如果一个事务需要锁多个行,要把最可能赵成锁冲突、最可能影响并发度的锁尽量往后放

可以试想一下,两个事务都要锁同一行,在第一条语句锁,和在最后一条语句锁,哪个对其他事务的影响更小?很明显是最后一行啦。

说到行锁,就不可避免谈到死锁。如果两个线程互相持有对方想要的锁资源,那就会进入一个互相等待的状态,也就是死锁了。

死锁有什么办法解决吗?有以下两种策略可以考虑:

  • 直接进入等待,直到超时。是通过 innodb_lock_wait_timeout 参数来设置。
  • 启用死锁监测。发现死锁后,主动回滚其中的一个事务,让其他事务可以继续进行,通过设置 innodb_deadlock_detect 参数为 on 来表示开启。

直接使用第二种就可以了,第一种时间很难权衡的,第二种也不是没有弊端,死锁监测是需要消耗CPU的,所以如果生产上有这样一种现象,就是 CPU 利用率很高,但是每秒却执行不了几个事务,那就要考虑一下是不是有大量的事务并发请求获取同一行的行锁,造成死锁监测了。