MySQL使用存储过程随机获取数据表中若干条记录

DROP PROCEDURE `get_rand_result`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_rand_result`(IN `i` INT UNSIGNED) NOT DETERMINISTIC READS SQL DATA SQL SECURITY DEFINER BEGIN

DECLARE mx INT;
DECLARE mi INT;
DECLARE var TEXT DEFAULT '';

SELECT MAX(`id`) INTO @mx FROM `users_profile`;
SELECT MIN(`id`) INTO @mi FROM `users_profile`;

DROP TEMPORARY TABLE IF EXISTS `tmp_table`;
CREATE TEMPORARY TABLE `tmp_table` (
  `id` bigint(20) unsigned NOT NULL
) ENGINE = MEMORY;

WHILE i>0 DO
  SELECT FLOOR(@mi + RAND() * (@mx-@mi)) INTO @var;
  INSERT INTO `tmp_table` VALUES(@var);
  SET i=i-1;
END WHILE;

SELECT `users_profile`.* FROM `users_profile` INNER JOIN `tmp_table` ON `users_profile`.`id` = `tmp_table`.`id`;

END

数据表 `users_profile` 记录数 100W+
存储过程中最后一条SQL查询性能优化过程:in(±80ms) > right join(±40ms) > inner join(±3ms) ;

发表评论

电子邮件地址不会被公开。 必填项已用*标注