MySql学习笔记

最近在看极客时间的MySQL实战45讲，记录下一些学习笔记

一、MySql基本架构

image.png

二、rendo log与binlog

  • rendo log是Innodb（存储引擎）特有功能，其实就是WAL(Write-Ahead Logging)技术。即当变更发生时，先把变更内容写入日志，然后回应变更完成，后续在逐步吧日志内容同步到磁盘。需要注意的是这里的日志也是写在磁盘内。
  • binlog是逻辑日志，一般有两种格式。statement 格式的是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。binlog+备份可以用于回档到误删前一刻。(有点跑偏，想到了万恶出bug的游戏回档大法)
  • 下图为 update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的：

                    image.png

三、事务隔离

  • 脏读(A事务读到B事务未提交的数据)、重复读(A事务读取两次数据a，期间a被B事务修改后提交，两次数据不一致)、幻读(A事务读取两次a=1的数据，期间B事务插入了一天a=1的数据)
  • 读未提交、读已提交、可重复读、串行化
  • 可重复读的实现，read view副本（mvcc）：

image.png

四、索引

  • 哈希表(kv型数据库)、有序列表(静态数据存储，例如2017年城市人口信息)、红黑树->二叉搜索数->n叉数->b数->b+树(数据全部放入叶子节点)
  • 主键索引（聚簇索引，叶子节点存整行的值，实际数据就存储在这个索引上，即表和索引存在一起）,非主键索引（非聚簇索引/二级索引，叶子节点存主键的值）。
  • 聚簇索引的特性要求，主键最好自增，且从1开始，以减少其他非聚簇索引叶子节点存的数据量
  • 覆盖索引->减少回表。
  • 最左前缀，即联合索引a,b。则可以考虑不要索引a。但是单查b无法使用此索引
  • 索引下推，联合索引(name,age)。那么如下sql在mysql5.6之后，可以只对name条件命中后，age=10的数据进行回表。
 select * from tuser where name like '张%' and age=10 and ismale=1;

五、MySQL锁事

 

  • 全局锁——逻辑备份
  • 表级锁——表锁，MDL(读写锁)
  • 行锁——两阶段锁，在事务结束后才会释放
  • 死锁——超时释放/死锁检测

image.png

  • 死锁检测——并发度控制。热点库存快速更新(秒杀场景)容易因为死锁检测造成数据库cpu飙升。需要注意中间件层面控制并发度

六、MVCC

  • undo log——回退日志
  • tx_id——事务id，数据更新后记录在undo log中
  • read view——只读快照（一致性视图）。其中记录当前活跃事务列表。其中最小为低水位，最大为高水位

image.png

  • rr在事务真正启动后，创建上图的read view。rc在每次查询时窜国家read view。
  • rr与rc每次查询，读取undo log中最新记录，取其tx_id，与当前事务id对比，如果低于低水位，或者再高低水位之前，单不在活跃事务列表中，则此记录可见，否则取undo log中的上一个版本的数据，并比对tx_id，以此类推
  • 更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read)。select语句加锁也是当前读。下图中最后k为3

image.png

  • begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动（rr快照此时创建）。

七、索引扩充

  • 数据按页读写，每个数据页的大小默认是 16KB。
  • redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

                    image.png

  • 唯一索引用无法使用 change buffer 的优化机(更新的数据，不在内存页中要从磁盘把数据拉入内存，判定是否数据是否已存在)
  • mysql通过采样得到索引基数
  • 为了减少回表，如果扫描行数相差不大，会优先使用主键索引。
  • 一些形如select * from table where a=* order by b desc limit 1的sql，预期使用a索引，实际可能使用b索引，对此如果a不会影响b的殊勋，优化方案除了force index外，还有select * from table where a=* order by b,a desc limit 1。通用优化有select * from (select * from table where a=* order by b,a desc limit 100) temp limt 1。两者都是去引导索引优化器意识到使用b索引的代价很大，转而选择a索引。
  • 字符串加索引思路：
    • 直接创建完整索引，这样可能比较占用空间；
    • 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引;
    • 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
    • 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
  • MyISAM 表虽然 count(*) 很快，但是不支持事务；InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题。自动优化采用非主键索引（主键索引存储数据，b+tree过大）
  • count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。count(主键 id) 依旧会判定非空，效率顺序为：count(字段)<count(主键 id)<count(1)≈count(*)，建议尽量使用 count(*)。

八、rendo log扩充

  • 当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
  • mysql读取时，如果内存页满，需要释放内存页，如果释放的是脏页，需要刷盘(flush)到磁盘。
  • 刷脏页速度如下：

image.png

九、索引空洞与重建表

  • 增删改可能引起索引存储的空洞，考虑主键索引与数据存储关联，所以其实整体存储有空洞
  • 重建表可以解决空洞问题，online ddl流程如下。增加字段也是如下流程：

            image.png

十、rr下的幻读与间隙锁

为了后续分析，我们以下面的建表语句建表t

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
  • rr下快照读不会产生幻读，但是当前读可能发生幻读，为解决此问题，mysql引入间隙锁
  • 跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
  • 如果要去掉gap lock，可以考虑改用RC隔离级别+binlog_format=row
  • select for update与update语句，条件中有明确的索引时(主键上有聚簇索引)，行锁+间隙锁。无索引值时，会锁定整个表+所有间隙。
  • 锁加在索引上，所有查询过过程中访问到的对象都会加锁
  • 加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
  • 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
  • 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
  • 间隙锁是动态的，即(5,10)和(10，15)两个间隙锁，如果10的数据被删除或修改，会优化为(5,15)
  • 回表操作会额外对主键索引(聚簇索引)加锁。先使用覆盖索引，再使用主键索引时，主键索引上会会不产生锁，所以可以修改数据，如下图：

image.png

  • next-key lock的间隙锁和行锁是分别申请的，具体可见下图，图中seesion b虽然没获取行锁，当时获取了间隙锁，单只session A插入操作失败，

image.png

posted @ 2020-12-21 17:50  豆豆323  阅读(91)  评论(0)  编辑  收藏  举报