mysql transactions and indexes

5.mysql transactions and indexes


Execute a group of sql statements in the same batch

If an sql error occurs, all the sql in the batch will be cancelled

mysql transaction processing only supports innoDB and BDB data table types

ACID principle of transaction

Atomic ity

  • All operations in the whole transaction are either completed or not completed, and it is impossible to stagnate in an intermediate link. If an error occurs during the execution of a transaction, it will be rolled back to the state before the start of the transaction, as if the transaction had never been executed.


  • A transaction can encapsulate state changes (unless it is read-only). Transactions must always keep the system in a consistent state, no matter how many concurrent transactions there are at any given time. In other words, if multiple transactions are concurrent, the system must also operate as a serial transaction. Its main feature is protection and invariance. Taking the transfer case as an example, suppose there are five accounts, and the balance of each account is 100 yuan, then the total amount of the five accounts is 500 yuan. If multiple transfers occur between the five accounts at the same time, no matter how many are concurrent, for example, 5 yuan is transferred between accounts a and B, 10 yuan is transferred between accounts C and D, and 15 yuan is transferred between accounts B and E, The total amount of the five accounts should still be 500 yuan, which is protective and invariable.


  • Isolate state execution transactions so that they appear to be the only operation performed by the system at a given time. If two transactions run at the same time and perform the same functions, the isolation of transactions will ensure that each transaction is considered to be the only one using the system in the system. This attribute is sometimes called serialization. In order to prevent confusion between transaction operations, requests must be serialized or serialized so that only one request is used for the same data at the same time.


  • After the transaction is completed, the changes made by the transaction to the database will be permanently saved in the database and will not be rolled back.
-- use set Statement to change the auto submit mode
SET autocommit = 0;   /*close*/
SET autocommit = 1;   /*open*/

-- be careful:
--- 1.MySQL The default is auto submit
--- 2.Auto commit should be turned off first when using transactions

-- Start a transaction,Mark the starting point of the transaction

-- Commit a transaction to the database

-- Rollback transaction,The data returns to the initial state of this transaction

-- reduction MySQL Automatic submission of database
SET autocommit =1;

-- Save point
SAVEPOINT Savepoint name -- Set a transaction savepoint
ROLLBACK TO SAVEPOINT Savepoint name -- Rollback to savepoint
RELEASE SAVEPOINT Savepoint name -- Delete savepoint

-- test
-- A Buy a product with a price of 500 yuan online,Online bank transfer.
-- A Your bank card balance is 2000,Then give it to the merchant B Pay 500.
-- business B The bank card balance at the beginning was 10000
USE `shop`;

CREATE TABLE `account` (
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,

INSERT INTO account (`name`,`cash`)

-- Transfer realization
SET autocommit = 0; -- Turn off auto submit
START TRANSACTION;  -- Start a transaction,Mark the starting point of the transaction
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- Commit transaction
# rollback;
SET autocommit = 1; -- Resume auto commit


Function of index:

  • Improve query speed
  • Ensure data uniqueness
  • The connection between tables can be accelerated to realize the referential integrity between tables
  • When using grouping and sorting clauses for data retrieval, the grouping and sorting time can be significantly reduced
  • Full text search field for search optimization


  • Primary key: an attribute group can uniquely identify a record
  • The most common index type
  • Ensure the uniqueness of data records
  • Ensure the location of specific data records in the database
  • Unique index: avoid duplicate values in a data column in the same table
  • Difference from primary key index: there can only be one primary key index and multiple unique indexes
  • General index: quickly locate specific data
  • General indexes can be set for both index and key keywords
  • Fields that should be added to query criteria
  • Too many indexes should not be added, which will affect the insertion, deletion and modification of data
  • Full text index: quickly locate specific data
  • Can only be used for datasheets of type MyISAM
  • Can only be used for char, varchar, text data column types
  • Suitable for large data sets

Index criteria

  • The more indexes, the better
  • Do not index data that changes frequently
  • It is recommended not to add indexes to tables with small amount of data
  • The index should generally be added to the field of the search criteria
#Method 1: when creating a table
    CREATE TABLE Table name(
               Field name 1 data type [integrity constraint...],
               Field name 2 data type [integrity constraint...],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [Index name] (field name [(length)] [ASC |DESC])

#Method 2: CREATE an index on an existing table
                    ON Table name (field name [(length)] [ASC |DESC]);

#Method 3: ALTER TABLE creates an index on an existing table
       ALTER TABLE Table name add [unique | Fulltext | spatial] index
                            Index name (field name [(length)] [ASC |DESC]);
#Delete index: DROP INDEX index name ON table name;
#Delete primary key index: ALTER TABLE table name DROP PRIMARY KEY;

#Display index information: SHOW INDEX FROM student;

/*Add full text index*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);

/*EXPLAIN : Analyze SQL statement execution performance*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*Use full-text indexing*/
-- Full text search passed MATCH() Function complete.
-- Search string as against() The parameters of are given. The search is performed in a manner that ignores the case of letters. For each record row in the table, MATCH() Returns a correlation value. That is, between the search string and the record line MATCH() The similarity scale between the text of the column specified in the list.
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

Before we start, let's talk about the version of full-text index, storage engine and data type support

MySQL 5.6 In previous versions, only MyISAM storage engine supports full-text indexing;
MySQL 5.6 And later versions, MyISAM and InnoDB storage engines support full-text indexing;
Full text indexes can be created only when the data types of fields are char, varchar, text and their series.
When testing or using full-text indexing, first check whether your MySQL version, storage engine and data type support full-text indexing.

Tags: MySQL

Posted by rid243 on Mon, 02 May 2022 07:52:02 +0300