MySQL Database Indexes and Transactions

1. Index

1.1 Concept

• An index is a decentralized storage structure created to speed up the retrieval of data rows in a table. An index is created for a table. It is composed of index pages other than data pages. Each row in an index page contains a logical pointer to speed up the retrieval of physical data.
• In the Database Diagram, you can create, edit, or delete each index type in the Indexes/Keys property page for the selected table. An index is saved in the database when you save the table to which it is attached, or when you save the relationship graph in which the table resides.

In layman's terms, the relationship between indexes and tables and data in a database is similar to the relationship between books (tables), book contents (data), and book catalogs (indexes) on a bookshelf.

1.2 Function

Creating indexes in a database system has the following main functions:

• Quick data retrieval
• Ensure the uniqueness of data records
• Implement referential integrity between tables
• 在使用order by、 group by clauses to retrieve data, using indexes can reduce the time for sorting and grouping.
  

1.3 Principles of Indexing

1.3.1 Reducing the number of disk accesses is the core idea of ​​building an index

The purpose of an index is to facilitate querying.
MySQL query is mainly select . The basic execution process select includes traversing the table, taking out each record in turn, and filtering according to the conditions of the where clause. Since MySQL stores data on the hard disk, when querying, each record means accessing the hard disk. The access efficiency of IO devices to the hard disk is much lower than that to the memory. Therefore, reducing the number of disk accesses can improve the efficiency of queries, which is the core idea of ​​building indexes.

1.3.2 B+ Tree is suitable for implementing the underlying index

Reducing the number of accesses to data is an important idea when implementing indexing. Next, we will analyze several data structures to find a more suitable data structure for implementing indexing.

Binary Search Tree:

Since the binary search tree may be a single-branch tree, the time complexity is O(N)

AVL Tree:

• The AVL tree is essentially a binary balanced search tree, which is an improvement on the binary search tree. It ensures that the height difference between the left and right subtrees does not exceed 1, that is, there will be no single-branch tree structure, and the search time complexity is O(logN).
• Because the height difference between the left and right subtrees must not exceed 1, insertion or deletion operations will destroy the structure of the AVL tree. Therefore, the tree needs to be adjusted at any time. Although the query efficiency is met, the efficiency of insertion and deletion operations is reduced, and the time complexity of insertion and deletion is O(logN)
  

Red-black tree:

• The red-black tree is essentially an AVL tree with relaxed rules, that is, it does not force the height difference between the left and right subtrees to be no more than 1, which will lower the requirements to ensure the efficiency of insertion and deletion operations.
• The overall difference between the AVL tree and the query, insertion and deletion time complexity is O(logN)

Hash table:

• The time complexity of a hash table for query, insertion, and deletion is O(1).
• However, one of the key points of the hash table is that it must be compared for equality, but conditions such as greater than or less than cannot be achieved, which does not conform to the actual query situation.
  

So far, it seems that only AVL tree or red-black tree is more suitable for the implementation of MySQL index, and the search efficiency of these two data structures is directly determined by the height of the tree. Therefore, if the data increases, the height of the tree will also increase.

For further optimization, you can use an N-ary search tree to reduce the height of the tree, that is, reduce disk IO to improve search efficiency.

B-Tree:

B-tree is a type of N-ary search tree

B-tree example structure:

Used in indexes, each node represents a record

Characteristics of B-tree:

• Each node may contain N subtrees
• Each node may have multiple values.
• The values ​​of the left subtree are all less than the corresponding value of the root node, and the values ​​of the right subtree are all greater than the corresponding value of the root node
  

B+ Tree:

B+ tree is a special N-ary search tree, an improved version of B tree.

B+ tree example structure:

Improvements of B+ tree over B tree:

• Leaf nodes store each row of records, and non-leaf nodes only need to store the index value of each row.
• The values ​​of non-leaf nodes are repeated, making the leaf node layer a complete data set.
• All leaf nodes can be connected in a way similar to a linked list.
  

Advantages of B+ Tree:

• Good at range searching
• Since all queries fall on leaf nodes, the query speed is relatively stable
• Since the leaf nodes are the complete set of data, the leaf nodes can be stored on the hard disk, and the non-leaf nodes can be directly stored in the memory, which greatly reduces the number of hard disk reads.
  

1.4 Applicable Scenarios

• The number of searches is relatively high, while the number of insertions and deletions is relatively low, so it is suitable to use indexes.
• Since the index itself also takes up a certain amount of space, it is not suitable to use the index if the disk is tight.
• An index is created by specifying a column. When a column has a large degree of distinction, it is suitable to use an index, such as an auto-increment primary key.
  

1.5 Usage Statements

Replenish:

When creating primary key constraint, a unique constraint, or a foreign key constraint, an index for the corresponding column is automatically created.

1.5.1 View Index

grammar:

show index from table name;

Example:

1.5.2 Creating an Index

grammar:

create index index name on table name (field name);

Example:

1.5.3 Deleting an Index

grammar:

drop index index name on table name;

Example:

Notice:

The primary index cannot be deleted and an error will be reported if it is deleted.

2. Transactions

2.1 Concept

Things: It is a very broad concept in computers, generally referring to things to be done or things done. In a relational database, a transaction can be a SQL statement or a group of SQL statements or an entire program.

In layman's terms, for example, in a bank transfer operation, A transfers 500 yuan to B, then this operation actually includes two operations: A's account balance decreases by 500 yuan and B's account balance increases by 500 yuan.

Things are equivalent to packaging this series of actions into a whole, either doing nothing or doing everything.

2.2 Why use transactions?

Using the above bank transfer example, assuming that the operation of reducing 500 yuan from account A is successful, but the operation of increasing 500 yuan from account B is not successful, then the transfer operation is a failure.

The core characteristic of things is: packing a series of operations together to form a whole, either all of them are done or none of them are done.

Do nothing means: if an operation fails, the intermediate state at that time will be secretly restored

Therefore, using things can ensure that a series of operations will not be completed only partially, it will either be completed completely or not completed at all.

2.3 Four major attributes

A transaction is the basic unit of recovery and concurrency control. It has four properties: atomicity, consistency, persistence, and isolation.

At the heart of things is atomicity

2.3.1 Atomicity

concept:

A transaction is an indivisible unit of work. The operations included in the transaction are either all done or none of them are done.

The core of things is atomicity, the core of atomicity is to roll back to the intermediate state, the core of rolling back to the intermediate state is rollback, and the core of rollback is to remember each step of the operation

2.3.2 Consistency

concept:

A transaction must change the database from one consistent state to another consistent state. Consistency and atomicity are closely related.

Before and after executing a transaction, the data in the current table is in a reasonable state

2.3.3 Persistence

concept:

Persistence, also known as permanence , means that once a transaction is committed, the changes it makes to the data in the database should be permanent. Subsequent other operations or failures should not have any effect on it.

The data of transaction operations are directly operated on the hard disk, and the data on the hard disk is persistent.

2.3.4 Isolation

concept:

The execution of a transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and concurrently executed transactions cannot interfere with each other.

2.4 Usage

Open things:

start transaction;

Execute multiple SQL statements

Rollback or commit

-- Rollback: Indicates that all the above SQL statements fail to rollback;

-- Submit: Indicates that all the above SQL statements are successfully committed;

This is the end of this article about MySQL database indexes and transactions. For more relevant MySQL indexes and transactions, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!