The transaction

Put a set of SQL into a batch to execute

The principle of

Atomicity: Succeed together or fail together

Consistency: The status before and after an operation is the same

Persistence: A transaction reverts to its original state without committing

The transaction is committed and persisted to the database

Isolated: Transactions performed by multiple users do not affect each other

-- Transaction flow
-- Turn off automatic submission
SET autocommit = 0;
-- Transaction start
START TRANSACTION

SQL after START TRANSACTION is marked as being in the same TRANSACTION
INSERT INTO. UPDATE ...- submit
COMMIT

- the rollback
ROLLBACK

-- End of transaction
SET autocommit = 1 -- Enable automatic submission
Copy the code

The index

The essence of improving query efficiency is data structure

classification

  • The primary key index

    • The only

  • The only index

    • Avoid duplicate columns and be repeatable

  • Conventional index

    • The default

  • The full text indexing

    • It has to do with the database engine
    -- Displays all index information
show index

Add full text index
alter table school.student addFulltext index index name (' studentname ');Select * from table where id = 1; create index where id = 1;
create index id_app_user_name on app_user(`name`);
Copy the code

The index principles

  • More indexes are not always better
  • Do not index data that changes frequently
  • Tables with small data volumes do not need indexes
  • Indexes are generally added to fields that are commonly queried

The data structure of the index

Hash

Btree