中文亚洲精品无码_熟女乱子伦免费_人人超碰人人爱国产_亚洲熟妇女综合网

當(dāng)前位置: 首頁 > news >正文

深圳航空公司seo外鏈優(yōu)化

深圳航空公司,seo外鏈優(yōu)化,跨境電商十大平臺(tái),訪問wordpress數(shù)據(jù)庫梳理面試過程中數(shù)據(jù)庫相關(guān)的常見問題,需要說明的是,這篇文章主要是基于MySQL數(shù)據(jù)庫,其他類型的數(shù)據(jù)庫還請(qǐng)自行參考使用。 數(shù)據(jù)庫概述 為什么使用數(shù)據(jù)庫 1、數(shù)據(jù)庫增刪改查更方便 2、提供了事務(wù)的能力 本質(zhì)是更好的管理數(shù)據(jù)。 數(shù)據(jù)庫體系結(jié)…

梳理面試過程中數(shù)據(jù)庫相關(guān)的常見問題,需要說明的是,這篇文章主要是基于MySQL數(shù)據(jù)庫,其他類型的數(shù)據(jù)庫還請(qǐng)自行參考使用。

數(shù)據(jù)庫概述

為什么使用數(shù)據(jù)庫

1、數(shù)據(jù)庫增刪改查更方便
2、提供了事務(wù)的能力
本質(zhì)是更好的管理數(shù)據(jù)。

數(shù)據(jù)庫體系結(jié)構(gòu)

一個(gè)數(shù)據(jù)庫系統(tǒng)各個(gè)部分以及它們之間的聯(lián)系如下圖所示:
請(qǐng)?zhí)砑訄D片描述

一條SQL的執(zhí)行過程

MySQL中一條SQL的執(zhí)行過程如下圖所示:
請(qǐng)?zhí)砑訄D片描述
(1) 連接器
建立連接是客戶端和MySQL交互的第一步。這里涉及到通信協(xié)議。連接器支持短連接也支持長連接,同時(shí)為了避免頻繁創(chuàng)建和銷毀連接造成性能損失,可選擇利用連接池進(jìn)程管理,如druid,cp30等。
(2) 查詢緩存
查詢緩存主要用來緩存所執(zhí)行的 select語句以及該語句的結(jié)果集。存儲(chǔ)的數(shù)據(jù)是以鍵值對(duì)的形式進(jìn)行存儲(chǔ),如果開啟了緩存,那么在一條查詢sql語句進(jìn)來時(shí)會(huì)先判斷緩存中是否包含當(dāng)前的sql語句鍵值對(duì),如果存在直接將其對(duì)應(yīng)的結(jié)果返回,如果不存在再執(zhí)行后面一系列操作。如果沒有開啟則直接跳過。在數(shù)據(jù)變換頻繁的表中,是不推薦使用的,當(dāng)一張表的數(shù)據(jù)發(fā)生變化,其所有緩存都將清空。
MySQL的查詢緩存默認(rèn)是關(guān)閉的,需要手動(dòng)配置參數(shù) query_cache_type,來開啟查詢緩存。需要說明的是,MySQL 8.0 版本后移除了查詢緩存。之所以刪除查詢緩存,主要有以下兩方面的考慮:
1)只要有對(duì)一個(gè)表的更新,這個(gè)表上所有的查詢緩存都會(huì)被清空
2)SQL任何字符上的不同,如空格,注釋,都會(huì)導(dǎo)致緩存不命中
所以查詢緩存生效的場景主要是配置表。其他的業(yè)務(wù)表,根本無法利用查詢緩存的特性。
(3) 分析器
分析器由兩部分組成:解析器和預(yù)處理器,這里是將兩者的功能合并在一起。
對(duì)于解析器,主要是將SQL進(jìn)行語義解析,具體來說包含以下幾步:(1) 先進(jìn)行詞法解析,校驗(yàn)吃法規(guī)則;(2) 語法解析,判斷輸入的這個(gè) SQL 語句是否滿足 MySQL 語法;(3) 語義解析,對(duì)SQL中的表、索引、視圖等對(duì)象進(jìn)行解析,并對(duì)照數(shù)據(jù)字典檢查這些對(duì)象的名稱以及相關(guān)結(jié)構(gòu),看看這些字段、表、視圖等是否在數(shù)據(jù)庫中。如果表名與列名不準(zhǔn)確的話,則數(shù)據(jù)庫會(huì)就會(huì)反饋錯(cuò)誤信息給客戶端。 若語法與表名或者列名同時(shí)寫錯(cuò)的話,則系統(tǒng)是先提示說語法錯(cuò)誤,等到語法完全正確后再提示說列名或表名錯(cuò)誤。
對(duì)于預(yù)處理器,主要是針對(duì)預(yù)處理 SQL。因?yàn)榻^大多數(shù)情況下,某條 SQL 語句可能會(huì)被反復(fù)調(diào)用執(zhí)行,或者每次執(zhí)行的時(shí)候只有個(gè)別的值不同(比如 select 的 where 子句值不同,update 的 set 子句值不同,insert 的 values 值不同)。如果每次都需要經(jīng)過詞法語義解析、語句優(yōu)化、制定執(zhí)行計(jì)劃等,則效率就明顯不行了。所謂預(yù)編譯語句就是將此類 SQL 語句中的值用占位符替代,可以視為將 SQL 語句模板化或者說參數(shù)化,一般稱這類語句叫Prepared Statements。預(yù)編譯語句的優(yōu)勢在于歸納為:一次編譯、多次運(yùn)行,省去了解析優(yōu)化等過程;此外預(yù)編譯語句能防止 SQL 注入
(4) 查詢優(yōu)化器
查詢優(yōu)化器會(huì)找出執(zhí)行該語句所有可能使用的方案,然后選擇一條最優(yōu)查詢路徑,即MySQL認(rèn)為的效率最高的方式,并生成執(zhí)行計(jì)劃。查詢優(yōu)化器的目的是為了得到目標(biāo)SQL的執(zhí)行計(jì)劃。比如一個(gè)表中創(chuàng)建了多個(gè)索引,優(yōu)化器會(huì)根據(jù)IO和CPU成本,選出代價(jià)最小的索引進(jìn)行執(zhí)行。
查詢成本計(jì)算可參考Mysql–查詢的成本如何計(jì)算一文。也可通過SQL語句前添加上 explain 關(guān)鍵字查看執(zhí)行計(jì)劃(重點(diǎn)關(guān)注type、key、Extra字段)。
(5) 執(zhí)行引擎
查詢語句后,經(jīng)過sql的優(yōu)化器,會(huì)產(chǎn)生一個(gè)執(zhí)行計(jì)劃。根據(jù)MySQL執(zhí)行計(jì)劃的輸出,分析索引使用情況、掃描的行數(shù)可以預(yù)估查詢效率;進(jìn)而可以重構(gòu)SQL語句、調(diào)整索引,提升查詢效率。
執(zhí)行引擎會(huì)利用存儲(chǔ)引擎提供的相應(yīng)的 API 來完成SQL操作。注意,不同功能的存儲(chǔ)引擎實(shí)現(xiàn)的 API 是相同的。所以,調(diào)整存儲(chǔ)引擎,操作方式并不需要同步調(diào)整。最后,執(zhí)行引擎把數(shù)據(jù)返回給客戶端,即使沒有結(jié)果也要返回。
(6) 存儲(chǔ)引擎
InnoDB:支持事務(wù)處理,支持外鍵,支持崩潰修復(fù)能力和并發(fā)控制。如果需要對(duì)事務(wù)的完整性要求比較高(比如銀行),要求實(shí)現(xiàn)并發(fā)控制(比如售票),那選擇InnoDB有很大的優(yōu)勢。如果需要頻繁的更新、刪除操作的數(shù)據(jù)庫,也可以選擇InnoDB,因?yàn)橹С质聞?wù)的提交(commit)和回滾(rollback)。
MyISAM:插入數(shù)據(jù)快,空間和內(nèi)存使用比較低。如果表主要是用于插入新記錄和讀出記錄,那么選擇MyISAM能實(shí)現(xiàn)處理高效率。如果應(yīng)用的完整性、并發(fā)性要求比 較低,也可以使用。如果數(shù)據(jù)表主要用來插入和查詢記錄,則MyISAM引擎能提供較高的處理效率。

為什么選擇MySQL數(shù)據(jù)庫

MySQL的技術(shù)優(yōu)勢如下:
高可靠性:MySQL具有非常高的容錯(cuò)性和穩(wěn)定性,即使在硬件或軟件出現(xiàn)故障的情況下,也能保證數(shù)據(jù)的完整性和安全性。
高性能:MySQL采用了先進(jìn)的優(yōu)化技術(shù),包括索引優(yōu)化、查詢優(yōu)化、緩存優(yōu)化等,以確保數(shù)據(jù)庫的高效運(yùn)行,并且支持高并發(fā)訪問。
靈活性:MySQL是一個(gè)模塊化的數(shù)據(jù)庫系統(tǒng),可以輕松擴(kuò)展和自定義,適應(yīng)不同類型的應(yīng)用程序和使用場景。
安全性:MySQL具有多重安全功能,包括訪問控制、加密存儲(chǔ)、事務(wù)支持等,可以有效保護(hù)數(shù)據(jù)庫的安全和完整性。
可擴(kuò)展性:MySQL數(shù)據(jù)庫可以輕松地添加新的節(jié)點(diǎn),從而可以更好地處理大量數(shù)據(jù)。

可滿足高可靠性、高性能的需求。

為什么選擇InnoDB

數(shù)據(jù)安全性高:InnoDB存儲(chǔ)引擎通過日志和緩沖池等機(jī)制來保證數(shù)據(jù)的完整性和一致性,可以處理各種意外情況,例如系統(tǒng)崩潰、斷電等問題。
支持大事務(wù):InnoDB存儲(chǔ)引擎支持大事務(wù)(超過1GB),使得用戶可以對(duì)大型數(shù)據(jù)進(jìn)行修改操作而不用分批提交。
高并發(fā)性能:InnoDB存儲(chǔ)引擎支持行級(jí)鎖定,可以保證多個(gè)用戶同時(shí)訪問同一條數(shù)據(jù)時(shí)不會(huì)出現(xiàn)死鎖問題,從而提高了并發(fā)性能。

滿足高可靠性、高性能的需求。

InnoDB引擎的4大特性

(1) 插入緩沖(insert buffer)

(2) 二次寫(double write)

(3) 自適應(yīng)哈希索引(ahi)

(4) 預(yù)讀(read ahead)

數(shù)據(jù)庫設(shè)計(jì)

MyISAM與InnoDB對(duì)比

場景MyISAMInnoDB
存儲(chǔ)結(jié)構(gòu)每張表被存放在三個(gè)文件:frm-表格定義、MYD(MYData)-數(shù)據(jù)文件、MYI(MYIndex)-索引文件所有的表都保存在同一個(gè)數(shù)據(jù)文件中(也可能是多個(gè)文件,或者是獨(dú)立的表空間文件),InnoDB表的大小只受限于操作系統(tǒng)文件的大小,一般為2GB
記錄存儲(chǔ)順序按記錄插入順序保存按主鍵大小有序插入
外鍵不支持支持
事務(wù)不支持支持
鎖支持表級(jí)鎖定行級(jí)鎖定、表級(jí)鎖定,鎖定力度小并發(fā)能力高
SELECT 操作MyISAM更優(yōu)
INSERT、UPDATE、DELETE 操作InnoDB更優(yōu)
索引的實(shí)現(xiàn)方式B+樹索引,myisam 是堆表B+樹索引,Innodb 是索引組織表
哈希索引不支持支持
全文索引支持不支持

