Lesson four supplement the stereoscopic view of 01--database system

Database system Architecture I. Classification of database systems: Object-oriented, relational, object-relational 1, Object-oriented database (OODBS)

(1) Object-oriented database system is a kind of persistent and Sharable object Library and manager, and it is based on Oo thought, so this kind of database also has the concepts of encapsulation, class, class level, etc.
(2) The currently popular open source object-oriented database: db4o

2. Relational database (RDBMS)

(1) Relational database based on the characteristics of the table, convenient for users to use the query language SQL to increase and revise the search, the current mainstream open source products are MySQL

3. Object-relational database

(1) This concept preserves the structure of the relational database, but allows the columns in the relational table to contain a complex object that can bundle the processing of complex data (a stored procedure), and SQL allows the invocation of "object methods" that are equivalent to the relational type.
(2) At present, mainstream database vendors support some object-oriented concepts more or less, and in MySQL we also introduce the support of OpenGIS (open geodata Interoperability specification).

Ii. system Architecture of the relational database 1, database management system and operating system there are many similar places, for example:

(1) Support a large number of users to read and write a copy of the data (table), which requires reasonable processing concurrency ===================== "cache technology (buffer) to provide fast access under large concurrent volume." Concurrent access requires that the memory management system of the database system adopt a similar operating system virtual memory management method and algorithm.
(2) Network aspects of database management system ========== "network protocol is designed to be able to resolve the client's query at high speed

2. Basic function Module of DBMS (1) Network Client Connection

（1.1）客户端连接到DBMS有两种方式，一种是通过通信方式，另一种是通过接口连接，接口方式连接到DBMS的，数据库系统已经成为应用系统一部分，即称为嵌入式数据库系统。（1.2）网络通信方式连接到DBMS的，一般采用通用的连接器：例如，ODBC  JDBC .NET等连接。（1.3）不管是网络通信方式，还是接口直连内嵌式数据库系统，应用程序客户端都要发送SQL命令（查询语句或预处理语句）到数据库系统，获取命令的执行结果集，解析和处理结果集，最后展示给用户。

(2) SQL parsing and optimization (Parser+optimizer)

The 
  (2.1) SQL interface--sql handles--sql optimizations--sql Execution (2.2) The query interface is the SQL interface (SQL Interface), which receives SQL statement (2.3) SQL statement processing, including: statement legality check--semantic check --Get object resolution lock--check the data access rights, these four steps. Where: (2.3.1) statement legality check: when the corresponding SQL statement is not found in the cache, the legality of SQL begins to be checked. In the process of grammar checking, the table names and lists contained in the SQL statements are not parsed, only the syntax check, (2.3.2) semantic check: SQL conforms to the syntax, then the semantic check, that is, the statements in the fields, tables and other content to check, to determine whether the field, the table is in the database.        If the syntax and semantics are wrong at the same time, the system will first prompt for syntax errors, and then prompt for column or table name errors when the syntax is correct.        (2.3.3) To obtain object resolution Lock: One of the main tasks of the database is to ensure the data integrity in a large number of concurrent situations, when the syntax and semantics are correct, the system will lock the object we need to query, mainly to ensure data consistency, to prevent the query process, other users of the object structure to change. (2.3.4) Check of data access: After syntax and semantic checking, the server also checks for permissions, and if the user connecting to the server does not have access to the database, the client is not able to obtain the data. It is important to note that the database server is preceded by a syntax and semantic check before it checks access rights. (2.4) SQL optimization: The database query optimizer (Optimize) is an integral part of the RDBMS server, and the task of the query optimizer is to optimize an SQL statement by generating an execution plan with the optional execution plan to find the lowest estimated cost. When a SQL is fed into the RDBMS server, it is not parsed and submitted to the database query optimizer. The query optimizer will perform query rewriting and expression evaluation to produce an alternative execution plan. The number of execution plans that can be selected, depending on the size of the planned space defined in the RDBMS (2.5) SQL execution: Most relational database server processes do not go directly to the database query after receiving the client SQL, but first look in the database cache, whether there is an execution plan for the same statement. If in the database cache, just have someone else to use this query statement, the server process will directly pass this data to the client, save the follow-up work. 

