MySQL5.7.11关联查询速度很慢测试（和5.5比较）

ccxpts

一、建立测试环境：MySQL5.7.11
mysql> create table a (
  a1 char(18) not null default '',
  a2 char(20),
  primary key(a1)
);

mysql> create table b (
  b1 char(18) not null default '',
  b2 char(20),
  primary key(b1)
);

mysql> DELIMITER $$
DROP PROCEDURE IF EXISTS proc_auto_insertdata$$
CREATE PROCEDURE proc_auto_insertdata()
BEGIN
  DECLARE init_data INTEGER DEFAULT 1;
  DECLARE str char(6);

  delete from a;
  delete from b;
  WHILE init_data <= 2000 DO
    IF init_data<10 THEN SET str = concat('000', init_data);
    ELSEIF init_data<100 THEN SET str = concat('00', init_data);
    ELSEIF init_data<1000 THEN SET str = concat('0', init_data);
    ELSE SET str = init_data;
    END IF;

    INSERT INTO a VALUES(str, CONCAT('用户A-',init_data));
    INSERT INTO b VALUES(init_data, CONCAT('用户B-',init_data));
    SET init_data = init_data + 1;
  END WHILE;
END$$
DELIMITER ;

mysql> CALL proc_auto_insertdata();

mysql> DELIMITER $$
DROP FUNCTION IF EXISTS get_code$$
CREATE DEFINER = CURRENT_USER FUNCTION get_code(in_code CHAR(4))
  RETURNS CHAR(4) NO SQL
BEGIN
  DECLARE str CHAR(4) default '';
  DECLARE init_data INTEGER DEFAULT 1;

  SET init_data = in_code;
  IF init_data<10 THEN SET str = concat('000', init_data);
  ELSEIF init_data<100 THEN SET str = concat('00', init_data);
  ELSEIF init_data<1000 THEN SET str = concat('0', init_data);
  ELSE SET str = init_data;
  END IF;

  RETURN str;  
END;$$
DELIMITER ;
===========================================================================
二、测试join连接一：
mysql> explain select a1, a2, b2 from a
join b ON a1=get_code(b1);

MySQL 5.7.11 (1 min 25.67 sec)
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
     filtered: 100.00
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                |
+-------+------+-----------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`a`.`a1` AS `a1`,
`test`.`a`.`a2` AS `a2`,`test`.`b`.`b2` AS `b2`
from `test`.`a` join `test`.`b`
where (`test`.`a`.`a1` = `get_code`(`test`.`b`.`b1`)) |
+-------+------+-----------------------------------------------------------+

MySQL 5.5.28 (47.88 sec)
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
        Extra: Using where; Using join buffer
2 rows in set (0.00 sec)

===========================================================================
三、测试join连接二：
mysql> explain select AA.code, AA.a2, BB.b2 from (
  select a1 as code, a2 from a
) as AA
join (
  select get_code(b1) as code, b2 from b
) as BB ON AA.code = BB.code;

MySQL 5.7.11  (1 min 25.18 sec)
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
     filtered: 100.00
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 se

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                               |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`a`.`a1` AS `code`,`test`.`a`.`a2` AS `a2`,`test`.`b`.`b2` AS `b2` from `test`.`a` join `test`.`b` where (`test`.`a`.`a1` = `get_code`(`test`.`b`.`b1`)) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

MySQL5.5.28 (0.60 sec)
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
        Extra: Using where; Using join buffer
*************************** 3. row ***************************
           id: 3
  select_type: DERIVED
        table: b
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
        Extra:
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED
        table: a
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
        Extra:
4 rows in set (0.04 sec)
======================================================================

ccxpts

今天安装了一套Percona Server 5.6.29，按上述SQL测试了一把，情况如下：
二、测试join连接一：
mysql> explain select a1, a2, b2 from a
join b ON a1=get_code(b1);
MySQL 5.6.29  (56.83 sec)
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
        Extra: Using where; Using join buffer (Block Nested Loop)

===========================================================================
三、测试join连接二：
mysql> explain select AA.code, AA.a2, BB.b2 from (
  select a1 as code, a2 from a
) as AA
join (
  select get_code(b1) as code, b2 from b
) as BB ON AA.code = BB.code;
MySQL 5.6.29  (0.05 sec)
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived3>
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 5
          ref: AA.code
         rows: 10
        Extra: Using where
*************************** 3. row ***************************
           id: 3
  select_type: DERIVED
        table: b
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
        Extra: NULL
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED
        table: a
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2000
        Extra: NULL
4 rows in set (0.00 sec)