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


    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 ;





  • 相关阅读:
    【翻译/介绍】jump consistent hash:零内存消耗,均匀,快速,简洁,来自Google的一致性哈希算法 [2015-03-13]
    现代密码学实践指南[2015年]
    本博客迁走了
    高性能web系统的架构和系统优化
    vs 2013调试的时候重启的解决方案
    年会与项目管理
    javascript 关闭窗口,弹出新窗口并带有确认关闭对话框解决办法
    成长
    POCO exception
    通过OpenGL ES在iOS平台实践增强现实(二)
  • 原文地址:https://www.cnblogs.com/secbook/p/2655264.html
Copyright © 2020-2023  润新知