对于mysql中B-Tree复合索引中各字段的ASC/DESC注意点以及ORDER BY优化等

CREATE TABLE `tbl_direct_pos_201506` (
  `acq_ins_code` char(13) NOT NULL DEFAULT '' COMMENT '机构代码',
  `trace_num` char(6) NOT NULL DEFAULT '' COMMENT '跟踪号',
  `trans_datetime` char(10) NOT NULL DEFAULT '' COMMENT '交易时间',
  `process_flag` char(1) DEFAULT NULL COMMENT '处理标识',
  `rev_flag` char(1) DEFAULT NULL COMMENT '接收标识',
  `before_trans_code` char(3) DEFAULT NULL COMMENT '交易类型',
  `trans_amt` decimal(15,3) DEFAULT NULL COMMENT '交易金额',
  `acct_num` char(21) DEFAULT NULL COMMENT '卡号',
  `mer_type` char(4) DEFAULT NULL COMMENT '商户类型',
  `recv_ins_code` char(13) DEFAULT NULL COMMENT '发卡行代码',
  `retrivl_ref_num` char(12) DEFAULT NULL COMMENT '检索参考号',
  `resp_auth_code` char(6) DEFAULT NULL COMMENT '授权码',
  `resp_code` char(2) DEFAULT NULL COMMENT '应答码',
  `term_id` char(8) DEFAULT NULL COMMENT '终端代码',
  `mer_code` char(15) DEFAULT NULL COMMENT '商户代码',
  `mer_addr_name` char(40) DEFAULT NULL COMMENT '商户名称和地址，前 25 字节是名称，后面是地址',
  `self_define` varchar(300) DEFAULT NULL COMMENT '第 259 字节是卡片类型',
  `sys_date` char(8) NOT NULL DEFAULT '' COMMENT '交易日期',
  `sa_sav2` varchar(300) DEFAULT NULL COMMENT '第 243 字节是 DCC 标识',
  `rec_create_time` datetime DEFAULT NULL COMMENT '联机入库时间',
  `rec_update_time` datetime DEFAULT NULL COMMENT '最后修改时间',
  PRIMARY KEY (`sys_date`,`trans_datetime`,`acq_ins_code`,`trace_num`),
  KEY `idx_direct_pos_create_time` (`rec_create_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='交易月表模板';

表中有200万条数据，DDL。

该sql的执行时间为2.47s，看下sql的执行计划：

看到Using filesort就说明sql需要优化了，在此补充一下Extra中的类型含义：

Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。

Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。

Range checked for each Record（index map:#） ：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。

Using filesort ：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。

Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。

Using temporary ：看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。

Where used ：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

 

该 sql 一个 where 字段，四个 order by 字段，都在主键里边呀，而且 order by 的顺序完全符合最左前缀原则，为什么还要 filesort？
Mysql索引创建手册里如是说：
索引列的定义可以跟随 ASC 或者 DESC。这些关键字允许为未来扩展用于指定升序或降序索引值存储。这个语法会被解析但却被忽略。索引列总是以升序排列。——也就是说你写了不会报错，但写了白写。
这样看来，我们的主键没起排序作用，原因就在于我们的主键是各主键字段 asc 存储， order by 里 desc 和 asc(默认是 asc) 混用。为了验证这个说法，我们把该 order by 换为和主键一致的 asc：

select substr(t.acq_ins_code, 3) merAcqInsCode, t.mer_code, t.term_id, substr(t.mer_addr_name, 1, 12) merName,
				tt.trans_desc, t.rev_flag, t.trans_amt, concat(substr(t.sys_date, 1, 4), t.trans_datetime) transTime,t.before_trans_code,
				t.acct_num, t.retrivl_ref_num,  t.resp_code, t.resp_auth_code,  r.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_datetime,
				case substr(t.sa_sav2,259,1)  when 1 then '借记卡' when 2 then '贷记卡'
				when 3 then '准贷记卡' when 4 then '私有预付卡' else '' end  cardType,
				case 
					when locate('VIS',t.sa_sav2) > 0 then 'VISA' 
					when locate('JCB',t.sa_sav2) > 0 then 'JCB' 
					when locate('DNC',t.sa_sav2) > 0 then '大莱卡' 
					when locate('CUP',t.sa_sav2) > 0 then '银联境内卡' 
					when locate('UPI',t.sa_sav2) > 0 then '银联*卡'
					else '' end cardBrand 
			from tbl_direct_pos_201506 t
			left join trans_recv_ins r on r.recv_ins_code = t.recv_ins_code
			left join tbl_trans_type tt on tt.trans_code = t.before_trans_code
			where t.sys_date between '20150622' and '20150628' 
			order by
			 t.sys_date, t.trans_datetime, t.acq_ins_code, t.trace_num
			limit 0, 20;

执行时间为0.023s，再来看下执行计划：

Using filesort没有了。但是业务要求 sys_date 和 trans_datetime降序，其余两个字段倒无所谓，这时候我们可以将orde by后条件改为一致，即：

select substr(t.acq_ins_code, 3) merAcqInsCode, t.mer_code, t.term_id, substr(t.mer_addr_name, 1, 12) merName,
				tt.trans_desc, t.rev_flag, t.trans_amt, concat(substr(t.sys_date, 1, 4), t.trans_datetime) transTime,t.before_trans_code,
				t.acct_num, t.retrivl_ref_num,  t.resp_code, t.resp_auth_code,  r.recv_ins_name,t.acq_ins_code,t.trace_num,t.trans_datetime,
				case substr(t.sa_sav2,259,1)  when 1 then '借记卡' when 2 then '贷记卡'
				when 3 then '准贷记卡' when 4 then '私有预付卡' else '' end  cardType,
				case 
					when locate('VIS',t.sa_sav2) > 0 then 'VISA' 
					when locate('JCB',t.sa_sav2) > 0 then 'JCB' 
					when locate('DNC',t.sa_sav2) > 0 then '大莱卡' 
					when locate('CUP',t.sa_sav2) > 0 then '银联境内卡' 
					when locate('UPI',t.sa_sav2) > 0 then '银联*卡'
					else '' end cardBrand 
			from tbl_direct_pos_201506 t
			left join trans_recv_ins r on r.recv_ins_code = t.recv_ins_code
			left join tbl_trans_type tt on tt.trans_code = t.before_trans_code
			where t.sys_date between '20150622' and '20150628' 
			order by
			 t.sys_date desc, t.trans_datetime desc, t.acq_ins_code desc, t.trace_num desc 
			limit 0, 20;

运行结果0.029s，完美！