MySQL高級DBA目錄TOC\o"1-4"\h\z\u1.mysql第一天課程 61.1.mysql數據庫介紹 61.2.mysql三種安裝方式區別和平臺支持更新 71.3.項目1：WindowsServer搭建mysql數據庫 81.4.項目2：Linux搭建mysql單實例數據庫（生產環境規劃） 91.5.項目3：Linux搭建mysql多實例數據庫（生產環境規劃） 101.6.mysql4種啟動的方式 111.7.mysql常用維護命令 121.8.mysql數據類型 142.mysql第二天課程 162.1.mysqlDDL語法官方介紹 162.2.mysql數據庫管理案例 162.3.mysql約束介紹以及分類 182.4.mysql數據表管理以及約束使用案例 192.5.mysql索引介紹 242.6.mysqlB+Tree索引深入解析（b樹和b+樹） 252.7.mysql主鍵索引和普通索引的區別 322.8.InnoDB一級索引和二級索引的關系：回表 322.9.堆組織表(HOT)和索引組織表(IOT)的區別 342.10.mysqlB-Tree索引使用案例 363.mysql第三天課程 383.1.mysqlDML語言官方介紹 383.2.mysqlDML語言-insertinto 383.3.mysqlDML語言-update 403.4.mysqlDML語言-delete 403.5.mysqlDML語言-select 413.6.mysqlDML語言-select高級查詢連接查詢和子查詢 483.7.mysql體系架構-物理文件 543.8.mysql體系架構-文件類型 543.9.mysql體系架構-數據庫與實例的關系 563.10.mysql體系架構-存儲引擎 573.11.mysql體系架構-InnoDB邏輯存儲結構 583.12.mysql體系架構-InnoDB存儲引擎體系架構 603.13.InnoDB存儲引擎的常用參數配置 663.14.InnoDBBufferPool原理與配置 693.15.InnoDB表空間介紹以及管理 733.16.深入分析InnoDB內部結構 773.17.mysql常用空間信息查詢SQL 774.mysql第四天課程 794.1.mysql數據庫事務相關概念理解 794.2.mysqlinnodb事務管理 824.3.mysql鎖分類介紹 874.4.mysql各種鎖的名詞 894.5.mysql查看鎖狀態、鎖相關參數、解決死鎖故障 914.6.mysqlMVCC多版本并發控制 934.7.mysqlMVCC的實現原理 954.8.mysql權限系統介紹 1004.9.MySQL權限詳解 1034.10.MySQL用戶權限信息管理 1054.11.mysql安全之密碼強度驗證 1084.12.mysql安全之SSL認證 1124.13.mysql安全之重置ROOT密碼 1165.mysql第五天課程 1185.1.mysqlbinlog二進制日志概述 1185.2.mysqlbinlog二進制日志解析 1185.3.mysqlBinlog二進制日志參數配置 1195.4.mysqlBinlog二進制日志刷盤原理 1215.5.mysqlBinlog二進制格式解析 1225.6.開啟mysqlBinlog日志、插入測試數據 1235.7.mysqlBinlog日志的常用命令 1245.8.mysqlBinlog日志查看工具及使用案例 1255.9.mysql通過binlog日志恢復數據-誤刪除的表 1275.10.mysql針對binlog日志對表的增刪改統計分析 1275.11.mysql邏輯備份工具mysqldump 1285.12.mysql邏輯備份工具mysqldump生產備份案例 1305.13.mysql物理備份工具xtrabackup 1315.14.mysql物理備份工具xtrabackup生產備份案例 1356.mysql第六天課程 1366.1.InnodbonlineDDL 1366.2.mysql復制技術的介紹 1416.3.mysqlinnodb二階段日志提交機制和組提交解析 1436.4.mysql主從復制技術之同步方式 1446.5.mysql主從復制技術之GTID特性 1486.6.mysql主從復制技術之常用配置參數 1506.7.主從項目1：搭建傳統異步主從復制（使用mysqldump） 1526.8.主從項目2：搭建GTID無損同步主從復制（使用xtrabackup） 1537.mysql第七天課程 1567.1.MaxScale讀寫分離中間件介紹 1567.2.MaxScale讀寫分離架構圖介紹 1567.3.MaxScale功能介紹 1587.4.MaxScale項目1：一主兩從復制實現讀寫分離（一組復制） 1587.5.MaxScale項目2：一主兩從復制實現讀寫分離（兩組復制） 1597.6.企業級項目：mysql+keepalived雙主熱備故障切換案例 1608.mysql第八天課程 1618.1.mysql復制技術之MGR介紹 1618.2.mysql大型高可用集群架構(GroupReplication)拓撲圖 1628.3.mysql復制技術之MGR特點 1638.4.mysql復制技術之MGR模式 1638.5.mysqlMGR(Multi-primary)模式的限制 1638.6.mysql主從復制技術之MGR限制匯總 1648.7.mysqlGroupReplication復制參數解析 1658.8.項目一：mysqlGroupReplication多主模式項目實施 1668.9.項目二：mysqlGroupReplication單主模式項目實施 1678.10.mysqlGroupReplication節點狀態轉移 1688.11.項目三：在線修改Single-Maste為Multi-Master模式 1688.12.項目四：mysqlGroupReplication在線増加節點 1689.mysql第九天課程 1709.1.GaleraClusterr(PXC)技術方案介紹 1709.2.GaleraClusterr(PXC)大型高可用方案 1709.3.PXC集群的原理 1719.4.PXC集群的特點 1729.5.PXC集群的缺點 1739.6.PXC集群的使用限制 1739.7.PXC集群使用到的端口 1749.8.PXC集群的使用總結 1759.9.PXC集群參數詳解 1759.10.項目一：PXC集群數據庫項目實施 1759.11.PXC集群gcache、grastate.dat、gvwstate.dat文件介紹 1769.12.PXC集群數據庫部署檢查和相關參數 1779.13.項目二：PXC增加與刪除集群節點案例 18010.mysql第10天課程 18110.1.集群技術的分類 18110.2.常用的負載均衡 18110.3.keepalived介紹 18210.4.HAProxy軟件介紹 18210.5.HAProxy軟件優點 18310.6.關于網站架構比較合理主流的架構方案 18310.7.Keepalived+HAproxy+PXC集群拓撲圖 18410.8.Keepalived+HAproxy+PXC項目環境介紹與實施 18411.mysql第11天課程 18711.1.mysql優化概述和優化方案 18711.2.操作系統優化調整 18911.3.mysql數據庫層面常用優化指標分析 19211.4.SQL分析和優化 19611.5.mysql性能優化之執行計劃Explain 19711.6.mysql性能優化之收集統計信息 19811.7.mysql索引優化經驗與SQLAdvisor 20011.8.mysql索引優化案例和索引使用經驗 20211.9.Pt-Query-Digest工具介紹 20312.mysql第12天課程 20512.1.MyCat產品特性與分表分庫功能介紹 20512.2.mysql高并發業務系統架構優化方案（分庫-業務拆分） 20812.3.mysql高并發業務系統架構優化方案（分表） 21012.4.mysql高并發業務系統架構優化方案（分庫分表） 21212.5.MyCat安裝與啟動 21312.6.mycat配置系統介紹 21412.7.MyCat分庫分表功能與分片規則功能 21412.8.MyCat多租戶應用講解 21812.9.MyCat分表分庫分片項目實戰-垂直分庫 21912.10.MyCat分庫分表分片項目實戰-按全國省份分片枚舉 22112.11.MyCat分表分庫分片項目實戰-水平分庫-主鍵范圍 222mysql第一天課程mysql數據庫介紹mysql是一款安全、跨平臺、高效的，并與PHP、Java等主流編程語言緊密結合的數據庫系統。該數據庫系統是由瑞典的mysqlAB公司開發、發布并支持，由mysql的初始開發人員DavidAxmark和MichaelMontyWidenius于1995年建立的。目前mysql被廣泛地應用在Internet上的中小型網站中。由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，使得很多公司都采用mysql數據庫以降低成本。mysql數據庫可以稱得上是目前運行速度最快的SQL語言數據庫之一。除了具有許多其他數據庫所不具備的功能外，mysql數據庫還是一種完全免費的產品，用戶可以直接通過網絡下載mysql數據庫，而不必支付任何費用。2009年4月20日，Oracle并購了Sun，這也意味著mysql歸屬到甲骨文的旗下,Oracle公司發布兩種mysql的版本,企業版和社區版:1.社區版的免費，出問題mysql公司概不負責，是企業版的測試版，功能卻沒有企業版功能完善。2.企業版的收費，并且價格不便宜，標準版2000美元，企業版5000美元，高級集群版10000美元（6萬人民幣），但是只包含穩定的功能。目前mysql最穩定，最好用的是5.7版本，相當于Oracle11G版本，這也是所有互聯網公司使用得最多的一個版本，所以本次文檔將以mysql5.7.20以上的版本來展開培訓。mysql三種安裝方式區別和平臺支持更新二進制安裝（生產推薦）linux下二進制格式的軟件是指事先已經在各種平臺編譯安裝好相關的軟件，然后壓縮打包，在安裝時只需要解壓或者執行安裝可執行文件即可;源碼編譯安裝（開發/測試推薦）使用make和makefile工具可以簡單快速地解決各個源文件之間復雜的依賴關系，同時，make工具還可以自動完成所有源碼文件的編譯工作，并且可以只對上次編譯后修改過的文件進行增量編譯;rpm/yum方式安裝（學習推薦）在mysql官網下載rmp二進制安裝包使用rpm-ivh進行安裝;mysql不同版本平臺支持更新/support/supportedplatforms/database.html項目1：WindowsServer搭建mysql數據庫環境規劃虛擬機版本： VMware?Workstation15Pro系統版本： WindowsServer2012R2網絡連接方式： 橋接主機名： dbos110GateWay: IP: 10CPU: 8核內存： 16Gmysql版本： mysql5.7.20社區版數據庫安裝過程安裝過程請看實戰操作。項目2：Linux搭建mysql單實例數據庫（生產環境規劃）環境規劃虛擬機版本： VMware?Workstation15Pro系統版本： RedHatEnterpriseLinuxServerrelease7.6網絡連接方式： 橋接mysql版本： mysql5.7.29社區版主機名： dbos111GateWay: IP: 11mysqlport： 3306CPU: 8核內存： 16G硬盤： sda 150G 掛載/目錄sdb 100G LVM邏輯卷掛載/mysqlsdc 100G LVM邏輯卷掛載/mysqlsdd100G LVM邏輯卷掛載/mysqlLinux環境安裝過程RHEL7.6安裝過程請看實戰操作。mysql數據庫搭建過程mysql5.7.29安裝部署過程請看實戰操作。項目3：Linux搭建mysql多實例數據庫（生產環境規劃）環境規劃虛擬機版本： VMware?Workstation15Pro系統版本： RedHatEnterpriseLinuxServerrelease7.6網絡連接方式： 橋接mysql版本： mysql5.7.29社區版主機名： dbos111GateWay: IP: 11mysqlport： 3307CPU: 8核內存： 16G硬盤： sda 150G 掛載/目錄sdb 100G LVM邏輯卷掛載/mysql sdc 100G LVM邏輯卷掛載/mysqlsdd100G LVM邏輯卷掛載/mysqlLinux環境安裝過程使用項目2：Linux搭建mysql單實例數據庫項目的環境。mysql數據庫搭建過程mysql5.7.29安裝部署過程請看實戰操作，再增加個新的mysql（3307端口）實例。mysql4種啟動的方式大家都知道Oracle是多進程的數據庫，而mysql是一種單進程多線程的數據庫，但是為什么使用psaux|grep"mysql"這條命令的時候會出現兩個關于mysql的進程，這里就涉及到mysql的4種啟動方式：mysqld: 存放在$basedir/bin目錄下，是使用C/C++語言編寫編譯出來的二進制可執行文件，是計算機進程，mysql數據庫的主程序,可以直接通過以下命令，指定參數文件和用戶來啟動mysql主進程，但是一般不建議這樣啟動;./mysqld--defaults-file=/etc/f--user=mysql& 可以通過以下命令去查看mysqld執行文件的所有參數以及變量;./mysqld--verbose--helpmysqld_safe: 是個shell腳本,腳本位置在$basedir/bin路徑下，默認去調用mysqld服務器程序,進行服務器的監聽，如果mysqld進程掉了，mysqld_safe會自動啟動mysqld程序;可以通過以下命令去查看mysqld_safe腳本文件的所有啟動參數;./mysqld_safe--helpmysql.server 是mysql啟動/停止的shell腳本,腳本位置在mysql安裝目錄的support-files路徑下，默認去調用mysqld_safe腳本文件啟動mysql進程,生產環境強烈建議使用這個腳本進行啟動與停止mysql進程;mysqld_multi 是mysql多實例啟動/停止的shell腳本,腳本位置在$basedir/bin路徑下，默認去調用mysqld_safe腳本文件啟動mysql進程,但是一般不會使用這個腳本來進行mysql多實例管理;mysql常用維護命令查看數據庫版本mysql>showvariableslike'%version%';切換數據庫、列出所有的數據庫、查看數據庫信息mysql>showdatabases;mysql>use<database_name>;mysql>showcreatedatabases<database_name>;查看數據庫存儲引擎狀態mysql>showengines;mysql>showengineinnodbstatus\G查看數據庫使用的插件mysql>showplugins;查看數據庫的狀態mysql>showstatus;查看數據庫主庫、從庫狀態mysql>showmasterstatus;mysql>showslavestatus\G查看觸發器和存儲過程mysql>showtriggers;mysql>showprocedurestatus;查看mysql數據庫當前線程mysql>showprocesslist;查看某個用戶的權限mysql>showgrantsforroot@'%';mysql>showgrantsforroot@'localhost'; 10)查看數據庫下的表和索引mysql>showtables;mysql>showcreatetable

