Basic knowledge of MySQL

The history of database Logical Structure: Hierarchical type, network type, relational type (RDBMS relational database management system). Our common MySQL is the relationship type, its core is the relational table. In order to control its data access and relationship maintenance, it also provides additional functions, such as data security authentication, authorization implementation, and partial data access control index; The user sees only the data that he can see implemented in the view.

MySQL's current development: MySQL was acquired by Oracle and is now mariadb,webscaledb based on MySQL improvements.

MySQL is based on the C/S architecture, the client is mysql,server for mysqld,client and server interaction is implemented by the SQL protocol. There are two kinds of implementation: 1, with the development of the command line, the command line directly write SQL statements, to make additional deletion check. 2. Encapsulate the SQL protocol into an API, such as a JDBC driver, to connect using the JDBC driver.

SQL statements: DDL (Create, drop, alert), DML (INSERT, delete, update, select), DCL (Grant, REVOKE). is the interface that the user interacts with the mysqld.

In order to support concurrency, a transaction is provided, i.e. Acid,a atomicity C consistency I isolation D persistence. Inodb is a support transaction, MyISAM no transaction. The isolation level of the transaction, read UNCOMMITTED, read-committed, reread, serializable.

The user at the command line or the API interface input SQL statement, based on the socket communication SQL statement to mysqld, in mysqld with SQL interpreter, then the user operation, so in Mysqld also provides file Manager, cache Manager, disk operators, Transaction manager, lock manager, Recovery Manager.

Transaction log: There is a large number of random IO on the mechanical hard disk, in order to improve the write performance, the data is written to the transaction log (continuous IO) before writing to the hard disk. Logging: A simple record of the operation.

　　Data write process: Open Transaction (transaction manager)--Write memory (cache manager)--Write transaction log (Cache manager)--Commit Transaction (transaction manager)-- > Write to database hard disk space (Disk Manager). If the transaction is not committed, it can also be rolled back (recovery manager).

Index: To provide an index for easy lookup, the index points to the location of the corresponding data on disk, the storage format of the index, and the lookup algorithm is critical to the reading of the data. The index is divided into primary index, level two index ....

　　Data lookup process: First find the query cache, if found to return directly, if the query cache is not analyzed by the parser, and then go to index space to read the top-level index, such as memory, based on the top-level index level of lookup to the specified data.

MYSQLD Internal structure:

The first paradigm: The field is the basic data type; the second paradigm: attributes are completely dependent on the main building; the third paradigm: non-primary attributes cannot appear in multiple tables of an entity at the same time.

The constraint of relational database: main health constraint, external health constraint, conditional constraint, non-null constraint.

Common component architectures for MySQL:

MySQL development tool, draw red line for common tools:

MySQL installation directory:

　　

　　

Basic knowledge of MySQL