Overview of PHP to MySQL data query process

HP layer to MySQL layer

Php to sql component hierarchy is as shown below:

ext/mysqli and ext/mysql are client-side extension libraries (library functions), and extension libraries at the client-side script level . Mysqli library is an extended version of the mysql library. The extended version adds column binding (Bind Column). PDO (PHP Data Object) is another data object-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 engines of the PHP side (client). libmysql is a general database connection engine, while mysqlnd is a connection engine developed exclusively for PHP and belongs to Zend. When PHP performs database query by calling the mysql_query() function in the extension library (ext/mysqli and ext/mysql), the Zend engine will issue a query request to the MySQL server through the mysql (mysqlnd and libmysql) query engine.

Data query at MySQL layer

After the MySQL server receives the query request from the client, the query execution process is as shown in the figure above:
1. Query cache, if hit, the result set will be directly returned to the client, otherwise Enter step 2
2. Perform parsing, preprocessing, query optimization and other operations on the SQL statement in sequence, and finally generate a query execution plan (the query execution plan of select can be viewed through explain select)
3. The query execution engine of the MySQL server will execute the query based on the query The execution plan calls the storage engine to query the data. When the last level of association of the SQL statement is executed, a query result set will be generated
4. The query result set is sent to the client, and there are two ways to return it: the MySQL server caches the result set or does not cache it. This is determined by the parameter SQL_BUFFER_RESULT set up. And, if the user sets SQL_CACHE, then a copy of the result set of this query is stored in the query cache (relevant to step 1).

Enlightenment of the SQL_CACHE parameter:
Decompose complex (multiple association) queries into multiple simple queries, because
1) cache hits of simple queries,
2) cache of complex query results are prone to failure (too many associations) Table)
3) The simple query lock holding rate is low

MySQL Server to the PHP layer

Communication mode The communication between MySQL Server and the client adopts "half-duplex communication", which means: there can only be one client and one server is reading, and the other must be writing.

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

Disadvantages: Flow control cannot be performed. One end starts sending a message, and the other end must fully accept the message before it can respond to it.

Enlightenment: The result set after the server query is sent to the client, and the client (the client's query engine, such as mysqlnd) must accept it completely. Therefore, if you only need a few rows, remember to add limit to the sql statement and use select * sparingly.

Result set return mode In the result set return, each row of records is packaged through the client-server communication protocol, and then handed over to the lower tcp protocol; of course, at the tcp layer, the protocol of each row of records can be cached first Packets are sent out into large packets (transparent to the application layer).

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

Server-side cache mode

Client command: mysql_unbuffer_query(), the cache of the result set is not set in the client's sql driver extension (mysqlnd), so when feth_array_xxx reads a record from the result set, it is required Read from the server buffer.

Server-side no cache mode

Client command: mysql_query(), a buffer is set in the client's sql driver extension (mysqlnd) to cache the server's result set, so fetch_array_xxx reads from the result set When a record is obtained, the row is obtained directly from the mysqlnd extended buffer.

Summary

If the result set is large: The server-side cache-free mode can reduce the memory pressure on the server, but it takes up the memory of the client. It just depends on the situation.

PHP layer to user layer

On the client side, the mysql extension engine (libmysql or mysqlnd) is connected to the server, and the user layer interacts with the mysql engine through the extension library (ext/mysql or ext/mysqli) ( The inspiration is to call the engine's API to read the result set).

The mechanisms of the engines libmysql and mysqlnd are different. The main difference is that mysqlnd is written in PHP and compiled into zend. And libmysql is a general library, zend needs to call this library to realize the database connection. Under this distinction, mysqlnd and zend have better adhesion, and there is one less layer of data copy when the data is transmitted to the user layer. The specific architectural differences are shown in the figure below. In the figure, the five-pointed star represents the cache buffer.

ext/mysqli and ext/mysql are client-side extension libraries (library functions): At the client-side script level, mysqlInd and libmysql are MySQL Server-side drivers. Among them, libmysql is a universal MySQL query driver, while mysqlnd is a Zend engine-based SQL driver specially designed for PHP. That is, the data-driven actions of mysqlnd require interaction between Zend and mysqlserver, while libmysql directly interacts with mysqlserver.

Comparison:
The process of database query between ext/mysqli (or ext/mysql) and libmysql is:
1) mysqi sends a query request to the libmysql driver
2) Libmysql executes the request and gets the result set in the buffers of the storage domain libmysql
3) Mysqli applies for memory: a buffer specified by zval
4) Mysqii copies the result set from libmysql to the buffer specified by zval
The process in database query of ext/mysqli (or ext/mysql) and mysqlnd is:
1) mysqi sends a query request to the mysqlnd driver
2) The mysqlnd driver executes the sql query through the zend engine. Each row of the result set is stored in a buffer (each buffer is scattered)
3) Mysqlnd creates multiple zvals and points to these buffers

For example:
In ext/mysql & libmysql, the libmysql driver obtains the result set Row1~Row3 after executing the SQL statement, and then ext/mysql copies the result set to the zend buffer, and then the mysqli_fetch_xxx function reads the result set from the memory in this area. content.
In ext/mysqli & mysqlInd, the mysqlnd driver executes the SQL statement to obtain the result set Row1~Row3, where each row is directly stored by a buffer of zend and pointed to by a zval. The client reads the results directly from this memory area through mapping to implement mysqli_fetch_xxx.

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 of zend), there are fewer A cache copy of the result set is made.

Reference

"High Performance MySQL"

http://www.cnxct.com/libmysql-mysqlnd-which-is-best-and-what-about-mysqli-pdomysql-mysql/

http:// www.cnxct.com/wp-content/uploads/2012/12/andrey-mysqlnd.pdf

Copyright Statement: This article is an original article by the blogger (http://blog.csdn.net/ordeder) and has not been authorized by the blogger No reproduction allowed.

The above introduces an overview of the data query process from PHP to MySQL, including all aspects. I hope it will be helpful to friends who are interested in MySql.

Computer/mobile phone tips: Cancel shared documents
By default, when you open My Computer in Windows XP, you will see some folders above the hard disk icon. These are "shared folders", and here are each folder used by users to share files. We can make these folders disappear from my computer. The principle is very simple. Just open the registry and find the following location: HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindows CurrentVersion ExplorerMyComputerNameSpaceDelegateFolders. Delete the {59031a47-3f72-44a7-89c5-5595fe6b30ee} key value. Open me next time. computer, these annoying folders no longer exist.

The above has introduced an overview of the PHP to MySQL data query process, including various aspects. I hope it will be helpful to friends who are interested in PHP tutorials.