innodb

MySQL 5.7
innodb存储引擎默认表有 .frm .ibd 两个文件
.frm 是每张表的表结构，不存储数据
.ibd 叫做独立表空间,（mysql5.6.6之后）默认存贮每张表的数据
innodb在数据目录下还有一个ibdata1 文件，大小啊为 12M 叫做系统表空间，来存储数据库的数据

  1. //my.cnf
  2. [server]
  3. innodb_filr_per_table=0  #0 系统表空间   1 独立表空间

每个单独的库里面还会有一个 db.opt，记录该库的默认字符集编码和字符集排序规则用的
MySQL 8.0
innodb存储引擎默认表只有 .ibd 一个文件
这一个文件同时存储了表结构和数据

myisam

MySQL 5.7
myisam 有三个文件 .MYD .MYI .frm
.MYD 存储数据 .MYI 存储索引 .frm 存储结构
MySQL 8.0
myisam 有三个文件 .MYD .MYI .sdi
还会有一个 .sdi 文件 相当于 .frm
对比 innodb 和 myisam
基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行速度比 InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。
1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
3. InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；
5. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了
6. MyISAM表格可以被压缩后进行查询操作
7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
8、InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键），而Myisam可以没有
9、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
Innodb：frm是表定义文件，ibd是数据文件
Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
image.png
[

](https://blog.csdn.net/qq_18671415/article/details/108713040)

索引设计

聚簇索引

索引及数据，数据及索引

  1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
    页内 的记录是按照主键的大小顺序排成一个 单向链表 。
    各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。
    存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表 。
  2. B+树的 叶子节点 存储的是完整的用户记录。
    所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

image.png
这种聚簇索引不需要手动创建，innodb会自动创建
优点：
数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非
聚簇索引更快
聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多
个数据块中提取数据，所以 节省了大量的io操作 。
缺点：
插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影
响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为
不可更新
二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据

二级索引

二级索引又叫 辅助索引、非聚簇索引
不使用主键单独建立的索引，根据索引列的数据进行顺序排列构建，叶子结点的值是主键id，根据这个列找到主键之后还需要使用聚簇索引进行所搜，这个过程叫做 ·回表

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照 c2和c3列 的大小进行排序，这个包含两层含义：
先把各个记录和页按照c2列进行排序。
在记录的c2列相同的情况下，采用c3列进行排序
注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：
建立 联合索引 只会建立1棵B+树。
为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

innodb 的B+树索引

  1. 更页面的位置不动

当创建一颗B+树时，根结点指针的位置就确定了，不再变化，如果当前的根页存满数据，要建立新的根时，会将当前根的数据拷贝出去，将新的数据存入当前节点

  1. 内节点中目录项记录的唯一性

image.png
当二级索引不是唯一的时会不知道新的数据有添加到那个叶子节点页块，应该把非叶子节点（内节点）变成唯一的 ==> 使用主键和二级索引列共同构成内节点
3. 一个页面最少存储2条记录

myisam 的索引方案

MyISAM引擎使用 B+Tree 作为索引结构，叶子节点的data域存放的是 数据记录的地址 。
myisam 的数据和索引是分开存储的，数据在.MYD 文件 索引在 .MYI 文件
image.png
MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。
① 在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。
② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数 据记录的地址。
③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址 。换句话说， InnoDB的所有非聚簇索引都引用主键作为data域。
④ MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通 过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
⑤ InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个 可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐 含字段作为主键，这个字段长度为6个字节，类型为长整型。

innodb 的数据存储格式

磁盘和内存的交互单位：页

innodb 将数据分为若干个页，每个页大小默认16KB
以页作为磁盘和内存交互的基本单位，也就是说一次最少从磁盘中读取16KB的数据，在数据库中，无论是读一行，还是读多行，都是将这些行所在的页进行加载，数据库管理存储空间的单位是页，数据库 I/O 操作的最小单位是页，页和页之间使用双向链表连接
image.png

页的上级结构

image.png
区：比页大一级的存储结构，一个区有64个连续的页，一个区大小默认（1MB）
段：由一个或多个区组成，区在文件系统中是一个连续分配的空间，不过段中不要求区和区是相连的
段是数据库的分配单位，不同类型的数据库对象分配不同的段，比如创建一张表==>表段，创建一个索引==> 索引段
表空间：逻辑容器，表空间是由段组成的，一个表空间由一个或多个段

页的内部结构

页按照类型划分常见的有 数据页（B+tree 的节点），系统页，Undo页，事务数据页
image.png
image.png