【原创】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 ; 

 

©著作权归作者所有：来自51CTO博客作者david_yeung的原创作品，如需转载，请注明出处，否则将追究法律责任

优化存储过程innodbMySQL性能优化