This article has participated in the third “topic writing” track of the Denver Creators Training Camp. For details, check out: Digg Project | Creators Training Camp third is ongoing, “write” to make a personal impact
MySql engine
MySql supports a variety of storage engines. Different storage engines use different storage methods. These storage methods use different underlying implementations, such as storage mechanism, indexing techniques, lock granularity, etc.
MyISAM is the default database engine for older versions of MySQL, which evolved from earlier ISAM. Although the performance is good, transactions are not supported. After mysql5, many storage engines are supported, with InnoDB being the default. Let’s take a look at MyISAM and InnoDB engines.
InnoDB storage engine
InnoDB is the engine of choice for transactional databases, supports transaction safe tables (ACID), row locking and foreign keys, InnoDB is the default MySQL engine. The main features of InnoDB are:
1. InnoDB is designed for processing big data and its CPU efficiency is very high.
InnoDB provides an ACID-compliant storage engine with commit, rollback and crash recovery capabilities. InnoDB locks row-level and also provides an Oracle-like unlocked read in SELECT statements. In SQL queries, you can freely mix innoDB-type tables with other MySQL table types, even within the same query.
4. InnoDB Storage engine is fully integrated with MySQL server, InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB keeps its tables and indexes in a logical table space that can contain several files (or raw disk files). Unlike MyISAM tables, InnoDB tables can be of any size.
5. InnoDB supports foreign key integrity constraints. When storing data in a table, the storage of each table is stored in the order of primary key.
InnoDB does not create a directory. With InnoDB, MySQL will create a 10MB automatic extension data file named ibdata1 and two 5MB log files named ib_logfile0 and ib_logFILe1 in the MySQL data directory.
MyISAM storage engine
MyISAM is based on and extends the ISAM storage engine. It is one of the most commonly used storage engines in Web, data warehousing, and other application environments. MyISAM has high insert and query speed, but does not support transactions. MyISAM features:
1, support large files (up to 63 bit file length)
BLOB and TEXT columns can be indexed
3. Dynamically sized rows produce less fragmentation when delete is mixed with update and insert operations.
4. The maximum number of indexes per MyISAM table is 64, which can be changed by recompilation.
5. Tables with VARCHAR can have fixed or dynamic record lengths.
6. VARCHAR and CHAR columns can be up to 64KB.
7. The maximum key length is 1000 bytes. For keys longer than 250 bytes, a key larger than 1024 bytes will be used.
NULL is allowed in the column of the index. This value is 0~1 bytes per key.
9. Data files and index files can be placed in different directories.
10. Each character column can have a different character set.
Creating the database using the MyISAM engine will produce three files. The file name starts with the table name. File type: FRM file storage table definition, data file extension. MYD (MYData), extension of the index file. MYI (MYIndex).
MyISAM is different from InnoDB
Transaction support
MyISAM: The emphasis is on performance, atomicity per query, faster number of executions than InnoDB types, but no transaction support.
InnoDB: provides advanced database functions such as transaction support transactions and external keys. Transaction-safe (ACID Compliant) tables with COMMIT, Rollback, and Crash recovery capabilities.
Storage structure
MyISAM: Each MyISAM is stored as three files on disk. The name of the first file starts with the name of the table, and the extension indicates the file type. . FRM File storage table definition. The extension name of the data file. MYD (MYData). The index file extension is. MYI (MYIndex).
InnoDB: All tables are stored in the same data file (can be multiple files, or separate table space files), InnoDB table size is only limited by the size of the operating system file, generally 2GB.
The storage space
MyISAM: can be compressed, small storage space. Three different storage formats are supported: static table (default, but note that there is no space at the end of data, will be removed), dynamic table, compressed table.
InnoDB: Needs more memory and storage, it creates its own buffer pool in main memory for caching data and indexes.
Portability, backup, and recovery
MyISAM: Data is stored as files, so it is very convenient for cross-platform data transfer. You can operate on a single table during backup and restore.
InnoDB: Copy data files, back up binlogs, or use mysqldump.
AUTO_INCREMENT
MyISAM: You can create a joint index with other fields. The autogrowth column of the engine must be an index. If it is a composite index, autogrowth may not be the first column. It can be incremented by sorting the preceding columns.
InnoDB: InnoDB must contain an index with only this field. The engine’s auto-growing column must be an index or, if it is a composite index, the first column of the composite index.
Table locks differences
Select, UPDATE, DELETE, and INSERT (insert, insert, insert, insert, insert, insert, insert, insert, insert, insert, insert, insert, insert, insert)
InnoDB: Supports transaction and row-level locking, which is the biggest feature of InnoDB. Row locking dramatically improves the capability of multi-user concurrent operations. InnoDB locks rows only WHERE primary key is valid, non-primary key WHERE all tables are locked.
MyISAM locks are table-level in granularity, while InnoDB supports row-level locks. InnoDB supports row locking, while MyISAM does not support row locking and only supports entire table locking. That is, the read lock and write lock on the same table of MyISAM are mutually exclusive. If there are both read and write requests in the waiting queue of MyISAM concurrent read and write requests, the default write request has a high priority, even if the read request arrives first. Therefore, MyISAM is not suitable for the situation where a large number of queries and modifications coexist.
The full text indexing
MyISAM: Supports full-text indexing (of FULLTEXT type)
InnoDB: FULLTEXT indexing is not supported, but InnoDB can use the sphinx plugin to support FULLTEXT indexing, and it works better.
MyIsam index and data are separate, InnoDB together, MyIsam naturally non-clustered index, at most has a unique nature, InnoDB data files themselves are primary key index files, such indexes are called “clustered index”.
Table primary key
MyISAM: allows tables without any indexes and primary keys. Indexes are the addresses of rows.
InnoDB: If there is no primary key or non-empty unique index, it automatically generates a 6 byte primary key (not visible to the user). The data is part of the primary index, and the additional index holds the value of the primary index. InnoDB has a larger primary key range, up to twice that of MyISAM.
CURD operation
MyISAM: If you do a lot of SELECT, MyISAM is a better choice.
InnoDB: If your data performs a lot of INSERTS or updates, InnoDB tables should be used for performance reasons. When InnoDB deletes FROM table, InnoDB does not re-create tables but deletes them row by row. If InnoDB wants to DELETE tables with a large amount of data, it is better to use truncate table command.
Scenarios for MyISAM and InnoDB
-
MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is a better choice if you need to perform a lot of SELECT queries in your application.
-
InnoDB is designed for transaction processing applications with numerous features, including ACID transaction support. If your application needs to perform a lot of INSERT or UPDATE operations, use InnoDB to improve the performance of multi-user concurrent operations.