mysql知识

TImoon
164 阅读11分钟

mysql存储引擎

MyISAM和InnoDB的区别

mysql5.5之后引入InnoDB

1）MyISAM支持表级锁，InnoDB支持行级锁

2）MyISAM不支持事务，InnoDB支持事务

3）MyISAM不支持崩溃恢复，InnoDB可以根据redolog进行崩溃后恢复

4）MyISAM不支持外键，InnoDB支持外键

mysql事务

事务是一组操作的合集

事务特性ACID

1）原子性 所有操作要么都执行，要么都不执行

2）一致性 一个事务必须使数据库从一个一致性状态变换到另一个一致性状态，比如a=1，b=3到a=2,b=2

3）隔离性 并发访问时，各个事务之间的操作互不干扰

4）持久性 事务完成后，对数据库的数据的修改是持久的

并发事务出现的问题

1）脏读 a事务对数据进行了修改还未提交，b事务读取了该数据，则读到的是脏数据

2）丢失修改 a事务读取并修改了数据还未提交，b事务对同一数据进行了修改，则最后会丢失掉一次修改。

3）不可重复读 a事务读取了数据，b事务对该数据做了修改，则a事务再次读取到的数据和第一次不一致。

4）幻读 a事务读取了数据，b事务提交了新的满足条件的数据，则a事务再次读取到的数据与第一次不一致。

隔离级别

1）读未提交 最低的隔离级别，会出现脏读，幻读或不可重复读

2）读已提交 可以防止脏读，会出现不可重复读和幻读

3）可重复读 mysql默认级别，多次读取的数据一致，可以防止不可重复读，会有幻读问题。使用mvcc(Multi-Version Concurrency Control)技术实现，要消除幻读还需加行锁，使用 Next-Key Locks

4）可串行化读 最高的级别，各个事务串行执行，互补干扰

MVCC

blog.csdn.net/SnailMann/a…

juejin.cn/post/684490…

baijiahao.baidu.com/s?id=162940…

MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。

mysql行记录隐藏字段

DB_TRX_ID 事务ID 6byte 记录创建/修改这条记录的事务ID

DB_ROLL_PTR 回滚指针 7byte 指向这条记录的上一个版本位置

DB_ROW_ID 隐藏主键 未指定自增主键时生成

还有一个删除flag字段，逻辑删除 image.png

undo日志

undo log主要分为两种：

insert undo log 代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃 update undo log 事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除 image.png

Read View(读视图)

什么是Read View，说白了Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大）

trx_list（名字我随便取的） 一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID up_limit_id 记录trx_list列表中事务ID最小的ID low_limit_id ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1

image.png

可见性判断

快照读：select 默认进行快照读，第一次select时生成当前记录的数据，之后的select都是第一次时的数据

select * from table ...;

当前读：每次读取的都是已提交的最新数据

select * from table where ? lock in share mode; //共享锁(s)，其他事务可读不可修改
select * from table where ? for update;      //排他锁(x)  其他事务无法上锁，无法读取和修改，但快照                                              //读可以
insert; 
update; 
delete;

对当前事务进行快照读，会生成一个readview,将拿到的数据中记录事务id和readview读视图中id进行比较判断

1）首先比较DB_TRX_ID < up_limit_id, 如果小于，则当前事务能看到DB_TRX_ID 所在的记录，如果大于等于进入下一个判断 2）接下来判断 DB_TRX_ID 大于等于 low_limit_id , 如果大于等于则代表DB_TRX_ID 所在的记录在Read View生成后才出现的，那对当前事务肯定不可见，如果小于则进入下一个判断 3）判断DB_TRX_ID 是否在活跃事务之中，trx_list.contains(DB_TRX_ID)，如果在，则代表我Read View生成时刻，你这个事务还在活跃，还没有Commit，你修改的数据，我当前事务也是看不见的；如果不在，则说明，你这个事务在Read View生成之前就已经Commit了，你修改的结果，我当前事务是能看见的

4）如果都不满足则根据回滚指针对前一个个版本数据进行判断

一句话总结就是，事务id小于读视图中最小id或者小于下一个最新事务id且不在活跃事务中则可见，否则根据版本链检索上一个版本数据。

不同隔离级别策略

在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；

而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。

mvcc可以实现可重复读，但无法解决幻读问题

锁机制
InnoDB有三种行锁的算法：

1，Record Lock：单个行记录上的锁。锁定一个记录上的索引，而不是记录本身。  没加索引则有默认的主键索引

2，Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。 左开右闭

3，Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题

当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

 

mysql索引

b-树

