解释索引优化底层原理

解释索引优化底层原理

• 字段使用索引奥秘：字段能二分查找（有序才能二分查找）

• 规则：组合索引（字段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索引优化知识点
前缀索引实例说明