mysql百萬到千萬級別數(shù)據(jù)量的優(yōu)化方案
點(diǎn)擊上方藍(lán)色字體，選擇“標(biāo)星公眾號”
優(yōu)質(zhì)文章，第一時間送達(dá)
百萬級
1. 字段選擇優(yōu)化
表字段 not null，因為 null 值很難查詢優(yōu)化且占用額外的索引空間，推薦默認(rèn)數(shù)字 0。
數(shù)據(jù)狀態(tài)類型的字段，比如 status, type 等等，盡量不要定義負(fù)數(shù)，如 -1。因為這樣可以加上 UNSIGNED，數(shù)值容量就會擴(kuò)大一倍。
可以的話用 TINYINT、SMALLINT 等代替 INT，盡量不使用 BIGINT，因為占的空間更小。
字符串類型的字段會比數(shù)字類型占的空間更大，所以盡量用整型代替字符串，很多場景是可以通過編碼邏輯來實現(xiàn)用整型代替的。
字符串類型長度不要隨意設(shè)置，保證滿足業(yè)務(wù)的前提下盡量小，用整型來存 IP。
單表不要有太多字段，建議在20以內(nèi)。
為能預(yù)見的字段提前預(yù)留，因為數(shù)據(jù)量越大，修改數(shù)據(jù)結(jié)構(gòu)越耗時。
2. 索引設(shè)計優(yōu)化
索引，空間換時間的優(yōu)化策略，基本上根據(jù)業(yè)務(wù)需求設(shè)計好索引，足以應(yīng)付百萬級的數(shù)據(jù)量，養(yǎng)成使用 explain 的習(xí)慣，關(guān)于 explain 也可以訪問：explain 讓你的 sql 寫的更踏實了解更多。
一個常識：索引并不是越多越好，索引是會降低數(shù)據(jù)寫入性能的。
索引字段長度盡量短，這樣能夠節(jié)省大量索引空間
取消外鍵，可交由程序來約束，性能更好。
復(fù)合索引的匹配最左列規(guī)則，索引的順序和查詢條件保持一致，盡量去除沒必要的單列索引。
值分布較少的字段（不重復(fù)的較少）不適合建索引，比如像性別這種只有兩三個值的情況字段建立索引意義不大。
需要排序的字段建議加上索引，因為索引是會排序的，能提高查詢性能。
字符串字段使用前綴索引，不使用全字段索引，可大幅減小索引空間。
3.查詢語句優(yōu)化
盡量使用短查詢替代復(fù)雜的內(nèi)聯(lián)查詢。
查詢不使用 select *，盡量查詢帶索引的字段，避免回表。
盡量使用 limit 對查詢數(shù)量進(jìn)行限制。
查詢字段盡量落在索引上，尤其是復(fù)合索引，更需要注意最左前綴匹配。
拆分大的 delete / insert 操作，一方面會鎖表，影響其他業(yè)務(wù)操作，還有一方面是 MySQL 對 sql 長度也是有限制的。
不建議使用 MySQL 的函數(shù)，計算等，可先由程序處理，從上面提的一些點(diǎn)會發(fā)現(xiàn)，能交由程序處理的盡量不要把壓力轉(zhuǎn)至數(shù)據(jù)庫上。因為多數(shù)的服務(wù)器性能瓶頸都在數(shù)據(jù)庫上。
查詢 count，性能：count(1) = count(*) > count(主鍵) > count(其他字段)。
查詢操作符能用 between 則不用 in，能用 in 則不用 or。
避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等這樣的操作符，因為這些查詢無法使用索引。
sql 盡量簡單，少用 join，不建議兩個 join 以上。
千萬級數(shù)據(jù)量
到了這個階段的數(shù)據(jù)量，數(shù)據(jù)本身已經(jīng)有很大的價值了，數(shù)據(jù)除了滿足常規(guī)業(yè)務(wù)需求外，還會有一些數(shù)據(jù)分析的需求。而這個時候數(shù)據(jù)可變動性不高，基本上不會考慮修改原有結(jié)構(gòu)，一般會考慮從分區(qū)，分表，分庫三方面做優(yōu)化：
1.分區(qū)
分區(qū)是根據(jù)一定的規(guī)則，數(shù)據(jù)庫把一個表分解成多個更小的、更容易管理的部分，是一種水平劃分。對應(yīng)用來說是完全透明的，不影響應(yīng)用的業(yè)務(wù)邏輯，即不用修改代碼。因此能存更多的數(shù)據(jù)，查詢，刪除也支持按分區(qū)來操作，從而達(dá)到優(yōu)化的目的。如果有考慮分區(qū)，可以提前做準(zhǔn)備，避免下列一些限制：
一個表最多只能有1024個分區(qū)（mysql5.6之后支持8192個分區(qū)）。但你實際操作的時候，最好不要一次性打開超過 100 個分區(qū)，因為打開分區(qū)也是有時間損耗的。
如果分區(qū)字段中有主鍵或者唯一索引列，那么所有主鍵列和唯一索引列都必須包含進(jìn)來，如果表中有主鍵或唯一索引，那么分區(qū)鍵必須是主鍵或唯一索引。對于這點(diǎn)，筆者補(bǔ)充說明，筆者本人是按 created_at 所在天進(jìn)行分區(qū)的，所以建表如下
CREATE TABLE `sms_record_hash` (
...... 省略字段定義
PRIMARY KEY (`id`,`created_at`)
......
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
/*!50100 PARTITION BY HASH (DAYOFYEAR(created_at))
PARTITIONS 366 */;
分區(qū)表中無法使用外鍵約束。
NULL值會使分區(qū)過濾無效，這樣會被放入默認(rèn)的分區(qū)里，請千萬不要讓分區(qū)字段出現(xiàn) NULL。
所有分區(qū)必須使用相同的存儲引擎。
2.分表
分表分水平分表和垂直分表。
水平分表即拆分成數(shù)據(jù)結(jié)構(gòu)相同的各個小表，如拆分成 table1, table2…，從而緩解數(shù)據(jù)庫讀寫壓力。
垂直分表即將一些字段分出去形成一個新表，各個表數(shù)據(jù)結(jié)構(gòu)不相同，可以優(yōu)化高并發(fā)下鎖表的情況。
可想而知，分表的話，程序的邏輯是需要做修改的，所以，一般是在項目初期時，預(yù)見到大數(shù)據(jù)量的情況，才會考慮分表。后期階段不建議分表，成本很大。
3.分庫
分庫一般是主從模式，一個數(shù)據(jù)庫服務(wù)器主節(jié)點(diǎn)復(fù)制到一個或多個從節(jié)點(diǎn)多個數(shù)據(jù)庫，主庫負(fù)責(zé)寫操作，從庫負(fù)責(zé)讀操作，從而達(dá)到主從分離，高可用，數(shù)據(jù)備份等優(yōu)化目的。
當(dāng)然，主從模式也會有一些缺陷，主從同步延遲，binlog 文件太大導(dǎo)致的問題等等，這里就不細(xì)講了！
————————————————
版權(quán)聲明：本文為CSDN博主「藍(lán)顏～歲月」的原創(chuàng)文章，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請附上原文出處鏈接及本聲明。
原文鏈接：
https://blog.csdn.net/weixin_40172337/article/details/114379553
鋒哥最新SpringCloud分布式電商秒殺課程發(fā)布
??????
??長按上方微信二維碼 2 秒
感謝點(diǎn)贊支持下哈 