Introducing the storage engine and architecture tutorial in Mysql

Mysql architecture and storage engine

1.1 Define database and instance

Database: frm, MYD, MYI, ibd, files ending in, when using NDB engine , the database file is placed in memory
Example: composed of background threads and shared memory. Shared memory can run in the background ready to share, and the database instance is what actually operates the database.
The relationship between instances and databases is one-to-one correspondence.
MySql is a single-process multi-thread architecture database, and the instance behaves as a process in the system.
It is similar to Oracle's parameter file spfile, but unlike Oracle, if there is no parameter file, the database will fail when starting the instance.

1.2 Composition of Mysql:
Connection pool component
Management services and tools components
sql interface component
Query Analyzer Component
Optimize components
Cache component
plug-in storage engine
physical file
The storage engine is based on table structure, not database

1.3Mysql storage engine
Mysql predefined storage engine interface to write your own storage engine
InnoDB storage engine
Supports things. The design goal is mainly for online transaction processing applications. It is characterized by row lock design, supports foreign keys, and supports non-locking reads similar to Oracle. By default, read and write operations will not generate locks.
Placed in a logical table space, the storage engine table is placed in an independent ibd file.
For storage in the table, an aggregation approach is adopted
MyISAM storage engine
Does not support things, table design, supports full-text index. For some OLAP database applications
The buffer pool just caches some index files and does not buffer data files
The engine table is composed of MYD and MYI. MYD is used to store data files, and MYI is used to store index files. You can use the myismpack tool to further compress the files.
NDB engine
It is a cluster storage engine. Its structure is a share noting cluster structure. All data is placed in the memory, and the primary key search speed is extremely fast.
And by adding NDB data storage nodes, database performance is linearly improved, creating a highly available and high-performance cluster system.
Memory engine
The data in the table is stored in memory. If the database is restarted or crashes, all the data in the table will disappear. Suitable for storing temporary tables
By default, a hash index is used, not a B+ tree index.
Only table locks are supported, and concurrency performance is relatively poor.
Archive storage engine
Use zlib algorithm for compression and storage. Very suitable for storing excessive data, such as log information
Use row locks to implement highly concurrent insert operations
Mainly provides high-speed insertion and compression functions.
Federate storage engine
The engine table does not store data, but only points to a MySQL database server. It does not support tables of heterogeneous databases.
Maria storage engine
Replaces the original MyiSAM storage engine and becomes the default engine of Mysql
Supports cached data and index files, applies row lock design, provides MVCC function, supports transaction and non-transaction security options
Query the storage engines supported by the mysql database through show engines

1.4 Connect to Mysq
It is a connection process that communicates with the Mysql database instance.
Commonly used communication methods: pipes, named pipes, named names, TCP/IP sockets, UNIX domain sockets
TCPIP:
mysql -h192.168.0.0.1 -u david -p
Mysql will check a permission view to determine whether the requested IP is allowed to connect to the Mysql instance. The table is user
Named pipes and shared memory
Start --enable>name-pipe in mysqlconfig file
Provide shared memory, in the configuration file, add --shard-Memory implementation
UNIX socket
mysql -udavid -S /tmp/mysql.sock

【Related recommendations】

1. Free mysql online video tutorial

2. MySQL latest manual tutorial

3. Those things about database design

The above is the detailed content of Introducing the storage engine and architecture tutorial in Mysql. For more information, please follow other related articles on the PHP Chinese website!