Rookie Rise DB Chapter 4 MySQL 5.6 Database Engine

The database storage engine is the underlying software component of the database, which we do not normally see, but is closely related to our operational database. The DBMS uses the data engine to create, query, update, and delete data operations. Different storage engines provide different storage mechanisms, indexing techniques, lock levels, and other features that use different storage engines and can also get specific functionality. Many databases now support several different database engines, and The core of MySQL is the storage engine. Let's meet here.

4.1 What is an engine

MySQL provides a number of different storage engines, including the engine that handles transaction security tables and the engine that handles non-transactional security tables. in MySQL , there is no need to use the same storage engine throughout the server, and for specific requirements, you can use a different storage engine for each table. You can use the show ENGINES statement to see the types of engines supported by the system, with the following results:

the value of the support column indicates whether the current engine can be used,Yes indicates that it can beused,no indicates that it cannot be used,DEFAULT Represented as the current default engine, from the above we know that the current default engine is InnoDB.

4.2 InnoDB storage Engine

InnoDB the preferred engine for transactional databases, supports transaction security tables (ACID), supports row locking and foreign keys. after MySQL5.5.5 . InnoDB as the default storage engine, theInnoDB main features are:

• innodb mysql provides transaction security with commit, rollback, and Crash resiliency ( acid innodb lock row level and also in select statement always provides a similar oracle non-locking read. These features increase multi-user deployment and performance. In sql query, you can freely innodb mysql Span style= "font-family: italics;" The types of tables can be mixed even in the same query.
• The InnoDB is designed to handle the largest performance of large volumes of data. Its CPU efficiency could be unmatched by any other disk-based relational database engine.
• The InnoDB storage engine is fully integrated with the MySQL Server,and theInnoDB Storage Engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB Its tables and indexes in a logical table space, the tablespace can contain several files (or raw disk partitions). This is different from the MyISAM table, such as in the MyISAM table where each table is in a separate file. the InnoDB table can be any size, even if the file size is limited to 2GB on the operating system.
• InnoDB supports foreign key integrity constraints (FOREIGN key). When you store data in a table, the storage for each table is stored in the primary key order, and if you do not specify a primary key when the table definition is displayed,InnoDB generates a 6 -byte ROWID for each row , and use this as the primary key.
• InnoDB is used in many large database sites that require high performance.

InnoDB does not create a directory, when using InnoDB ,mysql will Create a MySQL data directory named Ibdatal of the 10M the size of the auto-extended data file, and two named Ib_ligfile9 and the Ib_logfilel1 of the 5MB the size of the log file.

4.3 MyISAM storage engine

The MyISAM is based on the ISAM storage engine and extends it. It is One of the most commonly used storage engines in the WEB, data warehousing, and other application environments. MyISAM has a high insertion and query speed, but does not support transactions. in Previous versions of MySQL5.5.5,MyISAM was the default storage engine. MyISAM main features are:

• large files (up to five bits file length) are supported on file systems and operating systems that support large files.
• When the delete and update and insert operations are mixed, the rows of the dynamic dimensions produce less fragmentation. It is also done automatically by merging adjacent deleted blocks, and if the next block is deleted, it is expanded to the next one.
• each The maximum number of indexes for the MyISAM table is $, which can be changed by recompiling. The maximum number of columns per index is three.
• The maximum key length is a byte, which can also be changed by compiling. For cases where the key length is more than One byte, a key greater than the size of the byte will be used.
• the BLOB and TEXT columns can be indexed.
• A NULL value is allowed in the column of the index. This value accounts for the 0-1 bytes of each key .
• All numeric key values are stored in high-byte precedence to allow a higher index compression.
• one per table the internal processing of the Auto_increment column. the MyISAM automatically new column for INSERT and UPDATE operations, which makes auto_increment the column is faster (at least 10%). After the value of the sequence item is deleted, it is no longer available.

4.4 Memory storage Engine

The memory storage engine stores the data in a table in the RAM, providing quick access to queries and references to other data.

The main features of MEMORY are:

• each table of the MEMORY table can have up to Three indexes, a column of each index , and a maximum key length of ten bytes.
• The memory storage engine performs HASH and BTREE indexes.
• can be in a a non-unique key in the MEMORY table.
• The MEMORY table uses a fixed record length format.
• MEMORY does not support BLOB or TEXT columns.
• MEMORY supports auto_increment columns and indexes on columns that can contain NULL values.
• The MEMORY table is shared among all clients (like any other non- temporary table).
• The Memory table contents are in memory, and the internal table shares are created when the memories table and the server are idle during query processing.
• when no longer needed Memory table, you should perform a DELETE from or TRUNCATE table when you want to release memories used , or delete the entire table (using DROP table).

4.5 Storage engine selection

Different storage engines have different characteristics to suit different needs, as shown in the following table. To make a choice, you first need to consider what different capabilities each storage engine provides.

If you want to provide transactional security for commit, rollback, and crash resiliency (ACIDcompatible), and requires concurrency control,InnoDBis a good choice if the data table is primarily used to insert and query records, thenMyISAMthe engine can provide high processing efficiency: if the data is only temporarily stored, the amount of data is small, and the high data security is not required, you canchoose to save the data in memoryMEMORYengine,MySQLthe engine is used as a temporary table to hold the intermediate results of the query. If onlyINSERTand theSELECToperation, you can selectArchiveengine,ArchiveThe storage engine supports high-concurrency inserts, but is not inherently transaction-safe. Archivethe storage engine is ideal for storing archived data, such as logging information that can be usedArchiveengine.

Which engine to use is flexible to choose from, and multiple tables in a database can use different engines to meet a variety of performance and real-world requirements. Using the appropriate storage engine will improve the performance of the entire database.

Rookie Rise DB Chapter 4 MySQL 5.6 Database Engine