MySQL高級(jí)DBA核心項(xiàng)目

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

<table_name>;mysql>showindexfrom<table_name>;mysql數(shù)據(jù)類(lèi)型mysql支持的多種數(shù)據(jù)類(lèi)型主要有：數(shù)值數(shù)據(jù)類(lèi)型、日期/時(shí)間類(lèi)型、字符串類(lèi)型。數(shù)值類(lèi)型-整型浮點(diǎn)數(shù)&定點(diǎn)數(shù)注：定點(diǎn)數(shù)以字符串形式存儲(chǔ)，對(duì)精度要求高時(shí)使用decimal較好；盡量避免對(duì)浮點(diǎn)數(shù)進(jìn)行減法和比較運(yùn)算。

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

3,zhangsan, m, A

5,lisi, m, A

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

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