Shulou(Shulou.com)06/01 Report--

This article mainly introduces the definition and brief analysis of MyISAM database engine, hoping to supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated articles in industry information.

I. brief introduction of engine

MyISAM is the default database engine for MySQL (prior to version 5. 5) and has been improved by the earlier ISAM. Although the performance is excellent, there is one drawback: transaction processing (transaction) is not supported. However, over the past few years, MySQL also introduced InnoDB to strengthen reference integrity and concurrency violation handling mechanism, and then gradually replaced MyISAM

1.1.The Innodb engine

The Innodb engine provides support for database ACID transactions and implements four isolation levels of the SQL standard. For information about database transactions and their isolation levels, see the article on database transactions and their isolation levels. The engine also provides row-level locks and foreign key constraints, which are designed to deal with high-capacity database systems. It is actually a complete database system based on the background of MySQL. MySQL runtime Innodb will establish a buffer pool in memory to buffer data and indexes. However, the engine does not support indexes of type FULLTEXT, and it does not save the number of rows of the table, and the whole table needs to be scanned when SELECT COUNT (*) FROM TABLE. This engine is of course the first choice when you need to use database transactions. Because the granularity of the lock is smaller, the write operation does not lock the entire table, so using the Innodb engine can improve efficiency when concurrency is high. However, the use of row-level locks is not absolute, and if MySQL cannot determine the range to scan when executing a SQL statement, the InnoDB table will also lock the entire table.

1.2.The MyIASM engine

MyIASM is the default engine for MySQL, but it does not provide support for database transactions, row-level locks and foreign keys, so when INSERT (insert) or UPDATE (update) data, write operations need to lock the entire table, which is less efficient. However, unlike Innodb, the number of rows of the table is stored in MyIASM, so SELECT COUNT (*) FROM TABLE only needs to read the saved values directly without the need for a full table scan. MyIASM is also a good choice if the table has far more reads than writes and does not require the support of database transactions.

Second, engine selection

Large datasets tend to choose the InnoDB engine because it supports transaction processing and fault recovery. The size of the database determines the length of failure recovery time, InnoDB can use transaction logs for data recovery, which will be faster. Primary key queries can also be quite fast under the InnoDB engine, but it's important to note that too long primary keys can also cause performance problems, which I'll talk about later. A large number of INSERT statements (write multiple rows in each INSERT statement, bulk insert) are faster under MyISAM, but UPDATE statements are faster under InnoDB, especially when concurrency is large.

2.1Index of Index

Index is a data structure that helps MySQL obtain data efficiently. MyIASM and Innodb both use the data structure of tree as an index. I once wrote an article about tree. Tree is a great data structure. It's just my own understanding. Interested friends can read it. I'll go on to talk about the index structures used by these two engines. At this point, we should first talk about B-Tree and B+Tree.

2.1.1, B-Tree and B+Tree

B+Tree is a variant of B-Tree, so I'll talk about B-Tree first. I believe we all know the red-black tree. This is a red-black tree that I realized some time ago when I was learning "algorithm". You can refer to it. In fact, a red-black tree is similar to a 2-fork search tree, which has both 2-forked nodes and 3-forked nodes. B-Tree is similar, each node can have d branches (forks), d is called B-Tree degree, as shown in the following figure, each node can have 4 elements, 5 branches, so its degree is 5. The elements in B-Tree are ordered, for example, the elements in the nodes pointed to by the pointer to the left of element 7 in the figure are all less than 7, while the elements in the nodes pointed to by the pointers between elements 7 and 16 are all between 7 and 16. It is precisely to satisfy this relationship that efficient search can be achieved: first, a binary search is carried out from the root node, and the corresponding value is returned when found, otherwise the recursive search of the corresponding interval node is entered. Until the corresponding element is found or the null pointer is found, finding the null pointer indicates that the lookup failed. This search is very efficient and its time complexity is O (logN) (with d as the base, when d is very large, the height of the tree is very low), because only h nodes of tree height need to be retrieved at most.

It's time to talk about B+Tree, which is a variant of B-Tree, as shown in the following two figures:

As can be seen from the figure, the internal node of the B+Tree stores no data, only pointers, while the leaf node only stores data, not pointers. And a pointer to the adjacent leaf node is added to each leaf node, and this optimization improves the performance of interval access. For example, in the second graph, when the query key is from 18 to 49, when 18 is found, all the data nodes can be accessed at once by traversing along the node and pointer order, which greatly mentions the efficiency of interval query.

2.1.2. Index structure of MyISAM engine

The index structure of the MyISAM engine is B+Tree, where the content stored in the data field of B+Tree is the address of the actual data, that is, its index is separate from the actual data, but points to the actual data with the index, which is the so-called nonclustered index.

2.1.3. Index structure of Innodb engine

