近日在客户主机上得到一个奇葩问题，
CPU莫名其妙经常占用100%，查看对应的进程，全是
sQL进程如下
INSERT INTO `search_tmp_64b18bb8476a53_64989566` SELECT `main_select`.`entity_id`, SUM(score) AS `re***
查看详细日志为
INSERT INTO `search_tmp_64b18333281ac9_71844131` SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT `eav_index`.`entity_id`, (((0) + (0)) * 1 + LEAST((MATCH (data_index) AGAINST ('Dental* ceramic* bearings*' IN BOOLEAN MODE)), 1000000) * POW(2, search_weight)) AS `score` FROM `catalog_product_index_eav` AS `eav_index`
INNER JOIN `catalogsearch_fulltext_scope2` AS `search_index` ON eav_index.entity_id = search_index.entity_id
INNER JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id
INNER JOIN `cataloginventory_stock_status` AS `stock_index` ON stock_index.product_id = eav_index.entity_id AND `stock_index`.`website_id` = 0 AND `stock_index`.`stock_status` = 1 AND `stock_index`.`stock_id` = 1
INNER JOIN `catalog_category_product_index_store2` AS `category_ids_index` ON eav_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '2' WHERE (eav_index.store_id = '2') AND (`eav_index`.`attribute_id` = 99 AND `eav_index`.`value` in ('3', '4') AND `eav_index`.`store_id` = '2') AND (category_ids_index.category_id in ('6')) AND (MATCH (data_index) AGAINST ('Dental* ceramic* bearings*' IN BOOLEAN MODE))) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC
LIMIT 10000;全是这类的sql语句占用了全部CPU，有时把服务器都拉死机。
付费内容限时免费中...
此种情况经过认真的分析日志，得出结论
是搜索批量爬取侧边属性选择器导致！！！
如何解决？
请安装此插件可以解决问题
magento2-search-fix-master.zip
Magento已经为此问题提交了补丁：
补丁名称 MDVA-21095
下载链接https://github.com/magento/quality-patches/releases/tag/1.0.21
或是后台关闭所有产品的 filterable (with Results),这里设置为NO，重建索引，也可以解决此问题。此处在大量产品属性时会遇到。。
修改
/vendor/magento/module-catalog-search/Model/Search/FilterMapper/CustomAttributeFilter.php
<?php
/**
* Copyright © Magento, Inc. All rights reserved.
* See COPYING.txt for license details.
*/
namespace Magento\CatalogSearch\Model\Search\FilterMapper;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\Search\Adapter\Mysql\ConditionManager;
use Magento\Framework\DB\Select;
use Magento\Eav\Model\Config as EavConfig;
use Magento\Framework\Search\Request\FilterInterface;
use Magento\Store\Model\StoreManagerInterface;
use Magento\CatalogSearch\Model\Adapter\Mysql\Filter\AliasResolver;
use Magento\Catalog\Model\Product;
/**
* Applies filters by custom attributes to base select.
*
* @deprecated 101.0.0
* @see \Magento\ElasticSearch
*/
class CustomAttributeFilter
{
/**
* @var ResourceConnection
*/
private $resourceConnection;
/**
* @var ConditionManager
*/
private $conditionManager;
/**
* @var EavConfig
*/
private $eavConfig;
/**
* @var StoreManagerInterface
*/
private $storeManager;
/**
* @var AliasResolver
*/
private $aliasResolver;
/**
* @param ResourceConnection $resourceConnection
* @param ConditionManager $conditionManager
* @param EavConfig $eavConfig
* @param StoreManagerInterface $storeManager
* @param AliasResolver $aliasResolver
*/
public function __construct(
ResourceConnection $resourceConnection,
ConditionManager $conditionManager,
EavConfig $eavConfig,
StoreManagerInterface $storeManager,
AliasResolver $aliasResolver
) {
$this->resourceConnection = $resourceConnection;
$this->conditionManager = $conditionManager;
$this->eavConfig = $eavConfig;
$this->storeManager = $storeManager;
$this->aliasResolver = $aliasResolver;
}
/**
* Applies filters by custom attributes to base select
*
* @param Select $select
* @param FilterInterface[] $filters
* @return Select
* @throws \Magento\Framework\Exception\LocalizedException
* @throws \InvalidArgumentException
* @throws \DomainException
*/
public function apply(Select $select, FilterInterface ...$filters)
{
$select = clone $select;
$mainTableAlias = $this->extractTableAliasFromSelect($select);
$attributes = [];
foreach ($filters as $filter) {
$filterJoinAlias = $this->aliasResolver->getAlias($filter);
$attributeId = $this->getAttributeIdByCode($filter->getField());
if ($attributeId === null) {
throw new \InvalidArgumentException(
sprintf('Invalid attribute id for field: %s', $filter->getField())
);
}
$attributes[] = $attributeId;
//补丁里面没有，增加下面的是为了出现大量 Creating sort index INSERT INTO `search_tmp
$store_id = (int) $this->storeManager->getStore()->getId();
$removeExtraChildProducts = new \Zend_Db_Expr('(SELECT `entity_id`, `store_id`, `attribute_id`, `value` FROM ' . $this->resourceConnection->getTableName('catalog_product_index_eav') . sprintf(' WHERE `attribute_id`=%s AND `store_id`=%s GROUP BY CONCAT(`entity_id`," ",`attribute_id`," ",`store_id`," ",`value`) )', $attributeId, $store_id));
$select->joinInner(
// [$filterJoinAlias => $this->resourceConnection->getTableName('catalog_product_index_eav')],
[$filterJoinAlias => $removeExtraChildProducts],
$this->conditionManager->combineQueries(
$this->getJoinConditions($attributeId, $mainTableAlias, $filterJoinAlias),
Select::SQL_AND
),
[]
);
}
if (count($attributes) === 1) {
// forces usage of PRIMARY key in main table
// is required to boost performance in case when we have just one filter by custom attribute
$attribute = reset($attributes);
$filter = reset($filters);
$filterJoinAlias = $this->aliasResolver->getAlias($filter);
$select->where(
$this->conditionManager->generateCondition(
sprintf('%s.attribute_id', $filterJoinAlias),
'=',
$attribute
)
)->where(
$this->conditionManager->generateCondition(
sprintf('%s.value', $filterJoinAlias),
is_array($filter->getValue()) ? 'in' : '=',
$filter->getValue()
)
);
}
return $select;
}
/**
* Returns Joins conditions for table catalog_product_index_eav
*
* @param int $attrId
* @param string $mainTable
* @param string $joinTable
* @return array
*/
private function getJoinConditions($attrId, $mainTable, $joinTable)
{
return [
sprintf('`%s`.`entity_id` = `%s`.`entity_id`', $mainTable, $joinTable),
sprintf('`%s`.`source_id` = `%s`.`source_id`', $mainTable, $joinTable),
$this->conditionManager->generateCondition(
sprintf('%s.attribute_id', $joinTable),
'=',
$attrId
),
$this->conditionManager->generateCondition(
sprintf('%s.store_id', $joinTable),
'=',
(int) $this->storeManager->getStore()->getId()
)
];
}
/**
* Returns attribute id by code
*
* @param string $field
* @return int|null
* @throws \Magento\Framework\Exception\LocalizedException
*/
private function getAttributeIdByCode($field)
{
$attr = $this->eavConfig->getAttribute(Product::ENTITY, $field);
return ($attr && $attr->getId()) ? (int) $attr->getId() : null;
}
/**
* Extracts alias for table that is used in FROM clause in Select
*
* @param Select $select
* @return string|null
* @throws \Zend_Db_Select_Exception
*/
private function extractTableAliasFromSelect(Select $select)
{
$fromArr = array_filter(
$select->getPart(Select::FROM),
function ($fromPart) {
return $fromPart['joinType'] === Select::FROM;
}
);
return $fromArr ? array_keys($fromArr)[0] : null;
}
}还需要配置lmit数量限制，通过修改
/vendor/magento/module-catalog-search/etc/search_request.xml