數(shù)據(jù)類型

MySQL提供的數(shù)據(jù)類型有很多,具體來說,有以下幾類:整數(shù)類型、浮點(diǎn)數(shù)類型、日期和時(shí)間類型、字符串類型、文本和二進(jìn)制類型。具體見表格:

分類類型名稱大小數(shù)值范圍用途
整數(shù)類型TINYINT1 Bytes(-128,127)微小整數(shù)
整數(shù)類型SMALLINT2 Bytes(-32768,32767)小整數(shù)
整數(shù)類型MEDIUMINT3 Bytes(-8 388 608,8 388 607)中等整數(shù)
整數(shù)類型INT或INTEGER4 Bytes(-2147483648,2147483647)整數(shù)
整數(shù)類型BIGINT8 Bytes(-9223372036854775808,9223372036854775807)大整數(shù)
浮點(diǎn)數(shù)類型FLOAT4 Bytes科學(xué)計(jì)數(shù)法單精度浮點(diǎn)數(shù)
浮點(diǎn)數(shù)類型DOUBLE8 Bytes科學(xué)計(jì)數(shù)法雙精度浮點(diǎn)數(shù)
浮點(diǎn)數(shù)類型DECIMAL對(duì)DECIMAL(M,D) ,如果M>D,為M+2否則為D+2依賴于M和D的值精準(zhǔn)小數(shù)
日期和時(shí)間類型DATE3 BytesYYYY-MM-DD, (1000-01-01, 9999-12-31)日期
日期和時(shí)間類型TIME3 BytesHH:MM:SS, (-838:59:59, 838:59:59)時(shí)間
日期和時(shí)間類型YEAR1 BytesYYYY, (1901, 2155)
日期和時(shí)間類型DATETIME8 BytesYYYY-MM-DD hh:mm:ss, (1000-01-01 00:00:00, 9999-12-31 23:59:59)混合日期和時(shí)間
日期和時(shí)間類型TIMESTAMP4 BytesYYYY-MM-DD hh:mm:ss, (1970-01-01 00:00:01 UTC, 2038-01-19 03:14:07 UTC)標(biāo)準(zhǔn)時(shí)間戳
字符串類型CHAR0-255 bytes定長字符串
字符串類型VARCHAR0-65535 bytes變長字符串
文本和二進(jìn)制類型TINYBLOB0-255 bytes不超過 255 個(gè)字符的二進(jìn)制文本數(shù)據(jù)
文本和二進(jìn)制類型MEDIUMBLOB0-16777215 bytes二進(jìn)制形式的中等長度文本數(shù)據(jù)
文本和二進(jìn)制類型BLOB0-65535 bytes二進(jìn)制形式的文本數(shù)據(jù)
文本和二進(jìn)制類型LONGBLOB0-4294967295bytes二進(jìn)制形式極大文本數(shù)據(jù)
文本和二進(jìn)制類型TEXT0-65535 bytes文本數(shù)據(jù)
文本和二進(jìn)制類型TEXT0-65535 bytes文本數(shù)據(jù)
文本和二進(jìn)制類型BINARY(M)0-M bytes允許長度0~M個(gè)字節(jié)的定長字節(jié)字符串
文本和二進(jìn)制類型VARBINARY(M)0-M bytes允許長度0~M個(gè)字節(jié)的變長字節(jié)字符串

任何整數(shù)類型、浮點(diǎn)數(shù)類型都可以加上UNSIGNED屬性,表示數(shù)據(jù)是無符號(hào)的,即非負(fù)整數(shù),默認(rèn)是有符號(hào)數(shù)據(jù)。
整數(shù)類型可以被指定長度,例如:INT(11)表示長度為11的INT類型。長度在大多數(shù)場景是沒有意義的,它不會(huì)限制值的合法范圍,只會(huì)影響顯示字符的個(gè)數(shù),不建議指定長度。
CHAR 和 VARCHAR 類型類似,但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲(chǔ)或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換。
盡管CHAR有其單獨(dú)應(yīng)用場景,但是推薦使用VARCHAR,以保證可移植性。
MySQL也支持枚舉類型(ENUM),用于把不重復(fù)的數(shù)據(jù)存儲(chǔ)為一個(gè)預(yù)定義的集合。但是枚舉類型并不通用,建議使用CHAR或VARCHAR替換。
針對(duì)日期和時(shí)間類型,盡量使用TIMESTAMP,考慮到TIMESTAMP時(shí)間范圍有限,一般推薦使用VARCHAR存儲(chǔ),并在業(yè)務(wù)側(cè)進(jìn)行轉(zhuǎn)換。
TEXT 也提供TINYTEXT、MEDIUMBLOB、LONGTEXT等類型,這里不再展示。
盡量避免使用TEXT、BLOB等類型,因?yàn)椴樵儠r(shí)會(huì)使用臨時(shí)表,這將導(dǎo)致嚴(yán)重的性能開銷。
BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR,不同的是它們包含二進(jìn)制字符串而不要非二進(jìn)制字符串。也就是說,它們包含字節(jié)字符串而不是字符字符串。這說明它們沒有字符集,并且排序和比較基于列值字節(jié)的數(shù)值值。

數(shù)據(jù)庫范式使用規(guī)范

【強(qiáng)制】數(shù)據(jù)庫設(shè)計(jì)優(yōu)先滿足第三范式(3NF),如果無法滿足,則盡量滿足第二范式(2NF)

第一范式要求表中列的值具有原子性。(不存在組合字段)
第二范式要求表中的每條記錄都有唯一標(biāo)識(shí),且不存在部分依賴。所謂部分依賴,就是指所有非唯一標(biāo)識(shí)字段,都必須完全依賴唯一標(biāo)識(shí),不能只依賴唯一標(biāo)識(shí)的一部分。如果知道唯一標(biāo)識(shí)屬性的值,就可以檢索到任何表記錄的任何屬性的任何值。(不存在部分依賴,如果有,要拆分出來)
假設(shè)有關(guān)系模型R,包含如下屬性(學(xué)號(hào)、課程號(hào)、課程名、姓名、學(xué)分、成績),表示學(xué)生的課程得分,以學(xué)號(hào)和課程號(hào)為唯一標(biāo)識(shí),這里姓名屬性只依賴學(xué)號(hào),學(xué)分屬性只依賴課程號(hào),所以這里存在部分依賴,不符合第二范式的要求。
第二范式的核心思想是關(guān)系模型的內(nèi)聚,也即拆分思想。對(duì)于上面的關(guān)系模型,可以進(jìn)一步細(xì)分為如下幾個(gè)關(guān)系模型:
學(xué)生表:(學(xué)號(hào)、姓名)
課程表:(課程號(hào)、課程名、學(xué)分)
學(xué)生課程成績表:(學(xué)號(hào)、課程號(hào)、成績)
第三范式要求表中的每一個(gè)非主鍵字段都和主鍵字段直接相關(guān),也就是說,表中的所有非主鍵字段不能依賴于其他非主鍵字段,也即不存在傳遞依賴。(不存在傳遞依賴,如果有,要獨(dú)立出來)
假設(shè)有關(guān)系模型R,包含如下屬性(學(xué)號(hào)、姓名、年齡、學(xué)院、學(xué)院電話),因?yàn)榇嬖趥鬟f依賴:(學(xué)號(hào)) -> (姓名) -> (學(xué)院) -> (學(xué)院電話),所以不符合第三范式的要求。
第三范式的核心思想是消除所有基于函數(shù)依賴能夠發(fā)現(xiàn)的冗余并保持函數(shù)依賴。對(duì)于上面的關(guān)系模型,可以進(jìn)一步細(xì)分為如下幾個(gè)關(guān)系模型:
學(xué)生:(學(xué)號(hào)、姓名、年齡、學(xué)院)
學(xué)院:(學(xué)院、學(xué)院電話)

索引

什么是索引

索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分),它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針。
索引是一種數(shù)據(jù)結(jié)構(gòu)。數(shù)據(jù)庫索引,是數(shù)據(jù)庫管理系統(tǒng)中一個(gè)排序的數(shù)據(jù)結(jié)構(gòu),以協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)。索引的實(shí)現(xiàn)通常使用B樹及其變種B+樹。
更通俗的說,索引就相當(dāng)于目錄。為了方便查找書中的內(nèi)容,通過對(duì)內(nèi)容建立索引形成目錄。索引是一個(gè)文件,它是要占據(jù)物理空間的。

索引的基本原理

索引用來加速尋找那些具有特定值的記錄。如果沒有索引,一般來說執(zhí)行查詢時(shí)將會(huì)全表掃描。
索引的原理很簡單,就是把無序的數(shù)據(jù)變成有序的查詢。
1、把創(chuàng)建了索引的列的內(nèi)容進(jìn)行排序
2、對(duì)排序結(jié)果生成倒排表
3、在倒排表內(nèi)容上拼上數(shù)據(jù)地址鏈
4、在查詢的時(shí)候,先拿到倒排表內(nèi)容,再取出數(shù)據(jù)地址鏈,從而拿到具體數(shù)據(jù)

索引的優(yōu)缺點(diǎn)–為什么使用索引,以及索引存在的問題

索引的優(yōu)點(diǎn):
可以大大加快數(shù)據(jù)的檢索速度,這也是創(chuàng)建索引的最主要的原因。
通過使用索引,可以在查詢的過程中,使用優(yōu)化隱藏器,提高系統(tǒng)的查詢性能。
索引的缺點(diǎn):
(1) 時(shí)間方面:創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間,具體地,當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候,索引也要?jiǎng)討B(tài)的維護(hù),會(huì)降低增/改/刪的執(zhí)行效率;
(2) 空間方面:索引需要占物理空間。

索引的分類

