mysql内存表

Posted on | In | | 浏览 次

常说的mysql内存表包括两种，一种是仅在当前连接中有效，创建方法是 create temporary table table_name(i int)临时表与普通的表不同他不属于某个db而是从属于这个连接，所以在任何db里面show tables都看不到，并且同一个表名创建一次之后不管是在那个db下面都不能再创建了。临时表在创建时可以指定engine但是发现不管指定什么engine他都不会像普通的表一样会在mysql的数据目录里面找到对应的表结构以及数据，看了看资料应该是有一个tmp_table_size变量控制当临时表过大时会移动到硬盘上这种情况下速率会急速下降，因此猜测在大于某个量级之前临时表是一致装载在内存里面的，并且在当前连接中可见，不区分db，在连接断开时server会主动drop table。

  • 参考链接innodb_temporary
  • 临时表文档
    • A temporary table is created by using CREATE TEMPORARY TABLE statement. Notice that the TEMPORARY keyword is added between CREATE and TABLE keywords.
    • MySQL drops the temporary table automatically when the session ends or connection is terminated. Of course, you can use the DROP TABLE statement to drop a temporary table explicitly when you are no longer use it.
    • A temporary table is only available and accessible by the client who creates the table.
    • Different clients can create a temporary table with the same name without causing errors because only the client who creates a temporary table can see it. However, in the same session, two temporary tables cannot have the same name.
    • A temporary table can have the same name as an existing table in a database. For example, if you create a temporary table named employees in the sample database, the existing employees table becomes inaccessible. Every query you issue against the employees table refers to the employees temporary table. When you remove the employees temporary table, the permanent employees table is available and accessible again. Though this is allowed however it is not recommended to create a temporary table whose name is same as a name of a permanent table because it may lead to a confusion. For example, the connection to the MySQL database server is lost and you reconnect to the server automatically, you cannot differentiate between the temporary table and the permanent table. In the worst case, you may issue a DROP TABLE statement to remove the permanent table instead of the temporary table, which is not expected.
      *
      另外一种表就是正儿八经的内存表创建的时候指定engine=heap，这样创建的表与普通的表是一样的，对于任何用户可见，并且表结构是存在硬盘上的，不同的一点呢就是他的数据是在内存里面，所以断电或者重启mysql-server都会导致数据丢失，相应的他也有一个max_heap_table_size配置heap表的最大size，可以通过在server的启动参数中指定–init-file来每次启动mysql-server的时候进行初始化。

巧合的是mysql-server中默认的max_heap_table_size=tmp_table_size=16MB

转载请注明来源链接 http://just4fun.im/2017/01/15/mysql-e5-86-85-e5-ad-98-e8-a1-a8/ 尊重知识,谢谢:)