Chinaunix首页 | 论坛 | 博客
  • 博客访问: 4149652
  • 博文数量: 240
  • 博客积分: 11504
  • 博客等级: 上将
  • 技术积分: 4277
  • 用 户 组: 普通用户
  • 注册时间: 2006-12-28 14:24
文章分类

全部博文(240)

分类: Mysql/postgreSQL

2012-08-24 10:59:07

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 ;



阅读(5188) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~