(1) 順序索引和散列索引
按照索引的實(shí)現(xiàn)結(jié)構(gòu),基本的索引類型可以分為順序索引和散列索引:
順序索引:基于值的順序排序。
散列索引:基于將值平均分布到若干個(gè)散列桶中。一個(gè)值所屬的散列桶由散列函數(shù)決定。
其中,順序索引,按照記錄的文件(數(shù)據(jù)庫文件)是否按照某個(gè)搜索碼指定的順序排序,可以分為聚簇索引和非聚簇索引。
(2) 聚簇索引和非聚簇索引
如果包含記錄的文件(數(shù)據(jù)庫文件)按照某個(gè)搜索碼指定的順序排序,那么該搜索碼對(duì)應(yīng)的索引稱為聚集索引(clustering index)。聚集索引也稱為主索引(primary index)。也有將其稱為聚簇索引。
搜索碼指定的順序與文件中記錄的物理順序不同的索引稱為非聚集索引(noclustering index)或輔助索引(secondary index)。也稱為非聚簇索引。
(3) 稠密索引和稀疏索引
根據(jù)是否為所有的搜索碼值建立索引項(xiàng),可將順序索引細(xì)分為兩類:稠密索引和稀疏索引。
(4) 靜態(tài)索引和動(dòng)態(tài)索引
根據(jù)是否可動(dòng)態(tài)擴(kuò)展,將散列劃分為靜態(tài)散列和動(dòng)態(tài)散列。
(5) 位圖索引
位圖索引是一種為多碼上的簡單查詢?cè)O(shè)計(jì)的特殊索引,盡管每個(gè)位圖索引都是建立在一個(gè)碼之上的。
為了使用位圖索引,關(guān)系中的記錄必須按順序編號(hào),比如從0開始。對(duì)于給定的一個(gè)n值,必須能很簡單地檢索到編號(hào)為n的記錄。然后該記錄號(hào)就可以很簡單地轉(zhuǎn)化為一個(gè)塊編號(hào)和一個(gè)指出塊內(nèi)記錄的記錄號(hào)。
(6) 主鍵索引、唯一索引、普通索引、覆蓋索引、多碼索引(聯(lián)合索引、組合索引)
主鍵索引:數(shù)據(jù)列不允許重復(fù),不允許為NULL,一個(gè)表只能有一個(gè)主鍵。
唯一索引:數(shù)據(jù)列不允許重復(fù),允許為NULL值,一個(gè)表允許多個(gè)列創(chuàng)建唯一索引。
普通索引: 基本的索引類型,沒有唯一性的限制,允許為NULL值。
覆蓋索引存儲(chǔ)一些記錄的屬性(但不是搜索碼屬性)的值以及指向記錄的指針。存儲(chǔ)附加的屬性值對(duì)于輔助索引時(shí)非常有用的,因?yàn)樗鼈兪顾饕軌蚧卮鹨恍┎樵?#xff0c;甚至不需要找到實(shí)際的記錄。(索引中存儲(chǔ)記錄的屬性,查找時(shí),可以直接使用索引上的值)
簡單來說,當(dāng)一個(gè)查詢只需要訪問索引中的數(shù)據(jù),而無需訪問實(shí)際的表時(shí),就可以使用覆蓋索引。(減少一次查表操作,也有人將其稱為"回表")
多碼索引是指在復(fù)合的搜索碼上建立和使用索引。也常稱為"聯(lián)合索引"、“組合索引”。

大多數(shù)數(shù)據(jù)庫系統(tǒng)支持B+樹索引,并且有可能還額外支持某種形式的散列文件組織或散列索引。
要對(duì)文件組織和索引技術(shù)做出明智的選擇,實(shí)現(xiàn)者或數(shù)據(jù)庫設(shè)計(jì)者必須考慮以下問題:
(1)索引或散列組織的周期性重組代價(jià)是否可以接受
(2)插入和刪除的相對(duì)頻率如何
(3)是否愿意以增加最壞情況下的訪問時(shí)間為代價(jià)優(yōu)化平均訪問時(shí)間
用戶可能提出哪些類型的查詢
對(duì)于特定值查找,散列索引方案更可取。
對(duì)于值范圍的查找,順序索引更可取。
通常設(shè)計(jì)者會(huì)使用順序索引,除非他預(yù)先知道將來不會(huì)頻繁使用范圍查詢。在這種情況下使用散列索引。

B樹

InnoDB和MyISAM存儲(chǔ)引擎表,索引類型必須為BTRER,MEMORY表可以根據(jù)需要選擇HASH或者BTREE類型索引。
B 樹非葉子結(jié)點(diǎn)和葉子結(jié)點(diǎn)都存儲(chǔ)數(shù)據(jù),因此查詢數(shù)據(jù)時(shí),時(shí)間復(fù)雜度最好為 O(1),最壞為 O(log n)。

B+樹文件組織

索引順序文件組織最大的缺點(diǎn)是文件增大時(shí)性能下降:隨著文件的增大,增加的索引記錄所占百分比和實(shí)際記錄之間變得不協(xié)調(diào),不得不存儲(chǔ)在溢出塊中。我們通過在文件中使用B+樹索引來解決索引查找時(shí)性能下降的問題。
而 B+ 樹只在葉子結(jié)點(diǎn)存儲(chǔ)數(shù)據(jù),非葉子結(jié)點(diǎn)存儲(chǔ)關(guān)鍵字,且不同非葉子結(jié)點(diǎn)的關(guān)鍵字可能重復(fù),因此查詢數(shù)據(jù)時(shí),時(shí)間復(fù)雜度固定為 O(log n)。
更進(jìn)一步,可以通過B+樹的葉結(jié)點(diǎn)來組織存放實(shí)際記錄的磁盤塊,這樣B+樹不僅作為索引使用,而且也是一個(gè)文件中的記錄的組織者,即B+樹文件組織。B+樹的葉節(jié)點(diǎn)由一條鏈相連,因此,當(dāng)需要進(jìn)行一次全數(shù)據(jù)遍歷的時(shí)候,B+樹只需要使用O(logN)時(shí)間找到最小的一個(gè)節(jié)點(diǎn),然后通過鏈進(jìn)行O(N)的順序遍歷即可。
B+樹文件組織可以用于存儲(chǔ)大型數(shù)據(jù)對(duì)象,如clob類型或blob類型。
總結(jié)來說,B+樹可以降低數(shù)據(jù)查找性能、可以通過葉結(jié)點(diǎn)來組織數(shù)據(jù)記錄。

索引下推

索引下推(Index condition pushdown) 簡稱 ICP,在 Mysql 5.6 版本上推出的一項(xiàng)用于優(yōu)化查詢的技術(shù)。(簡單來說,就是將索引列相關(guān)的判斷放到存儲(chǔ)引擎去做,而無需返回給數(shù)據(jù)庫服務(wù)器判斷)
在不使用索引下推的情況下,在使用非主鍵索引進(jìn)行查詢時(shí),存儲(chǔ)引擎通過索引檢索到數(shù)據(jù),然后返回給 MySQL 服務(wù)器,服務(wù)器判斷數(shù)據(jù)是否符合條件。
而有了索引下推之后,如果存在某些被索引列的判斷條件時(shí),MySQL 服務(wù)器將這一部分判斷條件傳遞給存儲(chǔ)引擎,然后由存儲(chǔ)引擎通過判斷索引是否符合 MySQL 服務(wù)器傳遞的條件,只有當(dāng)索引符合條件時(shí)才會(huì)將數(shù)據(jù)檢索出來返回給 MySQL 服務(wù)器。
索引條件下推優(yōu)化可以減少存儲(chǔ)引擎查詢基礎(chǔ)表的次數(shù),也可以減少 MySQL 服務(wù)器從存儲(chǔ)引擎接收數(shù)據(jù)的次數(shù)。

索引的設(shè)計(jì)原則

(1) 【推薦】索引存儲(chǔ)結(jié)構(gòu)推薦BTREE
InnoDB和MyISAM存儲(chǔ)引擎表,索引類型必須為BTRER,MEMORY表可以根據(jù)需要選擇HASH或者BTREE類型索引。
(2)【建議】單個(gè)表上的索引個(gè)數(shù)不能超過7個(gè)
索引在加速查詢的同時(shí),也會(huì)帶來寫入速度降低的問題(寫入數(shù)據(jù)的同時(shí),要更新索引)。應(yīng)限制單表上索引個(gè)數(shù)。
(3)【建議】利用覆蓋索引來進(jìn)行查詢操作
覆蓋查詢即是查詢只需要通過索引即可拿到所需數(shù)據(jù),而不需要再次回表查詢,所以效率相對(duì)很高。所謂的覆蓋索引,就是索引上存儲(chǔ)的某一條記錄索引列的值,如果一個(gè)查詢只需要訪問索引中的數(shù)據(jù),而無需訪問實(shí)際的表時(shí),就可以實(shí)現(xiàn)覆蓋索引的效果。
(4)【強(qiáng)制】使用聯(lián)合索引時(shí)要遵循最左前綴匹配原則
所謂的聯(lián)合索引就是基于多個(gè)列創(chuàng)建索引,也稱"多碼索引"、"組合索引"等。舉例來說,一個(gè)表有A、B、C、D等列,如果聲明(A,B,C)為一個(gè)索引,那么這個(gè)索引就是一個(gè)聯(lián)合索引。聯(lián)合索引遵循最左前綴匹配原則。所謂最左前綴匹配的原則,就是最左優(yōu)先,在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開始匹配,聯(lián)合索引的第一個(gè)字段必須出現(xiàn)在查詢組句中,這個(gè)索引才會(huì)被用到,對(duì)(A,B,C)的索引,數(shù)據(jù)庫會(huì)同時(shí)際建立了(A)、(A,B)、(A,B,C)三個(gè)索引。
基于以上介紹,在使用聯(lián)合索引時(shí),為遵循最左匹配原則,要將使用最頻繁的列放在最左,這樣就能使用到索引。如上述聯(lián)合索引,A應(yīng)該是訪問頻率頻率最高的列。
(5) 【推薦】多表關(guān)聯(lián)查詢時(shí),保證被關(guān)聯(lián)的字段有索引
多表關(guān)聯(lián)查詢會(huì)引入性能問題,如果表的規(guī)模很大。對(duì)于大數(shù)據(jù)量場景,需要保證被關(guān)聯(lián)的字段有索引。
(6) 【建議】頻繁更新的列不建議創(chuàng)建索引
索引不是沒有代價(jià)的,索引在加速數(shù)據(jù)的檢索速度的同時(shí),因?yàn)樾枰S護(hù)索引對(duì)應(yīng)的實(shí)現(xiàn)結(jié)構(gòu),當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候,會(huì)降低增/改/刪的執(zhí)行效率。所以,對(duì)于需要頻繁更新的字段,不建議建立索引。如果需要加速這部分?jǐn)?shù)據(jù)的查詢性能,首先考慮業(yè)務(wù)場景是否合理,其次考慮當(dāng)前表結(jié)構(gòu)設(shè)計(jì)是否合理,看看能否將業(yè)務(wù)需要的字段變成不頻繁更新的列。
(7) 【強(qiáng)制】使用索引時(shí),要考慮索引生效、失效的場景
索引在sql中的where子句、order by子句、join子句、select子句中會(huì)生效。但是,也要注意索引不生效的情況。常見的索引失效場景有:(1) 查詢條件中有or。此時(shí),必須保證所有or相關(guān)的字段都有索引才能生效。所以要謹(jǐn)慎使用or語句。(2) like語句以%開頭。模糊查詢時(shí),使用%且將其放在開頭,會(huì)導(dǎo)致索引失效。(3) 如果存在類型轉(zhuǎn)換(如存儲(chǔ)是字符串,查詢的時(shí)候未用引號(hào),則會(huì)進(jìn)行類型轉(zhuǎn)換),索引會(huì)失效。(4) 索引列參與計(jì)算會(huì)導(dǎo)致索引失效(如執(zhí)行算數(shù)運(yùn)算或使用函數(shù))。(5) 違背最左匹配原則。(6) 如果Mysql 評(píng)估全表掃描要比使用索引要快,則索引失效(數(shù)據(jù)量很少,使用索引不會(huì)提升查找性能)。
(8)【建議】如果不明確查詢是否使用到索引,可使用執(zhí)行計(jì)劃判斷
執(zhí)行計(jì)劃的type列記錄掃描情況,最好到最差依次是:system>const>eq_ref>ref>range>index>all。其中all表示全表掃描,index表示觸發(fā)了索引。
執(zhí)行計(jì)劃除了用來分析查詢性能,還可以判斷查詢是否使用到了索引。執(zhí)行計(jì)劃的使用可以參考這篇WIKI。

