mysql总结（下）

------------------------------------日志部分---------------------------------------------

前提知识：

客户端连接MySQL的过程：

mysql其实是分为服务层和引擎层的：

1.服务层主要包含：客户端进行连接器的连接、然后下一步进行 查询 缓存，但是缓存有一个弊端，就是涉及到update语句缓存就会失效，（这里类似于redis的缓存旁路失效），所以较高的版本，就把mysql缓存去掉了，然后进行分析器（包括语法分析和词法分析）、之后是优化器，以及最后的执行器。

2：引擎层：这里和磁盘进行交互，首先包含一个 行记录缓冲池 ，然后主要是一些引擎的api接口，我们可以调用进行写入和查询；

1.一条更新语句的执行过程

总览：

主要讲一下执行器到引擎层以及和磁盘交互的过程：

1：当缓冲池有数据的时候，我们会进行加锁，访问修改

2：当缓冲池没有数据的时候：

1. 我们首先要在磁盘文件进行磁盘数据的一个加载，加载到引擎层的缓冲池中。
   
2. 然后将其写入uodo log日志中，用于回滚。
   
3. 然后进行事务的更新过程，利用执行器进行更行缓冲池的数据，（这时候我们可以称其为脏数据），因为不同于数据库中的数
   如果此时宕机，不用操作，因为没有刷到磁盘中，因为目前都没有提交事务，所以此时宕机都会消失，不会影响磁盘。
   
4. redo log日志的写入，这时候：就是记录下来你对数据做了什么修改，比如对“磁盘中某一页的id=10这行记录修改了name字段的值为xxx”，这 就是一个日志。mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志，再写磁盘 的技术就是 MySQL里经常说到的 WAL(Write-Ahead Logging) 技术。
   
5. redo log写入缓冲区，在你更新的的时候，有三种策略：
   5.1）0，事务提交的时候，每s进行写入os buffer，然后立即刷新到磁盘。----所以mysql宕机，会有1s数据的丢失
   
6. 5.2）1，事务提交的时候，直接写入os buffer中，然后刷新到磁盘。----对于每次提交的事务，都会写到了磁盘，不会有数据的丢失。(推荐，虽然有性能随时，但是保证数据的安全)

5.3）2，事务提交的时候，直接写入os buffer中，然后，每s刷到磁盘。----mysql宕机数据不会丢失，但是如果整个物理机器宕机，也会有1s的数据丢失。

现在来思考🤔:事务已经提交了，redo log 进入了磁盘，但是数据 仍然在内存中，还未刷入磁盘，此时机器宕机，数据会丢失？

答案是不会，因为虽然内存里的修改成name=xxx的数据会丢失，但是redo日志里已经说了，对某某数据做了修改 name=xxx。 所以此时mysql重启之后，mysql会进行检查恢复，会根据redo日志去恢复之前做过的修改，我们看下图。

6.binlog日志的写入缓冲区，在你准备提交事务的时候，有两种策略：

6.1）0，提交事务的时候，先刷新到os的cache中，由系统最后再刷新到磁盘文件。

6.2）1，提交事务的时候会立即刷新到磁盘中。

6.3）N，N个事务提交的时候才会写到磁盘中。

7.基于bin log 和 redo log的事务提交

最后一步，我们将binlog 写入磁盘后，会把此次更新的binlog文件名称和这次更新的binlog日志在文件中的位置（即：名称+位置），都会写入到redo log中，同时再redo log中写入commit标记，在完成这个事情后，才算完成最终的提交。

引：两阶段提交

**以上所有日志的写入磁盘，都是在事务提交前进行的（可以这样理解，写入的时候，都算事务提交的阶段，但是最后的commit才标志着最后的事务的完成），**可有看到redo log 经历了prepare和commit两个状态的阶段，为什么有两个阶段？

1）先写redo log，再写bin log，如果redo log成功，bin log写入失败，则系统恢复的时候，redo log进行了恢复，bin log就会少了这次数据的修改，所以之后恢复临时库的时候，bin log这句就丢失了。

2）先写bin log，再写redo log，如果bin log写完，而redo log崩溃，恢复的时候，redo log不会操作，但是如果恢复临时库的时候，bin log同样会执行这句，同样导致当前库和临时库的不同。