The index structure of the MyISAM engine is also B+Tree, but the index file of Innodb is the data file itself, that is, the data field of B+Tree stores the actual data, and this kind of index 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 data files of InnoDB are aggregated by the primary key, InnoDB requires that the table must have a primary key (MyISAM can not be explicitly specified). If it is not explicitly specified, the MySQL system will automatically select a column that can uniquely identify the data record as the primary key. If such a column does not exist, MySQL automatically generates an implied field for the InnoDB table as the primary key with a length of 6 bytes and a type of length × ×. And unlike MyISAM, the secondary index data field of InnoDB also stores the value of the corresponding record primary key rather than the address, so when searching with the secondary index, it will first find the primary key according to the secondary index, and then find the actual data according to the primary key index. Therefore, Innodb does not recommend using overly long primary keys, otherwise the secondary index will become too large. It is recommended to use self-increasing fields as the primary key, so that every node of the B+Tree will be sequentially filled without frequent split adjustment, which will effectively improve the efficiency of data insertion.

2.2.What is the difference between MyISAM and InnoDB? 2.2.1, 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. The .frm file stores the table definition. The data file has the extension .MYD (MYData). The index file has the extension .myi (MYIndex).

InnoDB: all tables are stored in the same data file (it may be multiple files, or separate tablespace files), and the size of InnoDB tables is only limited by the size of the operating system file, usually 2GB.

2.2.2, storage space

MyISAM: can be compressed, small storage space. Three different storage formats are supported: static tables (default, but note that there can be no spaces at the end of the data and will be removed), dynamic tables, and compressed tables.

InnoDB: more memory and storage is required, and it sets up its dedicated buffer pool in main memory for caching data and indexes.

2.2.3, portability, backup and recovery

MyISAM: data is stored as a file, so it is convenient in cross-platform data transfer. You can operate on a table separately during backup and restore.

InnoDB: the free solution can be to copy data files, back up binlog, or use mysqldump, which is relatively painful when the amount of data reaches dozens of gigabytes.

2.2.4, transaction support

MyISAM: the emphasis is on performance. Each query is atomic and executes several times faster than the InnoDB type, but does not provide transaction support.

InnoDB: provides advanced database functions such as transaction support transactions and foreign keys. Transaction security (transaction-safe (ACID compliant)) table with transaction (commit), rollback (rollback), and crash repair capability (crash recovery capabilities).

2.2.5 、 AUTO_INCREMENT

MyISAM: you can create a federated index with other fields. The auto-growing column of the engine must be an index, and if it is a combined index, the auto-grow may not be the first column, and it can sort and increment according to the previous columns.

The InnoDB:InnoDB must contain an index with only this field. The auto-growing column of the engine must be an index, and if it is a composite index, it must also be the first column of a composite index.

2.2.6, table lock difference

MyISAM: only table-level locks are supported. When users manipulate myisam tables, the select,update,delete,insert statement automatically locks the table. If the locked table meets insert concurrency, you can insert new data at the end of the table.

InnoDB: support for transaction and row-level locks is the most important feature of innodb. Row lock greatly improves the new energy of multi-user concurrent operation. But the row lock of InnoDB, only the primary key of WHERE is valid, the WHERE of non-primary key will lock the whole table.

2.2.7, full-text indexing

MyISAM: full-text indexing supporting FULLTEXT types

InnoDB: full-text indexing of type FULLTEXT is not supported, but innodb can use the sphinx plug-in to support full-text indexing, and the effect is better.

2.2.8, table primary key

MyISAM: tables that do not have any indexes and primary keys are allowed, and indexes are the addresses where rows are saved.

InnoDB: if a primary key is not set or a non-empty unique index is set, a 6-byte primary key is automatically generated (invisible to the user). The data is part of the primary index, and the additional index holds the value of the primary index.

2.2.9, the specific number of rows of the table

MyISAM: holds the total number of rows of the table, which will be fetched out directly if select count () from table;.

InnoDB: the total number of rows of the table is not saved, and if you use select count () from table;, you will traverse the entire table, which is quite expensive, but after adding the wehre condition, myisam and innodb handle it in the same way.

2.2.10. CURD operation

MyISAM: it's better to do a lot of SELECT,MyISAM.

InnoDB: if your data does a lot of INSERT or UPDATE, you should use the InnoDB table for performance reasons. DELETE InnoDB is better in terms of performance, but when DELETE FROM table, InnoDB will not re-establish the table, but delete it row by row. If you want to empty a table with a large amount of data on innodb, you'd better use the command truncate table.

2.2.11. Foreign key

MyISAM: not supported

InnoDB: support

Through the above analysis, we can basically consider using InnoDB to replace the MyISAM engine, because InnoDB has many good features, such as transaction support, stored procedures, views, row-level locking, and so on. In the case of a lot of concurrency, I believe that the performance of InnoDB must be much better than MyISAM. In addition, any kind of table is not omnipotent. Only by selecting the appropriate table type for the business type, can we maximize the performance advantages of MySQL. If it is not very complex Web applications, non-critical applications, you can continue to consider MyISAM, this specific situation can be considered by yourself.

Read the above definition and brief analysis of MyISAM database engine, hoping to bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.