什么是索引
索引是对数据库表中的一列或者多列的值进行排序的一种数据结构,如果把数据库中的表比作一本书,索引就是这本书的目录,通过目录可以快速查找到书中指定内容的位置。
索引也是一张表,该表中存储着索引的值和这个值的数据所在行的物理地址,使用索引后可以不用扫描全表来定位某行的数据,而是通过索引表来找到该行数据对应的物理地址。
索引是基于表的,而不是基于数据库的
索引的作用
- 数据库中的表、数据、索引之间的关系,类似于书架上的图书、书籍内容和书籍目录的关系。
- 索引所起的作用类似书籍目录,可用于快速定位、检索数据。
- 索引对于提高数据库的性能有很大的帮助。
索引的类型
普通索引
普通索引一般是在建表后再添加的
create index 索引名 on table_name(column1,column2);
alter table table_name add index 索引名(column1,column2);
主键索引
当一张表,把某个列设为主键的时候,则该列就是主键索引
例:
create table a(
id int primary key,
name varchar(20)
);
//这里id就是表的主键
如果当创建表时没有指定主键索引,也可以在创建表之后添加:
alter table table_name add primary key (column name);
唯一索引
索引列的值必须唯一,允许有空值,如果是组合索引,列值的组合必须唯一
create unique index index_name on table_name (column(length));
全文索引
全文索引主要针对文本文件,比如文章,标题,全文索引只有MyISAM有效(mysql5.6之后InnoDB也支持了全文索引)
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
索引的使用场景
要考虑对数据库表的某列或某几列创建索引,需要考虑以下几点:
- 数据量较大,且经常对这些列进行条件查询。
- 该数据库表的插入操作,及对这些列的修改操作频率较低。
- 索引会占用额外的磁盘空间。
满足以上条件时,考虑对表中的这些字段创建索引,以提高查询效率。
反之,如果非条件查询列,或经常做插入、修改操作,或磁盘空间不足时,不考虑创建索引。
索引的底层原理
- MyISAM引擎是MySQL5.5.8版本之前默认的存储引擎,不支持事务,支持全文检索,使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址,其结构如下:
上图是以以Col1为主键,MyISAM的示意图,可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果想在Col2上建立一个辅助索引,则此索引的结构如下图所示:
同样也是一棵B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
MyISAM的索引方式也叫做“非聚集索引”的。 - InnoDB的数据文件本身就是索引文件,叶节点包含了完整的数据记录,这种索引叫做聚集索引。
InnoDB存储引擎支持事务,其设计目标主要面向在线事务处理的应用,从MySQL数据库5.5.8版本开始,InnoDB存储引擎是默认的存储引擎。InnoDB支持B+树索引、全文索引、哈希索引。但InnoDB使用B+Tree作为索引结构
时,具体实现方式却与MyISAM截然不同。
第一个区别是InnoDB的数据文件本身就是索引文件。MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而InnoDB索引,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
上图是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录,这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
第二个区别是InnoDB的辅助索引data域存储相应记录主键的值而不是地址,所有辅助索引都引用主键作为data域。
聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
B-树
一棵M阶(M>2)的B树,是一棵平衡的M路平衡搜索树,可以是空树或者满足一下性质:
- 根节点至少有两个孩子
- 每个非根节点至少有M/2(上取整)个孩子,至多有M个孩子
- 每个非根节点至少有M/2-1(上取整)个关键字,至多有M-1个关键字,并且以升序排列
- key[i]和key[i+1]之间的孩子节点的值介于key[i]、key[i+1]之间
- 所有的叶子节点都在同一层
特性
1.关键字集合分布在整颗树中;
2.任何一个关键字出现且只出现在一个节点中;
3.每个节点存储date和key;
4.搜索有可能在非叶子节点结束;
5.一个节点中的key从左到右非递减排列;
6.所有叶节点具有相同的深度,等于树高h。
B+树
B+树是B-树的变形,也是一种多路搜索树:
- 其定义基本与B-树相同,除了:
- 非叶子节点的子树指针与关键字个数相同
- 非叶子节点的子树指针p[i],指向关键字值属于【k[i],k[i+1])的子树
- 为所有叶子节点增加一个链指针
- 所有关键字都在叶子节点出现
特性
1.所有关键字都出现在叶子节点的链表中(稠密索引),且链表中的节点都是有序的。
2. 不可能在非叶子节点中命中。
3. 非叶子节点相当于是叶子节点的索引(稀疏索引),叶子节点相当于是存储数据的数据层。
4. 更适合文件索引系统