而两阶段就不同了：

1）binlog有记录，redo log commit阶段，所以是正常完成的事务，不用恢复。

2）bin log 有记录，redo log prpare阶段，所以此时需要重新提交事务。

3）bin log无记录，redo log prepare阶段，回滚事务。

注：其实这里还是不太理解，但是本质主要是解决了redo log和bin log日志的数据库一致性。

8.事务完成

9.mysql会有个后台线程将内存数据（某个时间内）刷入到磁盘

在你IO线程把脏数据刷回磁盘之前，哪怕mysql宕机崩溃也没关系，因为重启之后，会根据redo日志恢复之前提交事 务做过的修改到内存里去，就是id=10的数据的name修改为了xxx，然后等适当时机，IO线程自然还是会把这个修改 后的数据刷到磁盘上的数据文件里去的 。

2.redo log的记录形式

redo log是一个循环写入的日志：

write pos：日志刷盘位置

check point：数据页的刷盘位置

所以一般来说，write pos会在check point前面，而check point和 write pos之间就是待要数据页的刷新操作，而write pos和check point之间是空的位置。

对于每次重启或者是崩溃恢复的时候：

1：write pos会在check point前面，则进行check point到write pos的一个恢复

2：当然存在check point在write pos之前的情况，这时候，就不需要恢复了，write pos继续在check point开始即可

3：如何区分两者谁在前，谁在后，是有一个LSN位置序号进行比较的。

3.三种日志的比较

有了上面的理解：

1）bin log

三种格式：

STATMENT格式： 基于SQL语句的复制，每一条修改数据的sql语句会记录到bin log中

row格式： 基于行的复制，不记录每条sql语句的上下问信息，仅需要记录哪条数据被修改了，以及修改成什么样了，这里不同于redo log，redo log是记录的物理页变更，精确到了哪一页的哪一行的直接数据变化。

MIXED格式： 一般的复制使用STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog

用途： 主要用于主从复制。

2）redo log 持久性的表示：记录的是物理页的记录

出发原则：

因为 Innodb 是以 页 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！

一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

所以：redo log其实只是记录了物理页的变更，并且可以顺序IO写入。

用途： 主要用于崩溃恢复的一个持久性。

3）undo log日志

数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。

实际上， 原子性 底层就是通过 undo log 实现的。undo log 主要记录了数据的逻辑变化 ，比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。

同时， undo log 也是 MVCC(多版本并发控制)实现的关键。

4.MVCC的实现原理

• 出发点：读写分离，对于读不加锁，写加锁，可以实现提交读、可重复读的隔离级别。
  
• 实现：undo log 多版本链条 + ReadView（读视图）
  
• 1.undo log链条：
  
• 每条数据其实有两个隐藏的字段：trx_id(最近更新这条数据的事务id)、roll_pointer（指向之前的undo log）
  
• 每次修改数据的时候都会更新trx_id、roll_pointer这两个字段，同时之前的索格数据块找对应的undo log通过roll_pointer指针串联起来，形成一个版本链条。
• 
  
• 2.ReadView 读视图：
  
• 执行一个事务，会生成一个ReadView，主要由一下组成：
  
• m_ids:Mysql哪些事务执行还未提交的事务列表
• min_ids:m_ids最小的事务id
  
• max_ids:m_ids最大的事务id
  
• cur_ids:当前的事务id
  
• 查询（并发）：
  
• 1.trx_id（查询的数据事务id） < min_ids:说明肯定能查询到；
  
• 2.trx_id > max_ids:说明查询不到，只能通过undo log多版本链条查看以前的；
  
• 3.trx_id = cur_ids:说明是当前事务修改的，可以查询到；
  
• 4.min_ids < trx_id < max_ids并且trs_id在当前m_ids中：其他事务正在处理，所以不能查询到。
  
• 3.已提交读
  
• 本质：每次查询的时候，都会生成一个新的读视图
  
• 事务B修改后，提交；这时候的事务A再次查询的时候，就会有新的readview，而此时的ReadView的事务列表中就会去掉事务B，从而查询到事务B修改后的。
  
• 4.可重复读
  
• 本质：每次查询的时候，不需要都生成一个新的读视图，只需要事务刚开始查询的时候的第一次即可。
  
