stored-procedures - MySQL存储过程导致问题？

Question

编辑:

我已经将我的 mysql 等待超时缩小到这一行:

    IF @resultsFound > 0 THEN
        INSERT INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId);
    END IF;

知道为什么这会导致问题吗？我无法解决!

我写了一个存储过程来搜索某些类别的产品，由于我遇到的某些限制，我无法做我想做的事(限制，但同时仍然返回找到的总行数，排序等..)

意思是将一串category Ids，从1,2,3拆分到一个临时表中，然后根据排序选项和限制构建全文搜索查询，执行查询字符串，然后选出总结果数。

现在，我知道我不是 MySQL 大师，离它还很远，我已经让它工作了，但我总是在产品搜索等方面超时。所以我认为这可能会导致某种问题？

有没有人知道我该如何整理它，或者甚至以一种我可能不知道的更好的方式来整理它？

谢谢。

DELIMITER $$

DROP PROCEDURE IF EXISTS `product_search` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `product_search`(keywords text, categories text, topLevelCategoryId int, sortOrder int, startOffset int, itemsToReturn int)
BEGIN

declare foundPos tinyint unsigned;
declare tmpTxt text;
declare delimLen tinyint unsigned;
declare element text;
declare resultingNum int unsigned;

drop temporary table if exists categoryIds;
create temporary table categoryIds
(
`CategoryId` int
) engine = memory;


set tmpTxt = categories;

set foundPos = instr(tmpTxt, ',');
while foundPos <> 0 do
set element = substring(tmpTxt, 1, foundPos-1);
set tmpTxt = substring(tmpTxt, foundPos+1);
set resultingNum = cast(trim(element) as unsigned);

insert into categoryIds (`CategoryId`) values (resultingNum);

set foundPos = instr(tmpTxt,',');
end while;

if tmpTxt <> '' then
insert into categoryIds (`CategoryId`) values (tmpTxt);
end if;

CASE
  WHEN sortOrder = 0 THEN
    SET @sortString = "ProductResult_Relevance DESC";
  WHEN sortOrder = 1 THEN
    SET @sortString = "ProductResult_Price ASC";
  WHEN sortOrder = 2 THEN
    SET @sortString = "ProductResult_Price DESC";
  WHEN sortOrder = 3 THEN
    SET @sortString = "ProductResult_StockStatus ASC";
END CASE;

SET @theSelect = CONCAT(CONCAT("
    SELECT SQL_CALC_FOUND_ROWS
      supplier.SupplierId as Supplier_SupplierId,
      supplier.Name as Supplier_Name,
      supplier.ImageName as Supplier_ImageName,

      product_result.ProductId as ProductResult_ProductId,
      product_result.SupplierId as ProductResult_SupplierId,
      product_result.Name as ProductResult_Name,
      product_result.Description as ProductResult_Description,
      product_result.ThumbnailUrl as ProductResult_ThumbnailUrl,
      product_result.Price as ProductResult_Price,
      product_result.DeliveryPrice as ProductResult_DeliveryPrice,
      product_result.StockStatus as ProductResult_StockStatus,
      product_result.TrackUrl as ProductResult_TrackUrl,
      product_result.LastUpdated as ProductResult_LastUpdated,

      MATCH(product_result.Name) AGAINST(?) AS ProductResult_Relevance
    FROM
      product_latest_state product_result
    JOIN
      supplier ON product_result.SupplierId = supplier.SupplierId
    JOIN
      category_product ON product_result.ProductId = category_product.ProductId
    WHERE
      MATCH(product_result.Name) AGAINST (?)
    AND
      category_product.CategoryId IN (select CategoryId from categoryIds)
    ORDER BY
      ", @sortString), "
    LIMIT ?, ?;
  ");

    set @keywords = keywords;
    set @startOffset = startOffset;
    set @itemsToReturn = itemsToReturn;

    PREPARE TheSelect FROM @theSelect;
    EXECUTE TheSelect USING @keywords, @keywords, @startOffset, @itemsToReturn;

    SET @resultsFound = FOUND_ROWS();

    SELECT @resultsFound as 'TotalResults';

    IF @resultsFound > 0 THEN
        INSERT INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId);
    END IF;

END $$

DELIMITER ;

非常感谢任何帮助!

Answer 1

您对此查询无能为力。

试试这个:

1. 创建 PRIMARY KEY在 categoryIds (categoryId)

   • 确保 supplier (supplied_id)是 PRIMARY KEY

   • 确保 category_product (ProductID, CategoryID) (按此顺序)是 PRIMARY KEY ，或者您的索引为 ProductID领导。

更新:

如果是INSERT导致问题和product_search_query在MyISAM表问题可能与MyISAM有关锁定。

MyISAM如果它决定将一行插入到表中间的空闲 block 中，这可能会导致超时，则锁定整个表。

尝试使用 INSERT DELAYED相反:

IF @resultsFound > 0 THEN
    INSERT DELAYED INTO product_search_query (QueryText, CategoryId) VALUES (keywords, topLevelCategoryId);
END IF;

这会将记录放入插入队列并立即返回。稍后将异步添加记录。

请注意，如果服务器在发出命令后但在实际插入记录之前挂掉，您可能会丢失信息。

更新:

因为你的 table 是 InnoDB ，这可能是表锁定的问题。 INSERT DELAYED InnoDB 不支持.

根据查询的性质，DML关于 InnoDB 的查询表可能会放置间隙锁，这将锁定插入。

例如:

CREATE TABLE t_lock (id INT NOT NULL PRIMARY KEY, val INT NOT NULL) ENGINE=InnoDB;
INSERT
INTO    t_lock
VALUES
        (1, 1),
        (2, 2);

此查询执行 ref扫描并锁定单个记录:

-- Session 1
START TRANSACTION;
UPDATE  t_lock
SET     val = 3
WHERE   id IN (1, 2)

-- Session 2
START TRANSACTION;
INSERT
INTO    t_lock 
VALUES  (3, 3)
-- Success

此查询在执行相同操作的同时，执行 range扫描并在键值 2 后放置间隙锁, 这不会让插入键值 3 :

-- Session 1
START TRANSACTION;
UPDATE  t_lock
SET     val = 3
WHERE   id BETWEEN 1 AND 2

-- Session 2
START TRANSACTION;
INSERT
INTO    t_lock 
VALUES  (3, 3)
-- Locks