MariaDB社区

标题: MySQL 5.6 MRR 的存储过程完美诠释 [打印本页]
作者: yueliangdao0608    时间: 2013-4-19 11:39
标题: MySQL 5.6 MRR 的存储过程完美诠释

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 ;
复制代码




欢迎光临 MariaDB社区 (http://123.56.88.72/) Powered by Discuz! X3.2