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

yueliangdao0608

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

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

我们针对语句：

1. select log_time from person where nick_name = 'Lucy';

复制代码

表结构为：

1. CREATE TABLE `person` (
   
2.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   
3.   `nick_name` varchar(40) NOT NULL,
   
4.   `log_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   
5.   PRIMARY KEY (`id`),
   
6.   KEY `idx_nick_name` (`nick_name`)
   
7. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 ;

复制代码

首先是MySQL 5.5

1. DELIMITER $
   
2. USE `ytt`$
   
3. DROP PROCEDURE IF EXISTS `sp_range_scan5_5`$
   
4. CREATE DEFINER=`admin`@`%` PROCEDURE `sp_range_scan5_5`()
   
5. BEGIN
   
6.       -- Sample sql statement is below.
   
7.       -- select log_time from person where nick_name = 'Lucy';
   
8.       DECLARE i INT UNSIGNED DEFAULT 0;
   
9.       DECLARE cnt INT UNSIGNED DEFAULT 0;
   
10.       SET @result = '';     
    
11.       SELECT COUNT(1) INTO cnt FROM person WHERE nick_name = 'Lucy';
    
12.      
    
13.       loop1:WHILE i < cnt
    
14.       DO
    
15.         SET @stmt = CONCAT('select id into @v_id from person where nick_name = ''Lucy'' order by nick_name asc limit ',i,',1');
    
16.         PREPARE s1 FROM @stmt;
    
17.         EXECUTE s1;
    
18.       
    
19.         SET @result = CONCAT(@result,'select log_time from person where id = @v_id');
    
20.         SET @result = CONCAT(@result,' union all ');
    
21.         SET i = i + 1;
    
22.       END WHILE loop1;
    
23.       SET @result = SUBSTR(@result,1,CHAR_LENGTH(@result)-CHAR_LENGTH(' union all '));
    
24.       PREPARE s1 FROM @result;
    
25.       EXECUTE s1;
    
26.       DROP PREPARE s1;
    
27.       SET @result = NULL;  
    
28.     END$
    
29. DELIMITER ;

复制代码

下来是MySQL 5.6

1. DELIMITER $
   
2. USE `ytt`$
   
3. DROP PROCEDURE IF EXISTS `sp_range_scan5_6`$
   
4. CREATE DEFINER=`admin`@`%` PROCEDURE `sp_range_scan5_6`()
   
5. BEGIN
   
6.       -- Sample sql statement is below.
   
7.       -- select log_time from person where nick_name = 'Lucy';
   
8.       DECLARE i INT UNSIGNED DEFAULT 0;
   
9.       DECLARE cnt INT UNSIGNED DEFAULT 0;
   
10.       DECLARE ids TEXT;   
    
11.       SET ids = '';
    
12.       SELECT COUNT(1) INTO cnt FROM person WHERE nick_name = 'Lucy';
    
13.      
    
14.       loop1:WHILE i < cnt
    
15.       DO
    
16.         SET @stmt = CONCAT('select id into @v_id from person where nick_name = ''Lucy''
    
17.          order by nick_name asc limit ',i,',1');
    
18.         PREPARE s1 FROM @stmt;
    
19.         EXECUTE s1;
    
20.         SET ids = CONCAT(ids,@v_id,',');
    
21.         SET i = i + 1;
    
22.       END WHILE loop1;
    
23.       SET ids = CONCAT('(',SUBSTR(ids,1,CHAR_LENGTH(ids)-1),')');
    
24.       SET @result = CONCAT('select log_time from person where id in',ids);
    
25.       PREPARE s1 FROM @result;
    
26.       EXECUTE s1;
    
27.       DROP PREPARE s1;
    
28.       SET @result = NULL;  
    
29.     END$
    
30. DELIMITER ;

复制代码