自增索引和非自增索引
自增索引我们建表时最常用的就是bigint了,插入数据不需要指定主键值,数据库会为我们按照当前使用到的值+1,生成一个新值,存入数据库。
非自增索引我们可以自定义指定,常用的比如生成的uuid。
二者的区别在哪里呢?
二者的区别可以说是自增主键的优势,自增主键新插入的数据都是顺序添加到最后的,没有数据搬移,因为主键是递增的,那么还是有序的,且体积小。
而非自增索引,比如uuid因为这是无需的,新插入的数据,可能会排在索引的最开头,所以涉及到数据搬移,这是非常耗性能的,且体积一般至少是32位的,体积大。
索引维护
页分裂
存储数据的数据页满了,那么就要进行页分裂了,也就是做数据拆分,把数据放到两个数据页上。
页合并
如果数据页上数据删除较多,比较空,那么就会进行页合并,数据更紧凑了,数据检索性能会提升。
回表
InnoDB中表的主键,都是包含整行的全部数据,普通索引只包含创建索引的列。
回表也是针对普通索引来说的,因为当一个索引没有包含sql查询的全部数据,此时就需要基于普通索引,再找到主键索引,基于主键索引,再获取对应的数据。
总结:从普通索引回到主键索引树搜索数据的过程,称为回表。
覆盖索引
如果查询使用的索引,包含了sql要返回的数据,不需要再回到主键查询数据,这就是索引覆盖。
例如:select ID from TT where a > 1 and a < 10,a是TT表中的一列,且创建了索引,这一条sql执行就不会回表,也就是覆盖索引了,因为普通索引存储了主键值。
前缀索引(最左前缀原则)
B+ 树索引,可以基于左侧原则来基于索引快速匹配,比如一个字符串的左半部分匹配其中一部分,也是会走索引的。
这就是我们常说的like会不会走索引的问题,select b from TT where b like ‘abc%’
select b from TT where b like 'abc%' -- 走索引
select b from TT where b like '%abc%' -- 不走索引
索引下推
MySQL 5.6引入的索引下推优化,在索引遍历过程中,优先检索索引包含的字段,来快速排出掉不符合条件的数据,从而大大提升性能。
例如:创建联合索引(a,c),b列无索引,
select ID from TT where a > 1 and b like 'abc%' and c > 10
执行这个sql,MySQL 5.6以后的版本会先检索a和c,对符合条件的数据再回表检索b。