• 这样的话，在事务B修改后，提交后，事务A再次查询的时候，因为B是当前的事务列表中，所以会根据undo log链条进行向前查询，从而查询查询到之前的。
  

5.一条查询语句的执行过程

select查询顺序应该是：

from---->where ----->group by----->having --------->select--------->order by ------>limit的吗？

所以相对于磁盘来说：

1. 从磁盘读取表到内存中
2. where进行第一步过滤
3. group by进行分组，生成多张临时表
4. 对每张临时表进行过滤
5. 然后进行条件查询，将结果又组合到一张表中
6. 然后进行排序
7. 最后进行limit过滤

注： 当然前面还是会有：连接器—查询缓存-----分析器----优化器-----执行引擎-------缓冲池行记录-------然后进行读取。

补充：

在 MySQL5.7.5 之前的版本，ONLY_FULL_GROUP_BY sql mode默认不开启。在5.7.5或之后的版本默认开启。允许在 having condition 中使用 select list 中的 alias。

6.日志最后

感谢下面各位大佬博客的讲解；然后对于我来说的一些认识：从三种日志，然后分别讲解了三种日志的不同点，针对三种日志在一条查询语句和一条修改语句的过程，当然查询语句不会用到，然后redo log的一个崩溃恢复的过程，包括日志的write pos和数据页的checkpoint，但是修改语句会涉及到redo log和bin log的一个顺序问题，而由此产生了两段提交过程来解决它俩不一致的问题，当然某些底层细节还是不太懂，就是在不同的刷盘策略下，其应对崩溃的解决方法，这里只知道个整体把，到了细节还是需要用到的时候会有更深的理解吧。

------------------------------------索引部分---------------------------------------------

1.hash索引

CREATE TABLE `testhash` (
  `fname` varchar(50) DEFAULT NULL,
  `lname` varchar(50) DEFAULT NULL,
  KEY `fname` (`fname`) USING HASH
) ENGINE=MEMORY;

为什么用MEMORY存储引擎，因为mysql只有MEMORY存储引擎显示支持哈希索引。

看如下查询：

select lname from testhash where fname ='Peter'

Mysql首先计算Peter的哈希值是8784，然后到哈希索引中找到对应的行指针，根据指针找到对应的数据行。 索引只存储哈希码及行指针，所以索引的数据结构非常的紧凑，这也让哈希索引查找速度非常快，但是哈希索引也有他的限制。

注：是不是和MISIAM存储引擎很相似，都是索引和数据分开的。

2.innodb默认不支持哈希索引

只能说一般是InnoDB自己在优化的过程中，才会自动的创建一张表来生成哈希索引。

3.优点和缺点

• 等值查询比较快
• 不适合范围查找

4.聚集索引和非聚集索引

• 概念：什么是聚集索引，什么是非聚集索引？
  主要出发点：是要看索引的排列顺序和表记录的排列顺序是否一致，因为对于InnoDB存储引擎来说，其主要是B+树，而B+树的数据结构中，存储的索引+数据，同时保证了排列顺序的一致，所以是聚集索引，而MySIAM是非聚集索引。
• 聚集索引：
• 主键索引：在结构中同时保留了主键key+行记录 .ibd结构 （数据就这一份）

辅助索引：在结构中为辅助键的key + 主键的key （这是什么结构？不知道）

回表：所以对于辅助索引一般会有回表操作，但是对于索引覆盖可以避免回表，情形如下：

select age from employee where age < 20

• 非聚集索引
  主键索引：在结构中保存的是主键key + 行记录的地址 .myd .myi（本质：数据和索引分离）
  辅助索引：结构和主键索引一样
  

5.常见的索引

主键索引（唯一为空）

• 如果定义了主键，那么InnoDB会使用主键作为聚簇索引
• 如果没有定义主键，那么会使用第一非空的唯一索引（NOT NULL and UNIQUE INDEX）作为聚簇索引
• 如果既没有主键也找不到合适的非空索引，那么InnoDB会自动生成一个包含了ROW_ID值的列作为聚簇索引，行都会根据这个ROW_ID排序。
• 注：没有主键的结果

