tablea 與 tableb 的數據部分可能會不一致，所以利用索引加速搜索,
已知道用小表驅動大表,可是要蒐集 colb_4 的數據不過一直產生臨時表導致搜尋效率降低,
利用索引已經將tableB搜尋的行數降低了。

tablea 與 tableb 的 欄位資訊

CREATE TABLE `tablea` (
  `tableA_ID` int(11) UNSIGNED NOT NULL AUTO_increment,
  `cola_1` varchar(15) NOT NULL,  
  `cola_2` varchar(15) NOT NULL,
  `cola_3` varchar(15) NOT NULL,
  PRIMARY KEY (tableA_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `tableb` (
  `tableB_ID` int(11) UNSIGNED NOT NULL AUTO_increment, 
  `cola_1` varchar(15) NOT NULL,
  `cola_2` varchar(15) NOT NULL,
  `cola_3` varchar(15) NOT NULL,
  `colb_4` varchar(20) NOT NULL,
  `colb_5` int(15) NOT NULL,
  PRIMARY KEY (tableB_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 建立索引優化
ALTER TABLE `tablea` ADD INDEX col_a123(cola_1,cola_2,cola_3);
ALTER TABLE `tableb` ADD INDEX col_a123(cola_1,cola_2,cola_3);
ALTER TABLE `tableb` ADD INDEX col_b45(colb_4,colb_5);

測試函數與程序

DELIMITER $$ 
CREATE FUNCTION str_rand( t_count int )  -- 產生固定隨機字母
RETURNS varchar(255)
BEGIN
  DECLARE str VARCHAR(255) DEFAULT "";
  DECLARE randstr VARCHAR(100) DEFAULT "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
  DECLARE t_str VARCHAR(1) DEFAULT "";
  DECLARE i int default 0;
 while i<=t_count do
   SET str = concat(str,substring(randstr,FLOOR(1+RAND()*52),1));
     SET i=i+1;
 end while;
  RETURN str;
END $$
DELIMITER ;

DELIMITER $$ 
CREATE FUNCTION num_rand()  -- 隨機數字 100-110
RETURNS int(5)
BEGIN
  DECLARE num int(5) DEFAULT 0;
  SET num = FLOOR(100+RAND()*10);
  RETURN num;
END $$
DELIMITER ;

DELIMITER $$ -- 生產測試數據 比例為 tablea:tableb = 1:3 
CREATE PROCEDURE `insert_data`(IN START INT(10),IN max_num INT(10) ) 
BEGIN
  DECLARE i int DEFAULT 0;
  SET autocommit = 0; 
  REPEAT
    SET i = i + 1;
    INSERT INTO tablea(cola_1,cola_2,cola_3) 
    	VALUES 
        	( CONCAT("cola1_",i),CONCAT("cola2_",i),CONCAT("cola3_",i) );
    INSERT INTO tableb(cola_1,cola_2,cola_3,colb_4,colb_5) 
    	VALUES 
        ( CONCAT("cola1_",i),CONCAT("cola2_",i),CONCAT("cola3_",i),str_rand(5),num_rand() ),
        ( CONCAT("cola1_",i),CONCAT("cola2_",i),CONCAT("cola3_",i),str_rand(5),num_rand() ),
        ( CONCAT("cola1_",i),CONCAT("cola2_",i),CONCAT("cola3_",i),str_rand(5),num_rand() );
    UNTIL i = max_num
  END REPEAT;
  COMMIT;
END $$
DELIMITER ;

-- 建立假數據
CALL insert_data(1,10000)  

查詢的方式

EXPLAIN SELECT `colb_4`,avg(`colb_5`)
FROM 
  tablea a
INNER JOIN
  tableb b ON ( a.cola_1 = b.cola_1 and a.cola_2 = b.cola_2 and a.cola_3 = b.cola_3 )
GROUP BY `colb_4`

結論

SHOW profiles 查詢結果
得知 Sending data 話費時間異常的高 經常在搜查硬碟的資料

實際上 tableb.colb_4 的欄位更大,但是目前的結果導致搜尋時會使用到臨時表
目的是要尋找到相同的 tableb.colb_4 並運算統計 tableb.colb_5
想請問如果要優化且不會產生臨時表該如何調整