导致MySQL存储过程出现问题? [英] MySQL stored procedure causing problems?

问题描述

我将mysql等待超时的范围缩小到了这一行:

I've narrowed my mysql wait timeout down to this line:

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

有人知道为什么这会引起问题吗?我无法解决！

Any idea why this would cause a problem? I can't work it out!

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

Hi guys, I've written a stored proc to search for products in certain categories, due to certain constraints i came across, i was unable to do what i wanted (limiting, but whilst still returning the total number of rows found, with sorting, etc..)

这意味着将一类类别ID从1,2,3拆分成一个临时表，然后根据排序选项和限制构建全文搜索查询，执行查询字符串，然后选择总数结果数.

It's meant splits up a string of category Ids, from 1,2,3 in to a temporary table, then builds the full-text search query based on sorting options and limits, executes the query string and then selects out the total number of results.

现在，我知道我不是MySQL专家，离它很远，我可以使用它，但是我一直在搜索产品等方面超时.因此，我认为这可能导致某种原因有问题吗?

Now, I know I'm no MySQL guru, very far from it, i've got it working, but i keep getting time outs with product searches etc.. so I'm thinking this may be causing some kind of problem?

有人有什么想法我可以整理一下，甚至可以做一个我可能不知道的更好的方法吗?

Does anyone have any ideas how I can tidy this up, or even do it in a much better way that I probably don't know about?

谢谢..

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 ;

非常感谢您的帮助！

推荐答案

对此查询您几乎无能为力.

There is little you can do with this query.

尝试一下:

1. 在categoryIds (categoryId)

• 确保supplier (supplied_id)是PRIMARY KEY

确保category_product (ProductID, CategoryID)(按此顺序)是PRIMARY KEY，或者您的索引以ProductID开头.

Make sure that category_product (ProductID, CategoryID) (in this order) is a PRIMARY KEY, or you have an index with ProductID leading.

更新:

如果是导致问题的原因是INSERT，并且是MyISAM表中的product_search_query，则可能是由于MyISAM锁定引起的.

If it's INSERT that causes the problem and product_search_query in a MyISAM table the issue can be with MyISAM locking.

MyISAM如果决定在表中间的空闲块中插入一行，这会导致超时，则锁定整个表.

MyISAM locks the whole table if it decides to insert a row into a free block in the middle of the table which can cause the timeouts.

尝试改用INSERT DELAYED:

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

这会将记录放入插入队列并立即返回.该记录将在以后异步添加.

This will put the records into the insertion queue and return immediately. The record will be added later asynchronously.

请注意，如果服务器在发出命令之后但实际上未插入记录之前死亡，则可能会丢失信息.

Note that you may lose information if the server dies after the command is issued but before the records are actually inserted.

更新:

由于您的表是InnoDB，因此表锁定可能是一个问题. InnoDB不支持INSERT DELAYED.

Since your table is InnoDB, it may be an issue with table locking. INSERT DELAYED is not supported on InnoDB.

根据查询的性质，在InnoDB表上的DML查询可能会放置间隙锁，从而锁定插入.

Depending on the nature of the query, DML queries on InnoDB table may place gap locks which will lock the inserts.

例如:

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扫描并将锁放在单个记录上:

This query performs ref scans and places the locks on individual records:

-- 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:

This query, while doing the same, performs a range scan and places a gap lock after key value 2, which will not let insert key value 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

这篇关于导致MySQL存储过程出现问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！