MySQL深入学习--day3

B+树索引的正确使用

索引并不是越多越好，索引创建越多，MySQL维护的代价越高，如果SQL未能完全使用到索引，创建索引的意义是不大的。

适用条件

表x，创建索引a,b,c。主键y。

全值匹配

select * from x where a = '' and b = '' and c = ''

当我们创建的索引abc，此时我们有a，b，c字段的索引是可以匹配到的，不论你a,b,c字段顺序如何，优化器会自动优化为索引的顺序。

匹配左边的列

select * from x where a = '' and d = ''

我们在此情况可以用到a的索引，但是如果第一个为b或c字段就不行。

匹配列前缀

select * from x where a like 'aaa%' and b like 'bbb%' # 不行'%aaa'或'%aaa%'

我们可以利用建立的索引找到a和b字段，因为a索引和b索引按照前缀排序的。但是反过来不行

匹配范围值

select * from x where a between 5 and 10;

因为是索引按照大小排序的，所以可以使用到索引。但是我们不用a直接用b是不能用到索引的。

精准匹配到某一列并范围匹配到另外一列

select * from x where a = 'aaa' and b between 5 and 10;

当我们是这种情况会找到a，然后根据b的排序找到b的范围值，是可以用到索引的。

用于排序

select * from x order by a,b,c; # 可以使用索引
select * from x order by b,a,c; # 不能使用索引

创建了a,b,c的索引可以根据a,b,c 排序，否则不能使用。

用于分组

select * from x group by a,b # 可以使用索引，顺序不对可以，会自动优化，但是得从左边开始

回表代价

二级索引最后的最后找到主键值需要拿着主键值去聚簇索引进行回表查询。

我们创建索引时可以尽量避免回表的出现，尽量使用索引的字段，否则回表会导致MySQL的性能下降。当然mysql对于大量数据需要回表的情况会直接优化成顺序查找，省的大量回表带来的开销。

这也是为什么我们不要用select * 的原因，如果我们只需要索引字段就select对应字段即可。当所需字段在索引中存在，会进行覆盖索引作为结果返回，不需要回表查值。

select * from x where a = '' and b = '' and c = ''; # 如果数据库中有其他字段除了abc和主键y。
select a,b,c,y from x where a = '' and b = '' and c = ''; # 不需要回表直接覆盖索引。

索引创建注意事项

1. 不需要对查询字段创建索引，只需要对搜索、排序、分组的字段进行即可。
2. 列的基数尽量大，基础小，即列的重复值较少的列创建索引
3. 索引列的类型能小尽量小，int能用tinyint就用。
4. 索引字符串的前缀，如果只需要前缀创建索引，但是如果前缀重复多可能会出现问题。
5. 让索引列在比较表达式中占独立的一部分。where a * 2 > 6 是用不了索引的，where a > 6 /2 可以用索引。
6. 主键插入顺序，如果主键插入不按顺序，是需要页分裂等操作的，所以建议主键自增。
7. 重复索引。索引重复只会更多的MySQL性能开销，且毫无意义。

MySQL的数据目录

数据存放目录，与安装目录区分开

mysql> show variables like 'datadir';
+---------------+------------------------------------+
| Variable_name | Value                              |
+---------------+------------------------------------+
| datadir       | D:\mysql\mysql-8.0.22-winx64\data\ |
+---------------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

数据库在文件中就是表现为存放目录下的一个与数据库同名的文件夹，系统数据库会直接存放在数据存放目录下。

表在文件系统中的表示 InnoDB存储表数据

描述表结构的文件：表名.frm

描述表数据和索引的文件：表名.ibd

系统表空间：即数据存放目录下的一个12M的文件，如果系统中数据库数据多，会更大。即系统数据库文件ibdata1文件。

独立表空间：在数据存放目录下数据库名的子目录里面，表名.frm 和 表名.ibd 。不过现在8.0.22已经只有表名.ibd了。

