MySQL实战45讲总结
MySQL45讲
1. ⼀条SQL查询语句怎么运⾏的
但是⼤多数情况下我会建议你不要使⽤查询缓存，为什么呢？因为查询缓存往往弊⼤于利。
查询缓存的失效⾮常频繁，只要有对⼀个表的更新，这个表上所有的查询缓存都会被清空。
2. ⼀条SQL更新语句怎么运⾏
MySQL ⾥经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写⽇志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。
group by子句redo log（粉板）fieldset标签的作用
当有⼀条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（⾥⾯，并更新内存，这个时候更新就算完成了。在适当的时候，将这个操作记录更新到磁盘⾥⾯，⽽这个更新往往是在系统⽐较空闲的时候做，这就像打烊以后掌柜做的事。
⼤⼩固定，循环写
crash-safe
binlog
redo log 是innoDB引擎特有的，server 层的叫 binlog(归档⽇志)
redolog 是物理⽇志，记录“在某个数据页上做了什么修改”；binlog 是逻辑⽇志，是语句的原始逻辑，⽐如“给 ID=2 这⼀⾏的 c 字段加 1 ”
redolog循环写，binlog追加
对于语句 update T set c=c+1 where ID=2;
1. 执⾏器先引擎取 ID=2 这⼀⾏。ID 是主键，直接⽤树搜索到。如果 ID=2 这⼀⾏所在数据页就在内存中，就直接返回给执⾏器；
否则，需要先从磁盘读⼊内存，再返回。
2. 执⾏器拿到引擎给的⾏数据，把这个值加上 1，N+1，得到新的⼀⾏数据，再调⽤引擎接⼝写⼊这⾏新数据。
3. 引擎将这⾏新数据更新到内存中，同时将这个更新操作记录到 redo log ⾥⾯，此时 redo log 处于 prepare 状态。然后告知执⾏器
执⾏完成了，随时可以提交事务。
4. 执⾏器⽣成这个操作的 binlog，并把 binlog 写⼊磁盘。
5. 执⾏器调⽤引擎的提交事务接⼝，引擎把刚刚写⼊的 redo log 改成提交（commit）状态，更新完成
对于redo log 是有两阶段的：commit 和 prepare
如果不使⽤“两阶段提交”，数据库的状态就有可能和⽤它的⽇志恢复出来的库的状态不⼀致.
先r后b:binlog丢失，少了⼀次更新，恢复后仍是0。
先b后r：多了⼀次事务，恢复后是1.
undolog
Undo log的存在保证了事务的原⼦性，MVCC就是依赖它来实现，当对任何⾏做了修改的时候都会在undo log⾥⾯记录，⼤量的undo log 构成⾏的历史版本记录，在需要的时候可以回退(rollback)到任何版本；
3.事务隔离
ACID（Atomicity、Consistency、Isolation、Durability，即原⼦性、⼀致性、隔离性、持久性）
SQL标准隔离级别：
读未提交: ⼀个事务还没提交时，它做的变更就能被别的事务看到。
读提交: ⼀个事务提交之后，它做的变更才会被其他事务看到。
可重复读: ⼀个事务执⾏过程中看到的数据，总是跟这个事务在启动时看到的数据是⼀致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串⾏化: 顾名思义是对于同⼀⾏记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前⼀个事务执⾏完成，才能继续执⾏
避免使⽤长事务，set autocommit=1, 通过显式语句的⽅式来启动事务。
information_schema 库的 innodb_trx 中可以查询长事务。
4-5. 索引
基于B+树。
mysql面试题acid主键索引的叶⼦节点存的是整⾏数据。 InnoDB ⾥，也被称为聚簇索引（clustered index）。
⾮主键索引的叶⼦节点内容是主键的值。在 InnoDB ⾥，⾮主键索引也被称为⼆级索引。
⾮主键索引查询会回表。
⾃增id可以避免维护B+树时的分裂、合并问题。
索引维护。
B+ 树为了维护索引有序性，在插⼊新值的时候需要做必要的维护。以上⾯这个图为例，如果插⼊新的⾏ ID 值为 700，则只需要在R5 的记录后⾯插⼊⼀个新记录。如果新插⼊的 ID 值为 400，就相对⿇烦了，需要逻辑上挪动后⾯的数据，空出位置。
resultsetmetadata获取字段注释更糟情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请⼀个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能⾃然会受影响。
除了性能外，页分裂操作还影响数据页的利⽤率。原本放在⼀个页的数据，现在分到两个页中，整体空间利⽤率降低⼤约 50%。
当然有分裂就有合并。当相邻两个页由于删除了数据，利⽤率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。
覆盖索引
即：where ⾮主键查询，但只查询ID,ID在⾮主键索引树上了，不需要回表。
联合索引
最左前缀
索引下推
对于where 条件，如果索引中包含了该字段信息，会直接进⾏过滤，不会再回表⽐对。
6. 全局锁和表锁
根据加锁的范围，MySQL ⾥⾯的锁⼤致可以分成全局锁、表级锁和⾏锁三类
1. 全局锁的典型使⽤场景是，做全库逻辑备份
Flush tables with read lock (FTWRL)：其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
相较于readOnly，本命令在客户端异常后会⾃动释放锁。
2. 表级锁（表锁和数据锁）
表锁的语法是 lock tables … read/write
另⼀类表级的锁是 MDL（metadata lock)。在 MySQL 5.5 版本中引⼊了 MDL，当对⼀个表做增删改查操作的时候，⾃动加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
1. 读写锁、写锁之间互斥
MDL会导致该表结构时阻塞，online DDL可以看下。
7.⾏锁
能零基础学编程吗MySQL 的⾏锁是在引擎层由各个引擎⾃⼰实现的。MyISAM 不⽀持⾏锁。不⽀持⾏锁意味着并发控制只能使⽤表锁，同张表上只能有⼀个更新在执⾏，这就会影响到业务并发度。
两阶段锁
在 InnoDB 事务中，⾏锁是在需要的时候才加上的，但并不是不需要了就⽴刻释放，⽽是要等到事务结束时才释放。这个就是两阶段锁协议。
如果事务中需要锁多个⾏，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
死锁
这样就互相等待了。（1互斥、占有且等待、不可剥夺、循环等待）
死锁后：
等待，设置超时时间
死锁检测，主动回滚某个事务（推荐且默认）。
但并发过多时，死锁检测耗费CPU过多。
保证不出现，关闭检测。
控制并发度
8. 事务到底是不是隔离的
begin/start transaction 命令并不是⼀个事务的起点，在执⾏到它们之后的第⼀个操作 InnoDB 表的语句，事务才真正启动。
在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。
数据表中的⼀⾏记录，其实可能有多个版本 (row)，每个版本有⾃⼰的 row trx_id，每个事务或者语句有⾃⼰的⼀致性视图。
三个虚线箭头，就是 undo log；⽽ V1、V2、V3 并不是物理上真实存在的，每次需要时根据当前版本
和 undo log 计算的。如需要 V2时，就通过 V4 依次执⾏ U3、U2 算出来。
InnoDB 为每个事务构造了⼀个数组，⽤来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。事务 ID 的最⼩值记为低⽔位，当前系统⾥⾯已经创建过的事务 ID 的最⼤值加 1 记为⾼⽔位。
绿⾊可见，红⾊不可见。黄⾊中，如果在数组中，是未提交的事务⽣成的，不可见。否则可见。
**InnoDB 利⽤了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能⼒。**
更新数据都是先读后写的，⽽这个读，只能读当前的值，称为“当前读”（current read）
对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
对于读提交，查询只承认在语句启动前就已经提交完成的数据；
9.普通索引和唯⼀索引
查询
对于普通索引查第⼀个记录后还要查下⼀个，直到不满⾜。唯⼀索引直接定位。但差距很⼩，innoDb
按数据页读写，16KB在内存。更新
更新⼀个数据页时，如果数据页在内存中就直接更新。不在，将更新操作缓存在 change buffer 中，不需从磁盘中读⼊了。在下次查询要访问这个数据页时，将数据页读⼊内存，然后执⾏ change buffer 中与这个页有关的操作。
change buffer 在内存中有拷贝，也会被写⼊到磁盘上。
将 change buffer 中的操作应⽤到原数据页，得到最新结果的过程为 merge。除访问数据页会触发 merge ，后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会 merge 。
唯⼀索引需要判断唯⼀性约束，必须读⼊数据页，也就直接写了，不需要change buffer。
普通索引就可以先写⼊change buffer，避免io开销。
那么对于读少写多，change buffer就有⽤。反过来还是要多次io，效益降低。
10. mysql为什么会选错索引
索引信息统计不准确的，可以使⽤ analyze table x重新分析。
优化器误判的，可以 force index强制指定。
或者修改语句引导优化器，增加/删除索引绕过。
11. 怎么给字符串字段加索引
使⽤前缀索引，定义好长度，就可以做到既节省空间，⼜不⽤额外增加太多的查询成本。正则化网络
使⽤前缀索引可能就⽤不上覆盖索引对查询性能的优化了。
倒序存储，再创建前缀索引，⽤于绕过字符串本⾝前缀的区分度不够的问题；
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种⽅式⼀样，都不⽀持范围扫描。
12.为什么我的Mysql会抖⼀下
当内存数据页跟磁盘数据页内容不⼀致的时候，我们称这个内存页为“脏页”。内存数据写⼊到磁盘后，内存和磁盘上的数据页的内容就⼀致了，称为“⼲净页”。
平时执⾏很快的更新操作，其实就是在写内存和⽇志，⽽ MySQL 偶尔“抖”⼀下的那个瞬间，可能就是
在刷脏页（flush）。
12.1 什么情况会引发数据库的 flush 过程呢？掌柜在什么情况下会把粉板上的赊账记录改到账本上？
粉板满了记不下。 InnoDB 的 redo log 写满。会停⽌所有更新，checkpoint往前推，redo log 留出空间继续写。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。