MySQL InnoDB索引原理

[diaosichengxuyuan]

索引分类

1.聚集索引：由主键构成的B+树索引，InnoDB在没有主键时会选择定义的第一个非空唯一索引作为主键或者自动生成一个longint的6字节的默认主键
2.辅助索引：B+索引下，非聚集索引即辅助索引
3.唯一索引：索引字段是唯一unique的
4.主键索引：既属于聚集索引又属于唯一索引
5.联合索引：又称复合索引，属于辅助索引
6.hash索引：hash数据结构构建的索引
7.FullText索引：针对文本查找的特殊场景索引

聚集索引原理图

InnoDB和MyISAM结构不同，InnoDB索引即数据，数据即索引。聚集索引采用B+树数据结构，叶子节点存储N条数据，叶子节点间通过双向链表关联，N条数据间也通过双向链表关联。
1.B+树是多叉的，树的高度较低，可以减少磁盘页之间切换的IO操作，查找快，所以根据id=xxx查找很快。
2.B+树每个节点上的数据是排序，所以根据主键的范围查找也很快。
3.InnoDB推荐使用与业务无关的自增主键，这样每个页顺序写满就可以开辟新的页，而使用非单调主键需要在页中频繁的移动记录，这样增加了系统开销，产生磁盘碎片。

辅助索引原理图

辅助索引叶子节点不直接存储数据，而是存储主键值，所以根据辅助索引查找需要先通过辅助索引查找主键，然后通过聚集索引查找记录。
1.不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
2.辅助索引字段的等于或者范围查找也是很快的。

联合索引原理图

联合索引（A,B,C）也是排序的，这也解释了最左匹配原则
1.select * from t where A = xxx 可以使用索引（A）ref
2.select * from t where A > xxx 可以使用索引（A）range
3.select * from t where A = xxx and B = xxx 可以使用索引（A,B）ref
4.select * from t where A = xxx and B > xxx 可以使用索引（A,B）range
5.select * from t where A = xxx and B = xxx and C = xxx 可以使用索引（A,B,C）ref
6.select * from t where A = xxx and B = xxx and C > xxx 可以使用索引（A,B,C）range
7.select * from t where A > xxx and B = xxx and C = xxx 只能使用索引（A）range
8.select * from t where B = xxx and C = xxx 不能使用索引

索引分析

type 类型的性能比较
ALL(全表扫描) < index(索引表全量扫描) < range(范围索引) < ref(非主键索引) < eq_ref(join时，左表全扫描，右表唯一，右表索引是eq_ref) < const(主键唯一索引) < system(索引只有一条数据)
explain使用：https://segmentfault.com/a/1190000008131735

建索引的原则：

1.数据量，数据量太少不需要建索引
2.区分度，count(distinct(sex))/count(sex)区分度大于0.7比较好
3.最左匹配原则
4.in，数据量大时可能不走索引
5.like，遵循最左匹配
6.or，左右两端都走索引时才走索引，索引推荐用union

参考：https://www.cnblogs.com/bonelee/p/6225211.html

作者原创，转载请注明出处，违法必究！