各位大哥大姐好!
最近小弟被mysql数据库查询问题给击倒了,还请大家多多帮忙。问题如下:
DELIMITER $$DROP PROCEDURE IF EXISTS `gmr21`.`SpListCountFromLogCopy`$$CREATE DEFINER=`gmr21`@`%` PROCEDURE `SpListCountFromLogCopy`(in nareaId varchar(10),in customerId varchar(32),
in provinceId varchar(10),in cityId varchar(10),IN cosmosUserId char(32), IN asUserId char(32),
IN startTime int(8),IN endTime int(8), IN videoKeyword varchar(100), IN adeType char, IN minCount INT, IN maxCount INT,IN pageNo INT, INOUT pageLimit INT, IN orderBy INT, IN orderDesc INT, OUT totalRecords INT)
BEGIN
SET @cosmosUserId = cosmosUserId;
set @asuserId=asUserId;
SET @refStartTime=startTime;
SET @refEndTime=endTime;
SET @refVideoKeyword = videoKeyword;
SET @minCount=minCount;
SET @maxCount = maxCount;
SET @nareaId=nareaId;
SET @customerId=customerId;
SET @provinceId=provinceId;
SET @cityId=cityId;
SET @uTrader = 10;
SET @userId = cosmosUserId;
SET @uCount = 0;
select count(ID) into @uCount from tb_sys_gmr where USER_ID=@userId;
if @uCount > 0 then
select TRADER_ID into @uTrader from tb_sys_gmr where USER_ID=@userId;
end if;
IF @minCount = '' THEN
SET @minCount = 1;
END IF;
IF @maxCount = '' THEN
SET @maxCount = 99999999;
END IF;
if adeType=1 then
SET @nisads = 2;
elseif adeType=2 then
SET @nisads = 1;
elseif adeType=0 then
SET @nisads =0;
end if;
if @refStartTime='' then
set @refStartTime='20080520';
end if;
if @refEndTime='' then
set @refEndTime='20201231';
end if;
SET @datetimes = @refStartTime;
SET @locationStr = '';
call SpSearchCustomerInfo(@nareaId,@customerId,@cosmosUserId,@provinceId,@cityId);
drop temporary table if exists tempUserDevice;
create temporary table tempUserDevice(id char(32), strsnid varchar(15),strname2 varchar(24), INDEX strsnid (strsnid),INDEX strname2 (strname2) );
set @datetimes=date(@datetimes);
set @refEndTime=date(@refEndTime); SET @LISTSQL=CONCAT('INSERT INTO tempUserDevice(strname2,id, strsnid)
select distinct(cus.strname2),dv.ID,trim(dv.DEVICE_CODE)
from tb_dev_device as dv
left join tb_dev_device_user devuser on devuser.DEVICE_ID=dv.ID
left join tb_sys_gmr gmr on gmr.USER_ID=devuser.USER_ID
left join tab_customer cus on cus.ntraderid=gmr.TRADER_ID
group by dv.ID order by dv.DEVICE_CODE ');
PREPARE _stmt FROM @LISTSQL;
EXECUTE _stmt;
DEALLOCATE PREPARE _stmt; SELECT musicInfo.strtitle as ADE_NAME , musicInfo.strfileurl, musicInfo.strmusicid as ADE_CODE, musicInfo.nplaytime, count(plylog.id) as totalCount,userdevice.strname2
from tb_ply_log as plylog
left join tempUserDevice as userdevice on userdevice.strsnid=plylog.DEVICE_CODE
left JOIN tab_music as musicInfo ON musicInfo.strmusicid=plylog.MUSIC_CODE
WHERE date(plylog.PLAY_START)>=@datetimes and date(plylog.PLAY_START)<=@refEndTime and musicInfo.nisads=@nisads GROUP BY userdevice.strname2;
END$$DELIMITER ;
这是小弟写的存储过程 其中tb_ply_log 表中有80万条数据tab_music表中有2万多条数据,其他的表数据不是很多,但是在最后一个结果集速度是相当的慢。在执行select * from tb_ply_log;这一个语句也需要10秒以上的时间,索引也加了,还请哥哥姐姐帮帮忙!
最近小弟被mysql数据库查询问题给击倒了,还请大家多多帮忙。问题如下:
DELIMITER $$DROP PROCEDURE IF EXISTS `gmr21`.`SpListCountFromLogCopy`$$CREATE DEFINER=`gmr21`@`%` PROCEDURE `SpListCountFromLogCopy`(in nareaId varchar(10),in customerId varchar(32),
in provinceId varchar(10),in cityId varchar(10),IN cosmosUserId char(32), IN asUserId char(32),
IN startTime int(8),IN endTime int(8), IN videoKeyword varchar(100), IN adeType char, IN minCount INT, IN maxCount INT,IN pageNo INT, INOUT pageLimit INT, IN orderBy INT, IN orderDesc INT, OUT totalRecords INT)
BEGIN
SET @cosmosUserId = cosmosUserId;
set @asuserId=asUserId;
SET @refStartTime=startTime;
SET @refEndTime=endTime;
SET @refVideoKeyword = videoKeyword;
SET @minCount=minCount;
SET @maxCount = maxCount;
SET @nareaId=nareaId;
SET @customerId=customerId;
SET @provinceId=provinceId;
SET @cityId=cityId;
SET @uTrader = 10;
SET @userId = cosmosUserId;
SET @uCount = 0;
select count(ID) into @uCount from tb_sys_gmr where USER_ID=@userId;
if @uCount > 0 then
select TRADER_ID into @uTrader from tb_sys_gmr where USER_ID=@userId;
end if;
IF @minCount = '' THEN
SET @minCount = 1;
END IF;
IF @maxCount = '' THEN
SET @maxCount = 99999999;
END IF;
if adeType=1 then
SET @nisads = 2;
elseif adeType=2 then
SET @nisads = 1;
elseif adeType=0 then
SET @nisads =0;
end if;
if @refStartTime='' then
set @refStartTime='20080520';
end if;
if @refEndTime='' then
set @refEndTime='20201231';
end if;
SET @datetimes = @refStartTime;
SET @locationStr = '';
call SpSearchCustomerInfo(@nareaId,@customerId,@cosmosUserId,@provinceId,@cityId);
drop temporary table if exists tempUserDevice;
create temporary table tempUserDevice(id char(32), strsnid varchar(15),strname2 varchar(24), INDEX strsnid (strsnid),INDEX strname2 (strname2) );
set @datetimes=date(@datetimes);
set @refEndTime=date(@refEndTime); SET @LISTSQL=CONCAT('INSERT INTO tempUserDevice(strname2,id, strsnid)
select distinct(cus.strname2),dv.ID,trim(dv.DEVICE_CODE)
from tb_dev_device as dv
left join tb_dev_device_user devuser on devuser.DEVICE_ID=dv.ID
left join tb_sys_gmr gmr on gmr.USER_ID=devuser.USER_ID
left join tab_customer cus on cus.ntraderid=gmr.TRADER_ID
group by dv.ID order by dv.DEVICE_CODE ');
PREPARE _stmt FROM @LISTSQL;
EXECUTE _stmt;
DEALLOCATE PREPARE _stmt; SELECT musicInfo.strtitle as ADE_NAME , musicInfo.strfileurl, musicInfo.strmusicid as ADE_CODE, musicInfo.nplaytime, count(plylog.id) as totalCount,userdevice.strname2
from tb_ply_log as plylog
left join tempUserDevice as userdevice on userdevice.strsnid=plylog.DEVICE_CODE
left JOIN tab_music as musicInfo ON musicInfo.strmusicid=plylog.MUSIC_CODE
WHERE date(plylog.PLAY_START)>=@datetimes and date(plylog.PLAY_START)<=@refEndTime and musicInfo.nisads=@nisads GROUP BY userdevice.strname2;
END$$DELIMITER ;
这是小弟写的存储过程 其中tb_ply_log 表中有80万条数据tab_music表中有2万多条数据,其他的表数据不是很多,但是在最后一个结果集速度是相当的慢。在执行select * from tb_ply_log;这一个语句也需要10秒以上的时间,索引也加了,还请哥哥姐姐帮帮忙!
解决方案 »
- 同求一条sql
- windows下pgAdmin导入数据的问题
- mysql中文查询乱码问题
- 找出两个表不一样的记录进行更新
- 关于postgresql的类型转换问题
- 关于MYSQL的两个问题,高手们请给点提示
- win“服务”中无法启动sql服务,显示“错误1058,原因可能是被禁用或相关的设备没有启动”
- 如何将MYSQL的数据库不要建在默认的DATA目录下,而建在其它目录下呢?
- 关于安装mysql后的启动问题。50分相送
- mysql workbench 6.3ce:语法没错可运行但是一直显示红叉,重启后消失,怎么回事啊,强迫症忍不了啊T_T
- 如何写SQL实现这样的需求?
- 急求个SQL语句。。在线等。
----------------------
这样的数据量不大,除非你每条记录有几M吧(估计你是不可能),这样的话,那说明你查询用的索引不合理
建议你explain SQL语句看看吧
from tb_ply_log as plylog
left join tempUserDevice as userdevice on userdevice.strsnid=plylog.DEVICE_CODE
left JOIN tab_music as musicInfo ON musicInfo.strmusicid=plylog.MUSIC_CODE
WHERE date(plylog.PLAY_START)>=@datetimes and date(plylog.PLAY_START) <=@refEndTime and musicInfo.nisads=@nisads GROUP BY userdevice.strname2;
我explain了一下 EXTRA 显示Using where; Using temporary; Using filesort 这个我一直没弄好,当我把GROUP BY userdevice.strname2 去掉后就只有显示Using where请大侠给解释一下或者找出问题所在,谢谢!
plylog ALL MUSIC_CODE NULL NULL NULL 802729 Using where; Using temporary; Using filesort
musicInfo eq_ref PRIMARY PRIMARY 33 plylog.MUSIC_CODE 1 Using where
这是explain 出来的东西!
这个表是全表扫描了,问题很明显啦
自己根据应用及数据的分布情况调整下优化下吧