Detailed explanation of transactions and indexes in MySQL database

1. Affairs:

A transaction is a logical set of operations that either all succeed or all fail!

——————————————————————————————————

1. SQL execution A: 1000 yuan ——> transfer 200 yuan B: 200 yuan

2. SQL execution A: 800 yuan ——> B: 400 yuan

——————————————————————————————————

Execute a group of SQL statements in a batch

Four major characteristics of transactions:

ACID principles

1. Atomicity : A transaction is the smallest execution unit and cannot be split. The atomicity of transactions ensures that actions are either fully completed or have no effect at all;

2. Consistency: The data remains consistent before and after the transaction is executed, and the results of multiple transactions reading the same data are the same;

3. Isolation : When accessing the database concurrently, a user's transaction is not interfered with by other transactions, and the databases between concurrent transactions are independent;

4. Durability : After a transaction is committed. The changes it makes to the data in the database are permanent and should not be affected even if the database fails. --------Transaction commit

What problems do concurrent transactions bring? (Some problems caused by isolation)

In a typical application, multiple transactions run concurrently, often operating on the same data to complete their respective tasks (multiple users operate on the same data). Although concurrency is necessary, it may lead to the following problems.

Dirty read : When a transaction is accessing data and modifying the data, and this modification has not been committed to the database, another transaction also accesses the data and then uses the data. Because this data has not been committed, the data read by another transaction is "dirty data", and the operations performed based on the "dirty data" may be incorrect.

Lost to modify: When one transaction reads a piece of data, another transaction also accesses the data. Then, after the data is modified in the first transaction, the second transaction also modifies the data. In this way, the modification results in the first transaction are lost, so it is called lost modification. For example: Transaction 1 reads data A=20 in a table, Transaction 2 also reads A=20, Transaction 1 modifies A=A-1, Transaction 2 also modifies A=A-1, the final result is A=19, and the modification of Transaction 1 is lost

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

Phantom read: Phantom read is similar to non-repeatable read. It happens when one transaction (T1) reads a few rows of data, and then another concurrent transaction (T2) inserts some data. In subsequent queries, the first transaction (T1) will find some additional records that did not exist originally, just like an illusion, so it is called phantom read.

The difference between non-repeatable read and phantom read;

The focus of non-repeatable reads is modification, such as reading a record multiple times and finding that the values ​​of some columns have been modified. The focus of phantom reads is addition or deletion, such as reading a record multiple times and finding that the number of records has increased or decreased.

What are the transaction isolation levels?

READ-UNCOMMITTED UNCOMMITTED: The lowest isolation level that allows reading of data changes that have not yet been committed, which may result in dirty reads, phantom reads, or non-repeatable reads.

READ-COMMITTED : Allows reading of data that has been committed by concurrent transactions, which can prevent dirty reads, but phantom reads or non-repeatable reads may still occur.

REPEATABLE REPEATABLE-READ( ): The results of multiple reads of the same field are consistent, unless the data is modified by the transaction itself. Dirty reads and non-repeatable reads can be prevented, but phantom reads may still occur.

SERIALIZABLE( : The highest isolation level, fully compliant with ACID isolation levels. All transactions are executed one by one in sequence, so there is no possibility of interference between transactions. In other words, this level can prevent dirty reads, non-repeatable reads, and phantom reads.

MySQL's default isolation level:

The default isolation level supported by the MySQL InnoDB storage engine is REPEATABLE-READ (rereadable). We can view it through the SELECT@@tx_isolation; command

2. Index:

MySQL's official definition of an index is: An index is a data structure that helps MySQL obtain data efficiently. By extracting the main stem of the sentence, we can get the essence of the index: the index is a data structure.

The data structures used by MySQL indexes mainly include BTree indexes and hash indexes. For hash indexes, the underlying data structure is a hash table. Therefore, when most requirements are for single record queries, hash indexes can be selected, which have the fastest query performance. For most other scenarios, BTree indexes are recommended.

MySQL's BTree index uses the B+Tree in the B-tree, but the implementation methods for the two main storage engines are different.

MyISAM: The data field of the B+Tree leaf node stores the address of the data record. During index retrieval, the index is first searched according to the B+Tree search algorithm. If the specified key exists, the value of its data field is retrieved, and then the corresponding data record is read using the value of the data field as the address. This is called a "nonclustered index".

InnoDB: Its data file itself is the index file. Compared with MyISAM, the index file and data file are separated, and the table data file itself is an index structure organized by B+Tree, and the leaf node data field of the tree stores the complete data records. The key of this index is the primary key of the data table, so the InnoDB table data file itself is the primary index. This is called a clustered index. The remaining indexes are used as auxiliary indexes. The data field of the auxiliary index stores the value of the primary key of the corresponding record instead of the address, which is different from MyISAM. When searching based on the primary index, you can directly find the node where the key is located to retrieve the data; when searching based on the auxiliary index, you need to first retrieve the value of the primary key and then go through the primary index. Therefore, when designing a table, it is not recommended to use a field that is too long as the primary key, nor is it recommended to use a non-monotonic field as the primary key, as this will cause the primary index to split frequently.

The role of index:

• Improve query speed
• Ensure data uniqueness
• It can speed up the connection between tables and realize the referential integrity between tables.
• When using grouping and sorting clauses for data retrieval, the grouping and sorting time can be significantly reduced
• Full-text search fields for search optimization.

Index classification:

• Primary Key

Unique identifier, the primary key cannot be repeated, and there can only be one column as the primary key

• Unique Index

Avoid duplicate columns. Unique indexes can be repeated. Multiple columns can be identified as unique indexes.

• Conventional Index

By default, the index or key keyword is used to set

• Full Text Index

Only available in specific database engines, MylSAM

Quickly locate data

Indexing criteria:

• More indexes are not better
• Don't index frequently changing data
• It is recommended not to add indexes to tables with small data volumes
• Indexes should generally be added to the fields of the search conditions.

The data structure of the index:

-- When creating the above index, we can specify the index type for it. There are two types of hash type indexes: fast for single query and slow for range query. Btree type index: B+ tree, the more layers there are, the exponential growth of data volume (we use it because InnoDB supports it by default)
-- Different storage engines support different index types. InnoDB supports transactions, row-level locking, B-tree, Full-text and other indexes, but does not support Hash indexes.
MyISAM does not support transactions, but supports table-level locking, B-tree, Full-text and other indexes, but does not support Hash indexes;
Memory does not support transactions, but supports table-level locking, B-tree, Hash and other indexes, but does not support Full-text indexes;
NDB supports transactions, row-level locking, and hash indexes, but does not support B-tree, Full-text, and other indexes.
Archive does not support transactions, but supports table-level locking. It does not support B-tree, Hash, Full-text, and other indexes.

Summarize

This article ends here. I hope it can be helpful to you. I also hope you can pay more attention to more content on 123WORDPRESS.COM!