联合索引查询原理和全值匹配规则

  1. 假设根据学生班级，学生姓名，科目名称建立联合索引。sql语句 select * from student_score where class_name =’1班’ and student_nama = ‘张三’ and subject_name = ‘数学’。
  2. where后面的字段就是索引字段，而且用的是等号，根据等值匹配规则，是完全可以走索引的。

  3. 查询过程先去索引页，先根据班级名称找，定位到数据页后，班级名称相同后再根据姓名找，定位到数据页后，姓名相同再根据科目找。

    常见索引使用规则

  4. 最左侧列匹配。假设联合索引是key(class_name,student_name,subject_name).写查询语句的时候

select from student_score where class_name =’’ and student_name = ‘’是可以走索引的，要写
select from student_score where student_name = ‘’不能走索引，因为在联合索引的B+树内，只能先查class_name,不能先查student_name。

  1. 最左前缀匹配原则，用like语法，like ‘1%’ 可以走索引，like ‘%1’不能走索引
  2. 范围查找。select * from student_score where class_name > ‘1’ and class_name < ‘5’可以走索引

select * from student_score where class_name > ‘1’ and class_name < ‘5’ and student_name > ‘’不能走索引，，where语句里有范围查询，对联合索引最左侧的列进行范围查找才能用索引。

  1. 等值匹配+范围匹配。

select * from student_score where class_name =’’ and student_name > ‘’ and student_name <” 可以走索引，student_name < ‘’不能走索引

使用order by排序的时候，如果使用索引

  1. select * from table where xxx=xxx order by xxx,执行是基于where语句通过索引筛选除一波数据，接着放到内存中，再通过排序算法按照某个字段走排序，再把排序返回。
  2. 这样数据量很多的话会很慢。通常情况下建立索引index(xx1,xx2,xx3)联合索引，索引树本来就是依次按照xx1,xx2,xx3三个字段排序，查询时可以直接得到有序的数据。再根据查到数据的主键去聚簇索引里回表查所有的数据。

  3. order by最好加上acs，desc。但是如果order by语句有的字段不在联合索引里，或者对order by语句用了复杂函数，就没法用索引去排序了。

    使用group by分组的时候，如何使用索引

  4. group by和order by使用索引原理和条件类似。本质都是在group by和order by之后的字段顺序和联合索引中的最左侧开始的字段顺序一致，然后充分利用索引树一排序的特性，快速的根据排序好的数据执行后续操作。

    回表查询和覆盖索引

  5. 回表查询是索引B+树里的节点仅仅包含了索引里的几个字段值以及主键值，即使根据索引树找到了需要的数据，那也仅仅是部分值，如果select * 还是需要回表操作，根据主键去主键的聚簇索引里去找所有字段数据。

  6. 有的查询语句，例如select from table order by xx1,xx2,xx3 .但因为select 仍需要全表扫描. 如果select * from table order by xx1,xx2,xx3 limit 10.这种会先根据索引树找到10条，再对这10条数据在聚簇索引里查找10次。

  7. 覆盖索引。类似select xx1,xx2,xx3 from table order by xx1,xx2,xx3.查询只需要联合索引里的几个字段的值，只要扫描联合索引的索引树就可以了。这种需要的字段直接在索引树里就能提取出来，不需要回表查询。

    设计索引需要考虑的因素

  8. 一般是先设计表结构，然后写业务代码，根据sql语句设计索引。根据where，order by，group by设计，索引字段最好覆盖where，order by，group by，遵循最左原则。尽可能保证每个sql语句都可以用到索引。

  9. 一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，这样才能发挥B+树二分法的优势。
  10. 用字段的类型比较小的列来设计索引，这样字段本身的值占用磁盘的空间小。这样索引时性能会好一点。
  11. 如果字段类型很大的话，又不得不使用索引，可以对前XX位建立索引。叫做前缀索引。但是这种只有where可以用得上，order by和group by用不上。
  12. 索引不要设计得太多，建议两三个联合索引就可以覆盖掉表里的所有数据了。
  13. 建议主键一定是自增的，不要使用UUID。使用UUID会导致聚簇索引频繁的分裂。