MySQL基础

堇荼丶

于 2024-10-07 11:07:34 发布

阅读量717

点赞数 10

分类专栏：进阶文章标签： mysql

本文链接：https://blog.csdn.net/qq_46278368/article/details/142737212

版权

进阶专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.MySQL

1.1 MySQL的逻辑架构

1.2 MySQL执行流程

1.客户端建立连接，发送请求
2.MySQL查询缓存
3.解析器进行语法解析，解析成解析数，并交由预处理器处理为新的解析树
4.查询优化器进行sql优化
5.执行计划，查询执行引擎
6.API接口进行查询

建立连接：MySQL客户端与MySQL服务器建立连接，通过SQL接口发送SQL语句。MySQL服务器先查询缓存，有就直接返回，没有就进行语句解析
解析：MySQL解析器通过关键字将SQL语句进行解析，并生成一颗对应的解析树，解析器使用MySQL语法规则验证和解析SQL语句，预处理器再进行进一步的解析，验证数据表和列以及字段名和别名，并生成一颗新的解析树（新的解析数树可能会和旧解析数结构一致）
优化：优化的结果的就是生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，以及表直接的连接顺序怎么样，使用explain查询语句的执行计划
执行：查询执行引擎会按照生成的执行计划调用存储引起提供的接口进行SQL查询，并将结果返回给客户端。

1.3 InnoDB和MyISAM的区别

	InnoDB	MyISAM
默认引擎	MySQL5.5之后	MySQL5.5之前
行锁	支持	不支持
表锁	支持	支持
事务	支持	不支持
外键	支持	不支持
聚簇索引	支持	不支持
全文索引	不支持	支持
必有唯一索引	是(没定义有隐藏列)	否
存储文件	.ibd	.sdi
崩溃恢复	支持	不支持
缓存	不仅缓存索引还缓存真实数据	只缓存索引

MyISAM占用内存比InnoDB少

1.4 b树和b+树的区别

关键字不同：b树每一个关键字有且只出现一次，且所有关键字按从小到大进行排序；而b+树有关键字会重复存储，非叶子节点只保存关键字
存储内容不同：b树每个节点除了存关键字（主键）还要存储数据；b+树所有的叶子结点存储内容包含关键字（主键）还以及指针，叶子节点构成一个有序链表。b+树找那个非叶子节点的关键字也会存于子节点中
查找不同：b树查找相当于二分查找，可以在非叶子结点结束，访问次数和时间不固定；b+树查找次数固定，稳定查到叶子结点

1.5 索引类型

1.5.1 聚簇索引

是一种数据的存储方式。按照索引的键值进行排序，相同索引的键值的行会被存储到一起，形成一个聚簇，每个表只能有一个聚簇（简而言之就是找到了索引就找到了需要的数据，所以其叶子节点包含全部数据，其他节点只包含索引列）

优点：1.数据访问更快，因为聚簇索引将数据和索引保存同一个b+树中

2.聚簇索引对于主键的排序查找和范围查找速度快

3.减少磁盘占用

4.实现相关数据的紧密关联

缺点：1.插入速度严重依赖于插入顺序

2.更新主键的代价高，因为会导致被更新的行移动

3.二级索引访问需要两次索引查找，第一次找主键值，第二次找行数据

1.5.2 非聚簇索引

也称为辅助索引或二级索引，这种索引将数据和索引分开存储，索引的叶子节点不放数据，而放指向数据行的地址，所以会导致回表查

优点：1.对数据进行修改和删除时不需要更新索引树，减少了系统的开销

2.查询速度相对较快

3.减少磁盘占用，提供存储效率

4.灵活的数据组织

缺点：1.要进行回表查

2.查询所有列信息时，需要进行额外的I/O

1.5.3 联合索引

为多个列建立索引

1.5.4 覆盖索引

一个索引包含了满足查询结果的所有数据就是覆盖索引

1.6 索引使用场景

1.7 优化

查询优化
索引优化
配置优化
内容管理
存储引擎选择
数据库设计优化
覆盖索引优化：禁止使用select *，禁止查询与业务无关的字段；进来使用覆盖索引
分组优化：group by没有过滤条件也能使用索引；包含了order by，group by、distinct这些查询的语句，where条件过滤出来的结果集尽量保持在1000行

