MySql performance-related concepts (performance tip0)

Topic: This is something that was recently recorded in high-performance MySqL version 2 ~

# Read locks (shared locks) and write locks (exclusive locks): Read locks are shared and are not blocked. Reading the same resource does not affect each other. Write locks are exclusive. A write locks block other read/write operations.

# Granularity of locked objects: Table locks and row locks.

Table lock: the entire table is locked. When a write operation is performed, the write lock is applied and the resource access is exclusive. When no data is written, read locks are applied, and the read locks do not conflict with each other. The write lock has a higher priority than the read lock. Low overhead.

Row-Level Lock: It locks a row with high overhead and supports maximum concurrent processing.

# Transaction: ACID

A atomicity: A transaction is an atomic unit of work that cannot be divided. Internal work is not partially executed, either completely or not executed at all.

C consistency: the database is switched from one consistent state to another consistent state. Data changes during transaction execution do not affect the database data.

I isolation: the results of a transaction are visible to other transactions only after the transaction is completed.

D Persistence: the result changes after a transaction is committed will be persistent and will not disappear immediately.

# MVCC: Multi-version Concurrency Control

# Several storage engines:

MyISAM: adds a table lock. During select queries, you can insert (insert concurrently) data in the same table. You can index data based on the first 500 characters of BLOB and TEXT.

InnoDB: Transaction engine, suitable for handling a large number of short-term transactions. It is based on Clustered indexes and does not compress indexes.

Memory: heap-based, Memory storage, and support for hash Indexes

Archive: only supports insert and select, does not support indexes, buffers data write operations, and uses the zlib Algorithm for compression during insertion, which consumes less I/O than the MyISAM disk, all select queries perform full table scans, which is suitable for logging and supports row-level locks.

Storage Engine	MySqL version	Transactions	Lock Granularity	Main Applications	Not applicable
MyISAM	All	Not Supported	Table locks Supporting Concurrent Inserts	Select, insert, high load	Scenarios where both reading and writing are important
MyISAM Merge	All	Not Supported	Table locks Supporting Concurrent Inserts	Segmented archiving and data warehouse	Many global searches
Memory (Heap)	All	Not Supported	Table lock	Intermediate computing, static data search	Large dataset, persistent Storage
InnoDB	All	Supported	Supports MVCC row-level locks	Transaction Processing	None
Falcon	6.0	Supported	Supports MVCC row-level locks	Transaction Processing	None
Archive	4.1	Supported	Supports MVCC row-level locks	Log records, aggregate analysis	Random read, update, and delete
CSV	4.1	Not Supported	Table lock	Log records, loading external data on a large scale	Random reading and indexing required
Blackhole	4.1	Supported	Supports MVCC row-level locks	Log record or synchronous Archiving	Unless for special purpose, it is not suitable for any occasion
Federated	5.0	N/	N/	Distributed Data Source	Unless for special purpose, it is not suitable for any occasion
NDB Cluster	5.0	Supported	Row-Level Lock	High reliability	Most typical applications
PBXT	5.0	Supported	Supports MVCC row-level locks	Transaction Processing and logging	Clustered index required
SolidDB	5.0	Supported	Supports MVCC row-level locks	Transaction Processing	None
Maria	6.x	Supported	Supports MVCC row-level locks	Replace MyISAM	None

From Change Dir