|
關(guān)于我們
 書單推薦 新書推薦 |
MySQL DBA工作筆記：數(shù)據(jù)庫管理、架構(gòu)優(yōu)化與運(yùn)維開發(fā) 
定 價(jià)：99 元
9 7 2 8 6 7 0 1 3 1 4 3 7 
本書通過大量真實(shí)的股價(jià)走勢進(jìn)行實(shí)戰(zhàn)分析，向讀者介紹了各種股市行情下的個(gè)股走勢分析技巧。
全書共9 章，共計(jì)103 項(xiàng)知識(shí)點(diǎn)，內(nèi)容包括了解盤口基礎(chǔ)知識(shí)、了解盤口要素、解密盤中買賣盤、掌握大盤盤口、認(rèn)識(shí)K 線及形態(tài)、看懂盤面量價(jià)關(guān)系、解讀盤口的線與勢、利用指標(biāo)看盤以及分析主力意圖。通過對(duì)本書的學(xué)習(xí)，讀者可以快速掌握盤口信息，分析盤口數(shù)據(jù)，準(zhǔn)確把握市場行情，及時(shí)做出正確的投資決策，獲得收益。
無論是新進(jìn)股民，還是股票投資愛好者，相信都可以在本書中學(xué)到看盤的基礎(chǔ)知識(shí)以及實(shí)戰(zhàn)技法，以便在之后的投資實(shí)戰(zhàn)中從容應(yīng)對(duì)股市變化。
章 MySQL 發(fā)展和技*選型 1.1 如何看待MySQL..............................................................................................................1 1.1.1 MySQL 始出...........................................................................................................1 1.1.2 MySQL 學(xué)習(xí)周期和難度........................................................................................1 1.1.3 解讀DB-Engines 的正確姿勢................................................................................2 1.1.4 MySQL 主要的一些分支........................................................................................3 1.1.5 如何看待MySQL 的技*發(fā)展...............................................................................3 1.2 如何看待MySQL 存儲(chǔ)引擎..............................................................................................5 1.2.1 InnoDB 發(fā)展時(shí)間線...............................................................................................6 1.2.2 存儲(chǔ)引擎之戰(zhàn).........................................................................................................7 1.2.3 存儲(chǔ)引擎矩陣圖.....................................................................................................7 1.3 MySQL 軟件和版本選型...................................................................................................8 1.3.1 選擇官方版的原因.................................................................................................9 1.3.2 Percona 分支的優(yōu)劣對(duì)比........................................................................................9 1.3.3 選擇MariaDB 的原因..........................................................................................10 1.3.4 MySQL 版本選型.................................................................................................10 1.3.5 分支選擇的參考和標(biāo)準(zhǔn)........................................................................................11 1.3.6 初步結(jié)論...............................................................................................................13 1.4 MySQL 常用工具選擇和建議.........................................................................................13 1.4.1 運(yùn)維管理類工具...................................................................................................14 1. 主流運(yùn)維管理工具...........................................................................................14 2. 數(shù)據(jù)備份恢復(fù)工具...........................................................................................14 3. MySQL 審計(jì)*件............................................................................................15 1.4.2 應(yīng)用工具...............................................................................................................15 1. 客戶端工具......................................................................................................15 2. 數(shù)據(jù)庫版本管理工具.......................................................................................15 1.4.3 監(jiān)控管理類工具...................................................................................................16 1. 作系統(tǒng)監(jiān)控..................................................................................................16 2. 性能監(jiān)控工具..................................................................................................16 1.4.4 診斷和優(yōu)化工具...................................................................................................16 1. 診斷工具..........................................................................................................16 2. 性能測試工具..................................................................................................18 3. 慢日志分析工具..............................................................................................18 1.4.5 初步結(jié)論...............................................................................................................18 1.5 MySQL 安裝....................................................................................................................19 1.5.1 常見的三種安裝方式............................................................................................19 1.5.2 MySQL 安裝規(guī)范.................................................................................................20 1.5.3 MySQL 5.7 安裝部署實(shí)踐與總結(jié)........................................................................20 1.5.4 搭建從庫...............................................................................................................23 案例1-1：MySQL 頻繁停庫的問題分析........................................................................25 2 章理解MySQL 體系結(jié)構(gòu) 2.1 通過文件來了解MySQL 體系結(jié)構(gòu).................................................................................29 2.1.1 從例子來理解MySQL 存儲(chǔ)結(jié)構(gòu).........................................................................32 2.1.2 慢日志診斷...........................................................................................................32 案例2-1：MySQL 日志故障的處理和分析....................................................................35 2.2 玩轉(zhuǎn)MySQL 數(shù)據(jù)字典....................................................................................................38 2.2.1 MySQL 巡檢模塊：Sys Sche* 的設(shè)計(jì)..............................................................39 2.2.2 解惑：MySQL 關(guān)于數(shù)據(jù)字典的一個(gè)疑問...........................................................47 2.3 InnoDB 體系結(jié)構(gòu)............................................................................................................50 2.3.1 InnoDB 體系結(jié)構(gòu)圖.............................................................................................51 2.3.2 查看InnoDB 狀態(tài)的小技巧.................................................................................52 2.3.3 InnoDB 的多線程技*..........................................................................................53 2.3.4 InnoDB 的緩存池管理技*..................................................................................55 2.3.5 InnoDB 中的臟頁管理..........................................................................................58 2.3.6 InnoDB 的日志管理.............................................................................................60 2.3.7 InnoDB 中的檢查機(jī)制..........................................................................................62 2.3.8 MySQL 是如何保證數(shù)據(jù)完整性的......................................................................63 2.4 換個(gè)角度看MySQL.........................................................................................................66 2.4.1 MySQL 里的一些極限值......................................................................................66 案例2-2：關(guān)于MySQL 中的一些極限值的初步驗(yàn)證糾錯(cuò)............................................. 67 2.4.2 mysql. service 啟動(dòng)腳本淺析................................................................................68 2.4.3 MySQL 待改進(jìn)的一些問題..................................................................................71 2.5 MySQL 參數(shù)解析............................................................................................................72 2.5.1 MySQL 參數(shù)變化分析..........................................................................................73 2.5.2 MySQL 5.7 參數(shù)解析............................................................................................74 3 章 MySQL 基礎(chǔ)運(yùn)維面面觀 3.1 環(huán)境部署和構(gòu)建..............................................................................................................76 3.1.1 源碼安裝MySQL.................................................................................................76 3.1.2 在eclipse 中配置MySQL 源碼環(huán)境....................................................................80 3.1.3 分分鐘搭建MySQL 一主多從環(huán)境......................................................................87 3.2 MySQL 服務(wù)管理............................................................................................................88 3.2.1 Socket 連接...........................................................................................................88 3.2.2 TCP/IP 連接..........................................................................................................89 3.2.3 MySQL 訪問模式的演進(jìn)......................................................................................89 3.2.4 無密碼登錄...........................................................................................................90 案例3-1：通過shell 腳本檢測MySQL 服務(wù)信息.......................................................... 91 案例3-2：MySQL 密碼加密認(rèn)證的簡單腳本................................................................. 93 案例3-3：MySQL 中如何得到權(quán)限信息........................................................................94 方法1：重新導(dǎo)出導(dǎo)入整個(gè)數(shù)據(jù)庫......................................................................94 方法2：導(dǎo)出mysql 的權(quán)限配置..........................................................................94 方法3：pt 工具導(dǎo)出............................................................................................95 3.3 MySQL 備份恢復(fù)............................................................................................................97 3.3.1 數(shù)據(jù)安全警示錄...................................................................................................97 3.3.2 常規(guī)備份方案.......................................................................................................98 備份工具1：mysqldump......................................................................................99 備份工具2：xtrabackup 工具............................................................................101 備份工具3：mysqlpump ....................................................................................102 備份工具4：mysqlbackup..................................................................................104 備份工具5：binlog 備份工具............................................................................105 數(shù)據(jù)導(dǎo)出小技巧.................................................................................................105 數(shù)據(jù)導(dǎo)入小技巧.................................................................................................106 3.3.3 MySQL 數(shù)據(jù)恢復(fù)...............................................................................................106 恢復(fù)方法1：使用xtrabackup 進(jìn)行數(shù)據(jù)全量恢復(fù)............................................. 107 恢復(fù)方法2：使用xtrabackup 進(jìn)行數(shù)據(jù)增量恢復(fù)............................................. 109 恢復(fù)方法3：使用mysqlbinlog 手工恢復(fù).......................................................... 112 恢復(fù)方法4：使用開源工具恢復(fù)數(shù)據(jù) binlog2sql.............................................. 115 恢復(fù)方法5：使用參數(shù)innodb_force_recovery.................................................. 117 恢復(fù)方法6：基于邏輯的數(shù)據(jù)恢復(fù)....................................................................117 恢復(fù)方法7：基于冷熱數(shù)據(jù)分離的恢復(fù)思路.................................................... 118 恢復(fù)方法8：基于句柄的無備份恢復(fù)................................................................ 119 恢復(fù)方法9：直接恢復(fù)物理文件........................................................................121 3.4 MySQL 安全審計(jì)..........................................................................................................122 3.4.1 常用方法.............................................................................................................122 3.4.2 性能測試結(jié)果.....................................................................................................123 3.4.3 測試小結(jié).............................................................................................................123 4 章 SQL 開發(fā)規(guī)范和基礎(chǔ) 4.1 數(shù)據(jù)庫開發(fā)規(guī)范............................................................................................................124 4.1.1 配置規(guī)范.............................................................................................................124 4.1.2 建表規(guī)范.............................................................................................................125 iv MySQL DBA 工作筆記：數(shù)據(jù)庫管理、架構(gòu)優(yōu)化與運(yùn)維開發(fā) 4.1.3 命名規(guī)范.............................................................................................................125 4.1.4 索引規(guī)范.............................................................................................................125 4.1.5 應(yīng)用規(guī)范.............................................................................................................126 4.1.6 分表規(guī)范.............................................................................................................127 4.1.7 存儲(chǔ)過程規(guī)范.....................................................................................................127 4.1.8 安全規(guī)范.............................................................................................................127 4.1.9 數(shù)據(jù)安全規(guī)范.....................................................................................................128 案例4-1：MySQL 無法創(chuàng)建表的問題分析..................................................................131 4.2 解讀MySQL 數(shù)據(jù)類型..................................................................................................135 4.2.1 MySQL 整數(shù)類型...............................................................................................135 案例4-2：數(shù)值類型在binlog 中需要注意的細(xì)節(jié)......................................................... 136 4.2.2 MySQL 小數(shù)類型...............................................................................................138 4.2.3 MySQL 字符串類型...........................................................................................139 案例4-3：MySQL 中需要注意的字段長度..................................................................140 4.2.4 MySQL 日期類型...............................................................................................142 案例4-4：MySQL 多版本的時(shí)間類型問題..................................................................143 4.2.5 JSON 類型..........................................................................................................144 4.3 MySQL 特有的SQL......................................................................................................146 4.3.1 新增類................................................................................................................146 4.3.2 刪除類................................................................................................................147 4.3.3 修改類................................................................................................................148 專題4-1：解讀Replace into 語句.................................................................................148 4.3.4 查詢類................................................................................................................153 4.4 MySQL 常用函數(shù)..........................................................................................................153 4.4.1 數(shù)學(xué)函數(shù).............................................................................................................154 案例4-5：MySQL 字符串中抽取數(shù)值的方法............................................................... 155 案例4-6：order by 的妙用.............................................................................................156 4.4.2 字符串函數(shù).........................................................................................................157 案例4-7：MySQL 字符函數(shù)的壓力測試......................................................................160 案例4-8：字符串動(dòng)態(tài)匹配...........................................................................................162 4.4.3 條件控制函數(shù).....................................................................................................162 4.4.4 日期和時(shí)間函數(shù).................................................................................................163 案例4-9：Now*和sysd*e*的差別..............................................................................166 4.4.5 系統(tǒng)信息函數(shù).....................................................................................................169 4.4.6 其他常用的MySQL 函數(shù)...................................................................................169 5 章 MySQL 運(yùn)維管理實(shí)踐 5.1 數(shù)據(jù)變更管理................................................................................................................171 5.1.1 MySQL 腳本部署的四種策略............................................................................171 案例5-1：實(shí)戰(zhàn)對(duì)比四種腳本部署策略的優(yōu)劣............................................................. 172 5.1.2 通過對(duì)比來了解onlineDDL..............................................................................175 案例5-2：MySQL 5.5 版本原生的DDL 代價(jià)測試....................................................... 176 案例5-3：MySQL 5.7 版本原生的DDL 代價(jià)測試....................................................... 176 5.1.3 Online DDL 的兩種算法.....................................................................................177 案例5-4：對(duì)比測試online DDL 的兩種算法（copy 和inplace） ............................... 177 5.1.4 pt-osc 的原理和實(shí)現(xiàn)...........................................................................................181 案例5-5：源碼分析pt-osc 的實(shí)現(xiàn)原理........................................................................181 案例5-6：平滑刪除數(shù)據(jù)的小技巧................................................................................184 5.2 MySQL 復(fù)制管理..........................................................................................................186 5.2.1 MySQL 復(fù)制的四種類型....................................................................................186 5.2.2 MySQL 半同步復(fù)制...........................................................................................187 案例5-7：簡單測試半同步復(fù)制....................................................................................190 5.2.3 GTID 的管理模式...............................................................................................191 案例5-8：修復(fù)GTID 復(fù)制失敗的分析.........................................................................195 5.2.4 如何看待主從延遲.............................................................................................200 案例5-9：MySQL 5.6、5.7 版本并行復(fù)制測試............................................................ 203 5.2.5 主從數(shù)據(jù)不一致的分析......................................................................................209 案例5-10：經(jīng)典的自增列問題測試..............................................................................210 案例5-11：主從不一致的修復(fù)過程..............................................................................212 6 章MySQL 查詢優(yōu)化 6.1 MySQL 優(yōu)化基礎(chǔ)..........................................................................................................215 6.1.1 MySQL 索引解析...............................................................................................216 6.1.2 推理SQL 的解析過程........................................................................................221 案例6-1：group by 問題淺析........................................................................................224 6.1.3 讀懂執(zhí)行計(jì)劃.....................................................................................................227 6.1.4 使用MySQL Profile 定位性能瓶頸....................................................................234 案例6-2：合理評(píng)估新特性的使用................................................................................238 6.2 SQL 查詢優(yōu)化................................................................................................................240 6.2.1 MySQL 中的派生表...........................................................................................240 6.2.2 MySQL 中的半連接...........................................................................................243 6.2.3 MySQL 反連接...................................................................................................247 6.2.4 行值表達(dá)式優(yōu)化.................................................................................................250 6.3 MySQL 優(yōu)化技巧..........................................................................................................251 6.3.1 MySQL 分頁邏輯優(yōu)化.......................................................................................251 6.3.2 數(shù)據(jù)隱式轉(zhuǎn)換.....................................................................................................254 案例6-3：一條upd*e 語句引發(fā)的“血案”................................................................ 256 7 章 MySQL 事務(wù)和鎖 7.1 MySQL 并發(fā)控制..........................................................................................................263 7.1.1 為什么需要事務(wù).................................................................................................263 7.1.2 MySQL 并發(fā)控制技*方案................................................................................265 7.1.3 MySQL 中的MVCC...........................................................................................266 7.2 事務(wù)隔離級(jí)別................................................................................................................269 7.2.1 MySQL 中的隔離級(jí)別RR 和RC.......................................................................270 7.2.2 RR 隔離級(jí)別下的unique 失效...........................................................................272 7.2.3 RR 隔離級(jí)別下的更新沖突................................................................................274 7.3 MySQL 鎖機(jī)制..............................................................................................................275 7.3.1 MySQL 鎖的類型...............................................................................................275 7.3.2 索引加鎖過程的差異..........................................................................................278 7.3.3 這樣分析一個(gè)死鎖問題......................................................................................280 7.4 經(jīng)典的死鎖案例集........................................................................................................283 場景1：3 條insert 語句導(dǎo)致的死鎖問題......................................................................283 場景2：事務(wù)回滾導(dǎo)致的死鎖.......................................................................................285 場景3：自增列導(dǎo)致的死鎖...........................................................................................287 場景4：事務(wù)提交導(dǎo)致的死鎖問題...............................................................................288 場景5：delete 和insert 混合的死鎖..............................................................................291 場景6：2 條delete 語句導(dǎo)致的死鎖問題.....................................................................293 7.5 事務(wù)降維........................................................................................................................294 降維策略1：存儲(chǔ)過程調(diào)用轉(zhuǎn)換為透明的SQL 調(diào)用................................................... 295 降維策略2：Drop 作轉(zhuǎn)換為可逆的DDL 作......................................................... 295 降維策略3：Trunc*e 作轉(zhuǎn)換為安全的DDL 作................................................... 295 降維策略4：DDL 作轉(zhuǎn)換為DML 作....................................................................296 降維策略5：Delete 作轉(zhuǎn)換為高效作....................................................................296 降維策略6：Upd*e 作轉(zhuǎn)換為Insert 作................................................................ 296 8 章 MySQL 集*和高可用設(shè)計(jì) 8.1 MySQL 高可用方案.......................................................................................................297 8.1.1 MySQL 高可用方案概覽....................................................................................298 8.1.2 MySQL 高可用方案的建議................................................................................298 8.1.3 MySQL 高可用的迭代方案思考........................................................................299 8.2 MySQL 高可用方案之MHA.........................................................................................300 8.2.1 MHA 原理和架構(gòu)...............................................................................................300 8.2.2 如何系統(tǒng)的測試MHA.......................................................................................303 8.2.3 快速測試MHA 的步驟......................................................................................304 8.2.4 從代碼關(guān)系圖理清MHA 的脈絡(luò).......................................................................308 8.2.5 我們可能不知道的MHA 邏輯...........................................................................312 8.2.6 MHA 的缺點(diǎn)和局限性.......................................................................................313 8.2.7 MHA 的補(bǔ)充和改進(jìn)...........................................................................................314 8.3 MySQL 高可用方案之InnoDB Cluster......................................................................... 315 8.3.1 InnoDB Cluster 三大件.......................................................................................316 8.3.2 快速入手InnoDB Cluster 的建議.......................................................................317 8.3.3 使用sandbox 快速部署InnoDBCluster............................................................. 318 8.3.4 InnoDb Cluster 核心組件：MGR....................................................................... 323 8.3.5 部署MGR 的幾種姿勢.......................................................................................324 方法1：分分鐘搭建MGR 單主/多主測試環(huán)境................................................ 324 方法2：線上環(huán)境規(guī)范部署...............................................................................325 8.3.6 常見的MGR 問題..............................................................................................327 問題1：單主模式加入節(jié)點(diǎn)失敗........................................................................328 問題2：模式配置錯(cuò)誤導(dǎo)致無法啟動(dòng)集*........................................................ 328 問題3：節(jié)點(diǎn)配置不統(tǒng)一導(dǎo)致集*無法啟動(dòng).................................................... 328 問題4：數(shù)據(jù)寫入失敗修復(fù)...............................................................................329 問題5：模擬災(zāi)難..............................................................................................329 問題6：如何判斷一個(gè)復(fù)制組中的主節(jié)點(diǎn)........................................................ 330 8.3.7 遷移到MGR 需要思考的問題...........................................................................331 案例8-1：切換到MGR 的參考步驟.................................................................332 案例8-2：大事務(wù)導(dǎo)致的運(yùn)維系統(tǒng)無法訪問..................................................... 336 8.4 基于consul 的高可用擴(kuò)展方案.....................................................................................339 8.4.1 基于consul 服務(wù)的高可用方案..........................................................................339 8.4.2 基于MHA Consul 的MySQL 高可用設(shè)計(jì).......................................................340 8.4.3 MySQL 高可用方案：MGR consul 組合測試........................................
你還可能感興趣
我要評(píng)論
|
