affair

Either all succeed or all fail

Transaction principle: ACID principle, atomicity, consistency, isolation, persistence (dirty read, phantom read...)

1. Basic concepts

Atomicity

Either all succeed or all fail

Consistency

The data integrity before and after the transaction shall be consistent

Isolation

Transaction isolation is that when multiple users access data concurrently, the transactions opened by the database for each user cannot be disturbed by the operation data of other transactions, and the transactions should be isolated from each other

Durability

Once the transaction is committed irreversibly, it is persisted to the database

Some problems caused by isolation:

Dirty read:

It means that one transaction reads uncommitted data from another transaction

Non repeatable:

When a row of data in a table is read in a transaction, the results of multiple reads are different (this is not necessarily wrong, but it is wrong in some cases)

Virtual reading (unreal reading)

It refers to that the data inserted by another transaction is read in one transaction, resulting in inconsistent reading

2. Execute transaction

/*
mysql Transaction auto commit is enabled by default
set autocommit = 0	close
set autocommit = 1	open
*/
-- Manual transaction processing
-- First, turn off automatic submission
SET autocommit = 0

-- Transaction on
-- Mark the beginning of a transaction, starting from the beginning sql All within the same transaction
START TRANSACTION

INSERT xx
INSERT xx

-- Commit: persistent (successful!)
COMMIT

-- Rollback: return to the original state (failed!)
ROLLBACK

-- End of transaction
-- Finally, turn on auto submit
SET autocommit = 1

-- understand
-- Save point name. Set a transaction save point, just like archiving
SAVEPOINT Save roll call
-- Rolling back to a savepoint is like reading a file
ROLLBACK TO SAVEPOINT Save roll call
-- Delete savepoint
RELEASE SAVEPOINT Save roll call

3. Simulation scenario:

-- transfer accounts
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

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

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

-- Simulated transfer: transaction
-- Turn off auto submit first
SET autocommit = 0;
-- Start a transaction
START TRANSACTION
-- A-500
UPDATE `account` SET money = money - 500
WHERE `name` = 'A'
-- B+500
UPDATE `account` SET money = money + 500
WHERE `name` = 'B'

-- Commit transaction
COMMIT;
-- RollBACK 
ROLLBACK;

-- Restore defaults
SET autocommit = 1;

The simulation scenarios of these transactions seem useless and need to be executed one by one. It seems very troublesome. In fact, when the business is written in java statements, it will be executed automatically. For example, when it is written in try... catch, COMMIT will be executed if there are no exceptions, and ROLLBACK will be executed if there are exceptions. This is almost the same operation

Indexes

In a table, there can only be one primary key index and multiple unique indexes

1. Basic concepts

• primary key index

  • Unique identifier. The primary key cannot be repeated. Only one column can be used as the primary key

• unique key

  • Avoid duplicate columns. Unique indexes can be repeated, and multiple columns can be identified as unique indexes

• General index (key/index)

  • By default, it is set by using the index and key keywords

• Full text index (fulltext)

  • Only under a specific database engine
  • Fast positioning data

2. Basic grammar

-- Indexes
-- Use of index
-- 1,Add indexes to fields when creating tables
-- 2,After creation, increase the index

-- Show all index information
SHOW INDEX FROM student

-- Add a full-text index (index name) column name
ALTER TABLE school.`student` ADD FULLTEXT INDEX studentname (studentname);

-- explain Used to analyze sql Status of implementation
-- Non full text index
EXPLAIN SELECT * 
FROM student

-- Full text index
EXPLAIN SELECT * 
FROM student
WHERE MATCH(studentname) AGAINST('Zhang');

3. Test index

/*
report errors:
	This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
*/
-- After reporting this error, baidu added this line of code
SET GLOBAL log_bin_trust_function_creators=1;
-- Insert 1 million pieces of data
-- It must be written before writing a function. It is a flag
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),'2412451@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;

-- Elapsed time: 1 min 21 sec
SELECT mock_data();

-- Elapsed time: 1.276 sec
SELECT * FROM app_user
WHERE `name` = 'User 9999';

-- id _ Table name _ Field name
-- create index Index name on surface(field)
CREATE INDEX id_app_user_name ON app_user(`name`);

-- Elapsed time: 0.004 sec
SELECT * FROM app_user
WHERE `name` = 'User 9999';

There is little difference between indexes when there is a small amount of data, but the difference is very obvious when there is big data

4. Indexing principle

• The more indexes, the better
• Do not index data that changes frequently
• Tables with small amounts of data do not need to be indexed
• Indexes are usually added to fields commonly used for queries

Indexed data structure

hash type index

Btree: default data structure of innoDB