SQL优化示例

1.表和索引的创建

建表语句如下：

create table test03(
	    a1 int(4) not null,
    	a2 int(4) not null,
    	a3 int(4) not null,
    	a4 int(4) not null
    );

建立索引的语句如下：建立a1,a2,a3,a4的符合索引

create index on test03 a1,a2,a3,a4(index_a1,a2,a3,a4);

2.分析索引优化得到情况

2.1

推荐写法：

explain select a1,a2 from test03 where a1 = 4 and a2 = 4 and a3 = 5 and a4 = 6;

分析执行情况：usingindex，使用到了四个索引

索引的使用顺序和符合索引的顺序是一致的。

2.2

explain select a1,a2,a3,a4 from test03  where  a4 = 4 and  a3 = 23 and a2 = 21 and a1 = 78;

分析执行情况：同样用到了四个索引，原因是：虽然编写索引的顺序不一致，但是在sql真正执行之前经过了sql优化器的调整，结果与上面是一致的，使用到了四个索引。

2.3

explain select a1,a2,a3,a4 from test03 where a1 = 1 and a2 = 1 and a4 = 3 order by a3;

分析执行情况：实际使用到的索引有两个，分别是a1和a2，没有用到a4是因为跨列使用了索引，不符合复合索引的最左原则。a1，a2这两个索引字段是起了作用的，所以不要回表查询，usingIndex，但是a4跨列使用，造成了该索引失效，需要回表查询，usingwhere；以上可以通过key_length看出来。

2.4

explain select a1,a2,a3,a4 from test03 where a1 = 1 and a4 = 4 order by a3;

分析执行情况：使用到的索引：a1，出现了usingfilesort（文件内排序），原因是跨列使用索引导致索引失效（ where和order by连起来看索引是否连续）

2.5

explain select a1,a2,a3,a4 from test03 where a1 = 1 and a4 = 5 order by a2,a3;

执行结果分析：以上sql使用的索引只有一个a1，where和order by加起来的索引是连续的符合最左原则（a1，a2,a3）所以没有出现using filesort

总结
（1）如果索引（a,b,c,d）符合索引和使用的顺序完全一致，且不跨列使用，则符合所以你全部生效，如果跨列使用，则只有部分有效，要符合最左原则。
（2）where 和order by连起来不要跨列使用，原因是会使用using filesort文件内排序，大大降低排序速率。