Therefore, with high-speed data cache and high hit rate, it can improve the query efficiency of SQL statement, on the one hand, reading data from memory is more efficient than data file from hard disk, on the other hand, it is also because of the reason of this statement parsing.

(3) Storage engine

三级结构对数据库的组织从内到外分三个层次描述：内模式、概念模式、外模式。内模式是真正存储数据的，概念模式和外模式仅是一种逻辑表示数据的方法，但却可以放心大胆使用它们，这是靠DBMS的映射功能实现的。查询优化和查询解析实现了概念模式的功能，然而对数据库数据的访问归根到底是需要访问磁盘上的物理文件的。

MySQL Database system

Functionally, MySQL is a modular design based on components, in fact, the MySQL architecture is neither strictly component-based nor truly modular. In a strict sense, the MySQL system integrates code based on function libraries and data structures.

MySQL subsystem and core library

MySQL contains the following subsystems and core libraries:
--Network connection and Network Communication protocol subsystem (TCP/IP protocol, which provides read and write parsing and sending of packets to other subsystems)
--Thread, process, and memory allocation subsystem (thread subsystem tracks various threads to ensure that the server owns the thread to handle it when the client sends the request)
--Query parsing and query optimization subsystem
--Storage Engine Interface Subsystem
--Various storage engine subsystems
--Security Management Subsystem (the first line of defense against external attacks by the database system, relying on user authentication and access control to protect the server)
--Log Subsystem
--other subsystems, such as copy function, error handling, etc.
--mysys Core Library files

(1) The connection between subsystems, example: a client through the network to connect MySQL server, do SQL query operations, subsystem collaboration process:

  (1.1) The network connection subsystem performs a series of underlying tasks related to the network protocol, and (1.2) The network connection subsystem calls the thread subsystem, which provides a thread to process the connection, which is called the connection thread (the connection thread may be from the thread cache threads Cache, possibly newly created); (1.3) connection thread: (1.3.1) First Call security management subsystem to verify the legality of user access (whether the user has access to the database, whether the password is correct, etc.): (1.3.2) When the connection thread confirms that the connection is valid, the data that is obtained (SQ L statement) According to the situation, if the query and the last query exactly the same, directly from the query cache value, if the cache can not directly get the query, the resolution subsystem is called to parse the SQL statement. At the same time, if the logging function is configured, the log system is also called to record the information (SQL statement, execution time, execution of the user, etc.), and (1.3.3) The parsing subsystem passes the parsing result to the optimization subsystem and optimizes the SQL statement. At the same time, it involves a security question: Does the user have access to the feature table? The security management subsystem will be called again to confirm permissions before the table can be manipulated. (1.4) Operate the table and send a series of requests to the storage Engine interface subsystem. Requests destined for subsystems can be considered as additions and deletions. SQL operations (1.4.1) Storage Engine interface subsystem automatically translates a series of requests into a specific storage engine subsystem method (for example, the InnoDB Storage engine subsystem). The so-called automatic, is actually the multi-state in object-oriented programming.        In a nutshell, the caller feels like interacting with the interface subsystem, but is actually dealing with a specific storage subsystem, such as the InnoDB storage engine subsystem. (1.4.2), the response storage subsystem module sends the results of SQL execution to the client, and if it does not go well, send an error message. Finally, the server returns control to the connection thread, the connection thread does cleanup work, and waits for the client's connection or other queries again until the client enters the QUIT command, which is the end of the session. (1.5) Supplement -1:mysql daily 90% of the work as described in the process is running, but there are exceptions, such as: If the server plays a replication function, then its replication subsystem will continue to read the binary log files, if the server is playing from the server role, from the server will also start two threads,    are SQL threads and IO threads, which collectively accept statements passed by the master server and update from the server. (1.6) Supplement-2: All subsystems of the collaborative operation, are the core library in silent support. MySQL puts together some of the most common and basic APIs to form a core library. 

From for notes (Wiz)

Lesson four supplement the stereoscopic view of 01--database system