MySQL索引使用方式以及段、区、页概念

2023年5月10日 127点热度 0人点赞 0条评论

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 = ''; # 不需要回表直接覆盖索引。

索引创建注意事项

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

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

InnoDB 表空间

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

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

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

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

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

区概念

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

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

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

XDES类型的页面。用来登记该组256个区的属性。
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虽然有点区别就是非碎片区的，不过是申请的专属的区，所以流程是差不多的。