Database Transactions and Indexes

Nature of the transaction:

Atomicity: All operations in the same transaction otherwise all succeed or all fail

Consistency: Consistency means that a transaction must transform a database from one consistent state to another, meaning that a transaction must be in a consistent state before and after execution.

Isolation: Isolation is when multiple users concurrently access the database, such as the same table, the database for each user-opened transactions, can not be disturbed by the operation of other transactions, multiple concurrent transactions to be isolated from each other.

　Persistence: persistence means that once a transaction is committed, changes to the data in the database are permanent

Problems that occur without considering the isolation of transactions:

Dirty reads: Dirty reads refer to the data in another uncommitted transaction that is read in one transaction process.

Non-repeatable reads: Non-repeatable reads refer to a transaction-wide query that returns different data values for a data in the database, because the query interval has been modified and submitted by another transaction.

Idle: For example, a transaction T1 a data item from "1" to "2" for all rows in a table, and the transaction T2 inserts a row of data items into the table, and the value of this data item is "1" and is submitted to the database. The user of the operation transaction T1, if you look at the data you just modified, will find that there is another line that has not been modified, in fact, this line is added from the transaction T2, as if the illusion, this is the occurrence of Phantom read.

Isolation level of the transaction:

Serializable (serialization): Can avoid dirty reading, non-repeatable reading, the occurrence of phantom reading

REPEATABLE READ (Repeatable Read): Can avoid dirty read, non-repeatable read occurrence

Read Committed (reading committed): prevents dirty reads from occurring

READ UNCOMMITTED (unread): lowest level, no case guaranteed

Index

The purpose of the index in the database is to speed up the search speed by independently storing the data of the indexed columns in the table with special data structures (such as B-tree,hash), and the database implementations typically use B-and + + trees

The index is the directory of the dictionary, and you can find the directory to get the location of the data we need without having to turn over the dictionary.

　　Issues to be aware of using indexes

　　　The index brings additional overhead, additional storage space, additional creation time, additional maintenance time, so choose the appropriate situation to build the index

　　Type of index

1. Normal index: Normal index allows indexed data columns to contain duplicate values.

2. Unique index: The data column contained by the index does not allow the same value and can contain null

3. Primary KEY index: Index created by primary key, unique and cannot be null,

4. Full-text indexing (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 dictionary follows the logical order of the phonetic query and the physical order), only one clustered index in a table

6. Nonclustered index: The logical order of the indexes in this index is different from the physical storage order of the upstream of the disk. (for example, a word in a dictionary that can be queried by a radical may be 54 pages in 554 pages) (not in MySQL)

　　Fields that are suitable for indexing:

　　　1. Search frequently

2. Regular sequencing

3. Often followed by the where statement

4. Small Data type

5. Simple data types

6. Avoid null in the column as far as possible

Database Transactions and Indexes