欧美成人精品手机在线观看_69视频国产_动漫精品第一页_日韩中文字幕网 - 日本欧美一区二区

數據庫性能是整個應用程序性能的重要部分。數據庫優化涉及的內容非常廣泛，各類數據庫都提供眾多的性能指標和大量的優化工 具。

下面我們簡單介紹一下優化的基本概念。一個運行良好的數據庫至少應具有以下特點：

合理的物理結構及硬件能力
合理的物理結構指數據庫文件及整個網絡的物理分布。硬件能力指是否有足夠的硬件資源來完成應用程序功能。合理的物理結構至少帶來兩個方面的好處：

1、適量的數據冗余，提高數據安全性。

2、平衡磁盤IO，增強數據讀寫能力。

而足夠的硬件能力的作用自然不言而喻。一旦建立好數據庫系統并開始運行，數據庫的物理結構就不能改變。

合理的系統參數
對應數據庫來說，隨著數據量的變化，數據庫性能也一直處在變化之中，因此數據庫建立之初設定的系統參數會變的越來越不合適，有時甚至阻礙了數據庫的正常運 行，導致性能瓶頸。因此觀察性能變化，隨時調整系統參數，使數據庫一直處于一個良好的運行狀態，就成為管理員最重要的日常工作之一。對系統參數的合理調 整，常常能將數據庫從崩潰的邊緣挽救回來。oracle、sqlserver這樣的高品質數據庫都為系統參數提供了靈活多變的調整方式。一般來說，只要數 據庫結構設計不存在重大缺陷，通過后期的調整，都可以使數據庫運行在一個良好的狀態下。

性能優良的sql語句
sql語句是在程序開發階段就已經決定了的，由低效率的sql語句給數據庫性能帶來的問題，往往在數據庫開始運行一段時間后才凸現出來（隨著數據量的不斷 增加），但發現后就變的難以改變，成為不可突破的性能瓶頸。因此，作為一名合格的開發人員，應該建立基本的優化概念和良好的編程習慣，從整體上提高應用程 序的質量。

同時，提高sql語句的執行效率，是提高整個數據庫性能的最立竿見影且價格低廉的方法之一。因為幾乎所有的數據庫都會不可避免的運行一些效率低下的 sql語句。對數據庫性能的調整，往往都是從sql語句調優開始的。

下面我們簡單介紹數據庫是怎么執行sql語句的。

sql語句是唯一從應用程序發送到數據庫實例的命令。數據庫實例所做的全部工作就是接受、解釋和執行sql 語句。

在絕大多數情況下，我們并不需要關心sql語句是怎么執行的，這是因為在當前流行的數據庫軟件中都無一例外的采用了高性能的優化器，而這些優化器在 絕大多數的情況下都能將用戶某些不合理的sql語句結構轉換成更合理的形式，從而有效提高sql的執行效率。

優化器的優化原理有兩種：

基于成本的優化
對一條sql語句，優化器會生成所有可能的執行方式，估計這些執行方式將使用的硬件成本，相互比較后從中選擇成本最低的執行計劃。缺點是必須收集大量統計 數據，對服務器造成額外的負擔。

基于規則的優化
相對基于成本的優化，基于規則的優化則顯得死板的多。比如sqlserver遇到blog_id=*的情況就會去找索引，實在找不到索引才使用全表掃描。 而不考慮有時候不用索引可能效率更高。顯然有些情況下基于規則的優化并不合適。

/*執行計劃的概念:簡單的說執行計劃就是指在執行sql語句前對代碼進行編譯時數據庫實例為sql選擇的執行路徑。如一個sql對A、B、C三個 表進行聯合查詢，數據庫會首先以某種方式對這3個表種符合條件的記錄進行查詢（全表掃描或其他），再將A、B、C表中符合條件的記錄讀入內存，將A、B表 的記錄相比較后得出的結果集與C表相比較，最后得到符合的結果集。但實際情況比這復雜的多。*/

oracle可以選擇兩種優化模式之中的一種，而其他數據庫則是固定的（DB2不清楚，關于DB2的資料太少了），基本都是基于規則的優化。

