原来知道有一些索引失效的条件,最近看了看mysql底层数据结构,明白了为什么会失效 ,记录之。众所周知,常用的mysql数据引擎有两种,今天全是以InnoDB为基础开启探索之旅的,另一种有时间再说吧。
数据页与数据行
我们都知道,数据库数据是存在磁盘中的,不过真正处理数据是在内存中进行的。这就需要从硬盘上不断地把数据读到内存中,由于内存和磁盘速度差了好几个数量级,所以为了避免频繁交互带来的性能问题,mysql一次会多读取一些,是多少呢?读一页。一页有16KB,也就是说一次读取一般都是16KB的倍数。页是硬盘内存交互的基本单位。
我们平时所说的一条记录叫数据行,InnoDB有四种不同类型的数据行,Compact、Redundant、Dynamic和Compressed。主要介绍下Compact
为了方便后面说明,建个表:
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` smallint(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8;
变长字段长度列表:类似于varchar() 这种可变长度,记录某个属性的长度,方便快速读取某属性的值。长度值是倒叙存放的。
Null值列表:记录可为空的那些值,是否为空。
为了方便说明举例:
id name age
99 haha (null)
1 表示 这个属性为null,0表示这个属性不为null。所以name对应着0,age对应着1。由于是倒叙存放的,所以 Null值列表 这个地方存放的是 10(age,name);
记录头信息:
数据有很多,关键的:
delete_mask:标记该记录是否被删除
record_type:表示当前记录的类型,0表示普通记录,1表示B+树非叶子节点记录,2表示最小记录,3表示最大记录。
接下来就是真实数据了,值得一提的是,还有三个隐藏项:
row_id:如果没有主键ID,数据库会自动生成一个行的标识ID。所以这个值是可选的。
transaction_id:事务ID
roll_pointer:回滚指针。
页行关系
如上图所示,是一张数据页内部结构。一个16KB的页,内部存放着很多行,比如说那3条记录,除此之外,内部存放着两个特殊的记录,最小记录和最大记录。数据页内部记录之间是以单链表的形式存放的,头尾分别是那两个特殊的记录。在内存中有很多页,页和页之间是用双链表连接的。这样方便快速定位到数据在哪一页上。
B+ 索引
聚簇索引
ok,现在知道了数据页、数据行,和它们之间的数据结构之后,就可以看看我们所谓的索引了。正如开头所说的,这边只介绍InnoDB的聚簇索引,另一种搜索引擎,先不提它(嗯,现在甚至连名字都不提)。
聚簇索引,就是说有一颗树,叶子节点就是真实数据行所构成的数据页。
一般为了搜索快一点,我们主键都是自动生成的(例如咱们的User表),所以最下面那层是根据id排序生成的。最底下那层的叶子节点是真实的数据,有4页,每页里面有一个单链表,就是我们的真实数据行。第二行有两页,每页中也是有个数据行构成的单链表,这是的数据行只包含了页码(最底下那层某页)、某页最大id,由此可见,第二行比最底下那行页数少了很多很多。就这样,一层一层的抽取,一定会有一个所谓的跟页。我们搜索数据就是从跟页开始的,一层一层往下找的。由于一个数据页可以存放16KB数据,所以三四层的树状图就已经能存放很多很多数据了,所以不要担心树会很深。再强调一下,页内是单链表,同层的页和页之间是双链表。
二级索引
上面那是以主键为搜索条件的索引,一般这棵树是自动生成的。
我们往往还会自己建立索引,比如给age添加索引。与聚簇索引类似,只不过叶子节点存的不是所有数据(并且根据age大小排序),而是存的该age属性和主键id,非叶子节点寸的是页码和下面那层某页最大的age值。这样,你确定了要搜的是哪些主键,还要回表(拿着这些主键回去聚簇索引找)去查询真实的数据。这边脑洞一下,即使你给age创建了索引,真正执行的时候,也不一定是通过查看二级索引,再回表的方式查数据(比如说通过二级索引搜索出来的是所有的id,再回表查询,得不偿失啊,还不如直接从聚簇索引直接去搜呢)。也可能根据聚簇索引直接搜索。具体采用哪种方式mysql自己会评估。
联合索引
还有种特殊的二级索引,联合索引,比如说给(name、age)添加联合索引,底层数据结构和普通二级索引没什么区别,只不过叶子节点存的不是所有数据(并且先根据name大小排序,name相同的情况下再根据age排序),而是存的该name、age属性和主键id,非叶子节点寸的是页码和下面那层某页最大的name值。所以如果搜索条件只有age,没有name的话,联合索引会失效,所以要遵循最左原则。