Mysql性能優(yōu)化教程

Mysql 性能優(yōu)化教程性能優(yōu)化教程 目錄目錄 目錄 1 背景及目標(biāo) 2 Mysql 執(zhí)行優(yōu)化 2 認(rèn)識數(shù)據(jù)索引 2 為什么使用數(shù)據(jù)索引能提高效率 2 如何理解數(shù)據(jù)索引的結(jié)構(gòu) 2 優(yōu)化實(shí)戰(zhàn)范例 3 認(rèn)識影響結(jié)果集 4 影響結(jié)果集的獲取 4 影響結(jié)果集的解讀 4 常見案例及優(yōu)化思路 5 理解執(zhí)行狀態(tài) 7 常見關(guān)注重點(diǎn) 7 執(zhí)行狀態(tài)分析 8 分析流程 9 常見案例解析 11 總結(jié) 12 Mysql 運(yùn)維優(yōu)化 14 存儲(chǔ)引擎類型 14 內(nèi)存使用考量 14 性能與安全性考量 14 存儲(chǔ) 寫入壓力優(yōu)化 15 運(yùn)維監(jiān)控體系 15 Mysql 架構(gòu)優(yōu)化 17 架構(gòu)優(yōu)化目標(biāo) 17 防止單點(diǎn)隱患 17 方便系統(tǒng)擴(kuò)容 17 安全可控 成本可控 17 分布式方案 18 分庫 建立復(fù)合索引并不難 area sex lastlogin 三個(gè)字段的復(fù)合索引 如何理解 解讀 首先 忘掉 btree 將索引字段理解為一個(gè)排序序列 另外 牢記數(shù)據(jù)查詢只能使用一個(gè)索引 每個(gè)字段建立獨(dú)立索引的情況下 也只能有一條索引被使用 如果只使用 area 會(huì)怎樣 搜索會(huì)把符合 area 的結(jié)果全部找出來 然后在這里 面遍歷 選擇命中 sex 的并排序 遍歷所有 area area 數(shù)據(jù) 如果使用了 area sex 略好 仍然要遍歷所有 area area and sex sex 數(shù)據(jù) 然后在這個(gè)基礎(chǔ)上排序 Area sex lastlogin 復(fù)合索引時(shí) 切記 lastlogin 在最后 該索引基于 area sex lastlogin 三個(gè)字段合并的結(jié)果排序 該列表可以想象如下 廣州女 時(shí)間 1 廣州女 時(shí)間 2 廣州女 時(shí)間 3 廣州男 深圳女 數(shù)據(jù)庫很容易命中到 area sex 的邊界 并且基于下邊界向上追溯 30 條記錄 搞定 在索引中迅速命中所有結(jié)果 無需二次遍歷 認(rèn)識影響結(jié)果集認(rèn)識影響結(jié)果集 影響結(jié)果集的獲取影響結(jié)果集的獲取 通過 Explain 分析 SQL 查看 rows 列內(nèi)容 通過慢查詢?nèi)罩镜?Rows examined 后面的數(shù)字 影響結(jié)果集數(shù)字是查詢優(yōu)化的重要中間數(shù)字 工程師在開發(fā)和調(diào)試過程中 應(yīng)隨 時(shí)關(guān)注這一數(shù)字 影響結(jié)果集的解讀影響結(jié)果集的解讀 查詢條件與索引的關(guān)系決定影響結(jié)果集 影響結(jié)果集不是輸出結(jié)果數(shù) 不是查詢返回的記錄數(shù) 而是索引所掃描的結(jié) 果數(shù) 范例 select from user where area 廈門 and sex 女 假設(shè) 索引為 area 假設(shè) User 表中 area 廈門 的有 條 而搜索返回結(jié)果為 60233 條 影響結(jié)果集是條 索引先命中條廈門用戶 再遍歷以 sex 女 進(jìn)行篩選 操作 得到 60233 條結(jié)果 如果該 SQL 增加 limit 0 30 的后綴 查詢時(shí) 先命中 area 廈門 然 后依順序執(zhí)行 sex 女 篩選操作 直到滿足可以返回 30 條為止 所涉 及記錄數(shù)未知 除非滿足條件的結(jié)果不足 30 條 否則不會(huì)遍歷條記錄 但是如果 SQL 中涉及了排序操作 比如 order by lastlogin desc 再有 limit 0 30 時(shí) 排序需要遍歷所有 area 廈門 的記錄 而不是滿足即止 影響結(jié)果集越趨近于實(shí)際輸出或操作的目標(biāo)結(jié)果集 索引效率越高 影響結(jié)果集與查詢開銷的關(guān)系可以理解為線性相關(guān) 減少一半影響結(jié)果集 即可 提升一倍查詢效率 當(dāng)一條搜索 query 可以符合多個(gè)索引時(shí) 選擇影響結(jié)果集最 少的索引 SQL 的優(yōu)化 核心就是對結(jié)果集的優(yōu)化 認(rèn)識索引是增強(qiáng)對結(jié)果集的判斷 基于 索引的認(rèn)識 可以在編寫 SQL 的時(shí)候 對該 SQL 可能的影響結(jié)果集有預(yù)判 并做 出適當(dāng)?shù)膬?yōu)化和調(diào)整 Limit 的影響 需要斟酌對待 如果索引與查詢條件和排序條件完全命中 影響結(jié)果集就是 limit 后面的數(shù)字 start end 比如 limit 200 30 影響結(jié)果集是 230 而不是 30 如果索引只命中部分查詢條件 甚至無命中條件 在無排序條件情況下 會(huì) 在索引命中的結(jié)果集 中遍歷到滿足所有其他條件為止 比如 select from user limit 10 雖然沒用到索引 但是因?yàn)椴簧婕岸魏Y選和排序 系統(tǒng)直接 返回前 10 條結(jié)果 影響結(jié)果集依然只有 10 條 就不存在效率影響 如果搜索所包含的排序條件沒有被索引命中 則系統(tǒng)會(huì)遍歷是所有索引所命 中的結(jié)果 并且排序 例如 Select from user order by timeline desc limit 10 如果 timeline 不是索引 影響結(jié)果集是全表 就存在需要全表數(shù)據(jù)排序 這 個(gè)效率影響就巨大 再比如 Select from user where area 廈門 order by timeline desc limit 10 如果 area 是索引 而 area timeline 未建立索引 則影 響結(jié)果集是所有命中 area 廈門 的用戶 然后在影響結(jié)果集內(nèi)排序 常見案例及優(yōu)化思路常見案例及優(yōu)化思路 毫秒級優(yōu)化案例 某游戲用戶進(jìn)入后顯示最新動(dòng)態(tài) SQL 為 select from userfeed where uid uid order by timeline desc limit 20 主鍵為 uid 該 SQL 每天執(zhí)行數(shù)百 萬次之多 高峰時(shí)數(shù)據(jù)庫負(fù)載較高 通過 show processlist 顯示大量進(jìn)程處 于 Sending data 狀態(tài) 沒有慢查詢記錄 仔細(xì)分析發(fā)現(xiàn) 因存在較多高頻用 戶訪問 命中 uid uid 的影響結(jié)果集通常在幾百到幾千 在上千條影響結(jié)果 集情況下 該 SQL 查詢開銷通常在 0 01 秒左右 建立 uid timeline 復(fù)合索 引 將排序引入到索引結(jié)構(gòu)中 影響結(jié)果集就只有 limit 后面的數(shù)字 該 SQL 查詢開銷銳減至 0 001 秒 數(shù)據(jù)庫負(fù)載驟降 Innodb 鎖表案例 某游戲數(shù)據(jù)庫使用了 innodb innodb 是行級鎖 理論上很少存在鎖表情況 出現(xiàn)了一個(gè) SQL 語句 delete from tabname where xid 這個(gè) SQL 非常用 SQL 僅在特定情況下出現(xiàn) 每天出現(xiàn)頻繁度不高 一天僅 10 次左右 數(shù) 據(jù)表容量百萬級 但是這個(gè) xid 未建立索引 于是悲慘的事情發(fā)生了 當(dāng)執(zhí) 行這條 delete 的時(shí)候 真正刪除的記錄非常少 也許一到兩條 也許一條都 沒有 但是 由于這個(gè) xid 未建立索引 delete 操作時(shí)遍歷全表記錄 全表被 delete 操作鎖定 select 操作全部被 locked 由于百萬條記錄遍歷時(shí)間較長 期間大量 select 被阻塞 數(shù)據(jù)庫連接過多崩潰 這種非高發(fā)請求 操作目標(biāo)很少的 SQL 因未使用索引 連帶導(dǎo)致整個(gè)數(shù)據(jù) 庫的查詢阻塞 需要極大提高警覺 實(shí)時(shí)排名策略優(yōu)化 背景 用戶提交游戲積分 顯示實(shí)時(shí)排名 原方案 提交積分是插入記錄 略 select count from jifen where gameid gameid and fenshu fenshu 問題與挑戰(zhàn) 即便索引是 gameid fenshu 復(fù)合索引 涉及 count 操作 當(dāng)分?jǐn)?shù)較低時(shí) 影響結(jié)果集巨大 查詢效率緩慢 高峰期會(huì)導(dǎo)致連接過多 優(yōu)化思路 減少影響結(jié)果集 又要取得實(shí)時(shí)數(shù)據(jù) 單純從 SQL 上考慮 不太有方法 將游戲積分預(yù)定義分成數(shù)個(gè)積分?jǐn)帱c(diǎn) 然后分成積分區(qū)間 原始狀態(tài) 每個(gè)區(qū)間設(shè)置一個(gè)統(tǒng)計(jì)數(shù)字項(xiàng) 初始為 0 每次積分提交時(shí) 先確定該分?jǐn)?shù)屬于哪兩個(gè)區(qū)間之間 這個(gè)操作非常簡 單 因?yàn)閰^(qū)間是預(yù)定義的 而且數(shù)量很少 只需遍歷即可 找到最該分 數(shù)符合的區(qū)間 該區(qū)間的統(tǒng)計(jì)數(shù)字項(xiàng) 獨(dú)立字段 可用內(nèi)存處理 異步 回寫數(shù)據(jù)庫或文件 1 記錄該區(qū)間上邊界數(shù)字為 duandian SQL select count from jifen where gameid gameid and fenshu fenshu and fenshu duandian 如果處于第一區(qū)間 則無需 duandian 這樣因?yàn)榈谝粎^(qū)間本身也是最好的成績 影響結(jié)果集不會(huì)很 多 通過該 SQL 獲得其在該區(qū)間的名次 獲取前面區(qū)間的總數(shù)總和 該數(shù)字是直接從上述提到的區(qū)間統(tǒng)計(jì)數(shù)字獲 取 不需要進(jìn)行 count 操作 將區(qū)間內(nèi)名次 前區(qū)間的統(tǒng)計(jì)數(shù)字和 獲得 總名次 該方法關(guān)鍵在于 積分區(qū)間需要合理定義 保證積分提交成績能平均散 落在不同區(qū)間 如涉及較多其他條件 如日排行 總排行 以及其他獨(dú)立用戶去重等 請按照影響結(jié)果集思路自行發(fā)揮 Redis 方案 Redis 數(shù)據(jù)結(jié)構(gòu)包括 String list dict 和 Zset 四種 在本案例中是非 常好的替代數(shù)據(jù)庫的方案 本文檔只做簡介 不做額外擴(kuò)展 String 哈希索引 key value 結(jié)構(gòu) 主鍵查詢效率極高 不支持排序 比較查詢 List 隊(duì)列結(jié)構(gòu) 在數(shù)據(jù)異步寫入處理中可以替代 memcache Dict 數(shù)組結(jié)構(gòu) 存儲(chǔ)結(jié)構(gòu)化 序列化內(nèi)容 可以針對數(shù)組中的特定列進(jìn) 行操作 Zset 有序數(shù)組結(jié)構(gòu) 分兩個(gè)子結(jié)構(gòu) 第一是多層樹形的存儲(chǔ)結(jié)構(gòu) 第二 是每個(gè)樹形節(jié)點(diǎn)的計(jì)數(shù)器 這樣類似于前面的分段方式 可以理解為多 層分段方式 所以查詢效率更高 缺點(diǎn)是更新效率有所增加 論壇翻頁優(yōu)化 背景 常見論壇帖子頁 SQL select from post where tagid tagid order by lastpost limit start end 翻頁 索引為 tagid lastpost 復(fù)合索引 挑戰(zhàn) 超級熱帖 幾萬回帖 用戶頻頻翻到末頁 limit 25770 30 一個(gè)操作 下來 影響結(jié)果集巨大 25770 30 查詢緩慢 解決方法 只涉及上下翻頁情況 每次查詢的時(shí)候?qū)⒃擁摬樵兘Y(jié)果中最大的 lastpost 和最小的分別記 錄為 minlastpost 和 maxlastpost 上翻頁查詢?yōu)?select from post where tagid tagid and lastpost maxlastpost order by lastpost limit 30 使用這種方式 影響 結(jié)果集只有 30 條 效率極大提升 涉及跳轉(zhuǎn)到任意頁 互聯(lián)網(wǎng)上常見的一個(gè)優(yōu)化方案可以這樣表述 select from post where tagid tagid and lastpost select lastpost from post where tagid tagid order by lastpost limit start 1 order by lastpost limit 30 或者 select from post where pid in select pid from post where tagid tagid order by lastpost limit start 30 第 2 條 S 語法在新的 mysql 版本已經(jīng)不支持 新版本 mysql in 的子語句不再支持 limit 條件 但可以分解為兩條 SQL 實(shí)現(xiàn) 原理不變 不做贅述 以上思路在于 子查詢的影響結(jié)果集仍然是 start 30 但是數(shù)據(jù)獲 取的過程 Sending data 狀態(tài) 發(fā)生在索引文件中 而不是數(shù)據(jù)表文 件 這樣所需要的系統(tǒng)開銷就比前一種普通的查詢低一個(gè)數(shù)量級 而主查詢的影響結(jié)果集只有 30 條 幾乎無開銷 但是切記 這里仍 然涉及了太多的影響結(jié)果集操作 延伸問題 來自于 uchome 典型查詢 SELECT FROM uchome thread WHERE tagid 73820 ORDER BY displayorder DESC lastpost DESC LIMIT start 30 如果換用 如上方法 上翻頁代碼 SELECT FROM uchome thread WHERE tagid 73820 and lastpost maxlastpost ORDER BY displayorder DESC lastpost ASC LIMIT 0 30 這里涉及一個(gè) order by 索引可用性問題 當(dāng) order by 中 復(fù)合索引的字段 一個(gè)是 ASC 一個(gè)是 DESC 時(shí) 其排序無法在索引中完成 所以只有 上翻頁可以正確使用索引 影響結(jié)果集為 30 下翻頁無法在排序中正確 使用索引 會(huì)命中所有索引內(nèi)容然后排序 效率低下 總結(jié) 基于影響結(jié)果集的理解去優(yōu)化 不論從數(shù)據(jù)結(jié)構(gòu) 代碼 還是涉及產(chǎn)品策略上 都需要貫徹下去 涉及 limit start num 的搜索 如果 start 巨大 則影響結(jié)果集巨大 搜索效率會(huì) 非常難過低 盡量用其他方式改寫為 limit 0 num 確系無法改寫的情況下 先 從索引結(jié)構(gòu)中獲得 limit start num 或 limit start 1 再用 in 操作或基于索引序 的 limit 0 num 二次搜索 請注意 我這里永遠(yuǎn)不會(huì)講關(guān)于外鍵和 join 的優(yōu)化 因?yàn)樵谖覀兊捏w系里 這是 根本不允許的 架構(gòu)優(yōu)化部分會(huì)解釋為什么 理解執(zhí)行狀態(tài)理解執(zhí)行狀態(tài) 常見關(guān)注重點(diǎn)常見關(guān)注重點(diǎn) 慢查詢?nèi)罩?關(guān)注重點(diǎn)如下 是否鎖定 及鎖定時(shí)間 如存在鎖定 則該慢查詢通常是因鎖定因素導(dǎo)致 本身無需優(yōu)化 需解 決鎖定問題 影響結(jié)果集 如影響結(jié)果集較大 顯然是索引項(xiàng)命中存在問題 需要認(rèn)真對待 Explain 操作 索引項(xiàng)使用 不建議用 using index 做強(qiáng)制索引 如未如預(yù)期使用索引 建議重新斟酌 表結(jié)構(gòu)和索引設(shè)置 影響結(jié)果集 這里顯示的數(shù)字不一定準(zhǔn)確 結(jié)合之前提到對數(shù)據(jù)索引的理解來看 還 記得嘛 就把索引當(dāng)作有序序列來理解 反思 SQL Set profiling show profiles for query 操作 執(zhí)行開銷 注意 有問題的 SQL 如果重復(fù)執(zhí)行 可能在緩存里 這時(shí)要注意避免緩 存影響 通過這里可以看到 執(zhí)行時(shí)間超過 0 005 秒的頻繁操作 SQL 建議都分析一下 深入理解數(shù)據(jù)庫執(zhí)行的過程和開銷的分布 Show processlist 執(zhí)行狀態(tài)監(jiān)控 這是在數(shù)據(jù)庫負(fù)載波動(dòng)時(shí)經(jīng)常進(jìn)行的一項(xiàng)操作 具體參見如下 執(zhí)行狀態(tài)分析執(zhí)行狀態(tài)分析 Sleep 狀態(tài) 通常代表資源未釋放 如果是通過連接池 sleep 狀態(tài)應(yīng)該恒定在一定數(shù)量范 圍內(nèi) 實(shí)戰(zhàn)范例 因前端數(shù)據(jù)輸出時(shí) 特別是輸出到用戶終端 未及時(shí)關(guān)閉數(shù)據(jù)庫 連接 導(dǎo)致因網(wǎng)絡(luò)連接速度產(chǎn)生大量 sleep 連接 在網(wǎng)速出現(xiàn)異常時(shí) 數(shù)據(jù) 庫 too many connections 掛死 簡單解讀 數(shù)據(jù)查詢和執(zhí)行通常只需要不到 0 01 秒 而網(wǎng)絡(luò)輸出通常需要 1 秒左右甚至更長 原本數(shù)據(jù)連接在 0 01 秒即可釋放 但是因?yàn)榍岸顺绦蛭磮?zhí) 行 close 操作 直接輸出結(jié)果 那么在結(jié)果未展現(xiàn)在用戶桌面前 該數(shù)據(jù)庫 連接一直維持在 sleep 狀態(tài) Waiting for net reading from net writing to net 偶爾出現(xiàn)無妨 如大量出現(xiàn) 迅速檢查數(shù)據(jù)庫到前端的網(wǎng)絡(luò)連接狀態(tài)和流量 案例 因外掛程序 內(nèi)網(wǎng)數(shù)據(jù)庫大量讀取 內(nèi)網(wǎng)使用的百兆交換迅速爆滿 導(dǎo)致大量連接阻塞在 waiting for net 數(shù)據(jù)庫連接過多崩潰 Locked 狀態(tài) 有更新操作鎖定 通常使用 innodb 可以很好的減少 locked 狀態(tài)的產(chǎn)生 但是切記 更新操作要 正確使用索引 即便是低頻次更新操作也不能疏忽 如上影響結(jié)果集范例所 示 在 myisam 的時(shí)代 locked 是很多高并發(fā)應(yīng)用的噩夢 所以 mysql 官方也開始 傾向于推薦 innodb Copy to tmp table 索引及現(xiàn)有結(jié)構(gòu)無法涵蓋查詢條件 才會(huì)建立一個(gè)臨時(shí)表來滿足查詢要求 產(chǎn)生巨大的恐怖的 i o 壓力 很可怕的搜索語句會(huì)導(dǎo)致這樣的情況 如果是數(shù)據(jù)分析 或者半夜的周期數(shù) 據(jù)清理任務(wù) 偶爾出現(xiàn) 可以允許 頻繁出現(xiàn)務(wù)必優(yōu)化之 Copy to tmp table 通常與連表查詢有關(guān) 建議逐漸習(xí)慣不使用連表查詢 實(shí)戰(zhàn)范例 某社區(qū)數(shù)據(jù)庫阻塞 求救 經(jīng)查 其服務(wù)器存在多個(gè)數(shù)據(jù)庫應(yīng)用和網(wǎng)站 其中一個(gè)不常用的小網(wǎng)站數(shù)據(jù)庫產(chǎn)生了一個(gè)恐怖的 copy to tmp table 操 作 導(dǎo)致整個(gè)硬盤 i o 和 cpu 壓力超載 Kill 掉該操作一切恢復(fù) Sending data Sending data 并不是發(fā)送數(shù)據(jù) 別被這個(gè)名字所欺騙 這是從物理磁盤獲取 數(shù)據(jù)的進(jìn)程 如果你的影響結(jié)果集較多 那么就需要從不同的磁盤碎片去抽 取數(shù)據(jù) 偶爾出現(xiàn)該狀態(tài)連接無礙 回到上面影響結(jié)果集的問題 一般而言 如果 sending data 連接過多 通常是 某查詢的影響結(jié)果集過大 也就是查詢的索引項(xiàng)不夠優(yōu)化 前文提到影響結(jié)果集對 SQL 查詢效率線性相關(guān) 主要就是針對這個(gè)狀態(tài)的系 統(tǒng)開銷 如果出現(xiàn)大量相似的 SQL 語句出現(xiàn)在 show proesslist 列表中 并且都處于 sending data 狀態(tài) 優(yōu)化查詢索引 記住用影響結(jié)果集的思路去思考 Storing result to query cache 出現(xiàn)這種狀態(tài) 如果頻繁出現(xiàn) 使用 set profiling 分析 如果存在資源開銷在 SQL 整體開銷的比例過大 即便是非常小的開銷 看比例 則說明 query cache 碎片較多 使用 flush query cache 可即時(shí)清理 也可以做成定時(shí)任務(wù) Query cache 參數(shù)可適當(dāng)酌情設(shè)置 Freeing items 理論上這玩意不會(huì)出現(xiàn)很多 偶爾出現(xiàn)無礙 如果大量出現(xiàn) 內(nèi)存 硬盤可能已經(jīng)出現(xiàn)問題 比如硬盤滿或損壞 i o 壓力過大時(shí) 也可能出現(xiàn) Free items 執(zhí)行時(shí)間較長的情況 Sorting for 和 Sending data 類似 結(jié)果集過大 排序條件沒有索引化 需要在內(nèi)存里排 序 甚至需要?jiǎng)?chuàng)建臨時(shí)結(jié)構(gòu)排序 其他 還有很多狀態(tài) 遇到了 去查查資料 基本上我們遇到其他狀態(tài)的阻塞較少 所以不關(guān)心 分析流程分析流程 基本流程 詳細(xì)了解問題狀況 Too many connections 是常見表象 有很多種原因 索引損壞的情況在 innodb 情況下很少出現(xiàn) 如出現(xiàn)其他情況應(yīng)追溯日志和錯(cuò)誤信息 了解基本負(fù)載狀況和運(yùn)營狀況 基本運(yùn)營狀況 當(dāng)前每秒讀請求 當(dāng)前每秒寫請求 當(dāng)前在線用戶 當(dāng)前數(shù)據(jù)容量 基本負(fù)載情況 學(xué)會(huì)使用這些指令 Top Vmstat uptime iostat df Cpu 負(fù)載構(gòu)成 特別關(guān)注 i o 壓力 wa 多核負(fù)載分配 內(nèi)存占用 Swap 分區(qū)是否被侵占 如 Swap 分區(qū)被侵占 物理內(nèi)存是否較多空閑 磁盤狀態(tài) 硬盤滿和 inode 節(jié)點(diǎn)滿的情況要迅速定位和迅速處理 了解具體連接狀況 當(dāng)前連接數(shù) Netstat an grep 3306 wc l Show processlist 當(dāng)前連接分布 show processlist 前端應(yīng)用請求數(shù)據(jù)庫不要使用 root 帳號 Root 帳號比其他普通帳號多一個(gè)連接數(shù)許可 前端使用普通帳號 在 too many connections 的時(shí)候 root 帳號仍 可以登錄數(shù)據(jù)庫查詢 show processlist 記住 前端應(yīng)用程序不要設(shè)置一個(gè)不叫 root 的 root 帳號來糊弄 非 root 賬戶是骨子里的 而不是名義上的 狀態(tài)分布 不同狀態(tài)代表不同的問題 有不同的優(yōu)化目標(biāo) 參見如上范例 雷同 SQL 的分布 是否較多雷同 SQL 出現(xiàn)在同一狀態(tài) 當(dāng)前是否有較多慢查詢?nèi)罩?是否鎖定 影響結(jié)果集 頻繁度分析 寫頻繁度 如果 i o 壓力高 優(yōu)先分析寫入頻繁度 Mysqlbinlog 輸出最新 binlog 文件 編寫腳本拆分 最多寫入的數(shù)據(jù)表是哪個(gè) 最多寫入的數(shù)據(jù) SQL 是什么 是否存在基于同一主鍵的數(shù)據(jù)內(nèi)容高頻重復(fù)寫入 涉及架構(gòu)優(yōu)化部分 參見架構(gòu)優(yōu)化 緩存異步更新 讀取頻繁度 如果 cpu 資源較高 而 i o 壓力不高 優(yōu)先分析讀取頻繁度 程序中在封裝的 db 類增加抽樣日志即可 抽樣比例酌情考慮 以不 顯著影響系統(tǒng)負(fù)載壓力為底線 最多讀取的數(shù)據(jù)表是哪個(gè) 最多讀取的數(shù)據(jù) SQL 是什么 該 SQL 進(jìn)行 explain 和 set profiling 判定 注意判定時(shí)需要避免 query cache 影響 比如 在這個(gè) SQL 末尾增加一個(gè)條件子句 and 1 1 就可 以避免從 query cache 中獲取數(shù)據(jù) 而得到真實(shí)的執(zhí)行狀態(tài) 分析 是否存在同一個(gè)查詢短期內(nèi)頻繁出現(xiàn)的情況 涉及前端緩存優(yōu)化 抓大放小 解決顯著問題 不苛求解決所有優(yōu)化問題 但是應(yīng)以保證線上服務(wù)穩(wěn)定可靠為目標(biāo) 解決與評估要同時(shí)進(jìn)行 新的策略或解決方案務(wù)必經(jīng)過評估后上線 常見案例解析常見案例解析 現(xiàn)象 服務(wù)器出現(xiàn) too many connections 阻塞 入手點(diǎn) 查看服務(wù)器狀態(tài) cpu 占用 內(nèi)存占用 硬盤占用 硬盤 i o 壓力 查看網(wǎng)絡(luò)流量狀態(tài) mysql 與應(yīng)用服務(wù)器的輸入輸出狀況 通過 Show processlist 查看當(dāng)前運(yùn)行清單 注意事項(xiàng) 日常應(yīng)用程序連接數(shù)據(jù)庫不要使用 root 賬戶 保證故障時(shí)可 以通過 root 進(jìn)入數(shù)據(jù)庫查看 show processlist 狀態(tài)分析 參見如上執(zhí)行狀態(tài)清單 根據(jù)連接狀態(tài)的分布去確定原因 緊急恢復(fù) 在確定故障原因后 應(yīng)通過 kill 掉阻塞進(jìn)程的方式 立即恢復(fù)數(shù)據(jù)庫 善后處理 以下針對常見問題簡單解讀 Sleep 連接過多導(dǎo)致 應(yīng)用端及時(shí)釋放連接 排查關(guān)聯(lián)因素 Locked 連接過多 如源于 myisam 表級鎖 更 innodb 引擎 如源于更新操作使 用了不恰當(dāng)?shù)乃饕蛭词褂盟饕?改寫更新操作 SQL 或建立恰當(dāng)索引 Sending data 連接過多 用影響結(jié)果集的思路優(yōu)化 SQL 查詢 優(yōu)化表索引結(jié) 構(gòu) Free items 連接過多 i o 壓力過大 或硬盤故障 Waiting for net writing to net 連接過多 mysql 與應(yīng)用服務(wù)器連接阻塞 其他仍參見如上執(zhí)行狀態(tài)清單所示分析 如涉及不十分嚴(yán)格安全要求的數(shù)據(jù)內(nèi)容 可用定期腳本跟蹤請求進(jìn)程 并 kill 掉僵死進(jìn)程 如數(shù)據(jù)安全要求較嚴(yán)格 則不能如此進(jìn)行 現(xiàn)象 數(shù)據(jù)庫負(fù)載過高 響應(yīng)緩慢 入手點(diǎn) 查看 cpu 狀態(tài) 服務(wù)器負(fù)載構(gòu)成 分支 1 i o 占用過高 步驟 1 檢查內(nèi)存是否占用 swap 分區(qū) 排除因內(nèi)存不足導(dǎo)致的 i o 開銷 步驟 2 通過 iostat 指令分析 i o 是否集中于數(shù)據(jù)庫硬盤 是否是寫入度較高 步驟 3 如果壓力來自于寫 使用 mysqlbinlog 解開最新的 binlog 文件 步驟 4 編寫日志分析腳本或 grep 指令 分析每秒寫入頻度和寫入內(nèi)容 寫入頻度不高 則說明 i o 壓力另有原因或數(shù)據(jù)庫配置不合理 步驟 5 編寫日志分析腳本或 grep 指令 分析寫入的數(shù)據(jù)表構(gòu)成 和寫入的 目標(biāo)構(gòu)成 步驟 6 編寫日志分析腳本 分析是否存在同一主鍵的重復(fù)寫入 比如出現(xiàn) 大量 update post set views views 1 where tagid 的操作 假設(shè)在一段時(shí)間 內(nèi)出現(xiàn)了 2 萬次 而其中不同的 tagid 有 1 萬次 那么就是有 50 的請求是 重復(fù) update 請求 有可以通過異步更新合并的空間 提示一下 以上所提及的日志分析腳本編寫 正常情況下不應(yīng)超過 1 個(gè)小時(shí) 而對系統(tǒng)負(fù)載分析所提供的數(shù)據(jù)支持價(jià)值是巨大的 對性能優(yōu)化方案的選擇 是非常有意義的 如果您認(rèn)為這項(xiàng)工作是繁冗而且復(fù)雜的工作 那么一定是 在分析思路和目標(biāo)把握上出現(xiàn)了偏差 分支 2 i o 占用不高 CPU 占用過高 步驟 1 查看慢查詢?nèi)罩?步驟 2 不斷刷新查看 Show processlist 清單 并把握可能頻繁出現(xiàn)的處于 Sending data 狀態(tài)的 SQL 步驟 3 記錄前端執(zhí)行 SQL 于前端應(yīng)用程序執(zhí)行查詢的封裝對象內(nèi) 設(shè)置隨機(jī)采樣 記錄前端執(zhí)行 的 SQL 保證有一定的樣本規(guī)模 并且不會(huì)帶來前端 i o 負(fù)載的激增 基于采樣率和記錄頻率 獲得每秒讀請求次數(shù)數(shù)據(jù)指標(biāo) 編寫日志分析腳本 分析采樣的 SQL 構(gòu)成 所操作的數(shù)據(jù)表 所操作的 主鍵 對頻繁重復(fù)讀取的 SQL 完全一致的 SQL 進(jìn)行判定 是否數(shù)據(jù)存在頻繁 變動(dòng) 是否需要實(shí)時(shí)展現(xiàn)最新數(shù)據(jù) 如有可能 緩存化 并預(yù)估緩存命 中率 對頻繁讀取但不重復(fù)的 SQL 結(jié)構(gòu)一致 但條件中的數(shù)據(jù)不一致 SQL 進(jìn) 行判定 是否索引足夠優(yōu)化 影響結(jié)果集與輸出結(jié)果是否足夠接近 步驟 4 將導(dǎo)致慢查詢的 SQL 或頻繁出現(xiàn)于 show processlist 狀態(tài)的 SQL 或 采樣記錄的頻繁度 SQL 進(jìn)行分析 按照影響結(jié)果集的思路和索引理解來優(yōu)化 步驟 5 對如上難以界定問題的 SQL 進(jìn)行 set profiling 分析 步驟 6 優(yōu)化后分析繼續(xù)采樣跟蹤分析 并跟蹤比對結(jié)果 善后處理 日常跟蹤腳本 不斷記錄一些狀態(tài)信息 保證每個(gè)時(shí)間節(jié)點(diǎn)都能回溯 確保隨時(shí)能了解服務(wù)器的請求頻次 讀寫請求的分布 記錄一些未造成致命影響的隱患點(diǎn) 可暫不解決 但需要記錄 如確系服務(wù)器請求頻次過高 可基于負(fù)載分布決定硬件擴(kuò)容方案 比如 i o 壓力過高可考慮固態(tài)硬盤 內(nèi)存占用 swap 可考慮增加內(nèi)容容量等 用盡可 能少的投入實(shí)現(xiàn)最好的負(fù)載支撐能力 而不是簡單的買更多服務(wù)器 總結(jié)總結(jié) 要學(xué)會(huì)怎樣分析問題 而不是單純拍腦袋優(yōu)化 慢查詢只是最基礎(chǔ)的東西 要學(xué)會(huì)優(yōu)化 0 01 秒的查詢請求 當(dāng)發(fā)生連接阻塞時(shí) 不同狀態(tài)的阻塞有不同的原因 要找到原因 如果不對癥下 藥 就會(huì)南轅北轍 范例 如果本身系統(tǒng)內(nèi)存已經(jīng)超載 已經(jīng)使用到了 swap 而還在考慮加大緩 存來優(yōu)化查詢 那就是自尋死路了 影響結(jié)果集是非常重要的中間數(shù)據(jù)和優(yōu)化指標(biāo) 學(xué)會(huì)理解這一概念 理論上影響 結(jié)果集與查詢效率呈現(xiàn)非常緊密的線性相關(guān) 監(jiān)測與跟蹤要經(jīng)常做 而不是出問題才做 讀取頻繁度抽樣監(jiān)測 全監(jiān)測不要搞 i o 嚇?biāo)廊?按照一個(gè)抽樣比例抽樣即可 針對抽樣中發(fā)現(xiàn)的問題 可以按照特定 SQL 在特定時(shí)間內(nèi)監(jiān)測一段全查 詢記錄 但仍要考慮 i o 影響 寫入頻繁度監(jiān)測 基于 binlog 解開即可 可定時(shí)或不定時(shí)分析 微慢查詢抽樣監(jiān)測 高并發(fā)情況下 查詢請求時(shí)間超過 0 01 秒甚至 0 005 秒的 建議酌情抽 樣記錄 連接數(shù)預(yù)警監(jiān)測 連接數(shù)超過特定閾值的情況下 雖然數(shù)據(jù)庫沒有崩潰 建議記錄相關(guān)連 接狀態(tài) 學(xué)會(huì)通過數(shù)據(jù)和監(jiān)控發(fā)現(xiàn)問題 分析問題 而后解決問題順理成章 特別是要學(xué) 會(huì)在日常監(jiān)控中發(fā)現(xiàn)隱患 而不是問題爆發(fā)了才去處理和解決 Mysql 運(yùn)維優(yōu)化運(yùn)維優(yōu)化 存儲(chǔ)引擎類型存儲(chǔ)引擎類型 Myisam 速度快 響應(yīng)快 表級鎖是致命問題 Innodb 目前主流存儲(chǔ)引擎 行級鎖 務(wù)必注意影響結(jié)果集的定義是什么 行級鎖會(huì)帶來更新的額外開銷 但是通常情況下是值得的 事務(wù)提交 對 i o 效率提升的考慮 對安全性的考慮 HEAP 內(nèi)存引擎 頻繁更新和海量讀取情況下仍會(huì)存在鎖定狀況 內(nèi)存使用考量內(nèi)存使用考量 理論上 內(nèi)存越大 越多數(shù)據(jù)讀取發(fā)生在內(nèi)存 效率越高 Query cache 的使用 如果前端請求重復(fù)度不高 或者應(yīng)用層已經(jīng)充分緩存重復(fù)請求 query cache 不必設(shè)置很大 甚至可以不設(shè)置 如果前端請求重復(fù)度較高 無應(yīng)用層緩存 query cache 是一個(gè)很好的偷懶選 擇 對于中等以下規(guī)模數(shù)據(jù)庫應(yīng)用 偷懶不是一個(gè)壞選擇 如果確認(rèn)使用 query cache 記得定時(shí)清理碎片 flush query cache 要考慮到現(xiàn)實(shí)的硬件資源和瓶頸分布 學(xué)會(huì)理解熱點(diǎn)數(shù)據(jù) 并將熱點(diǎn)數(shù)據(jù)盡可能內(nèi)存化 所謂熱點(diǎn)數(shù)據(jù) 就是最多被訪問的數(shù)據(jù) 通常數(shù)據(jù)庫訪問是不平均的 少數(shù)數(shù)據(jù)被頻繁讀寫 而更多數(shù)據(jù)鮮有讀寫 學(xué)會(huì)制定不同的熱點(diǎn)數(shù)據(jù)規(guī)則 并測算指標(biāo) 熱點(diǎn)數(shù)據(jù)規(guī)模 理論上 熱點(diǎn)數(shù)據(jù)越少越好 這樣可以更好的滿足業(yè)務(wù) 的增長趨勢 響應(yīng)滿足度 對響應(yīng)的滿足率越高越好 比如依據(jù)最后更新時(shí)間 總訪問量 回訪次數(shù)等指標(biāo)定義熱點(diǎn)數(shù)據(jù) 并 測算不同定義模式下的熱點(diǎn)數(shù)據(jù)規(guī)模 性能與安全性考量性能與安全性考量 數(shù)據(jù)提交方式 innodb flush log at trx commit 1 每次自動(dòng)提交 安全性高 i o 壓力大 innodb flush log at trx commit 2 每秒自動(dòng)提交 安全性略有影響 i o 承 載強(qiáng) 日志同步 Sync binlog 1 每條自動(dòng)更新 安全性高 i o 壓力大 Sync binlog 0 根據(jù)緩存設(shè)置情況自動(dòng)更新 存在丟失數(shù)據(jù)和同步延遲風(fēng)險(xiǎn) i o 承載力強(qiáng) 個(gè)人建議保存 binlog 日志文件 便于追溯 更新操作和系統(tǒng)恢復(fù) 如對日志文件的 i o 壓力有擔(dān)心 在內(nèi)存寬裕的情況下 可考慮將 binlog 寫 入到諸如 dev shm 這樣的內(nèi)存映射分區(qū) 并定時(shí)將舊有的 binlog 轉(zhuǎn)移到物 理硬盤 性能與安全本身存在相悖的情況 需要在業(yè)務(wù)訴求層面決定取舍 學(xué)會(huì)區(qū)分什么場合側(cè)重性能 什么場合側(cè)重安全 學(xué)會(huì)將不同安全等級的數(shù)據(jù)庫用不同策略管理 存儲(chǔ)存儲(chǔ) 寫入寫入壓力優(yōu)化壓力優(yōu)化 順序讀寫性能遠(yuǎn)高于隨機(jī)讀寫 將順序?qū)憯?shù)據(jù)和隨機(jī)讀寫數(shù)據(jù)分成不同的物理磁盤進(jìn)行 有助于 i o 壓力的疏解 數(shù)據(jù)庫文件涉及索引等內(nèi)容 寫入是隨即寫 binlog 文件是順序?qū)?淘寶數(shù)據(jù)庫存儲(chǔ)優(yōu)化是這樣處理的 部分安全要求不高的寫入操作可以用 dev shm 分區(qū)存儲(chǔ) 簡單變成內(nèi)存寫 多塊物理硬盤做 raid10 可以提升寫入能力 關(guān)鍵存儲(chǔ)設(shè)備優(yōu)化 善于比對不同存儲(chǔ)介質(zhì)的壓力測試數(shù)據(jù) 例如 fusion io 在新浪和淘寶都有較多使用 涉及必須存儲(chǔ)較為龐大的數(shù)據(jù)量時(shí) 壓縮存儲(chǔ) 可以通過增加 cpu 開銷 壓縮算法 減少 i o 壓力 前提是你確 認(rèn) cpu 相對空閑而 i o 壓力很大 新浪微博就是壓縮存儲(chǔ)的典范 通過 md5 去重存儲(chǔ) 案例是 QQ 的文件共享 以及 dropbox 這樣的共享服務(wù) 如果你上傳的是一個(gè)別人已有的文件 計(jì)算 md5 后 直接通過 md5 定位到原 有文件 這樣可以極大減少存儲(chǔ)量 涉及文件共享 頭像共享 相冊等應(yīng)用 通過這種方法可以減少超過 70 的存儲(chǔ)規(guī)模 對硬件資源的節(jié)省是相當(dāng)巨大的 缺點(diǎn)是 刪除文件需要甄別該 md5 是否有其他人使用 去重存儲(chǔ) 用戶量越 多 上傳文件越多 效率越高 文件盡量不要存儲(chǔ)到數(shù)據(jù)庫內(nèi) 盡量使用獨(dú)立的文件系統(tǒng)存儲(chǔ) 該話題不展 開 運(yùn)維監(jiān)控體系運(yùn)維監(jiān)控體系 系統(tǒng)監(jiān)控 服務(wù)器資源監(jiān)控 Cpu 內(nèi)存 硬盤空間 i o 壓力 設(shè)置閾值報(bào)警 服務(wù)器流量監(jiān)控 外網(wǎng)流量 內(nèi)網(wǎng)流量 設(shè)置閾值報(bào)警 連接狀態(tài)監(jiān)控 Show processlist 設(shè)置閾值 每分鐘監(jiān)測 超過閾值記錄 應(yīng)用監(jiān)控 慢查詢監(jiān)控 慢查詢?nèi)罩?如果存在多臺(tái)數(shù)據(jù)庫服務(wù)器 應(yīng)有匯總查閱機(jī)制 請求錯(cuò)誤監(jiān)控 高頻繁應(yīng)用中 會(huì)出現(xiàn)偶發(fā)性數(shù)據(jù)庫連接錯(cuò)誤或執(zhí)行錯(cuò)誤 將錯(cuò)誤信息 記錄到日志 查看每日的比例變化 偶發(fā)性錯(cuò)誤 如果數(shù)量極少 可以不用處理 但是需時(shí)常監(jiān)控其趨勢 會(huì)存在惡意輸入內(nèi)容 輸入邊界限定缺乏導(dǎo)致執(zhí)行出錯(cuò) 需基于此防止 惡意入侵探測行為 微慢查詢監(jiān)控 高并發(fā)環(huán)境里 超過 0 01 秒的查詢請求都應(yīng)該關(guān)注一下 頻繁度監(jiān)控 寫操作 基于 binlog 定期分析 讀操作 在前端 db 封裝代碼中增加抽樣日志 并輸出執(zhí)行時(shí)間 分析請求頻繁度是開發(fā)架構(gòu) 進(jìn)一步優(yōu)化的基礎(chǔ) 最好的優(yōu)化就是減少請求次數(shù) 總結(jié) 監(jiān)控與數(shù)據(jù)分析是一切優(yōu)化的基礎(chǔ) 沒有運(yùn)營數(shù)據(jù)監(jiān)測就不要妄談優(yōu)化 監(jiān)控要注意不要產(chǎn)生太多額外的負(fù)載 不要因監(jiān)控帶來太多額外系統(tǒng)開銷 Mysql 架構(gòu)優(yōu)化架構(gòu)優(yōu)化 架構(gòu)優(yōu)化目標(biāo)架構(gòu)優(yōu)化目標(biāo) 防止單點(diǎn)隱患防止單點(diǎn)隱患 所謂單點(diǎn)隱患 就是某臺(tái)設(shè)備出現(xiàn)故障 會(huì)導(dǎo)致整體系統(tǒng)的不可用 這個(gè)設(shè)備就 是單點(diǎn)隱患 理解連帶效應(yīng) 所謂連帶效應(yīng) 就是一種問題會(huì)引發(fā)另一種故障 舉例而言 memcache mysql 是一種常見緩存組合 在前端壓力很大時(shí) 如果 memcache 崩潰 理論上數(shù)據(jù)會(huì)通過 mysql 讀取 不存在系統(tǒng)不可用情況 但是 mysql 無法對抗如 此大的壓力沖擊 會(huì)因此連帶崩潰 因 A 系統(tǒng)問題導(dǎo)致 B 系統(tǒng)崩潰的連帶問題 在運(yùn)維過程中會(huì)頻繁出現(xiàn) 實(shí)戰(zhàn)范例 在 mysql 連接不及時(shí)釋放的應(yīng)用環(huán)境里 當(dāng)網(wǎng)絡(luò)環(huán)境異常 同機(jī) 房友鄰服務(wù)器遭受拒絕服務(wù)攻擊 出口阻塞 網(wǎng)絡(luò)延遲加劇 空連接數(shù)急劇 增加 導(dǎo)致數(shù)據(jù)庫連接過多崩潰 實(shí)戰(zhàn)范例 2 前端代碼 通常我們封裝 mysql connect 和 memcache connect 二者的順序不同 會(huì)產(chǎn)生不同的連帶效應(yīng) 如果 mysql connect 在前 那么 一旦 memcache 連接阻塞 會(huì)連帶 mysql 空連接過多崩潰 連帶效應(yīng)是常見的系統(tǒng)崩潰 日常分析崩潰原因的時(shí)候需要認(rèn)真考慮連帶效 應(yīng)的影響 頭疼醫(yī)頭 腳疼醫(yī)腳是不行的 方便系統(tǒng)擴(kuò)容方便系統(tǒng)擴(kuò)容 數(shù)據(jù)容量增加后 要考慮能夠?qū)?shù)據(jù)分布到不同的服務(wù)器上 請求壓力增加時(shí) 要考慮將請求壓力分布到不同服務(wù)器上 擴(kuò)容設(shè)計(jì)時(shí)需要考慮防止單點(diǎn)隱患 安全可控 成本可控安全可控 成本可控 數(shù)據(jù)安全 業(yè)務(wù)安全 人力資源成本 帶寬流量成本 硬件成本 成本與流量的關(guān)系曲線應(yīng)低于線性增長 流量為橫軸 成本為縱軸 規(guī)模優(yōu)勢 本教程僅就與數(shù)據(jù)庫有關(guān)部分討論 與數(shù)據(jù)庫無關(guān)部門請自行參閱其他學(xué)習(xí)資料 分布式方案分布式方案 分庫分庫 展示程序需要顯示發(fā)送者姓名 此時(shí)通常會(huì)在 message 表中增加字段 fromusername 甚至有的會(huì)增加 fromusersex 從而無需連表查詢直接輸出 信息的發(fā)送者姓名和性別 這就是一種簡單的 為了避免連表查詢而使用的 冗余字段設(shè)計(jì) 基于查詢的冗余設(shè)計(jì) 涉及分表操作后 一些常見的索引查詢可能需要跨表 帶來不必要的麻煩 確認(rèn)查詢請求遠(yuǎn)大于寫入請求時(shí) 應(yīng)設(shè)置便于查詢項(xiàng)的冗余表 冗余表要點(diǎn) 數(shù)據(jù)一致性 簡單說 同增 同刪 同更新 可以做全冗余 或者只做主鍵關(guān)聯(lián)的冗余 比如通過用戶名查詢 uid 再 基于 uid 查詢源表 實(shí)戰(zhàn)范例 1 用戶分表 將用戶庫分成若干數(shù)據(jù)表 基于用戶名的查詢和基于 uid 的查詢都是高并發(fā)請求 用戶分表基于 uid 分成數(shù)據(jù)表 同時(shí)基于用戶名做對應(yīng)冗余表 如果允許多方式登陸 可以有如下設(shè)計(jì)方法 uid passwd 用戶信息等等 主數(shù)據(jù)表 基于 uid 分表 ukey ukeytype uid 基于 ukey 分表 便于用戶登陸的查詢 分解成 如下兩個(gè) SQL select uid from ulist key 13 where ukey username and ukeytype login select from ulist uid 23 where uid uid and passwd passwd ukeytype 定義用戶的登陸依據(jù) 比如用戶名 手機(jī)號 郵件地址 網(wǎng)站昵稱等 Ukey ukeytype 必須唯一 此種方式需要登陸密碼統(tǒng)一 對于第三方 connect 接入模式 可以 通過引申額外字段完成 實(shí)戰(zhàn)范例 2 用戶游戲積分排名 表結(jié)構(gòu) uid gameid score 參見前文實(shí)時(shí)積分排行 表內(nèi)容巨大 需要 拆表 需求 1 基于游戲 id 查詢積分排行 需求 2 基于用戶 id 查詢游戲積分記錄 解決方案 建立完全相同的兩套表結(jié)構(gòu) 其一以 uid 為拆表主鍵 其二 以 gameid 為拆表主鍵 用戶提交積分時(shí) 向兩個(gè)數(shù)據(jù)結(jié)構(gòu)同時(shí)提交 實(shí)戰(zhàn)范例 3 全冗余查詢結(jié)構(gòu) 主信息表僅包括 主鍵及備注 memo 字段 text 類型 只支持主鍵查詢 可以基于主鍵拆表 所以需要展現(xiàn)和存儲(chǔ)的內(nèi)容均在 memo 字段重體現(xiàn) 對每一個(gè)查詢條件 建立查詢?nèi)哂啾?以查詢條件字段為主鍵 以主信 息表主鍵 id 為內(nèi)容 日常查詢只基于查詢?nèi)哂啾?然后通過 in 的方式從主信息表獲得內(nèi)容 優(yōu)點(diǎn)是結(jié)構(gòu)擴(kuò)展非常方便 只需要擴(kuò)展新的查詢信息表即可 核心思路 是 只有查詢才需要獨(dú)立的索引結(jié)構(gòu) 展現(xiàn)無需獨(dú)立字段 缺點(diǎn)是只適合于相對固定的查詢架構(gòu) 對于更加靈活的組合查詢束手無 策 基于統(tǒng)計(jì)的冗余結(jié)構(gòu) 為了減少會(huì)涉及大規(guī)模影響結(jié)果集的表數(shù)據(jù)操作 比如 count sum 操作 應(yīng) 將一些統(tǒng)計(jì)類數(shù)據(jù)通過冗余數(shù)據(jù)結(jié)構(gòu)保存 冗余數(shù)據(jù)結(jié)構(gòu)可能以字段方式存在 也可能以獨(dú)立數(shù)據(jù)表結(jié)構(gòu)存在 但是都 應(yīng)能通過源數(shù)據(jù)表恢復(fù) 實(shí)戰(zhàn)范例 論壇板塊的發(fā)帖量 回帖量 每日新增數(shù)據(jù)等 網(wǎng)站每日新增用戶數(shù)等 參見 Discuz 論壇系統(tǒng)數(shù)據(jù)結(jié)構(gòu) 有較多相關(guān)結(jié)構(gòu) 參見前文分段積分結(jié)構(gòu) 是典型用于統(tǒng)計(jì)的冗余結(jié)構(gòu) 后臺(tái)可以通過源數(shù)據(jù)表更新該數(shù)字 Redis 的 Zset 類型可以理解為存在一種冗余統(tǒng)計(jì)結(jié)構(gòu) 歷史數(shù)據(jù)表 歷史數(shù)據(jù)表對應(yīng)于熱點(diǎn)數(shù)據(jù)表 將需求較少又不能丟棄的數(shù)據(jù)存入 僅在少 數(shù)情況下被訪問 主從架構(gòu)主從架構(gòu) 基本認(rèn)識 讀寫分離對負(fù)載的減輕遠(yuǎn)遠(yuǎn)不如分庫分表來的直接 寫壓力會(huì)傳遞給從表 只讀從庫一樣有寫壓力 一樣會(huì)產(chǎn)生讀寫鎖 一主多從結(jié)構(gòu)下 主庫是單點(diǎn)隱患 很難解決 如主庫當(dāng)機(jī) 從庫可以響應(yīng)讀寫 但是無法自動(dòng)擔(dān)當(dāng)主庫的分發(fā)功能 主從延遲也是重大問題 一旦有較大寫入問題 如表結(jié)構(gòu)更新 主從會(huì)產(chǎn)生巨大 延遲 應(yīng)用場景 在線熱備 異地分布 寫分布 讀統(tǒng)一 仍然困難重重 受限于網(wǎng)絡(luò)環(huán)境問題巨多 自動(dòng)障礙轉(zhuǎn)移 主崩潰 從自動(dòng)接管 個(gè)人建議 負(fù)載均衡主要使用分庫方案 主從主要用于熱備和障礙轉(zhuǎn)移 潛在優(yōu)化點(diǎn) 為了減少寫壓力 有些人建議主不建索引提升 i o 性能 從建立索引滿足查詢要 求 個(gè)人認(rèn)為這樣維護(hù)較為麻煩 而且從本身會(huì)繼承主的 i o 壓力 因此優(yōu)化價(jià) 值有限 該思路特此分享 不做推薦 故障轉(zhuǎn)移處理故障轉(zhuǎn)移處理 要點(diǎn) 程序與數(shù)據(jù)庫的連接 基于虛地址而非真實(shí) ip 由負(fù)載均衡系統(tǒng)監(jiān)控 保持主從結(jié)構(gòu)的簡單化 否則很難做到故障點(diǎn)摘除 思考方式 遍歷對服務(wù)器集群的任何一臺(tái)服務(wù)器 前端 web 中間件 監(jiān)控 緩存 db 等等 假設(shè)該服務(wù)器出現(xiàn)故障 系統(tǒng)是否會(huì)出現(xiàn)異常 用戶訪問是否會(huì)出現(xiàn)異常 目標(biāo) 任意一臺(tái)服務(wù)器崩潰 負(fù)載和數(shù)據(jù)操作均會(huì)很短時(shí)間內(nèi)自動(dòng)轉(zhuǎn)移到其他服 務(wù)器 不會(huì)影響業(yè)務(wù)的正常進(jìn)行 不會(huì)造成惡性的數(shù)據(jù)丟失 哪些是可以丟失的 哪些是不能丟失的 緩存方案緩存方案 緩存結(jié)合數(shù)據(jù)庫的讀取緩存結(jié)合數(shù)據(jù)庫的讀取 Memcached 是最常用的緩存系統(tǒng) Mysql 最新版本已經(jīng)開始支持 memcache 插件 但據(jù)牛人分析 尚不成熟 暫不推薦 數(shù)據(jù)讀取 并不是所有數(shù)據(jù)都適合被緩存 也并不是進(jìn)入了緩存就意味著效率提升 命中率是第一要評估的數(shù)據(jù) 如何評估進(jìn)入緩存的數(shù)據(jù)規(guī)模 以及命中率優(yōu)化 是非常需要細(xì)心分析的 實(shí)景分析 前端請求先連接緩存 緩存未命中連接數(shù)據(jù)庫 進(jìn)行查詢 未命 中狀態(tài)比單純連接數(shù)據(jù)庫查詢多了一次連接和查詢的操作 如果緩存命中率 很低 則這個(gè)額外的操作非但不能提高查詢效率 反而為系統(tǒng)帶來了額外的 負(fù)載和復(fù)雜性 得不償失 相關(guān)評