【MySQL】索引（一）

一、磁盘

1、物理结构

• 磁盘通常由一个或多个坚硬的盘片组成，盘片表面涂有磁性材料用于记录信息。每个盘片都有上下两面，都可用来记录数据。磁盘还包括磁头、电动机、主控芯片与排线等部件。
• 磁头：每个盘片的每一面都对应一个读写磁头，用于在盘片上进行数据的读写操作。磁头悬浮在盘片上方几微米的距离，可以读取和写入数据。
• 磁道：盘片在格式化时会被划分成许多同心圆轨迹，这些同心圆称为磁道。磁道从盘片的最外圈开始向内依次编号，最外圈的磁道编号为0。
• 扇区：将每个磁道等分成若干个扇形的区域，每个区域称为一个扇区。扇区是磁盘存储数据的最小单位，通常每个扇区可存储512字节的数据（现在也有一些磁盘采用了更大的扇区尺寸，如4KB等）。
• 柱面：不同盘片上相同半径的磁道所组成的圆柱称为柱面。柱面的概念在磁盘数据存储和寻址中具有重要意义，它可以简化数据的定位和访问操作。

2、示意图

3、定位扇区

• CHS定位法：CHS定位法通过指定柱面（Cylinder）号、磁头（Head）号和扇区（Sector）号来定位磁盘上的一个扇区。这种定位方法适用于早期的硬盘，但随着硬盘容量的增加和物理结构的复杂性，CHS方法逐渐显得不再适用。
• LBA定位法：LBA（Logic Block Address）是操作系统内部使用的逻辑块地址，它简化了磁盘的寻址方式。操作系统为磁盘的每个扇区划分了逻辑地址，这些地址在操作系统内部是连续的。当需要访问某个扇区时，操作系统只需将逻辑地址转换为对应的磁盘物理地址（即CHS地址）即可。
• 相比于CHS定位法，LBA定位法通过逻辑地址来定位扇区，提高了磁盘的寻址效率和灵活性。

4、读写操作的基本方式

• 随机访问（Random Access）：在磁盘读写操作中，本次IO所给出的扇区地址与上次IO给出的扇区地址不连续，磁头需要在两次IO操作之间做较大的移动才能重新开始读/写数据。
• 连续访问（Sequential Access）：在磁盘读写操作中，当次IO给出的扇区地址与上次IO结束的扇区地址连续或接近，磁头能够快速地开始下一次IO操作。
• 性能差异：随机访问由于磁头需要频繁移动，因此读写速度相对较慢；连续访问由于磁头无需频繁移动，因此读写速度相对较快。

二、页

1、介绍

• MySQL与磁盘交互的基本单位是页（Page），这一点在MySQL的InnoDB存储引擎中尤为明显。页代表了每次从磁盘读取或写入到内存的数据量。
• InnoDB存储引擎中，页的默认大小是16KB。但可以通过系统变量innodb_page_size进行调整，不过调整时需要保证设置的值是操作系统“数据块”4KB的整数倍。而这意味着每次从磁盘读取或写入到内存的数据量都是16KB（页大小）。

2、示例

• MySQL 与磁盘交互基本单位

show global status like 'innodb_page_size';

3、作用与结构

• 作用：页作为磁盘和内存之间交互的基本单位，有助于优化磁盘IO效率。通过以页为单位进行数据的读写操作，可以减少磁盘IO的次数，从而提高整体性能。
• 页的头部（Page Header）记录了页的状态信息，如页的编号、类型、上一页和下一页的页号等。
• 页的尾部（File Trailer）则用于检测数据完整性的校验。
• 页的中间部分（User Records）存放了实际的数据行记录。
• 一个页内部通常包含多个行记录，即数据库中的多条数据。
• 此外，为了加快数据查找速度，页内部还会生成一个页目录（Page Directory），用于记录与二叉查找相关的信息。

4、类型

（1）数据页

• 数据页（Data Page）也称为索引页，因为InnoDB中“索引即数据”。主要用于存储表中的数据行记录，包含页头（Page Header）、页尾（File Trailer）、用户记录（User Records）、空闲空间（Free Space）和页目录（Page Directory）等部分。
• 页头包含页的元数据信息，如页号、页类型、校验和等。
• 页尾用于检测数据完整性的校验。
• 用户记录区存储实际的数据行，数据行之间通过链表连接。
• 页目录用于提高数据查找效率，通过二分查找快速定位到数据行。

