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来说插入是非常快,查询会快一点点。