pear::db_使用PEAR :: DB进行性能调整

pear::db

If you use PEAR::MDB2, you can set a custom debug handler and collect all the queries you execute for debugging and performance tunning purposes, as shown before. But what if you're using PEAR::DB? Well, since PEAR::DB doesn't allow you such a functionality out of the box, you can hack it a bit to get similar results.

如果使用PEAR :: MDB2 ，则可以设置自定义调试处理程序，并收集您为调试和性能调整目的而执行的所有查询，如前所述。 但是，如果您使用的是PEAR :: DB怎么办？ 好吧，由于PEAR :: DB不允许开箱即用这样的功能，因此您可以稍微修改一下以获得类似的结果。

简单的应用程序 (Simple app)

Let's say you have a simple app:

假设您有一个简单的应用程序：

<?php 
require_once 'DB.php';
 
$dsn = 'mysql://root@localhost/test';
$db =& DB::connect($dsn);
$db->setFetchMode(DB_FETCHMODE_ASSOC);
 
$sql = 'SELECT * FROM zipcodes';
$result = $db->getAll($sql);
$result = $db->getOne($sql);
$result = $db->getCol($sql);
$result = $db->getAll($sql);
$sql = 'SELECT zipcode FROM zipcodes';
$result = $db->getAll($sql);
$result = $db->getAll($sql);
$sql = 'SELECT CONCAT(zipcode, " - ", city) FROM zipcodes';
$result = $db->getAll($sql);
?>

Of course, this is an oversimplified example, usually you have more included files, class libraries and such, and it's not difficult to lose track of the database work as your app grows in complexity and size.

当然，这是一个过于简化的示例，通常您会包含更多包含的文件，类库等，并且随着应用程序的复杂性和规模的增长，不难理解数据库的工作。

Now let's debug this app to see what type of database work it does.

现在，让我们调试该应用程序以查看其执行的数据库类型。

黑客PEAR :: DB (Hacking PEAR::DB)

In my case, I'm using MySQL, so I need to find the DB/mysql.php file in my PEAR directory. I open that file and find the simpleQuery() method. That's where all queries and up, sooner or later. I find this piece of code:

就我而言，我正在使用MySQL，因此我需要在PEAR目录中找到DB / mysql.php文件。 我打开该文件并找到simpleQuery()方法。 这就是所有查询迟早会出现的地方。 我找到了这段代码：

<?php
if (!$this->options['result_buffering']) {
    $result = @mysql_unbuffered_query($query, $this->connection);
} else {
    $result = @mysql_query($query, $this->connection);
}
?>

Then I hack this piece of code, adidng some lines before and after it. The result:

然后，我修改了这段代码，在代码前后添加了几行。 结果：

<?php
// start
$start_time = array_sum(explode(' ',microtime()));
// end
 
if (!$this->options['result_buffering']) {
    $result = @mysql_unbuffered_query($query, $this->connection);
} else {
    $result = @mysql_query($query, $this->connection);
}
 
// start
$query_took = array_sum(explode(' ',microtime())) - $start_time;
@$GLOBALS['global_query_counter']++;
@$GLOBALS['all_the_queries'][$GLOBALS['global_query_counter'] . ' - ' . $query] = $query_took;
//end
?>

Now as my app's pages are executed, I'll collect invaluable DB information.

现在，在执行应用程序的页面时，我将收集宝贵的数据库信息。

报告中 (Reporting)

Let's see what we've collected.

让我们看看我们收集了什么。

You can add different types of reports in the footer of your application, or better yet, you can register a shutdown function to do the same. Here are some reporting ideas:

您可以在应用程序的页脚中添加不同类型的报告，或者更好的是，您可以注册关闭功能以执行相同的操作。 以下是一些报告提示：

<?php
// report 1.
echo "<pre>All the queries, by the order they are executed:\\n";
print_r($GLOBALS['all_the_queries']);
echo '</pre>';
 
// report 2.
echo "<pre>All the queries, ordered by the time they took, descending:\\n";
arsort($GLOBALS['all_the_queries']);
print_r($GLOBALS['all_the_queries']);
echo '</pre>';
 
// report 3.
$sum = 0;
foreach ($GLOBALS['all_the_queries'] AS $t) {
    $sum += $t;
}
echo '<pre>';
echo 'Total number of queries:   ' . $GLOBALS['global_query_counter'] . "\\n";
echo 'Total time spend querying: ' . $sum;
echo '</pre>';
 
 
// report 4.
$distinct = array();
foreach ($GLOBALS['all_the_queries'] AS $q=>$t) {
    $parts = explode(' - ', $q);
    unset($parts[0]);
    $query = implode(' - ', $parts);
    @$distinct[$query]++;
}
echo "<pre>How many duplications:\\n";
arsort($distinct);
print_r($distinct);
echo '</pre>';
?>

报告结果 (Report results)

Let's see what these reports gives us.

让我们看看这些报告给我们带来了什么。

All the queries, by the order they are executed:
Array
(
    [1 - SELECT * FROM zipcodes] => 0.00626707077026
    [2 - SELECT * FROM zipcodes] => 0.00730204582214
    [3 - SELECT * FROM zipcodes] => 0.00796985626221
    [4 - SELECT * FROM zipcodes] => 0.00654602050781
    [5 - SELECT zipcode FROM zipcodes] => 0.0058650970459
    [6 - SELECT zipcode FROM zipcodes] => 0.0239379405975
    [7 - SELECT CONCAT(zipcode, " - ", city) FROM zipcodes] => 0.00581502914429
)

All the queries, ordered by the time they took, descending:
Array
(
    [6 - SELECT zipcode FROM zipcodes] => 0.0239379405975
    [3 - SELECT * FROM zipcodes] => 0.00796985626221
    [2 - SELECT * FROM zipcodes] => 0.00730204582214
    [4 - SELECT * FROM zipcodes] => 0.00654602050781
    [1 - SELECT * FROM zipcodes] => 0.00626707077026
    [5 - SELECT zipcode FROM zipcodes] => 0.0058650970459
    [7 - SELECT CONCAT(zipcode, " - ", city) FROM zipcodes] => 0.00581502914429
)

Total number of queries:   7
Total time spend querying: 0.0637030601501

How many duplications:
Array
(
    [SELECT * FROM zipcodes] => 4
    [SELECT zipcode FROM zipcodes] => 2
    [SELECT CONCAT(zipcode, " - ", city) FROM zipcodes] => 1
)

谢谢阅读！(Thanks for reading!)

Any comments or suggestions are very welcome!

任何意见或建议都非常欢迎！

Tell your friends about this post on Facebook and Twitter

在Facebook和Twitter上告诉您的朋友有关此帖子的信息

翻译自: https://www.phpied.com/performance-tunning-with-peardb/

pear::db