MYSQL Memory Table Usage

MYSQL Memory Table Usage

The memory table uses a hash index to keep the data in memory, so it has a very fast speed and is suitable for caching small-to-medium databases, but with some restrictions on usage, the following are some of the feelings used by the blue grass.
1. The heap connection to all users is visible, which makes it ideal for caching.
2, only suitable for use in the occasion. The heap does not allow xxxtext and xxxblob data types; only the = and <=> operators are allowed to search for records (<, >, <=, or >= are not allowed); Auto_increment is not supported ; only allow indexes on non-empty data columns (not NULL).
Note: the operator "<=>" description: Null-safe equal. This operator performs the same comparison operation as the "=" operator, but when the two opcode is null, the resulting value is 1 and not null, and when an opcode is null, The resulting value is 0 and not null.
3. Once the server restarts, all heap table data is lost, but the heap table structure still exists because the heap table structure is stored under the actual database path and is not automatically deleted. After the reboot, the heap is emptied, and the query results for the heap are empty.
4. If the heap is a replicated data table, all the primary keys, indexes, and self-increment formats will no longer exist after copying, and the primary key and index need to be re-added, if necessary.
5, for the restart caused by the loss of data, there are the following solutions:
A, before any query, execute a simple query, determine whether the heap table exists data, if not, then write the data again, or drop table to re-copy a table. This requires more than one query. However, it is convenient to write an include file and call it at any time when the page needs to use the heap table.
b, for the page that needs the heap table, the dataset results are judged on the first and only the first time the table is queried, and if the result is empty, the data needs to be re-written. This saves you one query at a time.
C, a better approach is to automatically write data to the heap each time MySQL restarts, but the need to configure the server, the process is more complex, the universality is limited.
Blue Grass is currently using the second approach.
6. Some SQL statements that are expected to be used
If the table exists, delete the
DROP TABLE IF EXISTS ' abc ';
Copy entire table xyz to heap table ABC (contains all data)
CREATE TABLE ' abc ' type=heap SELECT * from ' XYZ ';
Add primary Key ID
ALTER TABLE ' abc ' ADD PRIMARY KEY (' id ');
Add index Username
ALTER TABLE ' abc ' ADD INDEX ' abc ' (' username ');
7. Create a Table instance
CREATE TABLE ' DB ' (
' id ' int (one) default NULL,
' Songname ' varchar (255) Not NULL default ' ',
' Singer ' varchar (255) Not NULL default ' ',
KEY ' Songname ' (' Songname ', ' singer ')
Type=heap table TYPE option also has this table structure to build the memory table. If MySQL restarts that memory table, the data will disappear. But the speed of access will be fast!

Several key parameters

Max_heap_table_size

MySQL HEAP MEMORY tables ways to increase row count support
The other people ask, remember.
MySQL MEMORY tables If the number of rows currently supported is not enough, the my.conf can be configured inside
Max_heap_table_size = 256M
Change the size
Set Max_Rows
On the run can ALTER TABLE tbl_name max_rows=
Max_Rows dependent on max_heap_table_size settings

My configuration file

My.cnf

[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
port=5004
tmp_table_size=1000m
max_heap_table_size=950m
# Default to using old password format for compatibility with MySQL 3.x
# clients (those using the Mysqlclient10 compatibility package).
Old_passwords=1
Default-character-set=utf8
Default-collation=utf8_bin
[Mysql.server]
User=mysql
Basedir=/var/lib
[Mysqld_safe]
Default-character-set=utf8
Default-collation=utf8_bin
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid
[Client]
#default-character-set=utf8

See MySQL reference manual There are a variety of database storage engines when you discover create TABLE:
TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | Mrg_myisam | MYISAM}
MySQL Database engine isam,myisam,heap2007-07-20 23:40 If you're a racer and you press the button to change the engine immediately without having to drive into the garage for a change, how does that feel? What the MySQL database does for developers is like a button changer, which lets you choose the database engine and gives you an easy way to switch it on.
MySQL's own engine must be sufficient, but in some cases, other engines may be better suited to complete the task than is used at hand. If you want, you can even use the mysql++ API to create your own database engine, just like hitting a cylinder and putting on your own carburetor. Now let's see how you can choose the engine and how to switch between the available engines.
Choose your engine
The database engine you can use depends on how MySQL was compiled when it was installed. To add a new engine, you must recompile MySQL. The idea of compiling an application just to add a feature might be strange for Windows developers, but in the Unix world this has become a standard. By default, MySQL supports three engines: ISAM, MyISAM, and heap. Two other types of InnoDB and Berkley (BDB) are also often available.
ISAM
ISAM is a well-defined and time-tested form of data management that, at design time, takes into account that the number of times the database is queried is much larger than the number of updates. As a result, ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. The two main disadvantages of ISAM are that it does not support transactional processing or fault tolerance: If your hard drive crashes, the data file cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and with its replication features, MySQL can support such a backup application.
MyISAM
MyISAM is the ISAM extended format for MySQL and the default database engine. In addition to providing a number of functions for index and field management that are not available in ISAM, MyISAM also uses a form-locking mechanism to optimize multiple concurrent read and write operations. The cost is that you need to run the Optimize Table command frequently to restore the space wasted by the updated mechanism. MyISAM also has some useful extensions, such as the Myisamchk tool for repairing database files and the Myisampack tool for recovering wasted space.
MyISAM emphasizes fast read operations, which may be the main reason why MySQL is so popular with Web development: in Web development, the bulk of your data operations are read operations. Therefore, most virtual hosting providers and Internet Platform providers (Internet presence Provider,ipp) only allow the use of the MyISAM format.
HEAP
The heap allows temporary tables that reside only in memory. Residing in memory makes the heap faster than ISAM and MyISAM, but the data it manages is unstable, and if it is not saved before shutting down, all the data will be lost. The heap does not waste a lot of space when data rows are deleted. The heap table is useful when you need to select and manipulate data using a select expression. Remember to delete the table after you have finished using the table. Let me repeat: Don't forget to delete the table after you have finished using the form.
InnoDB and Berkley DB
The InnoDB and Berkley DB (BDB) database engine is a direct product of the technology that makes MySQL flexible, which is the mysql++ API. Every challenge you face when using MySQL comes from the ISAM and the MyISAM database engine does not support transactional processing or foreign keys. Although much slower than ISAM and MyISAM engines, InnoDB and BDB include support for transaction processing and foreign keys, which are not available in the top two engines for two points. As mentioned earlier, if your design requires accesses than either or both of these features, you will be forced to use one of the latter two engines.
If you feel you are really skilled, you can also use mysql++ to create your own database engine. This API gives you the ability to manipulate fields, records, tables, databases, connections, security accounts, and all the other myriad features needed to build a DBMS such as MySQL. An in-depth explanation of the API is beyond the scope of this article, but it is important to understand the existence of mysql++ and the technology behind its interchangeable engines. It is estimated that this plug-in database engine model can even be used to create a local XML provider (XML provider) for MySQL. (Any mysql++ developer who reads this article can take this as a requirement.) ）
Press the Switch
The switch that makes all the flexibility possible is the MySQL extension--type parameter that is provided to ANSI SQL. MySQL allows you to specify the database engine at the level of the table, so they sometimes refer to table formats. The following example code shows how to create a table that uses the MyISAM, ISAM, and heap engines, respectively. Note that the code to create each table is the same, except for the last type parameter, which is used to specify the data engine.
CREATE TABLE Tblmyisam (
ID INT not NULL auto_increment,
PRIMARY KEY (ID),
Value_a TINYINT
) Type=myisam
CREATE TABLE Tblisam (
ID INT not NULL auto_increment,
PRIMARY KEY (ID),
Value_a TINYINT
) Type=isam
CREATE TABLE Tblheap (
ID INT not NULL auto_increment,
PRIMARY KEY (ID),
Value_a TINYINT
) type=heap
You can also use the ALTER TABLE command to move the original table from one engine to another. The following code shows how to use ALTER TABLE to move the MyISAM table to the InnoDB engine:
ALTER TABLE Tblmyisam Change Type=innodb
MySQL uses three steps to achieve this goal. First, a copy of the table is created. Any changes to the input data are then queued, and the copy is moved to another engine. Finally, any data changes queued to the queue are sent to the new table, and the original table is deleted.
--------------------------------------------------------------------------------
ALTER Table Shortcut
If you just want to update the table from ISAM to MyISAM, you can use the Mysql_convert_table_format command without having to write an ALTER TABLE expression.
--------------------------------------------------------------------------------
You can use the Show Table command (which is another extension of MySQL to the ANSI standard) to determine which engine is managing a particular table. SHOW table Returns a result set with multiple data columns that you can use to query for all types of information: the name of the database engine is in the Type field. The following sample code illustrates the use of Show table:
SHOW TABLE STATUS from Tblinnodb
--------------------------------------------------------------------------------
How to replace SHOW table
You can use show CREATE table [TableName] to retrieve the information that show table can retrieve.
--------------------------------------------------------------------------------
Finally, if you want to use an engine that has not been compiled into MySQL and is not activated, it is useless, and MySQL does not prompt this. And it will only provide you with a default format (MyISAM) table. In addition to using the default table format, there are ways to get MySQL to give error hints, but for now, if you are not sure whether a particular database engine is available, you need to use Show table to check the table format.
More choices mean better performance
The engine for a particular table needs to be recompiled and traced, and given this extra complexity, why do you want to use a non-default database engine? The answer is simple: adjust the database to meet your requirements.
To be sure, MyISAM is fast, but if your logic design requires transactional processing, you are free to use the engine that supports transaction processing. Further, since MySQL allows you to apply the database engine at the table level, you can optimize performance only for tables that require transaction processing, and leave the tables that do not require transaction processing to the lighter MyISAM engine. For MySQL, flexibility is the key.

Http://www.cnblogs.com/yinpengxiang/archive/2009/03/20/1417606.html

MYSQL Memory Table Usage