image.png

  • 1个结点最多存比最大子树数k-1的关键字数据；
  • 所有键值存储在整个树中
  • 任何一个关键字只在树中出现1次
  • 搜索可能在非叶子节点中结束（最好情况O(1)）
  • 在关键字全集内做一次查找，性能逼近二分查找
b+树

image.png

  • 多路平衡搜索树，只有叶子节点存储数据，非叶子节点只存储关键字
  • 叶子节点有一条引用链指向相邻节点，叶子节点
  • 一个结点最多有其最大子树k个关键字
  • 一个关键字在树中多次出现
  • 叶子节点拥有全部的关键字，叶子节点有序的、

需要补充的是，在数据库的聚集索引（Clustered Index）中，叶子节点直接包含卫星数据。在非聚集索引（NonClustered Index）中，叶子节点带有指向卫星数据的指针。

B+树和hash索引的区别
  • hash索引存在hash冲突
  • hash索引不支持范围和顺序查找
B+树和b-树的区别

  • b+树非叶子节点只存储关键字不存储数据，同样的页（innodb单位是页16kb）可以存储更多的数据，磁盘I/O数据大小固定的，一次从磁盘可以加载更多索引数据，减少磁盘I/O

  • b+树的检索是稳定的，任何查找都是从根几点到叶子节点O（logn）

  • b- 树范围查询使用中序遍历繁琐，b+树叶子节点有序且相连，范围查询更简单

    由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

    预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

    所以IO一次就是读一页的大小

    blog.csdn.net/a519640026/…

    zhuanlan.zhihu.com/p/54102723

索引类型

主键索引

数据表的主键列使用的就是主键索引。

二级索引（辅助索引）

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

聚集索引

聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。

在 Mysql 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

聚集索引的优点

聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

聚集索引的缺点
  1. 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  2. 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。
非聚集索引

非聚集索引即索引结构和数据分开存放的索引。

二级索引属于非聚集索引。

非聚集索引的叶子节点并不一定存放数据的指针， 因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚集索引的优点

更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

非聚集索引的缺点
  1. 跟聚集索引一样，非聚集索引也依赖于有序的数据
  2. 可能会二次查询(回表)  :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

image.png

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

B+树三层能存多少数据

zhuanlan.zhihu.com/p/86137284?…

这个问题的简单回答是：约 2 千万。

在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是 4k，而对于我们的 InnoDB 存储引擎也有自己的最小储存单元——页（Page），一个页的大小是 16K。

1、InnoDB 存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值 + 指针，在 B+ 树中叶子节点存放数据，非叶子节点存放键值 + 指针。

2、索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据；

这里我们先假设 B+ 树高为 2，即存在一个根节点和若干个叶子节点，那么这棵 B+ 树的存放总记录数为：根节点指针数 * 单个叶子节点记录行数。

上文我们已经说明单个叶子节点（页）中的记录数 =16K/1K=16。（这里假设一行记录的数据大小为 1k，实际上现在很多互联网业务数据记录大小通常就是 1K 左右）。

其实这也很好算，我们假设主键 ID 为 bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170。

那么可以算出一棵高度为 2 的 B+ 树，能存放 1170*16=18720 条这样的数据记录。

根据同样的原理我们可以算出一个高度为 3 的 B+ 树可以存放： 1170117016=21902400 条这样的记录。

sql语句在mysql中的执行过程

image.png

  • 连接器：  身份认证和权限相关(登录 MySQL 的时候)。
  • 查询缓存:  执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
  • 分析器:  没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
  • 优化器：  按照 MySQL 认为最优的方案去执行。
  • 执行器:  执行语句，然后从存储引擎返回数据。
mysql优化器

逻辑优化

  • 优先做选择和投影(选择条件在查询树上下推)
  • 子查询的消除
  • 嵌套连接的消除
  • 外连接的消除
  • 连接的消除
  • 使用等价谓词重写，对条件化简
  • 语义优化
  • 剪掉冗余操作(一些剪枝优化技术)、最小化查询块。

物理优化

查询优化器在物理优化阶段，主要解决的问题如下:

  • 从可选的单表扫描方式中，挑选什么样的单表扫描方式是最优的？
  • 对于两个表连接时，如何选择是最优的？
  • 对多个表连接，连接顺序有多种组合，是否要对每种组合都探索？如果不全部探索，怎么找到最优的一种组合？

在查询优化器实现的早期，使用的是逻辑优化技术，即使用关系代数规则和启发式规则对查询进行优化后，认为生成的执行计划就是最优的。在引入了基于代价的查询优化方式后，对查询执行计划做了定量的分析，对每一个可能的执行方式进行评估，挑出代价最小的作为最优的计划。目前数据库的查询优化器通常融合这两种方式。

avatar
文章
阅读
粉丝