php工具箱mysql停止进程,退出PHP脚本后终止或停止MySQL查询

I am running a statistics server at work that sometimes gets quite slow because of the amount of queries run on it.

Our marketing team uses it as its main stats tool. Some people in the team sometimes just quit the script before it ended (by closing their browser or tab). Meanwhile, SQL queries continue to be executed.

How can I kill or stop the associated queries of a PHP script when someone closes or leaves this script?

We of course do not want to kill these queries by hand (which we already do) but automatically as soon as the person quits the script.

Most of our statistics scripts are based on precalculations done every night for the heaviest of them, and these ones are not problematic for us. For the others, we have not yet implemented precalculations and we wanted to be able to kill orphan queries before doing it.

解决方案

You sometimes need to be able to kill a query that goes on too long. If you want to do that in your OS, you can do this-- it is fairly safe as MySQL runs all these in child processes. In Unix, this is ps and kill.

You can also use MysqlAdmin to do it, and it should be basically the same across OSes. You get a list of the processes that are running:

$ mysqladmin processlist # password if necessary

+----+------+-----------+----+---------+------+-------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+-----------+----+---------+------+-------+------------------+

| 2 | root | localhost | | Query | 0 | | show processlist |

+----+------+-----------+----+---------+------+-------+------------------+

Then look up mysqladmin kill to remove them.

EDIT

Okay, if it's about detecting the browser closed, I see a few approaches:

Check out PHP's connection handling. It looks like you can register a handle that is called if the user closes the browser.

If the handler doesn't get called, maybe you can call an interstitial page that says simply "Running query..." and then the rendering of that page can sit in a loop and wait for the query to complete or the connection to end. If the query completes, redirect to a "results" page, and if the connection goes down, manually kill the query (see above).

If those approaches prove fruitless, there is another option. The browser offers a Javascript "on unload" event that gets fired in the browser closest. A quick Ajax post from that could off the long-running query.

(Assignment: discuss the merits and disadvantages of all approaches.)

None of these strike me as beautiful, but with some work they can be made to work. To be honest, when we had this problem we just kept a processlist up and killed what looked like errant queries.