mysql memory引擎表

   mysql memory引擎的表适合做中间结果集，下面来做个试验：

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.24    |
+-----------+ 

  max_heap_table_size

DROP TABLE t_mem,t_inn;

SET max_heap_table_size=100*1024*1024;
CREATE TABLE t_mem(id INT,col1 INT,col2 INT,col3 INT) ENGINE=MEMORY;
CREATE TABLE t_inn(id INT,col1 INT,col2 INT,col3 INT) ENGINE=INNODB;

mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS `proc1`$$
mysql> CREATE PROCEDURE `proc1`()
mysql> BEGIN 
mysql>   DECLARE i INT;  
mysql>     SET i=0;  
mysql>     WHILE i<10000 DO  
mysql>       INSERT INTO t_mem VALUES(i,i,i,i);
mysql>       SET i=i+1;  
mysql>     END WHILE;  
mysql>  END$$
mysql> DELIMITER ;

mysql> DELIMITER $$
mysql> DROP PROCEDURE IF EXISTS `proc2`$$
mysql> CREATE PROCEDURE `proc2`()
mysql> BEGIN 
mysql>   DECLARE i INT;  
mysql>     SET i=0;  
mysql>     WHILE i<10000 DO  
mysql>       INSERT INTO t_inn VALUES(i,i,i,i);
mysql>      SET i=i+1;  
mysql>     END WHILE;  
mysql>  END$$

mysql> DELIMITER ;

mysql> CALL proc1();
Query OK, 0 rows affected (0.05 sec)

mysql> CALL proc2();
Query OK, 0 rows affected (28.54 sec)

mysql> SELECT COUNT(1) FROM t_mem;
+----------+
| COUNT(1) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)


mysql> SELECT COUNT(1) FROM t_inn;
+----------+
| COUNT(1) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)

  可以看到memeoy的表相对innodb来说插入是非常快，查询会快一点点。