㈠ 如何正確使用資料庫索引
問題補充：能不能具體點，新建一個索引就可以了嗎
基本上可以這么說，不過你也可以修改索引。
記住：
索引其實關鍵目的是為了加快檢索速度而建立的，所以，怎麼用索引是資料庫系統本身的事情，作為資料庫設計或使用者，設計並創建好索引然後體驗加上索引後的查詢變快的感覺就行了。所以，索引怎麼用就變為了「怎麼創建合適的索引」
以下回答是否符合你的要求？你還有什麼問題？
第一次回答：
一、索引是什麼
索引是與表或視圖關聯的磁碟上結構，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列生成的鍵。這些鍵存儲在一個結構（B 樹）中，使 sql Server 可以快速有效地查找與鍵值關聯的行。
表或視圖可以包含以下類型的索引：
* 聚集
o 聚集索引根據數據行的鍵值在表或視圖中排序和存儲這些數據行。索引定義中包含聚集索引列。每個表只能有一個聚集索引，因為數據行本身只能按一個順序排序。
o 只有當表包含聚集索引時，表中的數據行才按排序順序存儲。如果表具有聚集索引，則該表稱為聚集表。如果表沒有聚集索引，則其數據行存儲在一個稱為堆的無序結構中。
* 非聚集
o 非聚集索引具有獨立於數據行的結構。非聚集索引包含非聚集索引鍵值，並且每個鍵值項都有指向包含該鍵值的數據行的指針。
o 從非聚集索引中的索引行指向數據行的指針稱為行定位器。行定位器的結構取決於數據頁是存儲在堆中還是聚集表中。對於堆，行定位器是指向行的指針。對於聚集表，行定位器是聚集索引鍵。
o 您可以向非聚集索引的葉級添加非鍵列以跳過現有的索引鍵限制（900 位元組和 16 鍵列），並執行完整范圍內的索引查詢。
聚集索引和非聚集索引都可以是唯一的。這意味著任何兩行都不能有相同的索引鍵值。另外，索引也可以不是唯一的，即多行可以共享同一鍵值。
每當修改了表數據後，都會自動維護表或視圖的索引。
索引和約束
對表列定義了 PRIMARY KEY 約束和 UNIQUE 約束時，會自動創建索引。例如，如果創建了表並將一個特定列標識為主鍵，則 資料庫引擎自動對該列創建 PRIMARY KEY 約束和索引。有關詳細信息，請參閱創建索引（資料庫引擎）。
二、索引有什麼用
與書中的索引一樣，資料庫中的索引使您可以快速找到表或索引視圖中的特定信息。索引包含從表或視圖中一個或多個列生成的鍵，以及映射到指定數據的存儲位置的指針。通過創建設計良好的索引以支持查詢，可以顯著提高資料庫查詢和應用程序的性能。索引可以減少為返回查詢結果集而必須讀取的數據量。索引還可以強製表中的行具有唯一性，從而確保表數據的數據完整性。
設計良好的索引可以減少磁碟 I/O 操作，並且消耗的系統資源也較少，從而可以提高查詢性能。對於包含 SELECT、UPDATE、DELETE 或 MERGE 語句的各種查詢，索引會很有用。例如，在 AdventureWorks 資料庫中執行的查詢 SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250。執行此查詢時，查詢優化器評估可用於檢索數據的每個方法，然後選擇最有效的方法。可能採用的方法包括掃描表和掃描一個或多個索引（如果有）。
掃描表時，查詢優化器讀取表中的所有行，並提取滿足查詢條件的行。掃描表會有許多磁碟 I/O 操作，並佔用大量資源。但是，如果查詢的結果集是占表中較高百分比的行，掃描表會是最為有效的方法。
查詢優化器使用索引時，搜索索引鍵列，查找到查詢所需行的存儲位置，然後從該位置提取匹配行。通常，搜索索引比搜索表要快很多，因為索引與表不同，一般每行包含的列非常少，且行遵循排序順序。
查詢優化器在執行查詢時通常會選擇最有效的方法。但如果沒有索引，則查詢優化器必須掃描表。您的任務是設計並創建最適合您的環境的索引，以便查詢優化器可以從多個有效的索引中選擇。SQL Server 提供的資料庫引擎優化顧問可以幫助分析資料庫環境並選擇適當的索引。
三、索引怎麼用
索引其實關鍵目的是為了加快檢索速度而建立的，所以，怎麼用索引是資料庫系統本身的事情，作為資料庫設計或使用者，設計並創建好索引然後體驗加上索引後的查詢變快的感覺就行了。所以，索引怎麼用就變為了「怎麼創建合適的索引」，以下說明這個問題：
索引設計不佳和缺少索引是提高資料庫和應用程序性能的主要障礙。設計高效的索引對於獲得良好的資料庫和應用程序性能極為重要。為資料庫及其工作負荷選擇正確的索引是一項需要在查詢速度與更新所需開銷之間取得平衡的復雜任務。如果索引較窄，或者說索引關鍵字中只有很少的幾列，則需要的磁碟空間和維護開銷都較少。而另一方面，寬索引可覆蓋更多的查詢。您可能需要試驗若干不同的設計，才能找到最有效的索引。可以添加、修改和刪除索引而不影響資料庫架構或應用程序設計。因此，應試驗多個不同的索引而無需猶豫。
SQL Server 中的查詢優化器可在大多數情況下可靠地選擇最高效的索引。總體索引設計策略應為查詢優化器提供可供選擇的多個索引，並依賴查詢優化器做出正確的決定。這在多種情況下可減少分析時間並獲得良好的性能。若要查看查詢優化器對特定查詢使用的索引，請在 SQL Server Management Studio 中的「查詢」菜單上選擇「包括實際的執行計劃」。
不要總是將索引的使用等同於良好的性能，或者將良好的性能等同於索引的高效使用。如果只要使用索引就能獲得最佳性能，那查詢優化器的工作就簡單了。但事實上，不正確的索引選擇並不能獲得最佳性能。因此，查詢優化器的任務是只在索引或索引組合能提高性能時才選擇它，而在索引檢索有礙性能時則避免使用它。
建議的索引設計策略包括以下任務：
1. 了解資料庫本身的特徵。例如，它是頻繁修改數據的聯機事務處理 (OLTP) 資料庫，還是主要包含只讀數據的決策支持系統 (DSS) 或數據倉庫 (OLAP) 資料庫？
2. 了解最常用的查詢的特徵。例如，了解到最常用的查詢聯接兩個或多個表將有助於決定要使用的最佳索引類型。
3. 了解查詢中使用的列的特徵。例如，某個索引對於含有整數數據類型同時還是唯一的或非空的列是理想索引。篩選索引適用於具有定義完善的數據子集的列。
4. 確定哪些索引選項可在創建或維護索引時提高性能。例如，對現有某個大型表創建聚集索引將會受益於 ONLINE 索引選項。ONLINE 選項允許在創建索引或重新生成索引時繼續對基礎數據執行並發活動。
5. 確定索引的最佳存儲位置。非聚集索引可以與基礎表存儲在同一個文件組中，也可以存儲在不同的文件組中。索引的存儲位置可通過提高磁碟 I/O 性能來提高查詢性能。例如，將非聚集索引存儲在表文件組所在磁碟以外的某個磁碟上的一個文件組中可以提高性能，因為可以同時讀取多個磁碟。
或者，聚集索引和非聚集索引也可以使用跨越多個文件組的分區方案。在維護整個集合的完整性時，使用分區可以快速而有效地訪問或管理數據子集，從而使大型表或索引更易於管理。有關詳細信息，請參閱已分區表和已分區索引。在考慮分區時，應確定是否應對齊索引，即，是按實質上與表相同的方式進行分區，還是單獨分區。
# 設計索引。
索引設計是一項關鍵任務。索引設計包括確定要使用的列，選擇索引類型（例如聚集或非聚集），選擇適當的索引選項，以及確定文件組或分區方案布置。
# 確定最佳的創建方法。按照以下方法創建索引：
* 使用 CREATE TABLE 或 ALTER TABLE 對列定義 PRIMARY KEY 或 UNIQUE 約束
SQL Server 資料庫引擎自動創建唯一索引來強制 PRIMARY KEY 或 UNIQUE 約束的唯一性要求。默認情況下，創建的唯一聚集索引可以強制 PRIMARY KEY 約束，除非表中已存在聚集索引或指定了唯一的非聚集索引。默認情況下，創建的唯一非聚集索引可以強制 UNIQUE 約束，除非已明確指定唯一的聚集索引且表中不存在聚集索引。
還可以指定索引選項和索引位置、文件組或分區方案。
創建為 PRIMARY KEY 或 UNIQUE 約束的一部分的索引將自動給定與約束名稱相同的名稱。
* 使用 CREATE INDEX 語句或 SQL Server Management Studio 對象資源管理器中的「新建索引」對話框創建獨立於約束的索引
必須指定索引的名稱、表以及應用該索引的列。還可以指定索引選項和索引位置、文件組或分區方案。默認情況下，如果未指定聚集或唯一選項，將創建非聚集的非唯一索引。若要創建篩選索引，請使用可選的 WHERE 子句。
# 創建索引。
要考慮的一個重要因素是對空表還是對包含數據的表創建索引。對空表創建索引在創建索引時不會對性能產生任何影響，而向表中添加數據時，會對性能產生影響。
對大型表創建索引時應仔細計劃，這樣才不會影響資料庫性能。對大型表創建索引的首選方法是先創建聚集索引，然後創建任何非聚集索引。在對現有表創建索引時，請考慮將 ONLINE 選項設置為 ON。該選項設置為 ON 時，將不持有長期表鎖以繼續對基礎表的查詢或更新。
㈡ MySQL資料庫優化（七）
為了能最小化磁碟I/O MyISAM 存儲引擎採用了很多資料庫系統使用的一種策略 它採用一種機制將最經常訪問的表保存在內存區塊中
對索引區塊來說 它維護著一個叫索引緩存(索引緩沖)的結構體 這個結構體中放著許多那些最常使用的索引區塊的緩沖區塊 對數據區塊來說 MySQL沒有使用特定的緩存 它依靠操作系統的本地文件系統緩存本章首先描述了 MyISAM 索引緩存的基本操作 然後討論在MySQL 中所做的改進 它提高了索引緩存性能 同時能更好地控制緩存操作
線程之間不再是串列地訪問索引緩存 多個線程可以並行地訪問索引緩存 可以設置多個索引緩存 同時也能指定數據表索引到特定的緩存中索引緩存機制對 ISAM 表同樣適用 不過 這種有效性正在減弱 自從MySQL 開始 MyISAM 表類型引進之後 ISAM 就不再建議使用了 MySQL 更是延續了這個趨勢 ISAM 類型默認被禁用了
可以通過系統變數 key_buffer_size 來控制索引緩存區塊的大小 如果這個值大小為 那麼就不使用緩存 當這個值小得於不足以分配區塊緩沖的最小數量( )時 也不會使用緩存
當索引緩存無法操作時 索引文件就只通過操作系統提供的本地文件系統緩沖來訪問(換言之 表索引區塊採用的訪問策略和數據區塊的一致)
一個索引區塊在 MyISAM 索引文件中數純升是一個連續訪問的單元 通常這個索引區塊的大小和B樹索引節點大小一樣薯老(索引在磁碟中是以B樹結構來表示的 這個樹的底部時葉子節點 葉子節點之上則是非葉子節點)
在索引緩存結構中所有的區塊大小都是一樣的 這個值可能等於 大於 或小於表的索引區塊大小 通常這兩個值是不一樣的
當必須訪問來自任何錶的索引區塊時 伺服器首先檢查在索引緩存中是否有可用的緩沖區塊 如果有 伺服器就訪問緩存中的數據 而非磁碟 就是說 它直接存取緩存 而不是存取磁碟 否則 伺服器選擇一個(多個)包含其它不同表索引區塊的緩存緩沖區塊 將它的內容替換成請求表的索引區塊的拷貝 一旦新的索引區塊在緩存中了 索引數據就可以存取了
當發生被選中要替換的區塊內容修改了的情況時 這個區塊就被認為 臟 了 那麼 在替換之前 它的內容就必須先刷新到它指向的標索引
通常伺服器遵循LRU(最近最少使用)策略 當要選擇替換的區塊時 它選擇最近最少使用的索引區塊 為了想要讓選擇變得更容易 索引緩存模塊會維護一個包含所有使用區塊特別的隊列(LRU鏈) 當一個區塊被訪問了 就把它放到隊列的最後位置 當區塊要被替換時 在隊列開始位置的區塊就是最近最少使用的 它就是第一候選刪除對象
共享訪問索引緩存
在MySQL 以前 訪問索引緩存是串列的 兩個線程不能並行地訪問索引緩存緩沖 伺服器處理一個訪問索引區塊的請求只能等它之前的請求處理完 結果 新的請求所需的索引區塊就不在任何索引緩存環沖區塊中 因為其他線程把包含這個索引區塊的緩沖給更新了
從MySQL 開始 伺服器支持共享方式訪問索引緩存
沒有正在被更新的緩沖可以被多個線程訪問
緩沖正被更新時 需要使用這個緩沖的線程只能等到更新完成之後
多個線程可以初始化需要替換緩存區塊的請求 只要它們不幹擾別的線程(也就是 它們請求不同的索引區塊 因此不同的緩存區塊被替換)
共享方式訪問索引緩存令伺服器明顯改善了吞吐量
多重索引緩存
共享訪問索引緩存改善了性能 卻不能完全消褲尺除線程間的沖突 它們仍然爭搶控制管理存取索引緩存緩沖的結構 為了更進一步減少索引緩存存取沖突 MySQL 提供了多重索引緩存特性 這能將不同的表索引指定到不同的索引緩存
當有多個索引緩存 伺服器在處理指定的 MyISAM 表查詢時必須知道該使用哪個 默認地 所有的 MyISAM 表索引都緩存在默認的索引緩存中 想要指定到特定的緩存中 可以使用 CACHE INDEX 語句
如下語句所示 指定表的索 t t 和 t 引緩存到名為 hot_cache 的緩存中
mysql>CACHEINDEXt t t INhot_cache; + + + + + |Table|Op|Msg_type|Msg_text| + + + + + |test t |assign_to_keycache|status|OK| |test t |assign_to_keycache|status|OK| |test t |assign_to_keycache|status|OK| + + + + +
注意 如果伺服器編譯支持存 ISAM 儲引擎了 那麼 ISAM 表也使用索引緩存機制 不過 ISAM 表索引只能使用默認的索引緩存而不能自定義
CACHE INDEX 語句中用到的索引緩存是根據用 SET GLOBAL 語句的參數設定的值或者伺服器啟動參數指定的值創建的 如下 mysql> SET GLOBAL keycache key_buffer_size= * ;想要刪除索引緩存 只需設置它的大小為 mysql> SET GLOBAL keycache key_buffer_size= ;索引緩存變數是一個結構體變數 由名字和組件構成 例如 keycache key_buffer_size keycache 就是緩存名 key_buffer_size 是緩存組件 默認地 表索引在伺服器啟動時指定到主(默認的)索引緩存中 當一個索引緩存被刪掉後 指定到這個緩存的所有索引都被重新指向到了默認索引緩存中去 對一個繁忙的系統來說 我們建議以下三條策略來使用索引緩存 熱緩存佔用 %的總緩存空間 用於繁重搜索但很少更新的表 冷緩存佔用 %的總緩存空間 用於中等強度更新的表 如臨時表 冷緩存佔用 %的總緩存空間 作為默認的緩存 用於所有其他表 使用三個緩存的一個原因是好處在於 存取一個緩存結構時不會阻止對其他緩存的訪問 訪問一個表索引的查詢不會跟指定到其他緩存的查詢競爭 性能提高還表現在以下幾點原因 熱緩存只用於檢索記錄 因此它的內容總是不需要變化 所以 無論什麼時候一個索引區塊需要從磁碟中引入 被選中要替換的緩存區塊的內容總是要先被刷新 索引被指向熱緩存中後 如果沒有需要掃描全部索引的查詢 那麼對應到B樹中非葉子節點的索引區塊極可能還保留在緩存中 在臨時表裡必須頻繁執行一個更新操作是相當快的 如果要被更新的節點已經在緩存中了 它無需先從磁碟中讀取出來 當臨時表的索引大小和冷緩存大小一樣時 那麼在需要更新一個節點時它已經在緩存中存在的幾率是相當高的
中點插入策略
默認地 MySQL 的索引緩存管理系統採用LRU策略來選擇要被清除的緩存區塊 不過它也支持更完善的方法 叫做 中點插入策略
使用中點插入策略時 LRU鏈就被分割成兩半 一個熱子鏈 一個溫子鏈 兩半分割的點不是固定的 不過緩存管理系統會注意不讓溫子鏈部分 太短 總是至少包括全部緩存區塊的 key_cache_division_limit 比率 key_cache_division_limit 是緩存結構體變數的組件部分 因此它是每個緩存都可以設置這個參數值
當一個索引區塊從表中讀入緩存時 它首先放在溫子鏈的末尾 當達到一定的點擊率(訪問這個區塊)後 它就提升到熱子鏈中去 目前 要提升一個區塊的點擊率( )對每個區塊來說都是一樣的 將來 我們會讓點擊率依靠B樹中對應的索引區塊節點的級別 包含非葉子節點的索引區塊所要求的提升點擊率就低一點 包含葉子節點的B索引樹的區塊的值就高點
提升起來的區塊首先放在熱子鏈的末尾 這個區塊在熱子鏈內一直循環 如果這個區塊在該子鏈開頭位置停留時間足夠長了 它就會被降級回溫子鏈 這個時間是由索引緩存結構體變數的組件 key_cache_age_threshold 值來決定的
這個閥值是這么描述的 一個索引緩存包含了 N 個區塊 熱子鏈開頭的區塊在低於 N*key_cache_age_threshold/ 次訪問後就被移動到溫子鏈的開頭位置 它又首先成為被刪除的候選對象 因為要被替換的區塊還是從溫子鏈的開頭位置開始的
中點插入策略就能在緩存中總能保持更有價值的區塊 如果更喜歡採用LRU策略 只需讓 key_cache_division_limit 的值低於默認值
中點插入策略能幫助改善在執行需要有效掃描索引 它會將所有對應到B樹中高級別的有價值的節點推出的查詢時的性能 為了避免這樣 就必須設定 key_cache_division_limit 遠遠低於 以採用中點插入策略 則在掃描索引操作時那些有價值的頻繁點擊的節點就會保留在熱子鏈中了
索引預載入
如果索引緩存中有足夠的區塊用來保存全部索引 或者至少足夠保存全部非葉子節點 那麼在使用前就載入索引緩存就很有意義了 將索引區塊以十分有效的方法預載入索引緩存緩沖 從磁碟中順序地讀取索引區塊
沒有預載入 查詢所需的索引區塊仍然需要被放到緩存中去 雖然索引區塊要保留在緩存中 因為有足夠的緩沖 它們可以從磁碟中隨機讀取到 而非順序地
想要預載入緩存 可以使用 LOAD INDEX INTO CACHE 語句 如下語句預載入了表 t 和 t 的索引節點(區塊)
mysql>LOADINDEXINTOCACHEt t IGNORELEAVES; + + + + + |Table|Op|Msg_type|Msg_text| + + + + + |test t |preload_keys|status|OK| |test t |preload_keys|status|OK| + + + + +
增加修飾語 IGNORE LEAVES 就只預載入非葉子節點的索引區塊 因此 上述語句載入了 t 的全部索引區塊 但是只載入 t 的非葉子節點區塊
如果使用 CACHE INDEX 語句將索引指向一個索引緩存 將索引區塊預先放到那個緩存中去 否則 索引區塊只會載入到默認的緩存中去
索引緩存大小
MySQL 引進了對每個索引緩存的新變數 key_cache_block_size 這個變數可以指定每個索引緩存的區塊大小 用它就可以來調整索引文件I/O操作的性能
當讀緩沖的大小和本地操作系統的I/O緩沖大小一樣時 就達到了I/O操作的最高性能了 但是設置索引節點的大小和I/O緩沖大小一樣未必能達到最好的總體性能 讀比較大的葉子節點時 伺服器會讀進來很多不必要的數據 這大大阻礙了讀其他葉子節點
目前 還不能控制數據表的索引區塊大小 這個大小在伺服器創建索引文件 ` MYI 時已經設定好了 它根據數據表的索引大小的定義而定 在很多時候 它設置成和I/O緩沖大小一樣 在將來 可以改變它的值 並且會全面採用變數 key_cache_block_size
重建索引緩存
索引緩存可以通過修改其參數值在任何時候重建它 例如
mysql>SETGLOBALcold_cache key_buffer_size= * * ;
如果設定索引緩存的結構體變數組件變數 key_buffer_size 或 key_cache_block_size 任何一個的值和它當前的值不一樣 伺服器就會清空原來的緩存 在新的變數值基礎上重建緩存 如果緩存中有任何的 臟 索引塊 伺服器會先把它們保存起來然後才重建緩存 重新設定其他的索引緩存變數並不會重建緩存
lishixin/Article/program/Oracle/201311/16615
㈢ mysql 核心內容-上
1、SQL語句執行流程
MySQL大體上可分為Server層和存儲引擎層兩部分。
Server層：
連接器：TCP握手後伺服器來驗證登陸用戶身份，A用戶創建連接後，管理員對A用戶許可權修改了也不會影響到已經創建的鏈接許可權，必須重新登陸。
查詢緩存：查詢後的結果存儲位置，MySQL8.0版本以後已經取消，因為查詢緩存失效太頻繁，得不償失。
分析器：根據語法規則，判斷你輸入的這個SQL語句是否滿足MySQL語法。
優化器：多種執行策略可實現目標，系統自動選擇最優進行執行。
執行器：判斷是否有許可權，將最終任務提交到存儲引擎。
存儲引擎層
負責數據的存儲和提取。其架構模式是插件式的，支持InnoDB、MyISAM、Memory等多個存儲引擎。現在最常用的存儲引擎是InnoDB，它從MySQL 5.5.5版本開始成為了默認存儲引擎(經常用的也是這個)。
SQL執行順序
2、BinLog、RedoLog、UndoLog
BinLog
BinLog是記錄所有資料庫表結構變更（例如create、alter table）以及表數據修改(insert、update、delete)的二進制日誌，主從資料庫同步用到的都是BinLog文件。BinLog日誌文件有三種模式。
STATEMENT 模式
內容：binlog 記錄可能引起數據變更的 sql 語句
優勢：該模式下，因為沒有記錄實際的數據，所以日誌量很少 IO 都消耗很低，性能是最優的
劣勢：但有些操作並不是確定的，比如 uuid() 函數會隨機產生唯一標識，當依賴 binlog 回放時，該操作生成的數據與原數據必然是不同的，此時可能造成無法預料的後果。
ROW 模式
內容：在該模式下，binlog 會記錄每次操作的源數據與修改後的目標數據，StreamSets就要求該模式。
優勢：可以絕對精準的還原，從而保證了數據的安全與可靠，並且復制和數據恢復過程可以是並發進行的
劣勢：缺點在於 binlog 體積會非常大，同時，對於修改記錄多、欄位長度大的操作來說，記錄時性能消耗會很嚴重。閱讀的時候也需要特殊指令來進行讀取數據。
MIXED 模式
內容：是對上述STATEMENT 跟 ROW 兩種模式的混合使用。
細節：對於絕大部分操作，都是使用 STATEMENT 來進行 binlog 沒有記錄，只有以下操作使用 ROW 來實現：表的存儲引擎為 NDB，使用了uuid() 等不確定函數，使用了 insert delay 語句，使用了臨時表
主從同步流程：
1、主節點必須啟用二進制日誌，記錄任何修改了資料庫數據的事件。
2、從節點開啟一個線程（I/O Thread)把自己扮演成 mysql 的客戶端，通過 mysql 協議，請求主節點的二進制日誌文件中的事件 。
3、主節點啟動一個線程（mp Thread），檢查自己二進制日誌中的事件，跟對方請求的位置對比，如果不帶請求位置參數，則主節點就會從第一個日誌文件中的第一個事件一個一個發送給從節點。
4、從節點接收到主節點發送過來的數據把它放置到中繼日誌（Relay log）文件中。並記錄該次請求到主節點的具體哪一個二進制日誌文件內部的哪一個位置（主節點中的二進制文件會有多個）。
5、從節點啟動另外一個線程（sql Thread ），把 Relay log 中的事件讀取出來，並在本地再執行一次。
mysql默認的復制方式是非同步的，並且復制的時候是有並行復制能力的。主庫把日誌發送給從庫後不管了，這樣會產生一個問題就是假設主庫掛了，從庫處理失敗了，這時候從庫升為主庫後，日誌就丟失了。由此產生兩個概念。
全同步復制
主庫寫入binlog後強制同步日誌到從庫，所有的從庫都執行完成後才返回給客戶端，但是很顯然這個方式的話性能會受到嚴重影響。
半同步復制
半同步復制的邏輯是這樣，從庫寫入日誌成功後返回ACK確認給主庫，主庫收到至少一個從庫的確認就認為寫操作完成。
還可以延伸到由於主從配置不一樣、主庫大事務、從庫壓力過大、網路震盪等造成主備延遲，如何避免這個問題？主備切換的時候用可靠性優先原則還是可用性優先原則？如何判斷主庫Crash了？互為主備的情況下如何避免主備循環復制？被刪庫跑路了如何正確恢復？( o )… 感覺越來越扯到DBA的活兒上去了。
RedoLog
可以先通過下面demo理解：
飯點記賬可以把賬單寫在賬本上也可以寫在粉板上。有人賒賬或者還賬的話，一般有兩種做法：
1、直接把賬本翻出來，把這次賒的賬加上去或者扣除掉。
2、先在粉板上記下這次的賬，等打烊以後再把賬本翻出來核算。
生意忙時選後者，因為前者太麻煩了。得在密密麻麻的記錄中找到這個人的賒賬總額信息，找到之後再拿出算盤計算，最後再將結果寫回到賬本上。
同樣在MySQL中如果每一次的更新操作都需要寫進磁碟，然後磁碟也要找到對應的那條記錄，然後再更新，整個過程IO成本、查找成本都很高。而粉板和賬本配合的整個過程就是MySQL用到的是Write-Ahead Logging 技術，它的關鍵點就是先寫日誌，再寫磁碟。此時賬本 = BinLog，粉板 = RedoLog。
1、 記錄更新時，InnoDB引擎就會先把記錄寫到RedoLog（粉板）裡面，並更新內存。同時，InnoDB引擎會在空閑時將這個操作記錄更新到磁碟裡面。
2、 如果更新太多RedoLog處理不了的時候，需先將RedoLog部分數據寫到磁碟，然後擦除RedoLog部分數據。RedoLog類似轉盤。
RedoLog有write pos 跟checkpoint
write pos ：是當前記錄的位置，一邊寫一邊後移，寫到第3號文件末尾後就回到0號文件開頭。
check point：是當前要擦除的位置，也是往後推移並且循環的，擦除記錄前要把記錄更新到數據文件。
write pos和check point之間的是粉板上還空著的部分，可以用來記錄新的操作。如果write pos追上checkpoint，表示粉板滿了，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把checkpoint推進一下。
有了redo log，InnoDB就可以保證即使資料庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為crash-safe。 redolog兩階段提交：為了讓binlog跟redolog兩份日誌之間的邏輯一致。提交流程大致如下：
1 prepare階段 --> 2 寫binlog --> 3 commit
當在2之前崩潰時，重啟恢復後發現沒有commit，回滾。備份恢復：沒有binlog 。一致
當在3之前崩潰時，重啟恢復發現雖沒有commit，但滿足prepare和binlog完整，所以重啟後會自動commit。備份：有binlog. 一致
binlog跟redolog區別：
redo log是InnoDB引擎特有的；binlog是MySQL的Server層實現的，所有引擎都可以使用。
redo log是物理日誌，記錄的是在某個數據頁上做了什麼修改；binlog是邏輯日誌，記錄的是這個語句的原始邏輯，比如給ID=2這一行的c欄位加1。
redo log是循環寫的，空間固定會用完；binlog是可以追加寫入的。追加寫是指binlog文件寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌。
UndoLog
UndoLog 一般是邏輯日誌，主要分為兩種：
insert undo log
代表事務在insert新記錄時產生的undo log, 只在事務回滾時需要，並且在事務提交後可以被立即丟棄
update undo log
事務在進行update或delete時產生的undo log; 不僅在事務回滾時需要，在快照讀時也需要；所以不能隨便刪除，只有在快速讀或事務回滾不涉及該日誌時，對應的日誌才會被purge線程統一清除
3、MySQL中的索引
索引的常見模型有哈希表、有序數組和搜索樹。
哈希表：一種以KV存儲數據的結構，只適合等值查詢，不適合范圍查詢。
有序數組：只適用於靜態存儲引擎，涉及到插入的時候比較麻煩。可以參考Java中的ArrayList。
搜索樹：按照數據結構中的二叉樹來存儲數據，不過此時是N叉樹(B+樹)。廣泛應用在存儲引擎層中。
B+樹比B樹優勢在於：
B+ 樹非葉子節點存儲的只是索引，可以存儲的更多。B+樹比B樹更加矮胖，IO次數更少。
B+ 樹葉子節點前後管理，更加方便范圍查詢。同時結果都在葉子節點，查詢效率穩定。
B+樹中更有利於對數據掃描，可以避免B樹的回溯掃描。
索引的優點：
1、唯一索引可以保證每一行數據的唯一性
2、提高查詢速度
3、加速表與表的連接
4、顯著的減少查詢中分組和排序的時間
5、通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。
索引的缺點：
1、創建跟維護都需要耗時
2、創建索引時，需要對表加鎖，在鎖表的同時，可能會影響到其他的數據操作
3、 索引需要磁碟的空間進行存儲，磁碟佔用也很快。
4、當對表中的數據進行CRUD的時，也會觸發索引的維護，而維護索引需要時間，可能會降低數據操作性能
索引設計的原則不應該：
1、索引不是越多越好。索引太多，維護索引需要時間跟空間。
2、 頻繁更新的數據，不宜建索引。
3、數據量小的表沒必要建立索引。
應該：
1、重復率小的列建議生成索引。因為重復數據少，索引樹查詢更有效率，等價基數越大越好。
2、數據具有唯一性，建議生成唯一性索引。在資料庫的層面，保證數據正確性
3、頻繁group by、order by的列建議生成索引。可以大幅提高分組和排序效率
4、經常用於查詢條件的欄位建議生成索引。通過索引查詢，速度更快
索引失效的場景
1、模糊搜索：左模糊或全模糊都會導致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。
2、隱式類型轉換：比如select * from t where name = xxx , name是字元串類型，但是沒有加引號，所以是由MySQL隱式轉換的，所以會讓索引失效 3、當語句中帶有or的時候：比如select * from t where name=『sw』 or age=14
4、不符合聯合索引的最左前綴匹配：(A,B,C)的聯合索引，你只where了C或B或只有B,C
關於索引的知識點：
主鍵索引：主鍵索引的葉子節點存的是整行數據信息。在InnoDB里，主鍵索引也被稱為聚簇索引（clustered index）。主鍵自增是無法保證完全自增的哦，遇到唯一鍵沖突、事務回滾等都可能導致不連續。
唯一索引：以唯一列生成的索引，該列不允許有重復值，但允許有空值(NULL)
普通索引跟唯一索引查詢性能：InnoDB的數據是按數據頁為單位來讀寫的，默認每頁16KB，因此這兩種索引查詢數據性能差別微乎其微。
change buffer：普通索引用在更新過程的加速，更新的欄位如果在緩存中，如果是普通索引則直接更新即可。如果是唯一索引需要將所有數據讀入內存來確保不違背唯一性，所以盡量用普通索引。
非主鍵索引：非主鍵索引的葉子節點內容是主鍵的值。在InnoDB里，非主鍵索引也被稱為二級索引（secondary index）
回表：先通過資料庫索引掃描出數據所在的行，再通過行主鍵id取出索引中未提供的數據，即基於非主鍵索引的查詢需要多掃描一棵索引樹。
覆蓋索引：如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，我們就稱之為覆蓋索引。
聯合索引：相對單列索引，組合索引是用多個列組合構建的索引，一次性最多聯合16個。
最左前綴原則：對多個欄位同時建立的組合索引(有順序，ABC，ACB是完全不同的兩種聯合索引) 以聯合索引(a,b,c)為例，建立這樣的索引相當於建立了索引a、ab、abc三個索引。另外組合索引實際還是一個索引，並非真的創建了多個索引，只是產生的效果等價於產生多個索引。
索引下推：MySQL 5.6引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的欄位先做判斷，過濾掉不符合條件的記錄，減少回表字數。
索引維護：B+樹為了維護索引有序性涉及到頁分裂跟頁合並。增刪數據時需考慮頁空間利用率。
自增主鍵：一般會建立與業務無關的自增主鍵，不會觸發葉子節點分裂。
延遲關聯：通過使用覆蓋索引查詢返回需要的主鍵，再根據主鍵關聯原表獲得需要的數據。
InnoDB存儲: * .frm文件是一份定義文件，也就是定義資料庫表是一張怎麼樣的表。*.ibd文件則是該表的索引，數據存儲文件，既該表的所有索引樹，所有行記錄數據都存儲在該文件中。
MyISAM存儲：* .frm文件是一份定義文件，也就是定義資料庫表是一張怎麼樣的表。* .MYD文件是MyISAM存儲引擎表的所有行數據的文件。* .MYI文件存放的是MyISAM存儲引擎表的索引相關數據的文件。MyISAM引擎下，表數據和表索引數據是分開存儲的。
MyISAM查詢：在MyISAM下，主鍵索引和輔助鍵索引都屬於非聚簇索引。查詢不管是走主鍵索引，還是非主鍵索引，在葉子結點得到的都是目的數據的地址，還需要通過該地址，才能在數據文件中找到目的數據。
PS：InnoDB支持聚簇索引，MyISAM不支持聚簇索引
4、SQL事務隔離級別
ACID的四個特性
原子性（Atomicity）：把多個操作放到一個事務中，保證這些操作要麼都成功，要麼都不成功
一致性（Consistency）：理解成一串對數據進行操作的程序執行下來，不會對數據產生不好的影響，比如憑空產生，或消失
隔離性（Isolation，又稱獨立性）：隔離性的意思就是多個事務之間互相不幹擾，即使是並發事務的情況下，他們只是兩個並發執行沒有交集，互不影響的東西；當然實現中，也不一定需要這么完整隔離性，即不一定需要這么的互不幹擾，有時候還是允許有部分干擾的。所以MySQL可以支持4種事務隔離性
持久性（Durability）：當某個操作操作完畢了，那麼結果就是這樣了，並且這個操作會持久化到日誌記錄中
PS：ACID中C與CAP定理中C的區別
ACID的C著重強調單資料庫事務操作時，要保證數據的完整和正確性，數據不會憑空消失跟增加。CAP 理論中的C指的是對一個數據多個備份的讀寫一致性
事務操作可能會出現的數據問題
1、臟讀(dirty read)：B事務更改數據還未提交，A事務已經看到並且用了。B事務如果回滾，則A事務做錯了
2、 不可重復讀(non-repeatable read)：不可重復讀的重點是修改: 同樣的條件, 你讀取過的數據, 再次讀取出來發現值不一樣了，只需要鎖住滿足條件的記錄
3、 幻讀(phantom read)：事務A先修改了某個表的所有紀錄的狀態欄位為已處理，未提交；事務B也在此時新增了一條未處理的記錄，並提交了；事務A隨後查詢記錄，卻發現有一條記錄是未處理的造成幻讀現象，幻讀僅專指新插入的行。幻讀會造成語義上的問題跟數據一致性問題。
4、 在可重復讀RR隔離級別下，普通查詢是快照讀，是不會看到別的事務插入的數據的。因此，幻讀在當前讀下才會出現。要用間隙鎖解決此問題。
在說隔離級別之前，你首先要知道，你隔離得越嚴實，效率就會越低。因此很多時候，我們都要在二者之間尋找一個平衡點。SQL標準的事務隔離級別由低到高如下： 上圖從上到下的模式會導致系統的並行性能依次降低，安全性依次提高。
讀未提交：別人改數據的事務尚未提交，我在我的事務中也能讀到。
讀已提交(Oracle默認)：別人改數據的事務已經提交，我在我的事務中才能讀到。
可重復讀(MySQL默認)：別人改數據的事務已經提交，我在我的事務中也不去讀，以此保證重復讀一致性。
串列：我的事務尚未提交，別人就別想改數據。
標准跟實現：上面都是關於事務的標准，但是每一種資料庫都有不同的實現，比如MySQL InnDB 默認為RR級別，但是不會出現幻讀。因為當事務A更新了所有記錄的某個欄位，此時事務A會獲得對這個表的表鎖，因為事務A還沒有提交，所以事務A獲得的鎖沒有釋放，此時事務B在該表插入新記錄，會因為無法獲得該表的鎖，則導致插入操作被阻塞。只有事務A提交了事務後，釋放了鎖，事務B才能進行接下去的操作。所以可以說 MySQL的RR級別的隔離是已經實現解決了臟讀，不可重復讀和幻讀的。
5、MySQL中的鎖
無論是Java的並發編程還是資料庫的並發操作都會涉及到鎖，研發人員引入了悲觀鎖跟樂觀鎖這樣一種鎖的設計思想。
悲觀鎖：
優點：適合在寫多讀少的並發環境中使用，雖然無法維持非常高的性能，但是在樂觀鎖無法提更好的性能前提下，可以做到數據的安全性
缺點：加鎖會增加系統開銷，雖然能保證數據的安全，但數據處理吞吐量低，不適合在讀書寫少的場合下使用
樂觀鎖：
優點：在讀多寫少的並發場景下，可以避免資料庫加鎖的開銷，提高DAO層的響應性能，很多情況下ORM工具都有帶有樂觀鎖的實現，所以這些方法不一定需要我們人為的去實現。
缺點：在寫多讀少的並發場景下，即在寫操作競爭激烈的情況下，會導致CAS多次重試，沖突頻率過高，導致開銷比悲觀鎖更高。
實現：資料庫層面的樂觀鎖其實跟CAS思想類似， 通數據版本號或者時間戳也可以實現。
資料庫並發場景主要有三種：
讀-讀：不存在任何問題，也不需要並發控制
讀-寫：有隔離性問題，可能遇到臟讀，幻讀，不可重復讀
寫-寫：可能存更新丟失問題，比如第一類更新丟失，第二類更新丟失
兩類更新丟失問題：
第一類更新丟失：事務A的事務回滾覆蓋了事務B已提交的結果 第二類更新丟失：事務A的提交覆蓋了事務B已提交的結果
為了合理貫徹落實鎖的思想，MySQL中引入了雜七雜八的各種鎖：
鎖分類
MySQL支持三種層級的鎖定，分別為
表級鎖定
MySQL中鎖定粒度最大的一種鎖，最常使用的MYISAM與INNODB都支持表級鎖定。
頁級鎖定
是MySQL中鎖定粒度介於行級鎖和表級鎖中間的一種鎖，表級鎖速度快，但沖突多，行級沖突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。
行級鎖定
Mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖。行級鎖能大大減少資料庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大行級鎖不一定比表級鎖要好：鎖的粒度越細，代價越高，相比表級鎖在表的頭部直接加鎖，行級鎖還要掃描找到對應的行對其上鎖，這樣的代價其實是比較高的，所以表鎖和行鎖各有所長。
MyISAM中的鎖
雖然MySQL支持表，頁，行三級鎖定，但MyISAM存儲引擎只支持表鎖。所以MyISAM的加鎖相對比較開銷低，但數據操作的並發性能相對就不高。但如果寫操作都是尾插入，那還是可以支持一定程度的讀寫並發
從MyISAM所支持的鎖中也可以看出，MyISAM是一個支持讀讀並發，但不支持通用讀寫並發，寫寫並發的資料庫引擎，所以它更適合用於讀多寫少的應用場合，一般工程中也用的較少。
InnoDB中的鎖
該模式下支持的鎖實在是太多了，具體如下：
共享鎖和排他鎖 (Shared and Exclusive Locks)
意向鎖（Intention Locks）
記錄鎖（Record Locks）
間隙鎖（Gap Locks）
臨鍵鎖 （Next-Key Locks）
插入意向鎖（Insert Intention Locks）
主鍵自增鎖 (AUTO-INC Locks)
空間索引斷言鎖（Predicate Locks for Spatial Indexes）
舉個栗子，比如行鎖里的共享鎖跟排它鎖：lock in share modle 共享讀鎖：
為了確保自己查到的數據沒有被其他的事務正在修改，也就是說確保查到的數據是最新的數據，並且不允許其他人來修改數據。但是自己不一定能夠修改數據，因為有可能其他的事務也對這些數據使用了 in share mode 的方式上了S 鎖。如果不及時的commit 或者rollback 也可能會造成大量的事務等待。
for update排它寫鎖:
為了讓自己查到的數據確保是最新數據，並且查到後的數據只允許自己來修改的時候，需要用到for update。相當於一個 update 語句。在業務繁忙的情況下，如果事務沒有及時的commit或者rollback 可能會造成其他事務長時間的等待，從而影響資料庫的並發使用效率。
Gap Lock間隙鎖：
1、行鎖只能鎖住行，如果在記錄之間的間隙插入數據就無法解決了，因此MySQL引入了間隙鎖(Gap Lock)。間隙鎖是左右開區間。間隙鎖之間不會沖突。
2、間隙鎖和行鎖合稱NextKeyLock，每個NextKeyLock是前開後閉區間。
間隙鎖加鎖原則(學完忘那種)：
1、加鎖的基本單位是 NextKeyLock，是前開後閉區間。
2、查找過程中訪問到的對象才會加鎖。
3、索引上的等值查詢，給唯一索引加鎖的時候，NextKeyLock退化為行鎖。
4、索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，NextKeyLock退化為間隙鎖。
5、唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。
㈣ 資料庫索引的作用
為什麼要創建索引呢？這是因為，創建索引可以大大提高系統的性能。第一，通過創建唯一性索引，可以保證資料庫表中每一行數據的唯一性。第二，可以大大加快 數據的檢索速度，這也是創建索引的最主要的原因。第三，可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。第四，在使用分組和排序 子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間。第五，通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。
也許會有人要問：增加索引有如此多的優點，為什麼不對表中的每一個列創建一個索引呢？這種想法固然有其合理性，然而也有其片面性。雖然，索引有許多優點， 但是，為表中的每一個列都增加索引，是非常不明智的。這是因為，增加索引也有許多不利的一個方面。第一，創建索引和維護索引要耗費時間，這種時間隨著數據 量的增加而增加。第二，索引需要佔物理空間，除了數據表占數據空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大。 第三，當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了數據的維護速度。
索引是建立在資料庫表中的某些列的上面。因此，在創建索引的時候，應該仔細考慮在哪些列上可以創建索引，在哪些列上不能創建索引。一般來說，應該在這些列 上創建索引，例如：在經常需要搜索的列上，可以加快搜索的速度；在作為主鍵的列上，強制該列的唯一性和組織表中數據的排列結構；在經常用在連接的列上，這 些列主要是一些外鍵，可以加快連接的速度；在經常需要根據范圍進行搜索的列上創建索引，因為索引已經排序，其指定的范圍是連續的；在經常需要排序的列上創 建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度。
同樣，對於有些列不應該創建索引。一般來說，不應該創建索引的的這些列具有下列特點：第一，對於那些在查詢中很少使用或者參考的列不應該創建索引。這是因 為，既然這些列很少使用到，因此有索引或者無索引，並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。第二，對於那 些只有很少數據值的列也不應該增加索引。這是因為，由於這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的數據行佔了表中數據行的很大比 例，即需要在表中搜索的數據行的比例很大。增加索引，並不能明顯加快檢索速度。第三，對於那些定義為text, image和bit數據類型的列不應該增加索引。這是因為，這些列的數據量要麼相當大，要麼取值很少。第四，當修改性能遠遠大於檢索性能時，不應該創建索 引。這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因 此，當修改性能遠遠大於檢索性能時，不應該創建索引。
創建索引的方法和索引的特徵
創建索引的方法 51aspx.com
創建索引有多種方法，這些方法包括直接創建索引的方法和間接創建索引的方法。直接創建索引，例如使用CREATE INDEX語句或者使用創建索引向導，間接創建索引，例如在表中定義主鍵約束或者唯一性鍵約束時，同時也創建了索引。雖然，這兩種方法都可以創建索引，但 是，它們創建索引的具體內容是有區別的。
使用CREATE INDEX語句或者使用創建索引向導來創建索引，這是最基本的索引創建方式，並且這種方法最具有柔性，可以定製創建出符合自己需要的索引。在使用這種方式 創建索引時，可以使用許多選項，例如指定數據頁的充滿度、進行排序、整理統計信息等，這樣可以優化索引。使用這種方法，可以指定索引的類型、唯一性和復合 性，也就是說，既可以創建聚簇索引，也可以創建非聚簇索引，既可以在一個列上創建索引，也可以在兩個或者兩個以上的列上創建索引。
通過定義主鍵約束或者唯一性鍵約束，也可以間接創建索引。主鍵約束是一種保持數據完整性的邏輯，它限製表中的記錄有相同的主鍵記錄。在創建主鍵約束時，系 統自動創建了一個唯一性的聚簇索引。雖然，在邏輯上，主鍵約束是一種重要的結構，但是，在物理結構上，與主鍵約束相對應的結構是唯一性的聚簇索引。換句話 說，在物理實現上，不存在主鍵約束，而只存在唯一性的聚簇索引。同樣，在創建唯一性鍵約束時，也同時創建了索引，這種索引則是唯一性的非聚簇索引。因此， 當使用約束創建索引時，索引的類型和特徵基本上都已經確定了，由用戶定製的餘地比較小。
當在表上定義主鍵或者唯一性鍵約束時，如果表中已經有了使用CREATE INDEX語句創建的標准索引時，那麼主鍵約束或者唯一性鍵約束創建的索引覆蓋以前創建的標准索引。也就是說，主鍵約束或者唯一性鍵約束創建的索引的優先 級高於使用CREATE INDEX語句創建的索引。
索引的特徵
索引有兩個特徵，即唯一性索引和復合索引。
唯一性索引保證在索引列中的全部數據是唯一的，不會包含冗餘數據。如果表中已經有一個主鍵約束或者唯一性鍵約束，那麼當創建表或者修改表時，SQL Server自動創建一個唯一性索引。然而，如果必須保證唯一性，那麼應該創建主鍵約束或者唯一性鍵約束，而不是創建一個唯一性索引。當創建唯一性索引 時，應該認真考慮這些規則：當在表中創建主鍵約束或者唯一性鍵約束時，SQL Server自動創建一個唯一性索引；如果表中已經包含有數據，那麼當創建索引時，SQL Server檢查表中已有數據的冗餘性；每當使用插入語句插入數據或者使用修改語句修改數據時，SQL Server檢查數據的冗餘性：如果有冗餘值，那麼SQL Server取消該語句的執行，並且返回一個錯誤消息；確保表中的每一行數據都有一個唯一值，這樣可以確保每一個實體都可以唯一確認；只能在可以保證實體 完整性的列上創建唯一性索引，例如，不能在人事表中的姓名列上創建唯一性索引，因為人們可以有相同的姓名。
復合索引就是一個索引創建在兩個列或者多個列上。在搜索時，當兩個或者多個列作為一個關鍵值時，最好在這些列上創建復合索引。當創建復合索引時，應該考慮 這些規則：最多可以把16個列合並成一個單獨的復合索引，構成復合索引的列的總長度不能超過900位元組，也就是說復合列的長度不能太長；在復合索引中，所 有的列必須來自同一個表中，不能跨表建立復合列；在復合索引中，列的排列順序是非常重要的，因此要認真排列列的順序，原則上，應該首先定義最唯一的列，例 如在（COL1，COL2）上的索引與在（COL2，COL1）上的索引是不相同的，因為兩個索引的列的順序不同；為了使查詢優化器使用復合索引，查詢語 句中的WHERE子句必須參考復合索引中第一個列；當表中有多個關鍵列時，復合索引是非常有用的；使用復合索引可以提高查詢性能，減少在一個表中所創建的 索引數量。
索引的類型
根據索引的順序與數據表的物理順序是否相同，可以把索引分成兩種類型。一種是數據表的物理順序與索引順序相同的聚簇索引，另一種是數據表的物理順序與索引順序不相同的非聚簇索引。
聚簇索引的體系結構
索引的結構類似於樹狀結構，樹的頂部稱為葉級，樹的其它部分稱為非葉級，樹的根部在非葉級中。同樣，在聚簇索引中，聚簇索引的葉級和非葉級構成了一個樹狀 結構，索引的最低級是葉級。在聚簇索引中，表中的數據所在的數據頁是葉級，在葉級之上的索引頁是非葉級，索引數據所在的索引頁是非葉級。在聚簇索引中，數 據值的順序總是按照升序排列。
應該在表中經常搜索的列或者按照順序訪問的列上創建聚簇索引。當創建聚簇索引時，應該考慮這些因素：每一個表只能有一個聚簇索引，因為表中數據的物理順序 只能有一個；表中行的物理順序和索引中行的物理順序是相同的，在創建任何非聚簇索引之前創建聚簇索引，這是因為聚簇索引改變了表中行的物理順序，數據行按 照一定的順序排列，並且自動維護這個順序；關鍵值的唯一性要麼使用UNIQUE關鍵字明確維護，要麼由一個內部的唯一標識符明確維護，這些唯一性標識符是 系統自己使用的，用戶不能訪問；聚簇索引的平均大小大約是數據表的百分之五，但是，實際的聚簇索引的大小常常根據索引列的大小變化而變化；在索引的創建過 程中，SQL Server臨時使用當前資料庫的磁碟空間，當創建聚簇索引時，需要1.2倍的表空間的大小，因此，一定要保證有足夠的空間來創建聚簇索引。
當系統訪問表中的數據時，首先確定在相應的列上是否存在有索引和該索引是否對要檢索的數據有意義。如果索引存在並且該索引非常有意義，那麼系統使用該索引 訪問表中的記錄。系統從索引開始瀏覽到數據，索引瀏覽則從樹狀索引的根部開始。從根部開始，搜索值與每一個關鍵值相比較，確定搜索值是否大於或者等於關鍵 值。這一步重復進行，直到碰上一個比搜索值大的關鍵值，或者該搜索值大於或者等於索引頁上所有的關鍵值為止。
非聚簇索引的體系結構
非聚簇索引的結構也是樹狀結構，與聚簇索引的結構非常類似，但是也有明顯的不同。
在非聚簇索引中，葉級僅包含關鍵值，而沒有包含數據行。非聚簇索引表示行的邏輯順序。 非聚簇索引有兩種體系結構：一種體系結構是在沒有聚簇索引的表上創建非聚簇索引，另一種體系結構是在有聚簇索引的表上創建非聚簇索引。
如果一個數據表中沒有聚簇索引，那麼這個數據表也稱為數據堆。當非聚簇索引在數據堆的頂部創建時，系統使用索引頁中的行標識符指向數據頁中的記錄。行標識 符存儲了數據所在位置的信息。數據堆是通過使用索引分配圖（IAM）頁來維護的。IAM頁包含了數據堆所在簇的存儲信息。在系統表sysindexes 中，有一個指針指向了與數據堆相關的第一個IAM頁。系統使用IAM頁在數據堆中瀏覽和尋找可以插入新的記錄行的空間。這些數據頁和在這些數據頁中的記錄 沒有任何的順序並且也沒有鏈接在一起。在這些數據頁之間的唯一的連接是IAM中記錄的順序。當在數據堆上創建了非聚簇索引時，葉級中包含了指向數據頁的行 標識符。行標識符指定記錄行的邏輯順序，由文件ID、頁號和行ID組成。這些行的標識符維持唯一性。非聚簇索引的葉級頁的順序不同於表中數據的物理順序。 這些關鍵值在葉級中以升序維持。
當非聚簇索引創建在有聚簇索引的表上的時候，系統使用索引頁中的指向聚簇索引的聚簇鍵。聚簇鍵存儲了數據的位置信息。如果某一個表有聚簇索引，那麼非聚簇 索引的葉級包含了映射到聚簇鍵的聚簇鍵值，而不是映射到物理的行標識符。當系統訪問有非聚簇索引的表中數據時，並且這種非聚簇索引創建在聚簇索引上，那麼 它首先從非聚簇索引來找到指向聚簇索引的指針，然後通過使用聚簇索引來找到數據。
當需要以多種方式檢索數據時，非聚簇索引是非常有用的。當創建非聚簇索引時，要考慮這些情況：在預設情況下，所創建的索引是非聚簇索引；在每一個表上面，可以創建不多於249個非聚簇索引，而聚簇索引最多隻能有一個。
系統如何訪問表中的數據
一般地，系統訪問資料庫中的數據，可以使用兩種方法：表掃描和索引查找。第一種方法是表掃描，就是指系統將指針放置在該表的表頭數據所在的數據頁上，然後 按照數據頁的排列順序，一頁一頁地從前向後掃描該表數據所佔有的全部數據頁，直至掃描完表中的全部記錄。在掃描時，如果找到符合查詢條件的記錄，那麼就將 這條記錄挑選出來。最後，將全部挑選出來符合查詢語句條件的記錄顯示出來。第二種方法是使用索引查找。索引是一種樹狀結構，其中存儲了關鍵字和指向包含關 鍵字所在記錄的數據頁的指針。當使用索引查找時，系統沿著索引的樹狀結構，根據索引中關鍵字和指針，找到符合查詢條件的的記錄。最後，將全部查找到的符合 查詢語句條件的記錄顯示出來。
在SQL Server中，當訪問資料庫中的數據時，由SQL Server確定該表中是否有索引存在。如果沒有索引，那麼SQL Server使用表掃描的方法訪問資料庫中的數據。查詢處理器根據分布的統計信息生成該查詢語句的優化執行規劃，以提高訪問數據的效率為目標，確定是使用 表掃描還是使用索引。
索引的選項
在創建索引時，可以指定一些選項，通過使用這些選項，可以優化索引的性能。這些選項包括FILLFACTOR選項、PAD_INDEX選項和SORTED_DATA_REORG選項。
使用FILLFACTOR選項，可以優化插入語句和修改語句的性能。當某個索引頁變滿時，SQL Server必須花費時間分解該頁，以便為新的記錄行騰出空間。使用FILLFACTOR選項，就是在葉級索引頁上分配一定百分比的自由空間，以便減少頁 的分解時間。當在有數據的表中創建索引時，可以使用FILLFACTOR選項指定每一個葉級索引節點的填充的百分比。預設值是0，該數值等價於100。在 創建索引的時候，內部索引節點總是留有了一定的空間，這個空間足夠容納一個或者兩個表中的記錄。在沒有數據的表中，當創建索引的時候，不要使用該選項，因 為這時該選項是沒有實際意義的。另外，該選項的數值在創建時指定以後，不能動態地得到維護，因此，只應該在有數據的表中創建索引時才使用。
PAD_INDEX選項將FILLFACTOR選項的數值同樣也用於內部的索引節點，使內部的索引節點的填充度與葉級索引的節點中的填充度相同。如果沒有 指定FILLFACTOR選項，那麼單獨指定PAD_INDEX選項是沒有實際意義的，這是因為PAD_INDEX選項的取值是由FILLFACTOR選 項的取值確定的。
當創建聚簇索引時，SORTED_DATA_REORG選項清除排序，因此可以減少建立聚簇索引所需要的時間。當在一個已經變成碎塊的表上創建或者重建聚 簇索引時，使用SORTED_DATA_REORG選項可以壓縮數據頁。當重新需要在索引上應用填充度時，也使用該選項。當使用 SORTED_DATA_REORG選項時，應該考慮這些因素：SQL Server確認每一個關鍵值是否比前一個關鍵值高，如果都不高，那麼不能創建索引；SQL Server要求1.2倍的表空間來物理地重新組織數據；使用SORTED_DATA_REORG選項，通過清除排序進程而加快索引創建進程；從表中物理 地拷貝數據；當某一個行被刪除時，其所佔的空間可以重新利用；創建全部非聚簇索引；如果希望把葉級頁填充到一定的百分比，可以同時使用 FILLFACTOR選項和SORTED_DATA_REORG選項。
索引的維護
為了維護系統性能，索引在創建之後，由於頻繁地對數據進行增加、刪除、修改等操作使得索引頁發生碎塊，因此，必須對索引進行維護。
使用DBCC SHOWCONTIG語句，可以顯示表的數據和索引的碎塊信息。當執行DBCC SHOWCONTIG語句時，SQL Server瀏覽葉級上的整個索引頁，來確定表或者指定的索引是否嚴重碎塊。DBCC SHOWCONTIG語句還能確定數據頁和索引頁是否已經滿了。當對表進行大量的修改或者增加大量的數據之後，或者表的查詢非常慢時，應該在這些表上執行 DBCC SHOWCONTIG語句。當執行DBCC SHOWCONTIG語句時，應該考慮這些因素：當執行DBCC SHOWCONTIG語句時，SQL Server要求指定表的ID號或者索引的ID號，表的ID號或者索引的ID號可以從系統表sysindexes中得到；應該確定多長時間使用一次 DBCC SHOWCONTIG語句，這個時間長度要根據表的活動情況來定，每天、每周或者每月都可以。
使用DBCC DBREINDEX語句重建表的一個或者多個索引。當希望重建索引和當表上有主鍵約束或者唯一性鍵約束時，執行DBCC DBREINDEX語句。除此之外，執行DBCC DBREINDEX語句還可以重新組織葉級索引頁的存儲空間、刪除碎塊和重新計算索引統計。當使用執行DBCC DBREINDEX語句時，應該考慮這些因素：根據指定的填充度，系統重新填充每一個葉級頁；使用DBCC DBREINDEX語句重建主鍵約束或者唯一性鍵約束的索引；使用SORTED_DATA_REORG選項可以更快地創建聚簇索引，如果沒有排列關鍵值， 那麼不能使用DBCC DBREINDEX語句；DBCC DBREINDEX語句不支持系統表。另外，還可以使用資料庫維護規劃向導自動地進行重建索引的進程。
統計信息是存儲在SQL Server中的列數據的樣本。這些數據一般地用於索引列，但是還可以為非索引列創建統計。SQL Server維護某一個索引關鍵值的分布統計信息，並且使用這些統計信息來確定在查詢進程中哪一個索引是有用的。查詢的優化依賴於這些統計信息的分布准確 度。查詢優化器使用這些數據樣本來決定是使用表掃描還是使用索引。當表中數據發生變化時，SQL Server周期性地自動修改統計信息。索引統計被自動地修改，索引中的關鍵值顯著變化。統計信息修改的頻率由索引中的數據量和數據改變數確定。例如，如 果表中有10000行數據，1000行數據修改了，那麼統計信息可能需要修改。然而，如果只有50行記錄修改了，那麼仍然保持當前的統計信息。除了系統自 動修改之外，用戶還可以通過執行UPDATE STATISTICS語句或者sp_updatestats系統存儲過程來手工修改統計信息。使用UPDATE STATISTICS語句既可以修改表中的全部索引，也可以修改指定的索引。
使用SHOWPLAN和STATISTICS IO語句可以分析索引和查詢性能。使用這些語句可以更好地調整查詢和索引。SHOWPLAN語句顯示在連接表中使用的查詢優化器的每一步以及表明使用哪一 個索引訪問數據。使用SHOWPLAN語句可以查看指定查詢的查詢規劃。當使用SHOWPLAN語句時，應該考慮這些因素。SET SHOWPLAN_ALL語句返回的輸出結果比SET SHOWPLAN_TEXT語句返回的輸出結果詳細。然而，應用程序必須能夠處理SET SHOWPLAN_ALL語句返回的輸出結果。SHOWPLAN語句生成的信息只能針對一個會話。如果重新連接SQL Server，那麼必須重新執行SHOWPLAN語句。STATISTICS IO語句表明輸入輸出的數量，這些輸入輸出用來返回結果集和顯示指定查詢的邏輯的和物理的I/O的信息。可以使用這些信息來確定是否應該重寫查詢語句或者 重新設計索引。使用STATISTICS IO語句可以查看用來處理指定查詢的I/O信息。
就象SHOWPLAN語句一樣，優化器隱藏也用來調整查詢性能。優化器隱藏可以對查詢性能提供較小的改進，並且如果索引策略發生了改變，那麼這種優化器隱 藏就毫無用處了。因此，限制使用優化器隱藏，這是因為優化器隱藏更有效率和更有柔性。當使用優化器隱藏時，考慮這些規則：指定索引名稱、當 index_id為0時為使用表掃描、當index_id為1時為使用聚簇索引；優化器隱藏覆蓋查詢優化器，如果數據或者環境發生了變化，那麼必須修改優 化器隱藏。
索引調整向導
索引調整向導是一種工具，可以分析一系列資料庫的查詢語句，提供使用一系列資料庫索引的建議，優化整個查詢語句的性能。對於查詢語句，需要指定下列內容：
查詢語句，這是將要優化的工作量
包含了這些表的資料庫，在這些表中，可以創建索引，提高查詢性能
在分析中使用的表
在分析中，考慮的約束條件，例如索引可以使用的最大磁碟空間
這里指的工作量，可以來自兩個方面：使用SQL Server捕捉的軌跡和包含了SQL語句的文件。索引調整向導總是基於一個已經定義好的工作量。如果一個工作量不能反映正常的操作，那麼它建議使用的索 引不是實際的工作量上性能最好的索引。索引調整向導調用查詢分析器，使用所有可能的組合評定在這個工作量中每一個查詢語句的性能。然後，建議在整個工作量 上可以提高整個查詢語句的性能的索引。如果沒有供索引調整向導來分析的工作量，那麼可以使用圖解器立即創建它。一旦決定跟蹤一條正常資料庫活動的描述樣 本，向導能夠分析這種工作量和推薦能夠提高資料庫工作性能的索引配置。
索引調整向導對工作量進行分析之後，可以查看到一系列的報告，還可以使該向導立即創建所建議的最佳索引，或者使這項工作成為一種可以調度的作業，或者生成一個包含創建這些索引的SQL語句的文件。
索引調整向導允許為SQL Server資料庫選擇和創建一種理想的索引組合和統計，而不要求對資料庫結構、工作量或者SQL Server內部達到專家的理解程度。總之，索引調整向導能夠作到以下幾個方面的工作：
通過使用查詢優化器來分析工作量中的查詢任務，向有大量工作量的資料庫推薦一種最佳的索引混合方式
分析按照建議作出改變之後的效果，包括索引的用法、表間查詢的分布和大量工作中查詢的工作效果
為少量查詢任務推薦調整資料庫的方法
通過設定高級選項如磁碟空間約束、最大的查詢語句數量和每個索引的最多列的數量等，允許定製推薦方式
圖解器
圖解器能夠實時抓取在伺服器中運行的連續圖片，可以選取希望監測的項目和事件，包括Transact-SQL語句和批命令、對象的用法、鎖定、安全事件和 錯誤。圖解器能夠過濾這些事件，僅僅顯示用戶關心的問題。可以使用同一台伺服器或者其他伺服器重復已經記錄的跟蹤事件，重新執行那些已經作了記錄的命令。 通過集中處理這些事件，就能夠很容易監測和調試SQL Server中出現的問題。通過對特定事件的研究，監測和調試SQL Server問題變得簡單多了。
查詢處理器
查詢處理器是一種可以完成許多工作的多用途的工具。在查詢處理器中，可以互動式地輸入和執行各種Transact-SQL語句，並且在一個窗口中可以同時 查看Transact-SQL語句和其結果集；可以在查詢處理器中同時執行多個Transact-SQL語句，也可以執行腳本文件中的部分語句；提供了一 種圖形化分析查詢語句執行規劃的方法，可以報告由查詢處理器選擇的數據檢索方法，並且可以根據查詢規劃調整查詢語句的執行，提出執行可以提高性能的優化索 引建議，這種建議只是針對一條查詢語句的索引建議，只能提高這一條查詢語句的查詢性能。
系統為每一個索引創建一個分布頁，統計信息就是指存儲在分布頁上的某一個表中的一個或者多個索引的關鍵值的分布信息。當執行查詢語句時，為了提高查詢速度 和性能，系統可以使用這些分布信息來確定使用表的哪一個索引。查詢處理器就是依賴於這些分布的統計信息，來生成查詢語句的執行規劃。執行規劃的優化程度依 賴於這些分布統計信息的准確步驟的高低程度。如果這些分布的統計信息與索引的物理信息非常一致，那麼查詢處理器可以生成優化程度很高的執行規劃。相反，如 果這些統計信息與索引的實際存儲的信息相差比較大，那麼查詢處理器生成的執行規劃的優化程度則比較低。
查詢處理器從統計信息中提取索引關鍵字的分布信息，除了用戶可以手工執行UPDATE STATISTICS之外，查詢處理器還可以自動收集統計這些分布信息。這樣，就能夠充分保證查詢處理器使用最新的統計信息，保證執行規劃具有很高的優化 程度，減少了維護的需要。當然，使用查詢處理器生成的執行規劃，也有一些限制。例如，使用執行規劃只能提高單個查詢語句的性能，但是可能對整個系統的性能 產生正面的或者付面的影響，因此，要想提高整個系統的查詢性能，應該使用索引調整向導這樣的工具。
結論
在以前的SQL Server版本中，在一個查詢語句中，一個表上最多使用一個索引。而在SQL Server 7.0中，索引操作得到了增強。SQL Server現在使用索引插入和索引聯合演算法來實現在一個查詢語句中的可以使用多個索引。共享的行標識符用於連接同一個表上的兩個索引。如果某個表中有一 個聚簇索引，因此有一個聚簇鍵，那麼該表上的全部非聚簇索引的葉節點使用該聚簇鍵作為行定位器，而不是使用物理記錄標識符。如果表中沒有聚簇索引，那麼非 聚簇索引繼續使用物理記錄標識符指向數據頁。在上面的兩種情況中，行定位器是非常穩定的。當聚簇索引的葉節點分開時，由於行定位器是有效的，所以非聚簇索 引不需要被修改。如果表中沒有聚簇索引，那麼頁的分開就不會發生。而在以前的版本中，非聚簇索引使用物理記錄標識符如頁號和行號，作為行的定位器。例如， 如果聚簇索引（數據頁）發生分解時，許多記錄行被移動到了一個新的數據頁，因此有了多個新的物理記錄標識符。那麼，所有的非聚簇索引都必須使用這些新的物 理記錄標識符進行修改，這樣就需要耗費大量的時間和資源。
索引調整向導無論對熟練用戶還是新用戶，都是一個很好的工具。熟練用戶可以使用該向導創建一個基本的索引配置，然後在基本的索引配置上面進行調整和定製。新用戶可以使用該向導快速地創建優化的索引。
參考:
㈤ 資料庫索引的實現原理
資料庫索引的實現原理
一、概述資料庫索引，是資料庫管理系統中一個排序的數據結構，以協助快速查詢、更新資料庫表中數據。索引的實現通常使用B樹及其變種B+樹。在數據之外，資料庫系統還維護著滿足特定查找演算法的數據結構，這些數據結構以某種方式引用(指向)數據，這樣就可以在這些數據結構上實現高級查找演算法。這種數據結構，就是索引。其實說穿了，索引問題就是一個查找問題。二、索引的原理當我們的業務產生了大量的數據時，查找數據的效率問題也就隨之而來，所以我們可以通過為表設置索引，而為表設置索引要付出代價的：一是增加了資料庫的存儲空間，二是在插入和修改數據時要花費較多的時間(因為索引也要隨之變動)。
上圖展示了一種可能的索引方式。左邊是數據表，一共有兩列七條記錄，最左邊的是數據記錄的物理地址(注意邏輯上相鄰的記錄在磁碟上也並不是一定物理相鄰的)。為了加快Col2的查找，可以維護一個右邊所示的二叉查找樹，每個節點分別包含索引鍵值和一個指向對應數據記錄物理地址的指針，這樣就可以運用二叉查找在O(log2n)的復雜度內獲取到相應數據。索引是建立在資料庫表中的某些列的上面。在創建索引的時候，應該考慮在哪些列上可以創建索引，在哪些列上不能創建索引。一般來說，應該在這些列上創建索引：在經常需要搜索的列上，可以加快搜索的速度;在作為主鍵的列上，強制該列的唯一性和組織表中數據的排列結構;在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度;在經常需要根據范圍進行搜索的列上創建索引，因為索引已經排序，其指定的范圍是連續的;在經常需要排序的列上創建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間;在經常使用在WHERE子句中的列上面創建索引，加快條件的判斷速度。創建索引可以大大提高系統的性能第一，通過創建唯一性索引，可以保證資料庫表中每一行數據的唯一性。第二，可以大大加快數據的檢索速度，這也是創建索引的最主要的原因。第三，可以加速表和表之間的連接，特別是在實現數據的參考完整性方面特別有意義。第四，在使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間。第五，通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。也許會有人要問：增加索引有如此多的優點，為什麼不對表中的每一個列創建一個索引呢?因為，增加索引也有許多不利的方面。創建索引的弊端第一，創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。第二，索引需要佔物理空間，除了數據表占數據空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大。第三，當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了數據的維護速度。同樣，對於有些列不應該創建索引。一般來說，不應該創建索引的的這些列具有下列特點：第一，對於那些在查詢中很少使用或者參考的列不應該創建索引。這是因為，既然這些列很少使用到，因此有索引或者無索引，並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。第二，對於那些只有很少數據值的列也不應該增加索引。這是因為，由於這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的數據行佔了表中數據行的很大比例，即需要在表中搜索的數據行的比例很大。增加索引，並不能明顯加快檢索速度。第三，對於那些定義為text, image和bit數據類型的列不應該增加索引。這是因為，這些列的數據量要麼相當大，要麼取值很少。第四，當修改性能遠遠大於檢索性能時，不應該創建索引。這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因此，當修改性能遠遠大於檢索性能時，不應該創建索引。三、索引的類型根據資料庫的功能，可以在資料庫設計器中創建三種索引：唯一索引、主鍵索引和聚集索引。唯一索引唯一索引是不允許其中任何兩行具有相同索引值的索引。當現有數據中存在重復的鍵值時，大多數資料庫不允許將新創建的唯一索引與表一起保存。資料庫還可能防止添加將在表中創建重復鍵值的新數據。例如，如果在employee表中職員的姓(lname)上創建了唯一索引，則任何兩個員工都不能同姓。主鍵索引資料庫表經常有一列或列組合，其值唯一標識表中的每一行。該列稱為表的主鍵。在資料庫關系圖中為表定義主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特定類型。該索引要求主鍵中的每個值都唯一。當在查詢中使用主鍵索引時，它還允許對數據的快速訪問。聚集索引在聚集索引中，表中行的物理順序與鍵值的邏輯(索引)順序相同。一個表只能包含一個聚集索引。如果某索引不是聚集索引，則表中行的物理順序與鍵值的邏輯順序不匹配。與非聚集索引相比，聚集索引通常提供更快的數據訪問速度。四、局部性原理與磁碟預讀由於存儲介質的特性，磁碟本身存取就比主存慢很多，再加上機械運動耗費，磁碟的存取速度往往是主存的幾百分分之一，因此為了提高效率，要盡量減少磁碟I/O。為了達到這個目的，磁碟往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個位元組，磁碟也會從這個位置開始，順序向後讀取一定長度的數據放入內存。這樣做的理論依據是計算機科學中著名的局部性原理：當一個數據被用到時，其附近的數據也通常會馬上被使用。程序運行期間所需要的數據通常比較集中。由於磁碟順序讀取的效率很高(不需要尋道時間，只需很少的旋轉時間)，因此對於具有局部性的程序來說，預讀可以提高I/O效率。預讀的長度一般為頁(page)的整倍數。頁是計算機管理存儲器的邏輯塊，硬體及操作系統往往將主存和磁碟存儲區分割為連續的大小相等的塊，每個存儲塊稱為一頁(在許多操作系統中，頁得大小通常為4k)，主存和磁碟以頁為單位交換數據。當程序要讀取的數據不在主存中時，會觸發一個缺頁異常，此時系統會向磁碟發出讀盤信號，磁碟會找到數據的起始位置並向後連續讀取一頁或幾頁載入內存中，然後異常返回，程序繼續運行。五、B樹和B+樹數據結構1、B樹B樹中每個節點包含了鍵值和鍵值對於的數據對象存放地址指針，所以成功搜索一個對象可以不用到達樹的葉節點。成功搜索包括節點內搜索和沿某一路徑的搜索，成功搜索時間取決於關鍵碼所在的層次以及節點內關鍵碼的數量。在B樹中查找給定關鍵字的方法是：首先把根結點取來，在根結點所包含的關鍵字K1,…,kj查找給定的關鍵字(可用順序查找或二分查找法)，若找到等於給定值的關鍵字，則查找成功;否則，一定可以確定要查的關鍵字在某個Ki或Ki+1之間，於是取Pi所指的下一層索引節點塊繼續查找，直到找到，或指針Pi為空時查找失敗。2、B+樹B+樹非葉節點中存放的關鍵碼並不指示數據對象的地址指針，非也節點只是索引部分。所有的葉節點在同一層上，包含了全部關鍵碼和相應數據對象的存放地址指針，且葉節點按關鍵碼從小到大順序鏈接。如果實際數據對象按加入的順序存儲而不是按關鍵碼次數存儲的話，葉節點的索引必須是稠密索引，若實際數據存儲按關鍵碼次序存放的話，葉節點索引時稀疏索引。B+樹有2個頭指針，一個是樹的根節點，一個是最小關鍵碼的葉節點。所以 B+樹有兩種搜索方法：一種是按葉節點自己拉起的鏈表順序搜索。一種是從根節點開始搜索，和B樹類似，不過如果非葉節點的關鍵碼等於給定值，搜索並不停止，而是繼續沿右指針，一直查到葉節點上的關鍵碼。所以無論搜索是否成功，都將走完樹的所有層。B+ 樹中，數據對象的插入和刪除僅在葉節點上進行。這兩種處理索引的數據結構的不同之處：1、B樹中同一鍵值不會出現多次，並且它有可能出現在葉結點，也有可能出現在非葉結點中。而B+樹的鍵一定會出現在葉結點中，並且有可能在非葉結點中也有可能重復出現，以維持B+樹的平衡。2、因為B樹鍵位置不定，且在整個樹結構中只出現一次，雖然可以節省存儲空間，但使得在插入、刪除操作復雜度明顯增加。B+樹相比來說是一種較好的折中。3、B樹的查詢效率與鍵在樹中的位置有關，最大時間復雜度與B+樹相同(在葉結點的時候)，最小時間復雜度為1(在根結點的時候)。而B+樹的時候復雜度對某建成的樹是固定的。六、B/+Tree索引的性能分析到這里終於可以分析B-/+Tree索引的性能了。上文說過一般使用磁碟I/O次數評價索引結構的優劣。先從B-Tree分析，根據B-Tree的定義，可知檢索一次最多需要訪問h個節點。資料庫系統的設計者巧妙利用了磁碟預讀原理，將一個節點的大小設為等於一個頁，這樣每個節點只需要一次I/O就可以完全載入。為了達到這個目的，在實際實現B-Tree還需要使用如下技巧：每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也存儲在一個頁里，加之計算機存儲分配都是按頁對齊的，就實現了一個node只需一次I/O。B-Tree中一次檢索最多需要h-1次I/O(根節點常駐內存)，漸進復雜度為O(h)=O(logdN)。一般實際應用中，出度d是非常大的數字，通常超過100，因此h非常小(通常不超過3)。而紅黑樹這種結構，h明顯要深的多。由於邏輯上很近的節點(父子)物理上可能很遠，無法利用局部性，所以紅黑樹的I/O漸進復雜度也為O(h)，效率明顯比B-Tree差很多。綜上所述，用B-Tree作為索引結構效率是非常高的。
㈥ mysql主從資料庫在從庫上寫入主庫會斷開嗎
主庫的主機關機時，數據只能讀不能寫，並且從庫的主機關機後，數據仍然可讀可寫，這個不好弄，如果你對lua腳本有所了解，修改mysql-proxy 的讀寫分離腳本或許可以實現。 不過主庫關機時不能訪問，並且從庫關機仍可讀可寫，這個不需要配讀寫分離...
㈦ pgsql的主鍵存儲方式
PostgreSQL的穩定性極強，Innodb等索引在崩潰，斷電之類的災難場景下 抗擊打能力有了長足進步，然而很多 MqSQL用戶 都遇到過 Server級的資料庫丟失的場景 -- MySQL系統庫是 MyISAM，相比之下，PG資料庫這方面要更好一些。
任何系統都有它的性能極限，在高並發讀寫，負載逼近極限下，PG的性能指標仍可以位置雙曲線甚至對數曲線，到 頂峰之後不在下降，而MySQL明顯出現一個波峰後下滑（5.5版本 之後，在企業級版本中有個插件可以改善很多，不過需要付費）。
PG多年來在 GIS（地理信息）領域處於優勢地位，因為它有豐富的幾何類型，PG有大量字典，數組，bitmap等數據類型，相比之下 MySQL就差很多， Instagram就是因為 PG的空間資料庫 擴展 POSTGIS遠遠強於 MySQL的 my spatial 而採用 PgSQL的。
PG的「無鎖定」特性非常突出，甚至包括 vacuum這樣的整理數據空間的操作，這個和PGSQL的MVCC實現有關系。
PG可以使用函數 和 條件索引，這使得 PG資料庫的調優非常靈活， MySQL就沒有這個功能，條件索引在 web應用中 很重要。
PG有極其強悍的 SQL編程能力（9.x 圖靈完備，支持遞歸！），有非常豐富的統計函數和統計語法支持，比如分析函數（Oracle的叫法，PG里叫Window函數），還可以用多種語言來寫存儲過程，對於 R的支持也很好。這一點MySQL就差很多，很多分析功能都不支持，騰訊內部的存儲主要是 MySQL，但是數據分析主要是 Hadoop+ PgSQL。
PG的有多種集群架構可以選擇，plproxy可以之hi語句級的鏡像或分片，slony可以進行欄位級的同步配置，standby 可以構建 WAL文件級或流式的讀寫分離集群，同步頻率和集群策略調整方便。
一般關系型資料庫字元串有長度限制 8k 左右，無限長 TEXT類型的功能受限，只能作為外部大數據訪問。而 PG 的 TEXT 類型 可以直接訪問且無長度限制， SQL語法內置 正則表達式，可以索引，還可以全文檢索，或使用 xml xpath。用 PG的話，文檔資料庫都可以省了。
PgSQL對於 numa 架構的支持比 MySQL強一些，比 MySQL對於讀的性能更好一些， PgSQL提交可以完全非同步提交，而 MySQL的內存表不夠實用（因為表鎖的原因）。
pgsql除了存儲正常的數據類型外，還支持存儲
array，不管是一維數組還是多維數組均支持。
json和jsonb，相比使用 text存儲要高效很多。
json和 jsonb在更高的層面上看起來幾乎是一樣的，但是存儲實現上是不同的。
json存儲完的文本，json列會每次都解析存儲的值，它不支持索引，但 可以為創建表達式索引。
jsonb存儲的二進制格式，避免了重新解析數據結構。它支持索引，這意味著 可以不使用指定索引就能查詢任何路徑。
當我們比較寫入數據速度時，由於數據存儲 的方式的原因，jsonb會比 json 稍微的慢一點。json列會每次都 解析存儲的值，這意味著鍵的順序要和輸入的 時候一樣。但是 jsonb不同，以二進制格式存儲且不保證鍵的順序。因此如果有軟體需要依賴鍵的順序，jsonb可能不是最佳選擇。使用 jsonb的優勢還在於可以輕易的整合關系型數據和非關系型 數據 ，PostgreSQL對於 mongodb這類資料庫是一個不小的威脅，畢竟如果一個表中只有一列數據的類型是半結構化的，沒有必要為了遷就它而整個表的設計都採用 schemaless的結構。
1. CPU限制
PGSQL
沒有CPU核心數限制，有多少CPU核就用多少
MySQL
能用128核CPU，超過128核用不上
2. 配置文件參數
PGSQL
一共有255個參數，用到的大概是80個，參數比較穩定，用上個大版本配置文件也可以啟動當前大版本資料庫
MySQL
一共有707個參數，用到的大概是180個，參數不斷增加，就算小版本也會增加參數，大版本之間會有部分參數不兼容情況
3. 第三方工具依賴情況
PGSQL
只有高可用集群需要依靠第三方中間件，例如：patroni+etcd、repmgr
MySQL
大部分操作都要依靠percona公司的第三方工具（percona-toolkit，XtraBackup），工具命令太多，學習成本高，高可用集群也需要第三方中間件，官方MGR集群還沒成熟
4. 高可用主從復制底層原理
PGSQL
物理流復制，屬於物理復制，跟SQL Server鏡像/AlwaysOn一樣，嚴格一致，沒有任何可能導致不一致，性能和可靠性上，物理復制完勝邏輯復制，維護簡單
MySQL
主從復制，屬於邏輯復制，（sql_log_bin、binlog_format等參數設置不正確都會導致主從不一致）
大事務並行復制效率低，對於重要業務，需要依賴 percona-toolkit的pt-table-checksum和pt-table-sync工具定期比較和修復主從一致
主從復制出錯嚴重時候需要重搭主從
MySQL的邏輯復制並不阻止兩個不一致的資料庫建立復制關系
5. 從庫只讀狀態
PGSQL
系統自動設置從庫默認只讀，不需要人工介入，維護簡單
MySQL
從庫需要手動設置參數super_read_only=on，讓從庫設置為只讀，super_read_only參數有bug，鏈接：https://jiahao..com/s?id=1636644783594388753&wfr=spider&for=pc
6. 版本分支
PGSQL
只有社區版，沒有其他任何分支版本，PGSQL官方統一開發，統一維護，社區版有所有功能，不像SQL Server和MySQL有標准版、企業版、經典版、社區版、開發版、web版之分
國內外還有一些基於PGSQL做二次開發的資料庫廠商，例如：Enterprise DB、瀚高資料庫等等，當然這些只是二次開發並不算獨立分支
MySQL
由於歷史原因，分裂為三個分支版本，MariaDB分支、Percona分支 、Oracle官方分支，發展到目前為止各個分支基本互相不兼容
Oracle官方分支還有版本之分，分為標准版、企業版、經典版、社區版
7. SQL特性支持
PGSQL
SQL特性支持情況支持94種，SQL語法支持最完善，例如：支持公用表表達式（WITH查詢）
MySQL
SQL特性支持情況支持36種，SQL語法支持比較弱，例如：不支持公用表表達式（WITH查詢）
關於SQL特性支持情況的對比，可以參考：http://www.sql-workbench.net/dbms_comparison.html
8. 主從復制安全性
PGSQL
同步流復制、強同步（remote apply)、高安全，不會丟數據
PGSQL同步流復制：所有從庫宕機，主庫會罷工，主庫無法自動切換為非同步流復制（非同步模式），需要通過增加從庫數量來解決，一般生產環境至少有兩個從庫
手動解決：在PG主庫修改參數synchronous_standby_names =''，並執行命令： pgctl reload ，把主庫切換為非同步模式
主從數據完全一致是高可用切換的第一前提，所以PGSQL選擇主庫罷工也是可以理解
MySQL
增強半同步復制 ，mysql5.7版本增強半同步才能保證主從復制時候不丟數據
mysql5.7半同步復制相關參數：
參數rpl_semi_sync_master_wait_for_slave_count 等待至少多少個從庫接收到binlog，主庫才提交事務，一般設置為1，性能最高
參數rpl_semi_sync_master_timeout 等待多少毫秒，從庫無回應自動切換為非同步模式，一般設置為無限大，不讓主庫自動切換為非同步模式
所有從庫宕機，主庫會罷工，因為無法收到任何從庫的應答包
手動解決：在MySQL主庫修改參數rpl_semi_sync_master_wait_for_slave_count=0
9. 多欄位統計信息
PGSQL
支持多欄位統計信息
MySQL
不支持多欄位統計信息
10. 索引類型
PGSQL
多種索引類型（btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap，部分索引，表達式索引）
MySQL
btree 索引，全文索引（低效），表達式索引(需要建虛擬列)，hash 索引只在內存表
11. 物理表連接演算法
PGSQL
支持 nested-loop join 、hash join 、merge join
MySQL
只支持 nested-loop join
12. 子查詢和視圖性能
PGSQL
子查詢，視圖優化，性能比較高
MySQL
視圖謂詞條件下推限制多，子查詢上拉限制多
13. 執行計劃即時編譯
PGSQL
支持 JIT 執行計劃即時編譯，使用LLVM編譯器
MySQL
不支持執行計劃即時編譯
14. 並行查詢
PGSQL
並行查詢（多種並行查詢優化方法），並行查詢一般多見於商業資料庫，是重量級功能
MySQL
有限，只支持主鍵並行查詢
15. 物化視圖
PGSQL
支持物化視圖
MySQL
不支持物化視圖
16. 插件功能
PGSQL
支持插件功能，可以豐富PGSQL的功能，GIS地理插件，時序資料庫插件， 向量化執行插件等等
MySQL
不支持插件功能
17. check約束
PGSQL
支持check約束
MySQL
不支持check約束，可以寫check約束，但存儲引擎會忽略它的作用，因此check約束並不起作用（mariadb 支持）
18. gpu 加速SQL
PGSQL
可以使用gpu 加速SQL的執行速度
MySQL
不支持gpu 加速SQL 的執行速度
19. 數據類型
PGSQL
數據類型豐富，如 ltree，hstore，數組類型，ip類型，text類型，有了text類型不再需要varchar，text類型欄位最大存儲1GB
MySQL
數據類型不夠豐富
20. 跨庫查詢
PGSQL
不支持跨庫查詢，這個跟Oracle 12C以前一樣
MySQL
可以跨庫查詢
21. 備份還原
PGSQL
備份還原非常簡單，時點還原操作比SQL Server還要簡單，完整備份+wal歸檔備份（增量）
假如有一個三節點的PGSQL主從集群，可以隨便在其中一個節點做完整備份和wal歸檔備份
MySQL
備份還原相對不太簡單，完整備份+binlog備份（增量）
完整備份需要percona的XtraBackup工具做物理備份，MySQL本身不支持物理備份
時點還原操作步驟繁瑣復雜
22. 性能視圖
PGSQL
需要安裝pg_stat_statements插件，pg_stat_statements插件提供了豐富的性能視圖：如：等待事件，系統統計信息等
不好的地方是，安裝插件需要重啟資料庫，並且需要收集性能信息的資料庫需要執行一個命令：create extension pg_stat_statements命令
否則不會收集任何性能信息，比較麻煩
MySQL
自帶PS庫，默認很多功能沒有打開，而且打開PS庫的性能視圖功能對性能有影響（如：內存佔用導致OOM bug）
23. 安裝方式
PGSQL
有各個平台的包rpm包，deb包等等，相比MySQL缺少了二進制包，一般用源碼編譯安裝，安裝時間會長一些，執行命令多一些
MySQL
有各個平台的包rpm包，deb包等等，源碼編譯安裝、二進制包安裝，一般用二進制包安裝，方便快捷
24. DDL操作
PGSQL
加欄位、可變長欄位類型長度改大不會鎖表，所有的DDL操作都不需要藉助第三方工具，並且跟商業資料庫一樣，DDL操作可以回滾，保證事務一致性
MySQL
由於大部分DDL操作都會鎖表，例如加欄位、可變長欄位類型長度改大，所以需要藉助percona-toolkit裡面的pt-online-schema-change工具去完成操作
將影響減少到最低，特別是對大表進行DDL操作
DDL操作不能回滾
25. 大版本發布速度
PGSQL
PGSQL每年一個大版本發布，大版本發布的第二年就可以上生產環境，版本迭代速度很快
PGSQL 9.6正式版推出時間：2016年
PGSQL 10 正式版推出時間：2017年
PGSQL 11 正式版推出時間：2018年
PGSQL 12 正式版推出時間：2019年
MySQL
MySQL的大版本發布一般是2年~3年，一般大版本發布後的第二年才可以上生產環境，避免有坑，版本發布速度比較慢
MySQL5.5正式版推出時間：2010年
MySQL5.6正式版推出時間：2013年
MySQL5.7正式版推出時間：2015年
MySQL8.0正式版推出時間：2018年
26. returning語法
PGSQL
支持returning語法，returning clause 支持 DML 返回 Resultset，減少一次 Client <-> DB Server 交互
MySQL
不支持returning語法
27. 內部架構
PGSQL
多進程架構，並發連接數不能太多，跟Oracle一樣，既然跟Oracle一樣，那麼很多優化方法也是相通的，例如：開啟大頁內存
MySQL
多線程架構，雖然多線程架構，但是官方有限制連接數，原因是系統的並發度是有限的，線程數太多，反而系統的處理能力下降，隨著連接數上升，反而性能下降
一般同時只能處理200 ~300個資料庫連接
28. 聚集索引
PGSQL
不支持聚集索引，PGSQL本身的MVCC的實現機制所導致
MySQL
支持聚集索引
29. 空閑事務終結功能
PGSQL
通過設置 idle_in_transaction_session_timeout 參數來終止空閑事務，比如：應用代碼中忘記關閉已開啟的事務，PGSQL會自動查殺這種類型的會話事務
MySQL
不支持終止空閑事務功能
30. 應付超大數據量
PGSQL
不能應付超大數據量，由於PGSQL本身的MVCC設計問題，需要垃圾回收，只能期待後面的大版本做優化
MySQL
不能應付超大數據量，MySQL自身架構的問題
31. 分布式演進
PGSQL
HTAP資料庫：cockroachDB、騰訊Tbase
分片集群： Postgres-XC、Postgres-XL
MySQL
HTAP資料庫：TiDB
分片集群： 各種各樣的中間件，不一一列舉
32. 資料庫的文件名和命名規律
PGSQL
PGSQL在這方面做的比較不好，DBA不能在操作系統層面（停庫狀態下）看清楚資料庫的文件名和命名規律，文件的數量，文件的大小
一旦操作系統發生文件丟失或硬碟損壞，非常不利於恢復，因為連名字都不知道
PGSQL表數據物理文件的命名/存放規律是： 在一個表空間下面，如果沒有建表空間默認在默認表空間也就是base文件夾下，例如：/data/base/16454/3599
base：默認表空間pg_default所在的物理文件夾
16454：表所在資料庫的oid
3599：就是表對象的oid，當然，一個表的大小超出1GB之後會再生成多個物理文件，還有表的fsm文件和vm文件，所以一個大表實際會有多個物理文件
由於PGSQL的數據文件布局內容太多，大家可以查閱相關資料
當然這也不能全怪PGSQL，作為一個DBA，時刻做好資料庫備份和容災才是正道，做介質恢復一般是萬不得已的情況下才會做
MySQL
資料庫名就是文件夾名，資料庫文件夾下就是表數據文件，但是要注意表名和資料庫名不能有特殊字元或使用中文名，每個表都有對應的frm文件和ibd文件，存儲元數據和表/索引數據，清晰明了，做介質恢復或者表空間傳輸都很方便
33. 許可權設計
PGSQL
PGSQL在許可權設計這塊是比較坑爹，拋開實例許可權和表空間許可權，PGSQL的許可權層次有點像SQL Server，db=》schema=》object
要說許可權，這里要說一下Oracle，用Oracle來類比
在ORACLE 12C之前，實例與資料庫是一對一，也就是說一個實例只能有一個資料庫，不像MySQL和SQL Server一個實例可以有多個資料庫，並且可以隨意跨庫查詢
而PGSQL不能跨庫查詢的原因也是這樣，PGSQL允許建多個資料庫，跟ORACLE類比就是有多個實例（之前說的實例與資料庫是一對一）
一個資料庫相當於一個實例，因為PGSQL允許有多個實例，所以PGSQL單實例不叫一個實例，叫集簇（cluster），集簇這個概念可以查閱PGSQL的相關資料
PGSQL裡面一個實例/資料庫下面的schema相當於資料庫，所以這個schema的概念對應MySQL的database
注意點：正因為是一個資料庫相當於一個實例，PGSQL允許有多個實例/資料庫，所以資料庫之間是互相邏輯隔離的，導致的問題是，不能一次對一個PGSQL集簇下面的所有資料庫做操作
必須要逐個逐個資料庫去操作，例如上面說到的安裝pg_stat_statements插件，如果您需要在PGSQL集簇下面的所有資料庫都做性能收集的話，需要逐個資料庫去執行載入命令
又例如跨庫查詢需要dblink插件或fdw插件，兩個資料庫之間做查詢相當於兩個實例之間做查詢，已經跨越了實例了，所以需要dblink插件或fdw插件，所以道理非常簡單
許可權操作也是一樣逐個資料庫去操作，還有一個就是PGSQL雖然像SQL Server的許可權層次結構db=》schema=》object，但是實際會比SQL Server要復雜一些，還有就是新建的表還要另外授權
在PGSQL裡面，角色和用戶是一樣的，對新手用戶來說有時候會傻傻分不清，也不知道怎麼去用角色，所以PGSQL在許可權設計這一塊確實比較坑爹
MySQL
使用mysql庫下面的5個許可權表去做許可權映射，簡單清晰，唯一問題是缺少許可權角色
user表
db表
host表
tables_priv表
columns_priv表
1. 架構對比
Mysql：多線程
PostgreSql：多進程
多線程架構和多進程架構之間沒有絕對的好壞，例如oracle在unix上是多進程架構，在windows上是多線程架構。
2. 對存儲過程及事務的支持能力
MySql對於無事務的MyISAM表，採用表鎖定，一個長時間運行的查詢很可能會長時間的阻礙，而PostgreSQL不會尊在這種問題。
PostgreSQL支持存儲過程，要比MySql好，具備本地緩存執行計劃的能力。
3. 穩定性及性能
高並發讀寫，負載逼近極限下，PG的性能指標仍可以維持雙曲線甚至對數曲線，到頂峰之後不再下降，而 MySql 明顯出現一個波峰後下滑（5.5版本後Mysql企業版有優化，需要付費）
MySql的InnoDB引擎，可以充分優化利用系統的所有內存，超大內存下PG對內存使用的不那麼充分（需要根據內存情況合理分配）。
4. 高可用
InnoDB的基於回滾實現的 MVCC 機制，對於 PG 新老數據一起放的基於 XID 的 MVCC機制，是占優的。新老數據一起存放，需要定時觸發 VACUUM，會帶來多餘的 IO 和資料庫對象加鎖開銷，引起資料庫整理的並發能力下降。而且 VACUUM 清理不及時，還可能會引發數據膨脹
5. 數據同步方式：
Mysql到現在也是非同步復制，pgsql可以做到同步、非同步、半同步復制。
Mysql同步是基於binlog復制，屬於邏輯復制，類似於oracle golden gate，是基於stream的復制，做到同步很困難，這種方式更加適合非同步復制；
Pgsql的同是基於wal，屬於物理復制，可以做到同步復制。同時，pgsql還提供stream復制。
Mysql的復制可以用多級從庫，但是在9.2之前，PgSql不能用從庫帶從庫。
Pgsql的主從復制屬於物理復制，相對於Mysql基於binlog的邏輯復制，數據的一致性更加可靠，復制性能更高，對主機性能的影響也更小。
6. 許可權控制對比
MySql允許自定義一套不同的數據級、表級和列的許可權，運行指定基於主機的許可權
Mysql的merge表提供了 一個獨特管理多個表的方法。myisampack可以對只讀表進行壓縮，以後仍然可以直接訪問該表中的行。
7. SQL語句支持能力
PG有極其強悍的 SQL 編程能力（9.x 圖靈完備，支持遞歸！），有非常豐富的統計函數和統計語法支持，例如分析函數（Oracle的叫法，PG里叫window函數）
支持用多種語言來寫存儲過程，對於R的支持也很好。這一點上Mysql就差的很遠，很多分析功能都不支持。
PgSql對表名大小寫的處理，只有在Sql語句中，表明加雙引號，才區分大小寫。
在Sql的標准實現上要比Mysql完善，而且功能實現比較嚴謹。
對表連接支持比較完整，優化器的功能比較完整，支持的索引類型很多，復雜查詢能力較強。
Mysql採用索引組織表，這種存儲方式非常適合基於主鍵匹配的查詢、刪改操作，但是對表結果設計存在約束；
Mysql的Join操作的性能非常的差，只支持Nest Join，所以一旦數據量大，性能就非常的差。PostgresSQL除了支持 Nest Join 和 Sort Merge Join，PostgreSQL還支持正則表達式查詢，MySql不支持。
8. 數據類型支持能力
PostgreSQL可以更方便的使用UDF（用戶定義函數）進行擴展。
有豐富的幾何類型，實際上不止集合類型，PG有大量的字典、數組、bitmap等數據類型，因此PG多年來在 GIS 領域處於優勢地位。相比之下Mysql就差很多，instagram就是因為PG的空間數據擴展 PostGIS遠遠強於 MySql的 my spatial 而採用 PgSql的。Mysql中的空間數據類型有4種，分別是 CEOMETRY、POINT、LINESTRING、POLYGON，其空間索引只能在存儲引擎為 MyiSam的表中創建，用SPATIAL關鍵字進行擴展，使得能夠用於創建正規索引類型的語法創建空間索引。創建空間索引的列，必須將其聲明為NOT NULL。不同的存儲親情有差別。MyISAM和InnoDB 都支持 spatial extensions，但差別在於：如果使用MyISAM，可以建立 spatial index，而 InnoDB是不支持的。
pgsql對json支持比較好，還有很逆天的fdw功能，就是把別的資料庫中的表當自己的用。
pgsql的欄位類型支持的多，有很多mysql沒有的類型，但是實際中有時候用到。
一半關系型資料庫的字元串長度8k左右，無限長的 TEXT 類型的功能受限，只能作為外部帶數據訪問。而 PG 的 TEXT 類型可以直接訪問，SQL 語法內置正則表達式，可以索引，還可以全文檢索，或使用 xml xpath。用 PG 的話，文檔資料庫都可以省了。
postgresql 有函數，用於報表、統計很方便
PG支持 R-Trees這樣可擴展的索引類型，可以方便的處理一些特殊數據。
PG可以使用函數和條件所以，使得資料庫的調優非常靈活，mysql就沒有這個功能，條件索引在web應用中很重要。
9. 如可過程容錯能力
大批量數據入庫，PostgreSql要求所有的數據必須完全滿足要求，有一條錯誤，整個數據入庫過程失敗。MySql無此問題。
10. 表組織方式
pgsql用繼承的方式實現分區表，讓分區表的使用不方便且性能差，這點比不上mysql。
pg主表採用堆表存放，MySQL採用索引組織表，能夠支持比MySql更大的數據量。
MySql分區表的實現要優於PG的基於繼承表的分區實現，主要體現在分區個數達到成千上萬後的處理性能差異很大。
11. 開發結構
對於web應用來所，mysql 5.6 的內置 MC API 功能很好用，PgSQL差一些。
PG的「無鎖定」特性非常突出，甚至包括 vacuum 這樣的整理數據空間的操作，這個和 PGSQL的 MVCC 實現有關系。
好文要頂 關注我 收藏該文
茄子777
粉絲 - 0 關注 - 0
+加關注
00
« 上一篇： 多線程中的wait與join
» 下一篇： 負載均衡相關
posted @ 2022-11-02 16:20 茄子777 閱讀(55) 評論(0) 編輯 收藏 舉報
刷新評論刷新頁面返回頂部
登錄後才能查看或發表評論，立即 登錄 或者 逛逛 博客園首頁
【推薦】阿里雲新人特惠，爆款雲伺服器2核4G低至0.46元/天
【推薦】雙十一同價！騰訊雲雲伺服器搶先購，低至4.2元/月
編輯推薦：
· 一個有趣的 nginx HTTP 400 響應問題分析
· 誰說.NET沒有GC調優？只改一行代碼就讓程序不再佔用內存
· 為什麼標准庫的模板變數都是 inline 的
· .net 如何優雅的使用 EFCore
· 在 C# 中使用 Halcon 開發視覺檢測程序
閱讀排行：
· Entity Framework Core 7中高效地進行批量數據插入
· 除了 filter 還有什麼置灰網站的方式？
· 快速繪制流程圖「GitHub 熱點速覽 v.22.47」
· 使用.NET7和C#11打造最快的序列化程序-以MemoryPack為例
· 私藏！資深數據專家SQL效率優化技巧 ⛵