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

單表優(yōu)化

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

字段

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

索引

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

查詢SQL

可通過(guò)開(kāi)啟慢查詢?nèi)罩緛?lái)找出較慢的SQL不做列運(yùn)算：SELECT id WHERE age + 1 = 10，任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫(kù)教程函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等號(hào)右邊sql語(yǔ)句盡可能簡(jiǎn)單：一條sql只能在一個(gè)cpu運(yùn)算；大語(yǔ)句拆小語(yǔ)句，減少鎖時(shí)間；一條大sql可以堵死整個(gè)庫(kù)不用SELECT *OR改寫(xiě)成IN：OR的效率是n級(jí)別，IN的效率是log(n)級(jí)別，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)行全表掃描對(duì)于連續(xù)數(shù)值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5列表數(shù)據(jù)不要拿全表，要使用LIMIT來(lái)分頁(yè)，每頁(yè)數(shù)量也不要太大

延伸閱讀：

二、sql緩存

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

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

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

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