大量讀寫的mysql表怎么優化?

一、大量讀寫的mysql表優化步驟

單表優化

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

字段

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

索引

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

查詢SQL

可通過開啟慢查詢日志來找出較慢的SQL不做列運算：SELECT id WHERE age + 1 = 10，任何對列的操作都將導致表掃描，它包括數據庫教程函數、計算表達式等等，查詢時要盡可能將操作移至等號右邊sql語句盡可能簡單：一條sql只能在一個cpu運算；大語句拆小語句，減少鎖時間；一條大sql可以堵死整個庫不用SELECT *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來分頁，每頁數量也不要太大

延伸閱讀：

二、sql緩存

緩存可以發生在這些層次：

MySQL內部：在系統調優參數介紹了相關設置數據訪問層：比如MyBatis針對SQL語句做緩存，而Hibernate可以精確到單個記錄，這里緩存的對象主要是持久化對象Persistence Object應用服務層：這里可以通過編程手段對緩存做到更精準的控制和更多的實現策略，這里緩存的對象是數據傳輸對象Data Transfer ObjectWeb層：針對web頁面做緩存瀏覽器客戶端：用戶端的緩存

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

直寫式（Write Through）：在數據寫入數據庫后，同時更新緩存，維持數據庫與緩存的一致性。這也是當前大多數應用緩存框架如Spring Cache的工作方式。這種實現非常簡單，同步好，但效率一般?；貙懯剑╓rite Back）：當有數據要寫入數據庫時，只會更新緩存，然后異步批量的將緩存數據同步到數據庫上。這種實現比較復雜，需要較多的應用邏輯，同時可能會產生數據庫與緩存的不同步，但效率非常高。