mysql數據表規模90000000(九千萬)左右，怎麼優化查詢？

12-30

表結構：
url_id(int), word_id(int), word_location(int)
word_id建了index
------------------------------------------------------------
執行語句：

select * from table
where word_id in (word_id1, word_id2.....);

一般來說，9千萬級別的表，mysql單機是完全可以處理的，用不著分表。

給出的信息嚴重不足：

表中只有這三個column，還是還有其它的？請給出完整的表結構，包括所有的index和constraint
表文件的物理大小？GB級還是TB級？
url_id和word_id的layout是怎樣的？是unique還是有大量的重複？
請給出硬體配置，CPU/內存/存儲/操作系統

另外，好慢是多慢？請給出單個select和select ... where word_id in...的運行速度，這樣才能有一個baseline用來比較

在關鍵信息不足的情況下，開腦洞給出的建議：

不要使用select * from，直接指定要用的column。這是個常識。
必須在url_id上建立index，否則word_id的index不會發生作用，mysql還是會做table scan的。但是，如果url_id有大量的重複，索引的效率會非常低，mysql也可能會選擇不使用索引。從欄位名推測，這個表可能是用於在網頁中進行全文檢索的，即保存著url id和關鍵詞id的多對多的關係。如果是這樣，url_id會有大量的重複。建議建立一個新的column，類型是int auto_increment primary key。這樣雖然會多佔一些空間，但是能讓word_id的index有效地發生作用。
利用mysql自己的partition功能，把每一個partition放到不同的物理硬碟上，查詢的時候並行處理，速度還可以快若干倍。
如果我的猜測成立，這個表的數據量不會太大（GB級），可以考慮用mysql的memory table全部放在內存里。

對於GB級的資料庫，我認為僅僅是前兩個建議就可以將速度提高到可以接受的程度了。

另外，在應用的角度也可能有很大的優化空間。從sql語句來看，你試圖返回包含指定的關鍵字的所有記錄。問題是，包含某個關鍵字的記錄可能成千上萬。例如：一個關鍵字出現在100萬個網頁中，平均每個網頁出現10次，那麼一共會返回1000萬條記錄。但是你很可能並不需要返回這麼多記錄。你可能只需要返回前100個url id就可以了（知道哪些網頁包含了該關鍵字），那麼sql可以寫成：

select url_id from table xxx where word_id = yyy order by primaryid limit 100;

如果用戶需要下100條，再依次往下取。如果要知道某個關鍵字在某個網頁中的位置信息（word_location），那麼再給定url_id去取：

select word_location from table xxx where word_id = yyy and url_id = zzz;

如果你真的需要同時返回1000個關鍵字在所有的網頁中的所有的位置信息，那麼我估計你們的需求一定是出問題了。

最後，如果我來處理這個問題，這麼簡單的數據結構，我會直接寫點代碼，用hash table在內存中處理（如果內存放得下的話）。

mysql不是有full text index么？你這自己弄個倒排索引是圖啥？

分享：MySQL資料庫設計總結

作者針對MySQL資料庫設計總結了19條規則，其中對於查詢優化，提到：

資料庫索引
規則8：業務需要的相關索引是根據實際的設計所構造sql語句的where條件來確定的，業務不需要的不要建索引，不允許在聯合索引（或主鍵）中存在多於的欄位。特別是該欄位根本不會在條件語句中出現。
規則9：唯一確定一條記錄的一個欄位或多個欄位要建立主鍵或者唯一索引，不能唯一確定一條記錄，為了提高查詢效率建普通索引
規則10：業務使用的表，有些記錄數很少，甚至只有一條記錄，為了約束的需要，也要建立索引或者設置主鍵。
規則11：對於取值不能重複，經常作為查詢條件的欄位，應該建唯一索引(主鍵默認唯一索引)，並且將查詢條件中該欄位的條件置於第一個位置。沒有必要再建立與該欄位有關的聯合索引。

