一、大量讀寫的mysql表優(yōu)化步驟

單表優(yōu)化

除非單表數(shù)據(jù)未來會一直不斷上漲，否則不要一開始就考慮拆分，拆分會帶來邏輯、部署、運維的各種復(fù)雜度，一般以整型值為主的表在千萬級以下，字符串為主的表在五百萬以下是沒有太大問題的。而事實上很多時候MySQL單表的性能依然有不少優(yōu)化空間，甚至能正常支撐千萬級以上的數(shù)據(jù)量：

字段

盡量使用TINYINT、SMALLINT、MEDIUM_INT作為整數(shù)類型而非INT，如果非負(fù)則加上UNSIGNEDVARCHAR的長度只分配真正需要的空間使用枚舉或整數(shù)代替字符串類型盡量使用TIMESTAMP而非DATETIME，單表不要有太多字段，建議在20以內(nèi)避免使用NULL字段，很難查詢優(yōu)化且占用額外索引空間用整型來存IP

索引

索引并不是越多越好，要根據(jù)查詢有針對性的創(chuàng)建，考慮在WHERE和ORDER BY命令上涉及的列建立索引，可根據(jù)EXPLAIN來查看是否用了索引還是全表掃描應(yīng)盡量避免在WHERE子句中對字段進行NULL值判斷，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描值分布很稀少的字段不適合建索引，例如”性別”這種只有兩三個值的字段字符字段只建前綴索引字符字段較好不要做主鍵不用外鍵，由程序保證約束盡量不用UNIQUE，由程序保證約束使用多列索引時主意順序和查詢條件保持一致，同時刪除不必要的單列索引

查詢SQL

可通過開啟慢查詢?nèi)罩緛碚页鲚^慢的SQL不做列運算：SELECT id WHERE age + 1 = 10，任何對列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫教程函數(shù)、計算表達式等等，查詢時要盡可能將操作移至等號右邊sql語句盡可能簡單：一條sql只能在一個cpu運算；大語句拆小語句，減少鎖時間；一條大sql可以堵死整個庫不用SELECT *OR改寫成IN：OR的效率是n級別，IN的效率是log(n)級別，in的個數(shù)建議控制在200以內(nèi)不用函數(shù)和觸發(fā)器，在應(yīng)用程序?qū)崿F(xiàn)避免%xxx式查詢少用JOIN使用同類型進行比較，比如用’123’和’123’比，123和123比盡量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描對于連續(xù)數(shù)值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5列表數(shù)據(jù)不要拿全表，要使用LIMIT來分頁，每頁數(shù)量也不要太大

延伸閱讀：

二、sql緩存

緩存可以發(fā)生在這些層次：

MySQL內(nèi)部：在系統(tǒng)調(diào)優(yōu)參數(shù)介紹了相關(guān)設(shè)置數(shù)據(jù)訪問層：比如MyBatis針對SQL語句做緩存，而Hibernate可以精確到單個記錄，這里緩存的對象主要是持久化對象Persistence Object應(yīng)用服務(wù)層：這里可以通過編程手段對緩存做到更精準(zhǔn)的控制和更多的實現(xiàn)策略，這里緩存的對象是數(shù)據(jù)傳輸對象Data Transfer ObjectWeb層：針對web頁面做緩存瀏覽器客戶端：用戶端的緩存

可以根據(jù)實際情況在一個層次或多個層次結(jié)合加入緩存。這里重點介紹下服務(wù)層的緩存實現(xiàn)，目前主要有兩種方式：

直寫式（Write Through）：在數(shù)據(jù)寫入數(shù)據(jù)庫后，同時更新緩存，維持?jǐn)?shù)據(jù)庫與緩存的一致性。這也是當(dāng)前大多數(shù)應(yīng)用緩存框架如Spring Cache的工作方式。這種實現(xiàn)非常簡單，同步好，但效率一般。回寫式（Write Back）：當(dāng)有數(shù)據(jù)要寫入數(shù)據(jù)庫時，只會更新緩存，然后異步批量的將緩存數(shù)據(jù)同步到數(shù)據(jù)庫上。這種實現(xiàn)比較復(fù)雜，需要較多的應(yīng)用邏輯，同時可能會產(chǎn)生數(shù)據(jù)庫與緩存的不同步，但效率非常高。