博客

以下针对数据库存储引擎为InnoDB来说，假设针对字段a、b、c建立联合索引。

1、为什么索引支持最左索引前缀原则，构建了几个B+树？

只有一个B+树，这个B+树的非叶子节点按 a、b、c值顺序构造，叶子节点包括索引项和主键的记录。如果进行 select a,b,c from table 查询是不用进行回表，直接可以从索引表中获得结果。

2、使用联合索引时需注意设置的索引是否被正确使用上，举例说明:

• 全值匹配时，用到了索引，where子句几个搜索条件顺序调换时也会用到索引，因为Mysql中有查询优化器，会自动优化查询顺序

   select * from table_name where a ='' and b='' and c=''; //走索引查询
   select * from table_name where  b='' and a ='' and c=''; //索引查询
  

• 部分值匹配时，只要条件中有最左索引项就会用到索引

   select * from table_name where a ='' and b=''; //走索引查询
   select * from table_name where a ='' and c=''; //走索引查询
  

• 条件中没有最左索引，不会用到索引，全表扫描

  select * from table_name where b='' and c=''; //全表扫描
  

• 匹配范围值，可以对最左边的列进行范围查询，范围查询指<、>、between等

    select * from table_name where a<'' and c=''; //索引查询
    select * from table_name where a between '' and ''; //索引查询
  
    select * from table_name where a like 'A%'; //走索引查询
    select * from table_name where  a like '%A'//全表查询
    select * from table_name where  a like '%A%'//全表查询
  

• 精确匹配最左列 and 范围匹配另外一列 ，会使用索引

    select * from table_name where a='' and c>''; //索引查询
  

• order排序

    select * from table_name order by a,b,c; //索引查询
  

3、索引失效情况总结

• !=、<> 会索引导致失效，走全表扫描
• or连接条件，当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
• like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
• 组合索引，不是使用第一列索引，索引失效。
• 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
• 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引
• 对索引字段进行计算操作、字段上使用函数