MySQL performance optimization

concurrency control shared lock exclusive lock table-level row-level lock InnoDB Falcon

transaction: Start Transaction ..... Commit

ACID: atomicity, consistency, isolation, durability

A deadlock is a vicious phenomenon that occurs when two or more transactions occupy each other on a uniform resource and request locking. Deadlocks occur when multiple transactions enter a lock on the same resource in a different order. At any time, multiple errors at the same time lock a resource, must produce a deadlock.

Each transaction executes the first query, updates the rows of data, and locks the data rows in the process. Then each transaction goes into the second row of data, but finds that the row has been locked by the other, and the two transactions begin to wait for each other to finish.

Workaround: The InnoDB storage engine can predict cyclic correlation and immediately return an error.

MySQL provides a three transactional storage engine: InnoDB NDB Cluster Falcon

MySQL default operation mode is Autocommit mode. In a pro-connect, you can set by variable, enable (Enbale) and disable (Disable) SHOW VARIABLES like 'autocommit'

Four isolation levels: READ UNCOMMITTED reads uncommitted content read COMMITTED reads submissions

Repeatable READ Reread SERIALIZABLE serializable

Algorithm for database load balancing:

Random poll minimum connection first fastest response priority hash weight

High availability: Create redundant mechanisms that can be replaced online in the case of a part of the system, which is fast and reliable

Cache: Local cache, local shared-memory cache, distributed memory cache disk cache

Cache control: Time-to-live is explicitly voided when read

　　

MySQL performance optimization