（2）其他

类型	作用
系统页（System Page）	存储系统级别的元数据信息
Undo页（Undo Log Page）	存储事务的回滚信息，用于支持事务的原子性和一致性
事务数据页	存储与事务相关的其他数据
Change Buffer页	用于存储对二级索引的修改操作，以延迟写回磁盘，提高写入性能
Extent Descriptor（XDES）页	存储区的描述信息，一个区包含多个连续的页
InnoDB段信息页	存储段的信息，段是区的上级结构，由一个或多个区组成

5、组织与管理

• 页链表：多个页通过双向指针连接在一起，形成双向链表。即使页在物理空间上不连续，也可以通过链表在逻辑上保持连续。
• 页分裂与合并：当一个页中的数据量过多导致空间不足时，MySQL会进行页分裂操作，将部分数据迁移到新的页中。相反，当一个页中的数据量减少导致空闲空间过大时，MySQL会进行页合并操作，将相邻的页合并为一个大的页。
• 缓存管理：MySQL使用Buffer Pool来缓存数据页和索引页，以减少磁盘IO操作。当需要访问某个页时，可以先从Buffer Pool中查找，如果找到了就可以直接在内存中进行操作。

6、性能优化

• 调整页大小：根据实际情况和数据量的大小，选择合适的数据页大小。较小的数据页可以提高磁盘利用率，但可能会增加随机IO操作；较大的数据页可以加快顺序IO操作，但可能会浪费空间。
• 优化索引：定期进行索引重建，避免索引的碎片化，从而提高索引的查询性能。
• 合理配置缓存：通过合理配置Buffer Pool等缓存参数，可以提高数据页的访问速度和性能。

7、示意图（B+树）

• 上方为MySQL中innode db存储引擎下的索引结构，一般建表插入数据的时候，是在该结构下进行CURD操作。当表没有主键时也是如此，因为会有默认主键存在。
• 叶子节点保存有数据，而路上节点没有。即非叶子节点不需要数据，只需要目录项。因此，非叶子节点不存数据就可以存储更多的目录项，而存储目录项可以管理更多的叶子page。
• 这种方式下的树是一个“矮胖型”的树，而途径的路上节点减少，找到目标数据只需要更少的page，IO次数更少，在IO层面上提高了效率。即每一个节点都有目录项，可以大大提高搜索效率。
• 叶子节点全部用链表级联起来是B+树的特点，而用户也比较希望进行范围查找。

三、索引

1、作用

• 提高查询速度：索引可以显著加快数据检索速度，因为数据库系统可以利用索引快速定位到表中的数据行，而无需扫描整个表。
• 提高排序和分组效率：索引可以帮助数据库系统更快地执行排序和分组操作，因为这些操作通常需要对数据进行扫描和比较。
• 优化连接操作：在多表连接查询中，索引可以加速表之间的连接过程，因为数据库系统可以利用索引快速找到匹配的记录。

2、注意事项

• 选择合适的列：通常，在经常用于查询条件、连接条件、排序和分组操作的列上创建索引。避免在很少使用或数据重复率高的列上创建索引。
• 索引的维护成本：索引会占用额外的存储空间，并且在插入、更新和删除操作时需要维护索引，这会增加额外的开销。因此，需要根据查询需求和表的大小来权衡是否创建索引。
• 避免冗余索引：不要在同一个表上创建多个冗余的索引，以避免增加维护成本。
• 使用索引的提示：在查询时，可以使用索引提示（如USE INDEX、IGNORE INDEX等）来指导MySQL优化器使用或忽略特定的索引。
• 监控和优化索引：定期监控索引的使用情况和性能，并根据需要进行优化。例如，可以使用EXPLAIN语句来分析查询的执行计划，以确定是否使用了索引以及索引的使用是否有效。

四、非聚簇索引（Non-Clustered Index）

1、介绍

• 非聚簇索引不决定表中数据的物理存储顺序，它只是在数据行之外，为表中的一列或多列创建一个单独的索引结构。
• 非聚簇索引适用于需要频繁进行精确匹配查询（如=、IN等）的列。也适用于需要快速访问表中特定行的场景，但不需要频繁进行范围查询、排序或分组操作。

