索引一般有以下几种:
1)聚集索引
2)辅助索引(非聚集索引)
3)全文索引
4)自适应的哈希索引
索引的算法一般有以下几种:
1)B+树索引
2)倒排索引
3)哈希索引
1、B+树索引
聚集索引和辅助索引都是B+树索引,联合索引是属于辅助索引的一种情况,以下会详细介绍。
(1)聚集索引
mysql默认一张表按照主键建立聚集索引,一张表只能有一个聚集索引,聚集索引的叶子节点存放的是整张表的行记录数据,非叶子节点存放的是键值指向叶子的偏移量。
(2)辅助索引
一张表可以有多个辅助索引,辅助索引的叶子节点存放除了键值意外,还有指向一个指向主键索引的指针,根据该指针获得主键,然后遍历聚集索引,获得一行记录。
举例来说,如果辅助索引树高度为3,聚集索引树高度为3,则最终找到一个完整的行记录,需要6次IO访问。
(3)联合索引
联合索引,即多列索引,两个特性:
1)“最左匹配”
ALTER TABLE product ADD INDEX index_ab (a,b);
索引有效:
select * from product where a = 1;
select * from product where a = 1 and b = 2;
索引无效:
select * from product where b = 2;
2)“二键排序”
由于B+树的性质,所有的记录都是排好序的,对于多列索引,是这样排序的:先按1键排序,若1键相等,按二键排序,…,一次类推。
ALTER TABLE product ADD INDEX index_ab (a,b,c);
所以,以下搜索可以直接获得结果,无需再次排序:
select * from product where a = 1 order by b;
select * from product where a = 1 and b = 2 order by c;
但是,以下不符合“最左匹配”,所以会额外排序(Using filesort):
select * from product where a = 1 order by c;
2、倒排索引
全文索引是基于倒排索引的数据结构。
倒排索引用辅助表(Auxiliary Table)存储单词和单词在文档中的位置,通常用关联数组实现:
inverted file index:{单词,单词所在文档的ID}
full inverted index:{单词,(单词所在文档的ID:在文档中的具体位置)}
FTS Index Cache:全文检索索引缓存,红黑树结构。
文档分词后,索引缓存根据(word,position)进行排序,并暂存于该缓存。
当事务提交时,分词结果写入到索引缓存,然后InnoDB批量更新,写入辅助表。
当全文检索进行时,防止索引缓存中仍有数据,InnoDB将索引缓存中对应的word字段合并到辅助表,然后再进行查询。
代码示例
ALTER TABLE `student` ADD FULLTEXT INDEX ft_stu_name (`name`);
SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪');
3、哈希索引
InnoDB存储引擎用哈希算法来对字典进行查找,冲突机制为拉链法,哈希函数为除留余数法。
InnoBD通过参数innodb_adaptive_hash_index来控制自适应哈希索引的使用。
哈希索引只能用于等值搜索,对于范围搜索,无能为力。
SELECT * FROM table WHERE index_col = 'xxx';