先看一個(gè)MySQL未優(yōu)化的案例

使用阿里云rds for MySQL數(shù)據(jù)庫（就是MySQL5.6版本）,，有個(gè)用戶上網(wǎng)記錄表6個(gè)月的數(shù)據(jù)量近2000萬,，保留最近一年的數(shù)據(jù)量達(dá)到4000萬，查詢速度極慢,，日?？ㄋ溃瑖?yán)重影響業(yè)務(wù),。

老系統(tǒng),，當(dāng)時(shí)設(shè)計(jì)系統(tǒng)的人大概是大學(xué)沒畢業(yè)，表設(shè)計(jì)和SQL語句寫的不僅僅是垃圾,，簡直無法直視,。原開發(fā)人員都已離職，到我來維護(hù),，這就是傳說中的維護(hù)不了就跑路,，然后我就是掉坑的那個(gè)?。,。?/p>

方案概述

方案一：優(yōu)化現(xiàn)有MySQL數(shù)據(jù)庫

優(yōu)點(diǎn)：不影響現(xiàn)有業(yè)務(wù),，源程序不需要修改,，成本最低；缺點(diǎn)：有優(yōu)化瓶頸,，數(shù)據(jù)量過億就玩完了,。

方案二：升級數(shù)據(jù)庫類型

優(yōu)點(diǎn)：幾乎不需要做任何操作就能提升數(shù)據(jù)庫性能；缺點(diǎn)：多花錢,。

方案三：更換大數(shù)據(jù)引擎

優(yōu)點(diǎn)：沒有數(shù)據(jù)容量瓶頸,；缺點(diǎn)：需要修改源程序代碼，影響業(yè)務(wù),，總成本最高,。

1 優(yōu)化現(xiàn)有MySQL數(shù)據(jù)庫

1.1 數(shù)據(jù)庫設(shè)計(jì)

表字段避免null值出現(xiàn)，null值很難查詢優(yōu)化且占用額外的索引空間,，推薦默認(rèn)數(shù)字0代替null,。盡量使用INT而非BIGINT，如果非負(fù)則加上UNSIGNED（這樣數(shù)值容量會擴(kuò)大一倍）,，當(dāng)然能使用TINYINT,、SMALLINT、MEDIUM_INT更好,。使用枚舉或整數(shù)代替字符串類型,。盡量使用TIMESTAMP而非DATETIME。單表不要有太多字段,，建議在20以內(nèi),。用整型來存IP,。

1.2 索引設(shè)計(jì)

索引并不是越多越好，要根據(jù)查詢有針對性的創(chuàng)建,，考慮在WHERE和ORDER BY命令上涉及的列建立索引,，可根據(jù)EXPLAIN來查看是否用了索引還是全表掃描。應(yīng)盡量避免在WHERE子句中對字段進(jìn)行NULL值判斷,，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描,。

值分布很稀少的字段不適合建索引，例如'性別'這種只有兩三個(gè)值的字段,。字符字段只建前綴索引,。字符字段最好不要做主鍵。不用外鍵,，由程序保證約束,。盡量不用UNIQUE，由程序保證約束,。用多列索引時(shí)注意順序和查詢條件保持一致,，同時(shí)刪除不必要的單列索引。

另外,，使用可存下數(shù)據(jù)的最小的數(shù)據(jù)類型,，整型 < date,time < char,varchar < blob* 。使用簡單的數(shù)據(jù)類型,，整型比字符處理開銷更小,，因?yàn)樽址谋容^更復(fù)雜。如,，int類型存儲時(shí)間類型,，bigint類型轉(zhuǎn)ip函數(shù)。使用合理的字段屬性長度,，固定長度的表會更快,。

使用enum、char而不是varchar,。盡可能使用not null定義字段,。盡量少用text，非用不可最好分表,。查詢頻繁的列,，在where，group by,，order by,，on從句中出現(xiàn)的列。where條件中<,，<=,，=,，>，>=,，between,，in，以及l(fā)ike 字符串 通配符（%）出現(xiàn)的列,。

最后,，長度小的列，索引字段越小越好,，因?yàn)閿?shù)據(jù)庫的存儲單位是頁,，一頁中能存下的數(shù)據(jù)越多越好。離散度大（不同的值多）的列,，放在聯(lián)合索引前面,。查看離散度，通過統(tǒng)計(jì)不同的列值來實(shí)現(xiàn),，count越大,，離散程度越高。

1.3 SQL編寫

使用limit對查詢結(jié)果的記錄進(jìn)行限定,。避免select *,，將需要查找的字段列出來,。使用連接（join）來代替子查詢,。拆分大的delete或insert語句?？赏ㄟ^開啟慢查詢?nèi)罩緛碚页鲚^慢的SQL,。不做列運(yùn)算：

任何對列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫教程函數(shù),、計(jì)算表達(dá)式等等,，查詢時(shí)要盡可能將操作移至等號右邊。

SQL語句盡可能簡單：一條SQL只能在一個(gè)cpu運(yùn)算,；大語句拆小語句,，減少鎖時(shí)間；一條大SQL可以堵死整個(gè)庫,。OR改寫成IN：OR的效率是n級別,，IN的效率是log(n)級別，in的個(gè)數(shù)建議控制在200以內(nèi),。不用函數(shù)和觸發(fā)器,，在應(yīng)用程序?qū)崿F(xiàn)。避免%xxx式查詢,。

