Overview of data query process from PHP to MySQL _ MySQL

Abstract: This article describes how to initiate a mysql Query request from the PHP layer to the result set returned by mysqlserver, and briefly describes the actions and components that may be involved in each layer. Grasp the entire interaction process from the global perspective. Shows the PHP-SQL component layers from the Php layer to the MySQL layer: Summary

This article describes how to initiate a mysql Query request from the PHP layer to return a result set from the mysql server, and briefly describes the actions and components that may be involved in each layer. Grasp the entire interaction process from the global perspective.

PHP layer to MySQL layer

Shows the levels of components from Php to SQL:
MySQL "src =" http://img.bitscn.com/upimg/allimg/c150828/1440K01331GZ-19342.jpg "title =" \ "/>
Ext/mysqli and ext/mysql are client-side extension libraries (library functions) at the client script level. The Mysqli Library is an extension version of the mysql Library. the extension version adds the Bind Column binding. PDO (PHP Data Object) is another Data-oriented extension Library. These extension libraries are directly oriented to programmers, and their underlying implementation is the mysql connection engine (such as mysqlnd and libmysql) (refer to http://bbs.chinaunix.net/thread-3679393-1-1.html, http://blog.csdn.net/treesky/article/details/7286098 ).

Mysqlnd and libmysql are the database connection driver for PHP clients. Libmysql is a general database connection engine, while mysqlnd is a connection engine developed exclusively for PHP, from Zend.
When PHP queries a database by calling the mysql_query () function in the extension Library (ext/mysqli and ext/mysql), the Zend Engine uses mysql (mysqlnd and libmysql) the query engine sends a query request to the MySQL server.

MySQL-layer data query

After the MySQL server receives the query request from the client, the query execution process is shown in:
1. query the cache. if hit, the result set is directly returned to the client. Otherwise, go to step 2.
2. perform SQL statement parsing, preprocessing, query optimization, and other operations in sequence to generate a query execution plan (the select query execution plan can be viewed through the explain select statement)
3. the query execution engine of the MySQL server calls the storage engine to query data based on the query execution plan. When the last layer of the SQL statement is executed, the query result set is generated.
4. the query result set is sent to the client in two ways: the MySQL server caches the result set or does not cache the result set, which is set by the SQL _BUFFER_RESULT parameter. In addition, if you set SQL _CACHE, a copy of the query result set is stored in the query cache (related to step 1 ).

SQL _CACHE parameters:
Complex (multiple associated) queries are divided into multiple simple queries, because
1) cache hits for simple queries,
2) cache of complex query results is prone to invalidation (too many tables are associated)
3) low holding rate of simple query locks

MySQL Server to PHP layer

In the communication mode, the communication between the MySQL Server and the client is "half-duplex communication". This means that the client and the Server can only have one read and the other must be written.

Advantage: The protocol is simple, and the write permissions on the client and server are mutually exclusive.

Disadvantage: you cannot control the traffic. one end starts to send a message, and the other end must accept the message completely before responding to it.

Revelation: The result set after the server query is sent to the client. the client (the query engine of the client, such as mysqlnd) must be completely accepted. Therefore, if you only need a few lines, remember to add limit in the SQL statement and use select * less *.

In the result set return mode, each row of records is packaged through the client-server communication protocol and then handed over to the lower-layer tcp protocol. of course, at the tcp layer, you can first cache the protocol packages for each line of record to form a large package at the sending (transparent to the application layer ).

The MySQL server can release the buffer occupied by the result set only after sending all the result sets to the client.

Server cache mode

Client Command: mysql_unbuffer_query (). The result set cache is not set in the SQL Drive Extension (mysqlnd) of the client. Therefore, when fecth_array_xxx reads a record from the result set, read from the server buffer.

Serverless cache mode

Client Command: mysql_query (). buffer is set in the SQL Drive Extension (mysqlnd) of the client to cache the result set of the server. Therefore, when fecth_array_xxx reads a record from the result set, is to get the row directly from the buffer of mysqlnd extension.

Summary

If the result set is large: the serverless cache mode can reduce the server's memory pressure, but it occupies the client's memory. In this way, we can only choose from the situation.

PHP layer to user layer

On the client, the server is connected to the mysql extension engine (libmysql or mysqlnd), while the user layer is through the extension Library (ext/mysql or ext/mysqli) interact with the mysql engine (the inspiration is to call the engine's api to read the result set ).

The libmysql engine and mysqlnd engine have different mechanisms. The main difference is that mysqlnd is written in php and compiled into zend. While libmysql is a common Library, zend needs to call this library to achieve database connection. In this case, mysqlnd and zend have better adhesion. when data is transmitted to the user layer, a copy of data is missing. The specific architecture differences are shown in. In the figure, Pentagram indicates the cache buffer.
VcWxvrLjw + examples/H/examples/nT2lplbmTS/cfmtcRTUUzH/examples/bavtq/X99Do0qq + examples/examples + 3b/isunRr9bQtcS5/examples + examples/ release + 9sbS94bn7vK + Release/release/b7dv + release + DQoxKSBteXNxac/release + 3osvNsunRr8frx/release/bavzai5/XplbmTS/release + Release/K/ bTTuMPH + logs/bav1rTQ0FNRTNPvvuS1w7W9veG5 + logs/Kho7/logs/jT8tbQtsHIob3hufvKtc/summary = "conclusion-1"> summary

Mysqlnd and zend are more cohesive. in the SQL query driver, mysqlnd accesses the database through the zend Engine and directly stores the results in the zend buffer, compared with the libmysql driver (independent from zend), one result set cache copy is missing.