MySQL 5.6 MRR 的存储过程完美诠释

MySQL 5.6 即将发布， 5.6对优化器方面做了诸多优化。我这次主要解释 MRR（MULTI-RANGE-READ）。

我用存储过程解释了这一过程的改变。大家细心体会去吧。

我们针对语句：

select log_time from person where nick_name = 'Lucy';

表结构为：

CREATE TABLE `person` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `nick_name` varchar(40) NOT NULL,  `log_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),  KEY `idx_nick_name` (`nick_name`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

首先是MySQL 5.5.

DELIMITER $$USE `ytt`$$DROP PROCEDURE IF EXISTS `sp_range_scan5_5`$$CREATE DEFINER=`admin`@`%` PROCEDURE `sp_range_scan5_5`()BEGIN      -- Sample sql statement is below.      -- select log_time from person where nick_name = 'Lucy';      DECLARE i INT UNSIGNED DEFAULT 0;      DECLARE cnt INT UNSIGNED DEFAULT 0;      SET @result = '';          SELECT COUNT(1) INTO cnt FROM person WHERE nick_name = 'Lucy';          loop1:WHILE i < cnt      DO        SET @stmt = CONCAT('select id into @v_id from person where nick_name = ''Lucy'' order by nick_name asc limit ',i,',1');        PREPARE s1 FROM @stmt;        EXECUTE s1;              SET @result = CONCAT(@result,'select log_time from person where id = @v_id');        SET @result = CONCAT(@result,' union all ');        SET i = i + 1;      END WHILE loop1;      SET @result = SUBSTR(@result,1,CHAR_LENGTH(@result)-CHAR_LENGTH(' union all '));      PREPARE s1 FROM @result;      EXECUTE s1;      DROP PREPARE s1;      SET @result = NULL;     END$$DELIMITER ;

下来是MySQL 5.6.

DELIMITER $$USE `ytt`$$DROP PROCEDURE IF EXISTS `sp_range_scan5_6`$$CREATE DEFINER=`admin`@`%` PROCEDURE `sp_range_scan5_6`()BEGIN      -- Sample sql statement is below.      -- select log_time from person where nick_name = 'Lucy';      DECLARE i INT UNSIGNED DEFAULT 0;      DECLARE cnt INT UNSIGNED DEFAULT 0;      DECLARE ids TEXT;        SET ids = '';      SELECT COUNT(1) INTO cnt FROM person WHERE nick_name = 'Lucy';          loop1:WHILE i < cnt      DO        SET @stmt = CONCAT('select id into @v_id from person where nick_name = ''Lucy''         order by nick_name asc limit ',i,',1');        PREPARE s1 FROM @stmt;        EXECUTE s1;        SET ids = CONCAT(ids,@v_id,',');        SET i = i + 1;      END WHILE loop1;      SET ids = CONCAT('(',SUBSTR(ids,1,CHAR_LENGTH(ids)-1),')');      SET @result = CONCAT('select log_time from person where id in',ids);      PREPARE s1 FROM @result;      EXECUTE s1;      DROP PREPARE s1;      SET @result = NULL;     END$$DELIMITER ;

更多相关文章

随机推荐