Mysql关联表查询的索引

问题

    昨天，用户反映我们系统的一个功能效率很低，每次点击需耗时很久。

    对代码跟踪分析，发现主要问题出在一个关联表的查询，该操作需要执行该语句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

    调整SQL语句，去除子查询语句

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的索引等概念不是很清晰，所以目前只能这样在面上对问题进行一些简单的分析。希望今后有一天能真正从原理上解释这里发生的情况，而不是靠猜测。