一、索引的介绍
1、聚簇索引
B+树本身就是一个目录,或者说本身就是一个索引。它有两个特点:
- 使用记录主键值的大小进行页的排序
- B+树的叶子节点存储的是完整的用户记录。
把具有这两种特性的B+树称为聚簇索引,所有完整的用户记录都存放在这个聚簇索引的叶子节点处。InnoDB存储引擎会自动创建聚簇索引。所有的用户记录都存储在了叶子节点,索引即数据。
2、操作索引
创建索引
-
方式一:
create 索引类型 索引名 on 表(字段)
单值:create index id_index on student(id);
唯一:create unique index name_index on student(name);
复合:create index age_name_index on student(age, name); -
方式二:
ALTER table 表名 ADD INDEX 索引名(字段名)
单值:alter table student add index name_index(name)
查询索引
SHOW INDEX FROM 表名;
删除索引
DROP INDEX 索引名 ON 表名;
3. 如何挑选索引:(主键本身会自动生成聚簇索引)
1). 只为用于搜索、排序或分组的列创建索引。
2). 最好为那些列的基数(去重后的个数)大的列建立索引。
3). 只对字符串的前几个字符进行索引,尤其是在字符串类型能存储的字符比较多的时候。比如name varchar(100), 可设置索引 in_name(name(10))
4). 如果搜索条件有多个列的话,最好建立一个联合索引, 而不是分别为每个列建立一个索引;
5). 让索引列在表达式中单独出现
6). 尽量适用覆盖索引进行查询,避免回表带来的性能损耗。
4. 最左匹配原则
最左优先,以最左边的为起点任何连续的索引都能匹配上,遇到范围查询(>、<、between、like)就会停止匹配。搜索条件中的列的顺序必须和索引列的定义顺序一致,比方说索引列的定义顺序是name、birthday、phone_number,如果我们的搜索条件中只有name和phone_number,而没birthday,这样只能用到name列的索引,birthday和phone_number的索引就用不上了。
范围查询 LIke
如果只匹配前缀也是可以快速定位记录的,比如
SELECT * FROM person_info WHERE name LIKE 'As%';
但是若只给出后缀或者中间的某个字符串,比如这样:
SELECT * FROM person_info WHERE name LIKE '%As%';
MySQL就无法快速定位记录位置了,因为字符串中间有’As’的字符串并没有排好序,所以只能全表扫描了。
范围查询 >, <
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
由于B+树中的数据页和记录是先按name列排序的,所以查询过程如下:
- 通过B+树在叶子节点中找到第一条name值大于Asa的二级索引记录,读取该记录的主键值进行回表操作,获得对应的聚簇索引记录后发送给客户端。
- 根据上一步找到的记录,沿着记录所在的链表向后查找下一条二级索引记录,判断该记录是否符合name < 'Barlow’条件,如果符合,则进行回表操作后发送至客户端。
- 重复上一步骤,直到某条二级索引记录不符合name <'Barlow’条件为止。
注意:在使用联合进行范围查找时,如果对多个列同时进行范围查找的话,只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引,比方说这样:
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-01';
上边这个查询可以分成两个部分:
- 通过条件name > ‘Asa’ AND name < ‘Barlow’ 来对name进行范围,查找的结果可能有多条name值不同的记录,
- 对这些name值不同的记录继续通过birthday > '1980-01-01’条件继续过滤。
对于联合索引 idx_name_birthday_phone_number 来说,只能用到name列的部分,在搜索条件中继续以birthday列进行查找时是用不到这个B+树索引的。
5. =、in自动优化顺序
不需要考虑=、in等的顺序,mysql会自动优化这些条件的顺序,匹配尽可能多的索引列。
如有索引(a, b, c, d),查询条件c > 3 and b = 2 and a = 1 and d < 4 与a = 1 and c > 3 and b = 2 and d < 4 等顺序都是可以的,MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4,依次命中a、b、c。
6. 二级索引
若搜索条件不是主键时,新建一个B+树,使用记录c2列的大小进行记录和页的排序。
这个B+树与上边介绍的聚簇索引有几处不同:
- 使用记录c2列的大小进行记录和页的排序:
页内的记录是按照c2列的大小顺序排成一个单向链表。
各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表。 - B+树的叶子节点存储的并不是完整的用户记录,而只是c2列+主键这两个列的值。
- 目录项记录中不再是主键+页号的搭配,而变成了c2列+页号的搭配。
根据这个以c2列大小排序的B+树只能确定要查找记录的主键值,要想找到完整的用户记录,需要到聚簇索引中再查一遍,这个过程也被称为回表。
7. 联合索引
同时为多个列建立索引,比方让B+树按照c2和c3列的大小进行排序,这个包含两层:
-
先把各个记录和页按照c2列进行排序。
-
在记录的c2列相同的情况下,采用c3列进行排序
二、索引的内部构造
建议接下来的内容,先了解下我的另一篇博客《数据库 – InnoDB数据页》。
一些记录放到页里边的示意图就是:
一个简单的索引方案
在向表中插入许多条记录后,有很多页来存储数据,页与页之间类似于双向链表,可能是这样的效果:
因为这些16KB的页在物理存储上并不挨着,所以想根据主键值快速定位某些记录所在的页,需要给它们做个目录,每个页对应一个目录项,且目录项必须满足下面这个条件:
下一个数据页的主键值必须大于上一个页中的主键值。
每个目录项包括两个部分:
- 页的用户记录中最小的主键值,我们用key来表示。
- 页号,我们用page_no表示。
只需要把几个目录项在物理存储器上连续存储,比如放到一个数组里,就可以实现根据主键值快速查找某条记录的功能。
InnoDB中的索引方案
随着表中记录数量的增多,需要非常大的连续的存储空间才能把所有的目录项都放下;若对记录进行增删,意味着把所删目录项后的目录项都向前移动一下。
为解决上述问题,复用了之前存储用户记录的数据页,为了和用户记录做一下区分,我们把这些表示目录项的记录称为目录项记录。由记录头信息里的record_type属性进行区分,它的各个取值代表的意思如下:
- 0:普通的用户记录
- 1:目录项记录
- 2:最小记录
- 3:最大记录
可分配一个页来专门存储目录项记录,目录项记录只有主键值和页的编号两个列:
虽说目录项记录中只存储主键值和对应的页号,比用户记录需要的存储空间小多了,但一个页只有16KB大小,能存放的目录项记录也有限,如果表中的数据太多,以至于一个数据页不足以存放所有的目录项记录,就需要分配一个新的存储目录项记录的页:
存储目录项记录的页,但是这些页在存储空间中也可能不挨着,如果我们表中的数据非常多则会产生很多存储目录项记录的页,则需要为这些存储目录项记录的页再生成一个更高级的目录,就像是一个多级目录一样,大目录里嵌套小目录,小目录里才是实际的数据。
我们的实际用户记录其实都存放在B+树的最底层的节点上,这些节点也被称为叶子节点或叶节点,其余的节点都是用来存放目录项的。
MyISAM中的索引方案
MyISAM的索引方案也使用B+树,但是却将索引和数据分开存储,将表中的记录按照插入时间顺序的存储在一块存储空间上,可以通过行号而快速访问到一条记录。
有三个文件夹:
- MYD:存储数据,可通过行号快速查找;
- MYI:B+树的结构存储索引,通过索引找到行号;
- frm:表的一些框架信息
MyISAM会单独为表的主键创建一个B+树索引,但在B+树的叶子节点中存储的不是完整的用户记录,而是主键值 + 行号的组合。也就是先通过索引找到对应的行号,再通过行号去找对应的记录!意味着MyISAM中建立的索引全部都是二级索引!
对其它列的查询,是在叶子节点处存储的是相应的列 + 行号。这些索引也都是二级索引。
根页面万年不动窝
前边介绍B+树索引时,为方便理解,先把存储用户记录的叶子节点都画出来,然后接着画存储目录项记录的内节点,实际上B+树的形成过程是这样的:
- 每当为某个表创建一个B+树索引(聚簇索引默认就有)的时候,都会为这个索引创建一个根节点页面。最开始表中没有数据的时候,每个B+树索引对应的根节点中既没有用户记录,也没有目录项记录。
- 随后向表中插入用户记录时,先把用户记录存储到这个根节点中。
- 当根节点中的可用空间用完时继续插入记录,此时会将根节点中的所有记录复制到一个新分配的页,比如页a中,然后对这个新页进行页分裂的操作,得到另一个新页,比如页b。这时新插入的记录根据键值(也就是聚簇索引中的主键值,二级索引中对应的索引列的值)的大小就会被分配到页a或者页b中,而根节点便升级为存储目录项记录的页。
注意:一个B+树索引的根节点自诞生之日起,便不会再移动。只要对某个表建立一个索引,那么它的根节点的页号便会被记录到某个地方,凡是InnoDB存储引擎需要用到这个索引时,都会从那个固定的地方取出根节点的页号,从而来访问这个索引。