❶ 如何用python寫sql
python可以利用pymysql模塊操作資料庫。
什麼是 PyMySQL？
PyMySQL 是在 Python3.x 版本中用於連接 MySQL 伺服器的一個庫，Python2中則使用mysqldb。
PyMySQL 遵循 Python 資料庫 API v2.0 規范，並包含了 pure-Python MySQL 客戶端庫。
PyMySQL 安裝
在使用 PyMySQL 之前，我們需要確保 PyMySQL 已安裝。
PyMySQL 下載地址：https://github.com/PyMySQL/PyMySQL。
如果還未安裝，我們可以使用以下命令安裝最新版的 PyMySQL：
$ pip3 install PyMySQL
如果你的系統不支持 pip 命令，可以使用以下方式安裝：
1、使用 git 命令下載安裝包安裝(你也可以手動下載)：
$ git clone https://github.com/PyMySQL/PyMySQL$ cd PyMySQL/$ python3 setup.py install
2、如果需要制定版本號，可以使用 curl 命令來安裝：
$ # X.X 為 PyMySQL 的版本號$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz$ cd PyMySQL*$ python3 setup.py install
$ # 現在你可以刪除 PyMySQL* 目錄
注意：請確保您有root許可權來安裝上述模塊。
安裝的過程中可能會出現"ImportError: No mole named setuptools"的錯誤提示，意思是你沒有安裝setuptools，你可以訪問https://pypi.python.org/pypi/setuptools找到各個系統的安裝方法。
Linux 系統安裝實例：
$ wget https://bootstrap.pypa.io/ez_setup.py$ python3 ez_setup.py
資料庫連接
連接資料庫前，請先確認以下事項：
您已經創建了資料庫 TESTDB.
在TESTDB資料庫中您已經創建了表 EMPLOYEE
EMPLOYEE表欄位為 FIRST_NAME, LAST_NAME, AGE, SEX 和 INCOME。
連接資料庫TESTDB使用的用戶名為 "testuser" ，密碼為 "test123",你可以可以自己設定或者直接使用root用戶名及其密碼，Mysql資料庫用戶授權請使用Grant命令。
在你的機子上已經安裝了 Python MySQLdb 模塊。
如果您對sql語句不熟悉，可以訪問我們的SQL基礎教程
實例：
以下實例鏈接 Mysql 的 TESTDB 資料庫：
實例(Python 3.0+)
#!/usr/bin/python3
import pymysql
# 打開資料庫連接db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用 cursor() 方法創建一個游標對象 cursorcursor = db.cursor()
# 使用 execute() 方法執行 SQL 查詢 cursor.execute("SELECT VERSION()")
# 使用 fetchone() 方法獲取單條數據.data = cursor.fetchone()
print ("Database version : %s " % data)
# 關閉資料庫連接db.close()
執行以上腳本輸出結果如下：
創建資料庫表
如果資料庫連接存在我們可以使用execute()方法來為資料庫創建表，如下所示創建表EMPLOYEE：
實例(Python 3.0+)
#!/usr/bin/python3
import pymysql
# 打開資料庫連接db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用 cursor() 方法創建一個游標對象 cursorcursor = db.cursor()
# 使用 execute() 方法執行 SQL，如果表存在則刪除cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 使用預處理語句創建表sql = """CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT )"""
cursor.execute(sql)
# 關閉資料庫連接db.close()
資料庫插入操作
以下實例使用執行 SQL INSERT 語句向表 EMPLOYEE 插入記錄：
實例(Python 3.0+)
#!/usr/bin/python3
import pymysql
# 打開資料庫連接db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法獲取操作游標 cursor = db.cursor()
# SQL 插入語句sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""try: # 執行sql語句
cursor.execute(sql)
# 提交到資料庫執行
db.commit()except: # 如果發生錯誤則回滾
db.rollback()
# 關閉資料庫連接db.close()
以上例子也可以寫成如下形式：
實例(Python 3.0+)
#!/usr/bin/python3
import pymysql
# 打開資料庫連接db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法獲取操作游標 cursor = db.cursor()
# SQL 插入語句sql = "INSERT INTO EMPLOYEE(FIRST_NAME,
LAST_NAME, AGE, SEX, INCOME)
VALUES ('%s', '%s', %s, '%s', %s)" % ('Mac', 'Mohan', 20, 'M', 2000)try: # 執行sql語句
cursor.execute(sql)
# 執行sql語句
db.commit()except: # 發生錯誤時回滾
db.rollback()
# 關閉資料庫連接db.close()
以下代碼使用變數向SQL語句中傳遞參數:
資料庫查詢操作
Python查詢Mysql使用 fetchone() 方法獲取單條數據, 使用fetchall() 方法獲取多條數據。
fetchone():該方法獲取下一個查詢結果集。結果集是一個對象
fetchall():接收全部的返回結果行.
rowcount:這是一個只讀屬性，並返回執行execute()方法後影響的行數。
實例：
查詢EMPLOYEE表中salary（工資）欄位大於1000的所有數據：
實例(Python 3.0+)
#!/usr/bin/python3
import pymysql
# 打開資料庫連接db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法獲取操作游標 cursor = db.cursor()
# SQL 查詢語句sql = "SELECT * FROM EMPLOYEE
WHERE INCOME > %s" % (1000)try: # 執行SQL語句
cursor.execute(sql)
# 獲取所有記錄列表
results = cursor.fetchall()
for row in results: fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# 列印結果
print ("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % (fname, lname, age, sex, income ))except: print ("Error: unable to fetch data")
# 關閉資料庫連接db.close()
以上腳本執行結果如下：
資料庫更新操作
更新操作用於更新數據表的的數據，以下實例將 TESTDB 表中 SEX 為 'M' 的 AGE 欄位遞增 1：
實例(Python 3.0+)
#!/usr/bin/python3
import pymysql
# 打開資料庫連接db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法獲取操作游標 cursor = db.cursor()
# SQL 更新語句sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')try: # 執行SQL語句
cursor.execute(sql)
# 提交到資料庫執行
db.commit()except: # 發生錯誤時回滾
db.rollback()
# 關閉資料庫連接db.close()
刪除操作
刪除操作用於刪除數據表中的數據，以下實例演示了刪除數據表 EMPLOYEE 中 AGE 大於 20 的所有數據：
實例(Python 3.0+)
#!/usr/bin/python3
import pymysql
# 打開資料庫連接db = pymysql.connect("localhost","testuser","test123","TESTDB" )
# 使用cursor()方法獲取操作游標 cursor = db.cursor()
# SQL 刪除語句sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)try: # 執行SQL語句
cursor.execute(sql)
# 提交修改
db.commit()except: # 發生錯誤時回滾
db.rollback()
# 關閉連接db.close()
執行事務
事務機制可以確保數據一致性。
事務應該具有4個屬性：原子性、一致性、隔離性、持久性。這四個屬性通常稱為ACID特性。
原子性（atomicity）。一個事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做。
一致性（consistency）。事務必須是使資料庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的。
隔離性（isolation）。一個事務的執行不能被其他事務干擾。即一個事務內部的操作及使用的數據對並發的其他事務是隔離的，並發執行的各個事務之間不能互相干擾。
持久性（rability）。持續性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中數據的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。
Python DB API 2.0 的事務提供了兩個方法 commit 或 rollback。
實例
實例(Python 3.0+)
# SQL刪除記錄語句sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)try: # 執行SQL語句
cursor.execute(sql)
# 向資料庫提交
db.commit()except: # 發生錯誤時回滾
db.rollback()
對於支持事務的資料庫， 在Python資料庫編程中，當游標建立之時，就自動開始了一個隱形的資料庫事務。
commit()方法游標的所有更新操作，rollback（）方法回滾當前游標的所有操作。每一個方法都開始了一個新的事務。
錯誤處理
DB API中定義了一些資料庫操作的錯誤及異常，下表列出了這些錯誤和異常:
異常
描述
Warning 當有嚴重警告時觸發，例如插入數據是被截斷等等。必須是 StandardError 的子類。
Error 警告以外所有其他錯誤類。必須是 StandardError 的子類。
InterfaceError 當有資料庫介面模塊本身的錯誤（而不是資料庫的錯誤）發生時觸發。 必須是Error的子類。
DatabaseError 和資料庫有關的錯誤發生時觸發。 必須是Error的子類。
DataError 當有數據處理時的錯誤發生時觸發，例如：除零錯誤，數據超范圍等等。 必須是DatabaseError的子類。
OperationalError 指非用戶控制的，而是操作資料庫時發生的錯誤。例如：連接意外斷開、 資料庫名未找到、事務處理失敗、內存分配錯誤等等操作資料庫是發生的錯誤。 必須是DatabaseError的子類。
IntegrityError 完整性相關的錯誤，例如外鍵檢查失敗等。必須是DatabaseError子類。
InternalError 資料庫的內部錯誤，例如游標（cursor）失效了、事務同步失敗等等。 必須是DatabaseError子類。
ProgrammingError 程序錯誤，例如數據表（table）沒找到或已存在、SQL語句語法錯誤、 參數數量錯誤等等。必須是DatabaseError的子類。
NotSupportedError 不支持錯誤，指使用了資料庫不支持的函數或API等。例如在連接對象上 使用.rollback()函數，然而資料庫並不支持事務或者事務已關閉。 必須是DatabaseError的子類。
❷ 試述嵌入式SQL語言使用要考慮
嵌入式SQL程序的VC+SQL server 2000實現的環境配置
嵌入SQL的C應用程序具體到VC++6.0, SQL Server2000 下調試可分為五步：1、環境初始化;2、預編譯;3、編譯;4、連接;5、運行。下面就其中重要的的操作方法給以詳細說明。
1、環境初始化
(1) SQL Server2000為其嵌入式SQL提供了一此特殊的介面；默認的安裝方式沒有安裝這此介面；因此，需要把devtools.rar解壓到SQLServer的系統日錄下（即文件夾devtools中的所有文件）;如果操作系統安裝在C盤，則SQL Server的系統目錄是C:\Program Files\Microsoft SQL Server。（或 在安裝Microsoft SQL Server 2000時選擇安裝Development Tools，為使用嵌入式SQL語言准備必要的頭文件和庫文件。）
( 2)初始化Visual C++ 6.0編譯器環境。在命令行方式下運行文件\Microsoft Visual Studio\VC98\Bin\vcvars32.bat。
(3)初始化SQL Server的預編譯環境。在命令行方式下運行文件:\Devtools\samples\esqlc\setenv.bat。
( 4) VC++6.0環境配置。具體配置分為如下三步[：
①Tools->options->directories->Include Files:添加 C:\Program Files\Microsoft SQL Server\devtools\include。將SQL server自帶的用於資料庫開發的頭文件包含到工程環境中。
②Tools->options->directories->Lib Files:添加C:\Program Files\Microsoft SQL Server\devtools\x861ib。將開發用到的包包含到工程中。
③project->Settings->Link->Object/Library Moles，添加庫文件:SQLakw32.lib， Caw32.lib。這兩個文件之間用空格分開。
2、預編譯
C語言編譯程序不能識別應用程序中的SQL語句，需要經過預處理程序將其轉換成C語句。SQL Server的預處理程序是nsqlprep.exe。 nsqlprep.exe在SQL Server安裝日錄的MSSQL\Binn下。若SQL Server資料庫採用的是默任安裝方式，則需要把binn.rar的內容拷貝到指定目錄下。
Microsoft SQL Server 2000提供的預編譯程序nsqlprep.exe，用於對嵌入式SQL程序進行預編譯處理，生成C語言源程序.實際上就是將嵌入式SQL程序中的嵌入式SQL語句替換為對運行時庫文件Sqlakw32. dll的函數調用，接著運行時庫文件調用動態連接庫Ntwdblib. dll通過網路來存取Microsoft SQL Server 2000資料庫伺服器.
❸ SQL語句執行過程詳解
SQL語句執行過程詳解
一條sql，plsql的執行到底是怎樣執行的呢？
一、SQL語句執行原理：
第一步:客戶端把語句發給伺服器端執行當我們在客戶端執行 select 語句時,客戶端會把這條 SQL 語句發送給伺服器端,讓伺服器端的
進程來處理這語句。也就是說,Oracle 客戶端是不會做任何的操作,他的主要任務就是把客戶端產生
的一些 SQL 語句發送給伺服器端。雖然在客戶端也有一個資料庫進程,但是,這個進程的作用跟伺服器
上的進程作用事不相同的。伺服器上的資料庫進程才會對SQL 語句進行相關的處理。不過,有個問題需
要說明,就是客戶端的進程跟伺服器的進程是一一對應的。也就是說,在客戶端連接上伺服器後,在客戶
端與伺服器端都會形成一個進程,客戶端上的我們叫做客戶端進程;而伺服器上的我們叫做伺服器進程。
第二步:語句解析
當客戶端把 SQL 語句傳送到伺服器後,伺服器進程會對該語句進行解析。同理,這個解析的工作,
也是在伺服器端所進行的。雖然這只是一個解析的動作,但是,其會做很多「小動作」。
1. 查詢高速緩存(library cache)。伺服器進程在接到客戶端傳送過來的 SQL 語句時,不
會直接去資料庫查詢。而是會先在資料庫的高速緩存中去查找,是否存在相同語句的執行計劃。如果在
數據高速緩存中,則伺服器進程就會直接執行這個 SQL 語句,省去後續的工作。所以,採用高速數據緩
存的話,可以提高 SQL 語句的查詢效率。一方面是從內存中讀取數據要比從硬碟中的數據文件中讀取
數據效率要高,另一方面,也是因為這個語句解析的原因。
不過這里要注意一點,這個數據緩存跟有些客戶端軟體的數據緩存是兩碼事。有些客戶端軟體為了
提高查詢效率,會在應用軟體的客戶端設置數據緩存。由於這些數據緩存的存在,可以提高客戶端應用軟
件的查詢效率。但是,若其他人在伺服器進行了相關的修改,由於應用軟體數據緩存的存在,導致修改的
數據不能及時反映到客戶端上。從這也可以看出,應用軟體的數據緩存跟資料庫伺服器的高速數據緩存
不是一碼事。
2. 語句合法性檢查(data dict cache)。當在高速緩存中找不到對應的 SQL 語句時,則服
務器進程就會開始檢查這條語句的合法性。這里主要是對 SQL 語句的語法進行檢查,看看其是否合乎
語法規則。如果伺服器進程認為這條 SQL 語句不符合語法規則的時候,就會把這個錯誤信息,反饋給客
戶端。在這個語法檢查的過程中,不會對 SQL 語句中所包含的表名、列名等等進行 SQL 他只是語法
上的檢查。
3. 語言含義檢查(data dict cache)。若 SQL 語句符合語法上的定義的話,則伺服器進程
接下去會對語句中的欄位、表等內容進行檢查。看看這些欄位、表是否在資料庫中。如果表名與列名不
准確的話,則資料庫會就會反饋錯誤信息給客戶端。所以,有時候我們寫 select 語句的時候,若語法
與表名或者列名同時寫錯的話,則系統是先提示說語法錯誤,等到語法完全正確後,再提示說列名或表名
錯誤。
4. 獲得對象解析鎖(control structer)。當語法、語義都正確後,系統就會對我們需要查詢
的對象加鎖。這主要是為了保障數據的一致性,防止我們在查詢的過程中,其他用戶對這個對象的結構發
生改變。
5. 數據訪問許可權的核對(data dict cache)。當語法、語義通過檢查之後,客戶端還不一定
能夠取得數據。伺服器進程還會檢查,你所連接的用戶是否有這個數據訪問的許可權。若你連接上伺服器
的用戶不具有數據訪問許可權的話,則客戶端就不能夠取得這些數據。有時候我們查詢數據的時候,辛辛苦
苦地把 SQL 語句寫好、編譯通過,但是,最後系統返回個 「沒有許可權訪問數據」的錯誤信息,讓我們氣
半死。這在前端應用軟體開發調試的過程中,可能會碰到。所以,要注意這個問題,資料庫伺服器進程先
檢查語法與語義,然後才會檢查訪問許可權。
6. 確定最佳執行計劃 ?。當語句與語法都沒有問題,許可權也匹配的話,伺服器進程還是不會直接對
資料庫文件進行查詢。伺服器進程會根據一定的規則,對這條語句進行優化。不過要注意,這個優化是有
限的。一般在應用軟體開發的過程中,需要對資料庫的 sql 語言進行優化,這個優化的作用要大大地大
於伺服器進程的自我優化。所以,一般在應用軟體開發的時候,資料庫的優化是少不了的。當伺服器進程
的優化器確定這條查詢語句的最佳執行計劃後,就會將這條 SQL 語句與執行計劃保存到數據高速緩存
(library cache)。如此的話,等以後還有這個查詢時,就會省略以上的語法、語義與許可權檢查的步驟,
而直接執行 SQL 語句,提高 SQL 語句處理效率。
第三步:語句執行
語句解析只是對 SQL 語句的語法進行解析,以確保伺服器能夠知道這條語句到底表達的是什麼意
思。等到語句解析完成之後,資料庫伺服器進程才會真正的執行這條 SQL 語句。這個語句執行也分兩
種情況。
一是若被選擇行所在的數據塊已經被讀取到數據緩沖區的話,則伺服器進程會直接把這個數據傳遞
給客戶端,而不是從資料庫文件中去查詢數據。
若數據不在緩沖區中,則伺服器進程將從資料庫文件中查詢相關數據,並把這些數據放入到數據緩沖
區中(buffer cache)。
第四步:提取數據
當語句執行完成之後,查詢到的數據還是在伺服器進程中,還沒有被傳送到客戶端的用戶進程。所以,
在伺服器端的進程中,有一個專門負責數據提取的一段代碼。他的作用就是把查詢到的數據結果返回給
用戶端進程,從而完成整個查詢動作。從這整個查詢處理過程中,我們在資料庫開發或者應用軟體開發過
程中,需要注意以下幾點:
一是要了解資料庫緩存跟應用軟體緩存是兩碼事情。資料庫緩存只有在資料庫伺服器端才存在,在
客戶端是不存在的。只有如此,才能夠保證資料庫緩存中的內容跟資料庫文件的內容一致。才能夠根據
相關的規則,防止數據臟讀、錯讀的發生。而應用軟體所涉及的數據緩存,由於跟資料庫緩存不是一碼事
情,所以,應用軟體的數據緩存雖然可以提高數據的查詢效率,但是,卻打破了數據一致性的要求,有時候
會發生臟讀、錯讀等情況的發生。所以,有時候,在應用軟體上有專門一個功能,用來在必要的時候清除
數據緩存。不過,這個數據緩存的清除,也只是清除本機上的數據緩存,或者說,只是清除這個應用程序
的數據緩存,而不會清除資料庫的數據緩存。
二是絕大部分 SQL 語句都是按照這個處理過程處理的。我們 DBA 或者基於 Oracle 資料庫的
開發人員了解這些語句的處理過程,對於我們進行涉及到 SQL 語句的開發與調試,是非常有幫助的。有
時候,掌握這些處理原則,可以減少我們排錯的時間。特別要注意,資料庫是把數據查詢許可權的審查放在
語法語義的後面進行檢查的。所以,有時會若光用資料庫的許可權控制原則,可能還不能滿足應用軟體許可權
控制的需要。此時,就需要應用軟體的前台設置,實現許可權管理的要求。而且,有時應用資料庫的許可權管
理,也有點顯得繁瑣,會增加伺服器處理的工作量。因此,對於記錄、欄位等的查詢許可權控制,大部分程
序涉及人員喜歡在應用程序中實現,而不是在資料庫上實現。
DBCC DROPCLEANBUFFERS
從緩沖池中刪除所有清除緩沖區。
DBCC FREEPROCCACHE
從過程緩存中刪除所有元素。
DBCC FREESYSTEMCACHE
從所有緩存中釋放所有未使用的緩存條目
SQL語句中的函數、關鍵字、排序等執行順序:
1. FROM 子句返回初始結果集。
2. WHERE 子句排除不滿足搜索條件的行。
3. GROUP BY 子句將選定的行收集到 GROUP BY 子句中各個唯一值的組中。
4. 選擇列表中指定的聚合函數可以計算各組的匯總值。
5. 此外,HAVING 子句排除不滿足搜索條件的行。
6. 計算所有的表達式;
7. 使用 order by 對結果集進行排序。
8. 查找你要搜索的欄位。
二、SQL語句執行完整過程：
1.用戶進程提交一個 sql 語句:
update temp set a=a*2，給伺服器進程。
2.伺服器進程從用戶進程把信息接收到後,在 PGA 中就要此進程分配所需內存,存儲相關的信息,如在會
話內存存儲相關的登錄信息等。
3.伺服器進程把這個 sql 語句的字元轉化為 ASCII 等效數字碼,接著這個 ASCII 碼被傳遞給一個
HASH 函數,並返回一個 hash 值,然後伺服器進程將到shared pool 中的 library cache 中去查找是否存在相
同的 hash 值,如果存在,伺服器進程將使用這條語句已高速緩存在 SHARED POOL 的library cache 中的已
分析過的版本來執行。
4.如果不存在,伺服器進程將在 CGA 中,配合 UGA 內容對 sql,進行語法分析,首先檢查語法的正確性,接
著對語句中涉及的表,索引,視圖等對象進行解析,並對照數據字典檢查這些對象的名稱以及相關結構，並根據
ORACLE 選用的優化模式以及數據字典中是否存在相應對象的統計數據和是否使用了存儲大綱來生成一個
執行計劃或從存儲大綱中選用一個執行計劃,然後再用數據字典核對此用戶對相應對象的執行許可權,最後生成
一個編譯代碼。
5.ORACLE 將這條 sql 語句的本身實際文本、HASH 值、編譯代碼、與此語名相關聯的任何統計數據
和該語句的執行計劃緩存在 SHARED POOL 的 library cache中。伺服器進程通過 SHARED POOL 鎖存
器(shared pool latch)來申請可以向哪些共享 PL/SQL 區中緩存這此內容,也就是說被SHARED POOL 鎖存
器鎖定的 PL/SQL 區中的塊不可被覆蓋,因為這些塊可能被其它進程所使用。
6.在 SQL 分析階段將用到 LIBRARY
CACHE,從數據字典中核對表、視圖等結構的時候,需要將數據
字典從磁碟讀入 LIBRARY
CACHE,因此,在讀入之前也要使用LIBRARY
CACHE 鎖存器(library cache
pin,library cache lock)來申請用於緩存數據字典。 到現在為止,這個 sql 語句已經被編譯成可執行的代碼了,
但還不知道要操作哪些數據,所以伺服器進程還要為這個 sql 准備預處理數據。
7.首先伺服器進程要判斷所需數據是否在 db buffer 存在,如果存在且可用,則直接獲取該數據,同時根據
LRU 演算法增加其訪問計數;如果 buffer 不存在所需數據,則要從數據文件上讀取首先伺服器進程將在表頭部
請求 TM 鎖(保證此事務執行過程其他用戶不能修改表的結構),如果成功加 TM 鎖,再請求一些行級鎖(TX
鎖),如果 TM、TX 鎖都成功加鎖,那麼才開始從數據文件讀數據,在讀數據之前,要先為讀取的文件准備好
buffer 空間。伺服器進程需要掃面 LRU list 尋找 free db buffer,掃描的過程中,伺服器進程會把發現的所有
已經被修改過的 db buffer 注冊到 dirty list 中, 這些 dirty buffer 會通過 dbwr 的觸發條件,隨後會被寫出到
數據文件,找到了足夠的空閑 buffer,就可以把請求的數據行所在的數據塊放入到 db buffer 的空閑區域或者
覆蓋已經被擠出 LRU list 的非臟數據塊緩沖區,並排列在 LRU list 的頭部,也就是在數據塊放入 DB
BUFFER 之前也是要先申請 db buffer 中的鎖存器,成功加鎖後,才能讀數據到 db buffer。
8.記日誌 現在數據已經被讀入到 db buffer 了,現在伺服器進程將該語句所影響的並被讀
入 db buffer 中的這些行數據的 rowid 及要更新的原值和新值及 scn 等信息從 PGA 逐條的寫入 redo log
buffer 中。在寫入 redo log buffer 之前也要事先請求 redo log buffer 的鎖存器,成功加鎖後才開始寫入,當
寫入達到 redo log buffer 大小的三分之一或寫入量達到 1M 或超過三秒後或發生檢查點時或者 dbwr 之前
發生,都會觸發 lgwr 進程把 redo log buffer 的數據寫入磁碟上的 redo file 文件中(這個時候會產生log file
sync 等待事件)
已經被寫入 redofile 的 redo log buffer 所持有的鎖存器會被釋放,並可被後來的寫入信息覆蓋,
redo log buffer是循環使用的。Redo file 也是循環使用的,當一個 redo file 寫滿後,lgwr 進程會自動切換到
下一 redo file(這個時候可能出現 log fileswitch(checkpoint complete)等待事件)。如果是歸檔模式,歸檔進
程還要將前一個寫滿的 redo file 文件的內容寫到歸檔日誌文件中(這個時候可能出現 log file
switch(archiving needed)。
9.為事務建立回滾段 在完成本事務所有相關的 redo log buffer 之後,伺服器進程開始改寫這個 db buffer
的塊頭部事務列表並寫入 scn,然後 包含這個塊的頭部事務列表及 scn 信息的數據副本放入回滾段中,將
這時回滾段中的信息稱為數據塊的「前映像「,這個」前映像「用於以後的回滾、恢復和一致性讀。(回滾段可以
存儲在專門的回滾表空間中,這個表空間由一個或多個物理文件組成,並專用於回滾表空間,回滾段也可在其它
表空間中的數據文件中開辟。
10.本事務修改數據塊 准備工作都已經做好了,現在可以改寫 db buffer 塊的數據內容了,並在塊的頭部寫
入回滾段的地址。
11.放入 dirty list 如果一個行數據多次 update 而未 commit,則在回滾段中將會有多個「前映像「,除了第
一個」前映像「含有 scn 信息外,其他每個「前映像「的頭部都有 scn 信息和「前前映像」回滾段地址。一個
update 只對應一個 scn,然後伺服器進程將在 dirty list 中建立一
條指向此 db buffer 塊的指針(方便 dbwr 進程可以找到 dirty list 的 db buffer 數據塊並寫入數據文件中)。
接著伺服器進程會從數據文件中繼續讀入第二個數據塊,重復前一數據塊的動作,數據塊的讀入、記日誌、建
立回滾段、修改數據塊、放入 dirty list。當 dirty queue 的長度達到閥值(一般是 25%),伺服器進程將通知
dbwr 把臟數據寫出,就是釋放 db buffer 上的鎖存器,騰出更多的 free db buffer。前面一直都是在說明
oracle 一次讀一個數據塊,其實 oracle 可以一次讀入多個數據塊(db_file_multiblock_read_count 來設置一
次讀入塊的個數)
說明:
在預處理的數據已經緩存在 db buffer 或剛剛被從數據文件讀入到 db buffer 中,就要根據 sql 語句
的類型來決定接下來如何操作。
1>如果是 select 語句,則要查看 db buffer 塊的頭部是否有事務,如果有事務,則從回滾段中讀取數據;如
果沒有事務,則比較 select 的 scn 和 db buffer 塊頭部的 scn,如果前者小於後者,仍然要從回滾段中讀取數據;
如果前者大於後者,說明這是一非臟緩存,可以直接讀取這個 db buffer 塊的中內容。
2>如果是 DML 操作,則即使在 db buffer 中找到一個沒有事務,而且 SCN 比自己小的非臟
緩存數據塊,伺服器進程仍然要到表的頭部對這條記錄申請加鎖,加鎖成功才能進行後續動作,如果不成功,則要
等待前面的進程解鎖後才能進行動作(這個時候阻塞是 tx 鎖阻塞)。
用戶 commit 或 rollback 到現在為止,數據已經在 db buffer 或數據文件中修改完
成,但是否要永久寫到數文件中,要由用戶來決定 commit(保存更改到數據文件) rollback 撤銷數據的更改)。
1.用戶執行 commit 命令
只有當 sql 語句所影響的所有行所在的最後一個塊被讀入 db buffer 並且重做信息被寫入 redo log
buffer(僅指日誌緩沖區,而不包括日誌文件)之後,用戶才可以發去 commit 命令,commit 觸發 lgwr 進程,但不
強制立即 dbwr來釋放所有相應 db buffer 塊的鎖(也就是no-force-at-commit,即提交不強制寫),也就是說有
可能雖然已經 commit 了,但在隨後的一段時間內 dbwr 還在寫這條 sql 語句所涉及的數據塊。表頭部的行鎖
並不在 commit 之後立即釋放,而是要等 dbwr 進程完成之後才釋放,這就可能會出現一個用戶請求另一用戶
已經 commit 的資源不成功的現象。
A .從 Commit 和 dbwr 進程結束之間的時間很短,如果恰巧在 commit 之後,dbwr 未結束之前斷電,因為
commit 之後的數據已經屬於數據文件的內容,但這部分文件沒有完全寫入到數據文件中。所以需要前滾。由
於 commit 已經觸發 lgwr,這些所有未來得及寫入數據文件的更改會在實例重啟後,由 smon 進程根據重做日
志文件來前滾,完成之前 commit 未完成的工作(即把更改寫入數據文件)。
B.如果未 commit 就斷電了,因為數據已經在 db buffer 更改了,沒有 commit,說明這部分數據不屬於數
據文件,由於 dbwr 之前觸發 lgwr 也就是只要數據更改,(肯定要先有 log) 所有 DBWR,在數據文件上的修改
都會被先一步記入重做日誌文件,實例重啟後,SMON 進程再根據重做日誌文件來回滾。
其實 smon 的前滾回滾是根據檢查點來完成的,當一個全部檢查點發生的時候,首先讓 LGWR 進程將
redo log buffer 中的所有緩沖(包含未提交的重做信息)寫入重做日誌文件,然後讓 dbwr 進程將 db buffer 已
提交的緩沖寫入數據文件(不強制寫未提交的)。然後更新控制文件和數據文件頭部的 SCN,表明當前資料庫
是一致的,在相鄰的兩個檢查點之間有很多事務,有提交和未提交的。
像前面的前滾回滾比較完整的說法是如下的說明:
A.發生檢查點之前斷電,並且當時有一個未提交的改變正在進行,實例重啟之後,SMON 進程將從上一個
檢查點開始核對這個檢查點之後記錄在重做日誌文件中已提交的和未提交改變,因為
dbwr 之前會觸發 lgwr,所以 dbwr 對數據文件的修改一定會被先記錄在重做日誌文件中。因此,斷電前被
DBWN 寫進數據文件的改變將通過重做日誌文件中的記錄進行還原,叫做回滾,
B. 如果斷電時有一個已提交,但 dbwr 動作還沒有完全完成的改變存在,因為已經提交,提交會觸發 lgwr
進程,所以不管 dbwr 動作是否已完成,該語句將要影響的行及其產生的結果一定已經記錄在重做日誌文件中
了,則實例重啟後,SMON 進程根據重做日誌文件進行前滾.
實例失敗後用於恢復的時間由兩個檢查點之間的間隔大小來決定,可以通個四個參數設置檢查點執行的頻
率:
Log_checkpoint_interval:
決定兩個檢查點之間寫入重做日誌文件的系統物理塊(redo blocks)
的大小,默認值是 0,無限制。
log_checkpoint_timeout:
兩 個 檢 查 點 之 間 的 時 間 長 度（秒）默 認 值 1800s。
fast_start_io_target:
決定了用於恢復時需要處理的塊的多少,默認值是 0,無限制。
fast_start_mttr_target:
直接決定了用於恢復的時間的長短,默認值是 0,無限制(SMON 進程執行的前滾
和回滾與用戶的回滾是不同的,SMON 是根據重做日誌文件進行前滾或回滾,而用戶的回滾一定是根據回滾段
的內容進行回滾的。
在這里要說一下回滾段存儲的數據,假如是 delete 操作,則回滾段將會記錄整個行的數據,假如是 update,
則回滾段只記錄被修改了的欄位的變化前的數據(前映像),也就是沒有被修改的欄位是不會被記錄的,假如是
insert,則回滾段只記錄插入記錄的 rowid。 這樣假如事務提交,那回滾段中簡單標記該事務已經提交;假如是
回退,則如果操作是 delete,回退的時候把回滾段中數據重新寫回數據塊,操作如果是 update,則把變化前數據
修改回去,操作如果是 insert,則根據記錄的 rowid 把該記錄刪除。
2.如果用戶 rollback。
則伺服器進程會根據數據文件塊和 DB BUFFER 中塊的頭部的事務列表和 SCN 以及回滾段地址找到
回滾段中相應的修改前的副本,並且用這些原值來還原當前數據文件中已修改但未提交的改變。如果有多個
「前映像」,伺服器進程會在一個「前映像」的頭部找到「前前映像」的回滾段地址,一直找到同一事務下的最早的
一個「前映像」為止。一旦發出了 COMMIT,用戶就不能rollback,這使得 COMMIT 後 DBWR 進程還沒有
全部完成的後續動作得到了保障。到現在為例一個事務已經結束了。
說明:
TM 鎖:
符合 lock 機制的,用於保護對象的定義不被修改。 TX 鎖:
這個鎖代表一個事務,是行
級鎖,用數據塊頭、數據記錄頭的一些欄位表示,也是符合 lock 機制,有 resource structure、lock
structure、enqueue 演算法。
❹ pg鏌ヨ㈠勭悊嫻佺▼
騫惰屾煡璇浣跨敤澶氫釜鍚庡彴榪涚▼錛屼絾鍚庣榪涚▼鍩烘湰涓婂勭悊榪炴帴鐨勫㈡埛絝鍙戝嚭鐨勬墍鏈夋煡璇銆傛敼鍚庣鏈変簲涓瀛愮郴緇熺粍鎴愩
瑙ｆ瀽鍣ㄧ敓鎴愪竴涓瑙ｆ瀽鏍戱紝鍚庣畫瀛愮郴緇熷彲浠ヤ粠綰鏂囨湰鐨 SQL 璇鍙ヤ腑璇誨彇璇ヨВ鏋愭爲銆
濡備笅闈㈢殑鏌ヨ錛
瑙ｆ瀽鏍戞槸鍏舵牴鑺傜偣鏄瀹氫箟鍦 parsenodes.h涓鐨 [SelectStmt](javascript:void(0))緇撴瀯鐨勬爲銆
SELECT 鏌ヨ㈢殑鍏冪礌鍜岃В鏋愭爲鐨勭浉搴斿厓緔犵紪鍙風浉鍚屻備緥濡傦紝(1) 鏄絎涓涓鐩鏍囧垪琛ㄧ殑涓涓欏圭洰錛屽畠鏄琛ㄧ殑鈥渋d鈥濆垪錛(4) 鏄 WHERE 瀛愬彞錛屼緷姝ょ被鎺ㄣ
鐢變簬瑙ｆ瀽鍣ㄥ湪鐢熸垚瑙ｆ瀽鏍戞椂鍙媯鏌ヨ緭鍏ョ殑璇娉曪紝鍥犳ゅ彧鏈夊湪鏌ヨ涓鍑虹幇璇娉曢敊璇鏃舵墠浼氳繑鍥為敊璇銆
瑙ｆ瀽鍣ㄤ笉媯鏌ヨ緭鍏ユ煡璇㈢殑璇涔夈備緥濡傦紝鍗充嬌鏌ヨ㈠寘鍚涓嶅瓨鍦ㄧ殑琛ㄥ悕錛岃В鏋愬櫒涔熶笉浼氳繑鍥為敊璇銆傝涔夋鏌ョ敱鍒嗘瀽鍣/鍒嗘瀽鍣ㄥ畬鎴愩
鍒嗘瀽鍣ㄨ繍琛岀敱瑙ｆ瀽鍣ㄧ敓鎴愮殑瑙ｆ瀽鏍戠殑璇涔夊垎鏋愬苟鐢熸垚鏌ヨ㈡爲銆
鏌ヨ㈡爲鐨勬牴鏄瀹氫箟鍦 parsenodes.h涓鐨 [鏌ヨ](javascript:void(0))緇撴瀯錛涙ょ粨鏋勫寘鍚鍏剁浉搴旀煡璇㈢殑鍏冩暟鎹錛屼緥濡傛ゅ懡浠ょ殑綾誨瀷錛圫ELECT銆両NSERT 鎴栧叾浠栵級鍜屽嚑涓鍙跺瓙錛涙瘡涓鍙跺瓙褰㈡垚涓涓鍒楄〃鎴栨爲錛屽苟淇濆瓨鍚勪釜鐗瑰畾瀛愬彞鐨勬暟鎹銆
榪版煡璇㈡爲綆榪板備笅銆
閲嶅啓鍣ㄦ槸瀹炵幇 瑙勫垯緋葷粺 鐨勭郴緇燂紝蹇呰佹椂鏍規嵁瀛樺偍鍦 pg_rules緋葷粺鐩褰曚腑鐨勮勫垯鍙樻崲鏌ヨ㈡爲銆
PostgreSQL 涓鐨勮嗗浘 鏄浣跨敤瑙勫垯緋葷粺瀹炵幇鐨勩傚綋瑙嗗浘鐢 CREATE VIEW 鍛戒護瀹氫箟鏃訛紝鐩稿簲鐨勮勫垯浼氳嚜鍔ㄧ敓鎴愬苟瀛樺偍鍦ㄧ洰褰曚腑銆
鍋囪懼凡緇忓畾涔変簡浠ヤ笅瑙嗗浘錛屽苟涓斿瑰簲鐨勮勫垯瀛樺偍鍦 pg_rules 緋葷粺鐩褰曚腑銆
褰撳彂鍑哄寘鍚濡備笅鎵紺鴻嗗浘鐨勬煡璇㈡椂錛岃В鏋愬櫒灝嗗壋寤鴻В鏋愭爲錛屽傚浘鎵紺恆
鍦ㄨ繖涓闃舵碉紝閲嶅啓鍣ㄥ皢鑼冨洿琛ㄨ妭鐐瑰勭悊涓哄瓙鏌ヨ㈢殑瑙ｆ瀽鏍戱紝鍗沖瑰簲鐨勮嗗浘錛屽瓨鍌ㄥ湪 pg_rules 涓銆
璁″垝鍣ㄤ粠閲嶅啓鍣ㄦ帴鏀舵煡璇㈡爲騫剁敓鎴愬彲浠ョ敱鎵ц屽櫒鏈鏈夋晥鍦板勭悊鐨勶紙鏌ヨ錛夎″垝鏍戙
PostgreSQL 涓鐨勮″垝鍣ㄦ槸鍩轟簬綰鎴愭湰浼樺寲鐨勶紱瀹冧笉鏀鎸佸熀浜庤勫垯鐨勪紭鍖栧拰鎻愮ず銆傝繖涓瑙勫垝鍣ㄦ槸 RDBMS 涓鏈澶嶆潅鐨勫瓙緋葷粺
涓庡叾浠 RDBMS 涓鏍鳳紝PostgreSQL 涓鐨 EXPLAIN鍛戒護鏄劇ず璁″垝鏍戞湰韜銆 濡備笅鎵紺恆
浠栧瑰簲鐨勮″垝鏍戱細
姣忎釜璁″垝鑺傜偣閮芥湁鎵ц屽櫒闇瑕佸勭悊鐨勪俊鎮錛屽崟琛ㄦ煡璇㈢殑鎯呭喌涓嬶紝鎵ц屽櫒浠庤″垝鏍戠殑鏈絝鍒版牴榪涜屽勭悊銆
PostgreSQL 鐨勬煡璇浼樺寲鏄鍩轟簬鎴愭湰鐨勩傛垚鏈鏄鏃犻噺綰插礆紝瀹冧滑涓嶆槸緇濆圭殑緇╂晥鎸囨爣錛岃屾槸姣旇緝榪愯惀鐩稿圭嘩鏁堢殑鎸囨爣銆傛垚鏈鐢 costsize.c 涓瀹氫箟鐨勫嚱鏁頒及綆椼傛墽琛屽櫒鎵ц岀殑鎵鏈夋搷浣滈兘鏈夌浉搴旂殑鎴愭湰鍑芥暟銆備緥濡傦紝欏哄簭鎵鎻忓拰緔㈠紩鎵鎻忕殑鎴愭湰鍒嗗埆鐢 cost_seqscan() 鍜 cost_index() 浼扮畻銆
鏈変笁縐嶆垚鏈錛屽惎鍔ㄦ垚鏈錛屾墽琛屾垚鏈浠ュ強鎬繪垚鏈銆傚叾涓鎬繪垚鏈 = 鍚鍔ㄦ垚鏈 + 鎵ц屾垚鏈銆
欏哄簭鎵鎻忕殑鎴愭湰鐢 cost_seqscan() 鍑芥暟浼扮畻銆
鍏朵腑 seq_page_cost 銆 cpu_tuple_cost 鍜 cpu_operator_cost 鍦 postgresql.conf 鏂囦歡涓璁劇疆錛岄粯璁ゅ煎垎鍒涓 1.0 銆 0.01 鍜 0.0025 錛孨tuple鍜孨page鍒嗗埆鏄璇ヨ〃鐨勬墍鏈夊厓緇勫拰鎵鏈夐〉鐨勭紪鍙楓
浠庤繍琛屾垚鏈浼扮畻鍙浠ョ湅鍑猴紝PostgreSQL 鍋囪炬墍鏈夐〉闈㈤兘灝嗕粠瀛樺偍涓璇誨彇錛涗篃灝辨槸璇達紝PostgreSQL 涓嶈冭檻鎵鎻忕殑欏甸潰鏄鍚﹀湪鍏變韓緙撳啿鍖轟腑銆
鉶界劧 PostgreSQL 鏀鎸 涓浜涚儲寮曟柟娉 錛屼緥濡 BTree銆 GiST 銆 GIN 鍜 BRIN 錛屼絾緔㈠紩鎵鎻忕殑鎴愭湰鏄浣跨敤甯歌佺殑鎴愭湰鍑芥暟浼扮畻鐨勶細cost_index()銆
緔㈠紩鎵鎻忕殑鍚鍔ㄦ垚鏈鏄璇誨彇緔㈠紩欏典互璁塊棶鐩鏍囪〃涓絎涓涓鍏冪粍鐨勬垚鏈錛屽畠鐢變互涓嬬瓑寮忓畾涔夛細
Hindex鏄緔㈠紩鏍戠殑楂樺害銆
緔㈠紩鎵鎻忕殑榪愯屾垚鏈鏄琛ㄥ拰緔㈠紩鐨 cpu 鎴愭湰鍜 IO錛堣緭鍏/杈撳嚭錛夋垚鏈涔嬪拰錛
鍓嶄笁涓鎴愭湰瀹氫箟濡備笅錛
鍏朵腑 cpu_index_tuple_cost 鍜 random_page_cost 鍦 postgresql.conf 鏂囦歡涓璁劇疆錛堥粯璁ゅ垎鍒涓 0.005 鍜 4.0錛夛紱 qual_op_cost綺楃暐鏉ヨ村氨鏄鎸囨暟鐨勮瘎浼版垚鏈錛屽間負0.0025銆傞夋嫨鎬ч夋嫨鎬ф槸鎸囧畾WHERE瀛愬彞瀵圭儲寮曠殑鎼滅儲鑼冨洿鐨勬瘮渚嬶紱瀹冩槸涓涓浠 0 鍒 1 鐨勬誕鐐規暟
鏌ヨ㈣皳璇嶇殑閫夋嫨鐜囨槸閫氳繃鐩存柟鍥劇晫鍊間笌楂橀戝間及璁＄殑錛岃繖浜涗俊鎮閮藉偍瀛樺湪緋葷粺鐩褰昿g_staticstics涓錛屽苟鍙閫氳繃pg_stats瑙嗗浘鏌ヨ銆
琛ㄤ腑鐨勬瘡涓鍒楃殑楂橀戝奸兘鍦╬g_stats瑙嗗浘鐨刴ost_common_vals鍜宮ost_common_freqs涓鎴愬瑰瓨鍌ㄣ
鎺掑簭璺寰勪細鍦ㄦ帓搴忔搷浣滀腑琚浣跨敤銆傛帓搴忔搷浣滃寘鎷琽rder by銆佸綊騫惰繛鎺ョ殑棰勫勭悊鎿嶄綔錛屼互鍙婂叾浠栧嚱鏁般傚嚱鏁癱ost_sort()鐢ㄤ簬浼拌℃帓搴忔搷浣滅殑浠ｄ環銆傚傛灉鑳藉湪宸ヤ綔鍐呭瓨涓鏀句笅鎵鏈夊厓緇勶紝閭ｄ箞鎺掑簭鎿嶄綔浼氶夌敤蹇閫熸帓搴忕畻娉曘傚惁鍒欏氨浼氬壋寤轟復鏃舵枃浠訛紝浣跨敤鏂囦歡褰掑苟鎺掑簭綆楁硶銆
鎺掑簭璺寰勭殑鍚鍔ㄤ唬浠峰氨鏄瀵圭洰鏍囪〃鐨勬帓搴忎唬浠鳳紝鍥犳や唬浠峰氨鏄疧(Nsort) * Log 2 (Nsort)錛岃繖閲孨sort灝辨槸甯︽帓搴忕殑鍏冪粍鏁般傛帓搴忚礬寰勭殑榪愯屼唬浠峰氨鏄璇誨彇宸茬粡鎺掑簭濂界殑鍏冪粍鐨勪唬浠鳳紝鍥犳や唬浠峰氨鏄疧錛圢sort錛夈
PostgreSQL涓鐨勮″垝鍣ㄤ細鎵ц屼笁涓姝ラわ細
璁塊棶璺寰勬槸浼扮畻浠ｄ環鏃剁殑澶勭悊鍗曞厓銆傛瘮濡傞『搴忔壂鎻忋佺儲寮曟壂鎻忋佹帓搴忥紝浠ュ強鍚勭嶈繛鎺ユ搷浣滈兘鏈夊叾瀵瑰簲鐨勮礬寰勩傝塊棶璺寰勫彧鍦ㄨ″垝鍣ㄥ壋寤烘煡璇㈣″垝鏍戠殑鏃跺欎嬌鐢ㄣ傛渶蹇屾湰鐨勮塊棶璺寰勬暟鎹緇撴瀯灝辨槸relation.h涓瀹氫箟鐨刾ath緇撴瀯浣擄紝鐩稿綋浜庨『搴忔壂鎻忋傛墍鏈夊叾浠栫殑璺寰勮塊棶閮藉熀浜庤ョ粨鏋勩
鍦ㄥ壋寤鴻″垝鏍戜箣鍓嶏紝璁″垝鍣ㄥ皢綰垮筆lannerInfo涓鐨勬煡璇涔﹁繘琛屼竴浜涢勫勭悊銆傞勫勭悊鏈夊緢澶氭ラわ紝鏈鑺傚艱ㄨ哄拰鍗曡〃鏌ヨ㈠勭悊鐩稿叧鐨勪富瑕佹ラゃ
璁″垝鍣ㄥ規墍鏈夊彲鑳界殑璁塊棶璺寰勮繘琛屼唬浠蜂及璁★紝鐒跺悗閫夋嫨浠ｄ環鏈灝忕殑閭ｄ釜銆
鍦ㄦ渶鍚庝竴姝ヤ腑錛岃″垝鍣ㄦ寜鐓т唬浠鋒渶灝忕殑璺寰勭敓鎴愪竴棰楄″垝鏍戙
璁″垝鏍戠殑鏍硅妭鐐規槸瀹氫箟鍦╬lannodes.h涓鐨凱lannedstmt緇撴瀯錛屽寘鍚19涓瀛楁碉紝鍏朵腑鏈4涓浠ｈ〃鎬у瓧孌碉細
璁″垝鏍戝寘鍚鍚勫紡鍚勬牱鐨勮″垝鑺傜偣銆侾lanNode鏄鎵鏈夎″垝鑺傜偣鐨勫熀綾伙紝鍏朵粬璁″垝鑺傜偣閮戒細鍖呭惈PlanNode緇撴瀯銆傛瘮濡傞『搴忔壂鎻忚妭鐐筍eqScanNode鍖呭惈涓涓狿lanNode鍜屼竴涓鏁村瀷鍙橀噺scanrelid銆侾lanNode鍖呭惈14涓瀛楁碉紝涓嬮潰鏄7涓浠ｈ〃鎬у瓧孌:
鍦ㄥ崟琛ㄦ煡璇㈢殑渚嬪瓙涓錛屾墽琛屽櫒浠庤″垝鏍戜腑鍙栧嚭璁″垝鑺傜偣錛屾寜鐓ц嚜搴曞悜涓婄殑欏哄簭榪涜屽勭悊錛屽苟璋冪敤鑺傜偣鐩稿簲鐨勫勭悊鍑芥暟銆
姣忎釜璁″垝鑺傜偣閮芥湁鐩稿簲鐨勫嚱鏁幫紝鐢ㄤ簬鎵ц岃妭鐐瑰瑰簲鐨勬搷浣溿傝繖浜涘嚱鏁板湪src/backend/executor鐩褰曚腑銆
鐞嗚В鎵ц屽櫒濡備綍宸ヤ綔鐨勬渶濂芥柟寮忥紝灝辨槸闃呰籩xplain鍛戒護鐨勮緭鍑恆
鎴戜滑鍙浠ヨ嚜搴曞悜涓婇槄璇籩xplain鐨勭粨鏋滐紝鏉ョ湅涓鐪嬫墽琛屽櫒鏄濡備綍宸ヤ綔鐨勩
絎鍏琛岋細棣栧厛錛屾墽琛屽櫒閫氳繃nodeSeqscan.c涓瀹氫箟鐨勫嚱鏁版墽琛岄『搴忔壂鎻忔搷浣溿
絎鍥涜岋細鐒跺悗錛屾墽琛屽櫒閫氳繃nodeSort.c涓瀹氫箟鐨勫嚱鏁幫紝瀵歸『搴忔壂鎻忕殑緇撴灉榪涜屾帓搴忋
鎵ц屽櫒鍦ㄥ勭悊鏌ヨ㈡椂浼氫嬌鐢ㄥ伐浣滃唴瀛樺拰涓存椂緙撳啿鍖猴紝涓よ呴兘鍦ㄥ唴瀛樹腑鍒嗛厤銆傚傛灉鏌ヨ㈡棤娉曞湪鍐呭瓨涓瀹屾垚錛屽氨浼氱敤鍒頒復鏃舵枃浠躲
浣跨敤甯︽湁Analyze閫夐」鐨別xplain錛屽緟瑙ｉ噴鐨勫懡浠や細鐪熸ｆ墽琛岋紝騫舵樉紺哄疄闄呯粨鏋滆屾暟銆佸疄闄呮墽琛屾椂闂村拰瀹為檯鍐呭瓨浣跨敤閲忋
鍦ㄧ6琛岋紝explain鍛戒護鏄劇ず鎵ц屽櫒浣跨敤浜10000KB鐨勪復鏃舵枃浠躲備復鏃舵枃浠朵細琚涓存椂鍒涘緩鍦╞ase/pg_tmp瀛愮洰褰曚腑錛屽苟閬靛驚濡備笅鍛戒護瑙勫垯錛氾經鈥減gsql_tmp鈥濓綕+ 鍀涘壋寤烘湰鏂囦歡鐨刾ostgres榪涚▼pid鍀.{浠0寮濮嬬殑搴忓垪鍙穧
姣斿傦紝涓存椂鏂囦歡pgsql_tmp8903.5鏄痯id涓8903鐨刾ostgres榪涚▼鍒涘緩鐨勭6涓涓存椂鏂囦歡銆
PostgreSQL涓鏀鎸佷笁縐嶈繛鎺ユ搷浣滐紝鍒嗗埆鏄宓屽楀驚鐜榪炴帴錛屽綊騫惰繛鎺ュ拰鏁ｅ垪榪炴帴銆傚湪pg涓錛屽祵濂楀驚鐜榪炴帴鍜屽綊騫惰繛鎺ユ湁鍑犵嶅彉浣撱
榪欎笁縐嶈繛鎺ユ柟寮忛兘鏀鎸乸g涓鎵鏈夌殑榪炴帴鎿嶄綔錛屾敞鍏inner join銆 left/right outer join銆 full outer join絳夈
寰鐜宓屽楄繛鎺ヤ笉闇瑕佷換浣曞惎鍔ㄤ唬浠鳳紝鍥犳わ細start-up cost = 0
榪愯屼唬浠峰拰鍐呭栬〃灝哄哥殑涔樼Н鎴愭瘮渚嬶紝鍗硆un cost鏄疧(Nouter * Ninner)錛 Nouter鍜孨inner鍒嗗埆鏄澶栬〃鍜屽唴琛ㄧ殑鍏冪粍鏉℃暟銆俽un cost鐨勫畾涔夊備笅錛
Couter鍜孋inner鍒嗗埆鏄鍐呰〃鍜屽栬〃欏哄簭鎵鎻忕殑浠ｄ環銆
寰鐜宓屽楄繛鎺ョ殑浠ｄ環鎬諱細琚浼拌★紝浣嗗疄闄呬腑寰堝皯浼氫嬌鐢ㄨ繖縐嶈繛鎺ユ搷浣滐紝鍥犱負瀹冩湁鍑犵嶆洿楂樻晥鐨勫彉浣撱
鍦ㄤ笂闈㈡弿榪扮殑寰鐜宓屽楄繛鎺ヤ腑錛屾瘡褰撹誨彇涓鏉″栬〃涓鐨勫厓緇勬椂錛岄兘闇瑕佹壂鎻忓唴鏍囦腑鐨勬墍鏈夊厓緇勩備綅姣忔潯澶栬〃鍏冪粍瀵瑰唴鏍囧仛鍏ㄨ〃鎵鎻忥紝榪欎竴榪囩▼浠ｄ環楂樻槀錛宲g鏀鎸佷竴縐嶇墿鍖栧祵濂楀驚鐜榪炴帴錛屽彲浠ュ噺灝戝唴鏍囧叏琛ㄦ壂鎻忕殑浠ｄ環銆
鍦ㄨ繍琛屽祵濂楀驚鐜榪炴帴涔嬪墠錛屾墽琛屽櫒浼氫嬌鐢ㄤ復鏃跺厓緇勫瓨鍌ㄦā鍧楀瑰唴琛ㄨ繘琛屼竴嬈℃壂鎻忥紝灝嗗唴琛ㄥ厓緇勫姞杞藉埌宸ヤ綔鎴栦復鏃舵枃浠朵腑銆傚湪澶勭悊鍐呰〃鍏冪粍鏃訛紝涓存椂鍏冪粍瀛樺偍姣旂紦鍐插尯綆＄悊鍣ㄦ洿涓洪珮鏁堬紝鐗瑰埆鏄褰撴墍鏈夌殑鍏冪粍閮借兘鏀懼叆宸ヤ綔鍐呭瓨涓銆
qg鍐呴儴鎻愪緵浜嗕復鏃跺厓緇勫瓨鍌ㄧ殑妯″潡錛屽彲鐢ㄤ簬鍚勭嶆搷浣滐紝濡備簲鑺辮啒銆佸壋寤烘販鍚堟暎鍒楄繛鎺ョ殑鎵規＄瓑銆傝ユā鍧楀寘鍚涓緋誨垪鍑芥暟錛岄兘鍦╰uplestore.c涓銆傝繖浜涘嚱鏁扮敤浜庝粠宸ヤ綔鍐呭瓨鎴栦復鏃舵枃浠惰誨啓鍏冪粍銆傝ュ伐浣滃唴瀛樿繕鏄涓存椂鏂囦歡鍙栧喅浜庡緟瀛樺偍鍏冪粍鐨勬繪暟銆
涓婇潰鏄劇ず浜嗘墽琛屽櫒瑕佽繘琛岀殑鎿嶄綔錛屾墽琛屽櫒瀵硅繖浜涜″垝鑺傜偣鐨勫勭悊榪囩▼濡備笅錛
絎7琛岋細鎵ц屽櫒浣跨敤欏哄簭鎵鎻忥紝鐗╁寲鍐呴儴琛╰bl_b銆
絎4琛岋細鎵ц屽櫒鎵ц屽祵濂楀驚鐜榪炴帴鎿嶄綔錛屽栬〃鏄痶bl_a錛屽唴琛ㄦ槸鐗╁寲鐨則bl_b銆
濡傛灉鍐呰〃涓婃湁緔㈠紩錛屼笖璇ョ儲寮曡兘鐢ㄤ簬鎼滅儲婊¤凍榪炴帴鏉′歡鐨勫厓緇勶紝閭ｄ箞璁″垝鍣ㄥ湪澶栧栬〃鐨勬瘡鏉″厓緇勬悳緔㈠唴鏍囦腑鐨勫尮閰嶅厓緇勬椂錛屼細鑰冭檻浣跨敤緔㈠紩榪涜岀洿鎺ユ悳緔錛屼互鏇誇唬欏哄簭鎵鎻忋傝繖縐嶅彉浣撳彨鍋氱儲寮曞祵濂楀驚鐜榪炴帴錛屽備笅鍥炬墍紺恆傝櫧鐒惰繖縐嶅彉浣撳彨鍋氣滅儲寮曞祵濂楀驚鐜榪炴帴鈥濓紝浣嗘槸璋佽ョ畻娉曞熀鏈涓婂彧闇瑕佸湪澶栬〃涓婂驚鐜涓嬈★紝鍥犳よ繛鎺ユ搷浣滅殑鎵ц岄潪甯擱珮鏁堛
涓庡祵濂楀驚鐜榪炴帴涓嶅悓鐨勬槸錛屽綊騫惰繛鎺ュ彧鑳界敤浜庤嚜鐒惰繛鎺ヤ笌絳夊艱繛鎺ャ
鍑芥暟initial_cost_merge_join()鍜宖inal_cost_merge_join()鐢ㄤ簬浼拌″綊騫惰繛鎺ョ殑浠ｄ環銆
褰掑苟榪炴帴鐨勫惎鍔ㄦ垚鏈鏄鍐呰〃涓庡栬〃鎺掑簭鎴愭湰涔嬪拰錛屽洜姝ゅ叾鍚鍔ㄦ垚鏈涓猴細
榪欓噷Nouter鍜孨inner鍒嗗埆鏄澶栬〃鍜屽唴鏍囩殑鍏冪礌鏉℃暟錛岃岃繍琛屼唬浠鋒槸O(Nouter + Ninner)銆
涓嬪浘鏄褰掑苟榪炴帴鐨勭ず鎰忓浘銆
濡傛灉鎵鏈夊厓緇勯兘鍙浠ュ瓨鍌ㄥ湪鍐呭瓨涓錛岄偅涔堟帓搴忔搷浣滃氨鑳藉湪鍐呭瓨涓榪涜岋紝鍚﹀垯灝辨槸鐢ㄤ復鏃舵枃浠躲
絎9琛岋細鎵ц屽櫒瀵瑰唴琛╰bl_b榪涜屾帓搴忥紝浣跨敤欏哄簭鎵鎻忥紙絎11琛岋級銆
絎6琛岋細鎵ц屽櫒瀵瑰栬〃tbl_a榪涜屾帓搴忥紝浣跨敤欏哄簭鎵鎻(絎8琛)銆
絎4琛岋細鎵ц屽櫒鎵ц屽綊騫惰繛鎺ユ搷浣滐紝澶栬〃鏄鎺掑簭濂界殑tbl_a錛屽唴琛ㄦ槸鎺掑ソ搴忕殑tbl_b銆
涓庡祵濂楀驚鐜榪炴帴綾諱技錛屽綊騫惰繛鎺ヨ繕鏀鎸佺墿鍖栧綊騫惰繛鎺ワ紝鐗╁寲鍐呰〃錛屼嬌鍐呰〃鎵鎻忔洿涓洪珮鏁堛
涓嬮潰鏄鐗╁寲褰掑苟榪炴帴鐨別xplain緇撴灉錛屽緢瀹規槗鍙戠幇錛屼笌鏅閫氬綊騫惰繛鎺ョ殑宸寮傛槸絎9琛:Materialize銆
涓庡綊騫惰繛鎺ョ被浼礆紝hash榪炴帴鍙鑳界敤浜庤嚜鐒惰繛鎺ヤ笌絳夊艱繛鎺ャ
PostgreSQL涓鐨勬暎鍒楄繛鎺ョ殑琛屼負鍥犺〃鐨勫ぇ灝忚屽紓銆傚傛灉甯冩爣瓚沖熷皬錛堢『鍒囩殑璇達紝鍐呰〃澶у皬涓嶈秴榪囧伐浣滃唴瀛樼殑25%錛夛紝閭ｄ箞hash榪炴帴灝辨槸綆鍗曠殑涓ら樁孌靛唴瀛榟ash榪炴帴錛屽惁鍒欏皢浼氫嬌鐢ㄥ甫鍊炬枩鎵規＄殑娣峰悎hash榪炴帴銆
鍐呭瓨涓鐨刪ash榪炴帴鏄鍦╳ork_mem涓澶勭悊鐨勶紝鍦╬g涓錛屾暎鍒楄〃鍖哄煙琚縐頒綔澶勭悊鎵規°備竴涓鎵瑰勭悊鎵規′細鏈夊氫釜鏁ｅ垪妲斤紝鍐呴儴縐板叾涓烘《錛屾《鐨勬暟閲忕敱nodeHash.c涓瀹氫箟鐨凟xecChooseHashTableSize()鍑芥暟鎵紜瀹氥傛《鐨勬暟閲忔槸2鐨勬暣鏁版″籙銆
鍐呭瓨鏁ｅ垪榪炴帴鏈変袱涓闃舵碉紝鍒嗗埆鏄鏋勫緩闃舵靛拰鎺㈡祴闃舵點傚湪鏋勫緩闃舵碉紝鍐呭瓨琛ㄤ腑鐨勬墍鏈夊厓緇勯兘浼氳鎻掑叆鍒板勭悊鎵規′腑錛涘湪鎺㈡祴闃舵墊瘡鏉¤厱琛ㄥ厓緇勯兘浼氫笌澶勭悊鎵規′腑鐨勫唴琛ㄥ厓緇勬瘮杈冿紝濡傛灉婊¤凍榪炴帴鏉′歡錛屽垯灝嗕袱鏉″厓緇勮繛鎺ヨ搗鏉ャ
褰撳唴琛ㄧ殑鍏冪粍鏃犳硶鍏ㄩ儴瀛樺偍鍦ㄥ伐浣滃唴琛ㄤ腑鐨勫崟涓澶勭悊鎵規℃椂錛宲g浣跨敤甯﹀炬枩鎵規＄殑娣峰悎鏁ｅ垪榪炴帴綆楁硶錛岃ョ畻娉曟椂娣峰悎鏁ｅ垪榪炴帴璇剁殑涓縐嶅彉浣撱
鍦ㄧ涓涓鏋勫緩鍜屾帰嫻嬮樁孌祊ostgresql鍑嗗囧氫釜鎵規★紝瀹囬氱殑鏁扮洰綾諱技錛屽勭悊鎵規＄殑鏁版嵁鐢卞嚱鏁癊xecChooseHashTableSize()鍐沖畾錛屼篃灝辨槸2鐨勬暣鏁版″籙銆傚伐浣滃唴瀛樹腑鏅烘収鍒嗛厤涓涓澶勭悊鎵規★紝鑰屽叾浠栨壒嬈￠兘浠ヤ復鏃舵枃浠剁殑褰㈠紡鍒涘緩銆傚睘浜庤繖浜涙壒嬈＄殑鍏冪粍灝嗛氳繃涓存椂鍏冪粍瀛樺偍鍔熻兘琚鍐欏叆鍒扮浉搴旂殑鏂囦歡涓銆
涓轟簡鑾峰彇鏈浣寵″垝鏍戱紝璁″垝鍣ㄥ繀欏昏冭檻鍚勪釜緔㈠紩涓庡悇縐嶈繛鎺ユ柟娉曚箣闂寸殑鎵鏈夊彲鑳界粍鍚堛傚傛灉琛ㄧ殑鏁伴噺瓚呰繃鏌愪釜姘村鉤錛岃ヨ繃紼嬬殑浠ｄ環灝變細鍥犱負緇勫悎鐖嗙偢鑰屽彉寰楅潪甯告槀璐碉紝浠ヨ嚦浜庢牴鏈涓嶅彲琛屻
濡傛灉琛ㄧ殑鏁伴噺灝忎簬12寮狅紝璁″垝鍣ㄥ彲浠ヤ嬌鐢ㄥ姩鎬佽勫垝鏉ヨ幏鍙栨渶浣寵″垝銆