"MySQL Technology insider InnoDB Storage Engine" Reading notes the first chapter

MySQL Architecture and storage engine

1.1 Defining databases and instances

Database: A collection of physical operating system files or other forms of file types. The database file can make FRM,MYD,MYI,IBD the end of the file.

Example: The MySQL database consists of a background thread and a shared memory area. The database instance is the one that is really used to manipulate the database files.

The relationship between the instance and the database is typically one by one, and there may be situations where a database is being used by multiple data instances in a clustered situation.

MySQL is designed as a single-process multithreaded architecture database, which is similar to SQL Server, but differs from the Oracle Multi-Process architecture (Oracle's Windows version is also a single-process multithreaded architecture)

The performance of a MySQL DB instance on the system is a process.

If there is no parameter file in Oracle, the parameter file is not found when the instance is launched and the database failed to start. In the MySQL database, there can be no configuration file, in which case, MySQL will follow the compile-time default

The parameter setting launches the instance.

The MySQL database reads the configuration file in the order of/ETC/MY.CNF-->/etc/mysql/my.cnf-->/usr/local/mysql/etc/my.cnf-->~/.my.cnf.  If you have the same parameter in several configuration files, the MySQL database is the one that reads the parameters in the last configuration file. In a Linux environment, configuration files are typically placed under/ETC/MY.CNF. Under the Windows platform, the suffix name of the configuration file may be. CNF, or. ini.

There is a parameter DataDir in the configuration file that specifies the path where the database resides. The default datadir is/usr/local/mysql/data under the Linux operating system

You can use the command to view MySQL > show VARIABLES like ' DataDir ' \g;

　　

1.2 MySQL Architecture

Conceptually, a database is a collection of files that are organized in accordance with a data model and stored in level two storage; A database instance is a program, a layer of data management software located between the user and the operating system, and any manipulation of the database data by the user, including database definition, data query, data maintenance, Database run control is done under the DB instance, and the application can only work with the database through the DB instance.

MySQL consists of the following parts:

Connection pooling components, management services and tools components, SQL interface components, Query Analyzer components, optimizer components, buffer components, plug-in storage engines, physical files.

It is important to note that the storage engine is table-based, not a database.

1.3 MySQL Storage engine

1.3.1 InnoDB Storage Engine

The InnoDB storage engine supports things and is intended for online processing (OLTP) applications. It features a row lock design, supports foreign keys, and supports non-locking reads similar to Oracle, where the default read operation does not generate locks.

Starting with the MySQL4.1 (including 4.1) version, the InnoDB storage engine table is stored separately in a separate IBD file. In addition, the INNODB storage engine supports the use of bare devices to establish their table space.

If you specify a primary key without a display definition, the INNODB storage engine generates a 6-byte rowid for each row, which is used as the primary key.

1.3.2 MyISAM Storage Engine

MyISAM storage engine does not support transaction, table lock design, support full-text retrieval, mainly for some OLAP database applications.  Before the MySQL5.5.8 version, the MyISAM storage engine was the default storage engine (except for the Windows version). The buffer pool for the MyISAM storage engine caches only the index files, not the data files.

The MyISAM Storage engine table consists of myd and myi, MyD used to hold data files and myi to store index files. The table that is compressed with the Myisampack tool is read-only.

Before the MySQL5.0 version, the MyISAM default supported table size of 4GB, and after 5.0, 256TB of single-table data is supported by default.

1.3.3 NDB Storage Engine

The NDB storage engine is a clustered storage engine with higher availability data all in memory (after version 5.1 can put non-indexed data on disk) is a highly available, high-performance clustered system.

The connection operation for the NDB storage engine is done at the MySQL database layer, not at the storage engine level. This means that complex connection operations require significant network overhead, so queries are slow. If this problem is solved, the market for the NDB storage engine should be very large.

1.3.4 Memory Storage Engine

The Memory storage Engine (formerly known as the heap storage engine) stores the data in the table and, if the data restarts or crashes, the data in the table disappears.

Text and BLOB column types are not supported.

1.3.5 Archive Storage Engine

The archive storage engine supports only insert and select operations, and indexes are supported starting from MySQL5.1.

The archive storage engine is ideal for storing archived data, such as log information. The archive storage engine uses row locks for high-concurrency inserts, but it is not a secure storage engine in itself, and is designed primarily to provide high-speed insertion and compression capabilities.

1.3.6 Federated Storage Engine

The Federated Storage Engine table does not hold data, it simply points to a table on a remote database server.

1.3.7 Maria Storage Engine

Can be seen as a follow-up version of MyISAM. The Maria storage Engine features support for caching data and index files, application of row lock design, MVCC functionality, options to support things and non-transactional security, and better processing performance for BLOB character types.

mysql > SHOW engines\g; View supported storage engines

1.5 Connecting MySQL

The Connect MySQL operation is a connection process and the MySQL DB instance communicates. Processes communicate in a way that has pipelines, named pipes, named words, TCP/IP sockets, and UNIX domain sockets.

TCP/IP is network-based, and sockets are typically used for the same server.

Cases:

Tcp/ip:c:\ > Mysql-h 192.168.0.101-u david-p

UNIX domain sockets: mysql-udavid-s/tmp/mysql.sock

1.6 Summary

Database and instance Definitions

The architecture further highlights instances and databases

MySQL plug-in storage engine

The MySQL storage engine has no difference between the pros and cons only for unsuitable.

　　

"MySQL Technology insider InnoDB Storage Engine" Reading notes the first chapter