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