How to view the engine of a MySQL database/mysql the database engine

In general, MySQL will provide a variety of storage engines by default, which you can view by:

See what storage engine your MySQL has now provided:
Mysql> show engines;

Look at your MySQL current default storage engine:
Mysql> Show variables like '%storage_engine% ';

You want to see what engine the table is using (in the results, the storage engine that the table is currently using is the one behind the parameter engine):
Mysql> Show create table table name;

MySQL Database engine detailed

As a Java programmer, MySQL database everyone should not use it, the MySQL database engine should also have some knowledge, this article let me talk about MySQL database InnoDB and myiasm two kinds of engine and its index structure. Also to consolidate their mastery of this piece of knowledge.

InnoDB engine

The InnoDB engine provides support for database acid transactions, and implements four isolation levels for the SQL standard, with information about database transactions and their isolation levels in the article, database transactions and their isolation levels. The engine also provides row-level and foreign-key constraints, which are designed to handle a large-capacity database system, which is itself a complete database system based on the MySQL backend, and the MySQL runtime InnoDB creates buffer pools in memory for buffering data and indexes. However, the engine does not support an index of type Fulltext, and it does not save the number of rows in the table, and the full table needs to be scanned when Select COUNT (*) from table. The engine is of course preferred when it is necessary to use a database transaction. Because the lock granularity is smaller, the write operation does not lock the full table, so using the INNODB engine increases efficiency when concurrency is high. However, using row-level locks is not absolute, and if MySQL does not determine the scope to scan when executing an SQL statement, the INNODB table will also lock the full table.

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.

Choice 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.

index--Index

Index is the data structure that helps MySQL to get data efficiently. Myiasm and InnoDB both use the data structure of the tree as the index, about the tree I also once wrote an article tree is a great data structure, just their own understanding, interested friends can go to read. Here I go on the index structure used by these two engines, and here we should first talk about B-tree and B+tree.

B-tree and B+tree

B+tree is a variant of b-tree, then I will first talk about B-tree, I believe we all know the red and black trees, this is my previous time to learn "algorithm" a book, the realization of a red black tree, we can refer to. In fact, red and black trees are similar to the search tree, the tree has 2 fork nodes and 3 fork nodes. B-tree is also similar to this, its each node long can have a D branch (fork), D is called the degree of B-tree, as shown, each of its nodes can have 4 elements, 5 branches, so its degree is 5. The elements in the B-tree are ordered, and the elements in the nodes that point to the left side of element 7 are less than 7, and the elements in the nodes between elements 7 and 16 point to a node that is between 7 and 16, and it is satisfying such a relationship that it is efficient to find: The first binary lookup from the root node, the corresponding value is returned , otherwise it enters the corresponding interval node recursive lookup until the corresponding element is found or a null pointer is found, and a null pointer indicates that the lookup failed. This lookup is very efficient, with a time complexity of O (Logn) (base D, when D is very large, the height of the tree is very low), because each retrieval is only required to retrieve the height of the tree h nodes.

The next step is to talk about B+tree, which is a variant of B-tree, as shown in the following two images:

VCHLX/I85LLP0A/QP8LKOAM8L3A+DQO8ADMGAWQ9 index structure of the "MyISAM engine" >myisam engine

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 .

Index structure of the INNODB engine

The index structure of the MyISAM 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.

How to view the engine of a MySQL database/mysql the database engine