然而優化器畢竟不是智能的。很多時候，它不可避免的受到sql語句結構的影響。而SQL優化的實質就是在結果正確的前提下，用優化器可以識別的語 句，充份利用索引，減少表掃描的I/O次數，盡量避免表掃描的發生。

sql的書寫原則

以下介紹的標準sql的書寫原則是本文的核心，這些原則適用于絕大部分數據庫。介紹這些原則的時候，我會舉一些簡單的例子來說明，但這些例子在實際 生產中可能沒有什么實際意義。

1、使用索引
如果沒有任何索引，在執行sql語句時必定將做全表掃描。這和我們在看一本書時，如果沒有目錄，我們只能從第一頁開始查找，直到找到查詢的內容是一個道 理。全表掃描是效率最低的查詢方式，我們會看到絕大多數的低效率sql就是使用的全表掃描。因此在對一些大表進行查詢的時候，我們需要關心一下表中是否建 立了索引并盡量使用索引。

總的來說索引可以分為聚集索引和非聚集索引。一個表中只能有一個聚集索引。對一個表建立一個聚集索引后，數據庫會調整表記錄的順序，使表按照索引的 順序重新排列。而非聚集索引則不會改變表的結構。

使用的最常見的索引就是主鍵索引。實際上在表中指定一列為主鍵的時候，就在這列上建立了唯一值索引并強制該列的值唯一，這就成了主鍵索引。

在下面幾種情況下，應該建立索引：

1.   有大量重復值、且經常有范圍查詢（between,>,<，>=,<=）和order by、group by發生的列，可以考慮使用聚集索引；

2.   在使用最頻繁的列且常常需要使用函數的時候，可以考慮使用函數索引。

3.   在使用最頻繁的列且常常需要對兩個或多個列進行聯合查詢的時候，可以考慮使用組合索引。

4.   在重復率較低的列上建立唯一索引。

索引雖有助于提高性能，但不是索引越多越好，恰好相反過多的索引會導致系統低效。因為用戶在表中每加進一個索引，維護索引集合就要做相應的更新工 作，同時每次有數據改變的時候就需要維護索引。

因此盡管使用索引非常有必要，但是在以下情況下，我們并不贊成使用索引。

1、  大量對表數據的修改（插入過刪除）

2、  數據量很小

關于怎么使用索引，就是多使用存在索引的列作為查詢條件。但是使用了帶索引的列，也不一定就使用了索引，后面會提到相應的知識。

2、和任何高級語言一樣，sql也是越簡單越好。
大家先看看下面的語句：

select TOP $size$ BLOG_ID AS blogID,

BLOG_DOMINO AS blogDomino,

BLOG_TEMPLATE_ID AS blogTemplateID

from blog

where BLOG_ID NOT IN

(select BLOG_ID

from blog

where BLOG_ID>=$size$+$fromID$)

AND LOCKED=1

orDER BY BLOG_ID DESC；

通過查看執行計劃，我們可以看到執行查詢的步驟：

1、子查詢中使用blog.pk_blog（主鍵索引）查詢BLOG中滿足blog>=$size$+$fromID$的記錄,這步占總成本 的46％。

2、使用blog.pk_blog（主鍵索引）,查詢BLOG中滿足LOCKED=1的記錄。這步占總成本的44％。

3、使用Merge join。這步占總成本的10％。總成本為0.23。

我們可以看到兩次對blog表的查詢都使用了blog_id上的主鍵索引，同時表連接消耗的資源也比較少。應該說，對于子查詢結構，這個查詢的效率 并不低。但是，這和下面的語句完全是等價的：

select TOP $size$ BLOG_ID AS blogID,

BLOG_DOMINO AS blogDomino,

BLOG_TEMPLATE_ID AS blogTemplateID

from blog

where BLOG_ID<$size$+$fromID$

AND LOCKED=1

orDER BY BLOG_ID DESC；

總成本為0.0035。

優化器不是人工智能，在第一個查詢中指定使用子查詢結構，優化器就無法突破這個限制，盡管兩個查詢是相同的，優化器還是不能將第一種查詢結構等價的 轉換成第二種。所以查詢語句使用的結構對優化器影響重大，我們在進行多表查詢等復雜情況的時候的時候（如可能需要使用聯接查詢、子查詢、嵌套查詢、 group by等），應多進行一些考慮。一個原則是結構要盡量簡單，這樣在編譯效率、執行效率和程序可讀性方面都有好處。

