Features and usage of MySQL memory table online user statistics

Features and usage of MySQL memory tables :? Www. nowamagic. netlibrarysvedadetail1405? The memory table is a table in the memory. The memory size can be specified by max_heap_table_size in My. cnf. For example, max_heap_table_size1024M is different from that in the temporary table.

Features and usage of MySQL memory tables :? Http://www.nowamagic.net/librarys/veda/detail/1405? The memory table is a table in the memory. The memory size can be specified through max_heap_table_size in My. cnf. For example, max_heap_table_size = 1024 M. The memory table is not consistent with the temporary table.

Features and usage of MySQL memory tables online user statistics

Original article :? Http://www.nowamagic.net/librarys/veda/detail/1405

?

The memory table is a table in the memory. The size of the memory used can be calculated by using My. specify max_heap_table_size in cnf. For example, max_heap_table_size = 1024 M. The memory table is different from the temporary table. The temporary table is also stored in the memory. The maximum memory required for the temporary table must be set by tmp_table_size = M. When the data exceeds the maximum value of the temporary table, it is automatically converted to a disk table. When I/O operations are required, the performance will be greatly reduced, but the memory table will not. When the memory table is full, A message indicating full data error is displayed.

Both temporary tables and memory tables can be created manually. However, temporary tables are used to organize data after being created by the system to improve performance, such as subqueries, temporary tables cannot be shared among multiple connections. Here we will only discuss memory tables

Create a table that can be created using engine = heap (type is not supported in mysql5.5 and will be used in the future to form a habit ).

1	create?table?test

2

(

3	????id?int?unsigned?not?null?auto_increment?primary?key,

4	????state?char(10),

5	????type?char(20),

6	????date?char(30)

7	)ENGINE=MEMORY?DEFAULT?CHARSET=utf8;

Features of memory tables

1. Memory tables are stored on disks. The extension is. frm ,? Therefore, restart is not lost.
2. The data in the memory table is stored in the memory, so data will be lost after restart.
3. The memory table uses a fixed record length format.
4. Memory tables do not support BLOB or TEXT columns, such as varchar and text fields.
5. The memory table supports the AUTO_INCREMENT column and the index of the column that can contain NULL values (this is incorrect because it is not supported on the Internet ). Memory tables support greater than (> )? Less (? <) The operation is not supported on the Internet.
6. After mysql is restarted, the primary key, auto-increment, and index still exist, but data is lost. This is also used to correct some incorrect texts on the Internet.
7. The memory table is shared among all clients (just like any other non-TEMPORARY table ).
8. The MEMORY storage engine performs HASH and BTREE indexes. You can specify one or another for a given index by adding a USING clause as follows:

1	CREATE?TABLE?lookup

2	(id?INT,?INDEX?USING HASH (id))

3	ENGINE = MEMORY;

4

?

5	CREATE?TABLE?lookup

6	(id?INT,?INDEX?USING BTREE (id))

7	ENGINE = MEMORY;

1. You can use -- init-file to initialize the memory table to avoid data clearing after mysql is restarted. For example, -- init-file = "/data/mysql/init. SQL", the init. SQL format is:

1	use db_test;

2	select?***?into?m_table;

1. During database replication, if the host crashes, the delete from [memory table] will be automatically added to the binLog to delete the slave data to ensure data consistency between the two sides.
2. The memory table does not support transactions.
3. The memory table is a table lock. When the modification is frequent, the performance may decrease.

Memory table usage

Memory tables use Hash hash indexes to store data in the memory. Therefore, it is extremely fast and suitable for caching Small and Medium databases, but it is subject to some restrictions.

1. Heap is visible to all users, which makes it very suitable for caching.
2. Applicable only to scenarios. Heap does not allow xxxTEXT and xxxBLOB data types. Note: The operator "<=>" description: NULL-safe equal. this operator and the "=" operator perform the same comparison operation. However, when both operation codes are NULL, the obtained value is 1 instead of NULL. When an operation code is NULL, the value is 0 instead of NULL.
3. Once the server is restarted, all heap table data is lost, but the heap table structure still exists because the heap table structure is stored in the actual database path and will not be deleted automatically. After the restart, the heap will be cleared. At this time, the heap query results will be empty.
4. If heap is a replicated data table, all primary key, index, auto-increment and other formats will no longer exist after the replication. You need to re-Add the primary key and index if necessary.
5. There are the following solutions for data loss caused by restart:

• Before any query, execute a simple query to determine whether the heap table has data. If no data exists, re-write the data or DROP the table to copy a new table. This requires more than one query. However, you can write an include file, which can be called at any time on the page that requires the heap table.
• For the page on which the heap table is required, the results of the dataset are judged for the first time and only for the first time when the table is queried. If the results are empty, the data needs to be written again. This saves one query.
• A better way is to automatically write data to heap every time mysql restarts. However, you need to configure the server. The process is complicated and the versatility is limited.

Small record, mutual conversion between MyISAM and InnoDB

1	// InnoDB to MyISAM

2	ALTER?TABLE?`tablename` ENGINE = MYISAM

3	// Convert MyISAM to InnoDB

4	alter?table?tablename type=innodb;

5	ALTER?TABLE?`tablename` ENGINE = InnoDB