為圖表優化 MySQL 表

我們的應用程序根據使用者設置以不同的速率（在 500 毫秒 - 10,000 毫秒之間）收集數據點，並查詢該數據以在圖表上繪製點。

我從以下表結構開始：

CREATE TABLE `monitored_parameter_data_378` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `command_id` int(11) DEFAULT NULL,
 `data_value` varchar(255) DEFAULT NULL,
 `system_unit` varchar(255) DEFAULT NULL,
 `unit` varchar(255) DEFAULT NULL,
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

一旦我們收集了超過 20,000 個點（這顯然會繼續增長），性能就會大大降低。返回結果需要 950-1,100 毫秒，因此我將架構更改為以下內容，希望從時間戳列索引中受益：

CREATE TABLE `monitored_parameter_data_378_test` (
 `command_id` int(11) DEFAULT NULL,
 `data_value` varchar(255) DEFAULT NULL,
 `system_unit` varchar(255) DEFAULT NULL,
 `unit` varchar(255) DEFAULT NULL,
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY `CREATED_IDX` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

不幸的是，在進行更改後，我沒有看到響應時間有任何改善。

為了測試和建立基線，我使用以下查詢：

SELECT * FROM <table_name> WHERE created BETWEEN '2017-01-01' AND '2017-03-13';

表的索引版本需要 850 毫秒到 1,100 毫秒才能返回，所以我不確定如何最好地進行。當我對查詢執行 EXPLAIN 時，我看到正在使用索引，並且過濾了 50% 的數據，這比非索引版本中的 11.11% 好得多。

鑑於相當簡單的結構和提取數據的方法，是否有人對我如何將性能提升到一個新的水平有任何見解？

注意：有問題的機器是樹莓派

**更新：**我昨天將@a_vlad 的答案標記為已接受，因為根據原始問題它是正確的。也就是說，我重寫了要分 3 步完成的邏輯，首先我取回日期範圍內的第一個/最後一個點以及總點數，其次我根據點數計算這些點之間的間隔使用者想要繪圖，最後我通過執行以下操作查詢數據：

'SELECT * FROM monitored_parameter_data_378 WHERE id >= <variable containing first point in range> AND id <= <variable containing last point in range> AND id MOD <variable containing interval> = 0';

這會在大約 4 秒內將數據返回到包含 1,000,000 條記錄的表中的圖表上。

首先，最好將兩種結構結合起來：

• 並進行PK
• 並有“創建”的索引
• 作為一個變體，考慮一個自然的 PK - 創建 + 唯一標識行的東西
• 添加 - 還要考慮單位 ( system_unit) - 如果它是您的點的標識 (?)，將其更改為INT格式可能是個好主意，它會減少此列的記憶體和磁碟空間使用量。

PK 可能對其他一些查詢有好處，即使你沒有定義它，InnoDB 仍然會創建它，但在這種情況下你不能使用它。

更重要的是，響應時間不僅取決於索引（當然它必須有），還取決於記錄的數量。您可以將查詢更改為：

SELECT count(*) FROM <table_name> WHERE created BETWEEN '2017-01-01' AND '2017-03-13';

它會告訴你 - 伺服器必須返回多少條記錄。

僅有的幾種功能改進方法：

• 縮短時間範圍
• 減少列數 - 但這不是你的情況
• 更快的伺服器 - SSD（如果仍然沒有），至少增加 InnoDB 緩衝區的記憶體（如果不是必要的）

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