10分鐘小幅最佳化億級資料查詢，最佳化億級資料查詢

10分鐘小幅最佳化億級資料查詢，最佳化億級資料查詢

前幾天，一個使用者研發QQ找我，如下：

自由的海豚。 16:12:01

島主，我的一條SQL查不出來結果，能幫我看看不？

蘭花島主 16:12:10

多久不出結果？

自由的海豚 16:12:17

多久都沒出結果，一直沒看到結果過。

蘭花島主 16:12:26

呵呵，好。

蘭花島主 16:12:39

發下sql和執行計畫。

自由的海豚 16:12:55

select n.c1, n.c2,n.c3,n.c4,n.c5
  from (select  count(t.c1), t.c1, t.c2,t.c3,t.c4,t.c5
          from tab1 t
         where t.c2 not in ('val1','val2','val3','val4','val5')
         group by t.c1, t.c2,t.c3,t.c4,t.c5) n
 where not exists
 (select * from (
select  count(s.c2), s.c1, s.c2                     
  from (select m.c1, m.c2,m.c3,m.c4,m.c5
          from tab1 m
         where exists (select c1
                  from tab2 n
                 where c2 > sysdate - 14
                   and m.c1 = n.c1)
           and m.c1 is not null
           and m.c2 not in  ('val1','val2', 'val3', 'val4', 'val5')) s
 group by s.c1, s.c2) t1 where t1.c2 = n.c2)
   and n.c1 is not null;

蘭花島主 16:13:12

這兩張表大嗎？

自由的海豚 16:13:16

tab1小，tab2億級以上，兩周資料在幾千萬。

蘭化島主 16:13:22

OK。

蘭花島主 16:16:29

這麼改下sql吧:

with t1 as(
select count(t.c1), t.c1,t.c2,t.c3,t.c4,t.c5
  from tab1 t
 where t.c2 not in ('val1','val2','val3','val4','val5')
   and c1 is not null
 group by t.c1, t.c2,t.c3,t.c4,t.c5) 
select t1.c1,t1.c2,t1.c3,t1.c4,t1.c5
  from t1 
where not exists(
select /*+ use_hash(m,n)*/ m.c1, m.c2,m.c3,m.c4,m.c5
   from t1 m,tab2 n
  where n.c2 > sysdate - 14
    and m.c1 = n.c1
    and t1.c2 = m.c2);

蘭花島主 16:16:43

取下執行計畫。

自由的海豚 16:16:57

好的。

自由的海豚 16:17:25

蘭花島主 16:17:57

好的，試試吧。

自由的海豚 16:19:28

出結果了，37s

蘭花島主 17:20:21

 嗯，好。

蘭花島主 17:20:34

 這樣可以嗎？

自由的海豚 17:20:47

 可以了可以了

蘭花島主 17:21:11

 嗯，好，那就先這樣，不繼續調了。

自由的海豚 17:21:30

 恩 好的 謝謝島主 

蘭花島主 17:21:53

 不客氣，忙吧，有事兒聯絡。

自由的海豚 17:22:18

 恩，您忙。。。

至此，對使用者這個sql的最佳化結束，其實，這個語句應該還有最佳化的空間，只是，使用者說可以了那就可以了，因為最佳化是無止境的，而且，更進一步最佳化也許會需要更進一步的資訊，且有時會需要更大的改動，鑒於各方面因素，文中對語句和計划進行了處理，記錄於此，共勉！

億級資料的查詢最佳化，最佳化大師們來幫忙解決下

建議採用oracle 資料庫，可以把表按照規律分區，條件允許可以把不同的表放在不同的磁碟上也可以提升讀寫速度
 
mysql 達到1億層級的表怎設計最佳化

單表一億？還是全庫1億？
1.首先可以考慮業務層面最佳化，即垂直分表。
垂直分表就是把一個資料量很大的表,可以按某個欄位的屬性或使用頻繁程度分類,拆分為多個表。
如有多種業務類型，每種業務類型入不同的表，table1,table2,table3.
如果日常業務不需要使用所有資料，可以按時間分表，比如說月表。每個表只存一個月記錄。

2.架構上的最佳化，即水平分表。
水平分表就是根據一列或多列資料的值把資料行放到多個獨立的表裡，這裡不具備業務意義。
如按照id分表，末尾是0-9的資料分別插入到10個表裡面。
可能你要問，這樣看起來和剛才說的垂直分表沒什麼區別。只不過是否具備業務意義的差異，都是按欄位的值來分表。

實際上，水平分表現在最流行的實現方式，是通過水平分庫來實現的。即剛才所說的10個表，分布在10個mysql資料庫上。這樣可以通過多個低配置主機整合起來，實現高效能。

最常見的解決方案是cobar，這個文章介紹的比較完善，可以看看。
blog.csdn.net/shagoo/article/details/8191346

cobar的邏輯層次圖：

不過這種分庫方式也是有一定局限性的，需要應用程式做相應的配合，比如說分庫的情況下，雖然可以實現跨庫查詢，但是不能進行相關的group by計算。

另外，之前關於水平分表的實現方式，也可以通過表分區來實現。

mysql最佳化的方式有很多，選擇上主要還是要考慮個人的實際情況，如代碼不可控的情況下，就不適合選擇按欄位屬性分表的情況，這樣可能會帶來大量的重構以及很多不可預期的風險。

而架構的最佳化，雖然對應用是透明的，但對sql的寫法有很多局限性，比如說不能使用彙總函式等等，同時也需要有充足的硬體資源，只有一台伺服器的情況下是沒有意義的。

相比起來，代價最低的是按時間分表或分區，這兩種辦法對應用來說都是透明的。

分區只需要一次本機資料遷移的操作。
而通過分表把現網資料和曆史資料分離，唯一的代價是週期性資料維護。

一般如果表裡面有1億資料的情況下，索引的問題應該是常識了，這方面我就不說了。

另外如果覺得答的不錯多給點分。