事務(wù)

構(gòu)成單一邏輯工作單元的操作集合稱作事務(wù)(transaction)。即使有故障,數(shù)據(jù)庫系統(tǒng)也必須保證事務(wù)的正確執(zhí)行——要么執(zhí)行整個(gè)事務(wù),要么屬于該事務(wù)的操作一個(gè)也不執(zhí)行。

事務(wù)的ACID特性

數(shù)據(jù)庫需要維護(hù)事務(wù)的以下四個(gè)性質(zhì):
1.原子性(Atomicity)事務(wù)是一個(gè)原子操作,由一系列動(dòng)作組成。事務(wù)的原子性確保這一系列動(dòng)作要么全部完成,要么完全不起作用。
2.一致性(Consistency)隔離執(zhí)行事務(wù)時(shí)(在沒有其他事務(wù)并發(fā)的情況下),保持?jǐn)?shù)據(jù)庫的一致的數(shù)據(jù)庫狀態(tài)。事務(wù)必須保證數(shù)據(jù)庫的一致性————如果一個(gè)事務(wù)作為原子從一個(gè)一致的數(shù)據(jù)庫狀態(tài)開始獨(dú)立地運(yùn)行,則事務(wù)結(jié)束時(shí)數(shù)據(jù)庫也必須再次是一致的(邏輯一致性)。
3.隔離性(Isolation)并發(fā)事務(wù)執(zhí)行之間無影響,在一個(gè)事務(wù)內(nèi)部的操作對(duì)其他事務(wù)是不產(chǎn)生影響,這需要事務(wù)隔離級(jí)別來指定隔離性。每個(gè)事務(wù)都感覺不到系統(tǒng)中其他事務(wù)在并發(fā)地執(zhí)行。
4.持久性(Durability)一旦事務(wù)完成,數(shù)據(jù)庫的改變必須是永久的,即使出現(xiàn)系統(tǒng)故障。

事務(wù)的ACID實(shí)現(xiàn)

原子性與持久性:事務(wù)并非總能成功地執(zhí)行完成。這種事務(wù)稱為中止(abort)事務(wù)。如果要確保原子性,中止事務(wù)必須對(duì)數(shù)據(jù)庫的狀態(tài)不造成影響。因此,中止事務(wù)對(duì)數(shù)據(jù)庫所做過的任何改變必須撤銷。一旦中止事務(wù)造成的變更被撤銷,我們就說事務(wù)已回滾(rolled back)?;謴?fù)機(jī)制負(fù)責(zé)管理事務(wù)中止。典型的方法是維護(hù)一個(gè)日志(log)。每個(gè)事務(wù)對(duì)數(shù)據(jù)庫的修改都會(huì)記錄到日志中。我們先記錄執(zhí)行修改的事務(wù)標(biāo)識(shí)符、修改的數(shù)據(jù)項(xiàng)標(biāo)識(shí)符以及數(shù)據(jù)項(xiàng)的舊值(修改前的)和新值(修改后的)。然后數(shù)據(jù)庫才會(huì)修改。(為什么先寫日志,再寫數(shù)據(jù)庫?–保證修改可恢復(fù))
隔離性與一致性:事務(wù)處理系統(tǒng)通常允許多個(gè)事務(wù)并發(fā)執(zhí)行。在數(shù)據(jù)庫中使用并發(fā)執(zhí)行的動(dòng)機(jī)在本質(zhì)上與操作系統(tǒng)中使用多道程序(multiprogramming)的動(dòng)機(jī)是一樣的。當(dāng)多個(gè)事務(wù)并發(fā)地執(zhí)行時(shí),可能違背隔離性,這導(dǎo)致即使每個(gè)事務(wù)都正確執(zhí)行,數(shù)據(jù)庫的一致性也可能被破壞。數(shù)據(jù)庫系統(tǒng)必須控制事務(wù)之間的交互,以防止它們破壞數(shù)據(jù)庫的一致性。數(shù)據(jù)庫系統(tǒng)通過稱為并發(fā)控制機(jī)制保證這一點(diǎn)。
當(dāng)數(shù)據(jù)庫系統(tǒng)并發(fā)地執(zhí)行多個(gè)事務(wù)時(shí),相應(yīng)的調(diào)度不必是串行的。在多個(gè)事務(wù)的情形下,所有事務(wù)共享CPU時(shí)間。 在并發(fā)執(zhí)行中,通過保證所執(zhí)行的任何調(diào)度的效果都與沒有并發(fā)執(zhí)行的調(diào)度效果一樣,我們可以確保數(shù)據(jù)庫的一致性。

事務(wù)隔離性級(jí)別

在實(shí)際應(yīng)用中,數(shù)據(jù)庫中的數(shù)據(jù)是要被多個(gè)用戶共同訪問的,在多個(gè)用戶同時(shí)操作相同的數(shù)據(jù)時(shí),可能就會(huì)出現(xiàn)一些事務(wù)并發(fā)的問題:
1.臟讀(Dirty Read)。一個(gè)事務(wù)讀取到另一個(gè)事務(wù)未提交的數(shù)據(jù)。
2.不可重復(fù)讀(Non-repeatable Read)。一個(gè)事務(wù)對(duì)同一行數(shù)據(jù)重復(fù)讀取兩次,但得到的結(jié)果不同。如在兩次讀取操作之間,還有其他的事務(wù)更新該行數(shù)據(jù)。
3.虛讀/幻讀(Phantom Read)。一個(gè)事務(wù)執(zhí)行兩次查詢,但第二次查詢的結(jié)果包含了第一次查詢中未出現(xiàn)的數(shù)據(jù)。不可重復(fù)讀針對(duì)的是值的不同,幻讀針對(duì)的是數(shù)據(jù)條數(shù)的不同。
4.丟失更新(Lost Update)。丟失更新可分為兩類,分別是第一類丟失更新和第二類丟失更新。第一類丟失更新是指兩個(gè)事務(wù)同時(shí)操作同一個(gè)數(shù)據(jù)時(shí),當(dāng)?shù)谝粋€(gè)事務(wù)撤銷時(shí),把已經(jīng)提交的第二個(gè)事務(wù)的更新數(shù)據(jù)覆蓋了,第二個(gè)事務(wù)就造成了數(shù)據(jù)丟失。第二類丟失更新是指當(dāng)兩個(gè)事務(wù)同時(shí)操作同一個(gè)數(shù)據(jù)時(shí),第一個(gè)事務(wù)將修改結(jié)果成功提交后,對(duì)第二個(gè)事務(wù)已經(jīng)提交的修改結(jié)果進(jìn)行了覆蓋,對(duì)第二個(gè)事務(wù)造成了數(shù)據(jù)丟失。簡言之,丟失更新是指一個(gè)事務(wù)中的更新被另一個(gè)事務(wù)中的更新覆蓋,并根據(jù)丟失更新的事務(wù)細(xì)分為第一類丟失更新和第二類丟失更新。
為了避免上述事務(wù)并發(fā)問題的出現(xiàn),在標(biāo)準(zhǔn)的 SQL 規(guī)范中定義了四種事務(wù)隔離級(jí)別,不同的隔離級(jí)別對(duì)事務(wù)的處理有所不同:
1.Serializable(可串行化)
提供嚴(yán)格的事務(wù)隔離。它要求事務(wù)序列化執(zhí)行,事務(wù)只能一個(gè)接一個(gè)地執(zhí)行,不能并發(fā)執(zhí)行。此隔離級(jí)別可有效防止臟讀、不可重復(fù)讀、幻讀、丟失更新。但這個(gè)級(jí)別可能導(dǎo)致大量的超時(shí)現(xiàn)象和鎖競爭,在實(shí)際應(yīng)用中很少使用。
2.Repeatable Read(可重復(fù)讀)
一個(gè)事務(wù)在執(zhí)行過程中,可以訪問其他事務(wù)成功提交的新插入的數(shù)據(jù),但不可以訪問成功修改的數(shù)據(jù)。讀取數(shù)據(jù)的事務(wù)將會(huì)禁止寫事務(wù)(但允許讀事務(wù)),寫事務(wù)則禁止任何其他事務(wù)。此隔離級(jí)別可有效防止不可重復(fù)讀和臟讀,但可能存在幻讀的問題。
3.Read Committed (已提交讀)
一個(gè)事務(wù)在執(zhí)行過程中,既可以訪問其他事務(wù)成功提交的新插入的數(shù)據(jù),又可以訪問成功修改的數(shù)據(jù)。讀取數(shù)據(jù)的事務(wù)允許其他事務(wù)繼續(xù)訪問該行數(shù)據(jù),但是未提交的寫事務(wù)將會(huì)禁止其他事務(wù)訪問該行。此隔離級(jí)別可有效防止臟讀,第一類丟失更新(因事務(wù)回退導(dǎo)致的丟失),但可能存在不可重復(fù)讀、幻讀、第二類丟失更新(并發(fā)修改導(dǎo)致的丟失)的問題。
4.Read Uncommitted (未提交讀)
一個(gè)事務(wù)在執(zhí)行過程中,既可以訪問其他事務(wù)未提交的新插入的數(shù)據(jù),又可以訪問未提交的修改數(shù)據(jù)。如果一個(gè)事務(wù)已經(jīng)開始寫數(shù)據(jù),則另外一個(gè)事務(wù)不允許同時(shí)進(jìn)行寫操作,但允許其他事務(wù)讀此行數(shù)據(jù)。此隔離級(jí)別可防止第一類丟失更新,但可能存在臟讀、不可重復(fù)讀、幻讀、第二類丟失更新的問題。
以上所有隔離性級(jí)別都不允許臟寫(Dirty Write)。
一般來說,事務(wù)的隔離級(jí)別越高,越能保證數(shù)據(jù)庫的完整性一致性,但相對(duì)來說,隔離級(jí)別越高,對(duì)并發(fā)性能的影響也越大。因此,通常將數(shù)據(jù)庫的默認(rèn)隔離級(jí)別設(shè)置為已提交讀Committed Read),它既能防止臟讀,又能有較好的并發(fā)性能。雖然這種隔離級(jí)別會(huì)導(dǎo)致不可重復(fù)讀、幻讀和第二類丟失更新這些并發(fā)問題,但可通過在應(yīng)用程序中采用悲觀鎖或樂觀鎖加以控制。
請(qǐng)?zhí)砑訄D片描述
常見的關(guān)系型數(shù)據(jù)庫的默認(rèn)事務(wù)隔離級(jí)別采用的是READ_COMMITED,例如PostgreSQL、ORACLE、SQL Server和DB2。但是使用InnoDB引擎的MySQL數(shù)據(jù)庫默認(rèn)事務(wù)隔離級(jí)別是REPEATABLE_READ。

