• 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 ;





  • 相关阅读:
    Java线程状态和关闭线程的正确姿势
    Eclipse最全的编码设置
    Maven中的src/test/java颜色不正常
    web.xml 各版本的 Schema 头部声明
    程序员面试系列
    几款强大的网页生成工具
    endnoteX9批量导入enw
    关于Ubuntu16.04里安装elasticsearch-head显示集群健康值未连接的问题
    U盘做了系统盘,写入如硬盘映像过程中终止,怎么格式化硬盘重新写入?
    使用jupyter notebook出现kernel error
  • 原文地址:https://www.cnblogs.com/secbook/p/2655264.html
Copyright © 2020-2023  润新知