1.8 索引失效

进行计算或使用函数导致索引失效
%开头的模糊查询导致索引失效
使用不等于（！=或 <> ）导致索引失效
is not null或is null 也会导致索引失效
类型转换导致索引失效
违背最左前缀法则导致索引失效
索引中范围条件右边的列失效

1.9 排序优化

1.9.1 排序索引失效的情况

无过滤不索引，没有过滤条件的排序SQL会导致索引失效
顺序错索引，要和联合索引的顺序一致
排序反不索引，又有desc又有asc导致索引失效

1.9.2 单路排序

单路排序是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序

单路排序能一次性将结果读取出来，然后在sort buffer中排序，避免了双路排序的两次读的随机IO，但当获取的列有很多的时候，排序起来就很占空间

1.9.3 双路排序

双路排序也回表排序，先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在sort buffer中进行排序，排序完后需要再其次其他需要的字段信息

第一扫描出需要排序的字段，然后进行排序后，根据排序结构在进行第二遍扫描，第二遍扫描出需要查询的列数据，这样会引起大量的随机IO，效率不高但节约内存。排序使用快速排序，但是如果内存不够会按照block进行排序，将排序结果写入磁盘文件，然后再将结果合并

在MySQL内部，是通过比较max_length_for_sort_data（默认1024字节）这个系统变量跟需要排序的字段长度进行比较来判断用哪种排序的。如果需要排序的字段总长度小于max_length_for_sort_data，则采用单路排序，否则采用双路排序

1.10 基础日志

1.10.1 错误日志

记录MySQL服务器在启动和运行过程中发送的错误和异常情况

1.10.2 查询日志

记录所有执行的查询语句，包括insert，update，delete，select等。可以用于分析查询性能和调试问题，但是对于高负载的系统，开启查询日志可能会性能产生影响

1.10.3 慢查询日志

记录执行时间超过指定阈值的查询语句。它可以帮助找出执行时间较长的查询，以便进行性能优化

1.10.4 二进制日志

记录所有对数据库的更改操作，包括数据修改、表结果修改等。二进制日志可以用于数据恢复、主从复制等场景

1.11 事务日志

记录正在进行的事务的更改操作、事务日志用于保证数据库的ACID特性，并支持崩溃恢复
redo log是事务持久性的保证，undo log是事务原子性的保证。在事务中更新数据的潜质操作其实是要先写入一个undo log

1.11.1 redo log

redo log可以简单分为：重做日志的缓冲（redo log buffer）：保存在内存中，容易丢失，redo log buffer默认是16M最大为4G，最小为1M；

重做日志文件（redo log file）：保存在硬盘中，是持久的。

整体流程

1、先原始数据从磁盘中读入内存中来，修改数据的内存拷贝
2、生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
3、当事务commit时，将redo log buffer中的内容刷新到redo log file中，对redo log file采用追加写的方式
4、定期将内存中修改的数据刷新到磁盘中

优点

降低了刷盘的频率
占用的空间小

特点

redo日志是顺序写入磁盘的
事务执行过程中，redo log也会不断记录

1.11.2redo log的刷盘策略

redo log的写入并不是直接写入磁盘的，InnoDB引擎会在写redo log的时候先写redo log buffer，之后以一定策略将redo log buffer中的数据刷新至redo log file

刷盘的三种策略

1、设置innodb_flush_log_at_trx_commit为0：表示每次事务提交时不进行刷盘操作（不往redo log file中写）。系统默认主线程每隔1s进行一次重做日志的同步。性能最佳，数据风险高，可能会丢失1s的数据
2、设置innodb_flush_log_at_trx_commit为1（默认策略）：表示每次事务提交时都将进行同步，刷盘操作。数据安全性较高，性能较差
3、设置innodb_flush_log_at_trx_commit为2：表示每次事务提交时都只把redo log buffer内容写入page cache，不进行同步，由os自己决定什么时候同步到磁盘文件，性能较高，风险较高。可能会丢失最近提交的事务数据

1.11.3 undo log

