MySQL之索引学习——索引(一)

主要总结一下索引的概要、MySQL索引中使用的数据结构介绍以及一些索引的分类

一、索引的概要

在数据库中，索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

假设MySQL没有根据索引进行查找：

• 在一个数据页中查找
  • 以主键为搜索条件：在页目录中使用二分法快速定位到对应的槽，然后再遍历槽对应分组中的记录，即可快速找到指定的记录。
  • 以其他列作为搜索条件：对于非主键列的查找，由于没有为非主键列建立对应的目录页，因此无法用二分法快速定位相应的槽。因此只能从Infimum记录开始依次遍历单向链表中的每条记录，然后对比每条记录是否符合搜索条件，效率非常低。
• 在多个数据页中查找
  • 在多个页中查找记录可以分为两个步骤：
    • 定位到记录所在的页
    • 从所在的页内查找相应的记录
  • 在没有索引的情况下，无论是根据主键列还是其他列的值查找，都不能快速定位到记录所在的页，因此只能从第一页沿着双向链表一直往下找，非常耗时。

二、索引的分类

（一）按数据结构分类

Hash索引

哈希表是一种以键值存储数据的结构，输入待查找的键即 key，就可以找到其对应的值即 Value。

哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况，即哈希碰撞，处理这种情况的一种方法是，拉出一个链表。

但是，在哈希表中，数据的存储不是按顺序存放的，所以哈希索引做区间查询的速度是很慢的。

所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。

有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀。

在查找数据方面，有序数组可以通过二分查找的方式快速找到，时间复杂度是 O(log(N))。

同样，有序数组的索引结构支持范围查询，通过二分法找到需要查找的范围的首元素，然后向后遍历，直到找到第一个不满足条件的元素为止。

如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

所以，有序数组索引只适用于静态存储引擎。

B+树索引

InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键（key）
• 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

B+Tree 索引正是快速定位记录所在的数据页而建立的目录，而建立这个索引必须完成两件事：

• 第一：下一个数据页中用户的记录的主键值必须大于上一个页中用户记录的主键值
  • 在InnoDB中，在对页中的记录进行增删改操作时，必须通过一些记录移动的操作来始终保证：下一个数据页中用户的记录的主键值必须大于上一个页中用户记录的主键值，则这个过程也成为页分裂操作，即在一个数据页中插入记录，而该数据页在插入之前已经满了，则需要申请一个新的数据页，然后挪动部分数据过去。
• 第二：给所有的页建立一个目录项
  • 由于各个数据页的编号可能不是连续的，因此需要给这些数据页编制一个目录，每个页对应一个目录项，目录项包括两个部分：
    • 页的记录中最小的主键值，用key来表示
    • 页号，用page_no表示
  • 创建好目录项后，只需要把几个目录项在物理存储器上连续存储，就可以实现根据主键值快速查找某条记录的功能了。
  • 先从目录项中根据二分法快速定位到主键值所在的目录项对应的数据页，在从数据页中找到对应的记录。
    

    该图是在《MySQL是怎样运行的》一书中的图
    

上述这种给数据页生成目录项，每条目录项分别指向对应的数据页形成链表，通过目录项一级一级向下查找到对应记录所在数据页的结构，每层的数据页通过双向链表进行连接，其实就是B+树，而这些目录项就相当于是索引。

无论是存放用户记录的数据页，还是存放目录项记录的数据页，都把存放到B+树中，而这些数据页称为B+树的节点。

真正的用户记录存放在B+树最底层的节点上，这些节点称为叶子节点或叶节点。

其余用来存放目录项记录的节点成为非叶子节点或内节点，其中B+树最上边的那个节点也称为根节点。

（二）按物理存储分类

聚簇索引

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

聚簇索引有以下两个特点：

• 第一个特点：使用记录主键值的大小进行记录和页的排序
  • 页（包括叶子节点和非叶子节点）内的记录按照主键的大小顺序排成一个单向链表，页内的记录被划分成若干个组，每个组中主键值最大的记录在页内的偏移量会被当作槽依次存放在页目录中，可以在页目录中通过二分法快速定位到主键列等于某个值的记录。
  • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
  • 存放目录项记录的页分为不同的层级，在同一层级中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