<table_name>;mysql>showindexfrom<table_name>;mysql數據類型mysql支持的多種數據類型主要有：數值數據類型、日期/時間類型、字符串類型。數值類型-整型浮點數&定點數注：定點數以字符串形式存儲，對精度要求高時使用decimal較好；盡量避免對浮點數進行減法和比較運算。

時間/日期類型字符串類型mysql數據類型總結mysql常見的數據類型有int、decimal、date、datetime、varchar、text，對于數據類型這塊內容的熟練、精通，是開發要做的工作，我們作為mysqlDBA，更多的只是熟悉，了解即可。mysql第二天課程mysqlDDL語法官方介紹DataDefinitionStatements（DDL）語言介紹其語句包括create和drop，在數據庫中創建新表或刪除表createtable或droptable，為表加入索引等。DDL包括許多與數據庫目錄中獲得數據有關的保留字。它也是動作查詢的一部分。DataDefinitionStatements數據庫定義語言官方文檔地址/doc/refman/5.7/en/sql-data-definition-statements.htmlmysql數據庫管理案例創建一個tkjy數據庫CREATEDATABASEtkjy;CREATEDATABASEIFNOTEXISTStkjy; #defaultcharacterset:默認字符集CREATEDATABASEIFNOTEXISTStkjydefaultcharactersetUTF8;#defaultcollate：默認排序規格#utf8_general_ci：不區分大小寫#utf8_general_cs：區分大小寫 CREATEDATABASEIFNOTEXISTStkjydefaultcharactersetUTF8 defaultcollateutf8_general_ci;切換數據庫usetkjy;查詢數據庫showdatabases;#如果有很多數據庫，模糊查詢某個數據庫 showdatabaseslike'%tkjy%';#查詢創建數據庫的語句showcreatedatabasetkjy;#更新數據庫選項信息（操作需要謹慎） alterdatabasetkjycharactersetgbk;刪除數據庫dropdatabasetkjy;dropdatabaseifexiststkjy;使用mysqladmin工具創建、刪除數據庫mysqladmincreatetkjy-uroot-p123456mysqladmindroptkjy-uroot-p123456mysql約束介紹以及分類mysql約束作用主要是防止非法數據進入到表中,確保數據的正確性和一致性(統稱數據完整性)約束也可以防止一個表被刪除，主要有非空約束、唯一約束、主鍵約束、外鍵約束、檢查約束。mysql約束對應的五大關鍵詞NOTNULL： 如果在列上定義了notnull,那么當插入數據時,必須為列提供數據。UNIQUE： 當定義了唯一約束后,該列值是不能重復的,但是可以為null。PrimaryKey：用于唯一的標識表行的數據,當定義主鍵約東后,該列不但不能重復而且不能 為NULL。一張表最多只能有一個主鍵,但是可以由多個unique約束。ForeignKey：用于定義主表和從表之間的關系,外鍵約束要定義在從表上,主要則必須具有 主鍵約束或是uniques約束,當定義外鍵約束后,要求外鍵列數據必須在主表的主鍵列存 在或是為NULL。CHECK： 用于強制行數據必須滿足的條件,假定在sal列上定義了check約束,并要求sal列值在 1000~2000之間,如果不在1000~2000之間就會提示出錯，mysql里面的check約束是有， 但是所有存儲引擎都不生效，是個幌子，如果想實現類似于check約束的功能，可以通過 觸發器（TRIGGER）或者枚舉（ENUM）數據類型來實現，前面沒有講到枚舉數據類型，這 里帶過一下，mysql數據庫在程序設計過程中是非常不推薦使用觸發器和枚舉數據類 型，會極大影響數據庫運行性能。mysql數據表管理以及約束使用案例在tkjy數據庫下面創建一張student學生表usetkjy;createtableifnotexistsstudent( idint(5)unsignedauto_incrementprimarykeycomment'學生表主鍵', namevarchar(20)notnullcomment'學生姓名', agetinyintnotnullcomment'學生年齡', admission_timedatetimecomment'入學時間', genderenum('男','女','保密')comment'學生性別', student_idint(10)UNIQUEcomment'學生編號')engine=innodbdefaultcharset=utf8comment'學生表';#auto_increment： 主鍵自增（可選操作）#engine： 表使用存儲引擎（可選操作） #comment： 注釋（可選操作）#defaultcharset： 表的字符集（可選操作）使用like關鍵字通過舊表創建新表(包括舊表的結構+信息+索引)createtable<table_name>like<old_table_name>;使用as關鍵字通過舊表創建新表(包括舊表的結構+信息)createtable<table_name>asselect*from<old_table_name>;查看表、表結構、表創建語句showtables;showtablesfromtkjy;showtableslike'%stud%';descstudent;showcreatetablestudent\G;刪除表droptablestudent;重命名表(注意：切勿在生產環境上直接rename表)renametablestudenttonew_student;截斷表truncatetablenew_student;修改表結構#altertable<table_name>add/drop/modify/change#增加列、增加主鍵altertablenew_studentaddstudent_fromvarchar(10)notnull;altertablenew_studentadd(phoneintuniquenotnull,emailvarchar(20));altertablenew_studentaddprimarykey(id);#刪除列、刪除主鍵約束、刪除自增的主鍵約束altertablenew_studentdropemail;altertablenew_studentdropprimarykey;altertablenew_studentchangeididint;altertablenew_studentdropprimarykey;#重命名列altertablenew_studentchangestudent_fromst_fromvarchar(10);#修改表字段屬性altertablenew_studentmodifyst_fromvarchar(15)unique;#修改字符集,有數據不能改altertablenew_studentcharactersetgbk;altertablenew_studentcharactersetutf8; 在tkjy數據庫創建包含外鍵的員工表（YG）和工資表（gz）#創建工資表createtableifnotexiststkjy.gz( idint(5)primarykeycomment'工資表主鍵', salaryint(7)notnullcomment'薪水', jobvarchar(10)notnullcomment'工作崗位', departmentvarchar(5)notnullcomment'工作部門')engine=innodbdefaultcharset=utf8comment'員工表';#工資表插入數據insertintotkjy.gzvalues(1,10000,'銷售','銷售部');insertintotkjy.gzvalues(2,15000,'OracleDBA','技術部');insertintotkjy.gzvalues(3,20000,'mysqlDBA','技術部');insertintotkjy.gzvalues(4,18000,'java','研發中心');insertintotkjy.gzvalues(5,30000,'C++','研發中心');insertintotkjy.gzvalues(6,16000,'python','研發中心');commit;#查看工資select*fromtkjy.gz;#創建員工表createtableifnotexiststkjy.yg( idint(5)unsignedauto_incrementprimarykeycomment'員工表主鍵', namevarchar(7)notnullcomment'員工姓名', agetinyintnotnullcomment'員工年齡', entry_timeyearcomment'入職時間', genderenum('男','女','保密')comment'員工性別', gz_idint(5)notnull, foreignkey(gz_id)referencestkjy.gz(id)) engine=innodbdefaultcharset=utf8comment'員工表';#外鍵必須是主表的主鍵或者唯一鍵,如果是另外一張表主鍵的話，該表主鍵不允許帶有auto_increment自增長屬性。#主表記錄刪除時ondeletecascade/更新時的動作onupdatecascade#創建表以后再增加外鍵也可以#altertabletkjy.ygaddforeignkey(gz_id)referencestkjy.gz(id);#插入數據測試（在主表間鍵值內成功）insertintotkjy.ygvalues(1,'春野櫻',18,2015,'女',1);insertintotkjy.ygvalues(2,'漩渦鳴人',18,2016,'男',2);insertintotkjy.ygvalues(3,'宇智波佐助',18,2017,'男',2);insertintotkjy.ygvalues(4,'第一代火影',65,1970,'男',3);insertintotkjy.ygvalues(5,'第二代火影',60,1975,'男',4);insertintotkjy.ygvalues(6,'第三代火影',38,2000,'男',5);insertintotkjy.ygvalues(7,'第四代火影',38,2000,'男',6);commit;select*fromtkjy.yg;#插入數據測試（在主表間鍵值外失敗）insertintotkjy.ygvalues(8,'宇智波斑',28,2020,'男',7);外鍵總結：mysql數據庫不建議使用外鍵、會極大影響數據庫運行性能（并發訪問）。 mysql索引介紹什么是索引?在關系數據庫中，索引是一種單獨的、物理的數對數據庫表中一列或多列的值進行排序的一種存儲結構。 它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。索引的作用相當于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。索引提供指向存儲在表的指定列中的數據值的指針，然后根據您指定的排序順序對這些指針排序。數據庫使用索引以找到特定值，然后順指針找到包含該值的行。這樣可以使對應于表的SQL語句執行得更快，可快速訪問數據庫表中的特定信息。索引的優點通過創建唯一性索引,可以保證數據庫表中每一行數據的唯一性。可以加速表和表之間的連接,特別是在實現數據的參考完整性方面特別有意義。在使用分組和排序子句進行數據查詢時,可以減少查詢中分組和排序的時間。通過使用索引,可以在查詢的過程中,使用優化隱藏器,提高系統的性能。索引的缺點創建索引和維護索引要耗費時,這種時間隨著數據量的增加而增加。索引需要占物理空間,除了數據表占數據空間之外,每一個索引還要占一定的物理空間,如果要建立聚簇索引,那么需要的空間就會更大。當對表中的數據進行增加、刪除和修改的時候,索引也要動態的維護,這樣就降低了數據的維護速度。索引的分類B+樹索引(主要,默認索引)：mysqlinnodb默認的索引類型就是B+tree索引(B樹,B-樹,B+樹,B*樹),BinaryTree,就是一個二叉樹。hash索引：Hash索引結構的特殊性,其檢索效率非常高,索引的檢索可以一次定位,不像B-Tree索引需要從根節點到枝節點,最后才能訪問到頁節點這樣多次的IO訪問,所以Hash索引的查詢效率要遠高于B+Tree索引，僅僅能滿足"=""IN"和"<=>"查詢,不能使用范圍查詢。只有Memory存儲引擎顯示支持hash索引。FULLTEXT索引(全文檢索,MYISAM和INNODB引擎都支持了)。R-tree索引(用于對GIS數據類型創建SPATIAL空間索引)索引按邏輯的分類主鍵索引普通索引or單列索引多列索引(復合索引)唯一索引或者非唯一索引空間索引mysqlB+Tree索引深入解析（b樹和b+樹）在這里首先我拋出四個問題?為什么需要B-樹?有了B-樹，為什么需要B+樹？/B+樹演變的需求。B-樹和B+樹有什么區別?為什么MongoDB使用B-樹，而MySQL使用B+樹?B-樹由來定義：B-樹是一類樹，包括B-樹、B+樹、B*樹等，是一棵自平衡的搜索樹，它類似普通的平衡二叉樹，不同的一點是B-樹允許每個節點有更多的子節點。樹是專門為外部存儲器設計的，如磁盤，它對于讀取和寫入大塊數據有良好的性能，所以一般被用在文件系統及數據庫中。先來看看為什么會出現B-樹這類數據結構。傳統用來搜索的平衡二叉樹有很多，如AVL樹（平衡二叉搜索樹），紅黑樹等。這些樹在一般情況下查詢性能非常好，但當數據非常大的時候它們就無能為力了。原因當數據量非常大時，內存不夠用，大部分數據只能存放在磁盤上，只有需要的數據才加載到內存中。一般而言內存訪問的時間約為50ns（納秒），而磁盤在10ms（毫秒）左右。速度相差了近5個數量級，磁盤讀取時間遠遠超過了數據在內存中比較的時間。這說明程序大部分時間會阻塞在磁盤IO上。那么我們如何提高程序性能？減少磁盤IO次數，像AVL樹，紅黑樹這類平衡二叉樹從設計上無法“迎合”磁盤。關于磁盤可參考淺談計算機中的存儲模型（四）磁盤</wwh578867817/article/details/49005675>上圖是一顆簡單的平衡二叉樹，平衡二叉樹是通過旋轉來保持平衡的，而旋轉是對整棵樹的操作，若部分加載到內存中則無法完成旋轉操作。其次平衡二叉樹的高度相對較大為logn（底數為2），這樣邏輯上很近的節點實際可能非常遠，無法很好的利用磁盤預讀（局部性原理），所以這類平衡二叉樹在數據庫和文件系統上的選擇就被pass了。空間局部性原理：如果一個存儲器的某個位置被訪問，那么將它附近的位置也會被訪問。我們從“迎合”磁盤的角度來看看B-樹的設計。索引的效率依賴與磁盤IO的次數，快速索引需要有效的減少磁盤IO次數，如何快速索引呢？索引的原理其實是不斷的縮小查找范圍，就如我們平時用字典查單詞一樣，先找首字母縮小范圍，再第二個字母等等。平衡二叉樹是每次將范圍分割為兩個區間。為了更快，B-樹每次將范圍分割為多個區間，區間越多，定位數據越快越精確。那么如果節點為區間范圍，每個節點就較大了。所以新建節點時，直接申請頁大小的空間（磁盤是按block分的，一般為512Byte。磁盤IO一次讀取若干個block，我們稱為一頁，具體大小和操作系統有關，一般為4k，8k或16k），計算機內存分配是按頁對齊的，這樣就實現了一個節點只需要一次IO。上圖是一棵簡化的B-樹，多叉的好處非常明顯，有效的降低了B-樹的高度，為底數很大的logn，底數大小與節點的子節點數目有關，一般一棵B-樹的高度在3層左右。層數低，每個節點區確定的范圍更精確，范圍縮小的速度越快。上面說了一個節點需要進行一次IO，那么總IO的次數就縮減為了logn次。B-樹的每個節點是n個有序的序列(a1,a2,a3…an)，并將該節點的子節點分割成n+1個區間來進行索引(X1<a1,a2<X2<a3,…,an+1<Xn<anXn+1>an)。上圖是一顆B-樹，B-樹的每個節點有d~2d個key，這個因子指明了樹的分裂及合并的規則，這個規則維持了B-樹的平衡。B-樹的插入和刪除就不具體介紹了，很多資料都描述了這一過程。在普通平衡二叉樹中，插入刪除后若不滿足平衡條件則進行旋轉操作，而在B-樹中，插入刪除后不滿足條件則進行分裂及合并操作。簡單敘述下分裂及合并操作。分裂：如果有一個節點有2d個key，增加一個后為2d+1個key，不符合上述規則B-樹的每個節點有d~2d個key，大于2d，則將該節點進行分裂，分裂為兩個d個key的節點并將中值key歸還給父節點。合并：如果有一個節點有d個key，刪除一個后為d-1個key，不符合上述規則B-樹的每個節點有d~2d個key，小于d，則將該節點進行合并，合并后若滿足條件則合并完成，不滿足則均分為兩個節點。B-樹的查找，我們來看看B-樹的查找，假設每個節點有n個key值，被分割為n+1個區間，注意，每個key值緊跟著data域，這說明B-樹的key和data是聚合在一起的。一般而言，根節點都在內存中，B-樹以每個節點為一次磁盤IO，比如上圖中，若搜索key為25節點的data，首先在根節點進行二分查找（因為keys有序，二分最快），判斷key25小于key50，所以定位到最左側的節點，此時進行一次磁盤IO，將該節點從磁盤讀入內存，接著繼續進行上述過程，直到找到該key為止。B-樹由來B+樹是B-樹的變種，它與B-樹的不同之處在于：在B+樹中，key的副本存儲在內部節點，真正的key和data存儲在葉子節點上。n個key值的節點指針域為n而不是n+1。如下圖為一顆B+樹：因為內節點并不存儲data，所以一般B+樹的葉節點和內節點大小不同，而B-樹的每個節點大小一般是相同的，為一頁。為了增加區間訪問性，一般會對B+樹做一些優化。如下圖帶順序訪問的B+樹。B-樹和B+樹的區別B+樹內節點不存儲數據，所有data存儲在葉節點導致查詢時間復雜度固定為log(n)。而B-樹查詢時間復雜度不固定，與key在樹中的位置有關，最好為O(1)。B+樹葉節點兩兩相連可大大增加區間訪問性，可使用在范圍查詢等，而B-樹每個節點key和data在一起，則無法區間查找。B+樹更適合外部存儲。由于內節點無data域，每個節點能索引的范圍更大更精確在數據結構上：B樹為有序數組+平衡多叉樹，而B+樹為有序數組鏈表+平衡多叉樹為什么MongoDB索引選擇B-樹，而Mysql索引選擇B+樹這些內容了解后，我們來看為什么MongoDB索引選擇B-樹，而Mysql（InooDB引擎）索引選擇B+樹。Mysql大家應該比較熟悉，傳統的關系型數據庫，下面介紹下MongoDB。來看下wiki百科上MongoDB的定義：這段話的大致意思是MongoDB是文檔型的數據庫，是一種nosql，它使用類Json格式保存數據。文檔型數據庫和我們常見的關系型數據庫不同，一般使用XML或Json格式來保存數據，歸屬于聚合型數據庫。聚合型數據庫存儲模型：用類似Json的格式表示如下：相對于Mysql關系型數據庫，MongoDB這類nosql適用于數據模型簡單，性能要求高的場合。什么MongoDB使用B-樹，MongoDB是一種nosql，也存儲在磁盤上，被設計用在數據模型簡單，性能要求高的場合。性能要求高，看看B/B+樹的區別第一點。我們說過，盡可能少的磁盤IO是提高性能的有效手段。MongoDB是聚合型數據庫，而B-樹恰好key和data域聚合在一起。為什么Mysql使用B+樹?Mysql是一種關系型數據庫，區間訪問是常見的一種情況，而B-樹并不支持區間訪問（可參見上圖），而B+樹由于數據全部存儲在葉子節點，并且通過指針串在一起，這樣就很容易的進行區間遍歷甚至全部遍歷。B+樹葉節點兩兩相連可大大增加區間訪問性，可使用在范圍查詢等，而B-樹每個節點key和data在一起，則無法區間查找B+樹的查詢效率更加穩定，數據全部存儲在葉子節點，查詢時間復雜度固定為O(logn)B+樹更適合外部存儲。由于內節點無data域，每個節點能索引的范圍更大更精確mysql主鍵索引和普通索引的區別主鍵索引（primarykey）主鍵索引（PK）也稱為聚集索引(clusteredindex)。索引和數據存儲在一起，都存儲在同一個B+tree中的葉子節點,一般主鍵索引都是一級索引。普通索引（secondaryindex）普通索引（secondaryindex）也稱為二級索引。二級索引樹的葉子節點存儲的是主鍵而不是數據。也就是說，在找到索引后，得到對應的主鍵，再回到一級索引中找主鍵對應的數據記錄。InnoDB聚集索引和普通索引有什么差異？如果表定義了PK，則PK就是聚集索引。如果表沒有定義PK，則第一個notNULLunique列是聚集索引。否則，InnoDB會創建一個隱藏的row-id作為聚集索引。InnoDB普通索引的葉子節點存儲主鍵值，所以PK查詢非常快，直接定位行記錄。InnoDB一級索引和二級索引的關系：回表一級索引可以單獨存在，二級索引不能單獨存在，必須依附于一級索引，這叫做“回表”。在這里我給大家舉個案例，有一張名為t的表，表結構為：t(idPK,nameKEY,sex,flag);id是聚集索引，name是普通索引。t表中有四條記錄：1,shenjian, m, A