MySQL把这些为了回滚而记录的这些称为撤销日志或者回滚日志（undo log）。InnoDB对undo log的管理采用段的方式，也就是回滚段（rollback segment），在版本1.1之前只能有1个回滚段，但之后InnoDB最大支持128个回滚段。每个回滚段记录了1024个undo log segment，而在每个undo log segment段中进行undo页的申请

作用

回滚数据
MVCC

undo页的重用

当一个事务提交或回滚后，其对应的undo页就不再需要了，为了节约空间和提交性能，MySQL将这些不再需要的undo页标记为可重用状态并将他们添加到undo页的空闲链表中，之前使用的undo页可以被重新利用起来存储新的事务的undo信息

回滚段和事务

每个事务只会使用一个回滚段，一个回滚段在同一时刻可能会服务于多个事务
当一个事务开始的时候，会指定一个回滚段，在事务进行的过程中，当数据被修改时，原始的数据会被复制到回滚段中
当事务提交时，InnoDb会做两件事：将undo log放入列表中，以供之后的清理（purge）操作；判断undo log所在的页是否可以重用，若可以就分配给下个事务使用

undo log生成过程

1、start transaction ;
2、记录A=1到undo log  ;
3、update A = 3      ;
4、记录A=3到redo log  ;
5、记录B=2到undo log  ;
6、update B = 4 ;
7、记录B=4到redo log  ;
8、将redo log刷新到磁盘
9、commit

异常情况分析

在1-8步骤中任意一步宕机，系统未提交，该事务不会对磁盘上的数据做任何影响
在8-9宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交，因为此时的undo log已经持久化
若在9之后宕机，内存映射中变更的数据来不及刷盘，那么系统恢复之后，可以根据redo log把数据刷回磁盘

1.11.4 事务的隔离级别

读未提交：可以读到其他事务还未提交的数据。会导致脏的
读已提交：其他事务可以读到本事务已提交的数据。会导致不可以重复读问题
可重复读（默认）：不会读取到其他事务修改的数据即使已提交。会导致幻读
序列化：多个事务按照一定的顺序进行排列，使他们的执行结果与串行的执行结果相同。不会有以上问题，但性能低

1.12 InnoDb行锁

InnoDB实现了一下两种锁

共享锁（S）：又称读锁，简称S锁，共享锁就是可以多个事务对于同一个数据可以共享一把锁，都能访问到数据，但是只能读不能修改
排他锁（X）：又称写锁，简称X锁，排他所就是不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁。但是获取排他锁的事务是可以对数据进行读取和修改的

如果不通过索引条件检索数据，那么InnoDb将表中的所有记录加锁，实际效果跟表所一样

间隙锁：使用范围查询的时候，会把范围的数据行上锁，包括不存在的数据，这样可能会导致即使是不存在的数据也会插入不进去

优化

尽可能的让所有数据检索都能通过索引来完成，避免无索引导致行锁升级为表锁
合理设计索引，尽量所辖锁的范围
尽可能减少索引条件，及索引范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能使用低级别的事务隔离级别

1.13 MVCC

快照读

也叫一致读,读取的是快照数据，不加锁的简单的select都属于快照读

当前读

当前读获取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。加锁的select或者对数据进行增删改都会进行当前读

ReadView的规则

1、如果被访问的版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问他自己修改过的记录，所有当前事务可以访问

2、如果被访问的版本的trx_id属性值小于ReadView中的min_trx_id值，则表明生成该版本的的事务在当前事务生成ReadView之前已经提交了，所以该版本可以被当前事务访问

3、如果row.trx_id 大于ReadView中的min_trx_id以及row.trx_id小于ReadView中max_trx_id，则说明执行当前事务的时候很有可能存在一个并发事务，然后需要判断当前行记录的事务的id，是否在ReadView的m_ids（活跃事务）中，如果存在就说明的确存在一个并发事务，在进行数据读取的时候不允许读取当前行记录，可以通过行记录的回滚指针读取undo log来获取数据

4、如果row.trx_id大于max_.rx_id说明在执行当前事务的时候，又另外一个事务执行了并且提交了，那么，当前事务在读取数据的时候不允许读取当前行会数据，可以通过行记录的回滚指针读取undo log来获取数据

MVCC整体的操作流程