MySQL系列-4索引

索引是数据库系统里面最重要的概念之一，简单来说，索引的出现是为了提高数据查询效率。

索引常见模型
实现索引的方式有很多种，所以这里也就引入了索引模型的概念。可以用于提高读写效率的数据结构很多，这里先给介绍三种常见，也比较简单的数据结构，它们分别是哈希表、有序数组和搜索树。

1.哈希表
哈希表是一种以键-值（key-value）存储数据的结构，通过key就可以找到其对应的值即Value。哈希表只适用于等值查询。
2.有序数组
序数组在等值查询和范围查询场景中的性能都非常优秀。但是，在需要更新数据的时候就麻烦了，往中间插入一个记录就必须得挪动后面所有的记录，成本太高。所以，有序数组索引只适用于静态存储引擎。
3.搜索树

数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景。

在MySQL中，索引是在存储引擎层实现的，所以并没有统一的索引标准，不同存储引擎的索引的工作方式并不一样。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。
由于InnoDB存储引擎在MySQL数据库中使用最为广泛，所以下面就以InnoDB为例，分析其中的索引模型。

InnoDB 的索引模型
在InnoDB中，表都是根据主键顺序以索引的方式存放的，这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。
每一个索引在InnoDB里面对应一棵B+树。
假设，有一个主键列为ID的表，表中有字段k，并且在k上有索引。这个表的建表语句是：

create table test(
id int not null primary key, 
col2 int not null,
col3 varchar(32),
col4 int
index idx_col2 (col2)
)engine=InnoDB;

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

主键索引和普通索引的查询有什么区别？

• 如果语句是select * from test where id=500，即主键查询方式，则只需要搜索id这棵B+树；
• 如果语句是select * from test where col2=5，即普通索引查询方式，则需要先搜索k索引树，得到id的值为500，再到id索引树搜索一次。这个过程称为回表。
  显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

最左前缀原则

• 如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的
• 第二个需要考虑的是空间

扩展
索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

对于上面例子中的InnoDB表test，如果你要重建索引 col2，你的两个SQL语句可以这么写：
alter table test drop index idx_col2;
alter table test add index idx_col2 (col2);

重建主键索引
alter table test engine=InnoDB;