《MySQL实战45讲》事务与隔离级别、索引优化和锁等面试必知

本篇目录

《MySQL实战45讲》说明

MySQL 下载安装完成,知道如何修改 root 密码,学会基本的增删改查语句后,要真正掌握它,还需要知道 存储引擎事务的用法(特别是事务的隔离级别)、索引优化全局锁表级锁行级锁等更多更多知识。

前阿里资深专家林晓斌的《MySQL实战45讲》,大概是最好最好的进阶材料,有很多出超出「想当然认识」的知识。掌握这些知识的应聘者绝对能让面试官感到惊喜,更关键的是可以避免在以后工作中踩坑。

下面是我的学习笔记,专栏地址在本页最后。

SQL 语句的执行过程

MySQL 客户端(mysql 命令、各种语言的 sdk)和 MySQL 数据库的连接器建立连接,连接器将 sql 语句交给分析器,经过优化后执行。

MySQL语句执行

查询缓存往往弊大于利,将 query_cache_type 设置为 DEMAND,对于默认的 SQL 语句都不使用缓存。

MySQL 8.0 将查询缓存的整块功能删掉了

查看客户端:

show processlist

redolog 与 binlog 日志

redolog 是 MySQL InnoDB 引擎特有的日志:更新记录时,先将更新的内容写入 redolog 和内存,然后在系统空闲的时候刷新到磁盘。redolog 采用环状设计,循环写,写满后就刷新到磁盘,继续写。

binlog 是 MySQL Server 层的日志:所有引擎都可以开启,采用追加写的方式,形成一个个日志文件。

MySQL 的更新过程,浅颜色是 InnoDB 的操作,深颜色是 Server 的操作:

mysql更新时redolog和binlog的更新过程

MySQL 使用「两阶段提交」,prepare 阶段引擎写入 redolog,server 写入 binlog,然后开始 commit。

如果这期间宕机,MySQL 重启时根据 redolog 和 binlog 判断更新操作是否完成:只有 redolog 没有 binlog,撤回更新(评论中的说法)。

binlog 记录了过去一段时间里的所有操作记录,主要用来恢复数据:binlog 有两种格式,statement 格式记 sql 语句;row 格式记录行的内容,记两条,更新前和更新后的。

redolog 记录数据页 “做了什么改动”。

相关参数:

innodb_flush_log_at_trx_commit:建议值为1,每次事务的 redolog 直接持久化到磁盘
sync_binlog:                   建议值为1,每次事务的 binlog 都持久化到磁盘

数据库多久备份一次?备份间隔时间越长,从 binlog 恢复耗时越久。

事务、事务隔离级别和当前读

多个事务在数据库上同时执行时,按照每个事务看到的数据的样子,可以把事务的隔离分为 4 个级别:

1. 读未提交:当前事务还没提交时,其它事务就能看到变更
2. 读提交:  当前事务提交之后,其它事务才能看到变更
3. 可重复读:事务执行过程看到的数据和事务启动时看到的数据严格一致,无论其它进行了变更的事务是否提交
4. 串行化:  事务串行执行,写时加写锁,读时加读锁(最高等级,相当于无并发)

事务的隔离通过 MVCC(多版本并发控制)实现,事务执行过程中,被改动的行会保留每次变更的回滚,以及当前行上事务,其它事务要读取当前行按照一定规则选择合适的值。

如果事务存续时间太长,会形成大量回滚日志,所以一定要 避免长事务

事务启动方式:

1. 用 begin 或 start transaction 显式启动,用 commit 提交,用 rollback 回滚
2. set autocommit=0,会关闭当前线程的自动提交,事务从第一个语句开始,直到主动 commit 或 rollback 或连接断开才结束

建议:

1. set autocommit=1,总是通过显式语句启动事务
2. 或者用 commit work and chain,提交事务并自动启动下一个事务

查询长事务:

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

事务的隔离中存在一个当前读的场景:更新行时是先读后写,更新时读只能读当前值,称为「当前读」。

假设事务 A 要更新一行的值时,事务 B 在事务 A 启动后就修改了该行,那么事务 A 会在当前值(即事务 B 修改后的数值)上修改。 事务 A 完成修改后进行查询时,查到的是事务 A 修改后的值,而不是事务 A 启动时的值(可重复读中的情况,如果 A 没有修改操作,查询结果是事务 A 启动时的值)。

