我有一个php脚本，可以创建一个csv文件。
当我运行它时，服务器总是100%使用cpu，我在4/5分钟内无法访问网站。
如何限制此脚本的cpu使用率？
在脚本中，有一个mysqlSELECT，包含大约30000个结果行和一些strip_tag。
提前感谢
代码的一小部分：
$listing_sql = "
SELECT
p.products_id,
p.products_image,
p.manufacturers_id,
p.products_details_original_supplier_code,
p.products_details_original_prod_code,
p.products_weight,
p.products_tax_class_id AS tax_id,
p.products_quantity_local AS availability,
pd.products_name,
g.customers_group_price,
pd.products_description,
p2c.categories_id,
c.parent_id,
c.categories_id,
cd.categories_name,
m.manufacturers_id,
g.customers_group_price,
g.products_id,
IF(p.manufacturers_id = 0, NULL, m.manufacturers_name) AS marca,
IF(s.status, s.specials_new_products_price, NULL) AS specials_new_products_price,
IF(s.status, s.specials_new_products_price, g.customers_group_price) AS final_price,
IF(p.products_model = NULL, -1, p.products_model) as codprod
FROM
".TABLE_PRODUCTS." p
LEFT JOIN ".TABLE_SPECIALS." s ON p.products_id = s.products_id
LEFT JOIN ".TABLE_MANUFACTURERS." m ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN ".TABLE_PRODUCTS_GROUPS." g ON p.products_id = g.products_id AND g.customers_group_id = 3,
".TABLE_PRODUCTS_DESCRIPTION." pd,
".TABLE_PRODUCTS_TO_CATEGORIES." p2c,
".TABLE_CATEGORIES." c,
".TABLE_CATEGORIES_DESCRIPTION." cd
WHERE
p2c.categories_id = c.categories_id AND
c.categories_id = cd.categories_id AND
p.products_id = p2c.products_id AND
pd.products_id = p2c.products_id AND
pd.language_id = '$language_id' AND
cd.language_id = '$language_id'
ORDER BY final_price DESC
";
$filestring.=$row["products_id"]."|".$row["products_name"]."|".$site_url."/images/".$row["products_image"]."|".$site_url."/product_info.php?products_id=".$row["products_id"]."|".$cat_list."|".$final_price."|".$descrizione1."|".$row["marca"]."|" . $row["availability"] ."|" . $shippingprice2 ."|" . $row["codprod"]."|".$row["products_details_original_supplier_code"]."|".$row["products_details_original_prod_code"]."<endrecord>'r'n";
$rescount += 1;// $rescount++;
}
// echo $filestring;
$filename = $file."name_file";
file_put_contents("./files/$filename.csv", "$filestring");
exit;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `namedb`
--
-- --------------------------------------------------------
--
-- Struttura della tabella `oscpro_categories`
--
CREATE TABLE IF NOT EXISTS `oscpro_categories` (
`on_header` int(1) default '0',
`categories_id` int(11) NOT NULL auto_increment,
`categories_image` varchar(255) default NULL,
`parent_id` int(11) NOT NULL default '0',
`sort_order` int(3) default NULL,
`date_added` datetime default NULL,
`last_modified` datetime default NULL,
`categories_import_module_codes` varchar(255) default NULL,
`categories_status` tinyint(1) unsigned NOT NULL default '1',
`categories_allowed_customers_gid` int(11) default '0',
`categories_image_top` varchar(255) default NULL,
`categories_oscproie_module_code` varchar(255) default 'OSCPROIE',
PRIMARY KEY (`categories_id`),
KEY `idx_categories_parent_id` (`parent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1748 ;
-- --------------------------------------------------------
--
-- Struttura della tabella `oscpro_categories_description`
--
CREATE TABLE IF NOT EXISTS `oscpro_categories_description` (
`categories_id` int(11) NOT NULL default '0',
`language_id` int(11) NOT NULL default '1',
`categories_name` varchar(255) default NULL,
`categories_description` text,
`categories_pages_descriptionmetatag` text,
`categories_pages_keywordsmetatag` varchar(255) default NULL,
`categories_pages_titletag` varchar(255) default NULL,
`categories_image_alttag` varchar(255) default NULL,
`categories_pages_htmlseocode` text,
PRIMARY KEY (`categories_id`,`language_id`),
KEY `idx_categories_name` (`categories_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Struttura della tabella `oscpro_manufacturers`
--
CREATE TABLE IF NOT EXISTS `oscpro_manufacturers` (
`manufacturers_id` int(11) NOT NULL auto_increment,
`manufacturers_name` varchar(32) default NULL,
`manufacturers_image` varchar(64) default NULL,
`date_added` datetime default NULL,
`last_modified` datetime default NULL,
`manufacturers_import_module_codes` varchar(255) default NULL,
`manufacturers_oscproie_module_code` varchar(255) default 'OSCPROIE',
PRIMARY KEY (`manufacturers_id`),
KEY `IDX_MANUFACTURERS_NAME` (`manufacturers_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=610 ;
-- --------------------------------------------------------
--
-- Struttura della tabella `oscpro_products`
--
CREATE TABLE IF NOT EXISTS `oscpro_products` (
`products_id` int(11) NOT NULL auto_increment,
`products_quantity` int(11) NOT NULL default '0',
`products_details_original_supplier_code` varchar(64) default NULL,
`products_image_hot_linking_big` varchar(255) default NULL,
`products_image_hot_linking_medium` varchar(255) default NULL,
`products_image_hot_linking_small` varchar(255) default NULL,
`products_image` varchar(255) default NULL,
`products_image_extra1` varchar(255) default NULL,
`products_image_extra2` varchar(255) default NULL,
`products_image_extra3` varchar(255) default NULL,
`products_image_extra4` varchar(255) default NULL,
`products_image_extra5` varchar(255) default NULL,
`products_image_extra6` varchar(255) default NULL,
`products_price` decimal(15,4) NOT NULL default '0.0000',
`products_date_added` datetime default NULL,
`products_last_modified` datetime default NULL,
`products_date_available` datetime default NULL,
`products_weight` decimal(15,3) NOT NULL default '0.000',
`products_status` tinyint(1) NOT NULL default '0',
`products_carrot` tinyint(1) default '0',
`products_tax_class_id` int(11) NOT NULL default '0',
`manufacturers_id` int(11) NOT NULL default '0',
`products_ordered` int(11) NOT NULL default '0',
`products_percentage` tinyint(1) NOT NULL default '1',
`products_details_original_prod_code` varchar(64) default NULL,
`products_model` varchar(64) default NULL,
`products_details_pages_numbers` int(12) default '0',
`products_details_isbn` varchar(255) default NULL,
`products_details_upc` varchar(10) default NULL,
`products_details_release_date` datetime default NULL,
`products_details_run_time` int(12) default NULL,
`products_import_module_codes` varchar(255) default NULL,
`products_details_outside_code` varchar(255) default NULL,
`products_image_hot_linking` tinyint(1) default NULL,
`products_to_rss` tinyint(4) NOT NULL default '1',
`products_master` int(11) NOT NULL default '0',
`products_master_status` tinyint(2) NOT NULL default '-1',
`products_master_type` tinyint(1) NOT NULL default '1',
`products_listing_status` tinyint(2) NOT NULL default '1',
`products_master_temp` varchar(255) default NULL,
`products_ship_price` varchar(255) default NULL,
`products_ship_flag` tinyint(1) default '0',
`products_quantity_local` int(11) default '0',
`products_quantity_remote` int(11) default '0',
`products_details_oscproie_code` varchar(255) default NULL,
`products_oscproie_module_code` varchar(255) default 'OSCPROIE',
`products_bundle` enum('no','yes') NOT NULL default 'no',
`sold_in_bundle_only` enum('no','yes') NOT NULL default 'no',
`icecatcode` varchar(64) NOT NULL default '',
`eancode` varchar(13) NOT NULL default '',
PRIMARY KEY (`products_id`),
KEY `idx_products_date_added` (`products_date_added`),
KEY `idx_products_model` (`products_details_original_supplier_code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=39294 ;
-- --------------------------------------------------------
--
-- Struttura della tabella `oscpro_products_description`
--
CREATE TABLE IF NOT EXISTS `oscpro_products_description` (
`products_id` int(11) NOT NULL auto_increment,
`language_id` int(11) NOT NULL default '1',
`products_name` varchar(255) NOT NULL,
`products_description` text,
`products_url` varchar(255) default NULL,
`products_url_title` varchar(255) default NULL,
`products_url_extra1` varchar(255) default NULL,
`products_url_extra1_title` varchar(255) default NULL,
`products_url_extra2` varchar(255) default NULL,
`products_url_extra2_title` varchar(255) default NULL,
`products_url_extra3` varchar(255) default NULL,
`products_url_extra3_title` varchar(255) default NULL,
`products_url_extra4` varchar(255) default NULL,
`products_url_extra4_title` varchar(255) default NULL,
`products_viewed` int(5) default '0',
`products_details_author` varchar(255) default NULL,
`products_details_label` varchar(255) default NULL,
`products_details_distributedby` varchar(255) default NULL,
`products_details_levels` varchar(255) default NULL,
`products_details_language` varchar(255) default NULL,
`products_details_format` varchar(255) default NULL,
`main_image_alt_tag` varchar(255) default NULL,
`products_details_generic3` varchar(255) default NULL,
`products_details_titletag` varchar(255) default NULL,
`products_details_keywordsmetatag` varchar(255) default NULL,
`products_details_descriptionmetatag` text,
`products_details_hot_linking` varchar(255) default NULL,
PRIMARY KEY (`products_id`,`language_id`),
KEY `products_name` (`products_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=39294 ;
-- --------------------------------------------------------
--
-- Struttura della tabella `oscpro_products_groups`
--
CREATE TABLE IF NOT EXISTS `oscpro_products_groups` (
`customers_group_id` int(11) NOT NULL default '0',
`customers_group_price` decimal(15,4) NOT NULL default '0.0000',
`products_id` int(11) NOT NULL default '0',
`products_price` decimal(10,0) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Struttura della tabella `oscpro_products_to_categories`
--
CREATE TABLE IF NOT EXISTS `oscpro_products_to_categories` (
`products_id` int(11) NOT NULL default '0',
`categories_id` int(11) NOT NULL default '0',
PRIMARY KEY (`products_id`,`categories_id`),
KEY `ptc_catidx` (`categories_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Struttura della tabella `oscpro_specials`
--
CREATE TABLE IF NOT EXISTS `oscpro_specials` (
`specials_id` int(11) NOT NULL auto_increment,
`products_id` int(11) NOT NULL default '0',
`specials_new_products_price` decimal(15,4) NOT NULL default '0.0000',
`specials_date_added` datetime default NULL,
`specials_last_modified` datetime default NULL,
`expires_date` datetime default NULL,
`date_status_change` datetime default NULL,
`status` int(1) NOT NULL default '1',
`customers_group_id` int(11) NOT NULL default '0',
PRIMARY KEY (`specials_id`),
KEY `idx_specials_products_id` (`products_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=303 ;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
关于优化的一些初步想法：
p2c.categories_id = c.categories_id AND
c.categories_id = cd.categories_id AND
p.products_id = p2c.products_id AND
pd.products_id = p2c.products_id
这些看起来像是老式的连接。由于它们是完全联接，我首先将它们切换为INNER JOIN类型。我不知道它是否会带来性能优势，但它确实告诉数据库它是一个连接，而不是一个一般的相等比较，因此它可能能够进行实时优化。
AND
pd.language_id = '$language_id' AND
cd.language_id = '$language_id'
您在这些列上似乎没有索引，所以从这些列开始，重新衡量查询性能。
然后检查联接列，并尝试在这些列上添加索引。准确记录添加的索引：不要只对所有内容创建索引，因为这可能会影响其他地方的INSERT性能。我会添加一个索引，测量性能变化，删除它，添加另一个，测量性能改变，然后可能同时尝试两个，等等。
别忘了看看你的解释计划，看看瓶颈在哪里。
你的MySQL版本也很旧，你能更新吗？
另外，考虑用另一种方式来解决这个问题。正如我在评论中提到的，Apache在这个长进程运行时会锁定；我的猜测是，这是网站在此期间无法运行的原因，与CPU负载无关。对于其他用户来说，该网站可能运行得更慢，但不应完全锁定。
因此，设置一个cron来运行此查询，并将结果存储在web进程之外，您的网站在运行时应保持可操作性。试试看——设置起来很简单！
第三件事要尝试：您的表是MyISAM，我相信它锁定在表级别，而不是行级别。因此，将您的数据复制到测试机器上，设置您的站点，并确认当您的脚本在这个非实时环境中运行时，它是不可访问的。然后，将您的表转换为InnoDB，看看问题是否仍然存在。InnoDB在行级别锁定，因此即使其他行忙于您的长时间运行的查询，也可能允许访问。
确认这一点的另一种方法可能是创建一个琐碎的PHP脚本和MySQL表，以便脚本读取/写入表。当长时间运行的脚本处于活动状态时，它应该仍然有效，如果有效，它将显示MySQL对请求的响应仍然良好，但任何涉及锁定表的查询都必须等待。