首頁>Club>
6
回覆列表
  • 1 # hello大資料

    概述

    使用阿里雲rds for MySQL資料庫(就是MySQL5.6版本),有個使用者上網記錄表6個月的資料量近2000萬,保留最近一年的資料量達到4000萬,查詢速度極慢,日常卡死,嚴重影響業務。

    老系統,當時設計系統的人大概是大學沒畢業,表設計和SQL語句寫的不僅僅是垃圾,簡直無法直視。原開發人員都已離職,到我來維護,這就是傳說中的維護不了就跑路,然後我就是掉坑的那個!!!

    方案概述

    方案一:最佳化現有MySQL資料庫。優點:不影響現有業務,源程式不需要修改程式碼,成本最低。缺點:有最佳化瓶頸,資料量過億就玩完了。

    方案二:升級資料庫型別,換一種100%相容MySQL的資料庫。優點:不影響現有業務,源程式不需要修改程式碼,你幾乎不需要做任何操作就能提升資料庫效能,缺點:多花錢。

    方案三:一步到位,大資料解決方案,更換newSQL/noSQL資料庫。優點:沒有資料容量瓶頸,缺點:需要修改源程式程式碼,影響業務,總成本最高。

    最佳化現有MySQL資料庫資料庫設計

    表字段避免null值出現,null值很難查詢最佳化且佔用額外的索引空間,推薦預設數字0代替null。

    儘量使用INT而非BIGINT,如果非負則加上UNSIGNED(這樣數值容量會擴大一倍),當然能使用TINYINT、SMALLINT、MEDIUM_INT更好。

    儘量使用TIMESTAMP而非DATETIME。

    單表不要有太多欄位,建議在20以內。

    用整型來存IP。

    索引並不是越多越好,要根據查詢有針對性的建立,考慮在WHERE和ORDER BY命令上涉及的列建立索引,可根據EXPLAIN來檢視是否用了索引還是全表掃描。

    應儘量避免在WHERE子句中對欄位進行NULL值判斷,否則將導致引擎放棄使用索引而進行全表掃描。

    值分佈很稀少的欄位不適合建索引,例如"性別"這種只有兩三個值的欄位。

    字元欄位最好不要做主鍵。

    不用外來鍵,由程式保證約束。

    儘量不用UNIQUE,由程式保證約束。

    使用可存下資料的最小的資料型別,整型 < date,time < char,varchar < blob*

    使用簡單的資料型別,整型比字元處理開銷更小,因為字串的比較更復雜。如,int型別儲存時間型別,bigint型別轉ip函式。

    使用合理的欄位屬性長度,固定長度的表會更快。使用enum、char而不是varchar。

    儘可能使用not null定義欄位。

    儘量少用text,非用不可最好分表。

    查詢頻繁的列,在where,group by,order by,on從句中出現的列。

    where條件中<,<=,=,>,>=,between,in,以及like 字串+萬用字元(%)出現的列。

    長度小的列,索引欄位越小越好,因為資料庫的儲存單位是頁,一頁中能存下的資料越多越好。

    離散度大(不同的值多)的列,放在聯合索引前面。檢視離散度,透過統計不同的列值來實現,count越大,離散程度越高。

    SQL編寫

    使用limit對查詢結果的記錄進行限定。

    避免select *,將需要查詢的欄位列出來。

    使用連線(join)來代替子查詢。

    拆分大的delete或insert語句。

    可透過開啟慢查詢日誌來找出較慢的SQL。

    不做列運算:SELECT id WHERE age + 1 = 10,任何對列的操作都將導致表掃描,它包括資料庫教程函式、計算表示式等等,查詢時要儘可能將操作移至等號右邊。

    SQL語句儘可能簡單:一條SQL只能在一個cpu運算;大語句拆小語句,減少鎖時間;一條大SQL可以堵死整個庫。

    OR改寫成IN:OR的效率是n級別,IN的效率是log(n)級別,in的個數建議控制在200以內。

    不用函式和觸發器,在應用程式實現。

    避免%xxx式查詢。

    少用JOIN。

    使用同類型進行比較,比如用"123"和"123"比,123和123比。

    儘量避免在WHERE子句中使用!=或<>運算子,否則將引擎放棄使用索引而進行全表掃描。

    對於連續數值,使用BETWEEN不用IN:SELECT id FROM t WHERE num BETWEEN 1 AND 5。

    列表資料不要拿全表,要使用LIMIT來分頁,每頁數量也不要太大。

    分割槽

    部分查詢能夠從查詢條件確定只落在少數分割槽上,速度會很快。

    分割槽表的資料還可以分佈在不同的物理裝置上,從而搞笑利用多個硬體裝置。

    可以使用分割槽表賴避免某些特殊瓶頸,例如InnoDB單個索引的互斥訪問、ext3檔案系統的inode鎖競爭。

    可以備份和恢復單個分割槽。

    一個表最多隻能有1024個分割槽。

    如果分割槽欄位中有主鍵或者唯一索引的列,那麼所有主鍵列和唯一索引列都必須包含進來。NULL值會使分割槽過濾無效。

    所有分割槽必須使用相同的儲存引擎。

    分表

    分表就是把一張大表,按照如上過程都優化了,還是查詢卡死,那就把這個表分成多張表,把一次查詢分成多次查詢,然後把結果組合返回給使用者。

    分表分為垂直拆分和水平拆分,通常以某個欄位做拆分項。比如以id欄位拆分為100張表:表名為 tableName_id%100。

    但:分表需要修改源程式程式碼,會給開發帶來大量工作,極大的增加了開發成本,故:只適合在開發初期就考慮到了大量資料存在,做好了分表處理,不適合應用上線了再做修改,成本太高!!!而且選擇這個方案,都不如選擇我提供的第二第三個方案的成本低!故不建議採用。

    分庫升級資料庫

    開源資料庫會帶來大量的運維成本且其工業品質和MySQL尚有差距,有很多坑要踩,如果你公司要求必須自建資料庫,那麼選擇該型別產品。如tiDB pingcap/tidb,Cubrid Open Source Database With Enterprise Features。

    阿里雲POLARDB,POLARDB 是阿里雲自研的下一代關係型分散式雲原生資料庫,100%相容MySQL,儲存容量最高可達 100T,效能最高提升至 MySQL 的 6 倍。POLARDB 既融合了商業資料庫穩定、可靠、高效能的特徵,又具有開源資料庫簡單、可擴充套件、持續迭代的優勢,而成本只需商用資料庫的 1/10。

    阿里雲OcenanBase,淘寶使用的,扛得住雙十一,效能卓著,但是在公測中,我無法嘗試,但值得期待。

    阿里雲HybridDB for MySQL (原PetaData),雲資料庫HybridDB for MySQL (原名PetaData)是同時支援海量資料線上事務(OLTP)和線上分析(OLAP)的HTAP(Hybrid Transaction/Analytical Processing)關係型資料庫。

    騰訊雲DCDB,DCDB又名TDSQL,一種相容MySQL協議和語法,支援自動水平拆分的高效能分散式資料庫——即業務顯示為完整的邏輯表,資料卻均勻的拆分到多個分片中;每個分片預設採用主備架構,提供災備、恢復、監控、不停機擴容等全套解決方案,適用於TB或PB級的海量資料場景。

    hadoop家族。hbase/hive懟上就是了。但是有很高的運維成本,一般公司是玩不起的,沒十萬投入是不會有很好的產出的!

    我選擇了阿里雲的MaxCompute配合DataWorks,使用超級舒服,按量付費,成本極低。

    MaxCompute可以理解為開源的Hive,提供SQL/mapreduce/ai演算法/python指令碼/shell指令碼等方式操作資料,資料以表格的形式展現,以分散式方式儲存,採用定時任務和批處理的方式處理資料。DataWorks提供了一種工作流的方式管理你的資料處理任務和排程監控。

    當然你也可以選擇阿里雲hbase等其他產品,我這裡主要是離線處理,故選擇MaxCompute,基本都是圖形介面操作,大概寫了300行SQL,費用不超過100塊錢就解決了資料處理問題。

  • 中秋節和大豐收的關聯?
  • 與人交往中怎樣才能不卑微?