Mysql learning transactions and indexes

affair

What is a transaction

  • Transaction is to execute a group of SQL statements in the same batch
  • If an SQL statement fails, all SQL statements in the batch will be cancelled
  • MySQL transaction processing only supports InnoDB and BerkeleyDB Data table type

Transactional ACID principle

Atomicity

All operations in the whole transaction are either completed or not completed. 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.

Consistency

A transaction can encapsulate state changes (unless it is read-only). A transaction must always keep the system in a consistent state, no matter how many concurrent transactions are at any given time. That is to say, if multiple transactions are concurrent, the system must also operate as a serial transaction. Its main characteristics are preservation an invariance, Taking the transfer case as an example, if there are five accounts, and the balance of each account is 100 yuan, 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, such as 5 yuan between accounts a and B, 10 yuan between accounts C and D, and 15 yuan between accounts B and E, the total amount of the five accounts should still be 500 yuan, This is protection and invariance.

Isolated

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.

Persistent

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.

Basic grammar

-- 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
START TRANSACTION  

-- Commit a transaction to the database
COMMIT

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

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

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

test

/*
A Buy a commodity with a price of 500 yuan online and transfer it through online bank
A Your bank card balance is 2000, and then pay 500 to merchant B
 Merchant B's bank card balance at the beginning is 10000

Create a database shop and create a table account, and insert 2 pieces of data
*/

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)

-- 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

Indexes

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 time of grouping and sorting can be significantly reduced
  • Full text search field for search optimization

classification

  • Primary key index
  • Unique index
  • General index (Index)
  • Full text index (FullText)

Primary key index

Primary key: an attribute group can uniquely identify a record

characteristic:

  • The most common index type
  • Ensure the uniqueness of data records
  • Determine the location of specific data records in the database

Unique index

Function: avoid duplicate values in a data column in the same table

Difference from primary key index:

  • There can only be one primary key index
  • There may be more than one unique index
CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- or UNIQUE KEY `GradeID` (`GradeID`)
)

General index (Index)

Function: quickly locate specific data

be careful:

  • Both index and key keywords can set the general index
  • Fields that should be added to query criteria
  • Too many general indexes should not be added, which will affect the operation of data insertion, deletion and modification
CREATE TABLE `result`(
   -- Omit some code
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- Add when creating table
)

-- Add after creation
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

Full text index

Function: quickly locate specific data

be careful:

  • 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
/*
#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 creates an index on an existing table
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX Index name
                    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 ignoring 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 full-text index version, storage engine and data type support

MySQL 5.6 In previous versions, only MyISAM storage engine supported 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.
*/

Guidelines for indexing

  • 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

Indexed data structure

-- When creating the above index, we can specify the index type for it, which can be divided into two categories
hash Type index: single query is fast and range query is slow
btree Index of type: b+Tree, the more layers, the exponential growth of data volume (we use it because innodb (it is supported by default)

-- Different storage engines support different index types
InnoDB Supports transaction, row level locking, and B-tree,Full-text Index, not supported Hash Indexes;
MyISAM Transaction is not supported, table level locking is supported, and B-tree,Full-text Index, not supported Hash Indexes;
Memory Transaction is not supported, table level locking is supported, and B-tree,Hash Index, not supported Full-text Indexes;
NDB Supports transaction, row level locking, and Hash Index, not supported B-tree,Full-text Equal index;
Archive Transaction and table level locking are not supported B-tree,Hash,Full-text Equal index;

Test index

Create table app_user: 

CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT 'User nickname',
`email` varchar(50) NOT NULL COMMENT 'User mailbox',
`phone` varchar(20) DEFAULT '' COMMENT 'cell-phone number',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT 'Gender (0):Male; 1: (female)',
`password` varchar(100) NOT NULL COMMENT 'password',
`age` tinyint(4) DEFAULT '0' COMMENT 'Age',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app User table'

Batch insert data: 100w

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('user', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

Index efficiency test

No index

SELECT * FROM app_user WHERE name = 'User 9999'; -- Viewing time
SELECT * FROM app_user WHERE name = 'User 9999';
SELECT * FROM app_user WHERE name = 'User 9999';

mysql> EXPLAIN SELECT * FROM app_user WHERE name = 'User 9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
        rows: 992759
    filtered: 10.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)

Create index

CREATE INDEX idx_app_user_name ON app_user(name);

Test general index

mysql> EXPLAIN SELECT * FROM app_user WHERE name = 'User 9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ref
possible_keys: idx_app_user_name
        key: idx_app_user_name
    key_len: 203
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = 'User 9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = 'User 9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = 'User 9999';
1 row in set (0.00 sec)

Keywords: MySQL

Added by martins on Wed, 29 Dec 2021 11:48:23 +0200

Popular Keywords

©2025 Programming VIP