索引的常见模型
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类型