【原创】MySQL 5.6 MRR 的存储过程完美诠释

Arce

　　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 ;