MySQL 存储结构page及索引相关

页结构

页(Page)是 Innodb 存储引擎用于管理数据的最小存储单位。常见的页类型有数据页、Redo页 、系统页、事务数据页、BLOB页等。

数据页的每个页由七部分组成,大致功能如下:

  • FileHeader 文件头:记录页的通用信息,比如上下页的页号( FIL_PAGE_PREV 和 FIL_PAGE_NEXT 字段,所有页通过两个字段可以形成一条双向链表,进行全表遍历),页类型等。
  • PageHeader 页头:记录本页存储记录的状态信息,比如本页记录数量,槽数量。
  • Infimum + supremum Records 最小与最大记录,Infimum(下确界)记录比该页中任何主键值都要小的值,Supremum (上确界)记录比该页中任何主键值都要大的值,构成了页中记录的边界。
  • User Records 真正存数据的地方:以链表的形式存储一条条行记录
  • Free Space 存数据空间中尚未使用的区域
  • Page Directory 页目录:页中某些记录的相对位置,用于提升查询效率
  • File Trailer 文件尾:刷盘时校验页是否完整

行记录

Innodb 存储引擎提供了两种格式的行记录:Compact 和 Redundant,默认为Compact。Redundant是为兼容之前版本而保留的。

compact

  • Compact行格式的首部是一个非NULL变长字段长度列表,按照列的顺序逆序放置。当列的长度小于255字节,用1字节表示,若大于255个字节,用2个字节表示,变长字段的长度最大不可以超过2个字节(即,MySQL中varchar的最大长度为65 535,即2^16=65 535)。
  • 第二个部分是NULL标志位,该位指示了该行数据中是否有NULL值,用1表示。
  • 接下去的部分是为记录头信息(record header),固定占用5个字节(40位),每位的含义见下表4-1。
  • 最后的部分就是实际存储的每个列的数据了,NULL不占该部分任何数据,不占有任何存储空间。
  • 除了这些还有两个隐藏列,事务ID列和回滚指针,分别为6个字节和7个字节的大小。若InnoDB表没有定义Primary Key,每行还会增加一个6字节的RowID列(用于作为聚簇索引的唯一索引)。

Redundant行记录格式

Redundant是MySQL 5.0版本之前InnoDB的行记录存储方式,MySQL 5.0支持Redundant是为兼容之前版本。

B+Tree索引树高度如何计算

InnoDB页的大小默认值是16k。

首先给出结果:一个千万量级,且存储引擎是MyISAM或者InnoDB的表,其索引树的高度在3~5之间

看看怎么实现的?

首先假设表的记录数是N,每个B+TREE节点平均有B个索引KEY(即1,2,3,4,5… …),那么B+TREE索引树的高度就是logBN(B为底,换底公式,等价于logN/logB)。

那么最终树的高度是由B和N决定的,而B是由InnoDB页的大小和索引KEY大小决定的。InnoDB页的大小默认为16k,而索引key的大小有字段的大小决定。这也就说明了索引key越小,那么B+TREE节点的B数量越大,索引树的高度logBN(B为底)也就越矮。

假设表3000W条记录(因为2^25=33554432),如果B+TREE每个节点保存64个索引KEY,那么索引的高度就是(log2^25)/log64≈ 25/6 ≈ 4.17。

所以,在InnoDB中,主键要设计的尽量,主键越小,二级索引也会越小。

聚簇索引和非聚簇索引(辅助索引、二级索引)

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式

InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分。

也就是说数据与索引存储在一起的是聚簇索引。

InnoDB的主键索引是聚簇索引,其他索引是非聚簇索引。

InnoDB中的非聚簇索引的B+树叶子节点存储的是索引key和主键值,需要经过聚簇索引来找到行记录。

MyISAM的所有索引都是非聚簇索引。主键索引也是一个普通的唯一索引

关于二级索引

Innodb中除了一个聚簇索引外都是非聚簇索引,也就是二级索引,其B+数的叶子节点中存储的是列值主键值,因此通过二级索引查询首先查到是主键值,然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块(回表)。

MyISAM的主键索引和二级索引叶子节点存放的都是列值与数据的物理地址

覆盖索引及覆盖查询

如果Select选中的字段不在索引中就需要回表查询,如果所需要查询的字段都在索引中,那么就是覆盖查询

全表扫描

通常对无索引的表进行查询一般称为全表扫描。但是有时候即使有索引也不会使用。

条件中使用了null

左模糊查询Like %XXX%

使用or作为连接条件

使用[not] in时

使用模糊查询时

使用!=或者<>

建议使用 <,<=,=,>,>=,between等; 

使用参数作为条件时

order by 或group by 时导致没有使用索引

导致全表查询,而且需要filesort

索引类型及其数据结构

MySQL索引类型:Unique,normal,spatial,fulltext。也就是唯一索引,普通索引,空间索引,全文索引。

Normal 普通索引

表示普通索引,大多数情况下都可以使用。

Unique 唯一索引

表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为unique

约束唯一标识数据库表中的每一条记录,Unique(要求列唯一)、Primary Key(primary key = unique + not null 列唯一)。也就是说Unique是可以有且只有一个Null的(mysql中)

Full Text 全文索引

表示全文收索,在检索长文本的时候,效果最好,短文本建议使用Index,但是在检索的时候数据量比较大的时候,现将数据放入一个没有全局索引的表中,然后在用Create Index创建的Full Text索引,要比先为一张表建立Full Text然后在写入数据要快的很多(每增加一条记录就要重写一条索引到索引文件)。

SPATIAL 空间索引

空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建

建立索引的数据结构由Hash 和 BTree。

关于Count

MySQL学习笔记:count(1)、count(*)、count(字段)的区别