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) ;