MSQL系列(八) Mysql实战-SQL存储引擎

Mysql实战-SQL存储引擎

前面我们讲解了索引的存储结构，B+Tree的索引结构，我们一般都知道Mysql的存储引擎有两种，MyISAM和InnoDB,今天我们来详细讲解下Mysql的存储引擎

1.存储引擎

• 什么是存储引擎？
  MySQL中的数据用各种不同的技术存储在文件(或者内存)中。每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力
  存储引擎就是数据库底层的软件组织，数据库管理系统使用存储引擎来进行增删改查，不同的引擎底层采用不同的存储机制，索引机制，锁机制等等
• Mysql常用的存储引擎
  Mysql常用的存储引擎有两种, MyISAM和InnoDB

2.MyISAM的特点

• MyISAM 是MySQL最早的存储引擎
• MyISAM 支持表级锁定和高速读取
• MyISAM 既不支持事务、也不支持外键
• MyISAM 优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能
• MyISAM 它经常应用于只读或者以读为主的数据场景

MyISAM会把数据存储在磁盘上，分别存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：

• .frm(存储表定义)
• .MYD(MYData，存储数据)
• .MYI(MYIndex，存储索引)

MyISAM的索引的索引和数据是分开存储的，他的结构是B+Tree， 在Tree的叶子节点的数据区域存储的是实际数据的地址，指向实际的地址，这种索引就是非聚集索引

3. InnoDB的特点

1. InnoDB：MySql 5.6 版本默认的存储引擎
2. InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数
3. InnoDB InnoDB采用行级锁定，能够提高多用户并发数以及性能
4. InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销
5. InnoDB支持事务处理和外键约束等功能

InnoDB会把数据存储在磁盘上，分别存储成2个文件

• .frm(存储表定义)
• ibd(数据和索引文件)

它和MyISAM很大的区别就是InnoDB的数据文件本身就是索引文件，索引和数据是放在一起的，只有一个文件idb文件后缀结尾
InnoDB的索引结构也是B+Tree结构，但是Tree的叶子节点保存的就是完整的数据记录，索引的Key就是表的主键，数据文件本身就是主索引，这就要求表结构必须有主键，即使没有显式的设置主键也会默认生成一个隐式主键

数据和索引在一起，叶子节点包含了完整的数据记录，这种索引就叫做聚集索引

4.InnoDB和MyISAM的对比

对比	InnoDB引擎	MyISAM引擎
文件存储方式	.frm 后缀是表定义文件，ibd(数据和索引文件一起)	.frm后缀是表定义文件，.MYD是数据文件，.MYI是索引文件
索引方式	B+Tree	B+Tree
辅助索引data区域	Tree的数据区域data存储的是索引主键的值，所以需要靠辅助索引获得主键，然后用主键到主索引中检索获得记录，也就是回表	Tree的数据区域data存储的是索引地址
事务支持	支持事务ACID	不支持事务
锁机制	表级锁，行级锁	表级锁
count(*)查询	全表扫描	MyISAM表级锁直接存储了表行数，count(*)直接读取，无所扫描
读效率	效率低	效率高
写效率	效率高	效率低
常用场景	读写操作	读操作，读多写少

5.索引树层级

经常听到别人说Mysql的索引树一般会在3层，这个是有什么依据？为什么说Mysql的索引树一般都在1-3层的结构？

下面我们来具体的计算一下，看看3层结构可以支持多少数据

1. 我们可以根据B+树的原理进行一下数据推算，磁盘每页数据为4K
2. Mysql的B+树 对此又进行了一次调整，在Mysql也有自己的页概念，Mysql每一页数据等于磁盘4页的大小
3. Mysql里面的一页数据其实是16K，那么也就意味着Mysql里B+树的非叶子节点可存储16K的数据
4. 我们计算一个索引大小，Mysql规定一个索引占8B，索引和下一级索引之间的下一层地址空间占6B，也就是一个索引是 8+6 =14B
5. 我们按照每个非叶子节点的16K来计算，Mysql索引树每个节点能容纳(16*1024B)/14B=1170个索引key及指针
6. 叶子节点是存放数据节点的，假设每条数据的大小是1KB,那么每一个叶子节点都可以存放 16KB/1KB=16个数据
7. 假设我们现在有一个高度为3的B+Tree存满数据
8. 第一层 可以只放 索引+指针 1170个
9. 第二层同样是 1170个索引+指针
10. 第三层 可以存放16个数据，这里不是索引和指针了，是真实数据

那么三层的树，我们可以存放 1170117016 = 21,902,400 =2KW 两千万条数据，只需要3次I/O操作，我们就可以精确定位数据
所以我们的表数据一般而言都保持在千万级以内，索引树都会保持在3层之内，因此Mysql的索引树一般都在1-3层。

---

至此，我们彻底的了解了mysql存储引擎的分类及区别，也深入探讨了索引树一般会在3层的底层逻辑