SQL 一致性約束(完整性約束)有哪幾種?

NOT NULL: 用于控制字段的內(nèi)容一定不能為空(NULL)。
UNIQUE: 控件字段內(nèi)容不能重復(fù),一個(gè)表允許有多個(gè) Unique 約束。
PRIMARY KEY: 也是用于控件字段內(nèi)容不能重復(fù),但它在一個(gè)表只允許出現(xiàn)一個(gè)。
FOREIGN KEY: 用于預(yù)防破壞表之間連接的動(dòng)作,也能防止非法數(shù)據(jù)插入外鍵列,因?yàn)樗仨毷撬赶虻哪莻€(gè)表中的值之一。
CHECK: 用于控制字段的值范圍。

并發(fā)控制

當(dāng)數(shù)據(jù)庫中有多個(gè)事務(wù)并發(fā)執(zhí)行時(shí),事務(wù)的隔離性不一定能保持。為保持事務(wù)的隔離性,系統(tǒng)必須對(duì)并發(fā)事務(wù)之間的相互作用加以控制;這種控制是通過一系列機(jī)制中的一個(gè)稱為并發(fā)控制的機(jī)制來實(shí)現(xiàn)。

視圖

什么是視圖

為了提高復(fù)雜SQL語句的復(fù)用性和表操作的安全性,MySQL數(shù)據(jù)庫管理系統(tǒng)提供了視圖特性。所謂視圖,本質(zhì)上是一種虛擬表,在物理上是不存在的,其內(nèi)容與真實(shí)的表相似,包含一系列帶有名稱的列和行數(shù)據(jù)。但是,視圖并不在數(shù)據(jù)庫中以儲(chǔ)存的數(shù)據(jù)值形式存在。行和列數(shù)據(jù)來自定義視圖的查詢所引用基本表,并且在具體引用視圖時(shí)動(dòng)態(tài)生成。
視圖使開發(fā)者只關(guān)心感興趣的某些特定數(shù)據(jù)和所負(fù)責(zé)的特定任務(wù),只能看到視圖中所定義的數(shù)據(jù),而不是視圖所引用表中的數(shù)據(jù),從而提高了數(shù)據(jù)庫中數(shù)據(jù)的安全性。

視圖的特點(diǎn)

(1) 視圖的列可以來自不同的表,是表的抽象和在邏輯意義上建立的新關(guān)系。
(2) 視圖是由基本表(實(shí)表)產(chǎn)生的表(虛表)。
(3) 視圖的建立和刪除不影響基本表。
(4) 對(duì)視圖內(nèi)容的更新(添加,刪除和修改)直接影響基本表。
(5) 當(dāng)視圖來自多個(gè)基本表時(shí),不允許添加和刪除數(shù)據(jù)。
視圖的操作包括創(chuàng)建視圖,查看視圖,刪除視圖和修改視圖。

視圖的常見使用場景

(1) 重用SQL語句;
(2) 簡化復(fù)雜的SQL操作。在編寫查詢后,可以方便的重用它而不必知道它的基本查詢細(xì)節(jié);
(3) 使用表的組成部分而不是整個(gè)表;
(4) 保護(hù)數(shù)據(jù)。可以給用戶授予表的特定部分的訪問權(quán)限而不是整個(gè)表的訪問權(quán)限;
(5) 更改數(shù)據(jù)格式和表示。視圖可返回與底層表的表示和格式不同的數(shù)據(jù)。

視圖的優(yōu)缺點(diǎn)

視圖的優(yōu)點(diǎn)是:
(1) 查詢簡單化。視圖能簡化用戶的操作
(2) 數(shù)據(jù)安全性。視圖使用戶能以多種角度看待同一數(shù)據(jù),能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)
(3) 邏輯數(shù)據(jù)獨(dú)立性。視圖對(duì)重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性
視圖的缺點(diǎn)
(1) 性能。數(shù)據(jù)庫必須把視圖的查詢轉(zhuǎn)化成對(duì)基本表的查詢,如果這個(gè)視圖是由一個(gè)復(fù)雜的多表查詢所定義,那么,即使是視圖的一個(gè)簡單查詢,數(shù)據(jù)庫也把它變成一個(gè)復(fù)雜的結(jié)合體,需要花費(fèi)一定的時(shí)間。
(2) 修改限制。當(dāng)用戶試圖修改視圖的某些行時(shí),數(shù)據(jù)庫必須把它轉(zhuǎn)化為對(duì)基本表的某些行的修改。事實(shí)上,當(dāng)從視圖中插入或者刪除時(shí),情況也是這樣。對(duì)于簡單視圖來說,這是很方便的,但是,對(duì)于比較復(fù)雜的視圖,可能是不可修改的。

這些視圖有如下特征:1.有UNIQUE等集合操作符的視圖。2.有GROUP BY子句的視圖。3.有諸如AVG\SUM\MAX等聚合函數(shù)的視圖。 4.使用DISTINCT關(guān)鍵字的視圖。5.連接表的視圖(其中有些例外)

視圖使用規(guī)范

【建議】盡量不用或少用視圖、存儲(chǔ)過程、函數(shù)、觸發(fā)器

除非有明確的需求,否則不建議使用視圖、存儲(chǔ)過程、函數(shù)、觸發(fā)器。對(duì) MYSQL 來說, 視圖、存儲(chǔ)過程、函數(shù)、觸發(fā)器 還不是很成熟,沒有完善的出錯(cuò)記錄處理。此外,視圖、存儲(chǔ)過程、函數(shù)、觸發(fā)器會(huì)對(duì)讀寫性能產(chǎn)生影響,應(yīng)明確風(fēng)險(xiǎn)后,再使用。

存儲(chǔ)過程與函數(shù)

存儲(chǔ)過程是一個(gè)預(yù)編譯的SQL語句,優(yōu)點(diǎn)是允許模塊化的設(shè)計(jì),就是說只需要?jiǎng)?chuàng)建一次,以后在該程序中就可以調(diào)用多次。如果某次操作需要執(zhí)行多次SQL,使用存儲(chǔ)過程比單純SQL語句執(zhí)行要快。

存儲(chǔ)過程的優(yōu)缺點(diǎn)

優(yōu)點(diǎn):
1)存儲(chǔ)過程是預(yù)編譯過的,執(zhí)行效率高。
2)存儲(chǔ)過程的代碼直接存放于數(shù)據(jù)庫中,通過存儲(chǔ)過程名直接調(diào)用,減少網(wǎng)絡(luò)通訊。
3)安全性高,執(zhí)行存儲(chǔ)過程需要有一定權(quán)限的用戶。
4)存儲(chǔ)過程可以重復(fù)使用,減少數(shù)據(jù)庫開發(fā)人員的工作量。

缺點(diǎn):
1)調(diào)試麻煩,但是用 PL/SQL Developer 調(diào)試很方便!彌補(bǔ)這個(gè)缺點(diǎn)。
2)移植問題,數(shù)據(jù)庫端代碼當(dāng)然是與數(shù)據(jù)庫相關(guān)的。但是如果是做工程型項(xiàng)目,基本不存在移植問題。
3)重新編譯問題,因?yàn)楹蠖舜a是運(yùn)行前編譯的,如果帶有引用關(guān)系的對(duì)象發(fā)生改變時(shí),受影響的存儲(chǔ)過程、包將需要重新編譯(不過也可以設(shè)置成運(yùn)行時(shí)刻自動(dòng)編譯)。
4)如果在一個(gè)程序系統(tǒng)中大量的使用存儲(chǔ)過程,到程序交付使用的時(shí)候隨著用戶需求的增加會(huì)導(dǎo)致數(shù)據(jù)結(jié)構(gòu)的變化,接著就是系統(tǒng)的相關(guān)問題了,最后如果用戶想維護(hù)該系統(tǒng)可以說是很難很難、而且代價(jià)是空前的,維護(hù)起來更麻煩。

存儲(chǔ)過程的使用

【建議】盡量不用或少用視圖、存儲(chǔ)過程、函數(shù)、觸發(fā)器

除非有明確的需求,否則不建議使用視圖、存儲(chǔ)過程、函數(shù)、觸發(fā)器。對(duì) MYSQL 來說, 視圖、存儲(chǔ)過程、函數(shù)、觸發(fā)器 還不是很成熟,沒有完善的出錯(cuò)記錄處理。此外,視圖、存儲(chǔ)過程、函數(shù)、觸發(fā)器會(huì)對(duì)讀寫性能產(chǎn)生影響,應(yīng)明確風(fēng)險(xiǎn)后,再使用。

觸發(fā)器

觸發(fā)器是用戶定義在關(guān)系表上的一類由事件驅(qū)動(dòng)的特殊的存儲(chǔ)過程。觸發(fā)器是指一段代碼,當(dāng)觸發(fā)某個(gè)事件時(shí),自動(dòng)執(zhí)行這些代碼。

觸發(fā)器使用場景

(1) 可以通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改。
(2) 實(shí)時(shí)監(jiān)控某張表中的某個(gè)字段的更改而需要做出相應(yīng)的處理。例如可以生成某些業(yè)務(wù)的編號(hào)。
(3) 注意不要濫用,否則會(huì)造成數(shù)據(jù)庫及應(yīng)用程序的維護(hù)困難。

觸發(fā)器的優(yōu)缺點(diǎn)

優(yōu)點(diǎn):
(1) 觸發(fā)器提供了檢查數(shù)據(jù)完整性的替代方法。
(2) 觸發(fā)器可以捕獲數(shù)據(jù)庫層中業(yè)務(wù)邏輯中的錯(cuò)誤。
(3) 觸發(fā)器提供了運(yùn)行計(jì)劃任務(wù)的另一種方法。通過使用觸發(fā)器,不必等待運(yùn)行計(jì)劃的任務(wù),因?yàn)樵趯?duì)表中的數(shù)據(jù)進(jìn)行更改之前或之后自動(dòng)調(diào)用觸發(fā)器。
(4) SQL觸發(fā)器對(duì)于審核表中數(shù)據(jù)的更改非常有用。
缺點(diǎn):
(1) 觸發(fā)器只能提供擴(kuò)展驗(yàn)證,并且無法替換所有驗(yàn)證。一些簡單的驗(yàn)證必須在應(yīng)用層完成。 例如,您可以使用JavaScript或服務(wù)器端使用服務(wù)器端腳本語言(如JSP,PHP,ASP.NET,Perl等)來驗(yàn)證客戶端的用戶輸入。
(2)從客戶端應(yīng)用程序調(diào)用和執(zhí)行SQL觸發(fā)器不可見,因此很難弄清數(shù)據(jù)庫層中發(fā)生的情況。
(3) 觸發(fā)器可能會(huì)增加數(shù)據(jù)庫服務(wù)器的開銷。

觸發(fā)器的使用

【建議】盡量不用或少用視圖、存儲(chǔ)過程、函數(shù)、觸發(fā)器

