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