问题
昨天,用户反映我们系统的一个功能效率很低,每次点击需耗时很久。
对代码跟踪分析,发现主要问题出在一个关联表的查询,该操作需要执行该语句20次左右,每次约0.2s左右,累积到数秒。
因此问题集中到该SQL语句的优化。
SQL优化
系统采用Ibatis做持久化,该查询对应的sqlMap是:
select * from (
select
`d`.`seqnum` AS `voucherdetailseqnum`,
`v`.`seqnum` AS `voucherseqnum`,
`v`.`setofbooks` AS `setofbooks`,
`d`.`accountingtitle` AS `accountingtitle`,
`d`.`auxaudit` AS `auxaudit`,
`v`.`actualtime` AS `actualtime`,
`v`.`ifinitd` AS `ifinitd`,
`v`.`status` AS `status`
from
(`voucher` `v` join `voucherdetail` `d` on((`v`.`seqnum` = `d`.`parentseqnum`)))
) view_anyname
其中voucher主表记录数为8040,voucherdetail子表记录数为28301。
CREATE TABLE `voucher` (
`seqnum` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水号',
`setofbooks` int(11) NOT NULL COMMENT '账套',
`actualtime` datetime DEFAULT NULL COMMENT '凭证日期',
`ifinitd` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否年初凭证',
`status` varchar(1) DEFAULT NULL COMMENT '状态',
...
PRIMARY KEY (`seqnum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `voucherdetail` (
`seqnum` int(11) NOT NULL AUTO_INCREMENT COMMENT '流水号',
`parentseqnum` int(11) NOT NULL COMMENT '凭证流水号',
`accountingtitle` varchar(50) DEFAULT NULL COMMENT '科目',
...
PRIMARY KEY (`seqnum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
利用《SQL Manager for MySQL》直接分析查询语句:
select count(*) from (
select
`d`.`seqnum` AS `voucherdetailseqnum`,
`v`.`seqnum` AS `voucherseqnum`,
`v`.`setofbooks` AS `setofbooks`,
`d`.`accountingtitle` AS `accountingtitle`,
`d`.`auxaudit` AS `auxaudit`,
`v`.`actualtime` AS `actualtime`,
`v`.`ifinitd` AS `ifinitd`,
`v`.`status` AS `status`
from
(`voucher` `v` join `voucherdetail` `d` on((`v`.`seqnum` = `d`.`parentseqnum`)))
) view_anyname
where setofbooks=1 and ifinitd=false and Actualtime Between '2012-01-01 00:00:00' and '2012-02-01 00:00:00'
and Status In('A','C') and Accountingtitle = "1001";
耗时176ms左右。其执行计划为:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 28301 | Using where |
2 | DERIVED | d | ALL | NULL | NULL | NULL | NULL | 29244 | |
2 | DERIVED | v | eq_ref | PRIMARY | PRIMARY | 4 | soulda_dms.d.parentseqnum | 1 |
怀疑是记录数多、字段没有索引引起的问题,就增加专用索引:
create index `ik_voucherdetailquery_detail` on voucherdetail(`parentseqnum`,`accountingtitle`);
create index `ik_voucherdetailquery_main` on voucher(`setofbooks`,`ifinitd`,`actualtime`,`status`);
再次执行,执行时间没有变化--新建的索引并没有起作用! 新的执行计划为:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 28301 | Using where |
2 | DERIVED | d | ALL | ik_voucherdetailquery_detail | NULL | NULL | NULL | 27675 | |
2 | DERIVED | v | eq_ref | PRIMARY | PRIMARY | 4 | soulda_dms.d.parentseqnum | 1 |
select count(*)
from `voucher` `v` ,`voucherdetail` `d`
where `v`.`seqnum` = `d`.`parentseqnum`
and `v`.setofbooks=1 and `v`.ifinitd=false
and `v`.Actualtime Between '2012-01-01 00:00:00' and '2012-02-01 00:00:00'
and `v`.Status In('A','C') and `d`.Accountingtitle = "1001";
再次执行,执行时间30ms,效率提升接近6倍! 查看执行计划:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | v | range | PRIMARY,ik_voucherdetailquery_main | ik_voucherdetailquery_main | 20 | NULL | 189 | Using where; Using index |
1 | SIMPLE | d | ref | ik_voucherdetailquery_detail | ik_voucherdetailquery_detail | 157 | soulda_dms.v.seqnum,const | 138 | Using where; Using index |
索引起作用了--没有子查询,索引得到正确使用。
受ibatis的限制,我们不能在sqlMap的where语句中指定《`v`.setofbooks》,因此考虑创建视图、利用视图查询CREATE VIEW `v_voucherdetailquery` AS
select
`d`.`seqnum` AS `voucherdetailseqnum`,
`v`.`seqnum` AS `voucherseqnum`,
`v`.`setofbooks` AS `setofbooks`,
`d`.`accountingtitle` AS `accountingtitle`,
`d`.`auxaudit` AS `auxaudit`,
`v`.`actualtime` AS `actualtime`,
`v`.`ifinitd` AS `ifinitd`,
`v`.`status` AS `status`,
...
from
(`voucher` `v` join `voucherdetail` `d`)
where
(`v`.`seqnum` = `d`.`parentseqnum`);
select count(*) from v_voucherdetailquery
where setofbooks=1 and ifinitd=false and Actualtime Between '2012-01-01 00:00:00' and '2012-02-01 00:00:00'
and Status In('A','C') and Accountingtitle = "1001";
新查询语句能利用索引,执行效率与没有子查询语句时相同--提升接近6倍! 其执行计划:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | v | range | PRIMARY,ik_voucherdetailquery_main | ik_voucherdetailquery_main | 20 | NULL | 189 | Using where; Using index |
1 | SIMPLE | d | ref | ik_voucherdetailquery_detail | ik_voucherdetailquery_detail | 157 | soulda_dms.v.seqnum,const | 138 | Using where; Using index |
至此:SQL查询语句优化结束:1)使用视图整合join语句,以避开“子查询”导致索引失效的问题2)针对join及查询条件的索引--参见下节
mysql join表的索引
参考《How To Index For Joins With MySQL》
以本例数据表为例。将voucherdetail数据扩大64倍--1811268条记录。
无索引查询
执行时间960~990ms。执行计划如:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 1833663 | Using where |
1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 4 | soulda_dms.d.parentseqnum | 1 | Using where |
仅voucher索引1
create index `ik_voucherdetailquery_main` on voucher(`seqnum`,`setofbooks`,`ifinitd`,`actualtime`,`status`);
执行时间960~990ms。执行计划如:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 1833663 | Using where |
1 | SIMPLE | v | eq_ref | PRIMARY,ik_voucherdetailquery_main | PRIMARY | 4 | soulda_dms.d.parentseqnum | 1 | Using where |
仅voucher索引2
create index `ik_voucherdetailquery_main` on voucher(`setofbooks`,`ifinitd`,`actualtime`,`status`);
执行时间、执行计划不变。
仅voucherdetail索引
create index `ik_voucherdetailquery_detail` on voucherdetail(`parentseqnum`,`accountingtitle`);
执行时间730~750ms。执行计划如:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | d | index | ik_voucherdetailquery_detail | ik_voucherdetailquery_detail | 157 | NULL | 1862731 | Using where; Using index |
1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 4 | soulda_dms.d.parentseqnum | 1 | Using where |
voucher索引+voucherdetail索引1(voucher主键为组合索引首字段)
create index `ik_voucherdetailquery_detail` on voucherdetail(`parentseqnum`,`accountingtitle`);
create index `ik_voucherdetailquery_main` on voucher(`seqnum`,`setofbooks`,`ifinitd`,`actualtime`,`status`);
执行时间730~750ms。执行计划如:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | d | index | ik_voucherdetailquery_detail | ik_voucherdetailquery_detail | 157 | NULL | 1862731 | Using where; Using index |
1 | SIMPLE | v | eq_ref | PRIMARY,ik_voucherdetailquery_main | PRIMARY | 4 | soulda_dms.d.parentseqnum | 1 | Using where |
voucher索引+voucherdetail索引2(将voucher主键移至组合索引末端)
create index `ik_voucherdetailquery_detail` on voucherdetail(`parentseqnum`,`accountingtitle`);
create index `ik_voucherdetailquery_main` on voucher(`setofbooks`,`ifinitd`,`actualtime`,`status`,`seqnum`);
前1~3次执行时间30ms左右,后续16ms左右(?)。执行计划如:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | v | range | PRIMARY,ik_voucherdetailquery_main | ik_voucherdetailquery_main | 20 | NULL | 189 | Using where; Using index |
1 | SIMPLE | d | ref | ik_voucherdetailquery_detail | ik_voucherdetailquery_detail | 157 | soulda_dms.v.seqnum,const | 9313 | Using where; Using index |
voucher索引+voucherdetail索引3(将voucher主键从组合索引中去除)
create index `ik_voucherdetailquery_detail` on voucherdetail(`parentseqnum`,`accountingtitle`);
create index `ik_voucherdetailquery_main` on voucher(`setofbooks`,`ifinitd`,`actualtime`,`status`);
执行时间、计划均保持不变!
分析说明:
1)join表的索引应按join顺序,从最左边的表开始,先保证首次筛选能充分利用索引,得到结果子集后,利用该子集对下一表进行检索,此时《关联键+查询字段》的符合索引能加快检索,下一表依此类推。
2)如果索引未能按照表join的顺序创建(见1),则数据量大的表的索引可能更有效。如《仅voucher索引1》、《仅voucher索引2》两例的主表索引都失效,且执行效率不如《仅voucherdetail索引》--该表索引好像失效了。
3)索引列的选择非常重要,如《voucher索引+voucherdetail索引1(voucher主键为组合索引首字段)》的
create index `ik_voucherdetailquery_main` on voucher(`seqnum`,`setofbooks`,`ifinitd`,`actualtime`,`status`);
以`seqnum`(主键)作为组合索引的第一字段,致使该索引失效--参见对应的执行计划。
将`seqnum`(主键)移至组合索引的最后字段,组合索引生效,查询效率极大提升。(《voucher索引+voucherdetail索引2(将voucher主键移至组合索引末端)》)
从组合索引中去除主键,效果不变--《voucher索引+voucherdetail索引3(将voucher主键从组合索引中去除)》。
由于对Mysql的索引等概念不是很清晰,所以目前只能这样在面上对问题进行一些简单的分析。希望今后有一天能真正从原理上解释这里发生的情况,而不是靠猜测。