Report on the attempt to get the declared performance from the prepared statements
Update: the word “unsuccessful” has been removed from the article title. Details below!
Speaking in my article about typical misconceptions related to protection against SQL injections, I noted, among others, the fact that server-prepared expressions do not work in PHP according to the stated effective script - 1 time prepare (), then 1000 times execute ().
Well, that is, in theory, they work - within a single script run. But how many do you know scripts (written by professional programmers) that perform a bunch of identical requests? I don't know either. Repeated requests (some multiple updates) are fractions of a percent, and in the mass of their requests are unique (within a single script).
Accordingly, for our unique request, first execute prepare (), then execute (), then the script safely dies, so that after launching to process the next HTTP request, re-execute prepare () ... Somehow it doesn’t look much like optimization. Rather, the opposite.
As was rightly noted in the comments, I should have mentioned exceptions in the form of console scripts and demons that keep connecting to the database for a long time. However, most PHP scripts still work on the frontend, dying after executing a couple of dozen requests.
')
But is there really no way to somehow cache the prepared request between launches?
And then an idea struck me!
But there were still some inconsistencies, but the HPC conference turned up very well, at which people who could advise me were going to speak. And the expectations did not deceive me - so, taking this opportunity, I want to express my deep gratitude to Sergey Averin and Kosta Osipov for their lively participation and invaluable advice.
Let's move on to the content part.
So, the task:
We want to access the prepared request in a new PHP script.
There are two main problems here:
Looking ahead, I will say that everything turned out to be the other way around - the second problem was solved easily, and the first one was an insurmountable obstacle.
But first things first.
The first problem is solved clearly as - with the help of a persistent connection.
After all, we refer to the same connection as the previous script. That is, in theory, should get access to the whole environment, which was created by previous appeals.
The second problem was also resolved, and very simple.
Indeed, in Mysql there are TWO versions of the prepared expressions - conditionally speaking, “binary” and “string”!
And if the former do not suit us, the latter are exactly what we need!
Binary prepared expressions work through the API, the handler of the prepared query is available only as a class, and the data is sent to the server in a binary package, resembling the one in which the database returns the result of the query. Without a serious alteration, the access driver cannot be obtained.
But the "string", or "SQL prepared statements" - this is the usual SQL queries, which are given in the documentation as examples:
SQL !
prepare , execute — ? — :
PREPARE, … . !
, « prepare() — execute()» .
, , . , , , .
, , mysqli.
. , , «» — : , . mysqli C API mysql_change_user(). , , , , .
,
.
- , , , . . , — , . — , , .
, :
prepared statements , . « » ( native prepard statements), SET .
: BVadim , mysqli MYSQLI_NO_CHANGE_USER_ON_PCONNECT. , , prepared !
, , .
Speaking in my article about typical misconceptions related to protection against SQL injections, I noted, among others, the fact that server-prepared expressions do not work in PHP according to the stated effective script - 1 time prepare (), then 1000 times execute ().
Well, that is, in theory, they work - within a single script run. But how many do you know scripts (written by professional programmers) that perform a bunch of identical requests? I don't know either. Repeated requests (some multiple updates) are fractions of a percent, and in the mass of their requests are unique (within a single script).
Accordingly, for our unique request, first execute prepare (), then execute (), then the script safely dies, so that after launching to process the next HTTP request, re-execute prepare () ... Somehow it doesn’t look much like optimization. Rather, the opposite.
As was rightly noted in the comments, I should have mentioned exceptions in the form of console scripts and demons that keep connecting to the database for a long time. However, most PHP scripts still work on the frontend, dying after executing a couple of dozen requests.
')
But is there really no way to somehow cache the prepared request between launches?
And then an idea struck me!
But there were still some inconsistencies, but the HPC conference turned up very well, at which people who could advise me were going to speak. And the expectations did not deceive me - so, taking this opportunity, I want to express my deep gratitude to Sergey Averin and Kosta Osipov for their lively participation and invaluable advice.
Let's move on to the content part.
So, the task:
We want to access the prepared request in a new PHP script.
There are two main problems here:
- prepared statement lives only within one connection, it is so arranged. The problem, at first glance, is solved.
- The handler of the prepared expression is implemented as an object. There is no option - to transfer the object to another script is impossible
Looking ahead, I will say that everything turned out to be the other way around - the second problem was solved easily, and the first one was an insurmountable obstacle.
But first things first.
The first problem is solved clearly as - with the help of a persistent connection.
After all, we refer to the same connection as the previous script. That is, in theory, should get access to the whole environment, which was created by previous appeals.
The second problem was also resolved, and very simple.
Indeed, in Mysql there are TWO versions of the prepared expressions - conditionally speaking, “binary” and “string”!
And if the former do not suit us, the latter are exactly what we need!
Binary prepared expressions work through the API, the handler of the prepared query is available only as a class, and the data is sent to the server in a binary package, resembling the one in which the database returns the result of the query. Without a serious alteration, the access driver cannot be obtained.
But the "string", or "SQL prepared statements" - this is the usual SQL queries, which are given in the documentation as examples:
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
SET @a = 3;
SET @b = 4;
EXECUTE stmt1 USING @a, @b;
SQL !
prepare , execute — ? — :
mysql_pconnect($host,$user,$pass); // - pconnect!
mysql_query("PREPARE stmt2 FROM 'SELECT ?+? AS sum'") or trigger_error(mysql_error());
mysql_query("SET @a = 6") or trigger_error(mysql_error());
mysql_query("SET @b = 8") or trigger_error(mysql_error());
$res = mysql_query("EXECUTE stmt2 USING @a, @b") or trigger_error(mysql_error());
$row = mysql_fetch_array($res);
var_dump($row);
PREPARE, … . !
, « prepare() — execute()» .
, , . , , , .
, , mysqli.
. , , «» — : , . mysqli C API mysql_change_user(). , , , , .
,
- — «persistent connection , , »
- mysql ext deprecated — ..
- mysqli_multi_query
- , SQL prepared statements
.
- , , , . . , — , . — , , .
, :
prepared statements , . « » ( native prepard statements), SET .
: BVadim , mysqli MYSQLI_NO_CHANGE_USER_ON_PCONNECT. , , prepared !
, , .
Source: https://habr.com/ru/post/149613/
All Articles