1. Transaction security table: BDB InnoDB.
2. Non-transactional security tables: MyISAM, MEMORY, MERGE, EXAMPLE, NDB Cluster, ARCHIVE, CSV, BLACKHOLE, FEDERATED, etc.
The default storage engine of MySQL is MyISAM (InnoDB by default in version 5.7).
Set the parameters of the default storage engine in the configuration file default-table-type.
Query the storage engines supported by the current database:
Display engine;
Display a variable similar to "have%"; View the current default storage engine:
Display a variable similar to' %table_type%'; Specify the storage engine when creating a new table:
Create a table (...) engine = MyISAM Here are four commonly used storage engines: Myisam, InnoDB, MEMORY and MERGE.
I. Misam
1. data file:
MyISAM data table is stored in three files on disk, all of which have the same file names as the table names, and the extension is:
(1).frm: Storage data table structure definition.
(2).MYD: Store tabular data.
(3).MYI: storage table index.
Among them, data files and index files can be placed in different directories, and IO can be evenly distributed for faster speed. Specify the paths of index files and data files, which need to be specified through data directory and index directory statements when creating tables. (The file path must be absolute and accessible)
The table of MyISAM type may be damaged for various reasons, and the damaged table may be inaccessible, prompting that it needs to be repaired or returning an error result after access. You can use the check table statement to check the health of the MyISAM table, and use the repair table statement to repair the damaged MyISAM table.
2. Storage format:
(1) Static table (default): the field length is unchanged (the length of each record is fixed). The storage speed is very fast, easy to cache and easy to recover in case of failure; It usually takes up more space than dynamic tables.
(2) Dynamic table: It occupies a relatively small space, but frequent update and deletion of records will produce fragments, so it is necessary to execute the optimize table or myisamchk -r command regularly to improve performance, and it is difficult to recover when a fault occurs.
(3) Compressed table: created with myisampack tool, which takes up very little disk space. Because each record is compressed separately, there is only a small access overhead.
Static table data will be filled with blanks according to the definition of column width when stored, and these blanks will be deleted before returning the data to the application. If there are spaces after the content to be saved, these spaces will be deleted when the results are returned. (In fact, it is the behavior of data type char, and this problem will also occur if there is this data type in the dynamic table. )
(Static tables and dynamic tables are automatically selected according to the type of columns used. )
3. Advantages and disadvantages:
(1) Advantages: fast access speed.
(2) Neither transactions nor foreign keys are supported.
4. Application:
If the application is mainly read and insert operations, update and delete operations are few, and the requirements for transaction integrity and concurrency are not very high, then it is very suitable to choose this storage engine. MyISAM is one of the most commonly used storage engines in application environments such as Web and data warehouse.
Second, InnoDB.
1. storage mode:
InnoDB stores tables and indexes in two ways:
(1) Enjoy tablespace storage with * * *: the table structure created in this way is saved in. Frm files, data and indexes are stored in tablespaces defined by innodb_data_home_dir and innodb_data_file_path, which can be multiple files.
(2) Use multi-table space storage: the table structure created in this way is still stored in. Frm file, but the data and indexes of each table are saved in. Idb file. If it is a partitioned table, each partition corresponds to a separate. Idb file with the file name "table name+partition name". You can specify the location of the data file of each partition when you create the partition, so that the IO of the table is evenly distributed on multiple disks.
To use multi-table space storage mode, you need to set the parameter innodb_file_per_table and restart the server to take effect, and it only takes effect for newly created tables. There is no size limit for data files with multiple tablespaces, and it is not necessary to set parameters such as initial size, maximum limit and extended size of the file. Even in the multi-table space storage mode, you still need to enjoy the table space. InnoDB puts the internal data dictionary and work log in this file, so it is impossible to copy it. When backing up tables with multi-table space characteristics, the idb file is directly used. You can restore data backup to the database through the command:
ALTER TABLE tbl_name discards the tablespace;
ALTER TABLE tbl_name import tablespace; But this can only be restored to the original database of the table, and if it needs to be restored to other databases, it needs to be realized through mysqldump and mysqlimport.
2. Data file:
The data file of InnoDB is determined by the storage mode of the table.
(1) * * Tablespace file: defined by parameters innodb_data_home_dir and innodb_data_file_path, used to store data dictionaries and logs.
(2).frm: Definition of storage table structure.
(3).idb: When using multi-table space storage mode, it is used to store table data and indexes. If you use * * * tablespace storage, there is no such file.
3. Foreign key constraints:
InnoDB is the only MySQL engine that supports foreign key constraints. Foreign key constraint allows the database to ensure the integrity and consistency of data through foreign keys, but the introduction of foreign keys will slow down the speed and performance. When creating a foreign key, the parent table must have a corresponding index, and the child table will automatically create a corresponding index when creating a foreign key.
Examples of using foreign key constraints:
Create table "dep" (
` id ' smallint(6)NOT NULL AUTO _ INCREMENT，
Name' varchar(20) defaults to NULL.
Primary key (`id`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8；
Create table "emp" (
` id ' int( 1 1)NOT NULL AUTO _ INCREMENT，
Name' varchar(20) defaults to NULL.
Dep_id` smallint(6) is not empty.
Primary key (`id`),
Keywords `idx _ fk _ dep _ id` (`dep _ id`),
The constraint `fk _ EMP _ dep` foreign key (`dep _ id`) refers to dep` (`id) on the update cascade.
)ENGINE = InnoDB DEFAULT CHARSET = utf8； KEY: defines the index constraint name.
Constraint: Defines the foreign key constraint name. (Should be unique in the database; If not specified, the system will automatically generate a constraint name. )
ON: specifies the influence of parent table operations on child tables (by default, restrict is not defined).
Restricted and No Operation: When the child table has related records, the parent table cannot be updated or deleted.
Cascade: When the parent table is updated or deleted, the records corresponding to the child table are also updated or deleted.
Set null: When the parent table is updated or deleted, the corresponding field of the child table is set to null.
When a table is referenced by a foreign key created by another table, the corresponding index or primary key of the table cannot be deleted. When importing data from multiple tables, if you need to ignore the import order of tables, you can temporarily turn off foreign key checking; When performing load data and alter table operations, you can also speed up the processing by temporarily turning off foreign key constraints.
Close command:
set foreign _ key _ checks = 0； Open command:
set foreign _ key _ checks = 1； 4. Advantages and disadvantages:
(1) Advantages: Provides the ability to commit, rollback and crash recovery for transaction security.
(2) Disadvantages: Compared with MyISAM, InnoDB has lower writing efficiency and takes up more disk space to save data and indexes.
5. Application:
If the application requires high transaction integrity and data consistency under concurrent conditions, and data operations include many updating and deleting operations besides inserting and querying, then InnoDB storage engine should be a more suitable choice. InnoDB storage engine can not only effectively reduce the locks caused by deletion and update, but also ensure the complete submission and rollback of transactions. InnoDB is a suitable choice for systems that require high data accuracy, such as billing systems or financial systems.
Third, memory.
1. data file:
There is only one for each memory table. Frm disk file is used to store the structure definition of the table, and the table data is stored in memory. By default, a hash index is used instead of a BTREE index.
2. Advantages and disadvantages:
(1) Advantages: The access speed is fast, because the data exists in memory.
(2) Disadvantages: Once the service is closed, the data in the table will be lost; The size of the table is limited.
3. Application:
The memory storage engine is mainly used for code tables whose contents do not change frequently, or as an intermediate result table of statistical operation, which can conveniently and efficiently analyze the intermediate results and get the final statistical results.
Fourth, merger.
1. Engine principle:
The merge storage engine is a combination of a set of MyISAM tables, which must have the same structure. The consolidated table itself has no data, and the consolidated type table can be queried, updated and deleted. These operations are actually performed on the internal actual MyISAM table.
Define the insert operation of the merged table through the insert_method clause: use first or last to apply the insert operation to the first or last table accordingly; If it is not defined or defined as No, it means that the insert operation cannot be performed on this consolidated table. Deleting the merged table will only delete the definition of the merge, and has no effect on the internal table.
2. Data file:
(1).frm: Storage table definition.
(2).MRG: Store the information of the consolidated table, including which tables the consolidated table consists of and the basis for inserting new data. You can modify it by. Mrg file, but after modification, you should refresh it by refreshing the table.
3. Use examples:
Create table "m 1" (
` id ' int( 1 1)NOT NULL AUTO _ INCREMENT，
Name' varchar(20) defaults to NULL.
Primary key (`id`)
) ENGINE=MyISAM default character set = utf8
Create table "m2" (
` id ' int( 1 1)NOT NULL AUTO _ INCREMENT，
Name' varchar(20) defaults to NULL.
Primary key (`id`)
) ENGINE=MyISAM default character set = utf8
Create table "m" (
` id ' int( 1 1)NOT NULL AUTO _ INCREMENT，
Name' varchar(20) defaults to NULL.
Primary key (`id`)
)ENGINE = MRG _ MyISAM DEFAULT CHARSET = utf8 INSERT _ METHOD = LAST UNION =(` m 1 '，` m2 ')； 4. Application:
Used to logically combine a series of equivalent MyISAM tables and reference them as an object. The advantage of the merged table is that it can break through the size limit of a single MyISAM table. By distributing different tables on multiple disks, the access efficiency of the merged table can be effectively improved. This is very suitable for VLDB environment such as data warehouse.