少用JOIN,，使用同類型進(jìn)行比較,，比如用'123'和'123'比，123和123比,。盡量避免在WHERE子句中使用!=或<>操作符,，否則將引擎放棄使用索引而進(jìn)行全表掃描。對于連續(xù)數(shù)值,，使用BETWEEN不用IN：

SELECT id FROM t WHERE num BETWEEN 1 AND 5

列表數(shù)據(jù)不要拿全表,，要使用LIMIT來分頁，每頁數(shù)量也不要太大,。

1.4 分區(qū)

可以讓單表存儲更多的數(shù)據(jù),。分區(qū)表的數(shù)據(jù)更容易維護(hù)，可以通過清楚整個(gè)分區(qū)批量刪除大量數(shù)據(jù),，也可以增加新的分區(qū)來支持新插入的數(shù)據(jù),。

另外，還可以對一個(gè)獨(dú)立分區(qū)進(jìn)行優(yōu)化,、檢查,、修復(fù)等操作。部分查詢能夠從查詢條件確定只落在少數(shù)分區(qū)上,，速度會很快,。分區(qū)表的數(shù)據(jù)還可以分布在不同的物理設(shè)備上，從而高效利用多個(gè)硬件設(shè)備,?？梢允褂梅謪^(qū)表來避免某些特殊瓶頸，例如InnoDB單個(gè)索引的互斥訪問,、ext3文件系統(tǒng)的inode鎖競爭,。可以備份和恢復(fù)單個(gè)分區(qū),。

一個(gè)表最多只能有1024個(gè)分區(qū),。如果分區(qū)字段中有主鍵或者唯一索引的列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來,。分區(qū)表無法使用外鍵約束,。NULL值會使分區(qū)過濾無效。所有分區(qū)必須使用相同的存儲引擎,。

1.5 分表

分表就是把一張大表,，按照如上過程都優(yōu)化了，還是查詢卡死,，那就把這個(gè)表分成多張表,，把一次查詢分成多次查詢，然后把結(jié)果組合返回給用戶,。

分表分為垂直拆分和水平拆分,，通常以某個(gè)字段做拆分項(xiàng),。比如以id字段拆分為100張表：表名為 tableName_id%100。分表需要修改源程序代碼,，會給開發(fā)帶來大量工作,，極大的增加了開發(fā)成本，故：只適合在開發(fā)初期就考慮到了大量數(shù)據(jù)存在,，做好了分表處理,，不適合應(yīng)用上線了再做修改，成本太高?。,。《疫x擇這個(gè)方案,，都不如選擇我提供的第二第三個(gè)方案的成本低,！故不建議采用。

1.6 分庫

把一個(gè)數(shù)據(jù)庫分成多個(gè),，建議做個(gè)讀寫分離就行了,，真正的做分庫也會帶來大量的開發(fā)成本，得不償失,！不推薦使用,。

2 升級數(shù)據(jù)庫類型

開源數(shù)據(jù)庫會帶來大量的運(yùn)維成本且其工業(yè)品質(zhì)和MySQL尚有差距，有很多坑要踩,，如果你公司要求必須自建數(shù)據(jù)庫,，那么選擇該類型產(chǎn)品。

阿里云POLARDB,，POLARDB 是阿里云自研的下一代關(guān)系型分布式云原生數(shù)據(jù)庫,，100%兼容MySQL，存儲容量最高可達(dá) 100T,，性能最高提升至 MySQL 的 6 倍。POLARDB 既融合了商業(yè)數(shù)據(jù)庫穩(wěn)定,、可靠,、高性能的特征，又具有開源數(shù)據(jù)庫簡單,、可擴(kuò)展,、持續(xù)迭代的優(yōu)勢，而成本只需商用數(shù)據(jù)庫的 1/10,。

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

騰訊云DCDB,，DCDB又名TDSQL，一種兼容MySQL協(xié)議和語法,，支持自動(dòng)水平拆分的高性能分布式數(shù)據(jù)庫——即業(yè)務(wù)顯示為完整的邏輯表,，數(shù)據(jù)卻均勻的拆分到多個(gè)分片中；每個(gè)分片默認(rèn)采用主備架構(gòu),，提供災(zāi)備,、恢復(fù)、監(jiān)控,、不停機(jī)擴(kuò)容等全套解決方案,，適用于TB或PB級的海量數(shù)據(jù)場景。

3 更換大數(shù)據(jù)引擎

Hadoop家族,。hbase/hive懟上就是了,。但是有很高的運(yùn)維成本，一般公司是玩不起的,，沒十萬投入是不會有很好的產(chǎn)出的,！我選擇了阿里云的MaxCompute配合DataWorks，使用超級舒服,，按量付費(fèi),，成本極低。

MaxCompute可以理解為開源的Hive,，提供SQL,、Ai算法、python腳本,、shell腳本等方式操作數(shù)據(jù),，數(shù)據(jù)以表格的形式展現(xiàn)，以分布式方式存儲,，采用定時(shí)任務(wù)和批處理的方式處理數(shù)據(jù),。DataWorks提供了一種工作流的方式管理你的數(shù)據(jù)處理任務(wù)和調(diào)度監(jiān)控。

當(dāng)然你也可以選擇阿里云hbase等其他產(chǎn)品,，我這里主要是離線處理,，故選擇MaxCompute，基本都是圖形界面操作,，大概寫了300行SQL,，費(fèi)用不超過100塊錢就解決了數(shù)據(jù)處理問題。