MySQL InnoDB Engine insider Reading Note 2

1. Full table Scan

If you want to find a highly selective record, you can use an index. However, if you find a large amount of data, such as more than 20%,

The optimizer does not use indexes, but scans the entire table.

2. Sequential read: it refers to the ability to read the required data in sequence based on the leaf node of the index, but logically, it may be random read on a physical disk,

Logical Data is more ordered. Random read: access to the secondary index leaf points cannot completely obtain results.

The primary key is used to find the actual row data.

3. Secondary index optimization: the secondary index leaf node contains the primary key, but does not contain the complete row information. InnoDB always judges the secondary index leaf node first.

Determine whether the required data can be obtained.

4. combined index: the advantage is that 2nd key values can be sorted. For example, we need to query the shopping status of a user and sort by time to retrieve the last three

Times of purchase records, available for use

Joint indexing avoids multiple sorting operations because the index itself has been sorted in the leaf node

5. the locks in MyISAM are table locks. InnoDB provides support for non-locked reads and row-level locks with no related overhead. Implement the following two types of Row-level locks:

S shared lock, allowing the transaction to read a row of Data exclusive lock X, allowing the transaction to delete or update a row of data

Intention locks are table-level locks designed to reveal the types of locks that the next row will be requested in a transaction. There are two types:



Intention share lock is lock: the transaction needs to obtain the share lock of several rows in a table.

Intention exclusive lock IX lock, the transaction needs to obtain the exclusive lock for some rows in a table

Run the show engine InnoDB status \ G command. In the transcations section, select * from t where a <4

Lock in share mode

In the new InnoDB plugin, there are three tables under information_schema: innodb_trx, innodb_locks, and innodb_lock_watis.

View the lock details:

6. consistent non-locked read Operations



Multi-version control is used to read row data in the database at the current execution time. If the read row data is deleted or updated

The read operation does not wait for the row lock to be released. On the contrary, it reads a snapshot of the row.

Design of Three row locks:

1) record lock: the lock on a single row

2) Gap lock: locks a range, but does not include the record itself

3) Next-key lock: Lock a range and lock the record itself



The default transaction isolation level of InnoDB is read repeatable, while that of SQL Server and Oracle is read commited.

Innodb_lock_wait_timeout is used to control the lock wait time. The default value is 50 seconds. It is a dynamic number and can be modified,

Innodb_rollback_on_timeout sets whether to roll back the transaction after waiting for timeout

7. Transaction in progress

TPS calculation method: (com_coomit + com_rollback)/time, but the premise is that all transactions must be committed explicitly.

Com_commit and com_rollback are not calculated during implicit commit and rollback.

Show global status like 'com _ commit '\ G;

View the transaction isolation level of the current session. You can use:

Select @ tx_isolation \ G;

View global: Select @ Global. tx_isolation \ G;

In addition, it is recommended that you do not use declare exit handler for sqlexception In the stored procedure to handle exceptions. The program should handle exceptions.

8. Backup

1) logical backup

Mysqldump -- all-databases> dump. SQL // back up all databases

Mysqldump -- databases db1 DB2 db3> dump. SQL // back up the specified database

During backup, use the -- Single-transcation parameter. before backup, run the start transcation command to obtain the consistency during Backup.

InnoDB is effective.

-- Lock-tables: only valid for MyISAM tables

-- Local-all-tables: Lock all tables

2) Back up binary files

[Mysqld]

Log-bin

Sync_binlog = 1

Innodb_support_xa = 1

Restore binary files

Mysqlbinlog binlog.0001 | mysql-uroot-P Test

2) Hot Standby tool xtrabackup

./Xtrabackup -- backup // full backup

Incremental Backup

Full backup:./xtrabacup -- backup

Then write down the lsn

./Xtrabackup -- prepare

./Xtrabackup -- backup -- incremental = lsn

9 Performance Tuning

1) if it is multi-core, you can modify innodb_read_io_threads and innodb_write_io_threads to increase the thread and make full use of multiple cores.

2) In terms of memory, check InnoDB's hit rate

Show global status like 'innodb % read % '\ G;

The parameter description is as follows:

Innodb_buffer_pool_reads: The number of times pages are read from a physical disk.

Innodb_buffer_pool_read_ahead: Number of pre-reads

Innodb_buffer_pool_read_ahead_evicted: Number of pre-read pages that are replaced from the buffer pool if they are not read. Generally

Used to determine the pre-Read efficiency

Innodb_buffer_pool_read_requests: Number of times pages are read from the buffer pool

Innodb_data_read: Total number of bytes read

Innodb_data_reads: Number of read requests initiated. The number of pages that may need to be read each time

Buffer Pool hit rate = innodb_buffer_pool_read_requests/

(Innodb_buffer_pool_read_requests + innodb_buffer_pool_read_ahead + innodb_buffer_pool_reads)

Average number of bytes read each time = innodb_data_read/innodb_data_reads

3) The test tool sysbench is divided into three stages: Prepare, run, and clean.

For example:

Sysbench -- test = fileio -- file-num = 16 -- file-total-size = 2G prepare

Generate 16 files

Then test the random reading capability.

Sysbench -- test = fileio -- file-total-size = 2g -- file-test-mode = rndrd-max-time = 180 -- Max-

Requests = 1000000 -- num-thread = 16 -- init-RNG = on

Cleanup

Sysbench -- test = fileio -- file-num = 16 -- file-total-size = 2G cleanup



4) TPC testing tool tpcc-MySQL

The unit of tpcc-C performance is tpmc, short for transcation per minute.

The testing tool for win is: HTTP; // code.google.com/p/david -mysql-tools/download/list