MySQL Index and transaction detailed explanation 02/23 Update SLTechnology News&Howtos

MySQL Index and transaction detailed explanation

2025-02-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

Detailed explanation of MySQL Index and transaction 1. Preface

In the last chapter of , we explained the manual compilation and installation process of MySQL and the related database operation commands (sql statements). This paper will introduce the concept and principle of MySQL index and transaction in detail, and have a preliminary understanding of the concept of MySQL database view and briefly describe the stored procedure of the database.

Index 2.1 the concept of an index-what is an index?

generally speaking, a paper, or a book, has its catalogue, and the catalogue is generally an overview of all chapters, or the core of key points, and the concept of index is similar.

index, as its name implies, is a guide for users to search for the resources they need, but in the database, the index is generally considered to be a special file, especially in the Linux system ("everything is a file"). In technical terms, it means "reference pointer that represents a record".

2.2 the function of the index to speed up the query speed and improve the work efficiency; reduce the cost of iActiono, provide basic sorting; speed up the join between multiple tables; support the unique index (what is the unique index below), ensure the uniqueness of the data in the data table; 2.3 Index classification 2.3.1 General index

is a general index, just to distinguish it from other special indexes.

2.3.2 uniqueness index

is basically the same as a normal index, except that all values in the columns of a unique index are different, that is, "unique".

simple for example, student data table, the age can be a general index, but not a unique index, but the detailed address can be.

2.3.3 Primary key index

is also essentially a unique index, but it must be specified as "primary key", which requires that each value in the primary key be unique. In the last article, we mentioned the concept of primary key, which is also characterized by "non-empty uniqueness".

2.3.4 full-text index

The index type is FULLTEXT, and the full-text index can be created on columns of type char, vachar, or text.

2.3.5 single-column index and multi-column index

can create indexes on single or multiple columns. Multi-column indexes are generally used to distinguish between rows in which one column may have the same value.

2.4 principles for creating an index: built on a table (the index depends on the table)

indexes can improve the query speed of the database, but not all data tables need to create indexes. Because the index itself also needs to take up system resources, perhaps this problem will not be very prominent in general, because the resources of the server can be supported normally to some extent, but if the index file is too large, its size may reach the maximum file limit allowed by the operating system.

and, if the index is not used properly, it will also put a burden on the database. Therefore, there are principles for database to create indexes.

2.4.1 the principle of creating an index must be based on the primary key and foreign key of the table; the table (300 rows of records) should have an index; the join fields between tables should be indexed; fields with poor uniqueness should not be used as indexes; fields that are updated frequently are not suitable for indexing; small fields are suitable for indexing, while long fields are not suitable for indexing; 2.5 advantages and disadvantages of indexes

Advantages: quick query of required resources

Disadvantages: taking up space and resources

2.6 create and view indexes

First of all, we need to make sure that there are tables in the database and data in the tables.

Mysql > select * from fruit_info +-+ | id | price | newtype | +-+ | 1 | 2.50 | banana | | 2 | 5.50 | apple | | 3 | 6.00 | peach | +-+ 3 rows in set (0.00 sec) 2.6.1 create a normal index

Command format: create index on data Table (column name)

Example:

Where Non_unique is 1, which means it is not a unique index; Key_name corresponds to the index name, and here is id_index

2.6.2 create a uniqueness index

Command format: create unique index on data Table (column name)

Example:

Here the index name is type_index, and the value of the corresponding Non_unique is 0, indicating that it is a unique index. And the values of the uniqueness index are all different.

So let's consider a question: can the fields of a table in the database be both a normal index and a unique index?

Let's do some practical verification:

It turns out to be possible, but we need to know that the difference between a unique index and an ordinary index lies in "uniqueness". If you create a uniqueness index, you need to pay attention to the uniqueness of field matching when inserting data records.

2.6.3 create a primary key index

Command format: (1) create primary key when creating a table: create table table name ([...], primary key (list of columns))

(2) modify the table structure and add the primary key: alter table table name add primary key

Example:

There can be only one primary key in a table, but a primary key can consist of multiple fields.