3,zhangsan, m, A

5,lisi, m, A

9,wangwu, f, B兩個B+樹索引分別如上圖：（1）id為PK，聚集索引，葉子節點存儲行記錄；（2）name為KEY，普通索引，葉子節點存儲PK值，即id；既然從普通索引無法直接定位行記錄，那普通索引的查詢過程是怎么樣的呢？通常情況下，需要掃碼兩遍索引樹。例如：select*fromtwherename='lisi';是如何執行的呢？

如粉紅色路徑，需要掃碼兩遍索引樹：（1）先通過普通索引定位到主鍵值id=5；（2）在通過聚集索引定位到行記錄；這就是所謂的回表查詢，先定位主鍵值，再定位行記錄，它的性能較掃一遍索引樹更低。堆組織表(HOT)和索引組織表(IOT)的區別堆表數據存放在數據里面,索引存放在索引里。堆就是無序數據的集合,索引就是將數據變得有序,在索引中鍵值有序,數據還是無序的堆表中,主鍵索引和普通索引一樣的,葉子節點存放的是指向堆表中數據的指針（可以是一個頁編號加偏移量）,指向物理地址,沒有回表的說法堆表中,主鍵和普通索引基本上沒區別,和非空的唯一索引沒區別索引組織表mysql使用的是innodb存儲引擎,所以堆表一帶而過,重點關注索引組織表。對于主鍵的索引,頁子節點存放了一整行所有數據,其他索引稱為輔助索引(二級索引),它的頁子節點只是存放了鍵值和主鍵值主鍵包含了一張表的所有數據,因為主鍵索引的頁子節點中保存了每一行的完整記錄,包括所有列。如果沒有主鍵,MySQL會自動幫你加一個主鍵,但是對用戶不可見innodb中數據存放在聚集索引中,換言之,按照主鍵的方式來組織數據的其他索引(唯一索引,普通索引)的頁子節點存放該索引列的鍵值和主鍵值不管是什么索引非頁子節點存放的存放的就是鍵值和指針,不存數據,這個指針在innodb中是6個bit,鍵值就看數據大小了為什么主鍵查詢比二級索引查詢來的快主鍵索引里面包含了每一行完整的數據,只要找到那個主鍵就是找到那個記錄,二級索引,如果查詢的列不是索引列,走那個列的索引找到后還要去根據索引里保存的主鍵去找查詢列的內容,這里多了一步,這種查找叫書簽查找或者回表,如果一個高度為3的樹,本來查只要查三個頁,走二級索引就要查六個頁。mysqlB-Tree索引使用案例單列索引使用管理#語法： createindexindex_nameontable_name(col_name); altertabletable_nameaddindexindex_name(col_name); #案例： useyzjtestdb; createindexyzjtest_m1_inx_nameonyzjtestdb.yzjtest_m1(NAME); altertableyzjtestdb.yzjtest_m1addindexyzjtest_m1_inx_tel(TEL); #查看已創建索引： showindexfromyzjtestdb.yzjtest_m1; #索引使用： explainselect*fromyzjtestdb.yzjtest_m1wherename='yzjtest30000010'; explainselect*fromyzjtestdb.yzjtest_m1wheretel=; #索引刪除：dropindexyzjtest_m1_inx_nameonyzjtest_m1;dropindexyzjtest_m1_inx_telonyzjtest_m1;復合索引使用管理#語法： createindexindex_nameontable_name(col_name1,col_name2);; altertabletable_nameaddindexindex_name(col_name1,col_name2); #案例： useyzjtestdb; createindexyzjtest_m1_inx_name_telonyzjtestdb.yzjtest_m1(NAME,TEL); altertableyzjtestdb.yzjtest_m1addindexyzjtest_m1_inx_name_tel(NAME,TEL); #查看已創建索引： showindexfromyzjtestdb.yzjtest_m1; #索引使用： explainselect*fromyzjtestdb.yzjtest_m1wherename='yzjtest30000010' andtel=; #索引刪除：dropindexyzjtest_m1_inx_name_telonyzjtest_m1;mysql第三天課程mysqlDML語言官方介紹DataManipulationStatements（DML）語言介紹其語句包括動詞select、insert、update、delete。它們分別用于查詢、添加、修改和刪除，也稱為動作查詢語言。DataManipulationStatements數據操作語言官方文檔地址/doc/refman/5.7/en/sql-data-manipulation-statements.htmlmysqlDML語言-insertintoinsertinto語句語法#語法一：INSERTINTOtable_name(field1,field2,...fieldN)VALUES(value1,value2,...valueN);#語法二：INSERTINTOtable_name(field1,field2,...fieldN)VALUES(value1,value2,...valueN),(value1,value2,...valueN),(value1,value2,...valueN);#語法三：INSERTINTOtable_nameVALUES(value1,value2,...valueN);insertinto語句案例#創建一張學生表：useyzjtestdb;createtableifnotexistsyzjtestdb.students(idint(5)primarykeycomment'學生學號',namevarchar(10)notnullcomment'學生姓名',agetinyintnotnullcomment'學生年齡',genderenum('男','女','保密')comment'學生性別') engine=innodbdefaultcharset=utf8comment'員工表';#插入數據：insertintoyzjtestdb.studentsvalues(1,'漩渦鳴人',16,'男');commit;insertintoyzjtestdb.students(id,name,age)values(2,'宇智波佐助',16);commit;insertintoyzjtestdb.studentsvalues(3,'卡卡西',25,'男'),(4,'李洛克',16,'男'),(5,'宇智波斑',100,'男'),(6,'春野櫻',16,'女');commit;mysqlDML語言-updateupdate語句語法UPDATEtable_nameSETfield1=new-value1[,field2=new-value2][WHEREClause]update語句案例#將名字為漩渦鳴人的列數據修改成宇智波鼬：UPDATEyzjtestdb.studentsSETname='宇智波鼬'wherename='漩渦鳴人';commit;#將性別為男的學員年齡都+10：UPDATEyzjtestdb.studentsSETage=age+10wheregender='男'andage<50;commit;select*fromyzjtestdb.students;#修改性別為NULL的學員的名字和年齡：UPDATEyzjtestdb.studentsSETname='大蛇丸',age=127wheregenderisnull;mysqlDML語言-deletedelete語句語法DELETEFROMtable_name[WHEREClause]delete語句案例#刪除性別為null的學生,年齡大于100的學生：deletefromyzjtestdb.studentswheregenderisnullandage>100;commit;select*fromyzjtestdb.students;#刪除性別為男的學生或者年齡大于50的學生：deletefromyzjtestdb.studentswheregender='男'orage>50;commit;select*fromyzjtestdb.students;mysqlDML語言-selectselect語句語法、基本語法select字段/表達式from表名/視圖名where查詢條件;#selct子句:where 查詢條件groupby 在字段內不同的數據分一組having 分組以后對于數據的過濾limit 對于返回數據行數的限制orderby[asc|desc] 排序asc:升序，默認desc:降序#select表達式:變量 select10+20; --查詢運算 select10*20; select10-20; select10/20; selectnow(); --查詢當前時間 from子句 select*fromyzjtestdb.yg; --全列 selectemailfromyzjtestdb.yg; --單列 selectemail,job_idfromyzjtestdb.yg; --多列 select*fromrwxx,ssgj; --多表 select*fromrwxxx1,ssgjj1; --別名 select*fromrwxxx1,ssgjj1where=; --連接查詢列連接 selectname,concat(name,'-',country,'-',skills)fromssgj; selectname,concat(name,'-',country,'-',skills)"合并顯示"fromssgj; 虛擬表dual,提高兼容性 select1+1fromdual;selectnow()fromdual; SQL語句編寫規則 大小寫不敏感;SQL語句可以占用多行,以分號結束，關鍵字不能拆成多行;SQL運算符等于= ：id=15;大于> ：id>15;小于<：id<15;大于等于>=：id>=15;小于等于<=：id<=15;不等于<>：id<>15;不等于!=：id!=15;是空：isnull;非空 ：isnotnull;模糊查詢[not]like：字段like"%北京市%";范圍內[not]between ：字段between1and15;在什么范圍值內[not]in:字段in('范圍值1','范圍值2','范圍值3');#案例:usenaruto;select*fromrwxxwhereage=16;select*fromrwxxwhereage>16;select*fromrwxxwhereage<16;select*fromrwxxwhereage<>16;select*fromrwxxwhereage>=16;select*fromrwxxwhereage<=16;select*fromrwxxwhereagebetween16and25;select*fromrwxxwhereagenotbetween16and25;select*fromrwxxwhereage>=16andage<=25;select*fromrwxxwhereage>=16&&age<=25;select*fromrwxxwhereagein(16,17,28,100);select*fromrwxxwhereagenotin(16,17,28,100);select*fromrwxxwherenamelike"%宇智波%";select*fromrwxxwherenamenotlike"%宇智波%";邏輯運算非：not與：and,&&或：or#組合查詢案例:usenaruto;select*fromrwxxwhereage=16andgender='男';select*fromrwxxwhereage=16orname='卡卡西';select*fromssgjwhereskillsisnull;select*fromssgjwhereskillsisnotnull;模糊查詢like精確查詢：=模糊查詢：likeA% ：A開頭 %A：A結尾%A%：包含A%A%C%：包含A和CA%C：A開頭C結尾#模糊查詢案例:selecttelfromyzjtestdb.yzjtest_ygwheretellike'137%';selectJOBTITLEfromyzjtestdb.yzjtest_ygwhereJOBTITLElike'銷售%';selectregionfromyzjtestdb.yzjtest_ygwhereregionlike'%海%';查詢分組與排序groupby :分組orderby :排序#groupby分組groupby列{asc升序|desc降序},{withrollup}組內聚合計算#分組常用到的函數:max : 最大值min : 最小值avg : 平均值sum : 列/表達式總和count: 行數總和#分組案例:案例1：統計（yzjtest_sales銷售表）本月每個（商品消費分類、GOODS）銷量單數--不同類別商品分組selectcount(*),GOODSfromyzjtest_salesgroupbygoods;--默認升序selectcount(*),GOODSfromyzjtest_salesgroupby2orderby1; --降序 selectcount(*),GOODSfromyzjtest_salesgroupby2orderby1desc;--限制輸出一行 selectcount(*),GOODSfromyzjtest_salesgroupby2orderby1desclimit1;--案例2：統計（yg員工表）本月共發了多少工資（工資、SALARY）selectsum(SALARY)fromyg;--案例3：統計（yzjtest_yg員工表）不同地區（REGION）、不同部門（DEPT）共發了多少工資（SALARY）selectREGION,DEPT,sum(SALARY)fromyzjtest_yggroupby1,2;--案例4：統計（yzjtest_yg員工表）不同地區（REGION）、共發了多少工資（SALARY）selectREGION,sum(SALARY)fromyzjtest_yggroupby1;--案例5：withrollup利用組合條件進行排序后，再次統計selectREGION,DEPT,sum(SALARY)fromyzjtest_yggroupby1,2withrollup;--案例6：統計（yzjtest_yg員工表）不同地區（REGION）、發了總數工資（SALARY）超過兩百五十萬selectsum(SALARY),REGIONfromyzjtest_yggroupbyREGIONhavingsum(SALARY)>2500000;限制行數limit :限制行數#limit案例:案例1：查詢YG表限制輸出5行select*fromyzjtestdb.yglimit5;案例2：查詢YG表從第3行開始，顯示4行select*fromyzjtestdb.yglimit3,4;去除重復記錄distinct :去除重復記錄#distinct案例:案例1：去除yzjtest_yg表sex字段重復記錄selectsexfromyzjtest_yg; --先查看重復記錄selectdistinctsexfromyzjtest_yg; --sex字段去重union和unionallunion :無重并集，把多個結果組合并后去重unionall:有重并集，把多個結果組合并不去重#union案例:案例1：查詢rwxx表、ssgj表的name字段合并去重selectnamefromnaruto.rwxxunionselectnamefromnaruto.ssgj;#unionall案例:案例1：查詢rwxx表、ssgj表的name字段合并不去重selectnamefromnaruto.rwxxunionallselectnamefromnaruto.ssgj;forupdateforupdate :鎖表，悲觀鎖,生產環境切勿使用這條語句案例1：將rwxx表鎖住select*fromnaruto.rwxxforupdate;mysqlDML語言-select高級查詢連接查詢和子查詢select高級查詢之連接查詢joinSQLJOIN子句用于把來自兩個或多個表的行結合起來返回數據，基于這些表之間的共同字段。join連接分為3大類：自然連接NATURALJOIN：自然連接是在兩張表中尋找那些數據類型和列名都相同的字段，然后自動地將他們連接起來，并返回所有符合條件按的結果。內連接INNERJOIN：如果表中有至少一個匹配，則返回行外連接左外連接LEFTJOIN：即使右表中沒有匹配，也從左表返回所有的行右外連接RIGHTJOIN：即使左表中沒有匹配，也從右表返回所有的行全外連接FULLJOIN：只要其中一個表中存在匹配，則返回行（mysql不支持）#SQLjoin案例:案例1：自然連接NATURALJOINselect*fromnaruto.rwxxnaturaljoinnaruto.ssgj;案例2：內連接INNERJOINselect*fromnaruto.rwxxainnerjoinnaruto.ssgjbonname=; --寫法1select*fromnaruto.rwxxa,naruto.ssgjbwhere=; --寫法2select*fromnaruto.rwxxainnerjoinnaruto.ssgjbusing(name); --寫法3select,a.age,b.country,b.skillsfromnaruto.rwxxainnerjoinnaruto.ssgjbon=; 案例3：左外連接leftjoin/leftouterjoinselect*fromnaruto.rwxxrleftouterjoinnaruto.ssgjson=;案例4：又外連接rightjoin/rightouterjoinselect*fromnaruto.rwxxrrightouterjoinnaruto.ssgjson=;案例5：交叉連接crossjoinselect*fromnaruto.rwxxrcrossjoinnaruto.ssgjs;select高級查詢之子查詢selct子查詢是將一個查詢語句嵌套在另一個查詢語句中，在特