文章目錄一、參考文獻二、基本格式三、基本操作3.1 插入3.2 查詢3.3 更新3.4 刪除3.4.1 delete3.4.2 drop3.4.3 truncate四、進階操作4.1 操作符like、通配符4.2 聯(lián)合表操作4.2.1 舉例4.3 嵌套操作4.4 SQL常用函數(shù)五、數(shù)據(jù)庫索引六、執(zhí)行查詢語句期間發(fā)生了什么6.1 MySQL 的兩層架構(gòu)6.1.1 Server 層6.1.2 存儲引擎層1 Memory2 MylSAM3 InnoDB6.2 詳解InnoDB存儲引擎6.2.1 Buffer Pool 緩沖池6.2.2 undo 日志文件6.2.3 redo 日志文件6.2.4 bin log文件6.2.5 后臺線程一、參考文獻參考菜鳥教程二、基本格式select * from表名left join表名xon條件1where條件2group by … having … order by …執(zhí)行順序from _where __group by _ 對結(jié)果集進行分組having __主要和GROUP BY子句配合使用用于過濾聚合值select 查看結(jié)果集中的哪個列或列的計算結(jié)果DISTINCT去重order by __LIMIT舉例從多個班級中選出這些條件的班級——數(shù)學平均成績大于75分、平均成績按從高到低排名最前三的班級。SQLselect 班級, avg(數(shù)學成績) as 數(shù)學平均成績 where 數(shù)學成績 is not null group by 班級 having 數(shù)學平均成績 75 order by 數(shù)學平均成績 desc limit 0, 3。執(zhí)行步驟執(zhí)行 FROM 子句, 從學生成績表中組裝數(shù)據(jù)源的數(shù)據(jù)。執(zhí)行 WHERE 子句, 篩選學生成績表中所有學生的數(shù)學成績不為 NULL 的數(shù)據(jù) 。執(zhí)行 GROUP BY 子句, 把學生成績表按 “班級” 字段進行分組。計算 avg 聚合函數(shù), 按group by的班級分組求出 數(shù)學平均成績。執(zhí)行 HAVING 子句, 篩選出班級 數(shù)學平均成績大于 75 分的。執(zhí)行SELECT語句選擇數(shù)據(jù)繼續(xù)執(zhí)行后面幾個步驟。執(zhí)行 ORDER BY 子句, 把最后的結(jié)果按 “數(shù)學平均成績” 進行排序。執(zhí)行LIMIT 限制僅返回3條數(shù)據(jù)。結(jié)合ORDER BY 子句即返回所有班級中數(shù)學平均成績的前三的班級及其數(shù)學平均成績。三、基本操作3.1 插入INSERT INTO table_name (column_name1,column_name2,…) VALUES (value1,value2,…)3.2 查詢查詢某些字段SELECT column_name1,column_name2 FROM table_name;3.3 更新UPDATE table_name SET column1value1,column2value2,… WHERE some_columnsome_value;3.4 刪除3.4.1 deletedelete語句執(zhí)行刪除的過程是從表中刪除行并且同時將行刪除操作作為事務(wù)記錄在日志中保存以便進行進行回滾操作。請注意添加where如果省略了 WHERE 子句所有的記錄都將被刪除例如DELETE FROM table_name WHERE some_columnsome_value;3.4.2 dropdrop會刪除內(nèi)容和定義釋放空間。即把整個表去掉以后要新增數(shù)據(jù)是不可能的只能新增一個表。drop語句將刪除表的結(jié)構(gòu)被依賴的約束constrain)、觸發(fā)器trigger)、索引index)依賴于該表的存儲過程/函數(shù)將被保留但其狀態(tài)會變?yōu)閕nvalid。drop table 表名稱 eg: drop table dbo.Sys_Test3.4.3 truncatetruncate (清空表中數(shù)據(jù))不刪除定義保留表的數(shù)據(jù)結(jié)構(gòu)、刪除內(nèi)容、釋放空間、重置主鍵/自動增長列計數(shù)器。與drop不同truncate 只是清空表數(shù)據(jù)。注意truncate只能清空表數(shù)據(jù)不能刪除指定行數(shù)據(jù)。truncate table 表名稱比如runcate table dbo.Sys_Test四、進階操作4.1 操作符like、通配符like1選取 name 以字母 “G” 開始的所有客戶SELECT * FROM Websites WHERE name LIKE ‘G%’;2選取 name 以字母 “k” 結(jié)尾的所有客戶SELECT * FROM Websites WHERE name LIKE ‘%k’;3選取 name 包含模式 “oo” 的所有客戶SELECT * FROM Websites WHERE name LIKE ‘%oo%’;4選取 name 不包含模式 “oo” 的所有客戶SELECT * FROM Websites WHERE name NOT LIKE ‘%oo%’;通配符通配符描述例子%替代 0 個或多個字符選取 url 以字母 “https” 開始的所有網(wǎng)站SELECT * FROM Websites WHERE url LIKE ‘https%’_替代一個字符選取 name 以 “G” 開始然后是一個任意字符然后是 “o”然后是一個任意字符然后是 “l(fā)e” 的所有網(wǎng)站SELECT * FROM Websites WHERE name LIKE ‘G_o_le’[charlist]MySQL不支持 字符列中的任何單一字符1選取 name 以 “G”、“F” 或 “s” 開始的所有網(wǎng)站SELECT * FROM Websites WHERE name REGEXP ‘^ [GFs]’2選取 name 以 A 到 H 字母開頭的網(wǎng)站SELECT * FROM Websites WHERE name REGEXP ‘^ [A-H]’[^charlist] 或 [!charlist]MySQL不支持不在字符列中的任何單一字符選取 name 不以 A 到 H 字母開頭的網(wǎng)站SELECT * FROM Websites WHERE name REGEXP ‘^ [^A-H]’4.2 聯(lián)合表操作inner join返回兩張表的交集部分inner join joinleft join以左表為主表返回所有左表的數(shù)據(jù)left outer join left joinright join以右表為主表返回所有右表的數(shù)據(jù)right outer join right joinFULL JOIN完全連接可看作是兩張表的并集。如果匹配列的值在兩個表中匹配那么返回數(shù)據(jù)行否則返回空值。4.2.1 舉例參考知乎文章1person表2score表舉例select * from person t1 left join score t2 on t1.uid t2.uidselect * from person t1 join scorep t2 on t1.uid t2.uidselect * from person t1 full join scorep t2 on t1.uid t2.uid4.3 嵌套操作略代碼盡量避免嵌套原因難寫一旦寫錯就很難定位還可能把數(shù)據(jù)庫跑死。SQL調(diào)試難只能自己一步步執(zhí)行子語句調(diào)試。長SQL后期想跟隨業(yè)務(wù)修改太難了。長SQL過段時間連自己都看不懂重新看懂跟又開發(fā)了一遍似的。復(fù)雜 SQL 還會影響數(shù)據(jù)庫移植在一個數(shù)據(jù)庫上使用的函數(shù)放到另一數(shù)據(jù)庫可能不支持。4.4 SQL常用函數(shù)求平均值avg()求和sum()求總行數(shù)count求最大值max()求最小值min()求第n1名到第nm名limit n,m五、數(shù)據(jù)庫索引參考前面寫的文章索引六、執(zhí)行查詢語句期間發(fā)生了什么參考博客一條SQL查詢語句是如何執(zhí)行的MySQL是典型的 C/S架構(gòu)客戶端/服務(wù)器架構(gòu)客戶端進程向服務(wù)端進程發(fā)送一段文本MySQL指令服務(wù)器進程進行語句處理然后執(zhí)行并返回結(jié)果。6.1 MySQL 的兩層架構(gòu)6.1.1 Server 層Server 層是MySQL的核心功能模塊負責建立連接、分析和執(zhí)行 SQL主要包括連接器查詢緩存、解析器、預(yù)處理器、優(yōu)化器、執(zhí)行器等。另外所有的內(nèi)置函數(shù)如日期、時間、數(shù)學和加密函數(shù)等和所有跨存儲引擎的功能如存儲過程、觸發(fā)器、視圖等都在 Server 層實現(xiàn)。執(zhí)行一條 SQL 查詢語句期間發(fā)生了什么連接器建立連接管理連接。建立連接之后除非客戶端主動斷開連接否則服務(wù)器會等待客戶端發(fā)送請求。但是線程的創(chuàng)建和保持是需要消耗服務(wù)器資源的因此服務(wù)器會把長時間不活動的客戶端連接斷開。校驗用戶身份查詢緩存查詢語句如果命中查詢緩存則直接返回否則繼續(xù)往下執(zhí)行。MySQL 8.0 已刪除該模塊。解析 SQL通過解析器對 SQL 查詢語句進行如下操作方便后續(xù)模塊讀取表名、字段、語句類型詞法分析。就是把一條完整的SQL語句打碎成一個個單詞比如MySQL會把SELECT識別成查詢語句把字符串t_user識別成“表名 t_user”把字符串user_name識別成“列 user_name。語法分析。語法分析器會根據(jù)語法規(guī)則生成解析樹從而判斷SQL 語句是否滿足語法比如單引號是否閉合關(guān)鍵詞拼寫是否正確等。構(gòu)建語法樹。解析樹執(zhí)行 SQL執(zhí)行 SQL 共有三個階段預(yù)處理階段檢查表或字段是否存在將 select * 中的 * 符號擴展為表的所有列。優(yōu)化階段基于查詢成本的考慮 查詢優(yōu)化器會選擇成本最小的執(zhí)行計劃MySQL作者擔心我們寫的SQL太垃圾所以有設(shè)計出查詢優(yōu)化器輔助我們提高查詢效率。查詢優(yōu)化器會根據(jù)解析樹生成不同的執(zhí)行計劃Execution Plan然后選擇一種成本最小的執(zhí)行計劃。這里的成本指【I/O成本 CPU成本】IO 成本: 即從磁盤把數(shù)據(jù)加載到內(nèi)存的成本默認情況下讀取數(shù)據(jù)頁的 IO 成本是 1MySQL 是以頁的形式讀取數(shù)據(jù)的即當用到某個數(shù)據(jù)時并不會只讀取這個數(shù)據(jù)而會把這個數(shù)據(jù)相鄰的數(shù)據(jù)也一起讀到內(nèi)存中這就是有名的程序局部性原理所以 MySQL 每次會讀取一整頁一頁的成本就是 1。所以 IO 的成本主要和頁的大小有關(guān)CPU 成本將數(shù)據(jù)讀入內(nèi)存后還要檢測數(shù)據(jù)是否滿足條件和排序等 CPU 操作的成本顯然它與行數(shù)有關(guān)默認情況下檢測記錄的成本是 0.2。執(zhí)行階段根據(jù)執(zhí)行計劃執(zhí)行 SQL 查詢語句從存儲引擎讀取記錄返回給客戶端。存儲引擎處理數(shù)據(jù)6.1.2 存儲引擎層補充知識:MySQL支持 InnoDB、MyISAM、Memory 等多個存儲引擎不同的存儲引擎共用一個 Server 層。從 MySQL 5.5 版本開始MySQL默認InnoDB為存儲引擎 。我們常說的索引數(shù)據(jù)結(jié)構(gòu)就是由存儲引擎層實現(xiàn)的。不同的存儲引擎支持的索引類型也不相同比如 InnoDB 支持索引類型是 B樹且是默認使用。在數(shù)據(jù)表中創(chuàng)建的主鍵索引和二級索引默認使用的是 B 樹索引。存儲引擎層負責數(shù)據(jù)存儲和提取比如數(shù)據(jù)存儲在內(nèi)存還是磁盤、怎么從表里讀取數(shù)據(jù)怎么把數(shù)據(jù)寫入表中。表是由一行一行的記錄組成的但這只是邏輯上的概念其實只是看上去是這樣而已。為什么需要多種存儲引擎不同存儲引擎特性不同存儲引擎只是讀寫MySQL數(shù)據(jù)的插件可以根據(jù)不同目隨意更換。如何選擇存儲引擎1對數(shù)據(jù)一致性要求比較高需要事務(wù)支持可以選擇InnoDB。2如果數(shù)據(jù)查詢多更新少對查詢性能要求比較高可以選擇MyISAM。3如果需要一個用于查詢的臨時表可以選擇Memory。1 MemoryMemory存儲引擎以前也稱堆引擎它將所有數(shù)據(jù)存儲在RAM內(nèi)存中以便快速訪問。特點把數(shù)據(jù)放在內(nèi)存里面讀寫的速度很快。但是數(shù)據(jù)庫重啟或者崩潰數(shù)據(jù)會全部消失只適合做臨時表。2 MylSAM應(yīng)用范圍比較小表級鎖限制了讀/寫性能因此在Web和數(shù)據(jù)倉庫配置中通常用于只讀或以讀為主的工作。特點:支持表級別的鎖插入和更新會鎖表不支持事務(wù)擁有較高的插入insert和查詢select速度存儲了表的行數(shù)count速度更快。怎么快速向數(shù)據(jù)庫插入100萬條數(shù)據(jù)可以先用MylSAM插入數(shù)據(jù)然后修改存儲引擎為InnoDB。ALTER TABLE 表名 ENGINE 存儲引擎名稱;3 InnoDBMySQL 5.7及更新版中的默認存儲引擎。InnoDB是事務(wù)安全兼容ACID它具有提交、回滾和崩潰恢復(fù)功能來保護用戶數(shù)據(jù)。InnoDB行級鎖和Oracle風格的一致非鎖讀提高了多用戶并發(fā)性。InnoDB將用戶數(shù)據(jù)存儲在聚集索引中以減少基于主鍵的常見查詢的I/O。為了保持數(shù)據(jù)完整性InnoDB還支持外鍵引用完整性約束。特點支持事務(wù)支持外鍵因此數(shù)據(jù)的完整性、一致性更高支持行級別的鎖和表級別的鎖支持讀寫并發(fā)寫不阻塞讀MVCC特殊的索引存放方式可以減少IO提升査詢效率。番外為什么MySQL越來越像OracleInnoDB是InnobaseOy公司開發(fā)的它和MySQL AB公司合作開源了InnoDB的代碼。但是MySQL的競爭對手Oracle把InnobaseOy收購了。后來2008年Sun公司開發(fā)Java語言的Sun收購了MySQL AB2009年Sun公司又被Oracle收購了所以MySQL和 InnoDB又是一家了。6.2 詳解InnoDB存儲引擎事務(wù)在InnoDB中從提交到完成的整個流程準備更新一條 SQL 語句MySQLinnodb會先去緩沖池BufferPool中去查找這條數(shù)據(jù)沒找到就會去磁盤中查找如果查找到就會將這條數(shù)據(jù)加載到緩沖池BufferPool中。在加載到 Buffer Pool 的同時會將這條數(shù)據(jù)的原始記錄保存到 undo 日志文件中。innodb 會在 Buffer Pool 中執(zhí)行更新操作。更新后的數(shù)據(jù)會記錄在 redo log buffer 中。提交事務(wù)時會將內(nèi)存 redo log buffer 中的數(shù)據(jù)寫入到磁盤的 redo log 文件中。提交事務(wù)時MySQL還會1將本次修改的數(shù)據(jù)記錄到 bin log文件中2將本次修改的bin log文件名和修改的內(nèi)容在bin log中的位置記錄到redo log中3在redo log中寫入 commit 標記標識本次事務(wù)被成功提交了。6.2.1 Buffer Pool 緩沖池緩沖池 Buffer Pool是InnoDB非常重要的組件。MySQL 的數(shù)據(jù)最終是存儲在磁盤中的有了 Buffer Pool第一次查詢時就會將查詢結(jié)果存到Buffer Pool之后再有請求時就會先從緩沖池中查詢沒查到再去磁盤中I/O查找然后在放到 Buffer Pool 中。6.2.2 undo 日志文件在準備更新一條語句的時候該條語句已經(jīng)被加載到 Buffer pool 中了實際上這里還會同時在 undo 日志文件記錄下更新前的值。為什么要記錄更新前的值Innodb 存儲引擎的最大特點就是支持事務(wù)如果本次更新失敗也就是事務(wù)提交失敗那么該事務(wù)中的所有的操作都必須回滾到執(zhí)行前的樣子也就是說當事務(wù)失敗的時候也不會對原始數(shù)據(jù)有影響6.2.3 redo 日志文件redo log buffer內(nèi)存緩存記錄將要做的一些操作。redo log磁盤文件記錄數(shù)據(jù)被修改后的樣子。MySQL 為了提高效率會將更新操作先放在內(nèi)存中去完成然后會在事務(wù)提交后 將其持久化到磁盤日志文件中。知識補充如果 redo log Buffer 刷入磁盤前MySQL宕機了緩存會丟失沒關(guān)系因為 MySQL 會認為本次事務(wù)是失敗的所以數(shù)據(jù)依舊是更新前的樣子沒有任何影響。如果 redo log Buffer 刷入磁盤后MySQL宕機了緩存會丟失也沒關(guān)系因為 redo log buffer 中的數(shù)據(jù)已經(jīng)被寫入到磁盤redo log了下次重啟時 MySQL 會將 redo log 文件內(nèi)容恢復(fù)到 Buffer Pool 中和 Redis 的持久化機制類似Redis 啟動時會檢查 RDB 或者 AOF 或者兩者都檢查根據(jù)持久化的文件將數(shù)據(jù)恢復(fù)到內(nèi)存中。刷入磁盤參數(shù)設(shè)置通過 innodb_flush_log_at_trx_commit 參數(shù)設(shè)置刷入磁盤0 表示不刷入磁盤1 表示立即刷入磁盤2 表示先刷到 os cache6.2.4 bin log文件bin log 記錄對數(shù)據(jù)庫的整個修改操作對主從復(fù)制非常有用bin log刷盤策略可以通過sync_bin log修改策略。為0表示提交事務(wù)后先寫入os cache數(shù)據(jù)不會直接到磁盤中如果宕機bin log數(shù)據(jù)會丟失。建議將sync_bin log設(shè)置為 1 表示直接將數(shù)據(jù)寫入到磁盤文件中。bin log在redo log中被記錄提交事務(wù)時MySQL還會1將本次修改的數(shù)據(jù)記錄到 bin log文件中2將本次修改的bin log文件名和修改的內(nèi)容在bin log中的位置記錄到redo log中3在redo log中寫入 commit 標記標識本次事務(wù)被成功提交了。如果數(shù)據(jù)剛被寫入到bin log文件數(shù)據(jù)庫宕機了數(shù)據(jù)會丟失嗎——不會丟失只要redo log最后沒有 commit 標記就說明本次的事務(wù)是失敗的但是數(shù)據(jù)已經(jīng)被記錄到redo log的磁盤文件中了MySQL 重啟時會將 redo log 中的數(shù)據(jù)恢復(fù)加載到Buffer Pool。6.2.5 后臺線程疑問上面僅描述了在內(nèi)存中的更新操作哪怕是宕機又恢復(fù)了也僅是將更新后的記錄加載到Buffer Pool中這時 MySQL 數(shù)據(jù)庫中的這條記錄依舊是舊值內(nèi)存數(shù)據(jù)依舊是臟數(shù)據(jù)MySQL怎么保持內(nèi)存和數(shù)據(jù)庫表數(shù)據(jù)統(tǒng)一的呢解答MySQL 有個后臺線程它會在某個時機將Buffer Pool 中的臟數(shù)據(jù)刷到磁盤表中保持內(nèi)存和數(shù)據(jù)庫數(shù)據(jù)統(tǒng)一。 專項攻克——MySQL語句與底層原理剖析
文章目錄一、參考文獻二、基本格式三、基本操作3.1 插入3.2 查詢3.3 更新3.4 刪除3.4.1 delete3.4.2 drop3.4.3 truncate四、進階操作4.1 操作符like、通配符4.2 聯(lián)合表操作4.2.1 舉例4.3 嵌套操作4.4 SQL常用函數(shù)五、數(shù)據(jù)庫索引六、執(zhí)行查詢語句期間發(fā)生了什么6.1 MySQL 的兩層架構(gòu)6.1.1 Server 層6.1.2 存儲引擎層1 Memory2 MylSAM3 InnoDB6.2 詳解InnoDB存儲引擎6.2.1 Buffer Pool 緩沖池6.2.2 undo 日志文件6.2.3 redo 日志文件6.2.4 bin log文件6.2.5 后臺線程一、參考文獻參考菜鳥教程二、基本格式select * from表名left join表名xon條件1where條件2group by … having … order by …執(zhí)行順序from _where __group by _ 對結(jié)果集進行分組having __主要和GROUP BY子句配合使用用于過濾聚合值select 查看結(jié)果集中的哪個列或列的計算結(jié)果DISTINCT去重order by __LIMIT舉例從多個班級中選出這些條件的班級——數(shù)學平均成績大于75分、平均成績按從高到低排名最前三的班級。SQLselect 班級, avg(數(shù)學成績) as 數(shù)學平均成績 where 數(shù)學成績 is not null group by 班級 having 數(shù)學平均成績 75 order by 數(shù)學平均成績 desc limit 0, 3。執(zhí)行步驟執(zhí)行 FROM 子句, 從學生成績表中組裝數(shù)據(jù)源的數(shù)據(jù)。執(zhí)行 WHERE 子句, 篩選學生成績表中所有學生的數(shù)學成績不為 NULL 的數(shù)據(jù) 。執(zhí)行 GROUP BY 子句, 把學生成績表按 “班級” 字段進行分組。計算 avg 聚合函數(shù), 按group by的班級分組求出 數(shù)學平均成績。執(zhí)行 HAVING 子句, 篩選出班級 數(shù)學平均成績大于 75 分的。執(zhí)行SELECT語句選擇數(shù)據(jù)繼續(xù)執(zhí)行后面幾個步驟。執(zhí)行 ORDER BY 子句, 把最后的結(jié)果按 “數(shù)學平均成績” 進行排序。執(zhí)行LIMIT 限制僅返回3條數(shù)據(jù)。結(jié)合ORDER BY 子句即返回所有班級中數(shù)學平均成績的前三的班級及其數(shù)學平均成績。三、基本操作3.1 插入INSERT INTO table_name (column_name1,column_name2,…) VALUES (value1,value2,…)3.2 查詢查詢某些字段SELECT column_name1,column_name2 FROM table_name;3.3 更新UPDATE table_name SET column1value1,column2value2,… WHERE some_columnsome_value;3.4 刪除3.4.1 deletedelete語句執(zhí)行刪除的過程是從表中刪除行并且同時將行刪除操作作為事務(wù)記錄在日志中保存以便進行進行回滾操作。請注意添加where如果省略了 WHERE 子句所有的記錄都將被刪除例如DELETE FROM table_name WHERE some_columnsome_value;3.4.2 dropdrop會刪除內(nèi)容和定義釋放空間。即把整個表去掉以后要新增數(shù)據(jù)是不可能的只能新增一個表。drop語句將刪除表的結(jié)構(gòu)被依賴的約束constrain)、觸發(fā)器trigger)、索引index)依賴于該表的存儲過程/函數(shù)將被保留但其狀態(tài)會變?yōu)閕nvalid。drop table 表名稱 eg: drop table dbo.Sys_Test3.4.3 truncatetruncate (清空表中數(shù)據(jù))不刪除定義保留表的數(shù)據(jù)結(jié)構(gòu)、刪除內(nèi)容、釋放空間、重置主鍵/自動增長列計數(shù)器。與drop不同truncate 只是清空表數(shù)據(jù)。注意truncate只能清空表數(shù)據(jù)不能刪除指定行數(shù)據(jù)。truncate table 表名稱比如runcate table dbo.Sys_Test四、進階操作4.1 操作符like、通配符like1選取 name 以字母 “G” 開始的所有客戶SELECT * FROM Websites WHERE name LIKE ‘G%’;2選取 name 以字母 “k” 結(jié)尾的所有客戶SELECT * FROM Websites WHERE name LIKE ‘%k’;3選取 name 包含模式 “oo” 的所有客戶SELECT * FROM Websites WHERE name LIKE ‘%oo%’;4選取 name 不包含模式 “oo” 的所有客戶SELECT * FROM Websites WHERE name NOT LIKE ‘%oo%’;通配符通配符描述例子%替代 0 個或多個字符選取 url 以字母 “https” 開始的所有網(wǎng)站SELECT * FROM Websites WHERE url LIKE ‘https%’_替代一個字符選取 name 以 “G” 開始然后是一個任意字符然后是 “o”然后是一個任意字符然后是 “l(fā)e” 的所有網(wǎng)站SELECT * FROM Websites WHERE name LIKE ‘G_o_le’[charlist]MySQL不支持 字符列中的任何單一字符1選取 name 以 “G”、“F” 或 “s” 開始的所有網(wǎng)站SELECT * FROM Websites WHERE name REGEXP ‘^ [GFs]’2選取 name 以 A 到 H 字母開頭的網(wǎng)站SELECT * FROM Websites WHERE name REGEXP ‘^ [A-H]’[^charlist] 或 [!charlist]MySQL不支持不在字符列中的任何單一字符選取 name 不以 A 到 H 字母開頭的網(wǎng)站SELECT * FROM Websites WHERE name REGEXP ‘^ [^A-H]’4.2 聯(lián)合表操作inner join返回兩張表的交集部分inner join joinleft join以左表為主表返回所有左表的數(shù)據(jù)left outer join left joinright join以右表為主表返回所有右表的數(shù)據(jù)right outer join right joinFULL JOIN完全連接可看作是兩張表的并集。如果匹配列的值在兩個表中匹配那么返回數(shù)據(jù)行否則返回空值。4.2.1 舉例參考知乎文章1person表2score表舉例select * from person t1 left join score t2 on t1.uid t2.uidselect * from person t1 join scorep t2 on t1.uid t2.uidselect * from person t1 full join scorep t2 on t1.uid t2.uid4.3 嵌套操作略代碼盡量避免嵌套原因難寫一旦寫錯就很難定位還可能把數(shù)據(jù)庫跑死。SQL調(diào)試難只能自己一步步執(zhí)行子語句調(diào)試。長SQL后期想跟隨業(yè)務(wù)修改太難了。長SQL過段時間連自己都看不懂重新看懂跟又開發(fā)了一遍似的。復(fù)雜 SQL 還會影響數(shù)據(jù)庫移植在一個數(shù)據(jù)庫上使用的函數(shù)放到另一數(shù)據(jù)庫可能不支持。4.4 SQL常用函數(shù)求平均值avg()求和sum()求總行數(shù)count求最大值max()求最小值min()求第n1名到第nm名limit n,m五、數(shù)據(jù)庫索引參考前面寫的文章索引六、執(zhí)行查詢語句期間發(fā)生了什么參考博客一條SQL查詢語句是如何執(zhí)行的MySQL是典型的 C/S架構(gòu)客戶端/服務(wù)器架構(gòu)客戶端進程向服務(wù)端進程發(fā)送一段文本MySQL指令服務(wù)器進程進行語句處理然后執(zhí)行并返回結(jié)果。6.1 MySQL 的兩層架構(gòu)6.1.1 Server 層Server 層是MySQL的核心功能模塊負責建立連接、分析和執(zhí)行 SQL主要包括連接器查詢緩存、解析器、預(yù)處理器、優(yōu)化器、執(zhí)行器等。另外所有的內(nèi)置函數(shù)如日期、時間、數(shù)學和加密函數(shù)等和所有跨存儲引擎的功能如存儲過程、觸發(fā)器、視圖等都在 Server 層實現(xiàn)。執(zhí)行一條 SQL 查詢語句期間發(fā)生了什么連接器建立連接管理連接。建立連接之后除非客戶端主動斷開連接否則服務(wù)器會等待客戶端發(fā)送請求。但是線程的創(chuàng)建和保持是需要消耗服務(wù)器資源的因此服務(wù)器會把長時間不活動的客戶端連接斷開。校驗用戶身份查詢緩存查詢語句如果命中查詢緩存則直接返回否則繼續(xù)往下執(zhí)行。MySQL 8.0 已刪除該模塊。解析 SQL通過解析器對 SQL 查詢語句進行如下操作方便后續(xù)模塊讀取表名、字段、語句類型詞法分析。就是把一條完整的SQL語句打碎成一個個單詞比如MySQL會把SELECT識別成查詢語句把字符串t_user識別成“表名 t_user”把字符串user_name識別成“列 user_name。語法分析。語法分析器會根據(jù)語法規(guī)則生成解析樹從而判斷SQL 語句是否滿足語法比如單引號是否閉合關(guān)鍵詞拼寫是否正確等。構(gòu)建語法樹。解析樹執(zhí)行 SQL執(zhí)行 SQL 共有三個階段預(yù)處理階段檢查表或字段是否存在將 select * 中的 * 符號擴展為表的所有列。優(yōu)化階段基于查詢成本的考慮 查詢優(yōu)化器會選擇成本最小的執(zhí)行計劃MySQL作者擔心我們寫的SQL太垃圾所以有設(shè)計出查詢優(yōu)化器輔助我們提高查詢效率。查詢優(yōu)化器會根據(jù)解析樹生成不同的執(zhí)行計劃Execution Plan然后選擇一種成本最小的執(zhí)行計劃。這里的成本指【I/O成本 CPU成本】IO 成本: 即從磁盤把數(shù)據(jù)加載到內(nèi)存的成本默認情況下讀取數(shù)據(jù)頁的 IO 成本是 1MySQL 是以頁的形式讀取數(shù)據(jù)的即當用到某個數(shù)據(jù)時并不會只讀取這個數(shù)據(jù)而會把這個數(shù)據(jù)相鄰的數(shù)據(jù)也一起讀到內(nèi)存中這就是有名的程序局部性原理所以 MySQL 每次會讀取一整頁一頁的成本就是 1。所以 IO 的成本主要和頁的大小有關(guān)CPU 成本將數(shù)據(jù)讀入內(nèi)存后還要檢測數(shù)據(jù)是否滿足條件和排序等 CPU 操作的成本顯然它與行數(shù)有關(guān)默認情況下檢測記錄的成本是 0.2。執(zhí)行階段根據(jù)執(zhí)行計劃執(zhí)行 SQL 查詢語句從存儲引擎讀取記錄返回給客戶端。存儲引擎處理數(shù)據(jù)6.1.2 存儲引擎層補充知識:MySQL支持 InnoDB、MyISAM、Memory 等多個存儲引擎不同的存儲引擎共用一個 Server 層。從 MySQL 5.5 版本開始MySQL默認InnoDB為存儲引擎 。我們常說的索引數(shù)據(jù)結(jié)構(gòu)就是由存儲引擎層實現(xiàn)的。不同的存儲引擎支持的索引類型也不相同比如 InnoDB 支持索引類型是 B樹且是默認使用。在數(shù)據(jù)表中創(chuàng)建的主鍵索引和二級索引默認使用的是 B 樹索引。存儲引擎層負責數(shù)據(jù)存儲和提取比如數(shù)據(jù)存儲在內(nèi)存還是磁盤、怎么從表里讀取數(shù)據(jù)怎么把數(shù)據(jù)寫入表中。表是由一行一行的記錄組成的但這只是邏輯上的概念其實只是看上去是這樣而已。為什么需要多種存儲引擎不同存儲引擎特性不同存儲引擎只是讀寫MySQL數(shù)據(jù)的插件可以根據(jù)不同目隨意更換。如何選擇存儲引擎1對數(shù)據(jù)一致性要求比較高需要事務(wù)支持可以選擇InnoDB。2如果數(shù)據(jù)查詢多更新少對查詢性能要求比較高可以選擇MyISAM。3如果需要一個用于查詢的臨時表可以選擇Memory。1 MemoryMemory存儲引擎以前也稱堆引擎它將所有數(shù)據(jù)存儲在RAM內(nèi)存中以便快速訪問。特點把數(shù)據(jù)放在內(nèi)存里面讀寫的速度很快。但是數(shù)據(jù)庫重啟或者崩潰數(shù)據(jù)會全部消失只適合做臨時表。2 MylSAM應(yīng)用范圍比較小表級鎖限制了讀/寫性能因此在Web和數(shù)據(jù)倉庫配置中通常用于只讀或以讀為主的工作。特點:支持表級別的鎖插入和更新會鎖表不支持事務(wù)擁有較高的插入insert和查詢select速度存儲了表的行數(shù)count速度更快。怎么快速向數(shù)據(jù)庫插入100萬條數(shù)據(jù)可以先用MylSAM插入數(shù)據(jù)然后修改存儲引擎為InnoDB。ALTER TABLE 表名 ENGINE 存儲引擎名稱;3 InnoDBMySQL 5.7及更新版中的默認存儲引擎。InnoDB是事務(wù)安全兼容ACID它具有提交、回滾和崩潰恢復(fù)功能來保護用戶數(shù)據(jù)。InnoDB行級鎖和Oracle風格的一致非鎖讀提高了多用戶并發(fā)性。InnoDB將用戶數(shù)據(jù)存儲在聚集索引中以減少基于主鍵的常見查詢的I/O。為了保持數(shù)據(jù)完整性InnoDB還支持外鍵引用完整性約束。特點支持事務(wù)支持外鍵因此數(shù)據(jù)的完整性、一致性更高支持行級別的鎖和表級別的鎖支持讀寫并發(fā)寫不阻塞讀MVCC特殊的索引存放方式可以減少IO提升査詢效率。番外為什么MySQL越來越像OracleInnoDB是InnobaseOy公司開發(fā)的它和MySQL AB公司合作開源了InnoDB的代碼。但是MySQL的競爭對手Oracle把InnobaseOy收購了。后來2008年Sun公司開發(fā)Java語言的Sun收購了MySQL AB2009年Sun公司又被Oracle收購了所以MySQL和 InnoDB又是一家了。6.2 詳解InnoDB存儲引擎事務(wù)在InnoDB中從提交到完成的整個流程準備更新一條 SQL 語句MySQLinnodb會先去緩沖池BufferPool中去查找這條數(shù)據(jù)沒找到就會去磁盤中查找如果查找到就會將這條數(shù)據(jù)加載到緩沖池BufferPool中。在加載到 Buffer Pool 的同時會將這條數(shù)據(jù)的原始記錄保存到 undo 日志文件中。innodb 會在 Buffer Pool 中執(zhí)行更新操作。更新后的數(shù)據(jù)會記錄在 redo log buffer 中。提交事務(wù)時會將內(nèi)存 redo log buffer 中的數(shù)據(jù)寫入到磁盤的 redo log 文件中。提交事務(wù)時MySQL還會1將本次修改的數(shù)據(jù)記錄到 bin log文件中2將本次修改的bin log文件名和修改的內(nèi)容在bin log中的位置記錄到redo log中3在redo log中寫入 commit 標記標識本次事務(wù)被成功提交了。6.2.1 Buffer Pool 緩沖池緩沖池 Buffer Pool是InnoDB非常重要的組件。MySQL 的數(shù)據(jù)最終是存儲在磁盤中的有了 Buffer Pool第一次查詢時就會將查詢結(jié)果存到Buffer Pool之后再有請求時就會先從緩沖池中查詢沒查到再去磁盤中I/O查找然后在放到 Buffer Pool 中。6.2.2 undo 日志文件在準備更新一條語句的時候該條語句已經(jīng)被加載到 Buffer pool 中了實際上這里還會同時在 undo 日志文件記錄下更新前的值。為什么要記錄更新前的值Innodb 存儲引擎的最大特點就是支持事務(wù)如果本次更新失敗也就是事務(wù)提交失敗那么該事務(wù)中的所有的操作都必須回滾到執(zhí)行前的樣子也就是說當事務(wù)失敗的時候也不會對原始數(shù)據(jù)有影響6.2.3 redo 日志文件redo log buffer內(nèi)存緩存記錄將要做的一些操作。redo log磁盤文件記錄數(shù)據(jù)被修改后的樣子。MySQL 為了提高效率會將更新操作先放在內(nèi)存中去完成然后會在事務(wù)提交后 將其持久化到磁盤日志文件中。知識補充如果 redo log Buffer 刷入磁盤前MySQL宕機了緩存會丟失沒關(guān)系因為 MySQL 會認為本次事務(wù)是失敗的所以數(shù)據(jù)依舊是更新前的樣子沒有任何影響。如果 redo log Buffer 刷入磁盤后MySQL宕機了緩存會丟失也沒關(guān)系因為 redo log buffer 中的數(shù)據(jù)已經(jīng)被寫入到磁盤redo log了下次重啟時 MySQL 會將 redo log 文件內(nèi)容恢復(fù)到 Buffer Pool 中和 Redis 的持久化機制類似Redis 啟動時會檢查 RDB 或者 AOF 或者兩者都檢查根據(jù)持久化的文件將數(shù)據(jù)恢復(fù)到內(nèi)存中。刷入磁盤參數(shù)設(shè)置通過 innodb_flush_log_at_trx_commit 參數(shù)設(shè)置刷入磁盤0 表示不刷入磁盤1 表示立即刷入磁盤2 表示先刷到 os cache6.2.4 bin log文件bin log 記錄對數(shù)據(jù)庫的整個修改操作對主從復(fù)制非常有用bin log刷盤策略可以通過sync_bin log修改策略。為0表示提交事務(wù)后先寫入os cache數(shù)據(jù)不會直接到磁盤中如果宕機bin log數(shù)據(jù)會丟失。建議將sync_bin log設(shè)置為 1 表示直接將數(shù)據(jù)寫入到磁盤文件中。bin log在redo log中被記錄提交事務(wù)時MySQL還會1將本次修改的數(shù)據(jù)記錄到 bin log文件中2將本次修改的bin log文件名和修改的內(nèi)容在bin log中的位置記錄到redo log中3在redo log中寫入 commit 標記標識本次事務(wù)被成功提交了。如果數(shù)據(jù)剛被寫入到bin log文件數(shù)據(jù)庫宕機了數(shù)據(jù)會丟失嗎——不會丟失只要redo log最后沒有 commit 標記就說明本次的事務(wù)是失敗的但是數(shù)據(jù)已經(jīng)被記錄到redo log的磁盤文件中了MySQL 重啟時會將 redo log 中的數(shù)據(jù)恢復(fù)加載到Buffer Pool。6.2.5 后臺線程疑問上面僅描述了在內(nèi)存中的更新操作哪怕是宕機又恢復(fù)了也僅是將更新后的記錄加載到Buffer Pool中這時 MySQL 數(shù)據(jù)庫中的這條記錄依舊是舊值內(nèi)存數(shù)據(jù)依舊是臟數(shù)據(jù)MySQL怎么保持內(nèi)存和數(shù)據(jù)庫表數(shù)據(jù)統(tǒng)一的呢解答MySQL 有個后臺線程它會在某個時機將Buffer Pool 中的臟數(shù)據(jù)刷到磁盤表中保持內(nèi)存和數(shù)據(jù)庫數(shù)據(jù)統(tǒng)一。