索引类型

逻辑分类

按功能分类

主键索引:一张表只能有一个主键索引,不允许重复、不允许为 NULL

唯一索引:数据列不允许重复,允许为 NULL 值,一张表可有多个唯一索引,索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一

普通索引:一张表可以创建多个普通索引,一个普通索引可以包含多个字段,允许数据重复,允许 NULL 值插入

全文索引:它查找的是文本中的关键词,主要用于全文检索

按列数分类

单例索引:一个索引只包含一个列,一个表可以有多个单例索引

组合索引:一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则,即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效

物理分类

聚簇索引:指的是数据跟对应的索引列紧凑的存储在一起的数据存储方式。

非聚簇索引:数据跟索引分开存储

InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引,但是只有InnoDB的主键索引才是聚簇索引,InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。这与存储引擎的文件系统的不同有关。

存储引擎是基于表的,以下建立两张别使用MyISAM和InnoDB引擎的表,看看其在文件系统中对应的文件存储格式。

存储引擎为MyISAM:

  • *.frm:与表相关的元数据信息都存放在frm文件,包括表结构的定义信息等
  • *.MYD:MyISAM DATA,用于存储MyISAM表的数据
  • *.MYI:MyISAM INDEX,用于存储MyISAM表的索引相关信息

存储引擎为InnoDB:

  • *.frm:与表相关的元数据信息都存放在frm文件,包括表结构的定义信息等
  • *.ibd:InnoDB DATA,表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引,叶子节点存储索引和索引对应的数据

聚簇索引优点

  • 数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中
  • 聚簇索引对于主键的排序查找和范围查找速度非常快

聚簇索引缺点

  • 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
  • 更新聚簇索引的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。
  • 聚簇索引最大限度的提高了IO密集型应用的性能,如果数据全部在内存,聚簇索引就没有什么优势
  • 基于聚簇索引的表插入新行,或者主键被更新导致行移动时,可能面临列分页的问题
  • 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
  • 二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

索引优点与用处

优点

  1. 减少服务器扫描的数据量
  2. 避免排序和临时表
  3. 将随机IO变为顺序IO

用处

  1. 快速查找匹配WHERE子句的行
  2. 当有多个索引可以选择,MySQL会使用最少行的索引
  3. 如果表具有多列索引,则优化器使用索引的任何最左前缀来查找行
  4. 当有表连接时,从其他表检索行数据
  5. 查找特定索引列的min或max值,因为索引在叶子节点就是有顺序的,所以可以直接获取到极值
  6. 如果排序或分组时在可用索引的最左前缀上完成,则对表进行排序和分组。
  7. 在某些情况下,可以优化查询以检索值而无需查询数据行

索引使用原则

列的离散度

离散度 = 不同值的数量/总行数

越接近1,那么离散度越高,越接近0,离散度越低。

不建议对离散度低的列建立索引,比如性别(男,女),这种情况比起索引更建议分表

组合索引最左匹配

有的时候我们的多条件查询的时候,也会建立联合索引,例如查询成绩的时候必须同时输入身份证和考号。

联合索引在 B+Tree 中是复合的数据结构,它是按照从左到右的顺序来建立搜索树的(name 在左边,phone 在右边)。

suoy
从这张图可以看出来,name 是有序的,phone 是无序的。当 name 相等的时候,phone 才是有序的。

查询数据的时候,B+Tree 会优先比较 name 来确定下一步应该搜索的方向,往左还是往右。如果 name相同的时候再比较 phone。但是如果查询条件没有 name,就不知道第一步应该查哪个节点,因为建立搜索树的时候 name 是第一个比较因子,所以用不到索引。

B+树的搜索与普通搜索树一样,依赖顺序,所以建立索引的字段建议是有序且易于比较大小的字段

究竟用什么字段来创建索引,必须要考虑实际情况:
比如,数据表不需要再改动也不需要再插入新数据时,当然就可以使用有序且易于比较大小的字段,查询效率会更高,但是这可能在一定程度上牺牲了空间,算是以空间换时间的方法。那么反过来,为了节省空间,我们也可以用较短或者较简单的字段去创建索引。
但是如果建立索引之后数据会频繁插入呢?插入数据时,B+树为了维护索引的有序性,在新插入值时会做必要的维护,常常需要逻辑上挪动后面的数据以腾出位置。这时候会导致页分裂与页合并,插入效率和空间利用率都不高,这个时候还是用自增主键更好,因为顺序插入不会涉及到页分裂与页合并问题。

再举一个例子:
一个表包含id,name,age三个列,id为主键,name和age是组合索引,那么如果查询的时候有以下4句:

  • select * from table where name = ‘zhangsan’ and age = 10;
  • select * from table where name = ‘zhangsan’;
  • select * from table where age = 10;
  • select * from table where age = 10 and name = ‘zhangsan’;

上述4条语句中,只有第三条sql语句不会使用索引,因为没有匹配name直接匹配age是无法使用此组合索引的。第四条之所以可以使用索引是因为MySQL的优化器会对这个语句进行优化,调整成与第一句同样的效果。

组合索引的使用

1
2
CREATE INDEX idx_name on user_innodb(name); 
CREATE INDEX idx_name_phone on user_innodb(name,phone);

如上建立两条索引,按照最左匹配原则,用左边的字段 name 去查询的时候,也能用到索引,所以第一个索引完全没必要。
同样,根据最左匹配原则,只用where phone = ?查询是用不到此索引。

回表与覆盖索引

回表就是先通过数据库索引扫描出数据所在的行,再通过主键索引取出上个索引未提供的数据,即基于非主键索引的查询需要多扫描一颗索引树。
索引覆盖是跟回表相反的概念,在索引的叶子节点中如果能获取到查询的所有列数据,就无需回表的过程称之为索引覆盖。可以说没有回表的必要就是覆盖索引。

索引下推

索引下推指的是数据筛选的过程下移到存储引擎层来完成,而不是在server完成。(MySQL架构包含客户端,server层,存储引擎三部分)

例如:select * from table where name = ‘zhangsan’ and age = 10;
在索引没有下推之前,会先根据name的值将所有符合条件的数据加载到server层,再用age对数据进行筛选。
而使用索引下推之后,会根据name与age的结果直接从存储引擎返回所有符合条件的结果,而不是在server层。