InnoDB B+树索引特点

  • 每个索引都对应一颗B+树,B+树分为好多层,最下边一层是叶子节点 ,其余的都是内节点。所有用户记录都在B+树的叶子节点,所有目录项记录都存储在内节点。
  • 可以对感兴趣的列建立二级索引,二级索引的叶子节点包含的用户记录由索引列+主键组成,如果想通过二级索引来查找完整的用户记录的话,需要通过回表操作,也就是在通过二级索引找到主键值之后再到聚集索引中查找完整的用户记录。
  • B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表,而且每个页内的记录(不论是用户记录还是目录记录)都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引,则页面记录和记录先按照联合索引前边的列排序,如果该列值相同,再按照联合索引后边的列排序。
  • 通过索引查找记录都是从B+树的根节点开始,一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory(页目录),所以在这些页中的查找非常快。

索引的代价

索引虽然可以加快查询速度,但是不能乱建,否则会影响数据库的性能

空间上的代价

每建立一个索引都要为它建立一棵B+树,每一颗B+树的每一个节点都是一个数据页,一个页默认会占用16KB的存储空间。

时间上的代价

每次对表中的数据进行增、删、改操作时,都需要去修改各个B+树索引。B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录,还是内节点中的记录(也就是不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏,所以存储引擎需要额外的时间进行一些记录移位,页面分裂、页面回收等操作来维护好节点和记录的排序。

所以,一个表上索引建的越多,就会占用越多的存储空间,在增删改记录的时候性能就越差。

索引适用条件

全值匹配

如果我们的搜索条件中的列和索引列一致的话,这种情况称为全值匹配,比如说下边的查询语句

1
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

假设该表建立了对name,birthday,phone_number 3列 建立了名为 idx_name_birthday_phone_number 的索引

查询过程如下

  1. 因为B+树的数据页和记录先是按照name列的值进行排序的,所以先可以很快定位name列的值是Ashburn的记录位置
  2. 在name列相同的记录里又是按照birthday列的值进行排序,所以在name列的值的是Ashburn的记录里又可以快速定位birthday列的值是 '1990-09-27'的记录
  3. 如果name和birthday列的值都是相同的,那记录是按照phone_number列的值排序的,所以联合索引的3个列都可能被用到

匹配最左边的列

在查询语句中页可以不用包含全部联合索引中的列,只包含左边的列就可以,比如下方的查询语句

1
SELECT * FROM person_info WHERE name = 'Ashburn';

包含多个左边的列也可以使用索引

1
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';

那为什么查询条件必须出现左边的列才可以使用B+索引呢?比如下面的语句就用不到B+树索引

1
SELECT * FROM person_info WHERE birthday = '1990-09-27';

因为B+树的数据页和记录先是按照name列的值排序的,在name列的值相同的情况下才使用birthday列进行排序,也就是说name列的值不同的记录中的birthday值可能是无序的。所以索引的使用要包含左边连续的列

匹配列前缀

因为我们先前建立了idx_name_birthday_phone_number索引 ,所有的记录都是按照索引列的值从小到大的顺序排好序的,所以这极大的方便我们查找索引列的值在某个范围内的记录。比如说下边的查询语句:

1
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

由于B+树中的数据页和记录是先按name列排序的,所以我们上边的查询过程其实是这样的:

  1. 通过B+树在叶子节点中找到第一条name值大于Asa的二级索引记录,读取该记录的主键值进行回表操作,获得对应的聚簇索引记录后发送给客户端。
  2. 根据上一步找到的记录,沿着记录所在的链表向后查找(同一页面中的记录使用单向链表连接起来,数据页之间用双向链表连接起来)下一条二级索引记录,判断该记录是否符合name < 'Barlow'条件,如果符合,则进行回表操作后发送至客户端
  3. 重复上一步骤,直到某条二级索引记录不符合name <'Barlow'条件为止

不过在使用联合索引进行范围查找的时候需要注意,如果对于多个列同时进行范围查找的话,只有对索引列最左边的那个列进行范围查找的时候才能用到B+索引,比如:

1
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';

上边的查询可以分为两部分

  1. 通过条件 name > 'Asa' AND name < 'Barlow'来对name进行范围查询,查找的结果可能有多条name值不同的记录
  2. 对这些name值不同的记录继续通过 birthday > '1980-01-01'条件继续过滤

这样子对于联合索引 idx_name_birthday_phone_number来说,只能用到name列的部分,而用不到birthday列的部分,因为只有name值相同的情况下才能用birthday列的值进行排序,而这个查询中通过name进行范围查找的记录中可能并不是按照birthday列进行排序的,所以在搜索条件中继续以birthday列进行查找时是用不到这个B+树索引的