3、謹慎使用is null和is not null
    不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使在列上建立了索引，只要這些列中有一個含有null，該列就會從索引 中排除。也就是說，如果某列存在空值，即使對該列建索引也不會提高性能。同時，任何在where子句中使用is null或is not null的語句優化器是不允許使用索引的。

4、in和exists
很多資料都聲稱exists的效率高于in。而實際情況證明，在子句中使用in和exists效率是一樣的。

select ProductName

from Northwind.dbo.Products

where UnitPrice in

(select UnitPrice

from Northwind.dbo.Products

where ProductName = ‘Sir Rodney’’s Scones’)

select ProductName

from Northwind.dbo.Products b

where exists

(select UnitPrice

from Northwind.dbo.Products a

where a.ProductName = ‘Sir Rodney’’s Scones’

and a.UnitPrice=b.UnitPrice)

查看執行計劃可以看到，上面兩個語句的執行路徑和執行成本都完全一樣。

5、in和or
sqlserver會自動將in轉換成or，因此對sqlserver來說下面兩個語句是一樣的，而其他數據庫中in的效率要高于or。

select blog_name

from blog

where blog_name like ‘AB%’

or blog_name like ‘CD%’

or blog_name like ‘EF%’;

select blog_name

from blog

where blog_name in (‘AB%’,’CD%’,‘EF%’);

6、查詢字段要需要多少，查詢多少
我們每少提取一個字段，查詢速度就會有相應的上升。這主要是因為物理讀取成本降低了。所以要避免select *這樣的查詢，需要哪些字段就查詢哪些字段。

7、將行和操作數減到最少
使用 where 和 HAVING 子句只選擇需要的行，可以將 select 語句返回的行數減到最少。盡量少用不等于運算符 <> 或 !=。數據庫將必須在表或索引中掃描所有的值，以查看它們是否不等于表達式中給定的值。可以使用范圍重寫表達式：

where KeyColumn < ‘TestValue’ AND KeyColumn > ‘TestValue’8、盡量少用格式轉換，防止出現隱含的格式轉換。
舉個簡單的例子：

某個表中有一個時間字段time date型，現在開發人員需要寫一個sql：查詢出在2004-04-21到2005-04-21之間，注冊了多少blog新用戶。我們比較下面兩個語 句：

select *

from blog

where to_char(create_time,yyyymmdd) between ’20040421’and ’20050421’;

――將time轉換成字符型，和’20040421’及’20050421’做比較。

select *

from blog

where create_time<=to_date(‘2004-04-21’,’yyyy-mm-dd’)

and create_time<=to_date(‘2005-04-21’,’yyyy-mm-dd’)；

――將20040421及20050421轉換成date型，和create_time做比較。

（PL/SQL）

第二個語句的性能明顯優于第一個。這是因為第一個sql數據庫必須將每一行記錄的create_time都轉換一次，而第一個只需要對常量做一次轉 換。(第二個語句的性能明顯優于第一個的另一個重要原因就是第一個查詢不能使用create_time上的索引)。

隱含的格式轉換既是數據庫在必要時會自動將一個數據類型轉換成另一種可兼容的數據類型：

最常見的情況，一個字段sort_id varchar(10)型，現在要查詢出所有sort_id為3的用戶。

select * from blog where sort_id=3;

如果用戶沒有查看表結構，很可能認為sort_id是int型而寫出上面的語句。同時，數據庫不會報錯，這是因為編譯引擎自動做了一次數據類型轉 換。這就是一個隱藏轉換的典型例子。在查詢大量數據的時候，這樣不必要的轉換會降低sql的效率。

9、帶通配符（%）的like語句
要求在blog表中查詢名字中包含“我”的人。可以采用如下的查詢SQL語句：

select * from blog where blog_name like ‘%我%’;

這里由于通配符（%）在搜尋詞首出現，所以Oracle將不使用blog_name的索引。在很多情況下可能無法避免這種情況，但是一定要心中有 底，通配符如此使用會降低查詢速度。然而當通配符出現在字符串其他位置時，優化器就能利用索引。在下面的查詢中索引得到了使用：

