1、聚集索引:叶子节点(数据页)存放整张表的行数据,一张表只能有一个聚集索引,按照每张表的主键构造一棵B+树,如果没有定义主键,InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引,InnoDB就会定义隐藏的主键然后在上面进行聚集。
2、非聚集索引(辅助索引):叶子节点存放的是键值以及指向数据页的偏移量,需要进行二次查询
聚集索引和非聚集索引的区别:叶节点存放的信息不同;聚集索引只有一个,非聚集索引可以创建多个;
3、覆盖索引:即从辅助索引中查询,若找不到再去聚集索引中找。如果要查询的字段值在辅助索引上就有,就不用再查聚集索引了,这显然会减少IO操作。
4、哈希索引:存储引擎会给所有索引列计算一个哈希码,哈希索引只包含哈希值和行指针(key---value),哈希索引只支持等值查询,不支持大于,小于等比较。
5、联合索引:覆盖表中两列或者以上的索引,最左匹配原则
6、主键索引:唯一且不为空
7、唯一索引:唯一可以为空
8、普通索引:不唯一,可以为空
为什么用B+树会比B树更好?
- b+树的中间节点不保存数据,所以磁盘页能容纳更多节点元素,更“矮胖”;
- b+树查询必须查找到叶子节点,b树只要匹配到即可不用管元素位置,因此b+树查找更稳定;
- 对于范围查找来说,b+树只需遍历叶子节点链表即可(提高区间访问的性能),b树却需要重复地中序遍历
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询的条件的字段应该创建索引
- 查询中与其他表关联的字段,外键关系建立索引
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序的速度
哪些情况不要创建索引
- 表记录太少
- 经常增删改的表
- Where条件里用不到的字段不创建索引
- 数据重复且分布平均的表字段,因此应该只为经常查询和经常排序的数据列建立索引。
- 注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
explain解析:
using index :使用覆盖索引的时候就会出现
using where:在查找使用索引的情况下,需要回表去查询所需的数据
using index condition:在MySQL 5.6版本后加入的新特性(Index Condition Pushdown);会先条件过滤索引,过滤完索引后找到所有符合索引条件的数据行,随后用 WHERE 子句中的其他条件去过滤这些数据行;查找使用了索引,但是需要回表查询数据
using index & using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据