MyISAM存储表数据

描述表结构的文件：表名.frm

描述表数据的文件：表名.MYD

描述表索引的文件：表名.MYI

独立表空间就是由这三个文件组成。

其他文件

服务器进程文件、日志文件、SSL和RSA证书和密钥。

MySQL系统数据库

• mysql

存放用户账号和权限，一些存储过程、事件定义信息、一些运行时日志，帮助信息，时区信息。

• information_schema

维护服务器有哪些表，哪些视图，哪些触发器，哪些列，哪些索引

• performance_schema

维护服务器运行的状态信息，对MySQL的监控

• sys

通过视图的形式把前两个表结合起来，让程序员监控MySQL。

InnoDB 表空间

我们提到了行格式、页这两个概念。

行格式规定了每条数据，多条数据形成组，多个组存放在一个页中。

如果我们需要管理页的话，我们就需要区和段这个概念。

一个16KB的页来说，连续64个页就是一个区，也就是说一个区的大小为1MB。

连续256个区，就形成了一个组，一个组256MB。

区概念

对于每个表空间的第一个组来说，这个组第一个区前三个页面是不一样的。

1. FSP_HDR类型的页面。用来登记该组256个区的属性，但是还会存储表的基本属性。
2. IBUF_BITMAP类型的页。存储INSERT_BUFFER
3. INODE类型的页。存储INODE entry。

其余组的第一个区就是最先两个页面不一样。

1. XDES类型的页面。用来登记该组256个区的属性。
2. IBUF_BITMAP类型的页面

提问：为什么要使用区来管理？

因为对于页来说没有固定的存储地点，所以页是随意存储的，但是如果数据量已经很大的情况下，我们插入了一个很小的主键值，会建立一个物理存储位置在很后面的页，但是页会被插入到很前面，我们读页信息的时候，就会出现什么情况呢？

就是我们需要IO读取到最后，然后在回到当前继续读，是十分耗时的，也就是随机IO，与顺序IO性能差得多。

段概念

第一遍看到这个概念直接被搞蒙了。

InnoDB 中叶子节点存放的区和非叶子节点存放的区是分开的，这就是段的概念。一个为存放叶子节点区的段，和存放索引页区的段。

所以捋一下。每个聚簇索引会有两个段，一个段表示存放叶子页的区，一个段表示存放非叶子页的区。

那按照这样的话，一个表开局就要2M的存储空间，对于几条数据的是不是太大了。

所以出现碎片页的概念，一个区不属于某个段，而是直接属于表空间。它可以存储各个段的页，防止区的浪费。当一个段已经存储了32个碎片区，剩下就会直接创建附属的空闲区来存储页，而不是使用碎片页。

所以区有如下状态：

• 空闲区(FREE)、
• 有剩余空间的碎片区(FREE_FRAG)、
• 满的碎片区(FULL_FRAG)、
• 附属于某个段的空闲区(FSEG)。

对于每个区来说都有一个XDES Entry的结构。

• Segment ID （8字节）：如果状态为FSEG的话就是段的ID。否则没有意义
• List Node（12字节）：用来存储前一个和后一个区的地址
• State（4字节）：就是上述四种状态。
• Page State Bitmap（16字节）： 描述当前64个页，每个页2比特，一比特表示是否空闲，还有一个比特没什么用。

寻找最近的有空间的或空闲区

当段中存储的区小于32时，是会利用隶属于表空间的碎片区进行存储的。

流程：

• 新插入的页寻找空闲区进行存储，如何快速寻找到表空间的空闲碎片区呢？
  • 表空间会维护一个FREE状态的链表和FREE_FREG状态的链表以及FULL_FRAG状态的链表。
  • 如果空闲的碎片区还存在就会找出链表中取出一个插入，如果满了就改变其状态将其放入FULL_FRAG的链表中。
  • 如果没有空闲的碎片区，就会从FREE中取出一个来将其转变为空闲碎片区状态放入FREE_FREG状态的链表中。

