[TOC] # 設計 數據庫優化有哪些?分別需要注意什么? 數據庫設計 盡可能地使用最有效(最小)的數據類型 ~~~ tinyint(1Byte) smallint(2Byte) mediumint(3Byte) int(4Byte) bigint(8Byte) bad case：int(1)/int(11) ~~~ 不要將數字存儲為字符串，字符轉化為數字，用int存儲ip而非char(15) 優先使用enum或set，sex enum (‘F’, ‘M’) 避免使用NULL字段 ~~~ NULL字段很難查詢優化 NULL字段的索引需要額外空間 NULL字段的復合索引無效 bad case：`name` char(32) default null`age` int not null good case：`age` int not null default 0 ~~~ 1. 精確度與空間的轉換。在存儲相同數值范圍的數據時，浮點數類型通常都會比DECIMAL類型使用更少的空間。FLOAT字段使用4 字節存儲 數據。DOUBLE類型需要8 個字節并擁有更高的精確度和更大的數值范圍，DECIMAL類型的數據將會轉換成DOUBLE類型 2. InnoDB表行記錄物理長度不超過8KB，InnoDB的data page默認是16KB，基于B+Tree的特點，一個data page中需要至少存儲2條記錄。因此，當實際存儲長度超過8KB（尤其是TEXT/BLOB列）的大列（large column）時會引起“page-overflow存儲”，類似ORACLE中的“行遷移”，因此，如果必須使用大列（尤其是TEXT/BLOB類型）且讀寫頻繁的話，則最好把這些列拆分到子表中，不要和主表放在一起存儲，如果不太頻繁，可以考慮繼續保留在主表中，如果將 innodbpagesize 選項修改成 8KB，那么行記錄物理長度建議不超過4KB # 索引 1. 字符字段必須建前綴索引 2. 不在索引做列運算，bad case：select id where age +1 = 10; 3. innodb主鍵推薦使用自增列 主鍵建立聚簇索引 主鍵不應該被修改 字符串不應該做主鍵 如果不指定主鍵，innodb會使用唯一且非空值索引代替 4. 不用外鍵，請由程序保證約束 5. 避免在已有索引的前綴上建立索引。例如：如果存在index（a，b）則去掉in 6. 控制單個索引的長度。使用key（name（8））在數據的前面幾個字符建立索引 7. 要選擇性的使用索引。在變化很少的列上使用索引并不是很好，例如性別列 8. Optimize table可以壓縮和排序index，注意不要頻繁運行 9. Analyze table可以更新數據 10. 索引選擇性是不重復的索引值也叫基數（cardinality）表中數據行數的比值，索引選擇性=基數/數據行，count(distinct(username))/count(*) 就是索引選擇性，高索引選擇性的好處就是mysql查找匹配的時候可以過濾更多的行，唯一索引的選擇性最佳，值為1 11. 不要用重復或多余索引，對于INNODB引擎的索引來說，每次修改數據都要把主鍵索引，輔助索引中相應索引值修改，這可能會出現大量數 據遷移，分頁，以及碎片的出現 12. 超過20個長度的字符串列，最好創建前綴索引而非整列索引（例如：ALTER TABLE t1 ADD INDEX(user(20))），可以有效提高索引利用率，不過它的缺點是對這個列排序時用不到前綴索引。前綴索引的長度可以基于對該字段的統計得出， 一般略大于平均長度一點就可以了 13. 定期用 pt-duplicate-key-checker 工具檢查并刪除重復的索引。比如 index idx1(a, b) 索引已經涵蓋了 index idx2(a)，就可以刪除 idx2 索引了 # sql 1. sql語句盡可能簡單,一條sql只能在一個cpu運算，大語句拆小語句，減少鎖時間，一條大sql可以堵死整個庫(充分利用QUERY CACHE和充分利用多核CPU) 2. 簡單的事務,事務時間盡可能短,bad case：上傳圖片事務 3. 避免使用trig/func,觸發器、函數不用,客戶端程序取而代之 4. 不用select *,消耗cpu，io，內存，帶寬,這種程序不具有擴展性 5. OR改寫為IN() 6. or的效率是n級別 in的消息時log(n)級別 in的個數建議控制在200以內 select id from t where phone=’159′ or phone=’136′ =>select id from t where phone in (’159′, ’136′); 7. OR改寫為UNION mysql的索引合并很弱智 select id from t where phone = '159' or name = 'john'; => select id from t where phone='159' union select id from t where name='jonh'; 8. 避免負向%，如not in/like 9. 慎用count(*) 10. limit高效分頁 limit越大，效率越低 ~~~ select id from t limit 10000, 10; => select id from t where id > 10000 limit 10; ~~~ 11. 使用union all替代union，union有去重開銷 12. 少用連接join 13. 使用group by，分組、自動排序 15. 請使用同類型比較 16. 使用load data導數據，load data比insert快約20倍 17. 對數據的更新要打散后批量更新，不要一次更新太多數據 18. 使用性能分析工具 ~~~ Sql explain / showprofile / mysqlsla 使用--log-slow-queries –long-query-time=2查看查詢比較慢的語句。然后使用explain分析查詢，做出優化 show profile; mysqlsla; mysqldumpslow; explain; show slow log; show processlist; show query_response_time(percona) ~~~ 19. optimize 數據在插入，更新，刪除的時候難免一些數據遷移，分頁，之后就出現一些碎片，久而久之碎片積累起來影響性能， 這就需要DBA定期的優化數據庫減少碎片，這就通過optimize命令。如對MyISAM表操作：optimize table 表名 20. 禁止在數據庫中跑大查詢 21. 使?預編譯語句，只傳參數，比傳遞SQL語句更高效；一次解析，多次使用；降低SQL注入概率 22. 禁止使?order by rand() 23. 禁?單條SQL語句同時更新多個表 24. 避免在數據庫中進?數學運算(MySQL不擅長數學運算和邏輯判斷) 25. SQL語句要求所有研發，SQL關鍵字全部是大寫，每個詞只允許有一個空格 26. 能不用NOT IN就不用NOT IN，坑太多了。。會把空和NULL給查出來