Shulou(Shulou.com)06/02 Report--

This article mainly explains "what is the function of MySQL database index and transaction". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the role of MySQL database indexes and transactions?"

1. Indexes

1.1 concept

An index is a decentralized storage structure created to speed up the retrieval of data rows in a table. Indexes are built on tables and are made up of index pages other than data pages, and the rows in each index page contain logical pointers to speed up the retrieval of physical data.

In the database diagram, you can create, edit, or delete each index type in the Index / key property page of the selected table. When you save the table to which the index is attached, or when you save the diagram where the table is located, the index is saved in the database.

Generally speaking, the relationship between an index and the tables and data in a database is similar to that of books (tables), book content (data) and book catalogs (indexes) on the shelf.

1.2 effect

Establishing an index in a database system has the following main functions:

Quick data fetching

Ensure the uniqueness of data records

Achieve referential integrity between tables

When using order by and group by clauses for data retrieval, the time of sorting and grouping can be reduced by using the index.

1.3 the principle of indexing

1.3.1 reducing the number of disk visits is the core idea of building an index

The purpose of the index is to facilitate queries.

The query of MySQL is mainly performed by select,select, which includes traversing the table, fetching each record in turn, and filtering according to the conditions of the where sentence.

Because MySQL stores data on the hard disk, when querying, each time the record is taken out, it means accessing the hard disk.

However, the access efficiency of IO device to hard disk is much lower than that to memory, so reducing the number of disk access can improve the efficiency of query, which is the core idea of building index.

1.3.2 B + tree is suitable for implementing the underlying layer of the index

Reducing the number of visits to the data is an important idea when implementing the index. next, several data structures will be analyzed to find a more suitable data structure to implement the index.

Binary search tree:

Because the binary search tree may be an one-branch tree, the time complexity is O (N).

AVL tree:

The AVL tree is essentially a binary balanced search tree, which is an improvement of the binary search tree. It ensures that the height difference between the left and right subtrees is not more than 1, that is, there is no single-branch tree structure, and the search time complexity is O (logN).

Because the height difference between the left and right subtrees is not more than 1, the insert or delete operation will destroy the structure of the AVL tree. Therefore, the tree needs to be adjusted at any time. Although the query efficiency is satisfied, the efficiency of insert and delete operations is reduced, and the time complexity of insert and delete is O (logN).

Red and black trees:

The red-black tree is essentially a relaxed AVL tree, that is, do not force the height difference between the left and right subtrees to be less than 1, which will reduce the requirements to ensure the efficiency of insert and delete operations.

There is little difference between the whole and the AVL tree, and the time complexity of query insertion and deletion is O (logN).

Hash table:

Hash table can query, insert, delete time complexity is O (1)

But one of the key points of hash tables is that they must be equal, but conditions such as greater than or smaller than can not be achieved, which is not in line with the actual query.

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

For further optimization, the N-ary search tree can be used to reduce the height of the tree, that is, to reduce the disk IO, so as to improve the search efficiency.

B-tree:

B-tree is a kind of N-ary search tree.

Sample B-tree structure:

Used in an index, each node represents a record

The characteristics of B-tree:

Each node may contain N subtrees

There may be multiple values on each node.

The value of the left subtree is less than the corresponding value of the root node, and the value of the right subtree is greater than that of the root node.

B+ tree:

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

Sample structure of B+ tree:

The improvement of B + tree compared to B tree:

Leaf nodes store each row of records, while non-leaf nodes only need to store the index value of each row.

The values of non-leaf nodes are duplicated, so that the layer of leaf nodes is a complete set of data.

All leaf nodes can be connected in a way similar to a linked list.

Advantages of B+ tree:

Be good at searching the scope

Since all queries fall on leaf nodes, the query speed is relatively stable.

Because the leaf node is a complete set of data, the leaf node can be stored on the hard disk, and the non-leaf node can be stored directly in memory, which greatly reduces the number of times to read the hard disk.

1.4 applicable scenarios

The number of searches is more, and the number of insertions and deletions is less suitable for indexing.

Since the index itself 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 the differentiation of a column is large, it is suitable to use an index, such as a self-increasing primary key.

1.5 use statement

Add:

When you create a primary key constraint (primary key), a unique constraint (unique), and a foreign key constraint (foreign key), indexes for the corresponding columns are automatically created

1.5.1 View Index

Syntax:

Show index from table name

Example:

1.5.2 create an index

Syntax:

Create index index name on table name (field name)

Example:

1.5.3 deleting an index

Syntax:

Drop index index name on table name

Example:

Note:

The primary index cannot be deleted. Deletion will report an error.

two。 Business

2.1 concept

Thing: it belongs to a very broad concept in the computer, which generally refers to what is to be done or done. In a relational database, a transaction can be a SQL statement or a set of SQL statements or an entire program.

Popular speaking, such as the bank transfer operation, A to B 500 yuan, then this operation actually includes An account balance reduction of 500 yuan and B account balance increase of 500 yuan two operations.

A thing is equivalent to packaging this series of actions into a whole, either not doing it all or doing it all.

2.2 Why do you use transactions

Take the above example of bank transfer as an example, assuming that the operation of reducing 500 yuan by account An is successful, but the account with an increase of 500 yuan from account B is not successful, then the transfer operation is a failure.

The core feature of things is that a series of operations are packaged together to form a whole, either all or none at all.

To do nothing at all means that if an operation fails, it will secretly restore the intermediate state at this time.

So if you use things, you can ensure that a series of operations will not only complete part of them, but either completely or not at all.

2.3 four attributes

Transaction is the basic unit of recovery and concurrency control. It has four attributes: atomicity, consistency, persistence and isolation.

The core of things is atomicity.

2.3.1 atomicity

Concept:

A transaction is an indivisible unit of work, and the operations included in the transaction are either done or not done.

The core of things is atomicity, the core of atomicity is fallback to intermediate state, the core of fallback is rollback, and the core of rollback is to remember the operation of each step.

2.3.2 consistency

Concept:

The transaction must change the database from one consistency state to another. Consistency is closely related to atomicity.

Before and after the execution of the thing, 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, its changes to the data in the database should be permanent. Other operations or failures that follow should not affect it in any way.

The data of the operation of things is directly operated on the hard disk, while the data of the hard disk is persistent.

2.3.4 isolation

Concept:

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

2.4 method of use

To open things up:

Start transaction

Execute multiple SQL statements

Rollback or submit

-- rollback: indicates that all the above SQL statements failed rollback;-- submission: indicates that all the above SQL statements are successful commit; so far. I believe that everyone has a deeper understanding of "what is the function of MySQL database indexes and transactions". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.