当段中的碎片区存储超过32时，就会申请隶属于该段空间的区进行存储。

流程和之前差不多，但是段空间也会维护三个链表FREE和FULL以及NOT_FULL虽然有点区别就是非碎片区的，不过是申请的专属的区，所以流程是差不多的。

段的结构

前面我们不是提到了段并不是一个实际的存储单元，只是区的引用。

所以需要有一个结构来定义段，就是INODE Entry 结构

• Segment ID ：就是段的唯一ID
• NOT_FULL_N_USED：表示已经使用的页的个数，然后下次直接分配直接找到。
• 三个链表：很熟悉，就是表示空闲，没满，和满了的隶属于该段的区的链表。只会在碎片区分配满32个的时候才会进行分配。
• Magic Number魔数
• 碎片区的引用刚好32个。

所以在段中，碎片区的引用是在最底下，而专属区的引用是在链表中链着的。

所以你废了吗？

接下来我们可以讲解一下INODE Entry放在哪里呢，就需要介绍之前提到过的每个表空间的第一个区中固定的三个页面，只介绍俩页面

FSP_HDR页面和XDES页面

FSP_HDR类型的页面，就是比其他的区的第一个XDES页多了File Space Header就是记录当前表空间的一些属性，其他都是一样的。

• File Header就是头中的一些信息还有和File Trailer的校验
• File Space Header

Space ID 表示表空间的ID

Size 表空间页的大小

Free Limit 就是当前已经使用的页到多少了，下次直接从这个地址开始分配页面

FRAG_N_USED 表示碎片区已经使用的页

接下来的for FREE List 和for FREE_FRAG List和for FULL_FRAG List 表示表空间维护的三个有关碎片区的链表

Next Unuser Segment ID 表示下一个未分配的段ID，方便分配一个新的段ID

for SEG_INODES_FULL 和 for SEG_INODES_FREE 表示已经放满了INODE Entry 的INODE节点和空闲的INODE节点。（记住是存放INODE Entry也就是段结构的INODE节点）

INODE页面

INODE类型结构就是为了存储INODE Entry节点的，最多存储85个段。

结构中List Node for INODE Page List 就是指向上一个INODE节点和下一个INODE节点。

我们就是在这个INODE中存储段的INODE Entry节点的。

如果该页存储满了，就会在上面提到的List Base Node for SEG_INODES_FREE 就是空闲INODE页的基节点的链表引用，取出一个，空的话从碎片区中申请一个页来存放。

所以我们知道了段是怎么存储的，存储在哪里。

同时呢，我们已经知道一个索引会有两个段，一个叶子段，一个非叶子段。

我们怎么找到索引的页呢？

Segment Header结构

在这个结构之前，我们在数据页是提到过两个引用，但是没有具体介绍

在页结构的Page Header中有如下两个结构

这两个结构就是Segment Header这个结构

Space ID of the INODE Entry 就是INODE对应的表空间

Page Number of the INODE Entry 就是INODE对应的表空间下对应的页号

Byte Offset of the INODE Entry 就是INODE对应的页中对应段的偏移量。

我们就可以通过在索引的ROOT节点存储一个这样的结构，可以找到对应的段。包括叶子段和非叶子段，就是两个这个结构，然后去表空间中找到这两个段的地址即可。

系统表空间

介绍一个概念，数据字典即系统表空间中存放了一些固定的数据，以及数据库中的表，表名，列，列属于那个表等等基本信息。

还有一些已经用了的最大的表ID，最大的索引ID，最大的表空间ID，就是方便下次创建表啊索引啊这一些更方便一点，直接将值进行增加等操作进行赋值即可。

图片出自：《MySQL是怎样运行的：从根儿上理解MySQL》
对其进行总结概括，以及思路重新捋一遍。