Overview of data query process from PHP to MySQL _ PHP Tutorial

Overview of the data query process from PHP to MySQL. Overview of the data query process from PHP to MySQL from the HP layer to the MySQL layer, as shown in figure: extmysqli and extmysql are client-side extended libraries (library functions ), overview of the data query process from PHP to MySQL on the client script layer

From HP layer to MySQL layer

Shows the levels of components from Php to SQL:

Ext/mysqli and ext/mysql are client-side extension libraries (library functions) at the client script level. Mysqli Library is an extension version of mysql Library. the extension version adds the Column version to Bind Column) binding. PDO (PHP Data Object) is another Data-orientedObjectExtension Library. These extension libraries are directly oriented to programmers, and their underlying implementations are mysql connection engines such as mysqlnd and libmysql) reference http://bbs.chinaunix.net/thread-3679393-1-1.html, http://blog.csdn.net/treesky/article/details/7286098 ).

Mysqlnd and libmysql are 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 mysqlmysqlnd 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, and finally generate the query execution plan of the query execution plan select, which 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 have set SQL _CACHE, a copy of the result set of this query is stored in the query cache in step 1 ).

SQL _CACHE parameters:
Complex multi-join queries are divided into multiple simple queries, because
1) cache hits for simple queries,
2) the cache of complex query results is prone to invalid association of too many tables)
3) low holding rate of simple query locks

MySQL ServerTo the PHP layer

Communication mode MySQL ServerHalf-duplex communication is used to communicate with the client. 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: unable to proceedTrafficControl: 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 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 records to make up a large package transparent to the application layer when it is issued ).

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 (). in the SQL Drive extension mysqlnd of the client, no result set cache is set. Therefore, when fecth_array_xxx reads a record from the result set, read from the server buffer.

Serverless cache mode

Client Command: mysql_query (). in the SQL driver extension mysqlnd of the client, buffer is set 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), and the user layer is through the extension Library ext/mysql or ext/mysqli) interaction with the mysql engine is inspired by calling the engine's api to read the result set ).

The libmysql and mysqlnd mechanisms are different. 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.

Ext/mysqli and ext/mysql are client extension libraries (library functions): at the client script level, mysqlInd and libmysql are MySQL ServerTerminalDriver. Among them, libmysql is a common MySQL QueryDriverMysqlnd is an SQL driver based on the Zend Engine set for PHP, that is, the data-driven action of mysqlnd needs to go through Zend and mySqlserverWhile libmysql directly interacts with mySqlserverInteractive.

Comparison:
Ext/mysqli or ext/mysql) and libmysql databases query the process as follows:
1) mysqi sends a query request to the libmysql driver
2) Libmysql executes the request and obtains the buffer in the result set storage domain libmysql.
3) Mysqli memory application: a buffer specified by zval
4) Mysqii copies the result set from libmysql to the buffer specified by zval.
Ext/mysqli or ext/mysql) and mysqlnd databases query the process as follows:
1) mysqi sends a query request to the mysqlnd driver
2) the mysqlnd driver uses the zend Engine to execute SQL queries. each row of the result set is stored in a buffer and each buffer is scattered)
3) Mysqlnd creates multiple zval and points to these buffers

For example:
In ext/mysql & libmysql, the libmysql driver executes the SQL statement and obtains the result set Row1 ~ Row3, then ext/mysql copies the result set to zend buffer, and then the mysqli_fetch_xxx function reads the content of the result set from the memory of the region.
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 zend buffer and directed by a zval. The client directly reads the result from the memory area through ING to implement mysqli_fetch_xxx.

Summary

Mysqlnd and zend are more cohesive. in the SQL query driver, mysqlnd uses the zend EngineAccess DatabaseAnd directly store the result in zend buffer, which is independent of zend compared to the libmysql driver.

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 the author of the http://blog.csdn.net/ordeder) original articles, not allowed by the author can not be reproduced.

The preceding section describes the data query process from PHP to MySQL, including the following content. I hope to help anyone who is interested in MySql.

Computer/mobile phone tips:Cancel shared document
By default, open my computer in Windows XP and you will see some folders above the hard disk icon. These are "shared folders", which are used to share files. We can make these folders disappear in my computer, the principle is very simple, as long as you open the registry to find the location: HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Windows \ CurrentVersion \ Explorer \ MyComputer \ NameSpace \ DelegateFolders, delete the {59031a47-3f72-44a7-89c5-5595fe6b30ee} key value. The annoying folders will no longer exist when I open my computer next time.

HP layer to MySQL layer Php to SQL component level as shown in: ext/mysqli and ext/mysql are client-side extended libraries (library functions), in the client-side script layer...