Overview of data query process from PHP to MySQL

: This article describes the data query process from PHP to MySQL. For more information about PHP tutorials, see. 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. 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 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-side extended libraries (library functions): at the client script level, mysqlInd and libmysql are MySQL Server-side drivers. Among them, libmysql is a general MySQL Query driver, while mysqlnd is a Zend engine-based SQL driver designed for PHP. that is, the data-driven action of mysqlnd needs to be interacted with mysqlserver through Zend, while libmysql interacts directly with mysqlserver.

Comparison:
The query process of ext/mysqli (or ext/mysql) and libmysql databases is 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.
The query process of ext/mysqli (or ext/mysql) and mysqlnd databases is as follows:
1) mysqi sends a query request to the mysqlnd driver
2) the mysqlnd driver executes SQL queries through the zend Engine. each row of the result set is stored in a buffer (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 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.

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 (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 sharing documents
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.

The preceding section describes the data query process from PHP to MySQL, including the following content. if you are interested in the PHP Tutorial.