MySQL 5.6 MRR的存儲過程完美诠釋
MySQL 5.6 即將發布， 5.6對優化器方面做了諸多優化。
我這次主要解釋MRR（MULTI-RANGE-READ）。
我用存儲過程解釋了這一過程的改變。大家細心體會去吧。
我們針對語句：
[sql]
select log_time from person where nick_name = 'Lucy';
表結構為：
[sql]
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`), www.2cto.com
KEY `idx_nick_name` (`nick_name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
首先是MySQL 5.5.
[sql]
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 ')); www.2cto.com
PREPARE s1 FROM @result;
EXECUTE s1;
DROP PREPARE s1;
SET @result = NULL;
END$$
DELIMITER ;
下來是MySQL 5.6.
[sql]
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'' www.2cto.com
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 ;