select * from blog where blog_name like ‘c%’;

對于sqlserver，這種情況沒有影響。

10、ORDER BY和GROPU BY
對于ORDER BY和GROUP BY短語，任何一種索引都有助于select的性能提高。但是使用聚集索引的效率最高。如blog表，blog_id 為主鍵索引，blog_name上有一個聚集索引。

select *

from blog

order by blog_name;

由于聚集索引一個表中只能有一個，因此在無法使用聚集索引的情況下，使用其他索引也能提高查詢效率。

11、禁止進行列運算
任何對列的操作都將導致表掃描，原因是對列進行運算將直接導致無法使用該列上的索引（除非建立了函數索引）。它包括數據庫函數、計算表達式等等。因此，查 詢時要盡可能將操作移至等號右邊。

select *

from blog

where blog_id=3;

觀察執行計劃，發現使用了索引blog.PK_blog,整個查詢消耗了cpu time：0.00008;I/O成本：0.0032。預計成本：0.0032。而如果改成：

select *

from blog

where cast(blog_id as char)=’3’;

――將blog_id轉換為char型后與’3’相比較。

觀察執行計劃，發現使用了索引blog.IX_plogs_blog_id（這里使用這個索引的原因是由于無法使用 blog.PK_blog，sqlserver退而求其次，使用了建立在plogs_blog_id字段上的索引）,整個查詢消耗了cpu time：0.264;I/O成本：0.345。預計成本：0.61。性能下降了200倍！

同樣，應將類似:where blog_id*1000>28847;的語句改為：where blog_id>28.847;

12、使用表提示
使用表提示的本質是影響優化器，使優化器按照指定的執行計劃來執行sql語句（通常是制定多表連接時的連接方式）。一般情況下并不需要使用表提示，因為優 化器總是能選擇適當的執行計劃。這種做法只是增加了性能優化的可能性，并不一定會產生好的影響。

由于使用表提示有時反而會降低執行效率，因此，只有當我們發現一些sql在低效運行且認為有必要干預sql執行計劃的時候（可能已經嚴重影響了應用 程序的運行）才使用。同時需要通過反復調試來達到最佳的效果。

使用表提示的方法：Oracle中使用hint提示，sqlserver中使用option子句。

13、慎用游標
在某些必須使用游標的場合，可考慮將符合條件的數據行轉入臨時表中，再對臨時表定義游標進行操作，這樣可使性能得到明顯提高。

14、關于多表連接和子查詢
在實際應用中我們常常會遇到需要使用多表連接或子查詢的情況。同時我們會發現，很多時候這兩種查詢結構可以做等量的轉換。下面的例子就是這樣： select ProductNamefrom Northwind.dbo.Productswhere UnitPrice in(select UnitPricefrom Northwind.dbo.Productswhere ProductName = ‘Sir Rodney’’s Scones’) select Prd1.ProductNamefrom Northwind.dbo.Products AS Prd1JOIN Northwind.dbo.Products AS Prd2  ON (Prd1.UnitPrice = Prd2.UnitPrice)where Prd2.ProductName = ‘Sir Rodney’’s Scones’

(T-sql。使用的是northwind示例數據庫中的表)

子查詢和不包括子查詢但語義上等效的語句在性能方面通常沒有區別。但是，在一些必須檢查存在性的情況中，使用聯接會產生更好的性能。否則，為確保消 除重復值，必須為外部查詢的每個結果都處理嵌套查詢。所以在這些情況下，聯接方式會產生更好的效果。如下面兩個等效的sql：

select distinct a.blog_name

from blog a

where exists(

select b.blog_id

from blog_note b

where a.blog_id=b.blog_id

And b.create_time<’2005-04-25’ );

select distinct a.blog_name

from blog a,blog_note b

where a.blog_id=b.blog_id  

And b.create_time<’2005-04-25’;

分析結果：

查詢執行計劃可以看到執行成本：語句一：使用exists子查詢，總成本0.0567語句二：聯接查詢，總成本0.0535。

