索引是数据库系统里面最重要的概念之一,简单来说,索引的出现是为了提高数据查询效率。
索引常见模型
实现索引的方式有很多种,所以这里也就引入了索引模型的概念。可以用于提高读写效率的数据结构很多,这里先给介绍三种常见,也比较简单的数据结构,它们分别是哈希表、有序数组和搜索树。
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;