The primary key index is created automatically after the primary key is set. There is no need to specify a name. The system automatically generates the name "primary". The difference between the primary key index and the unique index is that the unique index can be null, while the primary key index is not null, so it can be simply understood by the formula: primary index = not null + unique index.

2.6.4 full-text index

The types of fields that can be established by a full-text index have been mentioned earlier, and the command format is as follows:

1. Create table table name (column name text,FULLTEXT (column name)) engine=MyISAM

2. Alter table table name add FULLTEXT (column name)

Example:

Check the storage engine type of the database: (storage engine we will explain in the next article)

You can specify multiple columns when creating the index

Command format: create index index name on table name (field 1, field 2)

Example:

Command format:

2.7.1 directly delete the index

Format: drop index index name on table name

Example:

Format: alter table table name drop index index name

Example:

Format: alter table table name drop primary key

Example:

Mysql > alter table student drop primary key;Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show index from student;Empty set (0.00 sec)

Of course, if the column containing the index is deleted when the table structure is modified, the corresponding index will also be deleted.

III. Transaction 3.1 the concept of transaction

Execute multiple orders as a whole, either successfully or not, such as a bank transfer

If the first part succeeds and the last part fails when the transaction is executed, the "rollback" mechanism is implemented, and the execution operation is returned to the starting point of the transaction, and the data does not change fruitfully.

3.2The ACID characteristics of transactions (4) 3.2.1 atomicity

A transaction is an indivisible working logic unit. A transaction can contain multiple sql statements, but the whole transaction is a complete operation and indivisible. (for example, when error appears in the transfer process, it will be rolled back to the original state, and there will be no change in the two assets.)

3.2.2 consistency

