字段優(yōu)化
- 表字段 not null，因?yàn)?null 值很難查詢(xún)優(yōu)化且占用額外的索引空間，推薦默認(rèn)數(shù)字 0。
- 數(shù)據(jù)狀態(tài)類(lèi)型的字段，比如 status, type 等等，盡量不要定義負(fù)數(shù)，如 -1。因?yàn)檫@樣可以加上 UNSIGNED，數(shù)值容量就會(huì)擴(kuò)大一倍。
- 可以的話(huà)用 TINYINT、SMALLINT 等代替 INT，盡量不使用 BIGINT，因?yàn)檎嫉目臻g更小。
- 字符串類(lèi)型的字段會(huì)比數(shù)字類(lèi)型占的空間更大，所以盡量用整型代替字符串，很多場(chǎng)景是可以通過(guò)編碼邏輯來(lái)實(shí)現(xiàn)用整型代替的。
- 字符串類(lèi)型長(zhǎng)度不要隨意設(shè)置，保證滿(mǎn)足業(yè)務(wù)的前提下盡量小。
- 用整型來(lái)存 IP。
- 單表不要有太多字段，建議在20以?xún)?nèi)。
- 為能預(yù)見(jiàn)的字段提前預(yù)留，因?yàn)閿?shù)據(jù)量越大，修改數(shù)據(jù)結(jié)構(gòu)越耗時(shí)。
索引設(shè)計(jì)
索引，空間換時(shí)間的優(yōu)化策略，基本上根據(jù)業(yè)務(wù)需求設(shè)計(jì)好索引，足以應(yīng)付百萬(wàn)級(jí)的數(shù)據(jù)量，養(yǎng)成使用 explain 的習(xí)慣，關(guān)于 explain 也可以訪問(wèn)：explain 讓你的 sql 寫(xiě)的更踏實(shí)了解更多。
- 一個(gè)常識(shí)：索引并不是越多越好，索引是會(huì)降低數(shù)據(jù)寫(xiě)入性能的。
- 索引字段長(zhǎng)度盡量短，這樣能夠節(jié)省大量索引空間；
- 取消外鍵，可交由程序來(lái)約束，性能更好。
- 復(fù)合索引的匹配最左列規(guī)則，索引的順序和查詢(xún)條件保持一致，盡量去除沒(méi)必要的單列索引。
- 值分布較少的字段（不重復(fù)的較少）不適合建索引，比如像性別這種只有兩三個(gè)值的情況字段建立索引意義不大。
- 需要排序的字段建議加上索引，因?yàn)樗饕菚?huì)排序的，能提高查詢(xún)性能。
- 字符串字段使用前綴索引，不使用全字段索引，可大幅減小索引空間。
編寫(xiě)習(xí)慣
- 查詢(xún)不使用 select *，盡量查詢(xún)帶索引的字段，避免回表。
- 盡量使用 limit 對(duì)查詢(xún)數(shù)量進(jìn)行限制。
- 查詢(xún)字段盡量落在索引上，尤其是復(fù)合索引，順序很重要。
- 拆分大的 delete / insert 操作，一方面會(huì)鎖表，影響其他業(yè)務(wù)操作，還有一方面是 MySQL 對(duì) sql 長(zhǎng)度也是有限制的。
- 不建議使用 MySQL 的函數(shù)，計(jì)算等，可先由程序處理，從上面提的一些點(diǎn)會(huì)發(fā)現(xiàn)，能交由程序處理的盡量不要把壓力轉(zhuǎn)至數(shù)據(jù)庫(kù)上。
- 查詢(xún) count，性能：count(1) = count(*) > count(主鍵) > count(其他字段)。
- 查詢(xún)操作符能用 between 則不用 in，能用 in 則不用 or。
- 避免使用 != 或 ＜＞、IS NULL 或 IS NOT NULL、IN ，NOT IN 等這樣的操作符，因?yàn)檫@些查詢(xún)可能進(jìn)行全表掃描。
- sql 盡量簡(jiǎn)單，少用 join，不建議兩個(gè) join 以上。
分區(qū)
分區(qū)是根據(jù)一定的規(guī)則，數(shù)據(jù)庫(kù)把一個(gè)表分解成多個(gè)更小的、更容易管理的部分，是一種水平劃分。對(duì)應(yīng)用來(lái)說(shuō)是完全透明的，不影響應(yīng)用的業(yè)務(wù)邏輯，即不用修改代碼。因此能存更多的數(shù)據(jù)，查詢(xún)，刪除也支持按分區(qū)來(lái)操作，從而達(dá)到優(yōu)化的目的。如果有考慮分區(qū)，可以提前做準(zhǔn)備，避免下列一些限制：
- 一個(gè)表最多只能有1024個(gè)分區(qū)（mysql5.6之后支持8192個(gè)分區(qū)）。
- 如果分區(qū)字段中有主鍵或者唯一索引列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來(lái)，如果表中有主鍵或唯一索引，那么分區(qū)鍵必須是主鍵或唯一索引。
- 分區(qū)表中無(wú)法使用外鍵約束。
- NULL值會(huì)使分區(qū)過(guò)濾無(wú)效。
- 目前mysql不支持空間類(lèi)型和臨時(shí)表類(lèi)型進(jìn)行分區(qū)。不支持全文索引。
- 所有分區(qū)必須使用相同的存儲(chǔ)引擎。
分區(qū)可按以下四種類(lèi)型分區(qū)：
- RANGE表分區(qū)：范圍表分區(qū)，按照一定的范圍值來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù);
- LIST表分區(qū)：列表表分區(qū)，按照一個(gè)一個(gè)確定的值來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù);
- HASH表分區(qū)：哈希表分區(qū)，按照一個(gè)自定義的函數(shù)返回值來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù);
- KEY表分區(qū) ：key表分區(qū)，與哈希表分區(qū)類(lèi)似，只是用MySQL自己的HASH函數(shù)來(lái)確定每個(gè)分區(qū)包含的數(shù)據(jù)。
分表
分表分水平分表和垂直分表。
- 水平分表即拆分成數(shù)據(jù)結(jié)構(gòu)相同的各個(gè)小表，如拆分成 table1, table2...，從而緩解數(shù)據(jù)庫(kù)讀寫(xiě)壓力。
- 垂直分表即將一些字段分出去形成一個(gè)新表，各個(gè)表數(shù)據(jù)結(jié)構(gòu)不相同，可以?xún)?yōu)化高并發(fā)下鎖表的情況。
可想而知，分表的話(huà)，程序的邏輯是需要做修改的，所以，一般是在項(xiàng)目初期時(shí)，預(yù)見(jiàn)到大數(shù)據(jù)量的情況，才會(huì)考慮分表。后期階段不建議分表，成本很大。
分庫(kù)
分庫(kù)一般是主從模式，一個(gè)數(shù)據(jù)庫(kù)服務(wù)器主節(jié)點(diǎn)復(fù)制到一個(gè)或多個(gè)從節(jié)點(diǎn)多個(gè)數(shù)據(jù)庫(kù)，主庫(kù)負(fù)責(zé)寫(xiě)操作，從庫(kù)負(fù)責(zé)讀操作，從而達(dá)到主從分離，高可用，數(shù)據(jù)備份等優(yōu)化目的。
