PHP 腳本中的 MySQL 查詢超時，升級到 VPS

我們最近不得不從共享主機升級到 VPS，因為我們在從 PHP 腳本執行的重要 SQL 查詢上遇到超時。

這個 PHP 腳本每天隨機執行數百次，同時執行很多次。

我在手動測試 PHP 腳本時發現的錯誤是這一行：

while($rs = mysqli_fetch_assoc($result) { . . .

它偶爾會得到(2013) “Lost connection to MySQL server during query”.錯誤。

這是有問題的查詢：

SELECT DISTINCT
 Inventory.id,
 Inventory.SKU AS inventory_sku,
 Inventory.Quantity AS inventory_quantity,
 MasterSKU.SKU_1,
 MasterSKU.SKU_2,
 MasterSKU.SKU_3,
 MasterSKU.SKU_4,
 MasterSKU.SKU_5,
 MasterSKU.SKU_6,
 MasterSKU.SKU_7,
 MasterSKU.SKU_8,
 MasterSKU.SKU_9,
 MasterSKU.SKU_10,
 MasterSKU.SKU_11,
 MasterSKU.SKU_12,
 MasterSKU.SKU_13,
 MasterSKU.SKU_14,
 MasterSKU.SKU_15,
 MasterSKU.SKU_16,
 MasterSKU.SKU_17,
 MasterSKU.SKU_18,
 MasterSKU.SKU_19,
 MasterSKU.SKU_20,
 MasterSKU.MultSKU,
 MasterSKU.QtySKU,
 MasterSKU.AltSKU,
 MasterSKU.SKU,
     '$qty' AS sold_quantity,
     '$sku' AS sold_sku
FROM eBayOrders
 LEFT OUTER JOIN MasterSKU
   ON MasterSKU.SKU = '$sku'
 LEFT OUTER JOIN Inventory
   ON Inventory.SKU IN (MasterSKU.AltSKU, MasterSKU.SKU_1, MasterSKU.SKU_2, MasterSKU.SKU_3, MasterSKU.SKU_4, MasterSKU.SKU_5, MasterSKU.SKU_6, MasterSKU.SKU_7, MasterSKU.SKU_8, MasterSKU.SKU_9, MasterSKU.SKU_10, MasterSKU.SKU_11, MasterSKU.SKU_12, MasterSKU.SKU_13, MasterSKU.SKU_14, MasterSKU.SKU_15, MasterSKU.SKU_16, MasterSKU.SKU_17, MasterSKU.SKU_18, MasterSKU.SKU_19, MasterSKU.SKU_20);

在 localhost 測試中，此查詢的單個實例執行時間為 5 到 6.5 秒。

我能夠對此進行一點優化，並通過刪除DISTINCT和添加將其縮短到 4 秒GROUP BY

SELECT
 Inventory.id,
 Inventory.SKU AS inventory_sku,
 Inventory.Quantity AS inventory_quantity,
 MasterSKU.SKU_1,
 MasterSKU.SKU_2,
 MasterSKU.SKU_3,
 MasterSKU.SKU_4,
 MasterSKU.SKU_5,
 MasterSKU.SKU_6,
 MasterSKU.SKU_7,
 MasterSKU.SKU_8,
 MasterSKU.SKU_9,
 MasterSKU.SKU_10,
 MasterSKU.SKU_11,
 MasterSKU.SKU_12,
 MasterSKU.SKU_13,
 MasterSKU.SKU_14,
 MasterSKU.SKU_15,
 MasterSKU.SKU_16,
 MasterSKU.SKU_17,
 MasterSKU.SKU_18,
 MasterSKU.SKU_19,
 MasterSKU.SKU_20,
 MasterSKU.MultSKU,
 MasterSKU.QtySKU,
 MasterSKU.AltSKU,
 MasterSKU.SKU,
     '$quantitypurchased' AS sold_quantity,
     '$sku' AS sold_sku
FROM eBayOrders
 LEFT OUTER JOIN MasterSKU
   ON MasterSKU.SKU = '$sku'
 LEFT OUTER JOIN Inventory
   ON Inventory.SKU IN (MasterSKU.AltSKU, MasterSKU.SKU_1, MasterSKU.SKU_2, MasterSKU.SKU_3, MasterSKU.SKU_4, MasterSKU.SKU_5, MasterSKU.SKU_6, MasterSKU.SKU_7, MasterSKU.SKU_8, MasterSKU.SKU_9, MasterSKU.SKU_10, MasterSKU.SKU_11, MasterSKU.SKU_12, MasterSKU.SKU_13, MasterSKU.SKU_14, MasterSKU.SKU_15, MasterSKU.SKU_16, MasterSKU.SKU_17, MasterSKU.SKU_18, MasterSKU.SKU_19, MasterSKU.SKU_20)
GROUP BY Inventory.id;

有了這個，我能夠將它降低到大約 4 秒（同樣，對於單個實例……在我們的共享主機上同時執行時，它在 30 秒後給出了超時錯誤）

現在，使用共享主機，我們無法更改我們的 MySQL 配置，例如wait_timeout net_read_timeout和net_write_timeout.

我的想法是將這些配置更改為接近最大數量，並啟用MySQL 慢查詢日誌以獲取日誌文件。

此外，我的腦海中出現了一個想法，想到我的表中的tinyintprocessed列，並檢查UPDATE在. 這樣我可以確保整個腳本一直執行到結束，並在我的數據庫中引用它。while($rs = mysqli_fetch_assoc($result)``UPDATE processed = 1

我已經在這個項目上工作了幾個月，這些超時問題正在扼殺系統，因此需要修復。

我們現在正在遷移到 VPS，我想知道是否有人可以考慮進一步優化查詢或表的方法，或者我們可以設置的任何 mysql 配置設置以確保我的腳本和查詢執行 100%時間。

有問題的表：

:MasterSKU

CREATE TABLE `MasterSKU` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`SKU` varchar(255) NOT NULL,
`AltSKU` varchar(255) DEFAULT NULL,
`QtySKU` int(11) NOT NULL DEFAULT '1',
`MultSKU` tinyint(1) NOT NULL DEFAULT '0',
`SKU_1` varchar(255) DEFAULT NULL,
`SKU_2` varchar(255) DEFAULT NULL,
`SKU_3` varchar(255) DEFAULT NULL,
`SKU_4` varchar(255) DEFAULT NULL,
`SKU_5` varchar(255) DEFAULT NULL,
`SKU_6` varchar(255) DEFAULT NULL,
`SKU_7` varchar(255) DEFAULT NULL,
`SKU_8` varchar(255) DEFAULT NULL,
`SKU_9` varchar(255) DEFAULT NULL,
`SKU_10` varchar(255) DEFAULT NULL,
`SKU_11` varchar(255) DEFAULT NULL,
`SKU_12` varchar(255) DEFAULT NULL,
`SKU_13` varchar(255) DEFAULT NULL,
`SKU_14` varchar(255) DEFAULT NULL,
`SKU_15` varchar(255) DEFAULT NULL,
`SKU_16` varchar(255) DEFAULT NULL,
`SKU_17` varchar(255) DEFAULT NULL,
`SKU_18` varchar(255) DEFAULT NULL,
`SKU_19` varchar(255) DEFAULT NULL,
`SKU_20` varchar(255) DEFAULT NULL,
`processed` tinyint(1) NOT NULL DEFAULT '0',
`comments` text,
PRIMARY KEY (`id`),
UNIQUE KEY `SKU` (`SKU`)
) ENGINE=MyISAM AUTO_INCREMENT=7815 DEFAULT CHARSET=latin1

:Inventory

CREATE TABLE `Inventory` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`SKU` varchar(255) NOT NULL,
`Quantity` int(11) NOT NULL DEFAULT '0',
`soldout` tinyint(1) NOT NULL DEFAULT '0',
`reorderamt` int(11) NOT NULL DEFAULT '0',
`zeroday` int(11) DEFAULT NULL,
`category` text,
`supplier` text,
`channel` text,
`location` text,
`incoming` varchar(255) DEFAULT NULL,
`lastmodified` timestamp NULL DEFAULT NULL,
`createdtime` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `SKU` (`SKU`)
) ENGINE=InnoDB AUTO_INCREMENT=4753 DEFAULT CHARSET=latin1

:eBayOrders

CREATE TABLE `eBayOrders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`OrderLineItemID` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`SalesRecordNumber` int(11) DEFAULT NULL,
`BuyerUserID` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`BuyerEmail` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`Title` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`SKU` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
`Quantity` int(11) NOT NULL,
`TransactionPrice` decimal(6,2) DEFAULT NULL,
`createdtime` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `ExtendedOrderID` (`OrderLineItemID`)
) ENGINE=MyISAM AUTO_INCREMENT=11668 DEFAULT CHARSET=latin1

不要跨列展開數組。取而代之的是另一個表JOIN，最多有 24行SKU。

這樣做，這個不可優化的子句就消失了：

Inventory.SKU IN ( ... 24 columns of `MasterSKU` ... )

取而代之的是一個可以優化的簡單JOIN程序。

如果您需要更多幫助，請提供SHOW CREATE TABLE相關表格。

更多的

從如下表開始：

CREATE TABLE OtherSkus (
   MasterSku VARCHAR(20) CHARSET=latin1 NOT NULL, -- for JOINing
   AltSku VARCHAR(20) CHARSET=latin1 NOT NULL,
   PRIMARY KEY(MasterSku, AltSku)
) ENGINE=InnoDB;

然後看看如何將 24 個 SKU 列縮小到可能只有 1 個。

引用自：https://dba.stackexchange.com/questions/167771