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

1. MySQL database engine:

1. Innodb engine:

The Innodb engine provides support for database ACID transactions and implements four isolation levels of the SQL standard.

Four isolation levels are defined in the SQL standard, each of which specifies the changes made in a transaction, which are visible within and between transactions, and which are not. Lower levels of isolation usually perform higher concurrency and lower system overhead. Four isolation levels: uncommitted reads (Read uncommitted): at the uncommitted read level, changes in a transaction are visible to other transactions, even if they are not committed. Transactions can read uncommitted data, which is also known as Dirty Read. This level can cause a lot of problems. In terms of performance, uncommitted reads are not much better than other levels, but lack many of the benefits of other levels and are rarely used in practical applications. Commit read (Read committed): the default isolation level for most database systems is commit read (but Mysql is not). Commit reads satisfy the simple definition of isolation mentioned earlier: at the beginning of a transaction, you can only "see" the changes made by the committed transaction. In other words, any changes made by one transaction from the beginning to the commit are invisible to other transactions. This level is sometimes called unrepeatable nonrepeatable read, because executing the same query twice may result in different results. Repeatable read: repeatable reading solves the problem of dirty reading. This level ensures that the results of reading the same record multiple times in the same transaction are consistent. But in theory, the repeatable isolation level still doesn't solve another Phantom read problem. The so-called illusory reading means that when a transaction is reading records in a certain range, another transaction inserts a new record in that range, and when the previous transaction reads the records in that range again, it will produce Phantom row. Repeatable readability is the default transaction isolation level for MySQL. Serializable (Serializable): serializable is the highest isolation level. It avoids the phantom reading problem mentioned earlier by forcing the serial execution of transactions. To put it simply, serializability adds a lock to every row of data read, so it can cause a lot of timeout and lock contention problems. This isolation level is also rarely used in practical applications, and it is considered only when there is a great need to ensure data consistency and it is acceptable that there is no concurrency.

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.

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

3. Choice of two engines:

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 is important to note that if the primary key is too long it can also cause performance problems.

A large number of INSERT statements (writing multiple rows in each INSERT statement and bulk inserting) are faster under MyISAM, but UPDATE statements are faster under InnoDB, especially when concurrency is large.

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.