The data before and after the execution of the transaction must be in a consistent state, but it changes dynamically during execution; (for example, the sum of assets of both parties before and after the transfer is unchanged (without taking into account other fees, such as fees).

3.2.3 isolation

concurrent transactions are isolated from each other, and transactions must be independent (for example, phone calls between An and B will not affect C and Ding's phone calls)

3.2.4 persistence

The results of transactions are permanent and irreversible (such as the result of a transfer, which is another transaction if a dispute arises. )

3.3 Operation of the transaction 3.3.1 automatic commit

automatically commits by default, that is, it automatically commits and executes the command after entering the sql statement, but it is generally not safe.

3.3.2 manually submit

uses this mode more frequently in the production environment, because manual submission can be cached, and the results displayed in the data in memory are checked to see if there are any errors. If there is an error, the rollback operation (rollback) is performed (rollback point is usually set).

3.3.3 use transaction commands to control transactions (4)

Begin: indicates the start of a transaction, followed by multiple sql statements; 0

Commit: means to commit a transaction, corresponding to the previous begin

Rollback: means to roll back a transaction. Between begin and rollback, you can roll back when something goes wrong.

Savepoint: indicates that the rollback point is set for use with the rollback command.

Example:

First of all, we have the following data table:

Mysql > desc fruit_info # Table structure +-+ | Field | Type | Null | Key | Default | Extra | +-- -+-+ | id | int (4) | NO | PRI | NULL | price | decimal (3Power2) | NO | | NULL | | newtype | varchar (6) | YES | UNI | NULL | | +-- -+-+ 3 rows in set (0.00 sec) mysql > select * from fruit_info # Table data +-+ | id | price | newtype | +-- + | 1 | 2.50 | banana | 2 | 5.50 | apple | | 3 | 6.00 | peach | 4 | 6.00 | orange | +-+ 4 rows in set (0.00 sec) mysql > begin # start a transaction flag Query OK, 0 rows affected (0 sec) mysql > insert into fruit_info values; # insert a record Query OK, 1 row affected (0 sec) mysql > select * from fruit_info; # just put it in the cache at this time and use rollback to return to the original state +-+ | id | price | newtype | +-+ | 1 | 2.50 | banana | 2 | 5.50 | apple | 3 | 6.00 | peach | 4 | 6.00 | orange | 5 | 4.00 | pear | +-+ 5 rows in set (0.00 sec) mysql > rollback Query OK, 0 rows affected (0.00 sec) mysql > select * from fruit_info +-+ | id | price | newtype | +-+ | 1 | 2.50 | banana | 2 | 5.50 | apple | 3 | 6.00 | peach | 4 | 6.00 | orange | +-+ 4 rows in set (0.00 sec) mysql > insert into fruit_info values (5Ling 4) 'pear') Query OK, 1 row affected (0.00 sec) mysql > commit; # cannot be returned to the original state using rollback after submission Query OK, 0 rows affected (0.00 sec) mysql > select * from fruit_info +-+ | id | price | newtype | +-+ | 1 | 2.50 | banana | 2 | 5.50 | apple | 3 | 6.00 | peach | 4 | 6.00 | orange | 5 | 4.00 | pear | +-+ 5 rows in set (0.00 sec) mysql > rollback Query OK, 0 rows affected (0.00 sec) mysql > select * from fruit_info +-+ | id | price | newtype | +-+ | 1 | 2.50 | banana | 2 | 5.50 | apple | 3 | 6.00 | peach | 4 | 6.00 | orange | 5 | 4.00 | pear | +-+ 5 rows in set (0.01 sec)

Set breakpoint-"rollback point"

Mysql > begin;Query OK, 0 rows affected (0.00 sec) mysql > insert into fruit_info values; ERROR 1062 (23000): Duplicate entry'5' for key 'PRIMARY'mysql > insert into fruit_info values); Query OK, 1 row affected (0.00 sec) mysql > savepoint S1 position query OK, 0 rows affected (0.00 sec) mysql > insert into fruit_info values; Query OK, 1 row affected (0.00 sec) mysql > savepoint S2 Query OK, 0 rows affected (0.00 sec) mysql > select * from fruit_info +-+ | id | price | newtype | +-- + | 1 | 2.50 | banana | 2 | 5.50 | apple | | 3 | 6.00 | peach | 4 | 6.00 | orange | 5 | 4.00 | pear | 6 | 4.00 | grape | 7 | 4.00 | cherry | + -+-+ 7 rows in set (0.00 sec) mysql > rollback to savepoint S2 Query OK, 0 rows affected (0.00 sec) mysql > select * from fruit_info +-+ | id | price | newtype | +-- + | 1 | 2.50 | banana | 2 | 5.50 | apple | | 3 | 6.00 | peach | 4 | 6.00 | orange | 5 | 4.00 | pear | 6 | 4.00 | grape | 7 | 4.00 | cherry | + -+-+ 7 rows in set (0.00 sec) mysql > rollback to savepoint S1 Query OK, 0 rows affected (0.00 sec) mysql > select * from fruit_info + -. -+ 6 rows in set (0.00 sec) mysql > rollback Query OK, 0 rows affected (0.00 sec) mysql > select * from fruit_info +-+ | id | price | newtype | +-+ | 1 | 2.50 | banana | | 2 | 5.50 | apple | | 3 | 6.00 | peach | 4 | 6.00 | orange | 5 | 4.00 | pear | +-+ 5 rows In set (0.00 sec) mysql > rollback to savepoint S1 ERROR 1305 (42000): SAVEPOINT S1 does not exist

Based on the above results, we can draw the following conclusions:

The rollback mechanism can be implemented by using transaction commands to control transactions.

When using savepoint with the rollback command, the location of the rollback is based on the final location of the command you executed

If you use the rollback command directly, you go straight back to the original state, and you cannot go back to other rollback nodes.

3.3.4set command

Set autocommit=0: disable autocommit-- the equivalent of begin

Set autocommit-=1: enable autocommit

IV. Summary

this article mainly introduces the concept of index and transaction in MySQL database in detail. The concept, principle and corresponding usage scenario here need us to understand in detail with examples. The classification of index, the difference between primary key index and unique index, the four characteristics of transaction and the rollback mechanism of transaction.

the operation of this article is not difficult, but the details of the principle of understanding is still relatively trivial mouthful, need to really understand, the interview when this piece of content is very important, thank you for reading!

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.