理解慢查询

Question

我为我的mySQL启用了慢查询日志，以观察执行时间超过10秒的慢查询。然后，我发现mysql.slow_log表中只记录了一个查询。这就是：

SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_active`, IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) AS `include_in_menu`, `core_url_rewrite`.`request_path` FROM `catalog_category_entity` AS `e`
 INNER JOIN `catalog_category_entity_int` AS `at_is_active_default` 
   ON (`at_is_active_default`.`entity_id` = `e`.`entity_id`) 
   AND (`at_is_active_default`.`attribute_id` = '42') 
   AND `at_is_active_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_is_active` 
   ON (`at_is_active`.`entity_id` = `e`.`entity_id`) 
   AND (`at_is_active`.`attribute_id` = '42') 
   AND (`at_is_active`.`store_id` = 1)
 INNER JOIN `catalog_category_entity_int` AS `at_include_in_menu_default` 
   ON (`at_include_in_menu_default`.`entity_id` = `e`.`entity_id`) 
   AND (`at_include_in_menu_default`.`attribute_id` = '67') 
   AND `at_include_in_menu_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_include_in_menu` 
   ON (`at_include_in_menu`.`entity_id` = `e`.`entity_id`) 
   AND (`at_include_in_menu`.`attribute_id` = '67') 
   AND (`at_include_in_menu`.`store_id` = 1)
 LEFT JOIN `core_url_rewrite` 
   ON (core_url_rewrite.category_id=e.entity_id) 
   AND (core_url_rewrite.is_system=1 
   AND core_url_rewrite.product_id IS NULL AND core_url_rewrite.store_id='1' 
   AND id_path LIKE 'category/%') 
WHERE (`e`.`entity_type_id` = '3') 
   AND (`e`.`entity_id` IN('####HERE ARE SOME IDs####')) 
   AND (IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = '1') 
   AND (IF(at_include_in_menu.value_id > 0, at_include_in_menu.value, at_include_in_menu_default.value) = '1')

它还会导致高CPU使用率。我在同一时间观察了日志时间和CPU监控曲线，发现。这是在9/10小时间隔之后经常发生的。当magento调用/执行此查询时，您能否帮助我，以便我可以更改负责执行此查询的任何设置？

Answer 1

这可能是由索引catalogue_url引起的

一个快速的解决办法是将所有索引设置为手动。

然后通过cron每小时运行这个shell脚本：

php shell/indexer.php --reindex catalog_product_attribute
php shell/indexer.php --reindex catalog_product_price
php shell/indexer.php --reindex tag_summary
php shell/indexer.php --reindex cataloginventory_stock
php shell/indexer.php --reindex catalogsearch_fulltext
php shell/indexer.php --reindex catalog_category_product
php shell/indexer.php --reindex catalog_category_flat
php shell/indexer.php --reindex catalog_product_flat 

它索引除urls之外的所有内容。

然后每晚运行以下命令：

<?php

echo "clearing core_url_rewrite so that indexer can repopulate immediately\n";

require_once "/home/path/to/store/app/Mage.php";
umask( 0 );
Mage::App('default');

$resource = Mage::getSingleton('core/resource');

$writeConnection = $resource->getConnection('core_write');

$query = "TRUNCATE `core_url_rewrite`";

$writeConnection->query($query);

echo "cleared\n";

$start = time();

echo "reindexing catalog_url start \n";

$process = Mage::getSingleton('index/indexer')->getProcessByCode('catalog_url');
$process->reindexEverything();

$finish = time() - $start;

echo "reindexing catalog_url finished in ".$finish." seconds \n";

?>