MySQL索引，存储引擎

追南风

于 2024-07-27 19:05:07 发布

阅读量1k

点赞数 28

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_69443943/article/details/140739647

版权

MySQL索引，存储引擎

MyISAM：拥有较高的插入，查询速度，但不支持事务
InnoDB ：5.5.8版本后Mysql的默认数据库引擎，支持ACID事务，支持行级锁定
Memory ：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失

InnoDB引擎

InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定保证数据一致性提升了它的多用户并发数量以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。默认使用B+TREE数据结构存储索引。

特点

支持事务，支持4个事务隔离（ACID）级别
行级锁定（更新时锁定当前行）
读写阻塞与事务隔离级别相关
既能缓存索引又能缓存数据
支持外键
InnoDB更消耗资源，读取速度没有MyISAM快
在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

业务场景

需要支持事务的场景（银行转账之类）
适合高并发，行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成的
数据修改较频繁的业务

InnoDB引擎调优

主键尽可能小，否则会给Secondary index带来负担
避免全表扫描，这会造成锁表
尽可能缓存所有的索引和数据，减少IO操作
避免主键更新，这会造成大量的数据移动

补充：事务（ACID）

A 事务的原子性(Atomicity)：指一个事务要么全部执行,要么不执行.也就是说一个事务不可能只执行了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱却没出来.这两步必须同时完成.要么就不完成.
.
C 事务的一致性(Consistency)：指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改变.
.
I 隔离性(Isolation）:当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
.
D 持久性(Durability）:事务的一旦提交对数据库的影响是永久的

MyISAM引擎

MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。默认使用B+TREE数据结构存储索引。

特点

不支持事务
表级锁定（更新时锁定整个表）
读写互相阻塞（写入时阻塞读入、读时阻塞写入；但是读不会互相阻塞）
只会缓存索引（通过key_buffer_size缓存索引，但是不会缓存数据）
不支持外键
读取速度快

业务场景

不需要支持事务的场景（像银行转账之类的不可行）
一般读数据的较多的业务
数据修改相对较少的业务
数据一致性要求不是很高的业务

MyISAM引擎调优

设置合适索引
启用延迟写入，尽量一次大批量写入，而非频繁写入
尽量顺序insert数据，让数据写入到尾部，减少阻塞
降低并发数，高并发使用排队机制
MyISAM的count只有全表扫描比较高效，带有其它条件都需要进行实际数据访问

Memory引擎

在内存中创建表。每个MEMORY表只实际对应一个磁盘文件(frm 表结构文件)。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。要记住，在用完表格之后就删除表格，不然一直占据内存空间。

特点

支持的数据类型有限制，比如：不支持TEXT和BLOB类型（长度不固定），对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；
支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；
由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；
查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；
默认使用hash索引。
如果一个内部表很大，会转化为磁盘表。

业务场景

那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地堆中间结果进行分析并得到最终的统计结果。
目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大的大小。
数据是临时的，而且必须立即可用得到，那么就可以放在内存中。
存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。

3️⃣索引结构

常见的索引结构

Mysql数据库中的常见索引结构有多种，常用Hash，B-树，B+树等数据结构来进行数据存储。树的深度加深一层，意味着多一次查询，对于数据库磁盘而言，就是多一次IO操作，导致查询效率低下。

前置：二叉搜索树

了解下二叉搜索树有助于我们理解B-树、B+树，二叉搜索树的特点是：

所有非叶子结点至多拥有两个儿子（Left和Right）；
.所有结点存储一个关键字；
非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；

以下都是二叉搜索树：

如果要找到65，左边的二叉树需要扫描3层（3次IO)，而右边的却需要6层。

B-Tree（B树）

B树是一种多路搜索树，一棵m阶的B树满足下列条件：

树中每个结点至多有m个孩子
根结点的儿子数为[2, M]；
除根结点以外的非叶子结点的儿子数为[M/2, M]；
每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
非叶子结点的关键字个数 = 指向子节点的指针个数-1；
非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
所有叶子结点位于同一层；

以下是3阶B树
240723184143542.png&pos_id=img-EVdELp3i-1722078145224)
磁盘读取数据是以盘块(block)为基本单位的。

B树的特征：

关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；
自动层次控制；

B树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

B+ Tree

B+树是B-树的变体，也是一种多路搜索树：

树中每个结点至多有m个孩子
根结点的儿子数为[2, M]；
除根结点以外的非叶子结点的儿子数为[M/2, M]；
每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
非叶子结点的子树指针与关键字个数相同；
非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树；（B树是开区间）；
为所有叶子结点增加一个链指针；
所有关键字都在叶子结点出现；

B+树的特征：

所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
不可能在非叶子结点命中；
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
更适合文件索引系统；

B+树的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；

为什么B+ 树比B 树更适合作为索引？

