一台客户的MYSQL服务器占用CPU过高，查找出占用进程，是MYSQL中的
INSERT INTO search_tmp_*******
SELECT search_index.entity_id, (((0) + (0) + (0) + (0)) * 1) AS score
FROM catalogsearch_fulltext_scope2 AS search_index
LEFT JOIN catalog_eav_attribute AS cea ON search_index.attribute_id = cea.attribute_id
INNER JOIN catalog_category_product_index AS category_ids_index ON search_index.entity_id = category_ids_index.product_id
LEFT JOIN catalog_product_index_eav AS zzmarque_filter ON search_index.entity_id = zzmarque_filter.entity_id AND zzmarque_filter.attribute_id = 244 AND zzmarque_filter.store_id = 1
LEFT JOIN cataloginventory_stock_status AS zzmarque_filter_stock ON zzmarque_filter_stock.product_id = zzmarque_filter.source_id
LEFT JOIN catalog_product_index_eav AS systeme_exploitation_filter ON search_index.entity_id = systeme_exploitation_filter.entity_id AND systeme_exploitation_filter.attribute_id = 534 AND systeme_exploitation_filter.store_id = 1
LEFT JOIN cataloginventory_stock_status AS systeme_exploitation_filter_stock ON systeme_exploitation_filter_stock.product_id = systeme_exploitation_filter.source_id
LEFT JOIN cataloginventory_stock_status AS stock_index ON search_index.entity_id = stock_index.product_id AND stock_index.website_id = 0
WHERE (stock_index.stock_status = 1) AND (category_ids_index.category_id = 4283) AND (zzmarque_filter.value IN ('818','4429') AND zzmarque_filter_stock.stock_status = 1) AND (search_index.entity_id IN (
SELECT entity_id
FROM
(
SELECT e.entity_id, IFNULL(current_store.value, main_table.value) AS taille_ecran_pouces
FROM catalog_product_entity AS e
INNER JOIN catalog_product_entity_varchar AS main_table ON main_table.entity_id = e.entity_id
LEFT JOIN catalog_product_entity_varchar AS current_store ON current_store.attribute_id = main_table.attribute_id AND current_store.store_id = 2
WHERE (main_table.attribute_id = '265') AND (main_table.store_id = 0)
HAVING (taille_ecran_pouces IN ('13230'))) AS filter
)) AND (systeme_exploitation_filter.value IN ('9908') AND systeme_exploitation_filter_stock.stock_status = 1)太多的查寻
INSERT INTO search_tmp_******
及写入语句超时执行，导致整体CPU占用过高
执行以下操作可修复：
付费内容限时免费中...
修改代码
/vendor/magento/module-catalog-search/Model/Adapter/Mysql/Filter/Preprocessor.php
从
private function processQueryWithField(FilterInterface $filter, $isNegation, $query)
{
/** @var Attribute $attribute */
$attribute = $this->config->getAttribute(Product::ENTITY, $filter->getField());
$linkIdField = $this->getMetadataPool()->getMetadata(ProductInterface::class)->getLinkField();
if ($filter->getField() === 'price') {
$resultQuery = str_replace(
$this->connection->quoteIdentifier('price'),
$this->connection->quoteIdentifier('price_index.min_price'),
$query
);
} elseif ($filter->getField() === 'category_ids') {
return 'category_ids_index.category_id = ' . (int) $filter->getValue();
} elseif ($attribute->isStatic()) {
$alias = $this->aliasResolver->getAlias($filter);
$resultQuery = str_replace(
$this->connection->quoteIdentifier($attribute->getAttributeCode()),
$this->connection->quoteIdentifier($alias . '.' . $attribute->getAttributeCode()),
$query
);
} elseif (
$filter->getType() === FilterInterface::TYPE_TERM &&
in_array($attribute->getFrontendInput(), ['select', 'multiselect'], true)
) {
$resultQuery = $this->processTermSelect($filter, $isNegation);
} elseif (
$filter->getType() === FilterInterface::TYPE_RANGE &&
in_array($attribute->getBackendType(), ['decimal', 'int'], true)
) {
$resultQuery = $this->processRangeNumeric($filter, $query, $attribute);
} else {
$table = $attribute->getBackendTable();
$select = $this->connection->select();
$ifNullCondition = $this->connection->getIfNullSql('current_store.value', 'main_table.value');
$currentStoreId = $this->scopeResolver->getScope()->getId();
$select->from(['e' => $this->resource->getTableName('catalog_product_entity')], ['entity_id'])
->join(
['main_table' => $table],
"main_table.{$linkIdField} = e.{$linkIdField}",
[]
)
->joinLeft(
['current_store' => $table],
'current_store.attribute_id = main_table.attribute_id AND current_store.store_id = '
. $currentStoreId,
null
)
->columns([$filter->getField() => $ifNullCondition])
->where(
'main_table.attribute_id = ?',
$attribute->getAttributeId()
)
->where('main_table.store_id = ?', Store::DEFAULT_STORE_ID)
->having($query);
$resultQuery = 'search_index.entity_id IN (
select entity_id from ' . $this->conditionManager->wrapBrackets($select) . ' as filter
)';
}
return $resultQuery;
}到
private function processQueryWithField(FilterInterface $filter, $isNegation, $query)
{
/** @var Attribute $attribute */
$attribute = $this->config->getAttribute(Product::ENTITY, $filter->getField());
$linkIdField = $this->getMetadataPool()->getMetadata(ProductInterface::class)->getLinkField();
if ($filter->getField() === 'price') {
$resultQuery = str_replace(
$this->connection->quoteIdentifier('price'),
$this->connection->quoteIdentifier('price_index.min_price'),
$query
);
} elseif ($filter->getField() === 'category_ids') {
return 'category_ids_index.category_id = ' . (int) $filter->getValue();
} elseif ($attribute->isStatic()) {
$alias = $this->aliasResolver->getAlias($filter);
$resultQuery = str_replace(
$this->connection->quoteIdentifier($attribute->getAttributeCode()),
$this->connection->quoteIdentifier($alias . '.' . $attribute->getAttributeCode()),
$query
);
} elseif (
$filter->getType() === FilterInterface::TYPE_TERM &&
in_array($attribute->getFrontendInput(), ['select', 'multiselect'], true)
) {
$resultQuery = $this->processTermSelect($filter, $isNegation);
} elseif (
$filter->getType() === FilterInterface::TYPE_RANGE &&
in_array($attribute->getBackendType(), ['decimal', 'int'], true)
) {
$resultQuery = $this->processRangeNumeric($filter, $query, $attribute);
} else {
}
return $resultQuery;
}修改
vendor/module-catalog-search/Model/Adapter/Mysql/BaseSelectStrategy/BaseSelectAttributesSearchStrategy.php
从
public function createBaseSelect(SelectContainer $selectContainer)
{
$select = $this->resource->getConnection()->select();
$mainTableAlias = $selectContainer->isFullTextSearchRequired() ? 'eav_index' : 'search_index';
$select->distinct()
->from(
[$mainTableAlias => $this->resource->getTableName('catalog_product_entity')],
['entity_id' => 'entity_id']
);
if ($selectContainer->isFullTextSearchRequired()) {
$tableName = $this->scopeResolver->resolve(
$selectContainer->getUsedIndex(),
$selectContainer->getDimensions()
);
$select->joinInner(
['search_index' => $tableName],
'eav_index.entity_id = search_index.entity_id',
[]
)->joinInner(
['cea' => $this->resource->getTableName('catalog_eav_attribute')],
'search_index.attribute_id = cea.attribute_id',
[]
);
}
$selectContainer = $selectContainer->updateSelect($select);
return $selectContainer;
}到
public function createBaseSelect(SelectContainer $selectContainer)
{
$select = $this->resource->getConnection()->select();
$mainTableAlias = $selectContainer->isFullTextSearchRequired() ? 'eav_index' : 'search_index';
$tableName = $this->resource->getTableName('catalog_product_index_eav');
if($mainTableAlias != "search_index")
{
$tableName = new \Zend_Db_Expr("(SELECT DISTINCT entity_id, store_id, source_id FROM `{$tableName}`)");
}
$select->distinct()
->from(
[$mainTableAlias => $tableName],
['entity_id' => 'entity_id']
)->where(
$this->resource->getConnection()->quoteInto(
sprintf('%s.store_id = ?', $mainTableAlias),
$this->storeManager->getStore()->getId()
)
);
if ($selectContainer->isFullTextSearchRequired()) {
$tableName = $this->scopeResolver->resolve(
$selectContainer->getUsedIndex(),
$selectContainer->getDimensions()
);
$select->joinInner(
['search_index' => $tableName],
'eav_index.entity_id = search_index.entity_id',
[]
)->joinInner(
['cea' => $this->resource->getTableName('catalog_eav_attribute')],
'search_index.attribute_id = cea.attribute_id',
[]
);
}
$selectContainer = $selectContainer->updateSelect($select);
return $selectContainer;
}及安装插件
