Database transactions and indexes, Database Transaction Indexes

Database transactions and indexes, Database Transaction Indexes

Transaction nature:

Atomicity: all operations in the same transaction may succeed or all operations may fail.

Consistency: Consistency means that the transaction must change the database from a consistent state to another consistent state, that is, a transaction must be in a consistent state before and after execution.

Isolation: isolation: when multiple users access the database concurrently, such as operating the same table, the transactions opened by the database for each user cannot be affected by the operations of other transactions, multiple concurrent transactions must be isolated from each other.

　Persistence: Once a transaction is committed, changes to the data in the database are permanent.

If you do not consider the isolation of transactions, the following problems may occur:

Dirty read: Dirty read refers to reading data from another uncommitted transaction during the transaction processing process.

Non-repeated read: Non-repeated read refers to the multiple queries within a transaction range for a certain data in the database, but different data values are returned, because at the query interval, modified and committed by another transaction.

Virtualization: for example, transaction T1 modifies a data item of all rows in a table from "1" to "2". At this time, transaction T2 inserts a row of data in the table, the value of this data item is "1" and submitted to the database. If a user who operates transaction T1 looks at the modified data, he will find that there is still a row that has not been modified. In fact, this row is added from transaction T2, as if it were an illusion, this is phantom reading.

Transaction isolation level:

Serializable: prevents dirty reads, repeated reads, and Phantom reads.

Repeatable read (Repeatable read): prevents dirty reads and repeated reads.

Read committed (Read committed): prevents dirty reads.

Read uncommitted (Read uncommitted): the lowest level, which cannot be guaranteed in any situation

Index

The index function in the database is used to accelerate the search speed. The principle is to store the data of index columns in a table in a special data structure (such as B-Tree and Hash ), database implementation usually uses B and B + trees

An index is equivalent to a dictionary directory. You can find a directory to obtain the location of the required data, without having to flip the entire dictionary.

　　Notes for using Indexes

　　　Indexing brings additional overhead, extra storage space, extra Creation Time, and extra maintenance time. Therefore, you must select the appropriate conditions to create an index.

　　Index type

1. Normal indexes: normal indexes allow indexed data columns to contain duplicate values.

2. Unique index: the data column contained by the index cannot have the same value. It can contain null.

3. Primary Key Index: The index created by the primary key. It must be unique and cannot be blank,

4. Full-text index (mysql ):

5. Clustered index: the logical order of the key values in the index determines the physical order of the corresponding rows in the table. (For example, the logical query order of the dictionary is the same as that of the physical query.) one table can have only one clustered index.

6. Non-clustered index: the logical order of the index is different from that of the physical storage on the disk. (For example, querying a word by the beginning of a dictionary on 54 pages may be on 554 pages) (not in mysql)

　　Fields suitable for index creation:

　　　1. Frequent search

2. Regular sorting

3. Often following the Where statement

4. Small Data Types

5. Simple data types

6. Avoid null in the column