索引的几种类型
按功能划分
主键索引:则是在唯一性索引的基础上又增加了不为空的约束,即 NOT NULL+UNIQUE
,一张表里最多只有一个主键索引,当然一个主键索引中可以包含多个字段。
普通索引:普通索引允许被索引的数据列包含重复的值。
唯一索引:则在普通索引的基础上增加了数据唯一性的约束
全文索引:全文索引基本上不可能用到。如果有这样的需求,请用第三方框架
按照物理实现划分
聚集索引
非聚集索引
聚集索引和非聚集索引
InnoDB聚集索引和非聚集索引有什么差异?
InnoDB聚集索引的叶子节点存储整行数据:
InnoDB普通索引的叶子节点存储主键值。
普通索引获取数据(比如ls这一行的数据)需要扫码两遍索引树:
(1)先通过普通索引定位到主键值id=5;
(2)再通过聚集索引定位到行记录;
这就是所谓的回表查询,先定位主键值,再定位行记录
什么是主键索引?
- 主键索引叶子节点存放的是整行数据;
聚集索引和主键索引不能作为同一个概念?
每张表都有唯一的聚集索引:
- 如果这张表定义了主键索引,那么这个主键索引就作为聚集索引。
- 如果这张表没有定义主键索引,那么该表的第一个唯一非空索引作为聚集索引。
- 如果这张表也没有唯一非空索引,那么 InnoDB 内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个 6 个字节的列,该列的值会随着数据的插入自增。
什么是覆盖索引?
覆盖索引即从非主键索引中就能查到所需的数据,而不需要查询主键索引中的数据,减少了树的搜索次数,显著提升性能
如何实现索引覆盖?
最常见的方法就是:将被查询的字段,建立到联合索引里去
举例:
student表里有a,b,c三个字段,我们给a建立了索引
SELECT a FROM student,该语句能够命中a索引,无需回表查询
SELECT a,b FROM student,该语句能够命中a索引,但是无法从a索引的索引树中得到b字段的数据,需要回表查询
如果把(a)单列索引升级为联合索引(a,b)就不同了,SELECT a,b FROM student可以在(a,b)索引的索引树中得到a和b字段的数据。
这就实现了索引覆盖
什么是前缀索引?
有时需要在很大的列上创建索引,这会造成索引特别慢。
使用前缀索引可以解决这个问题。
前缀索引是指对文本或者字符串的前几个字符建立索引,这样索引的长度更短,查询速度更快。