關於資料庫查詢業務的幾點思考

weixin_34148456發表於2018-10-11
資料庫


1、對查詢欄位建索引；

這個效果很明顯，建索引可以提升非常大的速度；

在這裡不得不講一下普通索引和唯一性索引的區別：

  1. 普通索引

　　普通索引的唯一任務是加快對資料的訪問速度。因此，應該只為那些最經常出現在查詢條件（WHERE column = ）或排序條件（ORDER BY column）中的資料列建立索引。只要有可能，就應該選擇一個資料最整齊、最緊湊的資料列（如一個整數型別的資料列）來建立索引。

  1. 唯一索引

　　普通索引允許被索引的資料列包含重複的值。比如說，因為人有可能同名，所以同一個姓名在同一個“員工個人資料”資料表裡可能出現兩次或更多次。 如果能確定某個資料列將只包含彼此各不相同的值，在為這個資料列建立索引的時候就應該用關鍵字UNIQUE把它定義為一個唯一索引。這麼做的好處：一是簡化了MySQL對這個索引的管理工作，這個索引也因此而變得更有效率；二是MySQL會在有新記錄插入資料表時，自動檢查新記錄的這個欄位的值是否已經在某個記錄的這個欄位裡出現過了；如果是，MySQL將拒絕插入那條新記錄。也就是說，唯一索引可以保證資料記錄的唯一性。事實上，在許多場合，人們建立唯一索引的目的往往不是為了提高訪問速度，而只是為了避免資料出現重複。

  1. 複合索引

    使用者可以在多個列上建立索引,這種索引叫做複合索引(組合索引)。 複合索引在資料庫操作期間所需的開銷更小,可以代替多個單一索引;同時有兩個概念叫做窄索引和寬索引,窄索引是指索引列為1-2列的索引,寬索引也就是索引列超過2列的索引;設計索引的一個重要原則就是能用窄索引不用寬索引,因為窄索引往往比組合索引更有效;

當建立複合索引index(column1,column2,column3),這就相當於建立了以下三個索引：

index(column1),index(column1,column2)index(column1,column2,column3)  // 跟三個欄位的順序沒有關係   比如：index(column3,column1,column2)，它們是一樣的效果

注意事項:

  • 對於複合索引,在查詢使用時,最好將條件順序按照索引的順序,這樣效率最高;

select * from table1 where col1=A AND col2=B AND col3=D

如果使用 where col2=B AND col1=A 或者 where col2=B 將不會使用索引

  • 何時使用複合索引
    根據where條件建索引是極其重要的一個原則; 注意不要過多用索引,否則對錶更新的效率有很大的影響,因為在操作表的時候要化大量時間花在建立索引中

  • 複合索引會替代單一索引麼
    如果索引滿足窄索引的情況下可以建立複合索引,這樣可以節約空間和時間

備註: 對一張表來說,如果有一個複合索引 on (col1,col2),就沒有必要同時建立一個單索引 on col1; 如果查詢條件需要,可以在已有單索引 on(col1)的情況下,新增複合索引on (col1,col2),對於效率有一定的提高 同時建立多欄位(包含5、6個欄位)的複合索引沒有特別多的好處,相對而言,建立多個窄欄位(僅包含一個,或頂多2個欄位)的索引可以達到更好的效率和靈活性

2、不要連表查詢

2.1 如何處理不在表中的欄位的展示？

先查對應資料的頁，在通過頁中的資料id，到其它表中查詢需要的欄位； 比如A表中有欄位 Aid, colA1, colA2 ,B表中有欄位 Bid, colB1, colB2, Aid, 而需要獲取的欄位有 colB1, colB2，colA1, colA2 這個時候可以這樣操作：

  • 第一步：根據查詢條件獲取B表中的資料 Bid, coldB1, colB2, Aid

  • 第二步：根據獲取到的Aid，從A表中獲取另外兩列colA1, colA2 儘量獲取資料的方式沒有連表查詢來的簡單，但可以在大資料量的時候，減少查詢或者傳輸資料所消耗的時間，尤其是在分頁查詢的時候。

2.2 如何處理在查詢條件中的欄位？

先將查詢條件從其它表中找出對應的id，再將id作為查詢條件，至目標表中進行查詢；

同樣是2.1中所提到的A,B表。假如查詢欄位是colA1，而需要獲取的欄位是colB1, colB2 這個時候可以這樣操作：

  • 第一步：根據colA1，線從A表中獲取對應的資料Aid欄位（當查詢前已經可以確定Aid時，該步驟可以省略，這步更適合colA1是作為模糊查詢呢的條件進行查詢的情況）

  • 第二步：根據Aid欄位，從B表中獲取所需要的欄位colB1,colB2

同樣，該組查詢也只是考慮在資料量比較大的時候，對於小資料的時候，沒什麼必要。

2.3 其它減少連表查詢的方法

  • 提供冗餘欄位，即將A表中需要展示的欄位，提前放入B表中，兩邊各存一份，這會增加維護和更新的成本，但可以避免連表查詢，可以視業務場景考慮使用

3 結尾

以上僅僅是個人對資料庫中部分查詢業務的思考，希望在服務執行過程中，可以對服務的呼叫起到一定的效率提升，如處理方式不妥之處，還請各位大大指正，共同探討更好的優化方案。


本文來自網易雲社群 ，經作者王飛授權釋出。

網易雲免費體驗館，0成本體驗20+款雲產品！

更多網易研發、產品、運營經驗分享請訪問網易雲社群。


相關文章：
【推薦】 MySQL MGR叢集搭建
【推薦】 淺析電商防止惡意下單
【推薦】 如何看待P2P領域的羊毛黨？

相關文章