除非有明確的需求,否則不建議使用視圖、存儲(chǔ)過程、函數(shù)、觸發(fā)器。對(duì) MYSQL 來說, 視圖、存儲(chǔ)過程、函數(shù)、觸發(fā)器 還不是很成熟,沒有完善的出錯(cuò)記錄處理。此外,視圖、存儲(chǔ)過程、函數(shù)、觸發(fā)器會(huì)對(duì)讀寫性能產(chǎn)生影響,應(yīng)明確風(fēng)險(xiǎn)后,再使用。

主從復(fù)制

數(shù)據(jù)庫使用 binlog 二進(jìn)制文件,記錄了數(shù)據(jù)可執(zhí)行的所有 SQL 語句。主從同步的目標(biāo)就是把主數(shù)據(jù)庫的 binlog 文件中的 SQL 語句復(fù)制到從數(shù)據(jù)庫,讓其在從數(shù)據(jù)的 relaylog 文件中再執(zhí)行一次這些 SQL 語句即可。
MySQL主從復(fù)制具體流程如下:
(1) 在主庫上把數(shù)據(jù)更高記錄到二進(jìn)制日志;
(3) 從庫創(chuàng)建一個(gè)IO線程,讀取主庫的binlog輸出線程發(fā)送的更新并拷貝這些更新到中繼日志;
(3) 從庫讀取中繼日志的事件,創(chuàng)建一個(gè)SQL線程,將其重放到從庫數(shù)據(jù)中。

主庫的binlog線程——記錄下所有改變了數(shù)據(jù)庫數(shù)據(jù)的語句,放進(jìn)master上的binlog中;
從庫的io線程——在使用start slave 之后,負(fù)責(zé)從master上拉取 binlog 內(nèi)容,放進(jìn)自己的relay log中;
從庫的sql執(zhí)行線程——執(zhí)行relay log中的語句;

什么是異步復(fù)制和半同步?

MySQL 的主從復(fù)制有兩種復(fù)制方式,分別是異步復(fù)制和半同步復(fù)制:
(1) 異步復(fù)制
MySQL 默認(rèn)的主從復(fù)制方式就是異步復(fù)制,因?yàn)?Master 根本不考慮數(shù)據(jù)是否達(dá)到了 Slave,或 Slave 是否成功執(zhí)行。
如果需要實(shí)現(xiàn)完全同步方式,即 Master 需要等待一個(gè)或所有 Slave 執(zhí)行成功后才響應(yīng)成功,那集群效率可想而知。故 MySQL 5.6 之后出現(xiàn)了一種折中的方式——半同步。
(2)半同步復(fù)制
一主一從,一主多從情況下,Master 節(jié)點(diǎn)只要確認(rèn)至少有一個(gè) Slave 接受到了事務(wù),即可向發(fā)起請(qǐng)求的客戶端返回執(zhí)行成功的操作。同時(shí) Master 是不需要等待 Slave 成功執(zhí)行完這個(gè)事務(wù),Slave 節(jié)點(diǎn)接受到這個(gè)事務(wù),并成功寫入到本地 relay 日志中就算成功。
另外,在半同步復(fù)制時(shí),如果主庫的一個(gè)事務(wù)提交成功了,在推送到從庫的過程當(dāng)中,從庫宕機(jī)了或網(wǎng)絡(luò)故障,導(dǎo)致從庫并沒有接收到這個(gè)事務(wù)的Binlog,此時(shí)主庫會(huì)等待一段時(shí)間(這個(gè)時(shí)間由rpl_semi_sync_master_timeout的毫秒數(shù)決定),如果這個(gè)時(shí)間過后還無法推送到從庫,那 MySQL 會(huì)自動(dòng)從半同步復(fù)制切換為異步復(fù)制,當(dāng)從庫恢復(fù)正常連接到主庫后,主庫又會(huì)自動(dòng)切換回半同步復(fù)制。
半同步復(fù)制的“半”體現(xiàn)在,雖然主從庫的Binlog是同步的,但主庫不會(huì)等待從庫執(zhí)行完Relay-log后才返回,而是確認(rèn)從庫接收到Binlog,達(dá)到主從Binlog同步的目的后就返回了,所以從庫的數(shù)據(jù)對(duì)于主庫來說還是有延時(shí)的,這個(gè)延時(shí)就是從庫執(zhí)行Relay-log的時(shí)間。所以只能稱為半同步。

備份與恢復(fù)

制定備份計(jì)劃,通過平臺(tái)實(shí)現(xiàn)備份與恢復(fù)。
(1) 制定備份計(jì)劃,比如根據(jù)數(shù)據(jù)庫的大小進(jìn)行備份,或基于時(shí)間進(jìn)行備份。需要說明的是,無論哪種備份方式,都會(huì)存在一定程度的數(shù)據(jù)丟失。
(2) 備份恢復(fù)時(shí)間,物理備份恢復(fù)快,邏輯備份恢復(fù)慢。
(3) 如果備份恢復(fù)失敗,需先定位恢復(fù)實(shí)現(xiàn)原因,排查后,再重新基于備份數(shù)據(jù)進(jìn)行恢復(fù),以此重復(fù),直到成功為止。

數(shù)據(jù)庫使用

數(shù)據(jù)庫的使用,主要是通過SQL的方式實(shí)現(xiàn)。

SQL 劃分

DDL、DML、DQL、DCL
SQL(結(jié)構(gòu)化查詢語言)的分類如下:
數(shù)據(jù)定義語言DDL:用于定義表的結(jié)構(gòu)和索引等。
數(shù)據(jù)操縱語言DML:用于插入、刪除、更新數(shù)據(jù)。
數(shù)據(jù)查詢語言DQL:用于查詢數(shù)據(jù)。
數(shù)據(jù)控制語言DCL:用于控制數(shù)據(jù)的訪問權(quán)限。
此外,SQL還包括事務(wù)控制語言TCL和系統(tǒng)控制語言TCL等。

DROP、DELETE與TRUNCATE的區(qū)別

在不再需要一張表的時(shí)候,用drop;在想刪除部分?jǐn)?shù)據(jù)行時(shí)候,用delete;在保留表而刪除所有數(shù)據(jù)的時(shí)候用truncate。

UNION與UNION ALL的區(qū)別?

如果使用UNION ALL,不會(huì)合并重復(fù)的記錄行,效率 UNION 高于 UNION ALL。

IN 和 EXISTS 的區(qū)別?

in 適合內(nèi)表比外表數(shù)據(jù)小的情況,exists 適合內(nèi)表比外表數(shù)據(jù)大的情況。如果查詢的內(nèi)外表大小相當(dāng),則二者效率差別不大。

數(shù)據(jù)類型使用

整數(shù)類型

包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,分別表示1字節(jié)、2字節(jié)、3字節(jié)、4字節(jié)、8字節(jié)整數(shù)。任何整數(shù)類型都可以加上UNSIGNED屬性,表示數(shù)據(jù)是無符號(hào)的,即非負(fù)整數(shù)。

int(20)中20的涵義

是指顯示字符的長度。20表示最大顯示寬度為20,但仍占4字節(jié)存儲(chǔ),存儲(chǔ)范圍不變;
不影響內(nèi)部存儲(chǔ),只是影響帶 zerofill 定義的 int 時(shí),前面補(bǔ)多少個(gè) 0,易于報(bào)表展示。
對(duì)大多數(shù)應(yīng)用,這種設(shè)計(jì)沒有意義,只是規(guī)定一些工具用來顯示字符的個(gè)數(shù);int(1)和int(20)存儲(chǔ)和計(jì)算均一樣。

浮點(diǎn)數(shù)類型

包括FLOAT、DOUBLE、DECIMAL。
DECIMAL可以用于存儲(chǔ)比BIGINT還大的整型,能存儲(chǔ)精確的小數(shù)。
而FLOAT和DOUBLE是有取值范圍的,并支持使用標(biāo)準(zhǔn)的浮點(diǎn)進(jìn)行近似計(jì)算。
計(jì)算時(shí)FLOAT和DOUBLE相比DECIMAL效率更高一些,DECIMAL你可以理解成是用字符串進(jìn)行處理。

FLOAT、DOUBLE

FLOAT類型數(shù)據(jù)可以存儲(chǔ)至多8位十進(jìn)制數(shù),并在內(nèi)存中占4字節(jié)。
DOUBLE類型數(shù)據(jù)可以存儲(chǔ)至多18位十進(jìn)制數(shù),并在內(nèi)存中占8字節(jié)。

字符串類型

包括VARCHAR、CHAR、TEXT、BLOB、BINARY等。

VARCHAR

VARCHAR用于存儲(chǔ)可變長字符串,它比定長類型更節(jié)省空間。
對(duì)于VARCHAR來說,MySQL限制最大長度是65536,但是,由于VARCHAR使用額外1或2個(gè)字節(jié)存儲(chǔ)字符串長度。列長度小于255字節(jié)時(shí),使用1字節(jié)表示,否則使用2字節(jié)表示,所以最多能存放的字符個(gè)數(shù)可能是為65532,也可能是65533。
VARCHAR存儲(chǔ)的內(nèi)容超出設(shè)置的長度時(shí),內(nèi)容會(huì)被截?cái)唷?/p>

CHAR

CHAR是定長的,根據(jù)定義的字符串長度分配足夠的空間。
CHAR會(huì)根據(jù)需要使用空格進(jìn)行填充方便比較。
CHAR適合存儲(chǔ)很短的字符串,或者所有值都接近同一個(gè)長度。
CHAR存儲(chǔ)的內(nèi)容超出設(shè)置的長度時(shí),內(nèi)容同樣會(huì)被截?cái)唷?/p>

VARCHAR和CHAR選擇

使用策略:
對(duì)于經(jīng)常變更的數(shù)據(jù)來說,CHAR比VARCHAR更好,因?yàn)镃HAR不容易產(chǎn)生碎片。
對(duì)于非常短的列,CHAR比VARCHAR在存儲(chǔ)空間上更有效率。
使用時(shí)要注意只分配需要的空間,更長的列排序時(shí)會(huì)消耗更多內(nèi)存。
盡量避免使用TEXT/BLOB類型,查詢時(shí)會(huì)使用臨時(shí)表,導(dǎo)致嚴(yán)重的性能開銷。
VARCHAR 在存取方面與char相反,它存取慢,因?yàn)殚L度不固定,但正因如此,不占據(jù)多余的空間,是時(shí)間換空間的做法。
總之,結(jié)合性能角度(char更快)和節(jié)省磁盤空間角度(varchar更小),具體情況還需具體來設(shè)計(jì)數(shù)據(jù)庫才是妥當(dāng)?shù)淖龇ā?/p>

MySQL 中int(10)和char(10)以及varchar(10)的區(qū)別

int(10)的10表示顯示的數(shù)據(jù)的長度,不是存儲(chǔ)數(shù)據(jù)的大小;chart(10)和varchar(10)的10表示存儲(chǔ)數(shù)據(jù)的大小,即表示存儲(chǔ)多少個(gè)字符。
int(10) 10位的數(shù)據(jù)長度 9999999999,占32個(gè)字節(jié),int型4位
char(10) 10位固定字符串,不足補(bǔ)空格 最多10個(gè)字符
varchar(10) 10位可變字符串,不足補(bǔ)空格 最多10個(gè)字符