B+ 树的磁盘读写代价更低
B+ 树的数据都集中在叶子节点，分支节点只负责指针（索引）；B 树的分支节点既有指针也有数据。这将导致B+ 树的层高会小于B 树的层高，也就是说B+ 树平均的Io次数会小于B 树。
B+ 树的查询效率更加稳定
B+ 树的数据都存放在叶子节点，故任何关键字的查找必须走一条从根节点到叶子节点的路径。所有关键字的查询路径相同，每个数据查询效率相当。
B+树更便于遍历
由于B+树的数据都存储在叶子结点中，分支结点均为索引，遍历只需要扫描一遍叶子节点即可；B树因为其分支结点同样存储着数据，要找到具体的数据，需要进行一次中序遍历按序来搜索。
B+树更擅长范围查询
B+树叶子节点存放数据，数据是按顺序放置的双向链表。B树范围查询只能中序遍历。
B+ 树占用内存空间小
B+ 树索引节点没有数据，比较小。在内存有限的情况下，相比于B树索引可以加载更多B+ 树索引。

Hash

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。Memory存储引擎使用Hash。
Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。也不支持任何范围查询，例如WHERE price > 100。
　　
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。

从上面的图来看，B+树索引和哈希索引的明显区别是：

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；这有个前提，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
哈希索引也不支持多列联合索引的最左匹配规则；
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

InnoDB B+Tree结构来存储索引

InnoDB使用B+Tree数据结构存储索引，根据索引物理结构可将索引划分为聚簇索引和非聚簇索引（也可称辅助索引或二级索引）。一个表中只能存在一个聚簇索引（主键索引），但可以存在多个非聚簇索引。

B+树叶子节点包含数据表中行记录就是聚簇索引（索引和数据是一块的）。

B+树叶子节点没包含数据表中行记录就是非聚簇索引（索引和数据是分开的）。

B+ 树可以存储多少行数据

InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小默认是16K。

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
123456

磁盘扇区、文件系统、InnoDB存储引擎都有各自的最小存储单元

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。

如果数据库只按这样的方式存储，那么如何查找数据就成为一个问题？
因为我们不知道要查找的数据存在哪个页中，也不可能把所有的页遍历一遍，那样太慢了。

于是人们想到了用B+ 树的方式组织这些数据，下图以InnoDB为例。

pointer往往是6个字节，指明对应key值的页面位置信息。key一般为索引主键，如果为单字段 bigint 类型，则为8字节。如此可计算一个页大概可以存放16 * 1024/（6+8）=1170行数据。假设一行数据1k，那么2层B+ 树（第一层索引，第二层叶子节点存数据）就可以存储1170 * 16 = 18 720行；三层则可以存储1170 * 1170 * 16=21902400行。

MyISAM B+Tree结构来存储索引

MyISAM也使用B+Tree数据结构存储索引，但都是非聚簇索引。

以下是MyISAM主键索引存储图
可见，索引和数据是分开的索引的data部分只是索引的地址值。其实上文也提到过，.MYI就是MyISAM表的索引文件，MYD是MyISAM表的数据文件。
在这里插入图片描述

索引设计原则

查询更快、占用空间更小
1.适合索引的列是出现在此处子句中的列，或者连接子句中指定的列
2.基数较小的表，索引效果较差，没有必要在此列建立索引
3.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。
4.不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
5.定义有外键的数据列一定要建立索引。

不适合索引的

6.更新频繁字段不适合创建索引
7.若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)8.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
9,对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
10.对于定义为文本、图像和位的数据类型的列不要建立索引。

事务的基本特性和隔离级别

事务基本特性ACID分别是:
1、原子性指的是一个事务中的操作要么全部成功，要么全部失败。
一致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设A只有90块，支付之前我们数据库里的数据都是符合约束的,但是如果事务执行成功了,我们的数据库数据就破坏约束了,因此事务不能成功,这里我们说事务提供了一致性的保证
2、隔离性指的是一个事务的修改在最终提交前，对其他事务是不可见的。
3、持久性指的是一旦事务提交，所做的修改就会永久保存到数据库中。
4、隔离性有4个隔离级别，分别是

read uncommit 读未提交，可能会读到其他事务未提交的数据，也叫做脏读。
用户本来应该读取到id=1的用户age应该是10，结果读取到了其他事务还没有提交的事务，结果读取结果age=20，这就是脏读。

read commit 读已提交，两次读取结果不一致，叫做不可重复读。
不可重复读解决了脏读的问题，他只会读取已经提交的事务。
用户开启事务读取id=1用户，查询到aqe=10，再次读取发现结果=20，在同一个事务里同一个查询读取到不同的结果叫做不可重复读。

repeatable read 可重复复读，这是mysql的默认级别，就是每次读取结果都一样，但是有可能产生幻读。

serializable 串行，一般是不会使用的，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。

什么是MVCC