很明显，缺少主键的表，InnoDB会内置一列用于聚簇索引来组织数据。而没有建立主键的话就没法通过主键来进行索引，查询的时候都是全表扫描，小数据量没问题，大数据量就会出现性能问题。

• 唯一索引
  就是唯一值的列建立的索引
• 普通索引
  不涉及唯一+非空

6.哪些需要创建索引？哪些不需要创建索引？

• 需要创建索引
• 1.经常查询的字段可以加快速度
• 2.主键（默认聚簇索引）
• 3.where中常用的列字段
• 4.在经常需要排序的列上加索引，**因为本身索引查出来就是有序的，**所以会加快速度
• 5.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
• 哪些不需要创建索引
• 1.查询中很少使用的字段
• 2.对于那些定义为text, image和bit数据类型的列不应该增加索引：这是因为，这些列的数据量要么相当大，要么取值很少。 取值大，索引很复杂，取值小，建立不出什么东西。
• 3.对于那些只有很少数据值的列也不应该该增加索引，因为在查询的时候会涉及到很大数据行的，并且涉及到回表，所以不一定很快。

7.联合索引以及最左匹配原则？

• MySql使用索引时需要索引有序，假设现在建立了“name，age，school”的联合索引。
• 那么索引的排序为从左到右：先按照name排序，如果name相同则使用age排序，如果age也相等则使用school排序。
• 原则：建立联合索引的时候要把查询频繁的字段放在前面。

8.如何查看创建的索引有没有被使用到？

• 命令：explain
• 分析：type字段
• all、range、ref（该索引列的值并不唯一）、ref_eq(使用了索引，并且值唯一)、const（主键放在where后）

9.索引失效原则？

• 使用不等<>= 查询
• 不符合最左匹配原则
• 联合索引，使用范围查找，后面的部分
• like字段 like"%a"，最左边是通配符
• mysql优化器本身分析出不走索引快的时候
• 索引列进行操作（表达式运算/分组函数）

10.索引的优缺点？

• 优点：加快查询的速度
• 缺点：数据的写入过程会涉及到索引的更新，也就是索引的维护
• 1.节点的插入：主要涉及到节点的分裂，当然还有节点索引值的修改
• 2.节点的删除：主要涉及到节点的合并，当然还有索引值的修改

https://cloud.tencent.com/developer/article/1692119

https://blog.csdn.net/doctor_who2004/article/details/77414742

https://www.nowcoder.com/discuss/389444?type=post&order=time&pos=&page=1&ncTraceId=&channel=-1&source_id=search_post_nctrack

-----------------------------------补充部分---------------------------------------------1.主键 超键 候选键 外键

• 主键：数据库表中对储存数据对象（一行）予以唯一和完整标识的数据列或属性的组合。唯一且不能为空。
• 超键：也是唯一标识，但是可以包含其他非主键字段，所以其包含主键。
• 候选键：也是唯一标识，但是是最小的超键，即不包含无关的属性。
• 外键：一个表中存在的另一个表的主键是这个表的外键。

如：表（学号、姓名、身份证）

学号：主键、超键

身份证：候选键、主键、超键

学号+姓名：超键

关系：超键>候选键>主键

２.drop,delete与truncate的区别

• drop直接删掉表,包括数据+结构。
• truncate删除表中数据，再插入时自增长id又从1开始。
• delete删除表中数据，可以加where字句。

3.内连接、外连接、笛卡尔积

• 内连接：join/inner join

• 等值连接：select * from R,S where R.B=S.B;或select * from R inner join S on R.B=S.B;
• 
  注：相同的部分都保留，而自然连接不是。

• 
  
• 交叉连接/笛卡尔积（cross join）

• select * from R,S;或select * from R cross join S;
  

• 自然连接（natural join）
  

• select R.A,R.B,S.C from R,S where R.B = S.B;或select * from R natural join S;
  
  注：先进行笛卡儿积操作，然后重复的属性只保留一份。

• 外连接（natural join）
  
• 左外连接：select * from R left join T on R.B=T.B;
  
  
• 右外连接：select * from R right join T on R.B=T.B;
  
  
• 全连接
  Mysql不存在！
  
• 三张表：
  
• R表
  
  
• S表
  
  
• T表
  
• 参考链接：

https://baijiahao.baidu.com/s?id=1655935519271290347&wfr=spider&for=pc