概述
使用阿里雲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塊錢就解決了資料處理問題。
概述
使用阿里雲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塊錢就解決了資料處理問題。