Mysql主键索引时间复杂度_Mysql索引总结

索引的常见模型

1.哈希表 查询效率高,比较适合等值查询,但不适合范围查询

2.有序数组,只有一层,减少I/O次数,也可以用二分法,效率比较高,但只适合存储静态数据,因为扩容成本会比较大

3.b+树,可以控制树的高度,减少I/O次数,同时时间复杂度也是log(N)

innodb的索引模型

根据叶子节点的内容

主键索引,也即我们常说的聚簇索引,叶子节点存的是整行的数据

非主键索引,叶子节点存的是主键的值,我们通过主键的值,在到主键索引上查询对应的值,这个过程也称为回表.为什么非主键索引不同样去存整行数据呢?主要考虑空间使用率,冗余数据会太多。

同时我们也可以得出结论,主键长度越小,那么普通索引的叶子节点就越小,所占的空间也就越小.

主键索引.png

假如我们执行 select * from T where ID=500,即主键查询,则只需搜索ID这棵B+树.

如果执行的是select * from T where k=5,即普通索引的方式,则只需先搜索k索引数,获取到ID的值为500,再到ID索引树上搜索一次.

关于页的分裂

假如我们插入新的行ID=700,则只需要在R5的记录后面插入一个新的记录.但如果我们新插入的行ID=400,需要逻辑上挪到后面的数据,空出位置,但如果R5所在的数据页已经满了,那就会新申请一个数据页,然后将部分数据挪过去,就会造成页分裂.

覆盖索引

我们回表,主要是因为拿主键的值,假如我们的索引上存了,那就没必要回表了,这个就是覆盖索引的使用场景,可以减少树的搜索次数.

但同时,覆盖索引也遵循最左匹配原则 假如我们建的索引是(name,age) 我们查询的时候最好也按照where name=? and age=?去查,而不是反过来。

关于索引的查询

可以使用explain命令来查看Mysql的查询计划

如explain select * from t where name='张三';

优先看返回的几个字段

1.type 如果是all，代表的全表扫描

2.再看key,是否使用了索引,null表示没有使用索引

3.再看rows，表示扫描的行数

4.再看extra,是否使用Using filesort或者Using temporary

索引需要选择区分度高的,在查询时能够过滤更多的行

以下可以考虑创建索引

经常被查询的列(作为where的条件)

经常用于表连接的列

经常排序分组的列(order by 或者group by)

使用不到索引的情况

(1)通过索引扫描的行记录数超过全表的30%,优化器就不会走索引,而变成全表扫描

(2)联合索引中,第一个查询条件不是最左索引列

(3)联合索引中,第一个索引列使用范围查询,只能使用到部分索引.

(4)模糊查询条件列最左以通配符%开始

(5)两个单列索引,一个用户检索,一个用于排序,那么只会用到一个,可以考虑建立联合索引

(6)查询字段上有索引,但使用了函数计算,这里也要注意隐式函数转换,比如定义的是int类型,你传的查询条件时string类型