---------------------------------------------原始SQL语句如下--------------------------------------------------
select bb.id,bb.cretime,bb.args,cc.src,cc.dst from (select callsessionid,cdruniqueid,var1value,acttime from callsession_acts where acttime >= '2010-7-15 4:00:00' and acttime < '2010-7-15 6:00:00' and var1key = 'filename') aa join (select id,filename,extname,folder,associate,cretime,args from voicefiles where cretime >= '2010-7-15 4:00:00' and cretime < '2010-7-15 6:00:00') bb on bb.associate = aa.callsessionid and aa.var1value = bb.filename and bb.folder = '1' join (select src,dst,userfield,calldate from cdr where calldate >= '2010-7-15 4:00:00' and calldate < '2010-7-15 6:00:00' and disposition = 'ANSWERED')cc on cc.userfield = CONCAT(aa.callsessionid,',',aa.cdruniqueid)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
大体写法就是 select ...(select 表1 where) 表1别名 连接 ...(select 表2 where) 表2别名 连接 ...(select 表3 where) 表3别名......
这个语句的执行过程 估计和 三个表直接连接的执行过程基本是一样的。 写法不同而已。现在问题是 一查询 CPU的内存等 的占用率 百分之百。多查几次,机器崩溃了。也就是说太占用系统资源,表结构设计的很不合理,70000条数据就崩,数据匹配率又很高(匹配率自己模拟了一些,170000条机器都没事,主要是匹配率 没摸你好实际的原因可能。)数据库由于历史原因不能改。
能达到以下执行过程应该可以(执行过程)。但我不知道如何写。
先查 表1 的数据 存放到一个临时表
select ..... 1 放临时表
再查 表2 的数据 存放到一个临时表
select ..... 2 放临时表
最后查 表3 的数据 存放到一个临时表
select ..... 3 放临时表然后 三个临时表连接 ---------------------------------------------------------------------------------------------------------------请问高手,以上执行过程怎么用一条 SQL语句写出来(那东西是个接口。不能用存储过程)
或者用其他方法。我如何达到节省资源的方法。效率又相对过得去。谢谢---------------------------------原是表如下------------------------------------------------------------------
#
# Table structure for table 'callsession_acts'
#CREATE TABLE IF NOT EXISTS callsession_acts (
actid int(16) NOT NULL auto_increment,
callsessionid varchar(64) NOT NULL ,
cdruniqueid varchar(64) NOT NULL DEFAULT '' ,
acttime datetime NOT NULL ,
function varchar(64) NOT NULL ,
var0key varchar(255) NOT NULL DEFAULT '' ,
var0value varchar(255) NOT NULL DEFAULT '' ,
var1key varchar(255) NOT NULL DEFAULT '' ,
var1value varchar(255) NOT NULL DEFAULT '' ,
var2key varchar(255) NOT NULL DEFAULT '' ,
var2value varchar(255) NOT NULL DEFAULT '' ,
var3key varchar(255) NOT NULL DEFAULT '' ,
var3value varchar(255) NOT NULL DEFAULT '' ,
extradata text NOT NULL ,
PRIMARY KEY (actid),
KEY callsessionid (callsessionid),
KEY cdruniqueid (cdruniqueid),
KEY acttime (acttime),
KEY function (function),
KEY var0key (var0key),
KEY var1key (var0key),
KEY var2key (var0key),
KEY var3key (var0key)
);
#
# Table structure for table 'cdr'
#CREATE TABLE IF NOT EXISTS cdr (
id int(11) NOT NULL auto_increment,
calldate datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ,
clid varchar(80) NOT NULL DEFAULT '' ,
src varchar(80) NOT NULL DEFAULT '' ,
dst varchar(80) NOT NULL DEFAULT '' ,
dcontext varchar(80) NOT NULL DEFAULT '' ,
channel varchar(80) NOT NULL DEFAULT '' ,
dstchannel varchar(80) NOT NULL DEFAULT '' ,
lastapp varchar(80) NOT NULL DEFAULT '' ,
lastdata varchar(80) NOT NULL DEFAULT '' ,
duration int(11) NOT NULL DEFAULT '0' ,
billsec int(11) NOT NULL DEFAULT '0' ,
disposition varchar(45) NOT NULL DEFAULT '' ,
amaflags int(11) NOT NULL DEFAULT '0' ,
accountcode varchar(20) NOT NULL DEFAULT '' ,
userfield varchar(255) NOT NULL DEFAULT '' ,
uniqueid varchar(255) NOT NULL DEFAULT '' ,
PRIMARY KEY (id),
KEY amaflags (amaflags),
KEY calldate (calldate),
KEY accountcode (accountcode),
KEY dcontext (dcontext),
KEY src (src),
KEY dst (dst),
KEY disposition (disposition),
KEY uniqueid (uniqueid)
);
#
# Table structure for table 'voicefiles'
#CREATE TABLE IF NOT EXISTS voicefiles (
id int(12) NOT NULL auto_increment,
filename varchar(255) NOT NULL ,
extname varchar(255) NOT NULL DEFAULT '' ,
folder varchar(255) NOT NULL DEFAULT '' ,
cretime datetime ,
description varchar(255) NOT NULL DEFAULT '' ,
label varchar(32) NOT NULL ,
associate varchar(255) NOT NULL DEFAULT '' ,
args varchar(255) NOT NULL DEFAULT '' ,
readonly int(1) NOT NULL DEFAULT '0' ,
mailprocessed int(1) NOT NULL DEFAULT '0' ,
PRIMARY KEY (id),
UNIQUE KEY label (label,folder,filename),
KEY cretime (cretime),
KEY mailprocessed (mailprocessed)
);
select bb.id,bb.cretime,bb.args,cc.src,cc.dst from (select callsessionid,cdruniqueid,var1value,acttime from callsession_acts where acttime >= '2010-7-15 4:00:00' and acttime < '2010-7-15 6:00:00' and var1key = 'filename') aa join (select id,filename,extname,folder,associate,cretime,args from voicefiles where cretime >= '2010-7-15 4:00:00' and cretime < '2010-7-15 6:00:00') bb on bb.associate = aa.callsessionid and aa.var1value = bb.filename and bb.folder = '1' join (select src,dst,userfield,calldate from cdr where calldate >= '2010-7-15 4:00:00' and calldate < '2010-7-15 6:00:00' and disposition = 'ANSWERED')cc on cc.userfield = CONCAT(aa.callsessionid,',',aa.cdruniqueid)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
大体写法就是 select ...(select 表1 where) 表1别名 连接 ...(select 表2 where) 表2别名 连接 ...(select 表3 where) 表3别名......
这个语句的执行过程 估计和 三个表直接连接的执行过程基本是一样的。 写法不同而已。现在问题是 一查询 CPU的内存等 的占用率 百分之百。多查几次,机器崩溃了。也就是说太占用系统资源,表结构设计的很不合理,70000条数据就崩,数据匹配率又很高(匹配率自己模拟了一些,170000条机器都没事,主要是匹配率 没摸你好实际的原因可能。)数据库由于历史原因不能改。
能达到以下执行过程应该可以(执行过程)。但我不知道如何写。
先查 表1 的数据 存放到一个临时表
select ..... 1 放临时表
再查 表2 的数据 存放到一个临时表
select ..... 2 放临时表
最后查 表3 的数据 存放到一个临时表
select ..... 3 放临时表然后 三个临时表连接 ---------------------------------------------------------------------------------------------------------------请问高手,以上执行过程怎么用一条 SQL语句写出来(那东西是个接口。不能用存储过程)
或者用其他方法。我如何达到节省资源的方法。效率又相对过得去。谢谢---------------------------------原是表如下------------------------------------------------------------------
#
# Table structure for table 'callsession_acts'
#CREATE TABLE IF NOT EXISTS callsession_acts (
actid int(16) NOT NULL auto_increment,
callsessionid varchar(64) NOT NULL ,
cdruniqueid varchar(64) NOT NULL DEFAULT '' ,
acttime datetime NOT NULL ,
function varchar(64) NOT NULL ,
var0key varchar(255) NOT NULL DEFAULT '' ,
var0value varchar(255) NOT NULL DEFAULT '' ,
var1key varchar(255) NOT NULL DEFAULT '' ,
var1value varchar(255) NOT NULL DEFAULT '' ,
var2key varchar(255) NOT NULL DEFAULT '' ,
var2value varchar(255) NOT NULL DEFAULT '' ,
var3key varchar(255) NOT NULL DEFAULT '' ,
var3value varchar(255) NOT NULL DEFAULT '' ,
extradata text NOT NULL ,
PRIMARY KEY (actid),
KEY callsessionid (callsessionid),
KEY cdruniqueid (cdruniqueid),
KEY acttime (acttime),
KEY function (function),
KEY var0key (var0key),
KEY var1key (var0key),
KEY var2key (var0key),
KEY var3key (var0key)
);
#
# Table structure for table 'cdr'
#CREATE TABLE IF NOT EXISTS cdr (
id int(11) NOT NULL auto_increment,
calldate datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ,
clid varchar(80) NOT NULL DEFAULT '' ,
src varchar(80) NOT NULL DEFAULT '' ,
dst varchar(80) NOT NULL DEFAULT '' ,
dcontext varchar(80) NOT NULL DEFAULT '' ,
channel varchar(80) NOT NULL DEFAULT '' ,
dstchannel varchar(80) NOT NULL DEFAULT '' ,
lastapp varchar(80) NOT NULL DEFAULT '' ,
lastdata varchar(80) NOT NULL DEFAULT '' ,
duration int(11) NOT NULL DEFAULT '0' ,
billsec int(11) NOT NULL DEFAULT '0' ,
disposition varchar(45) NOT NULL DEFAULT '' ,
amaflags int(11) NOT NULL DEFAULT '0' ,
accountcode varchar(20) NOT NULL DEFAULT '' ,
userfield varchar(255) NOT NULL DEFAULT '' ,
uniqueid varchar(255) NOT NULL DEFAULT '' ,
PRIMARY KEY (id),
KEY amaflags (amaflags),
KEY calldate (calldate),
KEY accountcode (accountcode),
KEY dcontext (dcontext),
KEY src (src),
KEY dst (dst),
KEY disposition (disposition),
KEY uniqueid (uniqueid)
);
#
# Table structure for table 'voicefiles'
#CREATE TABLE IF NOT EXISTS voicefiles (
id int(12) NOT NULL auto_increment,
filename varchar(255) NOT NULL ,
extname varchar(255) NOT NULL DEFAULT '' ,
folder varchar(255) NOT NULL DEFAULT '' ,
cretime datetime ,
description varchar(255) NOT NULL DEFAULT '' ,
label varchar(32) NOT NULL ,
associate varchar(255) NOT NULL DEFAULT '' ,
args varchar(255) NOT NULL DEFAULT '' ,
readonly int(1) NOT NULL DEFAULT '0' ,
mailprocessed int(1) NOT NULL DEFAULT '0' ,
PRIMARY KEY (id),
UNIQUE KEY label (label,folder,filename),
KEY cretime (cretime),
KEY mailprocessed (mailprocessed)
);
create table temp1 as select * from table1 where MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
select cc.FsName from (create TEMPORARY Table cc select * from FnCdr)
select e.FsName from (create TEMPORARY Table cc select * from FnCdr) e
上面这俩写法都不行
ADD INDEX (`var1key`(255),`acttime`); ALTER TABLE `voicefiles`
ADD INDEX (`folder`(255),`cretime`); ALTER TABLE `cdr`
ADD INDEX (`disposition`(45),`calldate`); explain
select bb.id,bb.cretime,bb.args,cc.src,cc.dst from
(select callsessionid,cdruniqueid,var1value,acttime from callsession_acts where acttime >= '2010-7-15 4:00:00' and acttime < '2010-7-15 6:00:00' and var1key = 'filename') aa
join
(select id,filename,extname,folder,associate,cretime,args from voicefiles where cretime >= '2010-7-15 4:00:00' and cretime < '2010-7-15 6:00:00' and folder = '1') bb
on bb.associate = aa.callsessionid and aa.var1value = bb.filename
join
(select src,dst,userfield,calldate from cdr where calldate >= '2010-7-15 4:00:00' and calldate < '2010-7-15 6:00:00' and disposition = 'ANSWERED') cc
on cc.userfield = CONCAT(aa.callsessionid,',',aa.cdruniqueid);看看这样效率会不会高一点,把explain 结果传上来看看,缺少数据不好分析
1,PRIMARY,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Impossible WHERE noticed after reading const tables
4,DERIVED,cdr,range,calldate,disposition,calldate,8,NULL,1,Using where
3,DERIVED,voicefiles,range,cretime,cretime,9,NULL,1,Using where
2,DERIVED,callsession_acts,range,acttime,acttime,8,NULL,1,Using where
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><html>
<head>
<title>explain
select bb.id,bb.cretime,bb.args,cc.src,cc.dst from
(select callsessionid,cdruniqueid,var1value,acttime from callsession_acts where acttime >= '2010-7-15 4:00:00' and acttime < '2010-7-15 6:00:00' and var1key = 'filename') aa
join
(select id,filename,extname,folder,associate,cretime,args from voicefiles where cretime >= '2010-7-15 4:00:00' and cretime < '2010-7-15 6:00:00' and folder = '1') bb
on bb.associate = aa.callsessionid and aa.var1value = bb.filename
join
(select src,dst,userfield,calldate from cdr where calldate >= '2010-7-15 4:00:00' and calldate < '2010-7-15 6:00:00' and disposition = 'ANSWERED') cc
on cc.userfield = CONCAT(aa.callsessionid,',',aa.cdruniqueid)</title>
<meta name="GENERATOR" content="MySQL-Front 2.5">
<style type="text/css"><!--
.header {background-color: #ECE9D8;}
th {vertical-align: top;}
td {vertical-align: top; background-color: #FFFFFF;}
//--></style>
</head><body><h3>explain
select bb.id,bb.cretime,bb.args,cc.src,cc.dst from
(select callsessionid,cdruniqueid,var1value,acttime from callsession_acts where acttime >= '2010-7-15 4:00:00' and acttime < '2010-7-15 6:00:00' and var1key = 'filename') aa
join
(select id,filename,extname,folder,associate,cretime,args from voicefiles where cretime >= '2010-7-15 4:00:00' and cretime < '2010-7-15 6:00:00' and folder = '1') bb
on bb.associate = aa.callsessionid and aa.var1value = bb.filename
join
(select src,dst,userfield,calldate from cdr where calldate >= '2010-7-15 4:00:00' and calldate < '2010-7-15 6:00:00' and disposition = 'ANSWERED') cc
on cc.userfield = CONCAT(aa.callsessionid,',',aa.cdruniqueid) (4 Records)</h3><table border=1>
<tr class="header">
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
<th>ref</th>
<th>rows</th>
<th>Extra</th>
</tr>
<tr>
<td>1 </td>
<td>PRIMARY </td>
<td>NULL </td>
<td>NULL </td>
<td>NULL </td>
<td>NULL </td>
<td>NULL </td>
<td>NULL </td>
<td>NULL </td>
<td>Impossible WHERE noticed after reading const tables </td>
</tr>
<tr>
<td>4 </td>
<td>DERIVED </td>
<td>cdr </td>
<td>range </td>
<td>calldate,disposition </td>
<td>calldate </td>
<td>8 </td>
<td>NULL </td>
<td>1 </td>
<td>Using where </td>
</tr>
<tr>
<td>3 </td>
<td>DERIVED </td>
<td>voicefiles </td>
<td>range </td>
<td>cretime </td>
<td>cretime </td>
<td>9 </td>
<td>NULL </td>
<td>1 </td>
<td>Using where </td>
</tr>
<tr>
<td>2 </td>
<td>DERIVED </td>
<td>callsession_acts </td>
<td>range </td>
<td>acttime </td>
<td>acttime </td>
<td>8 </td>
<td>NULL </td>
<td>1 </td>
<td>Using where </td>
</tr>
</table><br><br>
<i>generated 2010-11-29 16:09:41 by <a href="http://www.mysqlfront.de/">MySQL-Front 2.5</a></i></body></html>
select bb.id,bb.cretime,bb.args,cc.src,cc.dst from
(select callsessionid,cdruniqueid,var1value,acttime from callsession_acts where acttime >= '2010-7-15 4:00:00' and acttime < '2010-7-15 6:00:00' and var1key = 'filename') aa
join
(select id,filename,extname,folder,associate,cretime,args from voicefiles where cretime >= '2010-7-15 4:00:00' and cretime < '2010-7-15 6:00:00' and folder = '1') bb
on bb.associate = aa.callsessionid and aa.var1value = bb.filename
join
(select src,dst,userfield,calldate from cdr where calldate >= '2010-7-15 4:00:00' and calldate < '2010-7-15 6:00:00' and disposition = 'ANSWERED') cc
on cc.userfield = CONCAT(aa.callsessionid,',',aa.cdruniqueid);
另外您要先添加三个索引,在测试:
ALTER TABLE `callsession_acts`
ADD INDEX (`var1key`(255),`acttime`); ALTER TABLE `voicefiles`
ADD INDEX (`folder`(255),`cretime`); ALTER TABLE `cdr`
ADD INDEX (`disposition`(45),`calldate`);