An overview of the

The default storage engine was MyISAM before mysql5.5 and InnoDB after 5.5. To change the default engine, change the default-storage-engine in the configuration file. You can use Show Engines to see which engines are supported by the current database. Select engine from information_schema.engines where transactions = ‘yes’; To see which engines support transactions. Engine =… Or type =… To specify the engine to be used.

MyISAM

  1. It does not support transactions, nor does it support foreign keys. Its advantage is that the access speed is fast. Applications that do not require transactional integrity or that rely on SELECT/INSERT can basically use this engine to create tables.
  2. Each MyISAM has three files on disk, all with the same file name as the table name, but with the extension:
    • .frM (table definition)
    • .myd (MYDate: store data)
    • .myi (MYIndex: stored index)
  3. .myd file and. MYI files can be placed in different directories, specified by the Data Directory and Index Directory statements.
  4. A MyISAM TABLE may be damaged. You can use the CHECK TABLE statement to CHECK the health of the MyISAM TABLE, and use the REPAIR TABLE statement to REPAIR a damaged MyISAM TABLE.

  5. MyISAM supports 3 different storage formats:

    • Static (fixed length) tables
    • Dynamic table
    • The compression table

    In a static table, if the content you want to save is already followed by a space, the public will be removed when the result is returned.

    In dynamic tables, the record is not fixed, the advantage is relatively small footprint, the disadvantage is frequent update and delete records can generate fragmentation, need to periodically execute optimize table to improve performance.

    In the compressed table, created by the Myisampack tool, it takes up very little disk space. Because each record is individually compressed.

InnoDB

  1. InnoDB supports transaction security, InnoDB writes less efficiently than MyISAM and takes up more disk space.
  2. InnoDB autogrowth columns can be inserted manually, but if the value is null or 0, the value will be inserted automatically. You can use1228990Query the value used by the current thread to insert the last record. Can be achieved byalert table *** auto_increment=n;Statement enforces an automatic growth value.
  3. For InnoDB tables, autogrow columns must be indexes. If it is a composite index, it must also be the first column of the composite index, but for MyISAM tables, the autogrow column can be other columns of the composite index, so that after inserting records, the autogrow column is incremented in order of the composite index to the previous columns.
  4. MySQLSupport foreign keysStorage engine onlyInnoDBWhen a foreign key is created, the parent table must have the corresponding index, and the child table automatically creates the corresponding index when the foreign key is created. When creating an index, you can specify the operations to be performed on child tables when the parent table is deleted or updated, includingRestrict, CASCADE, Set NULL, and No Action. Restrict and No Action are the same. They restrict that the parent table cannot be updated if the child tables are associated. Casecade indicates that when the parent table is updated or deleted, the records corresponding to the child table are updated or deleted. Set NULL: When the parent table is updated or deleted, the fields corresponding to the child table are set null. When a table is referenced by a foreign key of another table, the corresponding index or primary key of the table cannot be deleted. You can useset foreign_key_checks=0;Temporarily close the foreign key constraint,set foreign_key_checks=1;Open the constraint.
  5. InnoDB stores tables and indexes in two ways:
    • Use shared table space storage.
    • Use multiple table Spaces for storage.

MEMORY

  1. Memory uses the contents of memory to create tables. Each MEMORY table actually corresponds to a disk file in. FRM format. Memory-type table access is very fast because the data is stored in MEMORY and HASH indexes are used by default, but once the server is shut down, the data in the table is lost, but the table continues to exist.
  2. 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 creating a MEMORY table.
  3. Memory is primarily used for code tables whose contents change infrequently, or as intermediate result tables for statistical operations.

MERGE

  1. Merge a storage engine is a group of MyISAM tables. The merge tables must have the same structure. The merge tables do not contain data.
  2. MERGE table inserts are defined according to the INSERT_METHOD clause, which can have three different values. The first and last values apply to the first or last table. This clause is not defined or is NO. Indicates that the MERGE table cannot be inserted.
  3. You can drop a MERGE table. This operation simply drops the MERGE table definition and has no impact on the internal tables.
  4. MERGE Saves two files starting with the MERGE table name on the disk:.frm file storage table definition; The.mrg file contains information about the MERGE table, 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 flush the table after modification.
  5. Merge tables do not intelligently write records to the corresponding tables, whereas partitioned tables do.

conclusion

To sum up:

Mysql is a common storage engine