tbl_direct_pos_201506 表有 190 万数据，DDL：
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='交易月表模板';
关于该表的一个慢查询日志如下：
# Time: 150701 15:45:28
# User@Host: test[test] @ localhost [127.0.0.1] Id: 1
# Query_time: 2.478195 Lock_time: 0.010007 Rows_sent: 20 Rows_examined: 450612
SET timestamp=1435736728;
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, t.trace_num
limit 0, 20;
日志中可以看出该 sql 的执行时间是 2.478 s。
我们来查看一下该 sql 的执行计划：
| id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
| 1 |
SIMPLE |
t |
range |
PRIMARY |
PRIMARY |
24 |
? |
299392 |
Using index condition; Using filesort |
| 1 |
SIMPLE |
r |
eq_ref |
PRIMARY |
PRIMARY |
41 |
msp.t.recv_ins_code |
1 |
Using where |
| 1 |
SIMPLE |
tt |
eq_ref |
PRIMARY |
PRIMARY |
14 |
msp.t.before_trans_code |
1 |
Using where |
执行计划分析：
Using filesort。是的，看到它，说明我们的查询需要优化了：文件排序是通过相应的排序算法，将取得的数据在内存中进行排序。
MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空的键而已。MyISAM 的索引默认为 B-TREE。也就是说，主键在这里相当于一个普通的 B-TREE。
该 sql 一个 where 字段，四个 order by 字段，都在主键里边呀，而且 order by 的顺序完全符 |