Shulou(Shulou.com)06/01 Report--

What are the three typical MySQL storage engines? What are their respective characteristics? What are the application scenarios?

MySQL5.5 defaults to InnoDB storage engine, where InnoDB and BDB provide transaction security tables, and other storage engines are non-transaction security tables.

To modify the default engine, you can modify the default-storage-engine in the configuration file. You can view the current database to the default engine through: show variables like 'default_storage_engine';. Command: show engines and show variables like 'have%' can list what the current database supports to the engine. The record where Value is displayed as disabled indicates that the database supports this engine and is disabled when the database starts. After MySQL5.1, there is an ENGINES table in the INFORMATION_SCHEMA database that provides exactly the same information as the show engines; statement. You can use the following statement to query which storage engines support transaction processing: select engine from information_chema.engines where transactions = 'yes'

You can use the engine keyword to specify which engine to use when creating or modifying a database.

Introduction to the main storage engines: MyISAM, InnoDB, MEMORY and MERGE:

Through engine=... when the table is created Or type=... To specify which engine to use. Show table status from DBname to view the specified table to the engine.

(1) MyISAM

It does not support transactions, nor does it support foreign keys, especially for fast access, no requirements for transaction integrity, or applications based on SELECT and INSERT can basically use this engine to create tables.

Each MyISAM is stored as 3 files on disk, where the file name and table name are the same, but the extensions are:

.frm (storage table definition)

MYD (MYData, storing data)

MYI (MYIndex, storage index)

Data files and index files can be placed in different directories, evenly distributed IO, for faster speed. To specify the path to the data file and index file, you need to specify it through the DATA DIRECTORY and INDEX DIRECTORY statements when the table is created, and the file path needs to be an absolute path.

Each MyISAM table has a flag that is set by the server or myisamchk program when it examines the MyISAM data table. The MyISAM table also has a flag to indicate whether the datasheet has been normally closed since it was last used. If the server thinks it crashes or crashes, this flag can be used to determine whether the data table needs to be checked and repaired. If you want this check to occur automatically, you can use the-- myisam-recover phenomenon when starting the server. This allows the server to automatically check the flags of the data table and make the necessary repairs each time the server opens an MyISAM data table. A table of type MyISAM may be corrupted, so you can use the CHECK TABLE statement to check the health of the MyISAM table and use the REPAIR TABLE statement to repair a corrupted MyISAM table.

MyISAM's table also supports three different storage formats:

Static (fixed length) table

Dynamic table

Compression table

Where static tables are the default storage format. The fields in the static table are non-variable length fields, so that each record is of fixed length. The advantage of this storage method is that it is stored very fast, easy to cache, and easy to recover in the event of failure; the disadvantage is that it usually takes up more space than dynamic tables. Static tables fill in spaces based on the width of the column definition when storing data, but do not get these spaces at access time, which have been removed before being returned to the application. At the same time, it should be noted that in some cases, you may need to return the space after the field, and when using this format, the space after the field will be automatically disposed of.

Dynamic tables contain variable-length fields and records are not fixed-length, so the advantage of storage is that it takes up less space, but frequent updates and deletions of records will result in fragmentation, OPTIMIZE TABLE statements or myisamchk-r commands need to be executed regularly to improve performance, and recovery is relatively difficult in the event of a failure.

The compression table is created by the myisamchk tool and takes up a very small amount of space because each record is compressed separately, so there is only a very small access expense.

(2) InnoDB

The InnoDB storage engine provides transaction security with commit, rollback, and crash recovery capabilities. But compared to MyISAM's storage engine, InnoDB writes are less efficient and take up more disk space to retain data and indexes.

1) Auto-grow columns:

The auto-growing column of the InnoDB table can be inserted manually, but if the insert is empty or 0, the actual insert is automatically incremented to the value. You can force the starting value of the auto-growth value through the "ALTER TABLE...AUTO_INCREMENT=n;" statement, which defaults to 1, but the default value is saved in memory and will be lost when the database is restarted. You can use LAST_INSERT_ID () to query the value used by the current thread to insert the record last. If you insert more than one record at a time, the automatic growth value used by the first record is returned.