select 语句如果加锁,也是当前读:

select k from t where id=1 lock in share mode;
select k from t where id=1 for update;

主键索引、普通索引和联合索引

索引的三种结构:哈希表、有序数组、N 叉树。InnoDB 引擎中每个索引是一棵 B+ 树。

主键索引的叶子节点是整行数据,非主键索引的叶子节点是主键的值,因此通过非主键索引查询会查两棵树,第二次查询为「回表」,要尽量避免回表

索引优化点:

1. 减少普通索引的叶子节点的大小,即主键要尽可能小
2. 高频的查询尽量通过覆盖索引,避免回表

主键长度越小,普通索引的叶子节点越小,占用空间越小,自增主键往往是更合理的选择。如果只有一个索引并且是唯一索引,例如 KV 场景,就不需要考虑其它索引的叶子节点大小问题。

查询主键值可以避免回表,这种情况称为「覆盖索引」,即目标数据已经在当前查询的索引上,无需回表。 联合索引也是覆盖索引,经常互相查询的字段可以建立联合索引。

联合索引最左匹配的特性可以减少索引数量。

假设为字段(a,b)建立了联合索引,那么 a 就可以不建索引,以 a 为查询条件时,可以利用联合索引的最左匹配特性。但是如果以 b 为查询条件,就不能利用最左匹配,需要为 b 单独建索引。

MySQL 5.6 引入了联合索引下推优化(index condition pushdown),在索引遍历过程中,直接过滤不满足的记录,减少回表次数。

MySQL联合索引的下推优化

全局锁、表级锁、行级锁

全局读锁:flush tables with read lock,整个库处于只读状态,其它线程中的操作语句都会被阻塞,全局锁通常用来做全库逻辑备份。

如果引擎支持可重复读事务,可以在备份前启动事务,确保备份正确,如果引擎不支持只能用全局锁。mysqldump 的 -single-transaction 参数在导数据之前启动事务。

表级锁:表级锁分为表锁和元数据锁。

表锁用法:对其它线程和当前线程都起作用。

locka tables ... read/write
unlock tables 
locak tables t1 read,t2 write

元数据锁:MDL,在访问一个表的时候自动加锁,MySQL 5.5 引入。

给一个表加字段、修改字段、加索引时,需要扫描全表的数据,在对大表操作时,要特别小心: 第一,如果有正在执行的长事务,暂定更新,或者 kill 长事务;第二,在 alter table 语句中设置等待时间,如果等待时间里没有拿到 MDL 写锁就放弃。

ALTER TABLE tbl_name WAIT N add column ...

行锁:在需要的时候被自动加上,如果在事务中,要等到事务结束时才释放,因此一个事务中要更新行,要尽可能把会造成冲突的放在最后,减少锁等待。

MyISAM 不支持行锁,InnoDB 支持

行锁可能存在死锁的情形,事务A和事务B更新同样的两行,但两者更新的顺序不同,会出现互相需要对方释放锁的情况。

innodb_lock_wait_timeout: 锁超时时间,默认 50s
innodb_deadlock_detect:  默认为 on,开启死锁检测,主动回滚一个事务(建议方案)

开启死锁检测后,每个新来的线程都要检测自己的加入是否会导致死锁,如果 1000 个线程同时更新相同行,检测次数是 1000 * 1000 量级的。

解决方法:

  1. 如果确定不会死锁,临时关掉死锁检测(有风险)
  2. 控制并发读,同一行只允许 10 个并发线程,其它线程等待(需要改动数据库服务端)
  3. 将单行记录拆分为多行

未完待续,还在学习中…

林晓斌极客时间专栏《MySQL实战45讲》

林晓斌《MySQL实战45讲》:特别优惠地址

林晓斌《MySQL实战45讲》MySQL深度学习

参考

  1. 李佶澳的博客

推荐阅读

赞助商广告

Copyright @2011-2019 All rights reserved. 转载请添加原文连接,合作请加微信lijiaocn或者发送邮件: [email protected],备注网站合作

友情链接:  李佶澳的博客  小鸟笔记  软件手册  编程手册  运营手册  爱马影视  网络课程  奇技淫巧  课程文档  精选文章  发现知识星球  百度搜索 谷歌搜索