mysql where和order mysqlwhere和order by索引

 一、MySQL排序分类

#1.通过索引扫描生成有序的结果

#2.使用文件排序(filesort)

 

1、索引扫描执行过程

#SQL语句中，WHERE子句和ORDER BY子句都可以使用索引：
    WHERE子句使用索引避免全表扫描，ORDER BY子句使用索引避免filesort（用“避免”可能有些欠妥，某些场景下全表扫描、filesort未必比走索引慢），以提高查询效率。
            （索引既有查询的能力，也有排序的功能）

虽然索引能提高查询效率，但在一条SQL里，对于一张表的查询 一次只能使用一个索引，也就是说当WHERE子句与ORDER BY子句要使用的索引不一致时，MySQL只能使用其中一个索引(B+树)

#一个既有WHERE又有ORDER BY的SQL中，使用索引有三个可能的场景：
    1、只用于WHERE子句 筛选出满足条件的数据

    2、只用于ORDER BY子句 返回排序后的结果

    3、既用于WHERE又用于ORDER BY，筛选出满足条件的数据并返回排序后的结果

 

#MySQL能为排序与查询使用相同的索引
-------------------------------------------------
key a_b_c(a,b,d)

#order by 能使用索引的
order by a
order by a,b
order by a,b,c
order by a DESC,b DESC,c DESC


#如果where使用索引的最左最左前缀定义为变量，则 order by能使用索引
where a = const order by b,c
where a = const and b = const order by c
where a = const and order by b,c
where a = const and b>const order by b,c    #b>const,order by必须有b


#不能使用索引进行排序的
where d = const order by b,c       #没有火车头
where a = const order by c          #没有B，中间车厢断开
where a = const order by a,d       #d不是索引的一部分
where a in （..）order by b,c        #对于排序说，多个相等条件也是范围查询

 

练习

create table tb2(
    age int,
    birth timestamp not null);

insert into tb2(age,birth) values(20,now());
insert into tb2(age,birth) values(21,now());
insert into tb2(age,birth) values(22,now());

create index idx_A_B on tb2(age,birth);

需要的表

 

 

 

 

 

2、文件排序

#人误以为它会：将一张非常大的表放入磁盘再进行排序，filesort仅仅是排序而已，filesort是否会使用磁盘取决于它操作的数据量大小

#总结来说就是，filesort按排序方式来划分 分为两种：
    1.数据量小时，在内存中快排

    2.数据量大时，在内存中分块快排，再在磁盘上将各个块做归并（涉及到磁盘IO）


#根据回表查询的次数，filesort又可以分为两种方式：

    1.回表读取两次数据(two-pass)：两次传输排序

    2.回表读取一次数据(single-pass)：单次传输排序