15、增加執行計劃的重復使用率
執行計劃生成后便駐留在高速緩存中。很多時候一段sql代碼并不是執行一次就完了，同樣的代碼會連續執行很多次。如一個用戶查詢了自己的用戶資料，0.1 秒后另一個用戶執行了相同的操作。如果數據庫能識別出這兩次sql語句是同樣的，在第二次執行時就不會再次編譯sql，而是直接使用駐留在高速緩存中的執 行計劃。在某些大型系統中一些基礎的sql可能會每秒執行上千次。因此重復使用執行計劃可以大大縮短sql的執行時間。但是有的時候數據庫不能識別出類似 的sql語句，比如用戶查詢自己資料的例子：

第一個用戶：

select * from blog where blog_id=1;

第二個用戶查詢的是：

select * from blog where blog_id=455;

sql文本的變化導致數據庫認為這是兩個不同的查詢，無法重復使用執行計劃。為了提高執行計劃的重復使用率，建議：

1、  使用對象（如表和視圖）的完全合法名稱。如：

select * from blogctynnd.dbo.blog;

2、  oracle強烈建議在應用程序中大量使用“綁定變量”，如下：

將：select * from blog where blog_id=1；

改為：

SQL>declarevid int default 1;res varchar(4);beginexecute immediate ’select * from blog where blog_id =:x’ into res using vid;dbms_output.put_line(res);  –oracle系統包，用于輸出。end;/（PL/SQL。這一招的用意很明顯就是 固定sql文本，把編譯器騙過去，有點瞞天過海的意思。其他數據庫使用綁定變量的方法也是一樣的，只是使用的語句稍有不同。）

對于sqlserver來說使用綁定變量意義不大，因為sqlserver數據庫引擎可以識別出上面給出的例子，綁定變量具有一定的優勢，但并不明 顯。

另外需要說明的是在優化器內部對執行計劃會存在一個衰減列表，到一定時間后執行計劃還沒有被重復使用，就會被清除出這個列表，這時再執行sql語句 就必須重新編譯了。

其實SQL的性能優化是一個復雜的過程，上述這些只是在應用層次的一種體現，深入研究還會涉及數據庫層的資源配置、網絡層的流量控制以及操作系統層 的總體設計。另外，不同的數據庫使用的優化原則不同，導致對同樣的sql語句，做出完全不同的執行計劃。即使是同樣的數據庫、同樣的語句，在數據庫運行的 不同時期執行效率也會發生很大的變化。因此，對數據庫的優化更多的時候是對數據庫的觀察和調試，而不是教條式的簡單修改。有興趣的同事可以使用下面的方式 查看sql語句的執行計劃，而執行計劃反映了此刻sql語句的執行過程，使用的資源等等，是sql語句效率高低的量化數據。

oracle:

sql>@rdbms/admin/utlxplan.sql――運行utlxplan.sql腳本，會自動創建一個plan_table。 這步只需要做一次。建立后每次進入sqlplus時：

sql>set autotrace on

這時每次執行sql，都會顯示出相應的執行計劃。

Sqlserver:

在sqlserver中查看執行計劃非常簡單：進入查詢分析器，在窗體中輸入T-sql語句。點擊【查詢預計的執行計劃】（該按鈕在切換數據庫下拉 菜單的左邊。或ctrl＋l）。這時執行計劃就會顯示在窗體下端。

其他數據庫查看執行計劃的方式如有需要可以上網查找。歡迎多交流。

后記

對于現有的每一種數據庫來說，標準SQL的功能顯然太弱了。因此，每一種數據庫都對標準SQL進行了性能擴展。對于oracle是PL/SQL。 Sqlserver是T－sql。

在本文中列舉的例子，并不一定在你的數據庫中也能成功執行。同時，本文中的某些原則，可能對于特定數據庫、特定優化模式下的sql性能并不能起到明 顯改善的作用。

比如在使用通配符的例子中：

select * from employee where last_name like ‘%cliton%’;

對于oracle，將無法使用last_name上的索引，導致這個查詢會發生全表掃描。而對于sqlserver來說，仍然可以正常使用索引。

盡管如此，在開發過程中對一些小細節的注意，不僅可以保證在大多數數據庫中有較高的執行效率，還可以使sql語句在今后數據庫運行的過程中或某些特 殊情況下（如系統遷移），也能長期保持穩定的狀態。