規則12：對於經常查詢的欄位，其值不唯一，也應該考慮建立普通索引，查詢語句中該欄位條件置於第一個位置，對聯合索引處理的方法同樣。
規則13：業務通過不唯一索引訪問數據時，需要考慮通過該索引值返回的記錄稠密度，原則上可能的稠密度最大不能高於0.2，如果稠密度太大，則不合適建立索引了。
當通過這個索引查找得到的數據量佔到表內所有數據的20%以上時，則需要考慮建立該索引的代價，同時由於索引掃描產生的都是隨機I/O，生其效率比全表順序掃描的順序I/O低很多。資料庫系統優化query的時候有可能不會用到這個索引。
規則14：需要聯合索引(或聯合主鍵)的資料庫要注意索引的順序。SQL語句中的匹配條件也要跟索引的順序保持一致。
注意：索引的順勢不正確也可能導致嚴重的後果。
規則15：表中的多個欄位查詢作為查詢條件，不含有其他索引，並且欄位聯合值不重複，可以在這多個欄位上建唯一的聯合索引，假設索引欄位為 (a1,a2,...an),則查詢條件(a1 op val1,a2 op val2,...am op valm)m&<=n,可以用到索引，查詢條件中欄位的位置與索引中的欄位位置是一致的。
規則16：聯合索引的建立原則(以下均假設在資料庫表的欄位a,b,c上建立聯合索引(a,b,c))
聯合索引中的欄位應盡量滿足過濾數據從多到少的順序，也就是說差異最大的欄位應該房子第一個欄位
建立索引盡量與SQL語句的條件順序一致，使SQL語句盡量以整個索引為條件，盡量避免以索引的一部分(特別是首個條件與索引的首個欄位不一致時)作為查詢的條件
Where a=1,where a&>=12 and a&<15,where a=1 and b&<5 ,where a=1 and b=7 and c&>=40為條件可以用到此聯合索引；而這些語句where b=10,where c=221,where b&>=12 and c=2則無法用到這個聯合索引。

當需要查詢的資料庫欄位全部在索引中體現時，資料庫可以直接查詢索引得到查詢信息無須對整個表進行掃描(這就是所謂的key-only)，能大大的提高查詢效率。
當a，ab，abc與其他表欄位關聯查詢時可以用到索引
當a，ab，abc順序而不是b，c，bc，ac為順序執行Order by或者group不要時可以用到索引
以下情況時，進行表掃描然後排序可能比使用聯合索引更加有效
a.表已經按照索引組織好了
b.被查詢的數據站所有數據的很多比例。
規則17：重要業務訪問數據表時。但不能通過索引訪問數據時，應該確保順序訪問的記錄數目是有限的，原則上不得多於10.

閱讀原文，請前往MySQL資料庫設計總結

假設url_id是主鍵， in 列表不要太長，建議小於200.

或者不使用in，基於主鍵，一條一條記錄查詢，簡單的查詢往往效率更高，可以獲得更高的吞吐。

然而你並沒有用到word_id的索引啊

word_id有索引，那url_id呢？

9000萬對於mysql來說多少壓力已經大了。說幾個優化：

1、in條件裡面是很多word_id還是比較少，如果多，建議用exist

2、where的特點，是不是可以階段性的將結果進行緩存，就跟你業務有關係了

3、如果是頻繁讀取的話，可以考慮根據word_id特徵進行拆表

使用表分區，分成100個，輕鬆搞定。幾行sql的事情。

in查詢索引沒用的

你就給兩個欄位，不好說。

9千萬的數據量不大，目前我們資料庫兩個多億的數據量，單機合理查詢都在正常響應時間內

合理分區加上合理索引，無壓力

explain一下看看查詢計劃，這個量級mysql應該也還可以吧，還有mysql的配置是否沒有調優過？把cache之類的加大些

這才多大。具體信息給足啊

sql的問題主要在select *和in條件上，前者好說，後者優化下查詢實現邏輯。其他層面小左同學已經答得很完善！

這執行計劃，服

這麼大的數據量通過索引的改進很難達到性能的改進。可以考慮分表來實現。另外你的sql這麼寫，不慢死才怪。

url_id加索引....蛤哈哈