For InnoDB tables, the auto-growing column must be an index. If it is a composite index, it must also be the first column of the composite index, but for the MyISAM table, the auto-growing column can be the other columns of the composite index, so that after the record is inserted, the auto-growing column is incremented by sorting the composite index to the first few columns.

2) Foreign key constraints:

The only storage engine that MySQL supports foreign keys is InnoDB. When creating a foreign key, the parent table must have a corresponding index, and the child table will automatically create the corresponding index when creating the foreign key.

When you create an index, you can specify the appropriate actions to take on the child table when you delete or update the parent table, including restrict, cascade, set null, and no action. Restrict is the same as no action, which means that the parent table cannot be updated when the child table is associated. Casecade means that the parent table updates or deletes the corresponding records of the child table when the parent table is updated or deleted. Set null means that the fields corresponding to the child table are set null when the parent table is updated or deleted.

When a table is referenced by a foreign key created by another table, the corresponding index or primary key of the table is prohibited from being deleted.

You can use set foreign_key_checks=0; to turn off foreign key constraints temporarily, and set foreign_key_checks=1; to open constraints.

(3) MEMORY

Memory uses content that exists in memory to create tables. Each MEMORY table actually corresponds to a disk file in .frm format. Access to a table of type MEMORY is very fast because its data is in memory and the HASH index is used by default, but once the server is shut down, the data in the table is lost, but the table continues to exist.

By default, memory data tables use hash indexes, which are very fast for equality comparisons, but much slower for range comparisons. Therefore, hash index values are suitable for use in the operators of "=" and "", not in the "" operator, nor in order by sentences. If you do use the "" or betwen operator, you can use btree indexes to speed things up.

The data stored in the MEMORY datasheet is exercised in a format with the same length, so processing is faster, which means that variable-length data types such as BLOB and TEXT cannot be used. VARCHAR is a type of variable length, but because it is treated internally as a fixed-length CHAR type within MySQL, it can be used.

Create table tab_memory engine=memory select id,name,age,addr from man order by id

Use USING HASH/BTREE to specify specific to the index.

Create index mem_hash using hash on tab_memory (city_id)

Using the-- init-file option when starting the MySQL service, putting statements such as insert into...select or load data infile into this file allows you to load the table from a persistent data source when the service starts.

The server needs enough memory to maintain its MEMORY table used at the same time, and when the MEMORY table is no longer used, to free up the memory occupied by the MEMORY table, you should execute DELETE FROM or truncate table or delete the entire table.

The amount of data placed in each MEMORY table is constrained by the max_heap_table_size system variable, which has an initial value of 16m, and the MAX_ rows clause can be used to specify the maximum number of rows in the table when the MEMORY table is created.

(4) MERGE

The merge storage engine is a combination of a group of MyISAM tables, the structure of these MyISAM tables must be exactly the same, there is no data in the MERGE table, and the MERGE type table can be queried, updated and deleted. These operations are actually operations on the internal MyISAM table. For the insert operation on the MERGE table, it is an insert table defined according to the INSERT_ table clause. There can be three different values. The first and last values make the insert operation be acted on the first or last table accordingly. If this clause or NO is not defined, it means that the insert operation cannot be carried out on the MERGE table. You can drop the MERGE table, which simply deletes the definition of the MERGE table and has no effect on the internal table. MERGE keeps two files on disk that start with the MERGE table name: the .frm file stores the definition of the table; the .MRG file contains information about the combined tables, including which tables the MERGE table consists of and the basis for inserting data. You can modify the MERGE table by modifying the .MRG file, but refresh it through flush table after modification.

Create table man_all (id int,name varchar (20)) engine=merge union= (man1,man2) insert_methos=last

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.