• 第二个特点：B+树的叶子节点存储的是完整的用户记录。所谓完整的用户记录，就是值这个记录中存储了所有列的值（包括隐藏列）

包含以上两个特点的B+树称为聚簇索引

二级索引（辅助索引、非聚簇索引）

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

不同于聚簇索引，二级索引的叶子节点存储的不是完整的用户记录，而是索引列+主键这两个列的值。

目录项记录中不再是主键+页号的搭配，而是索引列+页号的搭配。

以非主键列的大小为排序规则而建立的B+树需要执行回表操作才可以定位到完整的用户记录，这种B+树也称为二级索引或辅助索引。

聚簇索引和非聚簇索引的查询有什么区别？
1、当定位到二级索引的某一条符合条件的记录时，需要根据该记录中的主键信息回到聚簇索引中查找到完整的用户记录。
2、通过携带主键信息到聚簇索引中重新定位完整的用户记录的过程也成为回表。
3、若不采取回表操作，将完整的用户记录放到每棵B+树中，相当于每建立一棵B+树都需要把所有的用户记录复制一遍，会浪费存储空间。

回表的代价
1、在使用二级索引进行范围查询的时候，二级索引对应的主键值的大小是毫无规律的。
2、每读取一条二级索引记录，就需要根据该二级索引记录的主键值到聚簇索引中执行回表操作。
3、如果对应的聚簇索引记录所在的页面不在内存中，就需要将该页面从磁盘加载到内存中。
4、由于要读取很多主键值并不连续的聚簇索引记录，而这些聚簇索引记录分布在不同的数据页中，这些数据页的页号也毫无规律，因此会造成大量的随机I/O。
5、需要执行回表操作的记录越多，使用二级索引进行查询的性能就越低，因此某些查询宁愿使用全表扫描也不使用二级索引。
6、而选择全表扫描，还是二级索引+回表，这就是查询优化器的工作了。
7、查询优化器会事先针对表中的记录计算一些统计数据，再利用这些统计数据或者访问表中的少量记录来计算回表操作的记录数。
8、如果需要执行回表操作的记录数越多，就越倾向于使用全表扫描，反之则倾向于使用二级索引+回表。

（三）按字段特性分类

主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

在创建表时，创建主键索引的方式如下：

CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

唯一索引

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

在创建表时，创建唯一索引的方式如下：

CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

建表后，如果要创建唯一索引，可以使用这面这条命令：

CREATE UNIQUE INDEX index_name ON table_name(index_column_1,index_column_2,...); 

普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

在创建表时，创建普通索引的方式如下：

CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

建表后，如果要创建普通索引，可以使用这面这条命令：

CREATE INDEX index_name ON table_name(index_column_1,index_column_2,...); 

前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

在创建表时，创建前缀索引的方式如下：

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
); 

建表后，如果要创建前缀索引，可以使用这面这条命令：

CREATE INDEX index_name ON table_name(column_name(length)); 

（四）按字段个数分类

单列索引

单列索引指建立在单列上的索引称为单列索引，比如主键索引；

联合索引

联合索引指同时以多个列的大小作为排序规则，即同时为多个列建立索引。

例如建立索引(a,b)，则有：

• 先将各个记录和页按照a列进行排序；
• 在记录的a列相同的情况下，再采用b列进行排序；

联合索引中：

• 每条目录项记录都有a列、b列、页号3个部分组成。各条记录先按照a列的值进行排序，如果记录的a列相同，则按照b列的值进行排序；
• B+树叶子节点处的用户记录由a列、b列和主键列组成；

使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配：

• 例如当你建立联合索引（name, age）时，如果你要查的是所有名字第一个字是“张”的人，你的 SQL 语句的条件是where name like ‘张 %’。这时，你也能够用上这个索引。
• 只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

在建立联合索引的时候，如何安排索引内的字段顺序？

• 评估标准是索引的复用能力。
• 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的；
  • 如果既有联合索引(a, b)，又有基于 a、b 各自的查询，查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。
• 第二原则是，考虑的原则就是空间；

以上主要总结索引的一些选型以及MySQL中的一些索引类型分类。