我有一個 MySQL 資料庫，它有四列：library_name一個字串、function_name一個字串、low_num一個數字和high_num一個數字。low_num和high_num一起代表一個范圍，屬于同一個庫（具有相同的library_name）的所有范圍都是互斥的。我需要運行支持查詢 where，給定 ~14,000str對num，對于每對它回傳行 where low_num <= num < high_num and library_name = str。我已經創建了主鍵(library_name, low_num)，我目前最快的查詢是(select * from table where library_name = $name and $num between low_num and high_num limit 1) union (select ...) .... 即，每一對都有自己的查詢，然后它們都聯合在一起。但是，它仍然很慢（大約需要 20 秒）。當我對 14,000 對進行 1 次這樣的查詢時，我也遇到了記憶體問題，因此我不得不將其分解為約 14 個查詢以找到每個 1,000 對（但即使是這 1,000 對查詢中的一個也需要像4 秒）。任何想法如何加快這個查詢？
SHOW CREATE TABLE：https ://db-fiddle.com/f/bjB1zLez2suhdCzt6itge5/0
EXPLAIN SELECT（僅適用于聯合中的一項選擇）：https ://db-fiddle.com/f/hyq7aKN89soLZDPyxyJu5T/0
uj5u.com熱心網友回復：
好的，這是解釋：
| ID | 選擇型別 | 桌子 | 磁區 | 型別 | 可能的鍵 | 鑰匙 | key_len | 參考 | 行 | 過濾 | 額外的 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| #3 | 聯盟 | sym_large | 空值 | 范圍 | 基本的 | 基本的 | 1026 | 空值 | 14000 | 11.11 | 使用哪里 |
表的 P??RIMARY 鍵(library_name, low_nm)分別是 VARCHAR(255) 和 INT。1026 表示它正在使用 PRIMARY 鍵的key_len兩列：INT 為 4 個位元組，utf8mb4 字串為 255*4，字串長度為 2 個位元組。
可能同時過濾兩者low_num并且high_num是不可能的，因為兩者都是不等式條件。通常，索引僅有助于減少在相等條件下的 N 列的檢查行，在不等條件下最多減少一列。您的條件就像一個平等，然后是兩個不平等：
where library_name = $name and low_num <= $num and high_num >= $num
(equality) (inequality) (inequality)
所以只有搜索的前兩個詞可以被索引優化。第三個搜索詞必須通過測驗與前兩個詞匹配的每一行來“艱難地”應用。
將搜索應用于high_num而不是low_num可能會更好地減少檢查的行數，但這取決于資料。也就是說，如果平均而言 and 之間的行數$num比high_numand 之間low_num的行數少$num，那么它可能更有效。
如果這是真的，那么使用不同的索引(library_name, high_num)可能會有所幫助。但它仍然僅限于搜索三個條件中的兩個。無法過濾同一查詢中的兩個不等式。
如果您將 14,000 個姓名/號碼對加載到臨時表中并執行 JOIN 而不是數千個子查詢，您也可以避免使用 UNION。
CREATE TABLE tmp_search_pairs (
library_name VARCHAR(255) NOT NULL,
num INT UNSIGNED NOT NULL,
KEY (library_name, num)
);
...insert your search data into the temp table...
SELECT s.*
FROM sym_large AS s
JOIN tmp_search_pairs AS p
ON s.library_name = p.library_name
AND s.low_num <= p.num
AND s.high_num >= p.num;
我認為使用不等式語法與BETWEEN語法是個人偏好。他們應該優化相同。
uj5u.com熱心網友回復：
SELECT *
FROM ( SELECT * FROM sym_large
WHERE library_name = ?
AND low_num >= ?
ORDER BY low_num
LIMIT 1
) AS x
WHERE high_num <= ?;
內層查詢因為PK，速度很快，只找到一行。外部驗證 high_num 沒有被違反。如果您確定總會有匹配項，那么只需執行內部查詢。
（我認為這將比比爾的答案運行得快得多。讓我們知道。）
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/442567.html