MySQL storage engine notes

MySQL storage engine note storage engine overview plug-in storage engine is one of the important features of MySQL database, users can choose how to store and index data, whether to use things, and other features such as www.2cto.com based on application needs. MyISAMInnoDBMemory Transaction Security supports the lock machine tabulation lock row lock table lock index type B-tree index full-text index B-Tree Index cluster Index B-Tree Index HASH index data cache Support Index cache Support support for foreign keys www.2cto.com support for storage engine features MyISAM (ISAM → Indexed Sequential Access Method) engine a) MyISAM disadvantage: does not support transactions, does not support foreign keys advantage: fast access speed applicable: applications that do not require transaction integrity, have low concurrency, or use select and insert operations (web, data warehouse, etc.) B) MyISAMy table Storage Format static tables: fast storage, easy caching, and easy fault recovery (Note: spaces at the end of the data will be automatically removed) Dynamic tables: small space usage, deletion and updates can easily cause fragments (Note: optimize table or myisamchk-r fragment) Compressed table: each record is compressed separately, and the access cost is small. InnoDB engine a) InnoDB disadvantage: the write operation is inefficient and occupies more disks (retain data and indexes). Advantages: transaction security and support for foreign keys: applications with high consistency requirements for transaction integrity and concurrency and many update and delete operations (InnoDB effectively reduces table lock caused by Delete updates) (billing, financial system, etc) b) Auto-increment sequence 1. the auto-increment column must be an index. 2. for a composite index, the auto-increment column must be the first column (MyISAMy can be another column, and MyISAMy is sorted by the first few columns and then incremented: create table autoincrease_demo_myisam (t1 smallint not null auto_increment, t2 smallint not null, www.2cto.com name varchar (20), index (t2, t1) engine = myisam; insert the following data in sequence t1222332223322t223234232323423 to view the data in the database: select * from autoincrease_demo_myisam order by t2, t1; + ---- + ------ + | t1 | t2 | name | + ---- + ------ + | 1 | 2 | 2 | 2 | 2 | 3 | 3 | 2 | 2 | 4 | 2 | 2 | 5 | 2 | 3 | 6 | 2 | 2 | 7 | 2 | 3 | 8 | 2 | 2 | 9 | 2 | 2 | 10 | 2 | 3 | 1 | 3 | 3 | 2 | 3 | 4 | 3 | 3 | 4 | 3 | 4 | + ---- + ------ + c) the foreign key constraint MySQL only supports the foreign key storage engine InnoDB. When creating a foreign key, the parent table must have an index, and the word table will also create an index when creating a foreign key. Foreign key Association operations include restrict, cascade, set null, no action. www.2cto.com restrict, and no action: the parent table cannot be updated when the child table has association records. Cascade: when a parent table is updated or deleted, the corresponding records of the child table are updated or deleted. Set null: when the parent table is updated or deleted, the field corresponding to the word table is set null. D) Storage Mode shared tablespace storage: all data and indexes are stored in the tablespace defined by innodb_data_home_dir and innodb_data_file_path. Multiple files can be stored. Independent tablespace storage: Data and indexes of each table are stored in its own tablespace. (Single table backup and recovery are convenient) Memory engine a) advantage: the Memory storage engine uses the content stored in the Memory to create a table. By default, the HASH index is used. Table access is very fast (when starting the service, you can use the -- init-file option to insert statements such as insert into, select, or load data infile into this file, you can load tables from a persistent and stable data source when the service is started .) B) Disadvantages: The table size is limited, and data in the service closes the table will be lost. c) applicable: the content does not change frequently and is used as the intermediate result table of the statistical operation. Author xinhanggebuguake