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 ;





posted @ 2012-08-24 10:49  MXi4oyu  阅读(320)  评论(0编辑  收藏  举报