wwz_ henu 2022-02-13 05:26:31 阅读数:406
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 .
Read the configuration file from left to right , If there are the same parameters , Subject to the right parameter .
mysql --help | grep my.cnf order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
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 https://dev.mysql.com/downloads/cluster/ 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. https://en.javamana.com/2022/02/202202130526290533.html