2、特点

• 一个表可以有多个非聚簇索引。
• 非聚簇索引的叶节点包含了指向实际数据行的指针（或键的副本和指向数据行的行标识符），即当通过非聚簇索引查询数据时，需要先找到索引的叶节点，然后通过指针或行标识符再找到对应的数据行。
• 插入、删除和更新操作通常比聚簇索引更快，因为不需要维护数据的物理顺序。
• 查询性能可能不如聚簇索引，特别是当索引列的选择性较低（即索引列中的重复值较多）时。

3、MyISAM

（1）示例

create database myisam_test;
use myisam_test;create table mitest(
id int primary key,
name varchar(20) not null
)engine=MyISAM;show create table mitest\G

• Linux命令行操作

cd /var/lib/mysql
cd myisam_test/
ll

（2）说明

• .myd文件：是MyISAM存储引擎中用于存储表的实际数据的文件。每个MyISAM表都有一个与之对应的.myd文件，该文件包含了表中所有的数据记录。
• .myd文件是数据存储的核心。如果此文件损坏或丢失，那么表中存储的所有数据将无法恢复，这将对数据库的完整性和可用性造成直接影响。
• .myi文件：是MyISAM存储引擎中用于存储表的索引数据的文件。索引是为了加速数据的查询速度而设计的，它包含了表中所有列的索引信息，包括B树索引和其他类型的索引。通过索引，数据库系统可以更快地定位到所需的数据行，从而提高查询效率。
• .myi文件对于支持快速数据检索至关重要。如果缺失了索引文件，那么数据库系统在执行查询操作时可能需要扫描整个表来查找所需的数据，这将导致查询速度变慢，影响数据库的整体性能。

4、示意图

• MyISAM 表的主索引，Col1 为主键。
  

五、聚簇索引（Clustered Index）

1、介绍

• 聚簇索引决定了表中数据的物理存储顺序，即表中的数据行实际上是按照聚簇索引的键顺序来存储的。
• 聚簇索引适用于需要频繁进行范围查询、排序或分组操作的表。也适用于主键或唯一键，因为这些键通常用于唯一标识表中的每一行。

2、特点

• 一个表只能有一个聚簇索引，因为数据行本身只能按照一种顺序物理存储。
• 聚簇索引的叶节点包含了实际的数据行，即当通过聚簇索引查询数据时，可以直接找到对应的数据行，而不需要额外的查找操作。
• 由于数据行是按照聚簇索引排序的，因此范围查询（如BETWEEN、<、>等）和排序操作（如ORDER BY）的性能通常会更好。
• 相比于非聚簇索引，插入、删除和更新操作可能会更慢，因为需要维护数据的物理顺序。

3、innodb

（1）示例

create database innodb_test;use innodb_test;
create table ibtest(
id int primary key,
name varchar(20) not null
)engine=InnoDB;

ll ./innodb_test/

（2）功能与作用

• 存储表数据和索引：.ibd文件是InnoDB存储引擎生成的独立表空间文件，它主要用于存储表的数据和索引。每个InnoDB表都有一个与之对应的.ibd文件，该文件包含了表中所有的数据页和索引页。数据页用于存放表中实际的行数据，而索引页则用于存放表的索引信息，以便快速查询。
• 数据独立性：由于每个表都有独立的.ibd文件，这使得数据的备份和恢复变得更加简单。管理员可以单独备份或恢复某个表的.ibd文件，而无需影响其他表的数据。
• 空间利用与扩展性：.ibd文件支持表空间的动态扩展和收缩。管理员可以根据表的需求灵活调整表空间大小，从而最大化地利用存储空间。此外，InnoDB还支持对.ibd文件进行压缩操作，以减少表空间的大小并节省磁盘空间。

4、示意图

• 在InnoDB下，以非聚簇索引中示意图的 Col3 建立辅助（普通）索引。
  
  后续内容参见【MySQL】索引（二）

本文到这里就结束了，如有错误或者不清楚的地方欢迎评论或者私信
本文只是在学习过程中所做的总结，不会涉及过深的概念
创作不易，如果觉得博主写得不错，请点赞、收藏加关注支持一下💕💕💕