MySQL learning notes -- MySQL architecture and storage engine

wwz_ henu 2022-02-13 05:26:31 阅读数:406

mysql learning notes mysql architecture

notes : Used MySQL Technology insider Second edition books

1. Define databases and instances

database : A collection of physical operating system files or other forms of file types . That is, the file that stores data , for frm、MYD Etc , It can also be data in memory .
Database instance : A program for manipulating database files .
Instances and databases often correspond one-to-one , That is, an instance is used to operate a database , A database is operated by an instance . In the case of clusters , There may be cases where a database is used by multiple database instances .

Mysql It's single process multithreading . The performance of database instance on the system is a process .

When you start an instance , Will read the configuration file , Start the database instance according to the parameters of the configuration file .

mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
Read the configuration file from left to right , If there are the same parameters , Subject to the right parameter .
2.Mysql Architecture

Mysql The most important feature different from other databases is the plug-in table storage engine . Plug in storage engine architecture provides a series of standard management and service support , These standards have nothing to do with the storage engine itself , It may be necessary for each database system itself , Such as SQL Analysis and optimizer , The storage engine is the realization of the underlying physical structure , Each storage engine can be developed according to its own wishes .

The storage engine is table based , Not a database .

3. Storage engine

Each table can be used as required , Using different storage engines . Each storage engine has its own characteristics . You can see Mysql Supported engines and their differences .

InnoDB: Support transactions , Mainly for online transaction processing (OLTP). Row lock design , Full text index support , Support foreign keys , Supports non locked read , That is, the default read operation does not generate locks .

MyISAM: Unsupported transaction , Watch lock design , Full text index support , Mainly for online analysis and processing (OLAP). from MYD and MYI form .MYD Used to store data files ,MYI Used to store index files . The cache pool only caches index files .

NDB: Cluster storage engine ,share nothing Cluster architecture . Data in memory , You can put non index data on disk , The search speed of primary key is very fast .

          notes : This engine is independent , from download

Memory: Store the data in the table in memory , If the database restarts or crashes , The data will disappear , Suitable for making temporary tables . By default HASH Indexes . Only table locks are supported , I won't support it TEXT And BLOB Column type .Mysql Database usage Memory The storage engine stores the query intermediate result set , If the result set is larger than Memory Capacity settings , Or contain TEXT or BLOB The type field , be Mysql Will transfer it to MyISAM Save the engine table to disk , and MyISAM Do not cache data files , Therefore, there is a loss of query performance .

Archive: Only support INSERT and SELECT operation . Support the index 、 Row lock ; Use zlib The algorithm compresses the data rows and stores them , The compression ratio is generally 1:10, Suitable for storing archived data , Like a journal .

Federated: Does not store data , Point to remote Mysql Table on server . This engine is not turned on by default , Turn on federated The storage engine only needs to be in my.cnf Add to file ‘federated’ Can .

Maria: be used for MariaDB in , In place of MyISAM.

copyright:author[wwz_ henu],Please bring the original link to reprint, thank you.