mvcc，也就是多版本并发控制，是为了在读取数据时不加锁来提高读取效率和并发性的一种手段。mvcc所提到的读是快照读，也就是普通的select语句。快照读在读写时不用加锁，不过可能会读到历史数据。指的就是在读已提交和可重复读隔离级别下的快照读这两种隔离级别的事务在执行普通的SELCI操作B访问记录的版本链的过程。可以使不同事务的读一写、写-读操作并发执行，从而提升系统性能。READ COMMITD、REPEATABLEREAD这两个隔离级别的一个很大不同就是:生成ReadVew的时机不同，READCOMMITID在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前变成个ReadView，之后的查询操作都重复使用这个ReadView就好了。

什么是readview呢？

当我们用select读取数据时，这一时刻的数据会有很多个版本，但我们并不知道读取哪个版本，这时就靠readview来对我们进行读取版本的限制，通过readview我们才知道自己能够读取哪个版本。

简述MyISAM和InnoDB的区别

MyISAMM:
·不支持事务，但是每次查询都是原子的;

支持表级锁，即每次操作是对整个表加锁;

·存储表的总行数;

一个MYISAM表有三个文件:索引文件、表结构文件、数据文件;

·采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

InnoDb:
·支持ACID的事务，支持事务的四种隔离级别:

支持行级锁及外键约束:因此可以支持写并发，

·不存储总行数;

一个innoDb引|擎存储在一个文件空间(共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里)，也有可能为多个(设置为独立表空，表大小受操作系统文件大小限制，一般为2G)，受操作系统文件大小的限制:

主键索引采用聚集索引(索引的数据域存储数据文件本身)，辅索引的数据域存储主键的值;因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

索引覆盖是什么

索引覆盖就是一个SQL在执行时，可以利用索引|来快速查找，并且此SQL所要查询的字段在当前索引对应的字段中都包含了，那么就表示此SQL走完索引后不用回表了，所需
要的字段都在当前索引的叶子节点上存在，可以直接作为结果返回了

最左前缀原则是什么

当一个SQL想要利用索引是，就一定要提供该索引所对应的字段中最左边的字段，也就是排在最前面的字段，比如针对a,b,c三个字段建立了一个联合索引，那么在写一个sq时就一定要提供a字段的条件，这样才能用到联合索引，这是由于在建立a,b,b三个字段的联合索引时，底层的B+树是按照a,b,c三个字段从左往右去比较大小进行排序的，所以如果想要利用B+树进行快速查找也得符合这个规则

Innodb是如何实现事务的

Innodb通过Buffer Pool，LogBuffer，RedoLog，Undo Log来实现事务，以一个update语句为例:
1.Innodb在收到一个update语句后，会先根据条件找到数据所在的页，并将该页缓存在Buffer Pool中
2.执行update语句，修改BufferPool中的数据，也就是内存中的数据
3.针对update语句生成一个RedoLog对象，并存入LogBuffer中
4.针对update语句生成undolog日志，用于事务回滚
5.如果事务提交，那么则把RedoLog对象进行持久化，后续还有其他机制将Bufer Pool中所修改的数据页持久化到磁盘中
6.如果事务回滚，则利用undolog日志进行回滚

B+树和B树的区别?为什么MYSQL要用B+树而不用B树:

首先B树的所有节点都存储数据信息，而B+树的所有数据都存储在叶子节点
B+ 树是在B树的基础上的一种优化，使其更加适合外存储索引结构，InnoDB存储引擎及时B+ 树实现其索引结构
从B树结构图中可以看到每个节点中不仅包含数据的Key值，还有data值，而每一页的存储空间是有限的，如果data数据较大时会导致每一个节点(也就是每一页)能存情的Key的数量很小，当存储的数据是很大时同时会导致树的深度很深，高度很高，增大磁盘的I0次数，讲而影响査询效率，在B+树中，所有数据节点都是按存储值大小有序存放在同一层的叶子节点上，而非叶子节点上只存储Key值信息，这样可以大大增加每个节点存储的key值数量，降低B+树的高度

Mysql锁有哪些，如何理解

按锁粒度分类:
1.行锁:锁某行数据，锁粒度最小，并发度高
2.表锁:锁整张表，锁粒度最大，并发度低
3.间隙锁:锁的是一个区间
还可以分为:
1.共享锁:也就是读锁，一个事务给某行数据加了读锁，其他事务也可以读，但是不能写2.排它锁:也就是写锁，一个事务给某行数据加了写锁，其他事务不能读，也不能写
还可以分为:
1.乐观锁:并不会真正的去锁某行记录，而是通过一个版本号来实现的
2.悲观锁:上面所的行锁、表锁等都是悲观锁
在事务的隔离级别实现中，就需要利用锁来解决幻读

讲而影响査询效率，在B+树中，所有数据节点都是按存储值大小有序存放在同一层的叶子节点上，而非叶子节点上只存储Key值信息，这样可以大大增加每个节点存储的key值数量，降低B+树的高度