shopex网店加速，解决shopex网店 CPU% 高占用问题
使用ShopEX开网店，数据量大了以后CPU占用会非常严重，经常能达到满负荷100%。主要原因是shopex网店数据库里，关键的几个表中没有加索引。导致用户网店访问量稍大一点或搜索引擎一抓取，网店就会宕机。解决此问题的方法很简单，只需要将关键的几个表中加上索引即可。
增强工具技术人员，己经将需要添加索引的表和字段，整理成了SQL语句。只需要将下方语句在数据库中运行一下，即可向表中添加必要的索引。
(添加索引前，请先备份数据库。“sdb_” 为shopex数据库默认表前缀，如果你的网店修改了表前缀，下方中的“sdb”，需要替换为你的表前缀。)
ALTER TABLE `sdb_goods` ADD INDEX zq_goods_id ( `goods_id` );
ALTER TABLE `sdb_goods` ADD INDEX zq_cat_id ( `cat_id` );
ALTER TABLE `sdb_goods` ADD INDEX zq_type_id ( `type_id` );
ALTER TABLE `sdb_goods` ADD INDEX zq_brand_id ( `brand_id` );
ALTER TABLE `sdb_goods` ADD INDEX zq_bn ( `bn` );
ALTER TABLE `sdb_goods` ADD INDEX zq_p_order ( `p_order` );
ALTER TABLE `sdb_goods` ADD INDEX zq_d_order ( `d_order` );
ALTER TABLE `sdb_goods` ADD INDEX zq_disabled ( `disabled` );
ALTER TABLE `sdb_goods` ADD INDEX zq_marketable ( `marketable` );
ALTER TABLE `sdb_goods` ADD INDEX zq_goods_type ( `goods_type` );
ALTER TABLE `sdb_goods` ADD INDEX zq_last_modify ( `last_modify` );
ALTER TABLE `sdb_goods` ADD INDEX zq_cost ( `cost` );
ALTER TABLE `sdb_goods` ADD INDEX zq_price ( `price` );
ALTER TABLE `sdb_goods` ADD INDEX zq_name ( `name` );
ALTER TABLE `sdb_products` ADD INDEX zq_store ( `store` );
ALTER TABLE `sdb_products` ADD INDEX zq_marketable ( `marketable` );
ALTER TABLE `sdb_products` ADD INDEX zq_is_local_stock ( `is_local_stock` );
ALTER TABLE `sdb_plugins` ADD INDEX zq_app_type ( `app_type` );
ALTER TABLE `sdb_plugins` ADD INDEX zq_plugin_ident ( `plugin_ident` );
ALTER TABLE `sdb_plugins` ADD INDEX zq_plugin_type ( `plugin_type` );
ALTER TABLE `sdb_goods_spec_index` ADD INDEX zq_type_id ( `type_id` );
ALTER TABLE `sdb_goods_spec_index` ADD INDEX zq_spec_id ( `spec_id` );
ALTER TABLE `sdb_goods_spec_index` ADD INDEX zq_spec_value_id ( `spec_value_id` );
ALTER TABLE `sdb_goods_spec_index` ADD INDEX zq_spec_value ( `spec_value` );
ALTER TABLE `sdb_goods_spec_index` ADD INDEX zq_goods_id ( `goods_id` );
ALTER TABLE `sdb_goods_spec_index` ADD INDEX zq_product_id ( `product_id` );
ALTER TABLE `sdb_goods_type_spec` ADD INDEX zq_spec_id ( `spec_id` );
ALTER TABLE `sdb_goods_type_spec` ADD INDEX zq_type_id ( `type_id` );
ALTER TABLE `sdb_spec_values` ADD INDEX zq_spec_id ( `spec_id` );
ALTER TABLE `sdb_spec_values` ADD INDEX zq_spec_value ( `spec_value` );
ALTER TABLE `sdb_spec_values` ADD INDEX zq_p_order ( `p_order` );
ALTER TABLE `sdb_sitemaps` ADD INDEX zq_p_node_id ( `p_node_id` );
ALTER TABLE `sdb_sitemaps` ADD INDEX zq_action ( `action` );
ALTER TABLE `sdb_template_relation` ADD INDEX zq_source_type ( `source_type` );
ALTER TABLE `sdb_template_relation` ADD INDEX zq_source_id ( `source_id` );
ALTER TABLE `sdb_template_relation` ADD INDEX zq_template_type ( `template_type` );
ALTER TABLE `sdb_goods_cat` ADD INDEX zq_parent_id ( `parent_id` );
ALTER TABLE `sdb_goods_cat` ADD INDEX zq_type_id ( `type_id` );
ALTER TABLE `sdb_goods_cat` ADD INDEX zq_p_order ( `p_order` );
ALTER TABLE `sdb_goods_keywords` ADD INDEX zq_keyword ( `keyword` );
ALTER TABLE `sdb_goods_keywords` ADD INDEX zq_res_type ( `res_type` );
ALTER TABLE `sdb_seo` ADD INDEX zq_source_id ( `source_id` );
ALTER TABLE `sdb_seo` ADD INDEX zq_type ( `type` );
ALTER TABLE `sdb_seo` ADD INDEX zq_store_key ( `store_key` );
ALTER TABLE `sdb_order_items` ADD INDEX zq_order_id ( `order_id` );
ALTER TABLE `sdb_order_items` ADD INDEX zq_product_id ( `product_id` );
ALTER TABLE `sdb_order_items` ADD INDEX zq_type_id ( `type_id` );
ALTER TABLE `sdb_orders` ADD INDEX zq_member_id ( `member_id` );
ALTER TABLE `sdb_sell_logs` ADD INDEX zq_member_id ( `member_id` );
ALTER TABLE `sdb_sell_logs` ADD INDEX zq_product_id ( `product_id` );
ALTER TABLE `sdb_sell_logs` ADD INDEX zq_goods_id ( `goods_id` );
ALTER TABLE `sdb_sell_logs` ADD INDEX zq_number ( `number` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_base_file ( `base_file` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_base_slot ( `base_slot` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_base_id ( `base_id` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_widgets_type ( `widgets_type` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_widgets_order ( `widgets_order` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_border ( `border` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_tpl ( `tpl` );
参考：需要索引的表和字段
shopex数据库表名 | 需要加索引的字段 |
sdb_goods | goods_id,cat_id,type_id,brand_id,bn,p_order,d_order,disabled ,marketable,goods_type,last_modify,cost,price,name |
sdb_products | store,marketable,is_local_stock |
sdb_plugins | app_type,plugin_ident,plugin_type |
sdb_goods_spec_index | type_id,spec_id, spec_value_id, spec_value, goods_id, product_id |
sdb_goods_type_spec | spec_id,type_id |
sdb_spec_values | spec_id,spec_value,p_order |
sdb_sitemaps | p_node_id,action |
sdb_template_relation | source_type,source_id,template_type |
sdb_goods_cat | parent_id,type_id,p_order |
sdb_goods_keywords | keyword,res_type |
sdb_seo | source_id,type,type,store_key |
sdb_order_items | order_id,product_id,type_id |
sdb_orders | member_id |
sdb_sell_logs | member_id,product_id,goods_id,number |
sdb_widgets_set | base_file,base_slot,base_id,widgets_type,widgets_order,border,tpl |
使用ShopEX开网店，数据量大了以后CPU占用会非常严重，经常能达到满负荷100%。主要原因是shopex网店数据库里，关键的几个表中没有加索引。导致用户网店访问量稍大一点或搜索引擎一抓取，网店就会宕机。解决此问题的方法很简单，只需要将关键的几个表中加上索引即可。
增强工具技术人员，己经将需要添加索引的表和字段，整理成了SQL语句。只需要将下方语句在数据库中运行一下，即可向表中添加必要的索引。
(添加索引前，请先备份数据库。“sdb_” 为shopex数据库默认表前缀，如果你的网店修改了表前缀，下方中的“sdb”，需要替换为你的表前缀。)
ALTER TABLE `sdb_goods` ADD INDEX zq_goods_id ( `goods_id` );
ALTER TABLE `sdb_goods` ADD INDEX zq_cat_id ( `cat_id` );
ALTER TABLE `sdb_goods` ADD INDEX zq_type_id ( `type_id` );
ALTER TABLE `sdb_goods` ADD INDEX zq_brand_id ( `brand_id` );
ALTER TABLE `sdb_goods` ADD INDEX zq_bn ( `bn` );
ALTER TABLE `sdb_goods` ADD INDEX zq_p_order ( `p_order` );
ALTER TABLE `sdb_goods` ADD INDEX zq_d_order ( `d_order` );
ALTER TABLE `sdb_goods` ADD INDEX zq_disabled ( `disabled` );
ALTER TABLE `sdb_goods` ADD INDEX zq_marketable ( `marketable` );
ALTER TABLE `sdb_goods` ADD INDEX zq_goods_type ( `goods_type` );
ALTER TABLE `sdb_goods` ADD INDEX zq_last_modify ( `last_modify` );
ALTER TABLE `sdb_goods` ADD INDEX zq_cost ( `cost` );
ALTER TABLE `sdb_goods` ADD INDEX zq_price ( `price` );
ALTER TABLE `sdb_goods` ADD INDEX zq_name ( `name` );
ALTER TABLE `sdb_products` ADD INDEX zq_store ( `store` );
ALTER TABLE `sdb_products` ADD INDEX zq_marketable ( `marketable` );
ALTER TABLE `sdb_products` ADD INDEX zq_is_local_stock ( `is_local_stock` );
ALTER TABLE `sdb_plugins` ADD INDEX zq_app_type ( `app_type` );
ALTER TABLE `sdb_plugins` ADD INDEX zq_plugin_ident ( `plugin_ident` );
ALTER TABLE `sdb_plugins` ADD INDEX zq_plugin_type ( `plugin_type` );
ALTER TABLE `sdb_goods_spec_index` ADD INDEX zq_type_id ( `type_id` );
ALTER TABLE `sdb_goods_spec_index` ADD INDEX zq_spec_id ( `spec_id` );
ALTER TABLE `sdb_goods_spec_index` ADD INDEX zq_spec_value_id ( `spec_value_id` );
ALTER TABLE `sdb_goods_spec_index` ADD INDEX zq_spec_value ( `spec_value` );
ALTER TABLE `sdb_goods_spec_index` ADD INDEX zq_goods_id ( `goods_id` );
ALTER TABLE `sdb_goods_spec_index` ADD INDEX zq_product_id ( `product_id` );
ALTER TABLE `sdb_goods_type_spec` ADD INDEX zq_spec_id ( `spec_id` );
ALTER TABLE `sdb_goods_type_spec` ADD INDEX zq_type_id ( `type_id` );
ALTER TABLE `sdb_spec_values` ADD INDEX zq_spec_id ( `spec_id` );
ALTER TABLE `sdb_spec_values` ADD INDEX zq_spec_value ( `spec_value` );
ALTER TABLE `sdb_spec_values` ADD INDEX zq_p_order ( `p_order` );
ALTER TABLE `sdb_sitemaps` ADD INDEX zq_p_node_id ( `p_node_id` );
ALTER TABLE `sdb_sitemaps` ADD INDEX zq_action ( `action` );
ALTER TABLE `sdb_template_relation` ADD INDEX zq_source_type ( `source_type` );
ALTER TABLE `sdb_template_relation` ADD INDEX zq_source_id ( `source_id` );
ALTER TABLE `sdb_template_relation` ADD INDEX zq_template_type ( `template_type` );
ALTER TABLE `sdb_goods_cat` ADD INDEX zq_parent_id ( `parent_id` );
ALTER TABLE `sdb_goods_cat` ADD INDEX zq_type_id ( `type_id` );
ALTER TABLE `sdb_goods_cat` ADD INDEX zq_p_order ( `p_order` );
ALTER TABLE `sdb_goods_keywords` ADD INDEX zq_keyword ( `keyword` );
ALTER TABLE `sdb_goods_keywords` ADD INDEX zq_res_type ( `res_type` );
ALTER TABLE `sdb_seo` ADD INDEX zq_source_id ( `source_id` );
ALTER TABLE `sdb_seo` ADD INDEX zq_type ( `type` );
ALTER TABLE `sdb_seo` ADD INDEX zq_store_key ( `store_key` );
ALTER TABLE `sdb_order_items` ADD INDEX zq_order_id ( `order_id` );
ALTER TABLE `sdb_order_items` ADD INDEX zq_product_id ( `product_id` );
ALTER TABLE `sdb_order_items` ADD INDEX zq_type_id ( `type_id` );
ALTER TABLE `sdb_orders` ADD INDEX zq_member_id ( `member_id` );
ALTER TABLE `sdb_sell_logs` ADD INDEX zq_member_id ( `member_id` );
ALTER TABLE `sdb_sell_logs` ADD INDEX zq_product_id ( `product_id` );
ALTER TABLE `sdb_sell_logs` ADD INDEX zq_goods_id ( `goods_id` );
ALTER TABLE `sdb_sell_logs` ADD INDEX zq_number ( `number` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_base_file ( `base_file` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_base_slot ( `base_slot` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_base_id ( `base_id` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_widgets_type ( `widgets_type` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_widgets_order ( `widgets_order` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_border ( `border` );
ALTER TABLE `sdb_widgets_set` ADD INDEX zq_tpl ( `tpl` );
参考：需要索引的表和字段
shopex数据库表名 | 需要加索引的字段 |
sdb_goods | goods_id,cat_id,type_id,brand_id,bn,p_order,d_order,disabled ,marketable,goods_type,last_modify,cost,price,name |
sdb_products | store,marketable,is_local_stock |
sdb_plugins | app_type,plugin_ident,plugin_type |
sdb_goods_spec_index | type_id,spec_id, spec_value_id, spec_value, goods_id, product_id |
sdb_goods_type_spec | spec_id,type_id |
sdb_spec_values | spec_id,spec_value,p_order |
sdb_sitemaps | p_node_id,action |
sdb_template_relation | source_type,source_id,template_type |
sdb_goods_cat | parent_id,type_id,p_order |
sdb_goods_keywords | keyword,res_type |
sdb_seo | source_id,type,type,store_key |
sdb_order_items | order_id,product_id,type_id |
sdb_orders | member_id |
sdb_sell_logs | member_id,product_id,goods_id,number |
sdb_widgets_set | base_file,base_slot,base_id,widgets_type,widgets_order,border,tpl |
