Mysql efficient programming reading abstract-advanced application

MYSQL advanced application


I. Transaction Processing and locking
1. storage engine provided by mysql)


MyISAM's default high-speed engine does not support Transaction Processing
InnoDB supports row locking and transaction processing, which is slower than MyISAM.
/* The predecessor of the ISAM MyISAM engine. Standard installation is no longer available after mysql5.0
MERGE treats multiple MyISAM tables as one table ????
MEMORY and HEAP only store data in MEMORY
Falcon is a new engine that supports transaction processing.
ARCHIVE compresses and saves data (only insert and select operations can be performed)
CSV stores data in CSV format (used for cross-platform Data Exchange )*/


Tips ~~~ Show create table custom; the data displayed after the statement is executed is disorganized. However, if you replace the semicolon at the end with \ G, the result will appear more organized.


Alter table custom engine = MyISAM; modify the table storage engine. If not, check whether my. ini has activated innoDB.




2. Transaction Processing
Select * from custom; // view the data table content. There are three data items.
Begin;
Delete from custome;
Select * from custome; // No data in the data table
Rollback;
Select * from custom; // data table 3




Set autocommit = 0;
Select * from custome // four data records
Insert into M values ('t0001 ', "",...);
Select * from custom // five pieces of data
Rollback; // four data records


Begin;
Insert into M values ('t0001 ', "",...);
Insert into M values ('t0002 ', "Wang San ",...);
Savepoint sp;
Insert into M values ('t0003', "",...);
Rollback to savepoint sp; // return to Wang San


Note: After several commands are executed, Jiangbei automatically submits them, which is out of transaction processing control.
Drop database; drop table; drop; alter table;


3. Separation level of previous transaction processing in multi-user data updates
The higher the separation level, the higher the data integration, but the lower the runtime !!
Transaction Processing isolation level:
Non-committed reads cannot be repeated read fantasy reads
Read uncommitted 1 1 1

Read committed 0 1 1

Repeatable read0 0 1

Serializable 0 0 0

Non-committed read is also called dirty read ~ Can read uncommitted update data from other transaction processing. Read uncommitted level is for other slave
The read Action in transaction processing does not have any display separation level. It is generally not recommended!


Cannot be read repeatedly ~ The same data is read multiple times in a transaction, but the status of the data read by the update action of other transactions
Changed. Because of the update action of transaction processing A, the first and second results of transaction processing B are different!
The isolation level that occurs below the read committed level. To avoid this level, you must increase the isolation level to the read committed level.


Set session transaction isolation level read committed;


Fantasy reading ~~ When a transaction reads the same data multiple times, the insert/delete action is performed for other transactions.
The data that does not exist during the first read or disappears from the first read results.
To remove the value, the isolation level is upgraded to the serializable level.