生意清淡，閒來無事，研究一下如何提昇查詢速度。
我有一個公示資料的檔案，結構如下，
CREATE TABLE `公示資料` (
`統一編號` char(8) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
`公司名稱` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`公司所在地` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
PRIMARY KEY (`統一編號`) USING BTREE,
INDEX `公司所在地`(`公司所在地`(6)) USING BTREE,
INDEX `公司名稱`(`公司名稱`(4)) USING BTREE
) ENGINE = Aria AUTO_INCREMENT = 3098965 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci PAGE_CHECKSUM = 1 ROW_FORMAT = Page;
筆數約300萬筆，這個檔，平時僅供查詢，不提供新增、修改、刪除，我每個月會定期增補一次，每個月約新增 7000 ~ 10000 筆。
假如我下的查詢條件如下【地址在『仁德區大同路』上，公司名稱含有『資訊或者景觀』者】：
SELECT
`公示資料`.`統一編號`,
`公示資料`.`公司名稱`,
`公示資料`.`公司所在地`
FROM
`公示資料`
WHERE
`公示資料`.`公司名稱` REGEXP '資訊|景觀'
and `公示資料`.`公司所在地` REGEXP '.*仁德區.*大同路'；
這雖然查得到資料(耗時約4秒)，EXPLAIN 時，完全沒有引用到索引檔，既使我都有建立索引了。
請教各位先進，有沒有更好的檔案結構或索引建立方法或什麼查詢方式可以提昇效能？
已邀請的邦友 {{ invite_list.length }}/5
謝謝海綿寶寶邀請回答
在早期的AI研究中，研究者在開始解決問題前，通常會先定義好問題，並且尋找及套用歷史中曾經用來解決相似問題的解決方案。
當我們看到一個半徑為3圓，而目標是要取得它的面積時
Problem: 取圓面積，已知半徑r為3
這時有個公式自然的就會跳進我們腦中：
Solver: 圓面積公式 r^2*\pi
將Problem輸入Solver中，我們就能得到Solution
Solution： 圓面積為 9*pi
補充完跟問題無關的小知識，我們開始進入正題。
先說結論，的確就像lemon0417所說，適合用ElasticSearch來解決這個效率問題。
我來說說同樣推薦ElasticSearch的原因，
首先，定義問題：
目前約300萬筆資料，每個月定期新增7000~10000筆。
這個需求聽起來跟搜尋引擎技術中的靜態索引建構相似：有大量的資料需要建構成索引，每隔一段時間（搜尋引擎通常是每夜）要新增索引資料。
地址在某條路上，名稱中含有某特定詞。
這就像是搜尋時，我們常用的進階搜尋，目的是減少回傳給使用者的資料筆數，幫助使用者更快找到他們需要的東西。
既然需求這麼像搜尋引擎，我們何不用搜尋引擎來實現呢？而既然要用，與其自己親手打造一個，不如使用別人幫你做好的工具，這就是我推薦ElasticSearch的原因。在這篇文章中Bool Query段落中就用到了和SQL相似的AND，了解一下它怎麼Call API能夠大大幫助你解決效率問題；同時，海綿寶寶提到的Regex也在這篇文章的Regexp Query中提到，只是不太確定你會不會想用。在StackOverflow上的這篇文章或許也能夠幫你釐清問題，看看你的需求是不是和他相似。
關於實作上如何提升搜尋效率，我推薦一下自己的文章XD：Day 14: 怎麼提高搜尋速度呢？關於效率搜尋以及Day 15: 神奇的法杖 - 提高效率的WAND演算法。
總之，這是一個在MySQL Query之外的另一個方案，針對你的大量資料，花一點學習成本來了解ElasticSearch，能夠減少很多使用者在搜尋上的成本，提升使用者體驗。
感謝費心指導，我會認真學習您提到的方案及文章，若有心得，再向您報告。
坦白說，300萬筆花了4秒找出答案，使用者並沒有抱怨什麼，只是，身為程式設計師，總想要百尺竿頭，更進一步，看看還有沒有提昇的空間。
我了解～那種感覺就像，就算Google Search要等十秒我也願意等，但是如果我是Google工程師，我不願意讓我的客戶等那麼久。
可以再討論：）
於我心有戚戚焉！
有句俗語說：「東西沒壞就不修。」，可是，我發現，我似乎做不到，明明沒壞，也想修，看看能不能修得跑更快一點。
結果，有時，修著修著，把好好的東西修壞啦。
感謝丹兄指導，我在現已稍稍知道如何操作 ElasticSearch 了。
我知道如何利用 json 檔把 mysql 的檔匯進 ElasticSearch
但，現在的問題是，json檔似乎不能太大，我若一次匯入太多筆好像會卡卡。
是否一定要切割成很多個 json檔才行？
可以一次導入300萬筆嗎？
是你要研究還是我們要研究?
MySql優化是門很深的學問,
你可以去找MySql優化的書,
我記得有那種書厚度大概十幾公分吧...
第一步，先放棄中文欄位。速度就會變快很多了。
再來就是中文文字索引的問題了。這是一門學問，很難三言兩語就能跟你說清楚的。
比較簡單的方式是，在建立資料時，用正則的方式先將可能需要查尋的字串各欄位當key儲存。
然後拿這些欄位索引查看。
直接用正則查尋的方式，很容易發生全表查尋造成緩慢。
在建立資料時，用正則的方式先將可能需要查尋的字串各欄位當key儲存。
這一點做不到，因為資料是由經濟部和財政部下載下來的，每個月還會增修10000筆，不可能用人力去分拆欄位。
至於【放棄中文欄位】，我等一下去試看看，再向您報告。
報告：
中文欄名改成英文欄名後，查詢速度沒什麼影響，倒是索引檔的大小減少了14%左右，十分可觀。
資料內容及筆數完全沒變，只因個一個中英欄名可以造成檔案體積差這麼多，我倒是沒預料到。
我怎麼可能會叫你用人力。10000筆會改死人。
因為我曾經處理過。大略是如下的做法你參考
資料內可能先只有addr也就是住址。主id為pid
另建一張表來當索引用表。其欄位就是 key pid
在插入資料時，如「仁德區大同路」。利用正則判斷的方式。將依照「區」「路」「段」等等跟區分key有關的，利用正則來拆解成為key
依上面的例子就是拆成了「仁德區」「大同路」
建立在另一個索引表上。
而這些動作只需要在新增的時候。或是另外做個工具來建立處理就行了。
建索引檔可以加快查詢速度
前提是「查詢值和索引鍵值有對應關係」
以「地址」為例
以下為有效範例
資料表
country varchar(100)
city varchar(100)
district varchar(100)
street varchar(100)
建立索引
index on city
查詢
city='Taipei' or city='Kaohsung'
以下為無效範例
資料表
address varchar(100)
建立索引
index on address
查詢
address REGEXP 'Taipei|Kaohsung'
如果硬要用 REGEXP 查詢也不是不行
只是就不是單純建立索引檔就能解決的
要請真正的高手高手高高手才行
如果建錯了索引
就會像影片中的金剛腿和鐵頭功
用在錯誤的地方
以下為有效範例
資料表
country varchar(100)
city varchar(100)
district varchar(100)
street varchar(100)
建立索引
index on city
這一點做不到，因為資料是由經濟部和財政部下載下來的，每個月還會增修10000筆，不可能用人力去分拆欄位。
也不是我硬要用REGEXP，而是找出【地址在『仁德區的大同路』上，公司名稱含有『資訊或者景觀』者】的模糊搜尋，能有其它方法嗎？
若用 （`公示資料`.`公司所在地` like '%仁德區大同路%'） 也行不通，一來，一樣沒有用到索引，二來，地址中，有可能是
『仁德區仁心里大同路』或者『仁德區文山里大同路』，不用REGEXP，根本找不到。
這一點做不到，因為資料是由經濟部和財政部下載下來的，每個月還會增修10000筆，不可能用人力去分拆欄位。
其實換個角度想
如果寫隻程式去分拆欄位
只需要分拆「1筆」而已
另外
如果是全球的地址難度可能高一些
只有台灣的地址就單純許多
由經濟部和財政部下載的資料也比公司自己建立的要「整齊」(註)的多
總之
總難度比「做不到」要低得多
註：台/臺、樓/F、全型/半型的一致性
您說得好像也可行，我試看看，只是也不見得很整齊一致，同樣的『區、里、村』，有些三個字，有些二個字，在台北，會寫『忠孝路一段』，在高雄則寫『忠孝一路』....
又，地址之外，公司名稱又何如是好？要怎麼拆？
其實可以找到全台灣所有路的列表,
直接用這個下去搜尋分類資料就好了.
改用Elasticsearch、Solr
選我正解
insert into city (CityName) values (N'台北市')
insert into District (DistrictName) values (N'士林區')
insert into Road (District,RoadName) values (District.PrimaryKey,N'忠誠路')
Road有很多路名重複，所以加個District做區別
以上皆用guid當primarykey
一開始在塞資料的時候就要根據以上的表塞進去，最後台灣的地址總是有幾樓啥的，全部塞進一欄部解析
這樣select應該會比較快吧，避掉中文搜尋的問題
我是從MSSQL的觀點來看QQ
iThome鐵人賽
看影片追技術