OpenCart search.php 搜索功能bug与改进

作者 FarLee 2012年10月21日 19:58:02   ‖浏览(7,930)

OpenCart 目前官网上 August 18, 2012 提供下载的最新压缩包是 Opencart v1.5.4.1,它提供的最新版本的高级搜索功能不能根据分类来缩小搜索范围,即使指定了分类,也会搜索全站所有分类。

找到相应文件(catalog/controller/product/search.php)查看发现, 它通过 $this->model_catalog_product->getProducts($data) 以及getTotalProducts()获取产品。这两个位于catalog/model/catalog/product.php 中的两个函数,其sql中WHERE语句的AND OR括号位置都有问题,造成了opencart产品分类限制条件的丢失。

将这两个函数中的这行代码 $sql .= ")"; 移动到 if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {} 内部代码块的末尾处即可。

在github上面部署的opencart search搜索功能的这个bug已发现被修正了。Search in subcategories 改成 checked默认选中。否则pd.description 搜索不到。

另外,经常访问的产品分类页面获取产品,和搜索功能页面使用的是集成的同一个方法getProducts()和 getTotalProducts(), 造成了为搜索功能准备的一些不必要的附带sql请求,譬如LEFT JOIN多个表等等,严重影响了性能。看下mysql的慢日志:

# Query_time: 0.519173 Lock_time: 0.000179 Rows_sent: 30 Rows_examined: 175881
SET timestamp=1350318745;
SELECT p.product_id, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = ‘1’ GROUP BY r1.product_id) AS rating FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = ‘1’ AND p.status = ‘1’ AND p.date_available

将getProducts()分离为getProducts() (产品分类等页面) 和 getProductsSearch()(搜索页面)。getProducts()去掉LEFT JOIN product_description 和product_to_store,相应的WHERE子句,order,注释掉if (!empty($data[‘filter_name’]) || !empty($data[‘filter_tag’])) {}部分 。将search.php中的getProducts改为getProductsSearch()。

或者直接对getProducts() 加个判断是什么页面更加方便,由此组合成不同的sql的语句:

$search_flag = !empty($data[‘filter_name’]) || !empty($data[‘filter_tag’]);   //搜索页面为true
if ($search_flag) {…} else {…}

然后,分类页的Query_time减小了几十个数量级:

# Query_time: 0.013486 Lock_time: 0.000086 Rows_sent: 30 Rows_examined: 58703
SET timestamp=1350319377;
SELECT p.product_id, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = ‘1’ GROUP BY r1.product_id) AS rating FROM oc_product p LEFT JOIN oc_product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.status = ‘1’ AND p.date_available

同样getTotalProducts() 也可以同样改进下。

而针对于搜索页面的getProductsSearch(),同样也可以有针对性地进行优化了,展开就有很多地方可以改进了。以下针对于产品分类页面做一些分析:

1.某个sql请求搜索请求次数最多。

# Query_time: 0.001879 Lock_time: 0.000143 Rows_sent: 10 Rows_examined: 51
SET timestamp=1350374686;
SELECT * FROM oc_category c LEFT JOIN oc_category_description cd ON (c.category_id = cd.category_id) LEFT JOIN oc_category_to_store c2s ON (c.category_id = c2s.category_id) WHERE c.parent_id = ’59’ AND cd.language_id = ‘1’ AND c2s.store_id = ‘0’ AND c.status = ‘1’ ORDER BY c.sort_order DESC, LCASE(cd.name);

解决办法:my.cnf设置恰当的查询缓存query_cache_size 等,  http://hi.baidu.com/aganle/item/827dd7ee24fde8265b2d64f9。这部分查询将被缓存,经常性的请求很有可能命中。

2. 找出sql请求Query_time最大的语句进行优化,如Select添加索引:

# Query_time: 1.694980 Lock_time: 0.000182 Rows_sent: 1 Rows_examined: 117178
SET timestamp=1350375740;
SELECT COUNT(p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) WHERE p.status = ‘1’ AND p.date_available <= NOW() AND (LCASE(pd.name) LIKE ‘%day%’ OR MATCH(pd.tag) AGAINST(‘day’) OR LCASE(p.model) = ‘day’ OR LCASE(p.sku) = ‘day’ OR LCASE(p.upc) = ‘day’ OR LCASE(p.ean) = ‘day’ OR LCASE(p.jan) = ‘day’ OR LCASE(p.isbn) = ‘day’ OR LCASE(p.mpn) = ‘day’);

# Query_time: 1.218172 Lock_time: 0.000257 Rows_sent: 30 Rows_examined: 119194
SET timestamp=1350375741;
SELECT p.product_id, (SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = ‘1’ GROUP BY r1.product_id) AS rating FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) WHERE pd.language_id = ‘1’ AND p.status = ‘1’ AND p.date_available <= NOW() AND (LCASE(pd.name) LIKE ‘%day%’ OR MATCH(pd.tag) AGAINST(‘day’) OR LCASE(p.model) = ‘day’ OR LCASE(p.sku) = ‘day’ OR LCASE(p.upc) = ‘day’ OR LCASE(p.ean) = ‘day’ OR LCASE(p.jan) = ‘day’ OR LCASE(p.isbn) = ‘day’ OR LCASE(p.mpn) = ‘day’) GROUP BY p.product_id ORDER BY p.sort_order ASC LIMIT 0,30;

另外 getProducts()函数中的getproduct() 也有平均0.02s 每个产品的Query_time,产品数量一多也是优化效率较高的地方。

所以,适度的代码冗余和数据库设计中的冗余一样,有时候对于提升性能还是很有必要的。


来说兩句