目录
应用划分(主键、唯一、复合..)
1、从存储结构上来划分:BTree索引(B-Tree或B+Tree索引),Hash索引,full-index全文索引,R-Tree索引。这里所描述的是索引存储时保存的形式,
2、根据中数据的物理顺序与键值的逻辑(索引)顺序关系:聚集索引,非聚集索引。
3、从应用层次来分:主键索引,唯一索引,复合索引,普通索引,全文..
平时讲的索引类型一般是指在应用层次的划分。
-
主键索引:名为primary的唯一非空索引,不允许有空值,只有他只能有一个
-
唯一索引:索引列中的值必须是唯一的,但是允许为空值,避免同一个表中某数据列中的值重复,UNIQUE
唯一索引和主键索引的区别是:唯一约束的列可以为null且可以存在多个null值。唯一索引的用途:唯一标识数据库表中的每条记录,主要是用来防止数据重复插入。创建唯一索引的SQL语句如下:
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);
-
复合索引:在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时需遵循最左前缀原则。
-
普通索引(单值):一个索引只包含单个列,一个表可以有多个单列索引
-
全文索引:查找的是文本中的关键词,而不是比较索引中的值,只有在MyISAM引擎上才能使用,只能在CHAR、VARCHAR和TEXT类型字段上使用全文索引。FULLTEXT
结构划分(BTree、hash..)
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含以下几种:
索引结构 | 描述 |
---|---|
B+Tree索引 | 最常见的索引类型,大部分引擎都支持B+树索引,默认都是B+ |
Hash索引 | 底层数据结构是用哈希表实现,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-Tree(空间索引) | 空间索引是 MyISAM 引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少 |
Full-Text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式,类似于 Lucene, Solr, ES,少用 |
hash索引和B+树索引:
-
哈希索引不支持范围查询,b+支持范围查询
-
如果是等值查询,hash索引只需要经过一次算法即可找到相应的键值;
-
如果存储的数据重复度很低(也就是说基数很大),对该列数据以等值查询为主,没有范围查询、没有排序的时候,特别适合采用哈希
-
hash索引无法被用来进行数据的排序操作,hash值的大小关系不一定与hash计算之前的值一样
-
Hash 索引遇到大量Hash值相等的情况后性能就会很差,因为哈希冲突
-
哈希索引也不支持多列联合索引的最左匹配规则;Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值
-
Hash 索引无法做 like ‘xxx%’ 这样的部分模糊查询
对于B+树,相比于Hash好处:
1.利用Hash需要把数据全部加载到内存中,如果数据量大,是一件很消耗内存的事,而采用B+树,是基于按照节点分段加载,由此减少内存消耗。
2.和业务场景有段,对于唯一查找(查找一个值),Hash确实更快,但数据库中经常查询多条数据,这时候由于B+数据的有序性,与叶子节点又有链表相连,他的查询效率会比Hash快的多。
存储划分(聚簇和非聚簇)
在 InnoDB 存储引擎中,根据索引的存储形式,又可以分为以下两种:
分类 | 含义 | 特点 |
---|---|---|
聚集索引 | 叶子节点保存了整行数据(将数据存储与索引放一块) | 必须有,而且只有一个 |
非聚集(二级索引) | 叶子节点关联的是对应的主键id/行号(将数据与索引分开存储) | 可以存在多个 |
聚集索引选取规则:
-
如果存在主键,主键索引就是聚集索引
-
如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引
-
如果表没有主键或没有合适的唯一索引,则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引