Home >Backend Development >PHP Tutorial >Overview of PHP to MySQL data query process_PHP tutorial
Overview of PHP to MySQL data query process_PHP tutorial
- WBOYOriginal
- 2016-07-13 09:44:53749browse
Overview of PHP to MySQL data query process
HP layer to MySQL layer
The Php to sql component hierarchy is as shown below:
ext/mysqli and ext/mysql are client extension libraries (library functions), extension libraries at the client script level. Mysqli library is an extended version of the mysql library. The extended version adds column binding (Bind Column) binding. PDO (PHP Data Object) is another extension library oriented to dataobject. 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 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 mysqlmysqlnd and libmysql) query engine.
Data query at MySQL layer
After the MySQL server receives the client’s query request, the query execution process is as shown in the figure above:
1. Query the cache, and if there is a hit, directly return the result set to the client, otherwise go to step 2
2. Perform parsing, preprocessing, query optimization and other operations on the SQL statement in sequence, and finally generate the 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 call the storage engine to query the data based on the query execution plan. When the last level of association of the SQL statement is executed, the query result set
will be generated
4. The query result set is sent to the client, and there are two ways of returning it: MySQL server caches the result set or does not cache it. This is set by the parameter SQL_BUFFER_RESULT. 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 SQL_CACHE parameters:
Break down complex multiple join queries into multiple simple queries because
1) Cache hit for simple query,
2) The cache of complex query results is prone to failure due to too many tables associated with it)
3) The simple query lock holding rate is low
MySQL Server to PHP layer
Communication mode MySQL Server communicates with the client using "half-duplex communication", which means: only one of the client and the server can be 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: Unable to flow control, one end starts sending a message, and the other end must completely 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'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, each row of records can be cached first The protocol package is composed of a large package and is transparent to the application layer when it is sent out).
The MySQL server can only release the buffer occupied by the result set 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 client's SQL driver extension mysqlnd), so when feth_array_xxx reads a record from the result set, it needs to be read from the server's buffer.
Server-side no cache mode
Client command: mysql_query(), the buffer is set in the client's sql driver extension mysqlnd) to cache the server's result set, so when feth_array_xxx reads a record from the result set, it is obtained directly from the mysqlnd extended buffer row.
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 client's memory. It just depends on the situation.
PHP layer to user layer
On the client side, the server is connected to the mysql extension engine libmysql or mysqlnd), and the user layer interacts with the mysql engine through the extension library ext/mysql or ext/mysqli), which is to call the engine's api to read the results. 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 script level, mysqlInd and libmysql are the driver on the MySQL Server side. Among them, libmysql is a general MySQL query driver , and mysqlnd is a SQL driver based on the Zend engine specially set for PHP. That is, the data-driven actions of mysqlnd need to go through Zend and mysqlserver Interact, and libmysql interacts directly with mysqlserver.
Comparison:
ext/mysqli or ext/mysql) and libmysql database query process 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 the 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 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 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. Then ext/mysql copies the result set to the zend buffer, and then the mysqli_fetch_xxx function reads the contents of the result set from the memory in this area.
In ext/mysqli & mysqlInd, the mysqlnd driver executes the SQL statement to obtain the result set Row1~Row3, in which 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 buffer of zend. Compared with the libmysql driver, it is independent of zend ), one less result set cache copy is needed.
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 may not be reproduced without the permission of the blogger.
The above introduces an overview of the data query process from PHP to MySQL, including aspects of the process. I hope it will be helpful to friends who are interested in MySql.
Tips for computers/mobile phones: Cancel shared documents
By default, when you open My Computer in Windows XP, you will see some folders above the hard drive 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.
