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