mysql 百万级数据存储过程有什么优化方法吗

DROP PROCEDURE IF EXISTS `proc_battleinfo`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_battleinfo`(IN userid INT,IN gid INT)
BEGIN
    SET NAMES utf8mb4;
    SELECT drawid,roomid,bureau,gameplayer,basescore,playmethod,
        max(case when chairid=0 THEN UNIX_TIMESTAMP(concludetime) END) AS concludetime,
        max(case when chairid=0 THEN nickname END) AS na0,
        max(case when chairid=1 THEN nickname END) AS na1,
        max(case when chairid=2 THEN nickname END) AS na2,
        max(case when chairid=3 THEN nickname END) AS na3,
        max(case when chairid=0 THEN id END) AS uid0,
        max(case when chairid=1 THEN id END) AS uid1,
        max(case when chairid=2 THEN id END) AS uid2,
        max(case when chairid=3 THEN id END) AS uid3,
        sum(case when chairid=0 THEN score END) AS score0,
        sum(case when chairid=1 THEN score END) AS score1,
        sum(case when chairid=2 THEN score END) AS score2,
        sum(case when chairid=3 THEN score END) AS score3,
        max(case when chairid=0 THEN CONCAT(UNIX_TIMESTAMP(starttime),gid,roomid) END) AS replay
        FROM (SELECT a.drawid,b.id,b.chairid,b.score,b.starttime,b.concludetime,b.roomid,b.nickname,b.bureau,b.gameplayer,b.basescore,b.playmethod
        FROM (SELECT distinct drawid FROM draw_score WHERE id=userid AND gameid = gid
        AND concludetime >= date_sub(NOW(),INTERVAL 48 HOUR)) AS a left join draw_score b
        ON b.drawid = a.drawid) AS aa GROUP BY drawid ORDER BY drawid DESC limit 10;

END
;;

DELIMITER ;

mysql 百万级数据存储过程有什么优化方法吗

各位大神，看看这个存储过程能优化吗

5 个解决方案

#1

已经解决了，不需要优化存储过程，为表建立索引就可以极大的优化查询速度了
mysql 百万级数据存储过程有什么优化方法吗

这是建立索引后的explain

#2

楼主自已解决了问题，赞一个；

希望楼主常来论坛，帮助其他的网友解决问题。

mysql 百万级数据存储过程有什么优化方法吗

#3

楼上牛人多来指点下。 mysql 百万级数据存储过程有什么优化方法吗

#4

原来索引都没有嗦

#5

建立合适的索引确实查询效率可以加快很多，不然要全表进行检索

#1