MySQL memory table usage

MySQL 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. However, the usage is limited. The following are some of the feelings about the usage of the Blue Grass.
1. heap is visible to all users, which makes it very suitable for caching.
2. It is only suitable for use. Heap does not allow xxxtext and xxxblob data types. Only the = and <=> operators are allowed to search records (not allowed <,>, <= or> =). auto_increment is not supported; only non-empty data columns can be indexed (not null ).
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 automatically deleted. 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:
A. Before any query, run a simple query to check whether the heap table has data. If no data exists, write the data again, or drop the table to copy a table again. 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.
B. For the page on which the heap table is to be queried, the result of the dataset is judged for the first time and only for the first time. If the result is empty, the data needs to be written again. This saves one query.
C. A better way is to automatically write data to the heap every time MySQL restarts. However, you need to configure the server. The process is complicated and the versatility is limited.
Currently, Blue Grass uses the second method.
6. Some SQL statements that may be used as expected
// Delete a table if it exists
Drop table if exists 'abc ';
// Copy the entire table XYZ to heap table ABC (including all data)
Create Table 'abc' type = heap select * From 'xyz ';
// Add the primary key ID
Alter table 'abc' add primary key ('id ');
// Add the index Username
Alter table 'abc' add index 'abc' ('username ');
7. Create a table instance
Create Table 'db '(
'Id' int (11) default null,
'Songname' varchar (255) not null default '',
'Singer' varchar (255) not null default '',
Key 'songname' ('songname', 'singer ')
) Type = When heap creates a table, the table type option also has this table structure, that is, the memory table is created. If MySQL is restarted, the data in the memory table will disappear. But the access speed will be very fast!

Several key parameters

Max_heap_table_size

MySQL heap memory tables
Remember what others asked
MySQL memory tables
Max_heap_table_size = 256 m
Increase
Set max_rows
When running, you can alter table tbl_name max_rows =
Max_rows depends on max_heap_table_size settings

My configuration files

My. CNF

[Mysqld]
Datadir =/var/lib/MySQL
Socket =/var/lib/MySQL. Sock
Port = 5004
Tmp_table_size = 1000 m
Max_heap_table_size = 950 m
# 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. PID
[Client]
# Default-character-set = utf8

 

