MySQL实战45讲学习笔记(1~7)

本文为极客时间《MySQL实战45讲》的总结笔记,如有侵权,请通知我,将立马删除。建议大家去购买这套课程,真的是物有所值。

1、2一条语句的执行过程

1. Mysql结构

数据库总共分为两大部分:Server层和存储层,其中Server层又分为:连接器、(查询缓存)、分析器、优化器和执行器。存储层是以插件的形式,常见的有InnoDB和MyISAM。

2. 查询语句(select * from T where id = 2)

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

3. 日志

mysql中的日志分为两个部分:redo log 和 binlog。
redo log :这个服务于存储引擎, 这个是InnoDB引擎特有的日志系统
binlog : 这个服务于Server层,是所有引擎都可以使用的。

redo log 是为了减少磁盘的读写和crash-safe而产生的,它记录了更新、删除、插入语句,当Mysql空闲时就从redo log中读取这些日志,把数据更新到磁盘中。而binlog是记录做了哪些事情,当我们恢复备份信息时,可以读取binlog中的记录,然后去执行redo log中的记录去恢复信息。

4. 更新语句(update T 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,这也就是两阶段提交

3. 事务隔离

3.1 事务隔离的特性

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

3.2 事务的隔离级别

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

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

3.3 事务隔离的实现

每条记录在更新的时候都会记录一条回滚操作,记录上的最新值通过回滚操作都能回到它上一个版本,同一条记录在系统中可以存在多个版本,这就是数据库的多版本并发控制(MVCC)

3.4 回滚日志

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

    3.5 为什么尽量不使用长事务?

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

4、5 深入浅出索引(上,下)

4.1 索引的实现方式有哪些

  • 哈希表

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

  • 有序数组

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

  • 二叉树搜索树

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

  • N叉树

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

4.2 Innodb引擎使用的索引

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

4.3 索引的类型

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

4.4 覆盖索引

表 user,id(主键),name(普通索引)

当我们想查询 name = 张三 的id 时我们可以使用

1
select * from user where name = '张三'

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

1
select id from user where name = '张三'

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

4.5 索引最左前缀

表 user,id(主键),gender(性别),name(姓名),age(年龄)
联合索引(name,age)

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

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

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

一般来说如果建立了 (a,b)联合索引,就不需要在a上单独建立一个索引了,但是如果是根据b来查,那么还是需要在b上建立索引。

6.全局锁和表级锁

61. 全局锁

全局锁即是锁住整个数据库,mysql提供了一个加全局读锁的语句(FTWRL):

1
flush table with read lock

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

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

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

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

6.2 表级锁

mysql中的表级锁有两种,一种就是表锁,另一种是元数据锁(MDL):meta data lock。

6.2.1 表锁

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

1
lock tables 表名 read,表名 write

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

1
unlock tables 表名

6.2.2 元数据锁(MDL)

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

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

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

问:为什么我就给表加个字段,数据库就崩了?
如果在更改表结构之前有一个长事务在操作表(MDL读锁),当我们去添加表中一个字段时那么这个操作会添加一个MDL写锁,由于读写锁互斥,那么这个MDL写锁就会被阻塞,以至于后面的增删改查操作要加MDL读锁的都会被阻塞下去。

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

1
alert table 表名 wait 5 add colunm 列名

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

7. 行锁

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

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

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

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

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

7.1 死锁和死锁检测

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

死锁解决

  1. 超时释放
    设置超时时间,通过参数innodb_lock_wait_timeout设置,当超过这个时间之后将自动释放锁资源。默认是50s
  2. 死锁检测
    设置innodb_deadlock_detect为on来开启死锁检测,但它会造成额外的负担,每当一个事务被锁的时候,就要看看它所依赖的线程有没有被别人锁住,如此循环,最后判断是否出现了循环等待,也就是死锁。
-------------本文结束感谢您的阅读-------------