單db+單表+2000萬條靜態記錄的理想配置和結構

在過去的幾周里，我一直在努力盡可能地學習 SQL，並且在進行了研究和閱讀了許多文章之後，特別是（This SO Thread），我運氣不佳，希望能得到一些更好的建議來實現結果我需要。

---

我的設置：

1. 單一數據庫（執行 MariaDB，雖然這可以更改）
2. 具有 20+ 百萬條記錄的單表，其中包含靜態數據且永不更改。（這也保存到 csv 文件中）
3. Windows server 2012 數據中心（64gb ram + Xeon E5 @ 2.4ghz，12 核和 24 個邏輯處理器）-（如果推薦，可以更改作業系統）
4. 目前我一直在使用最新的 Xampp/phpmyadmin，同時測試不同的方法（但計劃使用更企業級的解決方案，如 sqlserver）。

---

我需要達到的目標：

我需要能夠對數據執行查詢（盡可能減少延遲），無論是對數據庫/表還是直接對 csv 文件，並返回一個包含 4 個列值的數據集。

---

我的數據：

數據是可追溯到 1999 年的歷史房產購買資訊。房產可以多次買賣，因此該表有重複的***(postcode, address and date)***記錄，但每一行的 uuid 都是唯一的。

範例查詢：

要執行查詢，我一直在使用 laravel / php，因為我對 mysql 命令行不太先進，並且會像這樣：

$query = DB::table('postcode_records')->select('uuid', 'postcode', 'address', 'sale_date')->where('postcode', '=', $pcode)->get();

這將查詢表並返回給定郵政編碼的所有記錄。這工作正常，但是需要 3-4 分鐘才能返回結果，並且在生產時會很可怕。在閱讀了很多文章之後，我希望盡可能快地實現毫秒或 1/2 秒的響應時間。

---

我的結構：

(1) - 這是具有多個複合鍵的目前結構..

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| uuid               | varchar(250) | NO   | PRI | NULL    |       |
| sale_price         | int(10)      | NO   |     | NULL    |       |
| sale_date          | datetime     | NO   | PRI | NULL    |       |
| postcode           | varchar(15)  | NO   | PRI | NULL    |       |
| house_name_num     | varchar(50)  | NO   |     | NULL    |       |
| flat_or_apartment  | varchar(50)  | NO   |     | NULL    |       |
| street             | varchar(50)  | NO   |     | NULL    |       |
| town               | varchar(150) | NO   |     | NULL    |       |
| address            | varchar(150) | NO   |     | NULL    |       |
| city               | varchar(150) | NO   |     | NULL    |       |
| district           | varchar(152) | NO   |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

(2) - 我用一個主鍵嘗試過這種結構，但是查詢時間沒有區別。

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| uuid               | varchar(250) | NO   | PRI | NULL    |       |
| sale_price         | int(10)      | NO   |     | NULL    |       |
| sale_date          | datetime     | NO   |     | NULL    |       |
| postcode           | varchar(15)  | NO   |     | NULL    |       |
| house_name_num     | varchar(50)  | NO   |     | NULL    |       |
| flat_or_apartment  | varchar(50)  | NO   |     | NULL    |       |
| street             | varchar(50)  | NO   |     | NULL    |       |
| town               | varchar(150) | NO   |     | NULL    |       |
| address            | varchar(150) | NO   |     | NULL    |       |
| city               | varchar(150) | NO   |     | NULL    |       |
| district           | varchar(152) | NO   |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

數據：

"000000D6-CFA4-476E-95A4-8680BE96B482","181995","2005-12-14 00:00:00","ST11 9TL","S","Y","F","12",,"HOFFMAN DRIVE","BLYTHE BRIDGE","STOKE-ON-TRENT","STAFFORD","STAFFORDSHIRE","A","A"
"000000FE-94CA-47DA-8D75-6FDFA5960D75","75000","2002-03-20 00:00:00","DA9 9PT","F","N","L","20",,"SWALLOW CLOSE","GREENHITHE","GREENHITHE","DARTFORD","KENT","A","A"
"0000012D-3A97-4FF7-BCA7-897FAA91E25B","52500","1997-06-27 00:00:00","BS20 6JQ","S","N","F","14",,"AVON WAY","PORTISHEAD","BRISTOL","NORTH SOMERSET","NORTH SOMERSET","A","A"

我希望我沒有讓任何人感到困惑。我試圖將文章保持為基本內容，但同時提供對我面臨的問題和我所擁有的設置的理解。

任何幫助都將是上帝的幫助，我已經嘗試了數週來解決這個問題！

您所缺少的只是郵政編碼的索引。在您的查詢中，您正在這樣做： where(‘postcode’, ‘=’, $pcode

它幾乎肯定會在數據庫層轉換為從 postcode = x 的表中選擇 a、b、c。

如果沒有郵政編碼上的索引，您正在對整個表進行慢速掃描，而使用索引您可以進行快速查找。

很可能您已PRIMARY KEY定義為PRIMARY KEY (uuid,sale_date,postcode)，並且您的執行計劃看起來類似於

MariaDB [test]> explain select uuid, postcode, address, sale_date from postcode_records where postcode = 'BS20 6JQ';
+------+-------------+------------------+------+---------------+------+---------+------+-------+-------------+
| id   | select_type | table            | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+------+-------------+------------------+------+---------------+------+---------+------+-------+-------------+
|    1 | SIMPLE      | postcode_records | ALL  | NULL          | NULL | NULL    | NULL | xxxxx | Using where |
+------+-------------+------------------+------+---------------+------+---------+------+-------+-------------+

其中 xxxxx 是表中的行數。

如果是這樣，請嘗試刪除主鍵並將其添加為

ALTER TABLE postcode_records DROP PRIMARY KEY;
ALTER TABLE postcode_records ADD PRIMARY KEY (postcode,uuid,sale_date);
ANALYZE TABLE postcode_records;

並再次執行 EXPLAIN。它應該變得更像

+------+-------------+------------------+------+---------------+---------+---------+-------+-------+-------------+
| id   | select_type | table            | type | possible_keys | key     | key_len | ref   | rows  | Extra       |
+------+-------------+------------------+------+---------------+---------+---------+-------+-------+-------------+
|    1 | SIMPLE      | postcode_records | ref  | PRIMARY       | PRIMARY | 17      | const | zzzzz | Using where |
+------+-------------+------------------+------+---------------+---------+---------+-------+-------+-------------+

查詢本身應該變得更快。它是否足夠快，還有待在您的環境中觀察。

另外，如果表是 InnoDB，檢查它的值，innodb_buffer_pool_size如果它不夠大，則增加它。

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