I have read the MySQL reference manual and found that there are multiple database storage engines in CREATE TABLE:
Type = {bdb | heap | isam | InnoDB | merge | mrg_myisam | MyISAM}
MySQL Database Engine isam, MyISAM, heap2007-07-20 if you are a racing driver and click a button to change the engine immediately without driving the car to the garage for a change, how does it feel? What MySQL database does for developers is like changing the engine by pressing a button. It lets you select a database engine and gives you a simple way to switch between it.
MySQL's built-in engine must be enough, but in some cases, other engines may be more suitable for completing tasks than they are used at hand. If you want to, you can even use MySQL ++ API to create your own database engine, just like hitting the cylinder and installing your own carburetor. Now let's take a look at how you choose an engine and how to switch between available engines.
Select your engine
The database engine you can use depends on how MySQL is compiled during installation. To add a new engine, you must recompile MySQL. Compile the application just to add a feature Program The concept may be strange for Windows developers, but it has become a standard in the Unix world. By default, MySQL supports three engines: isam, MyISAM, and heap. The other two types of InnoDB and Berkley (bdb) are also frequently used.
Isam
Isam is a well-defined and time-tested data table management method. It is designed to take into account that the number of database queries is much larger than the number of updates. Therefore, isam performs read operations quickly without occupying a large amount of memory and storage resources. The two major disadvantages of isam are that it does not support transaction processing or fault tolerance: If your hard disk crashes, data files cannot be recovered. If you are using isam in a key task application, you must always back up all your real-time data. With its copy feature, MySQL can support such backup applications.
MyISAM
MyISAM is the MySQL isam extension format and default database engine. In addition to providing a large number of functions for indexing and field management not available in isam, MyISAM also uses a table lock mechanism to optimize multiple concurrent read/write operations. The cost is that you need to run the optimize table command frequently to restore the space wasted by the update mechanism. MyISAM also has some useful extensions, such as the myisamchk tool used to fix database files and the myisampack tool used to restore wasted space.
MyISAM emphasizes fast read operations, which may be the main reason why MySQL is so favored by web development: In web development, a large number of data operations you perform are read operations. Therefore, most VM providers and Internet platform providers (Internet presence provider, iPP) only allow the use of MyISAM format.
Heap
Heap allows only temporary tables in memory. Heap is faster than isam and MyISAM in the memory, but the data it manages is unstable. If it is not saved before shutdown, all the data will be lost. When a row is deleted, heap does not waste much space. Heap tables are useful when you need to use select expressions to select and manipulate data. Remember to delete the table after the table is used up. Let me repeat it again: do not forget to delete the table after you have used up the table.
InnoDB and Berkley DB
InnoDB and Berkley dB (bdb) database engines are both direct products that make MySQL flexible technology. This technology is MySQL ++ API. When using MySQL, almost every challenge you face comes from the fact that the isam and MyISAM database engines do not support transaction processing or foreign keys. Although it is much slower than isam and MyISAM engines, InnoDB and bdb include support for transaction processing and Foreign keys, both of which are not available in the first two engines. As mentioned above, if your design requires one or both of these features, you will be forced to use one of the two engines.
If you feel that you are indeed highly skilled, you can also use MySQL ++ to create your own database engine. This API provides you with the functions of operation fields, records, tables, databases, connections, and security accounts, as well as all the other numerous functions required to create DBMS such as MySQL. An in-depth explanation of the API is beyond the scope of this article, but you need to understand the existence of MySQL ++ and the technology behind the interchangeable engine. This is very important. It is estimated that the plug-in database engine model can even be used to create a local XML provider for MySQL ). (Any MySQL ++ developer reading this article may regard this as a requirement .)
Press Switch
The switch that makes all flexibility possible is to provide the MySQL extension-type parameter for ansi SQL. MySQL allows you to specify the database engine at the table layer, so they sometimes refer to table formats. The following example Code Shows how to create tables using the MyISAM, isam, and heap engines respectively. Note that the code for creating each table is the same except for the final 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 a 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. Then, any changes to input data are queued, and the copy is moved to another engine. Finally, any changes made to data in the queue are sent to a new table, and the original table is deleted.
--------------------------------------------------------------------------------
Alter table shortcuts
If you want to update a table from isam to MyISAM, you can use the mysql_convert_table_format command without writing the alter table expression.
--------------------------------------------------------------------------------
You can use the show table command (another extension of MySQL to the ANSI standard) to determine which engine is managing specific tables. Show table returns a result set with multiple data columns. You can use this result set to query all types of information: the database engine name is in the Type field. The following sample code illustrates how to use show table:
Show table status from tblinnodb
--------------------------------------------------------------------------------
Replace show table
You can use show create table [tablename] to retrieve the information that can be retrieved by show table.
--------------------------------------------------------------------------------
Finally, if you want to use an engine that is neither compiled into MySQL nor activated, it is useless. MySQL will not prompt this. However, it only provides you with a table in the default format (MyISAM. In addition to using the default table format, there are also ways to make MySQL give an error message, but for now, if you are not sure whether a specific database engine is available, you need to use show table to check the table format.
More options mean better performance
Engines used for specific tables need to be re-compiled and tracked. Considering this extra complexity, why do you still want to use non-default database engines? The answer is simple: you need to adjust the database to meet your requirements.
Certainly, MyISAM is indeed fast, but if your logic design requires transaction processing, you can freely use the engine that supports transaction processing. Further, because MySQL allows you to use the database engine at the table layer, You can optimize the performance of the tables that require transaction processing, instead, the tables that do not need to be processed are handed over to the MyISAM engine, which is lighter. For MySQL, flexibility is the key.