Detailed explanation of MySQL database transactions and storage engine

1. Relational databases and non-relational databases

1. Characteristics of relational databases ：

1) The data appears in the form of a table

2) Each row has various record names

3) Each column is the data field corresponding to the record name

4) Many rows and columns form a form

5) Several forms form a database

2. Advantages of relational database:

1) Complex queries: SQL statements can be used for convenience Do very complex data queries between one table and multiple tables.

2) Transaction support: enables data access requirements with high security performance to be realized.

3. Advantages of non-relational databases:

1) Performance: NOSQL is based on key-value pairs, which can be imagined as tables The corresponding relationship between the primary key and the value does not need to be parsed by the SQL layer, so the performance is very high.

2) Scalability: Also because it is based on key-value pairs, there is no coupling between data, so it is very easy to expand horizontally.

Question: What are the differences between the three traditional databases?

Answer: The three are based on the relationship between data. The hierarchical database has a tree structure, and the network database has a link pointer structure. Relational database is a two-dimensional table structure.

2. Transaction (ACID)

A transaction is a series of transactions executed as a single logical unit of work Operations, these operations are submitted to the system together as a whole, and either all of them are executed or none of them are executed. A transaction is an indivisible logical unit of work.

Transactions must have the following four attributes, referred to as ACID attributes:

Atomicity : A transaction is a complete operation. Each operation of a thing is inseparable (atomic); either all are executed or none are executed;

Consistency (consistency): When the transaction is completed, The data must be in a consistent state;

Isolation (isolation): All concurrent transactions that modify the data are isolated from each other, which indicates that the transactions must be independent , it should not depend on or affect other transactions in any way;

Permanence (durability): After the transaction is completed, its modifications to the database are permanent , the transaction log can maintain the persistence of transactions.

Transaction isolation level:

Read uncommitted content (also called dirty read read uncommitted): means that all transactions can see the execution results of other uncommitted transactions. There may be dirty read, non-repeatable read and phantom read issues.

Read the submitted content (read committed): A transaction can only see the changes made by the transaction that has been submitted. Dirty reads can be avoided, and there may be problems with non-repeatable reads and phantom reads.

Repeatable read (repeatable read): It is the default transaction isolation level of MySQL. It ensures that when multiple instances of the same transaction read data concurrently, they will See the same rows of data. Dirty reads and non-repeatable reads can be avoided, and phantom reads may occur.

Serializable (serializable): It is the highest isolation level. It adds a shared lock to each read data row to prevent it from being read. may conflict with each other, thus solving the phantom reading problem. Dirty reads, non-repeatable reads, and phantom reads can be avoided.

Problems caused by concurrent transactions:

1. Update lost : Two transactions T1 and T2 read and modified the same data. The result submitted by T2 overwrites the result submitted by T1, causing the modification of T1 to be lost.

2. Dirty read: Transaction T1 modified a certain data and wrote it back to the disk. After transaction T2 read the same data, T1 due to some The reason is revoked. At this time, the modified data of T1 is restored to its original value. The data read by T2 is inconsistent with the data in the database. Then the data read by T2 is "dirty" data, that is, incorrect data.

3. Non-repeatable reading: refers to reading the same data multiple times within a transaction. Before this transaction ended, another transaction also accessed the same data. Then, between the two reads of data in the first transaction, due to the modification of the second transaction, the data read twice by the first transaction may be different. This means that the data read by the same query twice within a transaction is different, so it is called a non-repeatable read.

4. Phantom reading: For example: There are currently 10 employees with a salary of 5,000, and transaction A reads all 10 employees with a salary of 5,000. At this time, transaction B inserted a record with a salary of 5,000. At this time, transaction A again reads the employees with a salary of 5,000, and the record is 11. At this time, phantom reading occurs.

#What is the difference between non-repeatable read and phantom read? The focus of non-repeatable reading is modification, and the focus of phantom reading is addition or deletion.

MVCC (Multi-version Concurrency Control Mechanism): InnoDB’s MVCC is passed after each row of records This is achieved by saving two hidden columns. The creation time of the row and the deletion time of the row are saved respectively (what is stored here is not the actual time value, but the system version number). Every time a new transaction is started, the system version number is automatically incremented. The system version number at the start of the transaction will be used as the transaction ID to compare with the version number of each row of records queried. After using MVCC, phantom reads will not occur under the repeatable read isolation level.

3. Storage engine

The storage engine is the underlying software organization of the database. The database management system (DBMS) uses the data engine to create, query, Update and delete data.

1. InnoDB (clustered index method)

The underlying storage structure of innodb is a B-tree. Each node of the tree corresponds to a page of innodb. , the page size is fixed, generally set to 16K. The non-leaf nodes only have key values, and the leaf nodes contain complete data.

Usage scenarios: 1) Frequently updated tables handle multiple concurrent update requests;

2) Support transaction-safe tables ( ACID), supports row locks and foreign keys;

3) Can be restored through bin-log logs, etc.

innodbIf the primary key is not set, A 6-byte primary key will be automatically generated (not visible to the user).

2. MyISAM (non-clustered index mode)

MyISAM was the default storage engine of MySQL before 5.1, which emphasized performance. , but it does not support transactions, nor does it support row locks and foreign keys. It supports table locks; when inserting or updating data, the entire table needs to be locked, and the efficiency will be lower. Only the index is cached, not the real data. MyISAM reads data very quickly and does not occupy a lot of memory and storage resources.

MyISAM allows the existence of no primary key and is a static index structure.

Question: What is the difference between MyISAM and InnoDB in MySQL?

Answer: In the MySQL database, the two most commonly used engines are innodb and myisam. InnoDB is currently the default storage engine for MySQL.

1) Transactions: MyISAM emphasizes performance, and the query speed is faster than the InnoDB type, but it does not support transactions. InnoDB provides transaction support.

2) Foreign keys: MyISAM does not support foreign keys, but InnoDB supports foreign keys.

3) Locks: MyISAM only supports table-level locks. InnoDB supports row-level locks and table-level locks. The default is row-level locks. Row locks greatly improve the performance of multi-user concurrent operations. performance. innodb is more suitable for situations where there are many insert and update operations, while myisam is suitable for situations where there are frequent queries. In addition, the row lock of the innodb table is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, innodb will also lock the entire table, for example: update table set num=1 where name like "�a% ".

4) Full-text index: MyISAM supports full-text index, Innodb does not support full-text index. innodb provides full-text index support from MySQL 5.6 onwards.

5) Table primary key: Myisam allows tables without primary keys to exist; innodb: If no primary key is set, a 6-byte primary key will be automatically generated (not visible to the user).

6) The specific number of rows in the table: myisam: select count(*) from table, myisam simply reads the number of saved rows. Because myisam has a built-in counter, it reads directly from the counter when counting(*).

innodb: does not save the specific number of rows in the table. That is to say, when executing select count(*) from table, innodb will scan the entire table to calculate how many rows there are.

Recommended learning: MySQL tutorial

The above is the detailed content of Detailed explanation of MySQL database transactions and storage engine. For more information, please follow other related articles on the PHP Chinese website!