char(10)表示存儲(chǔ)定長的10個(gè)字符,不足10個(gè)就用空格補(bǔ)齊,占用更多的存儲(chǔ)空間
varchar(10)表示存儲(chǔ)10個(gè)變長的字符,存儲(chǔ)多少個(gè)就是多少個(gè),空格也按一個(gè)字符存儲(chǔ),這一點(diǎn)是和char(10)的空格不同的,char(10)的空格表示占位不算一個(gè)字符

TEXT和BLOB和BINARY

不推薦使用(設(shè)計(jì)上要考慮是否有必要),如果需要使用,要注意盡量單獨(dú)存儲(chǔ)。

枚舉類型(ENUM)

把不重復(fù)的數(shù)據(jù)存儲(chǔ)為一個(gè)預(yù)定義的集合。有時(shí)可以使用ENUM代替常用的字符串類型。
ENUM存儲(chǔ)非常緊湊,會(huì)把列表值壓縮到一個(gè)或兩個(gè)字節(jié)。
ENUM在內(nèi)部存儲(chǔ)時(shí),其實(shí)存的是整數(shù)。
盡量避免使用數(shù)字作為ENUM枚舉的常量,因?yàn)槿菀谆靵y。
排序是按照內(nèi)部存儲(chǔ)的整數(shù)。
不建議使用枚舉類型,推薦使用字符串類型存儲(chǔ),以保證可能存在的多數(shù)據(jù)庫支持的場景。

日期和時(shí)間類型

包括 year、time、date、datetime、timestamp 等類型。
盡量使用timestamp,空間效率高于datetime,用整數(shù)保存時(shí)間戳通常不方便處理。
如果需要存儲(chǔ)微秒,可以使用bigint存儲(chǔ)。

連接

連接的分類

SQL根據(jù)是否保留未匹配元組,將連接分為外連接和內(nèi)連接。
外連接通過在結(jié)果中創(chuàng)建包含空值元組的方式,保留了那些在連接中丟失的元組。實(shí)際上有三種形式的外連接:
(1) 左外連接(left outer join):只保留出現(xiàn)左外連接運(yùn)算之前(左邊)的關(guān)系中的元組。
(2) 右外連接(right outer join):只保留出現(xiàn)右外連接運(yùn)算之前(右邊)的關(guān)系中的元組。
(3) 全外連接(full outer join):保留出現(xiàn)在兩個(gè)關(guān)系中的元組。全外連接是左外連接與右外連接類型的組合。
注意,不同數(shù)據(jù)庫對(duì)全外連接的支持可能不同,如MySQL數(shù)據(jù)不支持全外連接。
內(nèi)連接也稱等值連接。注意,關(guān)鍵詞inner是可選的,當(dāng)join子句中沒有顯示使用inner或outer關(guān)鍵字時(shí),該連接類型則表示inner。
此外,自然連接是內(nèi)連接的一種特殊形式,自然連接連接的是同名屬性列,而內(nèi)連接則不要求兩屬性列同名。對(duì)內(nèi)連接來說,可以用using或on來指定某兩列字段相同的連接條件。

分頁

LIMIT 子句可以被用于強(qiáng)制 SELECT 語句返回指定的記錄數(shù)。LIMIT 接受一個(gè)或兩個(gè)數(shù)字參數(shù)。參數(shù)必須是一個(gè)整數(shù)常量。如果給定兩個(gè)參數(shù),第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量,第二個(gè)參數(shù)指定返回記錄行的最大數(shù)目。注意,初始記錄行的偏移量是 0。

聚合

count(*)和count(1)和count(column)使用比較

基于count()函數(shù)統(tǒng)計(jì)行數(shù)時(shí),count(*)和count(1)都可以用來統(tǒng)計(jì)表中的行數(shù),基于SQL規(guī)范考慮,推薦使用count(*)。注意,在使用count(*)時(shí),要注意不同存儲(chǔ)引擎的支持事務(wù)的水平,如MyISAM不支持事務(wù),使用的鎖是表級(jí)鎖,不會(huì)有并發(fā)的行操作,所以查詢的結(jié)果是準(zhǔn)確的。InnoDB支持事務(wù),并且支持行級(jí)鎖,行可能被并行修改,那么緩存記錄不準(zhǔn)確。且不加where條件時(shí),MyISAM 引擎會(huì)直接返回這個(gè)總數(shù)。
基于count(column_name) 統(tǒng)計(jì)指定列值的數(shù)目時(shí),要注意區(qū)分主鍵字段和非主鍵字段。因?yàn)橹麈I字段非空,所以統(tǒng)計(jì)的結(jié)果和count(1)和count(*)的值一樣。且不加where條件時(shí),MyISAM 引擎會(huì)直接返回這個(gè)總數(shù)。如果是非主鍵字段,不會(huì)統(tǒng)計(jì)NULL值,所以統(tǒng)計(jì)的結(jié)果和count(1)和count(*)的值可能不一樣。且會(huì)基于該字段是否使用了索引,決定是否基于索引統(tǒng)計(jì),還是全表掃描統(tǒng)計(jì)。

數(shù)據(jù)庫優(yōu)化

百萬級(jí)別或以上的數(shù)據(jù)如何刪除

如果需要保留的數(shù)據(jù)比較少的話,可以把要保留的數(shù)據(jù)備份出來。DROP原表,重新創(chuàng)建,先不要急著創(chuàng)建索引、主鍵,把數(shù)據(jù)導(dǎo)回去,然后在建索引、約束之類的。
如果需要保留的數(shù)據(jù)很多,根據(jù)MySQL官方手冊(cè)可知,刪除數(shù)據(jù)的速度和創(chuàng)建的索引數(shù)量是成正比的。所以可以先刪除索引,具體步驟如下:
1、先刪除索引
2、然后刪除其中無用數(shù)據(jù)
3、刪除完成后重新創(chuàng)建索引

數(shù)據(jù)庫CPU飆高問題定位及解決

在分析CPU使用率飆升根因前,先介紹下CPU使用率公式:

單位時(shí)間 CPU 資源 = 查詢執(zhí)行的平均成本 x 單位時(shí)間執(zhí)行的查詢數(shù)量  

可見,CPU使用率與【查詢執(zhí)行的平均成本】和【單位時(shí)間執(zhí)行的查詢數(shù)量】線性相關(guān),而這兩項(xiàng)就是我們常說的慢SQL以及數(shù)據(jù)庫QPS。
所以,CPU使用率飆升可歸納為以下兩點(diǎn):
1、 大量的慢SQL占用了cpu資源,拖垮了數(shù)據(jù)庫,這類的慢sql常常表現(xiàn)為:查詢的數(shù)據(jù)量過大,全表掃描、鎖搶占甚至死鎖、復(fù)雜查詢等。
2、 QPS過高,本質(zhì)上是數(shù)據(jù)庫的承載的流量過大。

1、QPS過高

如果想判斷是否是因?yàn)镼PS過高,導(dǎo)致CPU飆升,最好的方式是查看QPS曲線和CPU曲線是否保持一致,如果QPS曲線基本和CPU曲線保持一致,此時(shí)可斷定CPU飆升必然存在QPS過高的原因。示例如下:
請(qǐng)?zhí)砑訄D片描述
請(qǐng)?zhí)砑訄D片描述
接下來就是確認(rèn)是哪些SQL的QPS過高。對(duì)于MySQL數(shù)據(jù)庫,可以通過root用戶登錄數(shù)據(jù)庫,然后執(zhí)行’SHOW PROCESSLIST’命令查看。
請(qǐng)?zhí)砑訄D片描述
確定了高頻SQL(直接使用SHOW PROCESSLIST,查看重復(fù)SQL的多少,也能推斷出高頻SQL,如果無法監(jiān)測數(shù)據(jù)庫的QPS曲線),接下來就是對(duì)業(yè)務(wù)進(jìn)行分析,確認(rèn)下為什么會(huì)執(zhí)行如此頻繁的調(diào)用并給出優(yōu)化方案。

2、慢SQL

一般情況下,數(shù)據(jù)庫都會(huì)提供慢查詢?nèi)罩?#xff0c;所以只需根據(jù)慢查詢?nèi)罩緛泶_定慢SQL接口。以MySQL為例,可以通過root用戶登錄數(shù)據(jù)庫,執(zhí)行SHOW VARIABLES LIKE 'SLOW_QUERY_LOG%'命令來查看慢日志的路徑。
在指定路徑下獲取到慢查詢?nèi)罩竞?#xff0c;接下來就是分析慢日志,確認(rèn)慢SQL(確認(rèn)執(zhí)行時(shí)間超過1S的SQL)。

大表怎么優(yōu)化

某個(gè)表有近千萬數(shù)據(jù),CRUD比較慢,如何優(yōu)化?
當(dāng)MySQL單表記錄數(shù)過大時(shí),數(shù)據(jù)庫的CRUD性能會(huì)明顯下降,一些常見的優(yōu)化措施如下:
(1) 限定數(shù)據(jù)的范圍: 務(wù)必禁止不帶任何限制數(shù)據(jù)范圍條件的查詢語句。比如:我們當(dāng)用戶在查詢訂單歷史的時(shí)候,我們可以控制在一個(gè)月的范圍內(nèi)。
(2) 讀/寫分離:經(jīng)典的數(shù)據(jù)庫拆分方案,主庫負(fù)責(zé)寫,從庫負(fù)責(zé)讀;
(3) 緩存:使用MySQL的緩存,另外對(duì)重量級(jí)、更新少的數(shù)據(jù)可以考慮使用應(yīng)用級(jí)別的緩存;
(4) 通過分庫分表的方式進(jìn)行優(yōu)化,主要有垂直分表和水平分表。

超大分頁或深度分頁如何處理?

使用 offset + limit 在MySQL中分頁時(shí),隨著頁數(shù)的增加,查詢性能指數(shù)級(jí)增大。
這是由于 MySQL 并不是跳過 offset 的行數(shù),而是取 offset + limit 行,然后丟棄前 offset 行,返回 limit 行,當(dāng)offset特別大的時(shí)候,效率就非常的低下。
此處我們就可以采用覆蓋索引+延遲關(guān)聯(lián)技術(shù)來減少偏移量的定位進(jìn)行優(yōu)化。

##查詢語句
select id from product limit 10000000, 10
##優(yōu)化方式一
SELECT * FROM product WHERE ID >= (select id from product limit 10000000, 1) limit 10
##優(yōu)化方式二
SELECT * FROM product a JOIN (select id from product limit 10000000, 10) b ON a.ID = b.id
分庫分表后面臨的問題

