MySQL Database engine analysis

First blog post: http://www.jb51.net/article/38004.htm This feeling of self-writing is also good. We can look at it.

1. What is a database engine

In simple terms, the database is the core service for storing, processing, and protecting data. Use the database engine to control access and quickly process transactions to meet the requirements of most applications that need to process large amounts of data within the enterprise. Use the database engine to create a relational database for online transaction processing or online analytics processing data, which includes creating tables for storing data and database objects for viewing, managing, and securing data.

2.mysql Database Engine type (Baidu encyclopedia)2.1 ISAM

ISAM is a well-defined and time-tested form of data management that, at design time, takes into account that the number of times the database is queried is much larger than the number of updates. As a result, ISAM performs read operations quickly and does not consume large amounts of memory and storage resources. The two main disadvantages of ISAM are that it does not support transactional processing or fault tolerance: If your hard drive crashes, the data file cannot be recovered. If you are using ISAM in mission-critical applications, you must always back up all of your real-time data, and with its replication features, MySQL can support such a backup application.

2.2 MYISAM

MyISAM is the ISAM extended format for MySQL and the default database engine. In addition to providing a number of functions for index and field management that are not available in ISAM, MyISAM also uses a form-locking mechanism to optimize multiple concurrent read and write operations. The cost is that you need to run the Optimize Table command frequently to restore the space wasted by the updated mechanism. MyISAM also has some useful extensions, such as the Myisamchk tool for repairing database files and the Myisampack tool for recovering wasted space.

MyISAM emphasizes fast read operations, which may be the main reason why MySQL is so popular with Web development: in Web development, the bulk of your data operations are read operations. Therefore, most web hosting providers and internet platform providers only allow the use of the MyISAM format.

2.3 HEAP

The heap allows temporary tables that reside only in memory. Residing in memory makes the heap faster than ISAM and MyISAM, but the data it manages is unstable, and if it is not saved before shutdown, all data will be lost. The heap does not waste a lot of space when the data rows are deleted, and the heap table is useful when you need to select and manipulate data using the Select expression. Remember to delete the table after you have finished using the table.

2.4 InnoDB and Berkleydb

The InnoDB and Berkleydb (BDB) database engine is a direct product of the technology that makes MySQL flexible, which is the mysql++ API. Every challenge you face when using MySQL comes from the ISAM and the MYIASM database engine does not support transactional processing or foreign keys. Although much slower than ISAM and MyISAM engines, InnoDB and BDB include support for transaction processing and foreign keys, which are not available in the top two engines for two points. As mentioned earlier, if your design requires accesses than either or both of these features, you will be forced to use one of the latter two engines.

3. InnoDB Engine

The InnoDB engine provides support for database acid transactions and implements four isolation levels for the SQL standard, about database transactions and their isolation levels

See database transactions and their isolation levels in this article. The engine also provides row-level and foreign-key constraints, which are designed to handle high-volume data

Library system, which is itself a complete database system based on the MySQL background, the MySQL runtime InnoDB creates a buffer pool in memory for slow

Data and indexes. However, the engine does not support the index of the fulltext type, and it does not save the number of rows in the table when the Select COUNT (*) from

Table needs to be scanned all the time. The engine is of course preferred when it is necessary to use a database transaction. Because the lock has a smaller granularity, the write operation does not lock the full table,

Therefore, using the INNODB engine increases efficiency when concurrency is high. But using row-level locks is not absolute, if you are executing an SQL statement

MySQL cannot determine the scope to be scanned, and the InnoDB table will also lock the entire table.

4. Myiasm Engine

Myiasm is the default engine for MySQL, but it does not provide support for database transactions or row-level and foreign keys, so it is less efficient to write operations that require the entire table to be locked when insert (insert) or update (updated) data. Unlike InnoDB, however, the number of rows in the table is stored in myiasm, so the Select COUNT (*) from table only needs to read the saved values directly and does not require a full table scan. Myiasm is also a good choice if the table reads much more than writes and does not require support for database transactions.

5. Selection of two engines

Large datasets tend to select the InnoDB engine because it supports transactional processing and failback. The size of the database determines how long the recovery takes, and InnoDB can use the transaction log for data recovery, which is faster. Primary key queries can also be pretty fast under the InnoDB engine, but it's important to note that if the primary key is too long it can cause performance problems, as I'll see later in this question. A large number of INSERT statements (write multiple lines in each INSERT statement, BULK INSERT) will be faster under MyISAM, but the UPDATE statement will be faster under InnoDB, especially if the concurrency is large.

6.index--Index

Index is the data structure that helps MySQL to get data efficiently. Both myiasm and InnoDB use tree-like data structures as indexes.

The index structure of the MyISAM engine is B+tree, where the contents of the B+tree data field are the address of the actual data, that is, its index is separate from the actual data, but the index points to the actual data, which is called a nonclustered index.

The index structure of the InnoDB engine is also b+tree, but the InnoDB index file itself is the data file, which is the actual data stored in the B+tree data field, which is the clustered index. The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index. Because the InnoDB data file itself is clustered by the primary key, the INNODB requires that the table must have a primary key (MyISAM can not), and if it is not explicitly specified, the MySQL system automatically selects a column that uniquely identifies the data record as the primary key, and if no such column exists, Then MySQL automatically generates an implicit field for the InnoDB table as the primary key, which is 6 bytes long and has a length of type. And unlike MyISAM, the secondary index data field of InnoDB stores the value of the corresponding record primary key instead of the address, so when looking with a secondary index, the primary key is found based on the secondary index, and the actual data is found based on the primary key index. Therefore, InnoDB does not recommend using a long primary key, otherwise the secondary index becomes too large. It is recommended to use the self-increment field as the primary key so that each node of the b+tree is filled in order, without frequent splitting adjustments, which effectively increases the efficiency of inserting data.

MySQL Database engine analysis