[Database] MySQL indexes, transactions, views and ACID features

index

1) Essence : The essence of the index is a data structure of B+ tree

2) Role : In order to improve the query time for users to query database table records and improve query efficiency

3) When a table is indexed, additions, deletions and changes will not only modify the contents of the table, but also modify the index structure.

4) Create index
Primary key index: After specifying a column as the primary key, this column will increase the primary key index by default

// 创建表时
create table student(id int primary key, name varchar(255));

create table student(id int not null, name varchar(255), primary key(id));

// 使用alter关键字
alter table[表名称] add primary key([列名称])

Unique key index: After specifying a column as a unique key, this column will add a unique key index by default

// 创建表
create table student(id int unique not null, name varchar(255));

create table student(id int not null, name varchar(255), unique(id));

// alter
alter table[表名称] add unique(id);

Normal index

create table student(id int, varchar(255), index(id))

alter table emp add index(empno); 


5) Delete the index

// 删除主键索引
alter table [表名称] drop primary key;
// 删除唯一键索引
alter table [表名称] drop unique;
// 删除普通索引
alter table [表名称] drop index[索引名]

6) Index creation rules

  • Fields that are frequently used as query conditions
  • The uniqueness is too bad, not suitable for creating an index separately
  • Fields that are updated very frequently are not suitable for index creation
  • Fields that do not appear in the where clause are not indexed


Affairs

A transaction is composed of a set of DML statements, which are logically related. This set of statements either succeeds or fails.

1) SQL classification
DDL data definition language, used to maintain and store the structure of the data. Representative instructions: create, drop, alter

DML data manipulation language, manipulate data. Representative instructions: insert, delete, update

DCL data control language is mainly responsible for authority management and affairs. Representative instructions: grant, revoke, commit

2) Basic operations of

Insert picture description here


transactions 3) Transaction isolation level
If multiple clients operate the database at the same time, the transaction isolation level specifies different clients and different transactions. Can you see other uncommitted transactions? Modifications.

Isolation levelDirty readNon-repeatablePhantom readingLock read
Read uncommitedUnlocked
Read committed (read commited)XUnlocked
Repeatable readXXXUnlocked
SerializableXXXLock

4) Set the transaction isolation level

set session transcation isolation level[read commited];

The default transaction isolation level is repeatable read.

view

The view is a virtual table, derived from a real table.

The view also has a name and row data. When modifying the view, the data in the real table will be modified synchronously. Modify the real table, the data in the view will also be modified.

1) Create a view

create view [视图名称] as select语句;

2) View restriction rules

  • Uniquely named
  • Create a view to consider performance impact
  • The view cannot add an index, nor can it be associated with triggers or default values
  • Views can improve security and must have sufficient access rights
  • order by can be used in the view
  • View and table can be used together

ACID characteristics of transactions

1) Atomicity A
transaction is the smallest execution unit in an application, and a transaction is the smallest logical execution body that cannot be subdivided.

A group of SQL statements to be executed by the transaction are either all executed or not executed.

2) Consistency
The result of transaction execution must change from a consistent state of the database to another consistent state.

If the transaction is not completed and is interrupted, the unfinished modification is written to the database, and it is in an incorrect state. Therefore, consistency is guaranteed by atomicity.

3) Isolation
The execution of each transaction does not interfere with each other, and the internal operations of any transaction are isolated from other concurrent transactions.

The execution of multiple transactions does not affect each other.

4) Persistence
means that once a transaction is committed, the changes it makes to the database are recorded in permanent storage to the disk.