数据库 -- 索引

一、索引的介绍

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存储引擎需要用到这个索引时，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。