解释索引优化底层原理
-
字段使用索引奥秘:字段能二分查找(有序才能二分查找)
-
规则:组合索引(字段1,字段2…),只有固定字段1的值字段2才是有序的,只有固定字段2的值字段3才是有序的…以此类推这样才能二分查找。
-
范围查找之后、组合索引中不遵循最左匹配原则、like模糊查询中的中缀和后缀等等利用不了索引。Why?例子a、b、c三个字段组成的联合索引(a,b,c),范围查找如
select * from abc where a=20 and b>30 and c=2;
a和b使用到了索引,c没用到,原因:b查找到大于30的在B+树的同一层,而在同一层多个结点中,b不同,在每个符合 a=20 and b>30的结点中,c是无序的,无法使用二分查找以此决定向左走或者向右走,因此c没用到索引;select * from abc where a=20 and c=2;
只有a能够用到索引,a查找到等于20的,在a等于20的结点中,b没有固定住值,因此以b作为参考c是无序的,不能二分查找,也就使用不了索引…相关视频 -
难点:
众所周知,字段使用了运算符是用不了索引的,但是下面的例子出乎意料:
select * from actor where actor_id+1=5;
未使用索引
select actor_id from actor where actor_id+1=5;
使用了name字段的idx_actor_last_name辅助索引
但查看表中建的索引:
猜测:在 actor_id字段建立的主键索引对应B+树查找麻烦,而name字段的辅助索引叶子结点存的是 actor_id值,且有序,此场景下链表的遍历可能效率比二分查找更高。 -
select * from actor where actor_id=4+1
不算使用表达式:
-
尽量使用主键查询,而不是其他索引,因此主键查询不会触发回表查询。这句很好理解,那么这个主键应该怎么设置呢?推荐与业务无关的自增id作为mysql主键。什么?业务无关?自增?
业务无关:总的来说就是应用场景的变更,此时被认定为业务唯一标识的字段不唯一,例如:
人员表中工号是唯一的,为什么不用工号作为主键?
回答:分析了需求后,发现,人员表中工号存在“回收复用”的情况。比如,局长的工号永远都是00001,今年是李局长,明年李局长升迁了、退休了,张局长赴任,工号还是00001。如果用工号作为主键,那么子表引用人员表工号字段,就会出现以下情况:
1、2008年工号为00001的用户操作了100条业务,该工号00001代表李局长;
2、2009年工号为00001的用户操作了80条业务,该工号00001代表张局长。
最后统计工号为00001的用户一共操作了多少业务的时候,会得到工号为00001的张局长操作了180条业务。
因此,工号虽然在库表中唯一,但是他违背了“所有主键标实体都是惟一的”这一完整性要求,因此,不能将工号作为主键。
-
索引选择中组合索引:
1.组合索引即使在order by中符合,在where条件中不符合也不会走索引,详细见索引扫描
2.组合索引即使在order by中升序或降序要统一,不能够一个升一个降,详细见索引扫描 -
union all,in,or 都能够使用索引,但是推荐使用in
explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id =2;
- or 走索引条件:
如果是单列索引,or会使用索引
如果是组合素引:
1、全部的列都是组合索引,那么会使用全部列所对应的索引
2、如果部分列是组合索引,那么不会走索引
相关文章:
mysql索引优化知识点
前缀索引实例说明