求高手给优化SQL语句。谢谢。

xch2671774 2011-02-18 09:17:36

日前开发一个软件，后台数据库选用了MYSQL5.1，软件对于响应速度有着较高的要求。
现发现软件中使用的一些SQL语句执行效率偏慢，不能满足软件应用的要求。肯请高人指点SQL语句优化的思路。
这里先描述其中一个比较快的SQL语句。
有三个相关库表。
t_taskall：存放全部的任务。（任务签出也不删除）
t_tasktemp：任务临时表，存放用户请求签出的任务。它会是t_taskall的一个子集。（用户在签出时添加到该表，处理完后将任务从表中删除，并添加到t_taskdone）
t_taskdone：存放用户处理完成的任务。只存放用户处理完成的任务，它会是t_taskall的一个子集。

每次用户请求任务的时候，会调用一个存储过程，存储过程使用PREPARE执行一个字符串。以检出符合要求的任务。现在每次请求任务好慢啊。。。。。。。。。。我把存储过程中的SQL语句贴出来，请高手看看给点优化建议吧。
表结构：
/*==============================================================*/
/* Table: t_taskall */
/*==============================================================*/
DROP TABLE IF EXISTS `t_taskall`;
CREATE TABLE `t_taskall` (
`ta_tbpbid` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
`ta_tbid` smallint(5) unsigned zerofill NOT NULL,
`ta_id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
`ta_date` date NOT NULL,
`ta_time` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
`ta_set` smallint(5) unsigned zerofill NOT NULL DEFAULT '00000',
`ta_page` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
`ta_ducycbid` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
`ta_duid` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
PRIMARY KEY (`ta_tbpbid`,`ta_tbid`,`ta_id`,`ta_date`),
KEY `ta_index_ttis` (`ta_tbpbid`,`ta_tbid`,`ta_id`,`ta_set`),
KEY `ta_index_ttid` (`ta_tbpbid`,`ta_tbid`,`ta_id`,`ta_duid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE(TO_DAYS (`ta_date`))
(
PARTITION p20110228 VALUES LESS THAN (TO_DAYS('2011-02-28')),
PARTITION p20110315 VALUES LESS THAN (TO_DAYS('2011-03-15'))
) */;

/*==============================================================*/
/* Table: t_tasktemp */
/*==============================================================*/
DROP TABLE IF EXISTS `t_tasktemp`;
CREATE TABLE `t_tasktemp` (
`tt_tbpbid` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
`tt_tbid` smallint(5) unsigned zerofill NOT NULL DEFAULT '00000',
`tt_id` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
`tt_time` tinyint(4) NOT NULL,
`tt_set` smallint(5) unsigned zerofill NOT NULL DEFAULT '00000',
`tt_page` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
`tt_ducycbid` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
`tt_duid` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
`tt_userid` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
PRIMARY KEY (`tt_tbpbid`,`tt_tbid`,`tt_id`),
KEY `tt_index_ttu` (`tt_tbpbid`,`tt_tbid`,`tt_userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*==============================================================*/
/* Table: t_taskdone */
/*==============================================================*/
DROP TABLE IF EXISTS `t_taskdone`;
CREATE TABLE `t_taskdone` (
`td_tbpbid` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
`td_tbid` smallint(5) unsigned zerofill NOT NULL DEFAULT '00000',
`td_id` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
`td_date` date NOT NULL,
`td_time` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
`td_set` smallint(5) unsigned zerofill NOT NULL DEFAULT '00000',
`td_page` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
`td_ducycbid` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
`td_duid` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
`td_userid` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
PRIMARY KEY (`td_tbpbid`,`td_tbid`,`td_id`,`td_date`),
KEY `td_ttu` (`td_tbpbid`,`td_tbid`,`td_userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE(TO_DAYS (`td_date`))
(
PARTITION p20110228 VALUES LESS THAN (TO_DAYS('2011-02-28')),
PARTITION p20110315 VALUES LESS THAN (TO_DAYS('2011-03-15'))
) */;

存储过程里的SQL语句和EXPLAIN结果
explain
SELECT ta_tbpbid, ta_tbid, ta_id, ta_time, ta_piccid, ta_picbid, ta_set, ta_page, ta_ducycbid, ta_duid
FROM t_taskall
WHERE ta_tbpbid = 1 AND ta_tbid = 3 AND ROW(ta_tbpbid, ta_tbid, ta_id) NOT IN(SELECT tt_tbpbid, tt_tbid, tt_id FROM t_tasktemp WHERE tt_tbpbid = 1 AND tt_tbid = 3 UNION DISTINCT SELECT td_tbpbid, td_tbid, td_id FROM t_taskdone WHERE td_tbpbid = 1 AND td_tbid = 3) ORDER BY ta_duid ASC LIMIT 5;
+------+--------------------+------------+------+-------------------------------------+---------------+---------+----------------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+------------+------+-------------------------------------+---------------+---------+----------------+------+-----------------------------------------------------+
| 1 | PRIMARY | t_taskall | ref | PRIMARY,ta_index_ttis,ta_index_ttid | ta_index_ttis | 6 | const,const | 316 | Using where; Using filesort |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 3 | DEPENDENT UNION | t_taskdone | ref | PRIMARY,td_ttu | PRIMARY | 10 | func,func,func | 2 | Using where; Using index |
| NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+------+--------------------+------------+------+-------------------------------------+---------------+---------+----------------+------+-----------------------------------------------------+

...全文

83 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

feixianxxx 2011-02-19

打赏
举报

[code=SQ]SELECT
ta_tbpbid,
ta_tbid,
ta_id, t
a_time,
ta_piccid,
ta_picbid,
ta_set,
ta_page,
ta_ducycbid,
ta_duid
FROM t_taskall a
WHERE ta_tbpbid = 1
AND ta_tbid = 3
AND not exists(select * from t_tasktemp where a.ta_id=tt_id and tt_tbpbid = 1 AND tt_tbid = 3)
and not exists(select * from t_taskdone where a.ta_id=td_id and td_tbpbid = 1 AND td_tbid = 3);
ORDER BY ta_duid ASC LIMIT 5;
--然后再 t_taskall的ta_tbpbid ta_tbid ta_id上分别建立索引[/code]

feixianxxx 2011-02-19

打赏
举报

zuoxingyu 2011-02-18

打赏
举报

select * from t_taskall where not exists(select 1 from t_tasktemp where t_tasktemp.tt_tbpbid = t_taskall.ta_tbpbid)

and not exists(select 1 from t_taskdone where t_taskdone.td_tbpbid= t_taskall.ta_tbpbid);

这样就行

xch2671774 2011-02-18

打赏
举报

上二条测试数据吧。
INSERT INTO `t_taskall` VALUES (0000000001,00004,0000000009,'2011-02-17',00001,00001,001,0000000001,010),(0000000001,00004,0000000008,'2011-02-17',00001,00001,001,0000000001,009),(0000000001,00004,0000000007,'2011-02-17',00001,00001,001,0000000001,008),(0000000001,00004,0000000006,'2011-02-17',00001,00001,001,0000000001,007),(0000000001,00004,0000000005,'2011-02-17',00001,00001,001,0000000001,006),(0000000001,00004,0000000004,'2011-02-17',00001,00001,001,0000000001,005),(0000000001,00004,0000000003,'2011-02-17',00001,00001,001,0000000001,004),(0000000001,00004,0000000002,'2011-02-17',00001,00001,001,0000000001,003),(0000000001,00004,0000000001,'2011-02-17',00001,00001,001,0000000001,002),(0000000001,00003,0000000009,'2011-02-17',00001,00001,001,0000000001,010),(0000000001,00003,0000000008,'2011-02-17',00001,00001,001,0000000001,009),(0000000001,00003,0000000007,'2011-02-17',00001,00001,001,0000000001,008),(0000000001,00003,0000000006,'2011-02-17',00001,00001,001,0000000001,007),(0000000001,00003,0000000005,'2011-02-17',00001,00001,001,0000000001,006),(0000000001,00003,0000000004,'2011-02-17',00001,00001,001,0000000001,005),(0000000001,00003,0000000003,'2011-02-17',00001,00001,001,0000000001,004),(0000000001,00003,0000000002,'2011-02-17',00001,00001,001,0000000001,003),(0000000001,00003,0000000001,'2011-02-17',00001,00001,001,0000000001,002);

INSERT INTO `t_taskdone` VALUES (0000000001,00004,0000000002,'2011-02-17',00001,00001,001,0000000001,003,0000000001),(0000000001,00004,0000000001,'2011-02-17',00001,00001,001,0000000001,002,0000000001),(0000000001,00003,0000000002,'2011-02-17',00001,00001,001,0000000001,003,0000000001),(0000000001,00003,0000000001,'2011-02-17',00001,00001,001,0000000001,002,0000000001);

wwwwb 2011-02-18

打赏
举报

索引情况如何

zuoxingyu 2011-02-18

打赏
举报

WHERE ta_tbpbid = 1 AND ta_tbid = 3 AND ROW(ta_tbpbid, ta_tbid, ta_id) NOT IN

ROW是个啥东西啊？

zuoxingyu 2011-02-18

打赏
举报

[Quote=引用 2 楼 xch2671774 的回复:]
是想要找出没有被处理或请求的任务。

（没有被请求和处理的任务也就是）

只在t_taskall表里有，在t_tasktemp　和 t_taskdone表中都没有的记录。
[/Quote]
select * from `t_taskall` where not exists(select 1 from t_tasktemp　where t_taskall.ta_tbpbid=t_tasktemp.tt_tbpbid) and not exists(select 1 from t_taskdone where t_taskall.ta_tbpbid=t_taskdone.td_tbpbid);

这样就行。

xch2671774 2011-02-18