目前設置的數據庫性能改進。（mysql - marriaDB）

我目前有一個非常龐大的數據庫（無論如何對我來說），其中包含超過 1500 萬行的多個表。我正在嘗試提高整個表查找的性能，我實現了 memcached 來記憶體已經解析的結果，但是初始查找非常慢。20 秒 + 一個大查詢。

SELECT something, ROUND(SUM( ( amount/something_of_value) *column ) WHERE ... (the where clause is huge based on many conditionals) 目前設置：

託管 vps 伺服器

大表的 InnoDB 儲存

10.0.36-MariaDB

18GB 記憶體

8 個 2.40 GHz CPU。

我們不使用 query_cache，因為 25% 的查詢是寫查詢，並且經過一些研究，如果寫/更新查詢完成 - 記憶體被刪除。這是一個正確的方法嗎？

在不升級硬體的情況下改進初始查找的最佳方法是什麼？我可能遺漏了很多重要的細節，所以如果您需要更多資訊，請詢問。如果你能引導我朝著一個好的大方向前進，那也將不勝感激。我知道查詢優化，但我們正在努力改善查找時間和一般 mysql 性能。

編輯：

完整查詢：

SELECT  `metric`, SUM( `amount` )
   FROM  big_table
   WHERE  1
     AND  (`metric` = '1' )
     AND  (`source` = 'some_unique_source'
             OR  `source` = 'some_other_unique_source'
             OR  `source` = 'yet_another_unique_source'
             OR  `source` = 'some_most_unique_source'
             OR  `source` = 'almost_last_unique_source'
             OR  `source` = 'the_last_unique_source' 
          )
     AND  (`platform` = '2'
             OR  `platform` = '1'
             OR  `platform` = '3' 
          )
     AND  (`account` = '1'
             OR  `account` = '2'
             OR  `account` = '3'
             OR  `account` = '4'
             OR  `account` = '5'
             OR  `account` = '6'
             OR  `account` = '7'
             OR  `account` = '8'
             OR  `account` = '9'
             OR  `account` = '10'
             OR  `account` = '11'
             OR  `account` = '12' 
          )
     AND  (`something_id` = 'some.unique.id' )
     AND  `target_date` >= '2018-08-27'
     AND  `target_date` <= '2018-08-27'
   GROUP BY  `metric`;

創建查詢：

CREATE TABLE `big_table` (
 `stat_id` int(8) NOT NULL AUTO_INCREMENT,
 `metric` tinyint(1) NOT NULL DEFAULT '0',
 `source` varchar(16) DEFAULT NULL ,
 `platform` tinyint(1) NOT NULL DEFAULT '0' ,
 `account` int(2) DEFAULT NULL ,
 `something_id` varchar(128) DEFAULT 'EMPTY SOMETHING_ID',
 `target_date` date DEFAULT NULL,
 `country` varchar(2) DEFAULT NULL ,
 `version` varchar(16) DEFAULT NULL ,
 `amount` decimal(16,6) NOT NULL DEFAULT '0.000000' ,
 `tax` decimal(12,6) NOT NULL DEFAULT '0.000000' ,
 `currency` varchar(3) DEFAULT NULL,
 `currency_rate` decimal(12,6) DEFAULT '500.000000',
 `rate_updated` int(11) NOT NULL DEFAULT '0',
 `multiplier` decimal(16,6) NOT NULL DEFAULT '1.000000',
 `unique_key` varchar(180) DEFAULT NULL ,
 `caption` varchar(128) DEFAULT NULL,
 `transaction_timestamp` timestamp NULL DEFAULT NULL ,
 `finalised` tinyint(1) NOT NULL DEFAULT '0',
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`stat_id`),
 UNIQUE KEY `unique_key` (`unique_key`) USING BTREE,
 KEY `target_date` (`target_date`,`currency`),
 KEY `index_country` (`country`),
 KEY `currency_rate` (`currency_rate`,`multiplier`)
) ENGINE=InnoDB AUTO_INCREMENT=43453089 DEFAULT CHARSET=utf8

---

編輯：

日期始終為 1 天 - 腳本根據使用者輸入的日期範圍執行 foreach 日期。返回的mysql結果被解析成一個多維數組，然後在datarange完成後解析成一個json文件。現在我考慮了一下，更好的方法可能是進行更智能的查詢，其中結果將按日期分組，儘管我不知道在速度方面會有多大的改進。有 5 個主要選項卡，每個選項卡選擇不同的

$$ main $$thing - 分組和選擇帳戶、指標、來源、平台、國家和 something_id，然後 where 子句也是根據使用者輸入建構的，並且可能不同。這是一個自定義分析儀表板，如果這有助於理解我們使用它的目的。 使用者可以選擇許多不同的選擇，並根據使用者輸入建構自定義查詢。我通過排除國家/地區來減小選擇大小，因為預設情況下它會將所有 > 250 個國家/地區載入為 where 子句，使查詢長度變得非常荒謬和尷尬。為澄清起見 - 預設情況下，所有國家/地區都被標記為選中。將它粘貼到答案中讓我意識到它可以被浪費地改進。如果所有國家/地區都被選中，則刪除國家/地區將載入時間從 21 秒~ 減少到 8-10 秒（30 天 foreach 循環，基本上是您在 1400 萬數據庫行上看到的選擇）。雖然如果使用者排除至少一個國家，則 sql 將使用 250~ 個國家/地區建構。一世'

你沒有 1 天，你有 2 天。如果你真的只想要一天並且列是DATE，那麼簡單地說target_date = '2018-08-27'。這將有助於設計最佳索引。

從列開始=（以任何順序）：

INDEX(something_id, metric, target_date,
     xxxx)   -- then add one other column in the WHERE

如果只有 3 個“平台”，則忽略它的測試。我假設您正在動態建構查詢。因此，請在 UI 上投入更多精力，以便隨時優化事物。

不幸的是，您可能需要多個索引來處理多個案例。擁有超過 10 個索引是不合理的。因此，設計可能使用的索引並從=列開始。不要為超過 4 列的索引而煩惱。

其他問題：

• int(2)– INT 總是佔用 4 個字節；請參閱TINYINT（等）以節省空間。
• DEFAULT NULL——NOT NULL適時使用。
• OR將s更改為INs。這不會提高性能，但會更乾淨。
• 搬來之後IN，可以說country NOT IN (short-list)。
• 評論提到JOINvs LEFT JOIN，但我也沒有看到？

如果數據在插入後從未被修改，那麼建構和維護匯總表是可行的方法。經過精心設計，大約 5 個這樣的表可以處理大約 50 個不同的查詢。針對這些的查詢執行速度可能會快 10 倍。如需更多討論，請提供可能查詢的列表。

（我在 danblack 的答案中添加了一些關於匯總表的評論。）

變體：

• foo LIKE '%something'``foo–由於前導萬用字元，不能使用部分索引
• foo <> 'blah'或foo NOT IN ('blah')- 可能被視為“範圍”，因此，foo如果索引太早，可能會有害（對性能）；如果最後可能會有所幫助。
• country NOT IN ('Foostan')與country IN (very-long-list)- 非常長的列表需要一些時間來解析等；所以NOT IN會稍微好一點。
• 消除“檢查所有選項”——去做。優化器無法區分這和“一些已檢查”之間的區別。所以，是的，這是一個潛在的優化。

一旦你完成了類似的事情，下一個挑戰將是選擇一組有限的“複合” INDEXes——可能是 5 個索引，每個索引有 3 列。跟踪使用者向您提出的查詢。慢日誌是執行此操作的一種方法。

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