系統:
WIN10
MYSQL 8
需求:
有兩個資料表
一個是台中門牌及經緯度大約120萬筆資料
另一個是公司內部客戶的檔案 約1萬筆(測試用)
只需要客戶資料表的地址跟台中資料表有符合
顯示該筆地址 及台中資料表裡的座標跟一些相關欄位
問題:
以上是我的SQL寫法
因沒遇過這麼大資料量的 所以也不清楚這速度是否是正常的
原本2000筆的時候就大概要15分鐘了
SELECT addr.計算地址,addr.客編,`111年8月台中gis門牌_twd97+經緯度`.地址,`111年8月台中gis門牌_twd97+經緯度`.TWD97橫坐標,`111年8月台中gis門牌_twd97+經緯度`.TWD97縱坐標
FROM addr
JOIN `111年8月台中gis門牌_twd97+經緯度`
ON addr.`計算地址` LIKE `111年8月台中gis門牌_twd97+經緯度`.`地址`
INTO OUTFILE "E:\output.csv"
CHARACTER SET big5 ;
想請教各位大大 是否有能優化的方式 或是其他方法(目前是要求使用MySQL stored procedure)
已邀請的邦友 {{ invite_list.length }}/5
用like搜尋本身效率就不會太好，畢竟每一筆都要去檢核過一次，
然後用筆數多的去join筆數少的應該也可以增加些效能，
因為這樣每一筆只要比對一萬次就可以得到結果，
比起每一筆要比對120萬次還要來得快很多才對。
其他做法就是盡量先縮小要搜尋的範圍，再來join也可以增加效率。
建議先大兩個資料庫新增一些欄位做群組分類，然後從群組來做篩選。
111年8月台中GIS門牌TWD97+經緯度，有下面這些欄位：
省市縣市代碼,鄉鎮市區代碼,村里,鄰,街路段,地區,巷,弄,號,TWD97橫坐標,TWD97縱坐標,WGS84經度,WGS84緯度.
客戶資料表不知道有哪些欄位，建議這個資料表欄位先處理到跟上面欄位類似，然後再去下筆對的動作.
這樣篩選出同一個「村里」或是「鄰」之後再比對的話，要比對資料就不會這麽多了@@
+1
虛擬欄位+索引，可以解決這個問題。
無論如何 120 萬對上 1 萬筆，都不需要這麼久。
抱歉 我補充一下
我客戶資料表上 原本只有地址
有拆分為不同欄位
以及將原地址做一些標準化處理
最後一句我能理解 但是該如何著手呢
謝謝
長遠之計，是否您的客戶基本資料裡，就應有【TWD97橫坐標】和【TWD97縱坐標】這二個欄位，然後，在stored procedure中，當新增或更改客戶資料時，直接寫入其值，這樣就不必用 join 浪費時間了。
(座標應該不可能會變動吧)
如果這樣規畫的話，每次增修，您只要搜尋一次即可。
海綿給你的那篇，其實我也有回答。
先做到基本的幾件事
1.不要用中文欄位名：此動作是要降低索引資料容量。資料容量少相對的搜尋就會比較快。
2.不要用LIKE ORDER 且也不盡量不要使用到 GROUP。
LIKE或ORDER，會造成全表搜尋的動作出現。所以當表資料屬於百萬級的。
使用LIKE是一件很蠢的事。
善用子查尋處理
認真來說，其實會變慢的主要原因，大多還是因為資料數量。就算有做好索引的情況下。
當資料達到百萬千萬級時。就要考量如何做資料分流了
iThome鐵人賽
看影片追技術