MySQL Learning notes

1. mysql defaults to auto-commit, which is the autocommit mode, meaning that if a transaction is not explicitly opened, then each transaction is treated as a transaction to commit the operation.

Show variables like ' autocommit ' to see the current commit status, you can use set autocommit=1/0 to change, 1 to turn on autocommit, 0 to disable, and when it's 0 o'clock all queries are in one transaction, Until an explicit commit commit or a rollback rollback. However, this modification is not valid for non-transactional tables, such as MyISAM, and it will be in the autocommit state.

It is also important to note that there will be some commands that enforce commit to commit the current transaction, in the data definition language DDL, if it is a result of a large number of data changes, such as alter Table,lock table.

The isolation level can be set by the SET TRANSACTION isolation Level command, and the new isolation levels will be in effect for the next transaction, and the isolation level of the entire database can be set in the configuration file. You can also change the isolation level of the current session only: Mysql>set session transaction Isolation levels read commited.

2. It is important to note that the storage engine should not be mixed in the transaction, because in some rollbacks only the data table of the storage engine that supports the transaction can be rolled back, and changes on non-transactional tables cannot be undone.

3. The InnoDB uses a two-phase locking protocol, where the execution of a transaction can be performed at any time, and the lock is released only when the commit or rollback is executed, and at the same time.

4. It is recommended that autocommit be disabled in addition to the transaction, you can use the lock tables, and do not explicitly perform the lock tables at any other time, regardless of the engine used.

5. The Show Table Status command looks at the relevant information for the tag: Show table status like ' user ' \g, using \g to get formatted output. Row_format represents the format of the row, rows represents the number of rows, is an estimate in InnoDB, avg_row_length represents the average number of bytes per row, data_length the size of the table data, Max_data_ The maximum capacity of the length table data is related to the storage engine, Index_length represents the size of the index, Data_free represents the amount of space that can be applied, auto_increament the next automatically added value, create_time the time the table was created, Check_time the last time the table was checked, collation the default character set and character collation for the table, checksum if it is started, saves the real-time checksum of the entire table, and creat_options other options specified when the table was created, Comment to save some extra information.

6. How to change the engine: ALTER TABLE mytable ENGINE=INNODB, the transformation process will lose the characteristics of the original engine, the use of creating and querying is sometimes a better choice:

Crate table innodb_table like myisam_table;

ALTER TABLE innodb_table ENGINE=INNODB

INSERT INTO innodb_table select * from Myisam_table;

If the data is large, it is necessary to use chunking such as:

Start transaction;

INSERT INTO innodb_table select * from myisam_table

where ID between x and y;

Commit

If it is necessary to deliberately in the process of the original table lock, to ensure that the new table and the original table data consistent, this

Percona Toolkit provides a pt-online-schema-change tool

7.

MySQL Learning notes