❶ sql語句性能如何優化
如何加快查詢速度?
1、升級硬體
2、根據查詢條件,建立索引,優化索引、優化訪問方式，限制結果集的數據量。
3、擴大伺服器的內存
4、增加伺服器CPU個數
5、對於大的資料庫不要設置資料庫自動增長，它會降低伺服器的性能
6、在查詢Select語句中用Where字句限制返回的行數,避免表掃描,如果返回不必要的數據，浪費了伺服器的I/O資源，加重了網路的負擔降低性能。如果表很大，在表掃描的期間將表鎖住，禁止其他的聯接訪問表,後果嚴重。
7、查詢時不要返回不需要的行、列
8、用select top 100 / 10 Percent 來限制用戶返回的行數或者SET ROWCOUNT來限制操作的行
9、在IN後面值的列表中，將出現最頻繁的值放在最前面，出現得最少的放在最後面，減少判斷的次數
10、一般在GROUP BY 個HAVING字句之前就能剔除多餘的行，所以盡量不要用它們來做剔除行的工作。他們的執行順序應該如下最優：
select的Where字句選擇所有合適的行，Group By用來分組個統計行，Having字句用來剔除多餘的分組。這樣Group By 個Having的開銷小，查詢快.對於大的數據行進行分組和Having十分消耗資源。如果Group BY的目的不包括計算，只是分組，那麼用Distinct更快
11、一次更新多條記錄比分多次更新每次一條快,就是說批處理好
❷ 磁碟讀寫和資料庫讀寫哪個效率更高
假定在程序效率和關鍵過程相當且不計入緩存等措施的條件下，讀寫任何類型的數據都沒有直接操作文件來的快，不論MSYQL過程如何，最後都要到磁碟上去讀這個「文件」（記錄存儲區等效），所以當然這一切的前提是只讀 內容，無關任何排序或查找操作。
動態網站一般都是用資料庫來存儲信息，如果信息的及時性要求不高 可以加入緩存來減少頻繁讀寫資料庫。
兩種方式一般都支持，但是繞過操作系統直接操作磁碟的性能較高，而且安全性也較高，資料庫系中的磁碟性能一直都是瓶頸，大型資料庫一般基於unix
系統，當然win下也有，不常用應為win的不可靠性，unix下，用的是裸設備raw設備，就是沒有加工過的設備（unix下的磁碟分區屬於特殊設備，
以文件形式統一管理），由dbms直接管理，不通過操作系統，效率很高，可靠性也高，因為磁碟，cache和內存都是自己管理的，大型資料庫系統
db2，oracal，informix（不太流行了），mssql算不上大型資料庫系統。
1、直接讀文件相比資料庫查詢效率更勝一籌，而且文中還沒算上連接和斷開的時間。
2、一次讀取的內容越大，直接讀文件的優勢會越明
顯（讀文件時間都是小幅增長，這跟文件存儲的連續性和簇大小等有關系），這個結果恰恰跟書生預料的相反，說明MYSQL對更大文件讀取可能又附加了某些操
作（兩次時間增長了近30%），如果只是單純的賦值轉換應該是差異偏小才對。
3、寫文件和INSERT幾乎不用測試就可以推測出，資料庫效率只會更差。
4、很小的配置文件如果不需要使用到資料庫特性，更加適合放到獨立文件里存取，無需單獨創建數據表或記錄，很大的文件比如圖片、音樂等採用文件存儲更為方便，只把路徑或縮略圖等索引信息放到資料庫里更合理一些。
5、PHP上如果只是讀文件，file_get_contents比fopen、fclose更有效率，不包括判斷存在這個函數時間會少3秒左右。
6、fetch_row和fetch_object應該是從fetch_array轉換而來的，書生沒看過PHP的源碼，單從執行上就可以說明fetch_array效率更高，這跟網上的說法似乎相反。
磁碟讀寫與資料庫的關系：
一 磁碟物理結構
(1) 碟片：硬碟的盤體由多個碟片疊在一起構成。
在硬碟出廠時，由硬碟生產商完成了低級格式化(物理格式化)，作用是將空白的碟片(Platter)劃分為一個個同圓心、不同半徑的磁軌
(Track)，還將磁軌劃分為若干個扇區(Sector)，每個扇區可存儲128×2的N次方（N=0.1.2.3）位元組信息，默認每個扇區的大小為
512位元組。通常使用者無需再進行低級格式化操作。
(2) 磁頭：每張碟片的正反兩面各有一個磁頭。
(3) 主軸：所有磁片都由主軸電機帶動旋轉。
(4) 控制集成電路板：復雜！上面還有ROM（內有軟體系統）、Cache等。
二 磁碟如何完成單次IO操作
(1) 尋道
當控制器對磁碟發出一個IO操作命令的時候，磁碟的驅動臂(Actuator
Arm)帶動磁頭(Head)離開著陸區(Landing
Zone，位於內圈沒有數據的區域)，移動到要操作的初始數據塊所在的磁軌(Track)的正上方，這個過程被稱為尋道(Seeking)，對應消耗的時
間被稱為尋道時間(Seek Time)；
(2) 旋轉延遲
找到對應磁軌還不能馬上讀取數據，這時候磁頭要等到磁碟碟片(Platter)旋轉到初始數據塊所在的扇區(Sector)落在讀寫磁頭正下方之後才能開始讀取數據，在這個等待碟片旋轉到可操作扇區的過程中消耗的時間稱為旋轉延時(Rotational Delay)；
(3) 數據傳送
接下來就隨著碟片的旋轉，磁頭不斷的讀/寫相應的數據塊，直到完成這次IO所需要操作的全部數據，這個過程稱為數據傳送(Data Transfer)，對應的時間稱為傳送時間(Transfer Time)。完成這三個步驟之後單次IO操作也就完成了。
根據磁碟單次IO操作的過程，可以發現：
單次IO時間 = 尋道時間 + 旋轉延遲 + 傳送時間
進而推算IOPS(IO per second)的公式為：
IOPS = 1000ms/單次IO時間
三 磁碟IOPS計算
不同磁碟，它的尋道時間，旋轉延遲，數據傳送所需的時間各是多少？
1. 尋道時間
考慮到被讀寫的數據可能在磁碟的任意一個磁軌，既有可能在磁碟的最內圈（尋道時間最短），也可能在磁碟的最外圈（尋道時間最長），所以在計算中我們只考慮平均尋道時間。
在購買磁碟時，該參數都有標明，目前的SATA/SAS磁碟，按轉速不同，尋道時間不同，不過通常都在10ms以下：
3. 傳送時間2. 旋轉延時
和尋道一樣，當磁頭定位到磁軌之後有可能正好在要讀寫扇區之上，這時候是不需要額外的延時就可以立刻讀寫到數據，但是最壞的情況確實要磁碟旋轉整整
一圈之後磁頭才能讀取到數據，所以這里也考慮的是平均旋轉延時，對於15000rpm的磁碟就是(60s/15000)*(1/2) = 2ms。
(1) 磁碟傳輸速率
磁碟傳輸速率分兩種：內部傳輸速率(Internal Transfer Rate)，外部傳輸速率(External Transfer Rate)。
內部傳輸速率(Internal Transfer Rate)，是指磁頭與硬碟緩存之間的數據傳輸速率，簡單的說就是硬碟磁頭將數據從碟片上讀取出來，然後存儲在緩存內的速度。
理想的內部傳輸速率不存在尋道，旋轉延時，就一直在同一個磁軌上讀數據並傳到緩存，顯然這是不可能的，因為單個磁軌的存儲空間是有限的；
實際的內部傳輸速率包含了尋道和旋轉延時，目前家用磁碟，穩定的內部傳輸速率一般在30MB/s到45MB/s之間(伺服器磁碟，應該會更高)。
外部傳輸速率(External Transfer Rate)，是指硬碟緩存和系統匯流排之間的數據傳輸速率，也就是計算機通過硬碟介面從緩存中將數據讀出交給相應的硬碟控制器的速率。
硬碟廠商在硬碟參數中，通常也會給出一個最大傳輸速率，比如現在SATA3.0的6Gbit/s，換算一下就是6*1024/8，768MB/s，通常指的是硬碟介面對外的最大傳輸速率，當然實際使用中是達不到這個值的。
這里計算IOPS，保守選擇實際內部傳輸速率，以40M/s為例。
(2) 單次IO操作的大小
有了傳送速率，還要知道單次IO操作的大小(IO Chunk Size)，才可以算出單次IO的傳送時間。那麼磁碟單次IO的大小是多少？答案是：不確定。
操作系統為了提高 IO的性能而引入了文件系統緩存(File System Cache)，系統會根據請求數據的情況將多個來自IO的請求先放在緩存裡面，然後再一次性的提交給磁碟，也就是說對於資料庫發出的多個8K數據塊的讀操作有可能放在一個磁碟讀IO里就處理了。
還有，有些存儲系統也是提供了緩存（Cache），接收到操作系統的IO請求之後也是會將多個操作系統的 IO請求合並成一個來處理。
不管是操作系統層面的緩存還是磁碟控制器層面的緩存，目的都只有一個，提高數據讀寫的效率。因此每次單獨的IO操作大小都是不一樣的，它主要取決於系統對於數據讀寫效率的判斷。這里以SQL Server資料庫的數據頁大小為例：8K。
(3) 傳送時間
傳送時間 = IO Chunk Size/Internal Transfer Rate = 8k/40M/s = 0.2ms
可以發現：
(3.1) 如果IO Chunk Size大的話，傳送時間會變大，從而導致IOPS變小；
(3.2) 機械磁碟的主要讀寫成本，都花在了定址時間上，即：尋道時間 + 旋轉延遲，也就是磁碟臂的擺動，和磁碟的旋轉延遲。
(3.3) 如果粗略的計算IOPS，可以忽略傳送時間，1000ms/(尋道時間 + 旋轉延遲)即可。
4. IOPS計算示例
以15000rpm為例：
(1) 單次IO時間
單次IO時間 = 尋道時間 + 旋轉延遲 + 傳送時間 = 3ms + 2ms + 0.2 ms = 5.2 ms
(2) IOPS
IOPS = 1000ms/單次IO時間 = 1000ms/5.2ms = 192 (次)
這里計算的是單塊磁碟的隨機訪問IOPS。
考慮一種極端的情況，如果磁碟全部為順序訪問，那麼就可以忽略：尋道時間 + 旋轉延遲 的時長，IOPS的計算公式就變為：IOPS = 1000ms/傳送時間
IOPS = 1000ms/傳送時間= 1000ms/0.2ms = 5000 (次)
顯然這種極端的情況太過理想，畢竟每個磁軌的空間是有限的，尋道時間 + 旋轉延遲 時長確實可以減少，不過是無法完全避免的。
四 資料庫中的磁碟讀寫
1. 隨機訪問和連續訪問
(1) 隨機訪問(Random Access)
指的是本次IO所給出的扇區地址和上次IO給出扇區地址相差比較大，這樣的話磁頭在兩次IO操作之間需要作比較大的移動動作才能重新開始讀/寫數據。
(2) 連續訪問(Sequential Access)
相反的，如果當次IO給出的扇區地址與上次IO結束的扇區地址一致或者是接近的話，那磁頭就能很快的開始這次IO操作，這樣的多個IO操作稱為連續訪問。
(3) 以SQL Server資料庫為例
數據文件，SQL Server統一區上的對象，是以extent(8*8k)為單位進行空間分配的，數據存放是很隨機的，哪個數據頁有空間，就寫在哪裡，除非通過文件組給每個表預分配足夠大的、單獨使用的文件，否則不能保證數據的連續性，通常為隨機訪問。
另外哪怕聚集索引表，也只是邏輯上的連續，並不是物理上。
日誌文件，由於有VLF的存在，日誌的讀寫理論上為連續訪問，但如果日誌文件設置為自動增長，且增量不大，VLF就會很多很小，那麼就也並不是嚴格的連續訪問了。
2. 順序IO和並發IO
(1) 順序IO模式(Queue Mode)
磁碟控制器可能會一次對磁碟組發出一連串的IO命令，如果磁碟組一次只能執行一個IO命令，稱為順序IO；
(2) 並發IO模式(Burst Mode)
當磁碟組能同時執行多個IO命令時，稱為並發IO。並發IO只能發生在由多個磁碟組成的磁碟組上，單塊磁碟只能一次處理一個IO命令。
(3) 以SQL Server資料庫為例
有的時候，盡管磁碟的IOPS(Disk Transfers/sec)還沒有太大，但是發現資料庫出現IO等待，為什麼？通常是因為有了磁碟請求隊列，有過多的IO請求堆積。
磁碟的請求隊列和繁忙程度，通過以下性能計數器查看：
LogicalDisk/Avg.Disk Queue Length
LogicalDisk/Current Disk Queue Length
LogicalDisk/%Disk Time
這種情況下，可以做的是：
(1) 簡化業務邏輯，減少IO請求數；
(2) 同一個實例下，多個資料庫遷移的不同實例下；
(3) 同一個資料庫的日誌，數據文件分離到不同的存儲單元；
(4) 藉助HA策略，做讀寫操作的分離。
3. IOPS和吞吐量(throughput)
(1) IOPS
IOPS即每秒進行讀寫（I/O）操作的次數。在計算傳送時間時，有提到，如果IO Chunk Size大的話，那麼IOPS會變小，假設以100M為單位讀寫數據，那麼IOPS就會很小。
(2) 吞吐量(throughput)
吞吐量指每秒可以讀寫的位元組數。同樣假設以100M為單位讀寫數據，盡管IOPS很小，但是每秒讀寫了N*100M的數據，吞吐量並不小。
(3) 以SQL Server資料庫為例
對於OLTP的系統，經常讀寫小塊數據，多為隨機訪問，用IOPS來衡量讀寫性能；
對於數據倉庫，日誌文件，經常讀寫大塊數據，多為順序訪問，用吞吐量來衡量讀寫性能。
磁碟當前的IOPS，通過以下性能計數器查看：
LogicalDisk/Disk Transfers/sec
LogicalDisk/Disk Reads/sec
LogicalDisk/Disk Writes/sec
磁碟當前的吞吐量，通過以下性能計數器查看：
LogicalDisk/Disk Bytes/sec
LogicalDisk/Disk Read Bytes/sec
LogicalDisk/Disk Write Bytes/sec
❸ Sql server 安全,性能優化的15條方案
1．1 基本概念 與資料庫技術密切相關的基本概念包括：數據、資料庫、資料庫管理系統和資料庫系統四大概念。1. 數據（Data） 數據是對客觀事物的一種描述，是由能被計算機識別與處理的數值、字元等符號構成的集合，即數據是指描述事物的符號記錄。 廣義地說，數據是一種物理符號的序列，用於記錄事物的情況，是對客觀事物及其屬性進行的一種抽象化及符號化的描述。數據的概念應包括數據的內容和形式兩個方面。數據的內容是指所描述的客觀事物的具體特性，也就是通常所說的數據的「值」；數據的形式則是指數據內容所存儲的具體形式，即數據的「類型」。故此，數據可以用數據類型和值來表示。2. 資料庫（Data Base，DB） 資料庫是指長期存儲在計算機內部、有組織的、可共享的數據集合，即在計算機系統中按一定的數據模型組織、存儲和使用的相關聯的數據集合成為資料庫。 資料庫中的數據按照一定的數據模型組織、描述和存儲，具有較小的冗餘度、較高的數據獨立性、易擴展性、集中性和共享性，以文件的形式存儲在存儲介質上的。資料庫中的數據由資料庫管理系統進行統一管理和控制，用戶對資料庫進行的各種數據操作都是通過資料庫管理系統實現。3. 資料庫管理系統（Data Base Management System，DBMS） 資料庫管理系統是資料庫系統的核心，是為資料庫的建立、使用和維護而配置的軟體，是位於操作系統與用戶之間的一層數據管理軟體。主要功能是對資料庫進行定義、操作、控制和管理。1) 數據定義 數據的定義包括：定義構成資料庫結構的外模式、模式和內模式，定義各個外模式和模式之間的映射，定義模式與內模式之間的映射，定義有關的約束條件。2) 數據處理對數據的處理操作主要包括對資料庫數據的檢索、插入、修改和刪除等基本操作。3) 安全管理 對資料庫的安全管理主要體現在：對資料庫進行並發控制、安全性檢查、完整性約束條件的檢查和執行、資料庫的內部維護（如索引、數據字典的自動維護）等。並且能夠管理和監督用戶的許可權，防止擁護有任何破壞或者惡意的企圖。4) 數據的組織、存儲和管理 負責分類地組織、存儲和管理資料庫數據，確定以何種文件結構和存取方式物理地組織數據，如何實現數據之間的聯系，以便提高存儲空間利用以及提高隨機查找、順序查找、增加、刪除和查改等操作的時間效率。5) 建立和維護資料庫 建立資料庫包括資料庫數據的初始化與數據轉換等。維護資料庫包括資料庫的轉儲與恢復、資料庫的重組織與重構造、性能的監視與分析等。6) 數據通信介面提供與其他軟體系統進行通信的功能。4. 資料庫系統（Data Base System，DBS） 資料庫系統指在計算機系統中引入資料庫後的系統構成，一般有資料庫、資料庫管理系統、應用系統、資料庫管理員和用戶構成。1．2 資料庫系統的特點 資料庫系統的點主要有：數據的結構化、高共享性、低冗餘度、易擴充、較高的獨立性（物理數據獨立、邏輯數據獨立）以及數據由DBMS統一管理和控制（數據的安全性Security保護、數據的完整性Integrity保護、並發Concurrency控制、資料庫恢復Recovery）等。第二章 資料庫性能優化 資料庫作為一種獨立的、有組織、的可共享的數據集合，數據的查詢訪問是數據操作中頻度最高的操作。當數據量和訪問頻率達到一定程度的時候，系統的響應速度就至關重要了，這時候就需要對資料庫數據存儲的結構和方式進行優化，使其滿足系統需要的訪問響應速度。2．1 性能影響因素 常見的影響數據訪問速度的因素，有以下幾種：1. 沒有索引或者沒有用到索引 資料庫索引就像書籍中目錄一樣，使用戶在訪問資料庫數據時，不必遍歷所有數據就可以找到需要的數據。創建索引後，可以保證每行數據的唯一性，極大地提高數據檢索效率，這是一中犧牲空間換取性能的方法。沒有索引或者沒有用到索引是數據訪問速度慢最常見的因素，也是程序設計的一個缺陷所在。2. I/O吞吐量小，形成了瓶頸效應 I/O吞吐量是影響數據訪問速度的客觀因素（硬體因素）。在一定的硬體環境下，利用優化的部署方案可適當提高I/O吞吐量。3. 沒有創建計算列導致查詢不優化 計算列是一個比較特殊的列，不填寫任何設計類型，用戶不可以改變該列的值。計算列的值是通過一定的函數公式等以另一個或多個列的值為輸入值計算出的結果。如果沒相應的計算列，在一些數據查詢的時候需要對已有數據進行計算，從而浪費一部分性能。4. 內存不足 對資料庫數據的查詢訪問毫無疑問會佔用大量的內存空間，當內存不足的情況下，數據的訪問速度會受到明顯的影響甚至訪問出現超時情況，是影響數據訪問速度的客觀因素。5. 網路速度慢 網路速度慢是影響數據訪問速度的客觀因素。可通過提高網路訪問的位寬來解決。6. 查詢出的數據量過大 當查詢出的數據量過大時，內存的佔用、系統時間的佔用等都影響數據訪問的速度。可以採用多次查詢、定位查詢、和查詢數據量控制來解決。7. 鎖或者死鎖 鎖或者死鎖在資料庫數據訪問時會造成訪問者等待時間過程或者永久無法獲取到資源。這是查詢慢最常見的因素之一，是程序設計的缺陷，要盡量避免。8. 返回不必要的行和列 在一般的數據查詢中，都盡可能多的獲取數據信息，這樣造成了不必要的數據遍歷，大大的增加了數據訪問的響應的時間。所以在一般的查詢中，盡量查詢少的行和列，將數據遍歷時間降到最低以滿足數據輸出需求。9. 查詢語句不夠優化 在數據查詢訪問過程中，使用最頻繁的是使用自定義的查詢語句進行數據輸出的。所以編寫優化的查詢語句能夠很大程度上提高數據查詢訪問的速度。2．2 性能優化 資料庫性能優化主要是提高數據訪問的速度，即提高資料庫響應速度的性能指標。性能優化主要分為主觀因素和客觀因素兩部分的優化。這里主要針對影響性能的客觀因素進行優化。2．2．1 主觀因素優化 主觀因素主要是指伺服器的硬體環境。主要優化有以下幾個方面：1、 把數據、日誌、索引放到不同的I/O設備上，增加讀取速度，數據量越大，提高I/O吞吐量越重要；2、 縱向、橫向分割表，減少表的尺寸（sp_spaceuse）；3、 升級硬體；4、 提高網路訪問速度；5、 擴大伺服器的內存；配置虛擬內存：虛擬內存大小應基於計算機上並發運行的服務進行配置，一般設置為物理內存的1.5倍；如果安裝了全文檢索功能，並打算運行Microsoft搜索服務以便執行全文索引和查詢，可考慮將虛擬內存大小設置為至少計算機中物理內存的3倍；6、 增加伺服器CPU個數；其中並行處理比串列處理更需要資源。SQL SERVER根據系統負載情況決定最優的並行等級，復雜的需要消耗大量的CPU的查詢適合並行處理。不過更新操作UPDATE、INSERT、DELETE不能進行並行處理。 2．2．2 客觀因素優化 客觀因素主要指的是由於設計和開發中存在的缺陷和漏洞；主要優化有以下幾個方面：1. 優化索引（1） 根據查詢條件建立優化的索引、優化訪問方式，限制結果集的數據量。注意填充因子要適當（最好是使用默認值0）。索引應該盡量小，使用位元組數小的列建里索引（參照索引的創建），不要對有限的幾個值的欄位建立單一索引（如性別欄位）。（2） 如果使用LIKE進行查詢的話，簡單的使用INDEX是不行的，全文索引又太耗費空間。LIKE 『N%』使用索引，LIKE 『%N』不使用索引。用LIKE『%N%』查詢時，查詢耗時和欄位值總長度成正比，所以不能用CHAR類型而採用VARCHAR。對於欄位的值很長的欄位建立全文索引。（3） 重建索引DBCC REINDEX，DBCC INDEXDEFRAG，收縮數據和日誌DBCC SHRINKDB，DBCC SHRINKFILE。設置自動收縮日誌，對與大的資料庫不要設置資料庫自動增長，它會降低伺服器的性能。2. 資料庫部署優化（1） DB SERVER和APPLICATION SERVER分離，OLTP和OLAP分離；（2） 使用分區視圖。分布式分區視圖可用於實現資料庫伺服器聯合體，聯合體是一組分開管理的伺服器，他們互相協作分擔系統的處理負荷。A、在實現分區視圖之前，必須先水平分區表。B、在創建成員表後，在每個伺服器上定義一個分布式分區視圖，並且每個視圖具有相同的名稱。這樣引用分布式分區視圖名的查詢可以在任何一個成員伺服器上運行。系統操作如同每個成員伺服器都有一個原始表的復本一樣，不過每個伺服器上其實只有一個成員表和一個分布式分區視圖。數據的位置對應用程序是透明的。3. 查詢語句優化 T-SQL的寫法上有很大的講究，DBMS處理查詢計劃的過程是：a、查詢語句的詞法、語法檢查；b、將語句提交給DBMS的查詢優化器；c、優化器做代數優化和存取路徑的優化；d、由預編譯模塊生成查詢規劃；e、在合適的時間提交給系統處理執行；f、將執行結果返回給用戶。（1） COMMIT和ROLLBACK的區別：ROLLBACK回滾所有的事務；COMMIT提交當前的事務。在動態語句中寫事務，請將事務寫在外面，如：BEGIN TRAN EXEC（@SQL） COMMIT TRANS或者將動態SQL寫成函數或者存儲過程。（2） 在大數據兩的查詢輸出SELECT語句中盡量不要使用自定義函數，調用自定義函數的函數時系統調用是一個迭代過程，很影響查詢輸出性能的。在查詢欄位時盡可能使用小欄位兩輸出，並在WHERE子句或者使用SELECT TOP 10/1 PERCENT來限制返回的記錄數，使用SET ROWCOUNT來限制操作的記錄數，避免整表掃描。返回不必要的數據，不但浪費了伺服器的I/O資源，加重了網路的負擔，如果表很大的話，在表掃描期間將表鎖住，禁止其他的聯接訪問，後過很嚴重的。（3） SQL的注釋申明對執行查詢輸出沒有任何影響。（4） 使用計算列對數據進行簡單計算，盡量避免在查詢語句中對數據進行運算。（5） 盡可能不使用游標，它會佔用大量的資源。如果需要ROW-BY-ROW地執行，盡量採用非游標技術，如：客戶端循環、臨時表、TABLE變數、子查詢、CASE語句等等。（6） 使用PROFILER來跟蹤查詢，得到查詢所需的時間，找出SQL的問題所在，用索引優化器優化索引。（7） 注意UNION和UNION ALL的區別。在沒有必要的時候不要用DISINCT，它同UNION一樣會降低查詢速度，重復的記錄在查詢里是沒有問題的。（8） 用sp_configure 『query governor cost limit』或者 SET QUERY_COVERNOR_COST_LIMIT來限制查詢消耗的資源。當評估查詢消耗的 資源超出限制時，伺服器自動取消查詢，在查詢之前就扼殺掉。SET LOCKTIME 設置鎖的時間。（9） 不要在WHERE子句中列名加函數，如CONVERT，SUBSTRING等，如果必須用函數的時候，創建計算列在創建索引來替代。NOT IN會多次掃描表，使用EXISTS、NOT EXISTS、IN、LEFT OUTER JOIN來替代，其中EXISTS比IN更快，最慢的NOT操作。（10） 使用QUERY ANALYZER，查看SQL語句的查詢計劃和評估分析是否是優化的SQL。一般20%的代碼佔用了80%的資源，優化的重點就是這些慢的地方。（11） 如果使用了IN或者OR等時發現查詢沒有走索引，使用顯式申明指定索引，如：Select * From FA01(INDEX=IX_SEX) Where AA0107 IN(『01』,『02』)。（12） 在需要對已有數據進行比較復雜計算才能獲得查詢的結果數據時,將需要查詢的結果預先計算好放在表中,查詢的時候在SELECT。（13） 資料庫有一個原則是代碼離數據越近越好，所有有限選擇DEFAULT，依次為RULES，CONSTRAINT，PROCEDURE來編寫程序的質量高，速度快。如果要插入大的二進制到IMAGE列，使用存儲過程，千萬不要用內嵌INSERT直接插入。因為這樣應用程序首先將二進制轉換成字元串，伺服器收到字元後又將他轉換成二進制。存儲過程直接傳入二進制參數即可，處理速度明顯改善，如：CREATE PROCEDURE image_insert @image varbinary as Insert into table(fImage) values(@image)。（14） Between在某些時候比IN速度更快，更快地根據索引找到范圍。由於IN會比較多次，所以有時會慢些。（15） 盡量不要建沒有作用的事務例如產生報表時，浪費資源，只有在必須使用事務時才建立合適的事務。（16） 用OR的字句可以分解成多個查詢，並通過UNION連接多個查詢。速度取決與是否使用索引。如果查詢需要用聯合索引，用UNION ALL執行的效率更高些。（17） 盡量少用視圖，視圖的效率低。對視圖操作比直接對表操作慢，可以用SRORED PROCEDURE來代替。特別是不要用視圖嵌套，嵌套視圖增加了尋找原始資料的難度。視圖是存放在伺服器上的被優化好了的已經產生查詢規劃的SQL。對單表數據檢索時，不要使用指向多表的視圖，否則增加了不必要的系統開銷，查詢也會受到干擾。沒有必要時不要用DISTINCT和ORDER BY，這些動作可以改在客戶端執行，增加了額外的開銷，這同UNION和UNION ALL原理相同。（18） 當使用SELECT INTO和CREATE TABLE時，會鎖住系統表（SYSOBJECTS，SYSINDEXES等），從而阻塞其他的連接的存取。所以千萬不要在事務內部使用。如果經常要用到臨時表時請使用實表或者臨時表變數。盡量少用臨時表，用結果集和TABLE類型的變數來代替。（19） 在使用GROUP BY HAVING子句時，在使用前剔除多餘的行，盡量避免使用HAVING子句剔除行工作。剔除行最優的執行順序是：SELECT的WHERE子句選擇所有合適的行，GROUP
❹ access中使用sql語句not in效率太低，有沒有效率高的辦法啊
數據量多的話 ACCESS本身效率就會變低 access效率就是和數據量多少成反比的 建議換成SQL的資料庫好了
❺ nvarchar及nvarchar中的n及max是否會影響性能 實例分析
view plain to clipboardprint?
----------------------------------------------------------------------------------
-- Subject : nvarchar(n)及nvarchar(max)中的n及max是否會影響性能
-- Author : 柳永法(yongfa365) http://www.yongfa365.com/ [email protected]
-- CreateDate : 2010-11-22 23:31:04
-- Environment : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
-- Apr 2 2010 15:53:02
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.2 <x86></x86> (Build 3790: Service Pack 2)
----------------------------------------------------------------------------------
--首先創建兩個表，一個放nvarchar(4000),一個放nvarchar(max)
CREATE TABLE [dbo].[testnvarchar4000] (
id int IDENTITY (1, 1) ,
cnt nvarchar(4000)
)
GO
CREATE TABLE [dbo].[testnvarcharmax] (
id int IDENTITY (1, 1) ,
cnt nvarchar(max)
)
GO
--然後插入10萬條數據，每個cnt里放4000個字元(nvarchar(n)里n的最大值)，大約1.6G
BEGIN TRANSACTION
DECLARE @i INT ;
SET @i = 0 ;
while @i<100000
begin
insert into [testnvarchar4000] values(LEFT(REPLICATE(cast(@i as nvarchar)+'我是柳永法',1000),4000))
insert into [testnvarcharmax] values(LEFT(REPLICATE(cast(@i as nvarchar)+'我是柳永法',1000),4000))
set @i=@i+1
END
COMMIT
--清空緩存，或重啟SQL服務，測試查詢速度及lob讀取情況(lob是大對象的意思)
--測試testnvarcharmax
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT COUNT(*)
FROM testnvarcharmax
WHERE cnt LIKE '%柳永法%'
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
--測試testnvarchar4000
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT COUNT(*)
FROM testnvarchar4000
WHERE cnt LIKE '%柳永法%'
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
--結果：
--(1 行受影響)
--表 'testnvarcharmax'。掃描計數 3，邏輯讀取 100000 次，物理讀取 8494 次，預讀 99908 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
--
-- SQL Server 執行時間:
-- CPU 時間 = 1172 毫秒，佔用時間 = 30461 毫秒。
--(1 行受影響)
--表 'testnvarchar4000'。掃描計數 3，邏輯讀取 100000 次，物理讀取 8523 次，預讀 99916 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
--
-- SQL Server 執行時間:
-- CPU 時間 = 968 毫秒，佔用時間 = 30038 毫秒。
--從結果可以看出，這兩次讀取時間基本相同，並且都沒有lob讀取，以上數據為多次測試結果。
--給testnvarcharmax前1000條欄位長度+1,來測試是不是超過4000字就會使用lob讀取
UPDATE testnvarcharmax SET cnt=cnt+'1' WHERE id <=1000
--結果：
--(1 行受影響)
--表 'testnvarcharmax'。掃描計數 3，邏輯讀取 100000 次，物理讀取 8292 次，預讀 99696 次，lob 邏輯讀取 900 次，lob 物理讀取 83 次，lob 預讀 0 次。
--
-- SQL Server 執行時間:
-- CPU 時間 = 1124 毫秒，佔用時間 = 30318 毫秒。
--此結果顯示使用了lob讀取。但時間相差也不太大。
--欄位值加倍，再測試：
UPDATE testnvarcharmax SET cnt=cnt+cnt WHERE id <=1000
--結果：
--(1 行受影響)
--表 'testnvarcharmax'。掃描計數 3，邏輯讀取 100000 次，物理讀取 8164 次，預讀 99521 次，lob 邏輯讀取 1000 次，lob 物理讀取 101 次，lob 預讀 0 次。
--
-- SQL Server 執行時間:
-- CPU 時間 = 1094 毫秒，佔用時間 = 31095 毫秒。
從以上結果基本上可以證明自己文章頭提到的觀點，nvarchar(n)或nvarchar(max)中的n及max只是一個符號，不會影響佔用空間及性能，除非max存儲字元數大於4000，所以我們只要關注實際業務需要就可以了
❻ 開啟mysql慢查詢 的話 對系統性能影響大么
這是一個慢查詢日誌的展示工具，能夠幫助 DBA 或者開發人員分析資料庫的性能問題，給出全面的數據擺脫直接查看 slow-log。QAN（Query Analytics）
PMM 目前有 2 個版本，但是對於 QAN 來說其大致由三部分組成：
QAN-Agent（client）：負責採集 slow-log 的數據並上報到服務端
QAN-API（server）：負責存儲採集的數據，並對外提供查詢介面
QAN-APP：專門用來展示慢查詢數據的 grafana 第三方插件
1. 數據流轉
slow-log --> QAN-Agent --> QAN-API <--> QAN-APP（grafana）
2. pmm1 架構圖
❼ 武新：海量數據環境下如何提升I/O效率
一般而言，解決這三大性能瓶頸的手段主要有兩個，即硬體改進與軟體優化。 南大通用數據技術有限公司武新 所謂的硬體改進是一種被動的優化手段，主要是通過提升硬體來對系統進行擴容和優化。之所說這種方式較為被動，主要在於這需要提供巨大的成本，而且擴容的效果是有限的，不可能無限擴容，不是資料庫性能優化的根本之道。更為主要的是，體現不出DBA的價值，DBA首先應該想到的是利用主動的方式去對資料庫性能進行優化。 對資料庫優化的方式主要分為三個手段，如修改數據模型、修改SQL、影響CBO，以產生有效的SQL執行計劃;另外也可適用索引、MV等工具;第三種方式則是使用並行，充分利用SMP的特徵。不管採用何種方式對資料庫性能進行優化，我們要明確的是，資料庫性能優化的核心思想是提升查詢SQL的I/O效率。 另外我們還需要明白有關I/O的兩個定義，即I/O速度與I/O效率。I/O速度是I/O數據量與時間的比值，其單位是MB/s;而I/O效率是有效數據與實際I/O數據量的比值，其結果是一個百分比值。並且I/O速度與I/O效率並沒有相關性，通過硬體擴容只能提升I/O性能，並不能提高I/O效率。I/O效率是衡量不同資料庫面對海量數據統計、分析性能差異最關鍵的指標、也是現代資料庫研發最核心的問題之一。 資料庫性能最核心的問題之一是I/O效率低下，解決這個問題主要有以下幾個方式：1、使用不同索引定位需要的數據;2、產生高效的SQL執行計劃;3、提升每次I/O包含的有效數據量;4、對數據進行預計算(MV);5、使用分區技術、壓縮技術。 資料庫性能最核心的問題之二是傳統索引帶來的矛盾。使用索引的最大作用是提升I/O效率。但其產生的問題也不少，如對應用不透明、維護代價極高以及需要人工優化等等。不使用索引的好處顯而易見，無需維護，並且數據入庫速度快，但查詢I/O效率低、導致性能低下。這是使用或不適用傳統索引所帶來的矛盾，不管用還是不用，都會給資料庫性能帶來一定的影響。因此海量環境下的資料庫分析需要一種新的索引方式，在此，南大通用資料庫公司的武新給我們介紹了一種新的索引方式，即在GBase 8a中所用到的列存儲+粗粒度索引的方式。 這種方式主要有一下幾個特點，粗粒度的擴展性很高，幾乎不會對數據入庫性能造成影響;其次，局部性實現高效的數據邊入庫邊查詢、統計，數據入庫速度不隨數據量增加而降低，;並且，全欄位索引使得不需要再手工建立索引。
❽ sql函數與string拼接查詢id集合哪個效率高
由於SQL面向結面向程查詢語言所般支持SQL語言型關系型資料庫都使用基於查詢本優化器即查詢提供佳執行策略於優化器輸入條查詢語句輸執行策略 條SQL查詢語句種執行策略優化器估計全部執行所需間少所謂本低種所優化都基於用記所使用查詢語句where句優化器where句優化主要用搜索參數(Serach Argument) 搜索參數核思想資料庫使用表欄位索引查詢數據必直接查詢記錄數據 帶 =、<、、>= 等操作符條件語句直接使用索引列搜索參數： emp_id = "10001" 或 salary > 3000 或 a =1 and c = 7 列則搜索參數： salary = emp_salary 或 dep_id != 10 或 salary * 12 >= 3000 或 a=1 or c=7 應盡能提供些冗餘搜索參數使優化器更選擇余請看3種： 第種： select employee.emp_name,department.dep_name from department,employee where (employee.dep_id = department.dep_id) and (department.dep_code='01') and (employee.dep_code='01'); 搜索析結： Estimate 2 I/O operations Scan department using primary key for rows where dep_code equals '01' Estimate getting here 1 times Scan employee sequentially Estimate getting here 5 times 第二種： select employee.emp_name,department.dep_name from department,employee where (employee.dep_id = department.dep_id) and (department.dep_code='01'); 搜索析結： Estimate 2 I/O operations Scan department using primary key for rows where dep_code equals '01' Estimate getting here 1 times Scan employee sequentially Estimate getting here 5 times 第種與第二種運行效率相同第種優化器提供更選擇機 第三種： select employee.emp_name,department.dep_name from department,employee where (employee.dep_id = department.dep_id) and (employee.dep_code='01'); 種使用索引優化…… 使用SQL語句應注意幾點： 1、避免使用兼容數據類型例 Binary兼容數據類型兼容能使優化器執行些本進行優化操作例： select emp_name form employee where salary > 3000; 語句若salaryFloat類型則優化器難其進行優化3000整數我應編程使用3000.0要等運行讓DBMS進行轉化 2、盡量要使用表達式編繹所SQL能使用其平均密度估計要命記錄數 3、避免搜索參數使用其數操作符： select emp_name from employee where salary * 12 > 3000; 應改： select emp_name from employee where salary > 250; 4、避免使用 != 或 > 等操作符使系統使用索引能直接搜索表數據 目前,幾乎所應用程序都要資料庫打交道通查詢資料庫容易獲想要數據,令滿意:某些查詢間,響應速度慢究其原,硬體設備(CPU、磁碟)存取速度跟,內存容量夠,需要計算機製造商努力;另面沒進行查詢優化本文查詢優化問題,談點實踐體 解查詢 種查詢解執行,根據付銷少決定何解,何執行便敘述,先給例 關系:SUPPLIER(S#,SNAME,CITY) :S(關鍵字) PARTS (P#,PNAME,SIZE) :P(關鍵字) PROJECT (J#,JNAME,COLOR) :J(關鍵字) INVENTORY(S#,P#,QO) :V(關鍵字) SUPPLY(S#,J#,P#,QU) :Y(關鍵字) 其,QO:現數量 QU:要用數量 查詢找某城市能提供黑色轎車,且供應量於1000供應商名單 1. 般查詢 (1) 形卡氏積 S×P×J×V×Y; (2) 卡氏積選擇滿足條件元組; (3) S.SNAME投影 5元查詢查詢涉及卡氏積,卡氏積元組數組合性增,僅需要量存儲空間,且執行查詢間 2. 優化查詢 該查詢解處理介紹兩種: (1) 元查詢提取任N元查詢Q(X1,X2......Xn)替換元查詢Q1其執行Q2,即Q→(Q1,Q2) (2) 化簡 Q替換兩查詢Q1Q2,Q2Q1執行執行,變化,即Q1(X1,X2......Xm), Q2(Xm,Xm+1......Xn) 例例查詢兩元查詢 SELECTP# FROMP WHEREP.PNAME=『轎車』 AND P.COLOR=『黑色』 SELECTS#,J#,P#,QO FROMY WHEREV.QO>1000 另部查詢: SELECTS.SNAME FROMS,P,J,V,Y WHERE (S.S#=V.S# AND S.S#=Y.S# AND S.CITY=J.CITY AND P.P#=V.P# AND T.P#=V.P# AND J.J#=Y.J#) 面例查詢容易化簡化涉及(P,V)查詢其執行涉及(S,J,Y,V)查詢: SELECTS.SNAME FORMS,J,V,Y WHERES.CITY=J.CITY AND S.S#=Y.S# AND J.J#=Y.J# AND V.QO>1000 AND P.#=Y.P# AND V.S#=Y.S# AND V.P#=(SELECT V.P# FROM V,P WHERE V.P#=P P#AND P.PNAME=『轎車』 ANDP.COLOR=『黑色』) 3. 綜所述 元查詢提取幾乎總處,關系運算前盡能減少關系體積減少相應系統銷起作用;·通期待優化結,並絕 選擇優存取路徑 計算查詢表達式值要充考慮索引、數據存儲布等存取路徑,進步提高查詢效率例,選擇欄位、連接欄位否索引,利用索引HASHING算快速存取包含索引屬性特定值記錄建立索引,用戶按順序讀文件記錄,依照接近於物理順序順序讀文件記錄非效種接近物理順序讀取文件記錄索引稱聚簇索引聚簇索引使我利用存儲塊記錄物理聚簇優點,加快查詢速度面具體談點實踐體 前久,筆者參加內發外資料庫應用系統編程工作該系統台使用Oracle 7.3資料庫,OracleDBMS處理SQL執 行語句順序: (1) 根據WHERE句選擇行; (2) 根據GROUP BY句些行進行聚合; (3) 每組用組函數計算結; (4) 根據HAVING句選擇排除組; (5) 根據ORDER BY句組函數所結組進行排序 體現查詢優化思路執行順序,查詢性能具直接影響般說,WHERE句濾記錄越,查詢速度越快減少GROUP BY運算必須處理行數量工作筆者體 1. 盡量避免連接 例: PowerBuilder 5.0數據窗口選擇庫表自各表相同屬性名(域相同)連接起種自連接數情況益,些情況卻需要取消連接查詢條件P2000、P3000、P4000效區都1,兩種: (1) WHERE(P2000.效區=P3000.效區 P3000.效區=P4000.效區 P2000.效區=1 (2) WHERE(P2000.效區=1 P3000.效區=1 P4000.效區=1 第種自基礎添加P2000.效區=1,第二種先要取消連接,再重新寫WHERE語句表面看,第種簡單,其實第種降低執行效率,必要連接所筆者提醒使用者,要省事降低系統效率 2. 選擇佳解決案 解決同問題固,應用應該選擇佳解決例,某問題資料庫查詢兩種,執行結,效率卻同 查詢要求:程序運行界面輸入負責代碼(放code),查詢P2000表負責代碼等於code負責名;沒輸入負責代碼,查詢P2000表所負責名負責代碼取值范圍0～9999,兩種解決別: (1) IF 沒輸入負責代碼 THEN code1=0 code2=9999 ELSE code1=code2=負責代碼 END IF 執行SQL語句: SELECT 負責名 FROM P2000 WHERE 負責代碼>=:code1 AND負責代碼 1001) OR order_num=1008 雖customer_numorder_num建索引面語句優化器使用順序存取路徑掃描整表語句要檢索離行集合所應該改語句： SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001 UNION SELECT ＊ FROM orders WHERE order_num=1008 能利用索引路徑處理查詢 4．避免相關查詢 列標簽同主查詢where句查詢現能主查詢列值改變查詢必須重新查詢查詢嵌套層越效率越低應盡量避免查詢查詢避免要查詢濾掉盡能行 5．避免困難規表達式 MATCHESLIKE關鍵字支持通配符匹配技術叫規表達式種匹配特別耗費間例：SELECT ＊ FROM customer WHERE zipcode LIKE 98_ _ _ 即使zipcode欄位建立索引種情況採用順序掃描式語句改SELECT ＊ FROM customer WHERE zipcode >98000執行查詢利用索引查詢顯提高速度 另外要避免非始串例語句：SELECT ＊ FROM customer WHERE zipcode[23] >80where句採用非始串語句使用索引 6．使用臨表加速查詢 表集進行排序並創建臨表能加速查詢助於避免重排序操作且其面能簡化優化器工作例： SELECT cust.namercvbles.balance……other columns FROM custrcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance>0 AND cust.postcode>98000 ORDER BY cust.name 查詢要執行止所未付款客戶找放臨文件並按客戶名字進行排序： SELECT cust.namercvbles.balance……other columns FROM custrcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance>0 ORDER BY cust.name INTO TEMP cust_with_balance 面式臨表查詢： SELECT ＊ FROM cust_with_balance WHERE postcode>98000 臨錶行要比主錶行少且物理順序所要求順序減少磁碟I/O所查詢工作量幅減少 注意：臨表創建反映主表修改主表數據頻繁修改情況注意要丟失數據 7．用排序取代非順序存取 非順序磁碟存取慢操作表現磁碟存取臂移SQL語句隱藏情況使我寫應用程序容易寫要求存取量非順序頁查詢 些候用資料庫排序能力替代非順序存取能改進查詢 資料庫查詢優化技術(2) 際 實例析 面我舉製造公司例說明何進行查詢優化製造公司資料庫包括3表模式所示： 1．part表 零件號 零件描述 其列 （part_num） （part_desc） （other column） 102032 Seageat 30G disk …… 500049 Novel 10M network card …… …… 2．vendor表 廠商號 廠商名 其列 （vendor _num） （vendor_name） （other column） 910257 Seageat Corp …… 523045 IBM Corp …… …… 3．parven表 零件號 廠商號 零件數量 （part_num） （vendor_num） （part_amount） 102032 910257 3,450,000 234423 321001 4000000 …… 面查詢些表定期運行並產關於所零件數量報表： SELECT part_descvendor_namepart_amount FROM partvendorparven WHERE part.part_num=parven.part_num AND parven.vendor_num = vendor.vendor_num ORDER BY part.part_num 建立索引述查詢代碼銷十巨我零件號廠商號建立索引索引建立避免嵌套反復掃描關於表與索引統計信息： 表 行尺寸 行數量 每頁行數量 數據頁數量 （table） (row size) （Row count） （Rows/Pages） （Data Pages） part 150 10000 25 400 Vendor 150 1000 25 40 Parven 13 15000 300 50 索引 鍵尺寸 每頁鍵數量 頁面數量 （Indexes） (Key Size) （Keys/Page) (Leaf Pages） part 4 500 20 Vendor 4 500 2 Parven 8 250 60 看起相簡單3表連接其查詢銷通查看系統表看part_numvendor_num簇索引索引按照物理順序存放parven表沒特定存放序些表說明緩沖頁非順序存取功率語句優化查詢規劃：首先part順序讀取400頁再parven表非順序存取1萬每2頁（索引頁、數據頁）總計2萬磁碟頁vendor表非順序存取1.5萬合3萬磁碟頁看索引連接花費磁碟存取5.04萬 實際我通使用臨表3步驟提高查詢效率： 1．parven表按vendor_num序讀數據： SELECT part_numvendor_numprice FROM parven ORDER BY vendor_num INTO temp pv_by_vn 語句順序讀parven（50頁）寫臨表（50頁）並排序假定排序銷200頁總共300
❾ mysql怎樣高效率隨機獲取n條數據
資料庫優化有很多可以講，按照支撐的數據量來分可以分為兩個階段：單機資料庫和分庫分表，前者一般可以支撐500W或者10G以內的數據，超過這個值則需要考慮分庫分表。另外，一般大企業面試往往會從單機資料庫問起，一步一步問到分庫分表，中間會穿插很多資料庫優化的問題。本文試圖描述單機資料庫優化的一些實踐，資料庫基於mysql，如有不合理的地方，歡迎指正。
1、表結構優化
在開始做一個應用的時候，資料庫的表結構設計往往會影響應用後期的性能，特別是用戶量上來了以後的性能。因此，表結構優化是一個很重要的步驟。
1.1、字元集
一般來說盡量選擇UTF-8，雖然在存中午的時候GBK比UTF-8使用的存儲空間少，但是UTF-8兼容各國語言，其實我們不必為了這點存儲空間而犧牲了擴展性。事實上，後期如果要從GBK轉為UTF-8所要付出的代價是很高的，需要進行數據遷移，而存儲空間完全可以用花錢擴充硬碟來解決。
1.2、主鍵
在使用mysql的innodb的時候，innodb的底層存儲模型是B+樹，它使用主鍵作為聚簇索引，使用插入的數據作為葉子節點，通過主鍵可以很快找到葉子節點，從而快速獲取記錄。因此在設計表的時候需要增加一個主鍵，而且最好要自增。因為自增主鍵可以讓插入的數據按主鍵順序插入到底層的B+樹的葉子節點中，由於是按序的，這種插入幾乎不需要去移動已有的其它數據，所以插入效率很高。如果主鍵不是自增的，那麼每次主鍵的值近似隨機，這時候就有可能需要移動大量數據來保證B+樹的特性，增加了不必要的開銷。
1.3、欄位
1.3.1、建了索引的欄位必須加上not null約束，並且設置default值
1.3.2、不建議使用float、double來存小數，防止精度損失，建議使用decimal
1.3.3、不建議使用Text/blob來保存大量數據，因為對大文本的讀寫會造成比較大的I/O開銷，同時佔用mysql的緩存，高並發下會極大的降低資料庫的吞吐量，建議將大文本數據保存在專門的文件存儲系統中，mysql中只保存這個文件的訪問地址，比如博客文章可以保存在文件中，mysql中只保存文件的相對地址。
1.3.4、varchar類型長度建議不要超過8K。
1.3.5、時間類型建議使用Datetime，不要使用timestamp，雖然Datetime佔用8個位元組，而timestamp只佔用4個位元組，但是後者要保證非空，而且後者是對時區敏感的。
1.3.6、建議表中增加gmt_create和gmt_modified兩個欄位，用來記錄數據創建的修改時間。這兩個欄位建立的原因是方便查問題。
1.4、索引創建
1.4.1、這個階段由於對業務並不了解，所以盡量不要盲目加索引，只為一些一定會用到索引的欄位加普通索引。
1.4.2、創建innodb單列索引的長度不要超過767bytes，如果超過會用前255bytes作為前綴索引
1.4.3、創建innodb組合索引的各列索引長度不要超過767bytes，一共加起來不要超過3072bytes
2、SQL優化
一般來說sql就那麼幾種：基本的增刪改查，分頁查詢，范圍查詢，模糊搜索，多表連接
2.1、基本查詢
一般查詢需要走索引，如果沒有索引建議修改查詢，把有索引的那個欄位加上，如果由於業務場景沒法使用這個欄位，那麼需要看這個查詢調用量大不大，如果大，比如每天調用10W+，這就需要新增索引，如果不大，比如每天調用100+，則可以考慮保持原樣。另外，select * 盡量少用，用到什麼欄位就在sql語句中加什麼，不必要的欄位就別查了，浪費I/O和內存空間。
2.2、高效分頁
limit m,n其實質就是先執行limit m+n，然後從第m行取n行，這樣當limit翻頁越往後翻m越大，性能越低。比如
select * from A limit 100000,10，這種sql語句的性能是很差的，建議改成下面的版本:
selec id,name,age from A where id >=(select id from A limit 100000,1) limit 10
2.3、范圍查詢
范圍查詢包括between、大於、小於以及in。Mysql中的in查詢的條件有數量的限制，若數量較小可以走索引查詢，若數量較大，就成了全表掃描了。而between、大於、小於等，這些查詢不會走索引，所以盡量放在走索引的查詢條件之後。
2.4、模糊查詢like
使用 like %name%這樣的語句是不會走索引的，相當於全表掃描，數據量小的時候不會有太大的問題，數據量大了以後性能會下降的很厲害，建議數據量大了以後使用搜索引擎來代替這種模糊搜索，實在不行也要在模糊查詢前加個能走索引的條件。
2.5、多表連接
子查詢和join都可以實現在多張表之間取數據，但是子查詢性能較差，建議將子查詢改成join。對於mysql的join，它用的是Nested Loop Join演算法，也就是通過前一個表查詢的結果集去後一個表中查詢，比如前一個表的結果集是100條數據，後一個表有10W數據，那麼就需要在100*10W的數據集合中去過濾得到最終的結果集。因此，盡量用小結果集的表去和大表做join，同時在join的欄位上建立索引，如果建不了索引，就需要設置足夠大的join buffer size。如果以上的技巧都無法解決join所帶來的性能下降的問題，那乾脆就別用join了，將一次join查詢拆分成兩次簡單查詢。另外，多表連接盡量不要超過三張表，超過三張表一般來說性能會很差，建議拆分sql。
3、資料庫連接池優化
資料庫連接池本質上是一種緩存，它是一種抗高並發的手段。資料庫連接池優化主要是對參數進行優化，一般我們使用DBCP連接池，它的具體參數如下：
3.1 initialSize
初始連接數，這里的初始指的是第一次getConnection的時候，而不是應用啟動的時候。初始值可以設置為並發量的歷史平均值
3.2、minIdle
最小保留的空閑連接數。DBCP會在後台開啟一個回收空閑連接的線程，當該線程進行空閑連接回收的時候，會保留minIdle個連接數。一般設置為5，並發量實在很小可以設置為1.
3.3、maxIdle
最大保留的空閑連接數，按照業務並發高峰設置。比如並發高峰為20，那麼當高峰過去後，這些連接不會馬上被回收，如果過一小段時間又來一個高峰，那麼連接池就可以復用這些空閑連接而不需要頻繁創建和關閉連接。
3.4、maxActive
最大活躍連接數，按照可以接受的並發極值設置。比如單機並發量可接受的極值是100，那麼這個maxActive設置成100後，就只能同時為100個請求服務，多餘的請求會在最大等待時間之後被拋棄。這個值必須設置，可以防止惡意的並發攻擊，保護資料庫。
3.5、maxWait
獲取連接的最大等待時間，建議設置的短一點，比如3s，這樣可以讓請求快速失敗，因為一個請求在等待獲取連接的時候，線程是不可以被釋放的，而單機的線程並發量是有限的，如果這個時間設置的過長，比如網上建議的60s，那麼這個線程在這60s內是無法被釋放的，只要這種請求一多，應用的可用線程就少了，服務就變得不可用了。
3.6、minEvictableIdleTimeMillis
連接保持空閑而不被回收的時間，默認30分鍾。
3.7、validationQuery
用於檢測連接是否有效的sql語句，一般是一條簡單的sql，建議設置
3.8、testOnBorrow
申請連接的時候對連接進行檢測，不建議開啟，嚴重影響性能
3.9、testOnReturn
歸還連接的時候對連接進行檢測，不建議開啟，嚴重影響性能
3.10、testWhileIdle
開啟了以後，後台清理連接的線程會沒隔一段時間對空閑連接進行validateObject，如果連接失效則會進行清除，不影響性能，建議開啟
3.11、numTestsPerEvictionRun
代表每次檢查鏈接的數量，建議設置和maxActive一樣大，這樣每次可以有效檢查所有的鏈接。
3.12、預熱連接池
對於連接池，建議在啟動應用的時候進行預熱，在還未對外提供訪問之前進行簡單的sql查詢，讓連接池充滿必要的連接數。
4、索引優化
當數據量增加到一定程度後，靠sql優化已經無法提升性能了，這時候就需要祭出大招：索引。索引有三級，一般來說掌握這三級就足夠了，另外，對於建立索引的欄位，需要考慮其選擇性。
4.1、一級索引
在where後面的條件上建立索引，單列可以建立普通索引，多列則建立組合索引。組合索引需要注意最左前綴原則。
4.2、二級索引
如果有被order by或者group by用到的欄位，則可以考慮在這個欄位上建索引，這樣一來，由於索引天然有序，可以避免order by以及group by所帶來的排序，從而提高性能。
4.3、三級索引
如果上面兩招還不行，那麼就把所查詢的欄位也加上索引，這時候就形成了所謂的索引覆蓋，這樣做可以減少一次I/O操作，因為mysql在查詢數據的時候，是先查主鍵索引，然後根據主鍵索引去查普通索引，然後根據普通索引去查相對應的記錄。如果我們所需要的記錄在普通索引里都有，那就不需要第三步了。當然，這種建索引的方式比較極端，不適合一般場景。
4.4、索引的選擇性
在建立索引的時候，盡量在選擇性高的欄位上建立。什麼是選擇性高呢？所謂選擇性高就是通過這個欄位查出來的數據量少，比如按照名字查一個人的信息，查出來的數據量一般會很少，而按照性別查則可能會把資料庫一半的數據都查出來，所以，名字是一個選擇性高的欄位，而性別是個選擇性低的欄位。
5、歷史數據歸檔
當數據量到了一年增加500W條的時候，索引也無能為力，這時候一般的思路都是考慮分庫分表。如果業務沒有爆發式增長，但是數據的確在緩慢增加，則可以不考慮分庫分表這種復雜的技術手段，而是進行歷史數據歸檔。我們針對生命周期已經完結的歷史數據，比如6個月之前的數據，進行歸檔。我們可以使用quartz的調度任務在凌晨定時將6個月之前的數據查出來，然後存入遠程的hbase伺服器。當然，我們也需要提供歷史數據的查詢介面，以備不時之需。
以上就是對mysql 單機資料庫的優化資料整理，後續繼續補充相關資料，謝謝大家對本站的支持！
❿ sql語句聯合查詢 與 視圖想比較的話，那個效率快，為什麼。
sql效率比較快，存儲過程的好處是不僅快且更安全，但移植性差。視圖可以封裝查詢的復雜性，就像面向對象里類的概念一樣。