PHP to MySQL data query process overview

HP layer to MySQL layer

The PHP to the SQL component hierarchy is shown in the following illustration:

Ext/mysqli and Ext/mysql are the client's extender libraries (library functions), which are extended libraries at the client-side scripting level. The Mysqli Library is an extended version of the MySQL library, and the extended version adds a column binding (Bind column). PDO (PHP data Object) is another extension library for data-oriented objects . These extensions are directed to programmers, and their underlying implementations are MySQL connection engines (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 PHP-side (client) database connection driver engine. Libmysql is a general-purpose database connectivity engine, and MYSQLND is a dedicated PHP-developed connectivity engine, belonging to the Zend. When PHP queries the database by calling the mysql_query () function in the Extensions library (EXT/MYSQLI and Ext/mysql), The Zend engine will issue a query request to the MySQL server via the MySQL (MYSQLND and libmysql) query engine.

MySQL layer of data query

After the MySQL server receives the query request from the client, the query execution process is as shown in the previous illustration:
1. Query caching, if hit then return the result set directly to the client, otherwise go to step 2
2. The SQL statements in order parsing, preprocessing, query optimization, and so on, the final generation of query execution plan (select query execution plan 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 layer of an association of SQL statements is executed, a query result set is generated
4. The query result set is sent to the client, and there are two ways to return it: the MySQL server-side cache result set or not cache, which is set by the parameter Sql_buffer_result. Also, if the user sets a copy of the result set for the Sql_cache query, the replica is stored in the query cache (step 1 is related).

Enlightenment of Sql_cache Parameters:
Decompose complex (multiple associative) queries into multiple simple queries because
1 Cache hits for simple queries,
2 The cache of complex query results is easily invalidated (too many tables are associated)
3 low holding rate of simple query lock

My SQL Server to the PHP layer

Communication mode mySQL Server communicates with the client using Half-duplex communication, meaning that only one client and server can be read, and the other must be write.

Advantages: Simple protocol, client and service-side Write permission is mutually exclusive

Disadvantage: Unable to flow control, one end to send a message, the other end to fully accept the message to respond to it.

Revelation: The result set after the server-side query is sent to the client, and the client (the client's query engine, such as MYSQLND) must be fully accepted. So, if you only need a few rows, remember to add the use limit in the SQL statement, less with SELECT *.

Result set return mode result set back to cross, each row of records are packaged by client-server communication protocol, then handover to the lower TCP protocol; Of course, at the TCP layer, you can cache the protocol packets for each row of records, and make a large packet emitted (transparent to the application layer).

The MySQL server will not be able to release the buffer occupied by the result set until all the result sets have been sent to the client.

Server-Side Caching mode

Client command: Mysql_unbuffer_query (), the result set cache is not set in the client's SQL-driven extensions (MYSQLND), so when Fecth_array_xxx reads a record from the result set, it needs to be read from the server-side buffer.

Server-side No caching mode

Client command: mysql_query (), in the client's SQL-driven extension (MYSQLND), sets a buffer to cache the server-side result set, so when Fecth_array_xxx reads a record from the result set, is to get the row directly from the buffer in the mysqlnd extension.

Summary

If the result set is large: The server-side cache-Free mode can reduce the memory pressure on the server, but consumes the client's memory. This only depends on the situation and choice.

PHP Layer to user layer

On the client side, the MySQL extension engine (libmysql or MYSQLND) is docked to the server, and the user layer interacts with the MySQL engine via an extension library (Ext/mysql or EXT/MYSQLI) (the revelation is that the API of the calling engine reads the result set).

The mechanism of engine libmysql and Mysqlnd is different, the main difference is that MYSQLND is written in PHP and compiled into Zend. And Libmysql is a common library, Zend need to call the library to implement the database connection. In this case, MYSQLND and Zend have better adhesion, and when data is transferred to the user layer, there is less copy of one layer. The specific architectural differences are shown in the following figure. In the figure, the pentagram represents buffer buffer.

Ext/mysqli and Ext/mysql are the client's extender libraries (library functions): At the client script level Mysqlind and Libmysql are the drivers for mysql Server side. Among them, Libmysql is a general-purpose MySQL query driver , while Mysqlnd is a Zend engine based SQL driver specifically set for PHP, that is, MYSQLND data-driven actions need to go through Zend and mySQL Server interaction, and libmysql directly interacts with mySQL Server .

Contrast:
The procedures in the Ext/mysqli (or ext/mysql) and Libmysql database queries are:
1) MYSQI send query request to Libmysql driver
2) Libmysql executes the request and obtains the result set storage domain Libmysql buffers
3 mysqli application Memory: Zval specified a buffer
4) Mysqii from Libmysql copy result set to Zval specified buffer
The procedures in the Ext/mysqli (or ext/mysql) and MYSQLND database queries are:
1) MYSQI send query request to MYSQLND driver
2 The MYSQLND driver executes the SQL query through the Zend engine, each row of the result set is stored by a buffer (each buffer is dispersed)
3) Mysqlnd Create multiple zval and point to these buffers

For example:
In Ext/mysql & Libmysql, Libmysql drives Execute SQL statements and gets the result set ROW1~ROW3, then ext/mysql copies the result set to Zend buffer, after which Mysqli_fetch_ The XXX function reads the contents of the result set from the zone's memory.
In Ext/mysqli & Mysqlind, the MYSQLND driver executes the SQL statement to get the result set ROW1~ROW3, where each row is stored directly by a buffer of Zend and directed by a zval. The client uses the mapping to read the results directly from the memory area to implement MYSQLI_FETCH_XXX.

Summary

Mysqlnd and Zend are more cohesive, in the SQL query driver, MYSQLND access the database through the Zend engine and directly to the result storage domain Zend buffer, compared to the Libmysql driver (independent of Zend), One less result set cache copy.

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 NOTICE: This article is the original article of Bo Main (Http://blog.csdn.net/ordeder), without the permission of the blogger to reprint.

The above introduction of PHP to the MySQL data query process overview, including aspects of the content, I hope to be interested in MySQL friends help.

Computer/Mobile Little common sense: 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," where each folder is used to share files. We can make these folders disappear in my computer, the principle is very simple, just open the registry to find the following location: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows \currentversion\ Explorer\ Mycomputer\namespace\delegatefolders, delete {59031a47-3f72-44a7-89c5-5595fe6b30ee} key value, the next time I open My computer, these annoying folders will no longer exist.