Mysql关联表查询的索引

来源：互联网发布：淘宝手机版卖家中心编辑：程序博客网时间：2024/06/06 08:08

问题

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

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

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1PRIMARY<derived2>ALLNULLNULLNULLNULL28301Using where2DERIVEDdALLNULLNULLNULLNULL29244 2DERIVEDveq_refPRIMARYPRIMARY4soulda_dms.d.parentseqnum1

怀疑是记录数多、字段没有索引引起的问题，就增加专用索引：

create index `ik_voucherdetailquery_detail` on voucherdetail(`parentseqnum`,`accountingtitle`);create index `ik_voucherdetailquery_main` on voucher(`setofbooks`,`ifinitd`,`actualtime`,`status`);

再次执行，执行时间没有变化－－新建的索引并没有起作用！新的执行计划为：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1PRIMARY<derived2>ALLNULLNULLNULLNULL28301Using where2DERIVEDdALLik_voucherdetailquery_detailNULLNULLNULL27675 2DERIVEDveq_refPRIMARYPRIMARY4soulda_dms.d.parentseqnum1 调整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倍！查看执行计划：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEvrangePRIMARY,ik_voucherdetailquery_mainik_voucherdetailquery_main20NULL189Using where; Using index1SIMPLEdrefik_voucherdetailquery_detailik_voucherdetailquery_detail157soulda_dms.v.seqnum,const138Using 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_voucherdetailquerywhere 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倍！其执行计划：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEvrangePRIMARY,ik_voucherdetailquery_mainik_voucherdetailquery_main20NULL189Using where; Using index1SIMPLEdrefik_voucherdetailquery_detailik_voucherdetailquery_detail157soulda_dms.v.seqnum,const138Using where; Using index

至此：SQL查询语句优化结束：1)使用视图整合join语句，以避开“子查询”导致索引失效的问题2)针对join及查询条件的索引－－参见下节

mysql join表的索引

参考《How To Index For Joins With MySQL》

以本例数据表为例。将voucherdetail数据扩大64倍－－1811268条记录。

无索引查询

执行时间960～990ms。执行计划如：

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEdALLNULLNULLNULLNULL1833663Using where1SIMPLEveq_refPRIMARYPRIMARY4soulda_dms.d.parentseqnum1Using where

仅voucher索引1

create index `ik_voucherdetailquery_main` on voucher(`seqnum`,`setofbooks`,`ifinitd`,`actualtime`,`status`);

执行时间960～990ms。执行计划如：
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEdALLNULLNULLNULLNULL1833663Using where1SIMPLEveq_refPRIMARY,ik_voucherdetailquery_mainPRIMARY4soulda_dms.d.parentseqnum1Using 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。执行计划如：
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEdindexik_voucherdetailquery_detailik_voucherdetailquery_detail157NULL1862731Using where; Using index1SIMPLEveq_refPRIMARYPRIMARY4soulda_dms.d.parentseqnum1Using 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。执行计划如：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEdindexik_voucherdetailquery_detailik_voucherdetailquery_detail157NULL1862731Using where; Using index1SIMPLEveq_refPRIMARY,ik_voucherdetailquery_mainPRIMARY4soulda_dms.d.parentseqnum1Using 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左右（？）。执行计划如：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEvrangePRIMARY,ik_voucherdetailquery_mainik_voucherdetailquery_main20NULL189Using where; Using index1SIMPLEdrefik_voucherdetailquery_detailik_voucherdetailquery_detail157soulda_dms.v.seqnum,const9313Using 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的索引等概念不是很清晰，所以目前只能这样在面上对问题进行一些简单的分析。希望今后有一天能真正从原理上解释这里发生的情况，而不是靠猜测。