MySQL index transaction

Article Directory

index

concept:

An index is a special file that contains a reference pointer to a record in a data table. You can create an index on one or more columns in the table, and specify the type of index. Each type of index has its own data structure.

effect:

  • The relationship among tables, data, and indexes in the database is similar to the relationship among books, book contents, and book catalogs on the bookshelf.
  • The index plays a role similar to a book catalog, and can be used to quickly locate and retrieve data.
  • Indexes are of great help to improve the performance of the database.

scenes to be used

  • The amount of data is large, and conditional queries are often performed on these columns.
  • The insertion operation of the database table and the modification operation of these columns are less frequent.
  • Indexes will take up additional disk space. When
    the above conditions are met, consider creating indexes on these fields in the table to improve query efficiency. Conversely, if you query columns unconditionally, or frequently insert, modify, or disk space is insufficient, you do not consider creating an index.

Use index

When creating a primary key constraint (PRIMARY KEY), a unique constraint (UNIQUE), and a foreign key constraint (FOREIGN KEY), the index of the corresponding column will be automatically created.

View index

show index from 表名;

Example: View the existing index of the student table

show index from student;

Create index

For non-primary keys, non-unique constraints, non-foreign key fields, you can create ordinary indexes

create index 索引名 on 表名(字段名);

Example: Create the index of the name field in the class table

create index idx_classes_name on classes(name);

Delete index

drop index 索引名 on 表名;

Example: Delete the index of the name field in the class table

drop index idx_classes_name on classes;

Affairs

Why use transactions

Prepare data:

drop table if exists accout;
create table accout(
 id int primary key auto_increment,
 name varchar(20) comment '账户名称',
 money decimal(11,2) comment '金额'
);
insert into accout(name, money) values
('阿里巴巴', 5000),
('四十大盗', 1000);

For example: Forty thieves want to steal 2,000 yuan from Alibaba

-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';

If there is a network error when the first sentence is executed, or the database goes down, Alibaba's account will be reduced by 2,000 yuan, but the Forty Thieves account will not increase the amount.
Solution: Use transactions to control and ensure that the above two sentences of SQL either all succeed or all fail.

The concept of affairs

A group of operations in transaction logic, and each unit that composes this group of operations, either all succeed or all fail.

use

(1) Start transaction: start transaction;
(2) Execute multiple SQL statements
(3) Rollback or commit: rollback / commit
Description: Rollback means all failures, and commit means all successes.

start transaction;
update accout set money=money-2000 where name='阿里巴巴';
update accout set money=money+2000 where name='四十大盗';
commit;

The characteristics of the transaction

Atomicity
Consistency
Separation
Durability