事務(wù)支持分庫分表后,就成了分布式事務(wù)了。如果依賴數(shù)據(jù)庫本身的分布式事務(wù)管理功能去執(zhí)行事務(wù),將付出高昂的性能代價(jià); 如果由應(yīng)用程序去協(xié)助控制,形成程序邏輯上的事務(wù),又會(huì)造成編程方面的負(fù)擔(dān)。
(1) 跨庫join
只要是進(jìn)行切分,跨節(jié)點(diǎn)Join的問題是不可避免的。但是良好的設(shè)計(jì)和切分卻可以減少此類情況的發(fā)生。解決這一問題的普遍做法是分兩次查詢實(shí)現(xiàn)。在第一次查詢的結(jié)果集中找出關(guān)聯(lián)數(shù)據(jù)的id,根據(jù)這些id發(fā)起第二次請(qǐng)求得到關(guān)聯(lián)數(shù)據(jù)。
(2) 跨節(jié)點(diǎn)的count,order by,group by以及聚合函數(shù)問題
這些是一類問題,因?yàn)樗鼈兌夹枰谌繑?shù)據(jù)集合進(jìn)行計(jì)算。多數(shù)的代理都不會(huì)自動(dòng)處理合并工作。解決方案:與解決跨節(jié)點(diǎn)join問題的類似,分別在各個(gè)節(jié)點(diǎn)上得到結(jié)果后在應(yīng)用程序端進(jìn)行合并。和join不同的是每個(gè)結(jié)點(diǎn)的查詢可以并行執(zhí)行,因此很多時(shí)候它的速度要比單一大表快很多。但如果結(jié)果集很大,對(duì)應(yīng)用程序內(nèi)存的消耗是一個(gè)問題。
(3) 數(shù)據(jù)遷移,容量規(guī)劃,擴(kuò)容等問題

(4) ID問題
一旦數(shù)據(jù)庫被切分到多個(gè)物理結(jié)點(diǎn)上,將不能再依賴數(shù)據(jù)庫自身的主鍵生成機(jī)制。一方面,某個(gè)分區(qū)數(shù)據(jù)庫自生成的ID無法保證在全局上是唯一的;另一方面,應(yīng)用程序在插入數(shù)據(jù)之前需要先獲得ID,以便進(jìn)行SQL路由。

數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化

一個(gè)好的數(shù)據(jù)庫設(shè)計(jì)方案對(duì)于數(shù)據(jù)庫的性能往往會(huì)起到事半功倍的效果。
需要考慮數(shù)據(jù)冗余、查詢和更新的速度、字段的數(shù)據(jù)類型是否合理等多方面的內(nèi)容。
(1) 將字段很多的表分解成多個(gè)表
對(duì)于字段較多的表,如果有些字段的使用頻率很低,可以將這些字段分離出來形成新表。
因?yàn)楫?dāng)一個(gè)表的數(shù)據(jù)量很大時(shí),會(huì)由于使用頻率低的字段的存在而變慢。
(2) 增加中間表
對(duì)于需要經(jīng)常聯(lián)合查詢的表,可以建立中間表以提高查詢效率。
通過建立中間表,將需要通過聯(lián)合查詢的數(shù)據(jù)插入到中間表中,然后將原來的聯(lián)合查詢改為對(duì)中間表的查詢。
(3) 增加冗余字段
設(shè)計(jì)數(shù)據(jù)表時(shí)應(yīng)盡量遵循范式理論的規(guī)約,盡可能的減少冗余字段,讓數(shù)據(jù)庫設(shè)計(jì)看起來精致、優(yōu)雅。但是,合理的加入冗余字段可以提高查詢速度。
表的規(guī)范化程度越高,表和表之間的關(guān)系越多,需要連接查詢的情況也就越多,性能也就越差。
注意:
冗余字段的值在一個(gè)表中修改了,就要想辦法在其他表中更新,否則就會(huì)導(dǎo)致數(shù)據(jù)不一致的問題。

SQL優(yōu)化

SQL優(yōu)化可以參考數(shù)據(jù)庫訪問性能優(yōu)化一文。簡單來說,包括如下幾個(gè)方面:
1、減少數(shù)據(jù)訪問(減少磁盤訪問)
(1) 正確的創(chuàng)建并使用索引
2、返回更少數(shù)據(jù)(減少網(wǎng)絡(luò)傳輸或磁盤訪問)
(1) 數(shù)據(jù)分頁處理(減少行數(shù))
客戶端分頁、服務(wù)器分頁(內(nèi)存分頁)、數(shù)據(jù)庫分頁
(2) 只返回需要的字段(減少列數(shù))
3、減少交互次數(shù)(減少網(wǎng)絡(luò)傳輸)
batch 操作
IN LIST優(yōu)化
設(shè)置fetch size
優(yōu)化業(yè)務(wù)邏輯
4、減少服務(wù)器CPU開銷(減少CPU及內(nèi)存開銷)
使用綁定變量(使用預(yù)處理器的能力)
合理使用排序(排序數(shù)據(jù)的規(guī)模可控)
大量復(fù)雜運(yùn)算(如加解密處理)在客戶端處理
5、利用更多資源(增加資源)
客戶端多進(jìn)程并行訪問(謹(jǐn)慎使用,可能會(huì)帶來性能問題)
數(shù)據(jù)庫并行處理(需確認(rèn)數(shù)據(jù)是否支持一條SQL多個(gè)進(jìn)程處理)

參考

數(shù)據(jù)庫系統(tǒng)概念(第六版) A. Silberschatz H. F. Korth S. Sudarshan著 楊冬青 等譯
https://thinkwon.blog.csdn.net/article/details/104778621 MySQL數(shù)據(jù)庫面試題(2020最新版)
https://blog.csdn.net/adminpd/article/details/122910606 MySQL數(shù)據(jù)庫面試題總結(jié)(2022最新版)
https://db-engines.com/en/ranking DB-Engines Ranking
http://mysql.taobao.org/monthly/2021/10/02/ 數(shù)據(jù)庫系統(tǒng)-事物并發(fā)控制 Two-phase Lock Protocol
https://www.runoob.com/mysql/mysql-data-types.html MySQL 數(shù)據(jù)類型
https://blog.csdn.net/inrgihc/article/details/114000246 各種開源數(shù)據(jù)庫同步工具匯總
https://zhuanlan.zhihu.com/p/50638495 千萬級(jí)、百萬級(jí)數(shù)據(jù)刪除優(yōu)化
https://blog.csdn.net/Octopus21/article/details/122443762 如何使用 SQL 快速刪除數(shù)百萬行數(shù)據(jù)
https://juejin.cn/post/7026237038466695175 SQL 約束
https://blog.csdn.net/luyaran/article/details/81019372 mysql觸發(fā)器之優(yōu)缺點(diǎn)簡介
https://www.cnblogs.com/ivictor/p/15142160.html MySQL中 VARCHAR 可設(shè)置的最大長度是多少?
https://www.cnblogs.com/alexusli/archive/2009/02/02/1382509.html SQL Union和SQL Union All用法
https://zhuanlan.zhihu.com/p/50564425 MySQL中常用存儲(chǔ)引擎有哪些?它們相互之間有什么區(qū)別?
https://zhuanlan.zhihu.com/p/102147497 Mysql各種存儲(chǔ)引擎對(duì)比總結(jié)(常用幾種)
https://blog.nowcoder.net/n/ea5541083f1741dbb790b85119b0f990 【MySQL】一條SQL的執(zhí)行過程
https://www.cnblogs.com/mengxinJ/p/14045520.html 一條 sql 的執(zhí)行過程詳解
https://zhuanlan.zhihu.com/p/402757921 【MySQL】一、MySQL架構(gòu)與SQL執(zhí)行流程
https://zhuanlan.zhihu.com/p/613799143 MySQL高階知識(shí)點(diǎn)(一):SQL語句執(zhí)行流程
https://blog.csdn.net/qq_43618881/article/details/118657040 一條sql語句在MySQL的執(zhí)行過程
https://www.cnblogs.com/mengxinJ/p/14045520.html 一條 sql 的執(zhí)行過程詳解
https://blog.csdn.net/qq_41116027/article/details/124135359 sql執(zhí)行流程概述
https://blog.csdn.net/Leon_Jinhai_Sun/article/details/121573983 MySQL高級(jí) - 查詢緩存 - 開啟查詢緩存
https://www.cnblogs.com/haitaoli/p/10828564.html mysql開啟緩存、設(shè)置緩存大小、緩存過期機(jī)制
https://www.jianshu.com/p/9f9d5142b9f0 面試官:說說一條查詢sql的執(zhí)行流程和底層原理?
《高性能MySQL》 Baron Scbwartz, Peter Zaitsev, Vadim Tkacbenko 著,寧海元,周振興,碰立勛,翟衛(wèi)祥 等譯
https://www.cnblogs.com/sunjingwu/p/10755823.html MySQL——執(zhí)行計(jì)劃

http://www.risenshineclean.com/news/56587.html

相關(guān)文章:

  • 創(chuàng)造與魔法官方網(wǎng)站一起做喜歡的事瀏覽器里面信息是真是假
  • 免費(fèi)的域名解析楓樹seo網(wǎng)
  • 河北滄州泊頭做網(wǎng)站的電話aso優(yōu)化平臺(tái)有哪些
  • 網(wǎng)站設(shè)計(jì)培訓(xùn)seo信息優(yōu)化
  • dw做網(wǎng)站怎么發(fā)布全網(wǎng)營銷的公司
  • 網(wǎng)站升級(jí)維護(hù)汕頭seo網(wǎng)站推廣
  • 網(wǎng)站開發(fā)建設(shè)培訓(xùn)推廣軟文300字
  • 網(wǎng)站優(yōu)化 圖片每天新聞早知道
  • 最新網(wǎng)站域名seo關(guān)鍵詞排名優(yōu)化哪家好
  • wifi管理網(wǎng)站百度seo規(guī)則
  • 服務(wù)器2003怎么做網(wǎng)站開戶推廣競價(jià)開戶
  • 北京網(wǎng)站優(yōu)化診斷網(wǎng)站推廣怎么推廣
  • html5移動(dòng)網(wǎng)站開發(fā)流程怎么建網(wǎng)站平臺(tái)賣東西
  • 做網(wǎng)站哪個(gè)系統(tǒng)最好今日短新聞20條
  • 上海做門戶網(wǎng)站的公司app推廣活動(dòng)策劃方案
  • 怎樣做網(wǎng)站賺流量太原百度搜索排名優(yōu)化
  • 網(wǎng)站開發(fā)中怎么樣對(duì)接接口新媒體運(yùn)營工作是什么
  • 如何做淘寶店網(wǎng)站設(shè)計(jì)網(wǎng)頁
  • 網(wǎng)站制作設(shè)計(jì)方案百度關(guān)鍵詞推廣公司
  • lnmp 網(wǎng)站開發(fā)總結(jié)站長工具站長之家
  • wordpress grace7 破解深圳百度快照優(yōu)化
  • 做網(wǎng)站帶來好處網(wǎng)站優(yōu)化排名方法
  • 網(wǎng)站建設(shè)實(shí)現(xiàn)功能wix網(wǎng)站制作
  • 超級(jí)seo外鏈seo的推廣技巧
  • 做設(shè)計(jì)的有什么網(wǎng)站西安網(wǎng)絡(luò)推廣seo0515
  • 星子網(wǎng)今天最新新聞北京百度推廣排名優(yōu)化
  • 做商城的網(wǎng)站用什么框架好網(wǎng)絡(luò)營銷五個(gè)主要手段
  • wordpress 重裝win7優(yōu)化工具哪個(gè)好用
  • wordpress手機(jī)登錄跳轉(zhuǎn)頁面模板商丘seo教程
  • 清苑區(qū)建設(shè)網(wǎng)站找那家公司可以商用的電視app永久軟件