正文

優(yōu)化數(shù)據(jù)庫查詢速度（優(yōu)化數(shù)據(jù)庫查詢速度快嗎）

發(fā)布時(shí)間：2023-04-08 14:51:32 稿源：創(chuàng)意嶺閱讀： 125

大家好！今天讓創(chuàng)意嶺的小編來大家介紹下關(guān)于優(yōu)化數(shù)據(jù)庫查詢速度的問題，以下是小編對(duì)此問題的歸納整理，讓我們一起來看看吧。

開始之前先推薦一個(gè)非常厲害的Ai人工智能工具，一鍵生成原創(chuàng)文章、方案、文案、工作計(jì)劃、工作報(bào)告、論文、代碼、作文、做題和對(duì)話答疑等等

只需要輸入關(guān)鍵詞，就能返回你想要的內(nèi)容，越精準(zhǔn)，寫出的就越詳細(xì)，有微信小程序端、在線網(wǎng)頁版、PC客戶端

官網(wǎng)：https://ai.de1919.com。

創(chuàng)意嶺作為行業(yè)內(nèi)優(yōu)秀的企業(yè)，服務(wù)客戶遍布全球各地，如需了解SEO相關(guān)業(yè)務(wù)請(qǐng)撥打電話175-8598-2043，或添加微信：1454722008

本文目錄:

1、如何提高上百萬級(jí)記錄MySQL數(shù)據(jù)庫查詢速度
2、數(shù)據(jù)庫的表不變，通過什么手段能提高查詢速度？
3、數(shù)據(jù)庫查詢性能優(yōu)化方式有哪些
4、怎么樣提高千萬級(jí)SQL數(shù)據(jù)庫查詢速度

優(yōu)化數(shù)據(jù)庫查詢速度（優(yōu)化數(shù)據(jù)庫查詢速度快嗎）

一、如何提高上百萬級(jí)記錄MySQL數(shù)據(jù)庫查詢速度

關(guān)于mysql處理百萬級(jí)以上的數(shù)據(jù)時(shí)如何提高其查詢速度的方法

最近一段時(shí)間由于工作需要，開始關(guān)注針對(duì)Mysql數(shù)據(jù)庫的select查詢語句的相關(guān)優(yōu)化方法。

由于在參與的實(shí)際項(xiàng)目中發(fā)現(xiàn)當(dāng)mysql表的數(shù)據(jù)量達(dá)到百萬級(jí)時(shí)，普通SQL查詢效率呈直線下降，而且如果where中的查詢條件較多時(shí)，其查詢速度簡(jiǎn)直無法容忍。曾經(jīng)測(cè)試對(duì)一個(gè)包含400多萬條記錄（有索引）的表執(zhí)行一條條件查詢，其查詢時(shí)間竟然高達(dá)40幾秒，相信這么高的查詢延時(shí)，任何用戶都會(huì)抓狂。因此如何提高sql語句查詢效率，顯得十分重要。以下是網(wǎng)上流傳比較廣泛的30種SQL查詢語句優(yōu)化方法：

1、應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。

2、對(duì)查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。

3、應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num is null

可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：

select id from t where num=0

4、盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10

union all

select id from t where num=20

5、下面的查詢也將導(dǎo)致全表掃描：(不能前置百分號(hào))

select id from t where name like ‘%c%’

若要提高效率，可以考慮全文檢索。

6、in 和 not in 也要慎用，否則會(huì)導(dǎo)致全表掃描，如：

select id from t where num in(1,2,3)

對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7、如果在 where 子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇。然而，如果在編譯時(shí)建立訪問計(jì)劃，變量的值還是未知的，因而無法作為索引選擇的輸入項(xiàng)。如下面語句將進(jìn)行全表掃描：

select id from t where num=@num

可以改為強(qiáng)制查詢使用索引：

select id from t with(index(索引名)) where num=@num

8、應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where num/2=100

應(yīng)改為:

select id from t where num=100*2

9、應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where substring(name,1,3)=’abc’–name以abc開頭的id

select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

應(yīng)改為:

select id from t where name like ‘a(chǎn)bc%’

select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′

10、不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引。

11、在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。

12、不要寫一些沒有意義的查詢，如需要生成一個(gè)空表結(jié)構(gòu)：

select col1,col2 into #t from t where 1=0

這類代碼不會(huì)返回任何結(jié)果集，但是會(huì)消耗系統(tǒng)資源的，應(yīng)改成這樣：

create table #t(…)

13、很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：

select num from a where num in(select num from b)

用下面的語句替換：

select num from a where exists(select 1 from b where num=a.num)

14、并不是所有索引對(duì)查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢可能不會(huì)去利用索引，如一表中有字段 sex，male、female幾乎各一半，那么即使在sex上建了索引也對(duì)查詢效率起不了作用。

15、索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過6個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要。

16.應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列，因?yàn)?clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ)順序，一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整，會(huì)耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為 clustered 索引。

17、盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì) 逐個(gè)比較字符串中每一個(gè)字符，而對(duì)于數(shù)字型而言只需要比較一次就夠了。

18、盡可能的使用 varchar/nvarchar 代替 char/nchar ，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對(duì)于查詢來說，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些。

19、任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

20、盡量使用表變量來代替臨時(shí)表。如果表變量包含大量數(shù)據(jù)，請(qǐng)注意索引非常有限（只有主鍵索引）。

21、避免頻繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)表資源的消耗。

22、臨時(shí)表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行В?，?dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)。但是，對(duì)于一次性事件，最好使用導(dǎo)出表。

23、在新建臨時(shí)表時(shí)，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert。

24、如果使用到了臨時(shí)表，在存儲(chǔ)過程的最后務(wù)必將所有的臨時(shí)表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定。

25、盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。

26、使用基于游標(biāo)的方法或臨時(shí)表方法之前，應(yīng)先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。

27、與臨時(shí)表一樣，游標(biāo)并不是不可使用。對(duì)小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時(shí) 間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。

28、在所有的存儲(chǔ)過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON ，在結(jié)束時(shí)設(shè)置 SET NOCOUNT OFF 。無需在執(zhí)行存儲(chǔ)過程和觸發(fā)器的每個(gè)語句后向客戶端發(fā)送 DONE_IN_PROC 消息。

29、盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理。

30、盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。

二、數(shù)據(jù)庫的表不變，通過什么手段能提高查詢速度？

分布式系統(tǒng)就是將系統(tǒng)的應(yīng)用層，數(shù)據(jù)層或其它部分構(gòu)架成分布（物理和邏輯上的都可以）狀（通常是網(wǎng)狀）。分布式系統(tǒng)通常是為了增強(qiáng)系統(tǒng)的可擴(kuò)展性、穩(wěn)定性和執(zhí)行效率。比如在線游戲通常就是分布系統(tǒng)，里面所謂的“區(qū)”就是分布系統(tǒng)里子例程。而分布式數(shù)據(jù)庫其實(shí)也可以稱作分布式系統(tǒng)，數(shù)據(jù)持久化層是分布的（數(shù)據(jù)存在不同的數(shù)據(jù)庫中，可交互，有一套綜管系統(tǒng)來維護(hù)數(shù)據(jù)的完整性和準(zhǔn)確性）。所以說分布式系統(tǒng)更準(zhǔn)確地說是一種系統(tǒng)構(gòu)架概念，不是一種技術(shù)。

提高查詢速度。

1、用程序中，

保證在實(shí)現(xiàn)功能的基礎(chǔ)上，盡量減少對(duì)數(shù)據(jù)庫的訪問次數(shù)；

通過搜索參數(shù)，盡量減少對(duì)表的訪問行數(shù)

最小化結(jié)果集，從而減輕網(wǎng)絡(luò)負(fù)擔(dān)；

夠分開的操作盡量分開處理，提高每次的響應(yīng)速度；

在數(shù)據(jù)窗口使用

SQL 時(shí)，盡量把使用的索引放在選擇的首列；

算法的結(jié)構(gòu)盡量簡(jiǎn)單；

在查詢時(shí)，不要過多地使用通配符如

SELECT * FROM T1 語句，要用到幾列就選擇幾列如：

SELECT COL1,COL2 FROM T1 ；

在可能的情況下盡量限制盡量結(jié)果集行數(shù)如：

SELECT TOP 300 COL1,COL2,COL3 FROM T1,

因?yàn)槟承┣闆r下用戶是不需要那么多的數(shù)據(jù)的。

不要在應(yīng)用中使用數(shù)據(jù)庫游標(biāo)，游標(biāo)是非常有用的工具，但比使用常規(guī)的、面向集的SQL語句需要更大的開銷；

按照特定順序提取數(shù)據(jù)的查找。

2、避免使用不兼容的數(shù)據(jù)類型。例如

float和int、char和varchar、binary和varbinary是不兼容的。數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行一些本來可以進(jìn)行的優(yōu)化操作。

例如:

SELECT name FROM employee WHERE salary ＞60000

在這條語句中,如salary字段是money型的,則優(yōu)化器很難對(duì)其進(jìn)行優(yōu)化,因?yàn)?0000是個(gè)整型數(shù)。

我們應(yīng)當(dāng)在編程時(shí)將整型轉(zhuǎn)化成為錢幣型,而不要等到運(yùn)行時(shí)轉(zhuǎn)化。

3、盡量避免在WHERE子句中對(duì)字段進(jìn)行函數(shù)或表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

SELECT * FROM T1 WHERE F1/2=100 應(yīng)改為

SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’

應(yīng)改為:

SELECT * FROM RECORD WHERE CARD_NO LIKE ‘5378%’

SELECT member_number, first_name, last_name

FROM members

WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21

應(yīng)改為:

SELECT member_number, first_name, last_name

FROM members

WHERE dateofbirth< DATEADD(yy,-21,GETDATE())

即：任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等號(hào)右邊。

4、避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等這樣的操作符因?yàn)檫@會(huì)使系統(tǒng)無法使用索引,

而只能直接搜索表中的數(shù)據(jù)。例如:

SELECT id FROM employee WHERE id != 'B%'

優(yōu)化器將無法通過索引來確定將要命中的行數(shù),因此需要搜索該表的所有行。

5、盡量使用數(shù)字型字段，一部分開發(fā)人員和數(shù)據(jù)庫管理人員喜歡把包含數(shù)值信息的字段設(shè)計(jì)為字符型，

這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接回逐個(gè)比較字符串中每一個(gè)字符，

而對(duì)于數(shù)字型而言只需要比較一次就夠了。

6、合理使用EXISTS,NOT EXISTS子句。如下所示：

1.SELECT SUM(T1.C1)FROM T1

WHERE( (SELECT COUNT(*)FROM T2 WHERE T2.C2=T1.C2>0)

2.SELECT SUM(T1.C1) FROM T1 WHERE EXISTS(

SELECT * FROM T2 WHERE T2.C2=T1.C2)

兩者產(chǎn)生相同的結(jié)果，但是后者的效率顯然要高于前者。因?yàn)楹笳卟粫?huì)產(chǎn)生大量鎖定的表掃描或

是索引掃描。

如果你想校驗(yàn)表里是否存在某條紀(jì)錄，不要用count(*)那樣效率很低，而且浪費(fèi)服務(wù)器資源。

可以用EXISTS代替。如：

IF (SELECT COUNT(*) FROM table_name WHERE column_name = 'xxx')

可以寫成：

IF EXISTS (SELECT * FROM table_name WHERE column_name = 'xxx')

經(jīng)常需要寫一個(gè)T_SQL語句比較一個(gè)父結(jié)果集和子結(jié)果集，

從而找到是否存在在父結(jié)果集中有而在子結(jié)果集中沒有的記錄，如：

1.SELECT a.hdr_key FROM hdr_tbl a

---- tbl a 表示 tbl 用別名a代替

WHERE NOT EXISTS (SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key)

2.SELECTa.hdr_key FROM hdr_tbl a LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key

WHERE b.hdr_key IS NULL

3.SELECT hdr_key FROM hdr_tbl WHERE hdr_key NOT IN (SELECT hdr_key FROM dtl_tbl)

三種寫法都可以得到同樣正確的結(jié)果，但是效率依次降低。

7、盡量避免在索引過的字符數(shù)據(jù)中，使用非打頭字母搜索。這也使得引擎無法利用索引。

見如下例子：SELECT * FROM T1 WHERE NAME LIKE ‘%L%’

SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’

SELECT * FROM T1 WHERE NAME LIKE ‘L%’

即使NAME字段建有索引，前兩個(gè)查詢依然無法利用索引完成加快操作，引擎不得不對(duì)全表所

有數(shù)據(jù)逐條操作來完成任務(wù)。而第三個(gè)查詢能夠使用索引來加快操作。

8、充分利用連接條件，在某種情況下，兩個(gè)表之間可能不只一個(gè)的連接條件，這時(shí)在

WHERE 子句中將連接條件完整的寫上，有可能大大提高查詢速度。例：

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO

AND A.ACCOUNT_NO=B.ACCOUNT_NO

第二句將比第一句執(zhí)行快得多。

9、消除對(duì)大型表行數(shù)據(jù)的順序存取，盡管在所有的檢查列上都有索引，但某些形式的

WHERE子句強(qiáng)迫優(yōu)化器使用順序存取。如：

SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR

order_num=1008 解決辦法可以使用并集來避免順序存?。?#160;

SELECT ＊FROM orders WHERE customer_num=104 AND order_num>1001

UNION SELECT ＊FROM orders WHERE order_num=1008 這樣就能利用索引路徑處理查詢。

10、避免困難的正規(guī)表達(dá)式。

LIKE關(guān)鍵字支持通配符匹配，技術(shù)

三、數(shù)據(jù)庫查詢性能優(yōu)化方式有哪些

1、1、調(diào)整數(shù)據(jù)結(jié)構(gòu)的設(shè)計(jì)。這一部分在開發(fā)信息系統(tǒng)之前完成，程序員需要考慮是否使用ORACLE數(shù)據(jù)庫的分區(qū)功能，對(duì)于經(jīng)常訪問的數(shù)據(jù)庫表是否需要建立索引等。

2、2、調(diào)整應(yīng)用程序結(jié)構(gòu)設(shè)計(jì)。這一部分也是在開發(fā)信息系統(tǒng)之前完成，程序員在這一步需要考慮應(yīng)用程序使用什么樣的體系結(jié)構(gòu)，是使用傳統(tǒng)的Client/Server兩層體系結(jié)構(gòu)，還是使用Browser/Web/Database的三層體系結(jié)構(gòu)。不同的應(yīng)用程序體系結(jié)構(gòu)要求的數(shù)據(jù)庫資源是不同的。

3、3、調(diào)整數(shù)據(jù)庫SQL語句。應(yīng)用程序的執(zhí)行最終將歸結(jié)為數(shù)據(jù)庫中的SQL語句執(zhí)行，因此SQL語句的執(zhí)行效率最終決定了ORACLE數(shù)據(jù)庫的性能。ORACLE公司推薦使用ORACLE語句優(yōu)化器（Oracle Optimizer）和行鎖管理器（row-level manager）來調(diào)整優(yōu)化SQL語句。

4、4、調(diào)整服務(wù)器內(nèi)存分配。內(nèi)存分配是在信息系統(tǒng)運(yùn)行過程中優(yōu)化配置的，數(shù)據(jù)庫管理員可以根據(jù)數(shù)據(jù)庫運(yùn)行狀況調(diào)整數(shù)據(jù)庫系統(tǒng)全局區(qū)（SGA區(qū)）的數(shù)據(jù)緩沖區(qū)、日志緩沖區(qū)和共享池的大??；還可以調(diào)整程序全局區(qū)（PGA區(qū)）的大小。需要注意的是，SGA區(qū)不是越大越好，SGA區(qū)過大會(huì)占用操作系統(tǒng)使用的內(nèi)存而引起虛擬內(nèi)存的頁面交換，這樣反而會(huì)降低系統(tǒng)。

5、5、調(diào)整硬盤I/O，這一步是在信息系統(tǒng)開發(fā)之前完成的。數(shù)據(jù)庫管理員可以將組成同一個(gè)表空間的數(shù)據(jù)文件放在不同的硬盤上，做到硬盤之間I/O負(fù)載均衡。

6、6、調(diào)整操作系統(tǒng)參數(shù)，例如：運(yùn)行在UNIX操作系統(tǒng)上的ORACLE數(shù)據(jù)庫，可以調(diào)整UNIX數(shù)據(jù)緩沖池的大小，每個(gè)進(jìn)程所能使用的內(nèi)存大小等參數(shù)。

實(shí)際上，上述數(shù)據(jù)庫優(yōu)化措施之間是相互聯(lián)系的。ORACLE數(shù)據(jù)庫性能惡化表現(xiàn)基本上都是用戶響應(yīng)時(shí)間比較長(zhǎng)，需要用戶長(zhǎng)時(shí)間的等待。但性能惡化的原因卻是多種多樣的，有時(shí)是多個(gè)因素共同造成了性能惡化的結(jié)果，這就需要數(shù)據(jù)庫管理員有比較全面的計(jì)算機(jī)知識(shí)，能夠敏感地察覺到影響數(shù)據(jù)庫性能的主要原因所在。另外，良好的數(shù)據(jù)庫管理工具對(duì)于優(yōu)化數(shù)據(jù)庫性能也是很重要的。

ORACLE數(shù)據(jù)庫性能優(yōu)化工具

常用的數(shù)據(jù)庫性能優(yōu)化工具有：

1、1、ORACLE數(shù)據(jù)庫在線數(shù)據(jù)字典，ORACLE在線數(shù)據(jù)字典能夠反映出ORACLE動(dòng)態(tài)運(yùn)行情況，對(duì)于調(diào)整數(shù)據(jù)庫性能是很有幫助的。

2、2、操作系統(tǒng)工具，例如UNIX操作系統(tǒng)的vmstat，iostat等命令可以查看到系統(tǒng)系統(tǒng)級(jí)內(nèi)存和硬盤I/O的使用情況，這些工具對(duì)于管理員弄清出系統(tǒng)瓶頸出現(xiàn)在什么地方有時(shí)候很有用。

3、3、SQL語言跟蹤工具（SQL TRACE FACILITY），SQL語言跟蹤工具可以記錄SQL語句的執(zhí)行情況，管理員可以使用虛擬表來調(diào)整實(shí)例，使用SQL語句跟蹤文件調(diào)整應(yīng)用程序性能。SQL語言跟蹤工具將結(jié)果輸出成一個(gè)操作系統(tǒng)的文件，管理員可以使用TKPROF工具查看這些文件。

4、4、ORACLE Enterprise Manager（OEM），這是一個(gè)圖形的用戶管理界面，用戶可以使用它方便地進(jìn)行數(shù)據(jù)庫管理而不必記住復(fù)雜的ORACLE數(shù)據(jù)庫管理的命令。

5、5、EXPLAIN PLAN——SQL語言優(yōu)化命令，使用這個(gè)命令可以幫助程序員寫出高效的SQL語言。

ORACLE數(shù)據(jù)庫的系統(tǒng)性能評(píng)估

信息系統(tǒng)的類型不同，需要關(guān)注的數(shù)據(jù)庫參數(shù)也是不同的。數(shù)據(jù)庫管理員需要根據(jù)自己的信息系統(tǒng)的類型著重考慮不同的數(shù)據(jù)庫參數(shù)。

1、1、在線事務(wù)處理信息系統(tǒng)（OLTP），這種類型的信息系統(tǒng)一般需要有大量的Insert、Update操作，典型的系統(tǒng)包括民航機(jī)票發(fā)售系統(tǒng)、銀行儲(chǔ)蓄系統(tǒng)等。OLTP系統(tǒng)需要保證數(shù)據(jù)庫的并發(fā)性、可靠性和最終用戶的速度，這類系統(tǒng)使用的ORACLE數(shù)據(jù)庫需要主要考慮下述參數(shù)：

l l 數(shù)據(jù)庫回滾段是否足夠？

l l 是否需要建立ORACLE數(shù)據(jù)庫索引、聚集、散列？

l l 系統(tǒng)全局區(qū)（SGA）大小是否足夠？

l l SQL語句是否高效？

2、2、數(shù)據(jù)倉(cāng)庫系統(tǒng)（Data Warehousing），這種信息系統(tǒng)的主要任務(wù)是從ORACLE的海量數(shù)據(jù)中進(jìn)行查詢，得到數(shù)據(jù)之間的某些規(guī)律。數(shù)據(jù)庫管理員需要為這種類型的ORACLE數(shù)據(jù)庫著重考慮下述參數(shù)：

l l 是否采用B*-索引或者bitmap索引？

l l 是否采用并行SQL查詢以提高查詢效率？

l l 是否采用PL/SQL函數(shù)編寫存儲(chǔ)過程？

l l 有必要的話，需要建立并行數(shù)據(jù)庫提高數(shù)據(jù)庫的查詢效率

SQL語句的調(diào)整原則

SQL語言是一種靈活的語言，相同的功能可以使用不同的語句來實(shí)現(xiàn)，但是語句的執(zhí)行效率是很不相同的。程序員可以使用EXPLAIN PLAN語句來比較各種實(shí)現(xiàn)方案，并選出最優(yōu)的實(shí)現(xiàn)方案。總得來講，程序員寫SQL語句需要滿足考慮如下規(guī)則：

1、1、盡量使用索引。試比較下面兩條SQL語句：

語句A：SELECT dname, deptno FROM dept WHERE deptno NOT IN

(SELECT deptno FROM emp);

語句B：SELECT dname, deptno FROM dept WHERE NOT EXISTS

(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);

這兩條查詢語句實(shí)現(xiàn)的結(jié)果是相同的，但是執(zhí)行語句A的時(shí)候，ORACLE會(huì)對(duì)整個(gè)emp表進(jìn)行掃描，沒有使用建立在emp表上的deptno索引，執(zhí)行語句B的時(shí)候，由于在子查詢中使用了聯(lián)合查詢，ORACLE只是對(duì)emp表進(jìn)行的部分?jǐn)?shù)據(jù)掃描，并利用了deptno列的索引，所以語句B的效率要比語句A的效率高一些。

2、2、選擇聯(lián)合查詢的聯(lián)合次序?？紤]下面的例子：

SELECT stuff FROM taba a, tabb b, tabc c

WHERE a.acol between :alow and :ahigh

AND b.bcol between :blow and :bhigh

AND c.ccol between :clow and :chigh

AND a.key1 = b.key1

AMD a.key2 = c.key2;

這個(gè)SQL例子中，程序員首先需要選擇要查詢的主表，因?yàn)橹鞅硪M(jìn)行整個(gè)表數(shù)據(jù)的掃描，所以主表應(yīng)該數(shù)據(jù)量最小，所以例子中表A的acol列的范圍應(yīng)該比表B和表C相應(yīng)列的范圍小。

3、3、在子查詢中慎重使用IN或者NOT IN語句，使用where (NOT) exists的效果要好的多。

4、4、慎重使用視圖的聯(lián)合查詢，尤其是比較復(fù)雜的視圖之間的聯(lián)合查詢。一般對(duì)視圖的查詢最好都分解為對(duì)數(shù)據(jù)表的直接查詢效果要好一些。

5、5、可以在參數(shù)文件中設(shè)置SHARED_POOL_RESERVED_SIZE參數(shù)，這個(gè)參數(shù)在SGA共享池中保留一個(gè)連續(xù)的內(nèi)存空間，連續(xù)的內(nèi)存空間有益于存放大的SQL程序包。

6、6、ORACLE公司提供的DBMS_SHARED_POOL程序可以幫助程序員將某些經(jīng)常使用的存儲(chǔ)過程“釘”在SQL區(qū)中而不被換出內(nèi)存，程序員對(duì)于經(jīng)常使用并且占用內(nèi)存很多的存儲(chǔ)過程“釘”到內(nèi)存中有利于提高最終用戶的響應(yīng)時(shí)間。

CPU參數(shù)的調(diào)整

CPU是服務(wù)器的一項(xiàng)重要資源，服務(wù)器良好的工作狀態(tài)是在工作高峰時(shí)CPU的使用率在90％以上。如果空閑時(shí)間CPU使用率就在90％以上，說明服務(wù)器缺乏CPU資源，如果工作高峰時(shí)CPU使用率仍然很低，說明服務(wù)器CPU資源還比較富余。

使用操作相同命令可以看到CPU的使用情況，一般UNIX操作系統(tǒng)的服務(wù)器，可以使用sar –u命令查看CPU的使用率，NT操作系統(tǒng)的服務(wù)器，可以使用NT的性能管理器來查看CPU的使用率。

數(shù)據(jù)庫管理員可以通過查看v$sysstat數(shù)據(jù)字典中“CPU used by this session”統(tǒng)計(jì)項(xiàng)得知ORACLE數(shù)據(jù)庫使用的CPU時(shí)間，查看“OS User level CPU time”統(tǒng)計(jì)項(xiàng)得知操作系統(tǒng)用戶態(tài)下的CPU時(shí)間，查看“OS System call CPU time”統(tǒng)計(jì)項(xiàng)得知操作系統(tǒng)系統(tǒng)態(tài)下的CPU時(shí)間，操作系統(tǒng)總的CPU時(shí)間就是用戶態(tài)和系統(tǒng)態(tài)時(shí)間之和，如果ORACLE數(shù)據(jù)庫使用的CPU時(shí)間占操作系統(tǒng)總的CPU時(shí)間90％以上，說明服務(wù)器CPU基本上被ORACLE數(shù)據(jù)庫使用著，這是合理，反之，說明服務(wù)器CPU被其它程序占用過多，ORACLE數(shù)據(jù)庫無法得到更多的CPU時(shí)間。

數(shù)據(jù)庫管理員還可以通過查看v$sesstat數(shù)據(jù)字典來獲得當(dāng)前連接ORACLE數(shù)據(jù)庫各個(gè)會(huì)話占用的CPU時(shí)間，從而得知什么會(huì)話耗用服務(wù)器CPU比較多。

出現(xiàn)CPU資源不足的情況是很多的：SQL語句的重解析、低效率的SQL語句、鎖沖突都會(huì)引起CPU資源不足。

1、數(shù)據(jù)庫管理員可以執(zhí)行下述語句來查看SQL語句的解析情況：

SELECT * FROM V$SYSSTAT

WHERE NAME IN

('parse time cpu', 'parse time elapsed', 'parse count (hard)');

這里parse time cpu是系統(tǒng)服務(wù)時(shí)間，parse time elapsed是響應(yīng)時(shí)間，用戶等待時(shí)間

waite time = parse time elapsed – parse time cpu

由此可以得到用戶SQL語句平均解析等待時(shí)間＝waite time / parse count。這個(gè)平均等待時(shí)間應(yīng)該接近于0，如果平均解析等待時(shí)間過長(zhǎng)，數(shù)據(jù)庫管理員可以通過下述語句

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA

ORDER BY PARSE_CALLS;

來發(fā)現(xiàn)是什么SQL語句解析效率比較低。程序員可以優(yōu)化這些語句，或者增加ORACLE參數(shù)SESSION_CACHED_CURSORS的值。

2、數(shù)據(jù)庫管理員還可以通過下述語句：

SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;

查看低效率的SQL語句，優(yōu)化這些語句也有助于提高CPU的利用率。

3、3、數(shù)據(jù)庫管理員可以通過v$system_event數(shù)據(jù)字典中的“l(fā)atch free”統(tǒng)計(jì)項(xiàng)查看ORACLE數(shù)據(jù)庫的沖突情況，如果沒有沖突的話，latch free查詢出來沒有結(jié)果。如果沖突太大的話，數(shù)據(jù)庫管理員可以降低spin_count參數(shù)值，來消除高的CPU使用率。

內(nèi)存參數(shù)的調(diào)整

內(nèi)存參數(shù)的調(diào)整主要是指ORACLE數(shù)據(jù)庫的系統(tǒng)全局區(qū)（SGA）的調(diào)整。SGA主要由三部分構(gòu)成：共享池、數(shù)據(jù)緩沖區(qū)、日志緩沖區(qū)。

1、 1、共享池由兩部分構(gòu)成：共享SQL區(qū)和數(shù)據(jù)字典緩沖區(qū)，共享SQL區(qū)是存放用戶SQL命令的區(qū)域，數(shù)據(jù)字典緩沖區(qū)存放數(shù)據(jù)庫運(yùn)行的動(dòng)態(tài)信息。數(shù)據(jù)庫管理員通過執(zhí)行下述語句：

select (sum(pins - reloads)) / sum(pins) "Lib Cache" from v$librarycache;

來查看共享SQL區(qū)的使用率。這個(gè)使用率應(yīng)該在90％以上，否則需要增加共享池的大小。數(shù)據(jù)庫管理員還可以執(zhí)行下述語句：

select (sum(gets - getmisses - usage - fixed)) / sum(gets) "Row Cache" from v$rowcache;

查看數(shù)據(jù)字典緩沖區(qū)的使用率，這個(gè)使用率也應(yīng)該在90％以上，否則需要增加共享池的大小。

2、 2、數(shù)據(jù)緩沖區(qū)。數(shù)據(jù)庫管理員可以通過下述語句：

SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets','physical reads');

來查看數(shù)據(jù)庫數(shù)據(jù)緩沖區(qū)的使用情況。查詢出來的結(jié)果可以計(jì)算出來數(shù)據(jù)緩沖區(qū)的使用命中率＝1 - ( physical reads / (db block gets + consistent gets) )。

這個(gè)命中率應(yīng)該在90％以上，否則需要增加數(shù)據(jù)緩沖區(qū)的大小。

3、 3、日志緩沖區(qū)。數(shù)據(jù)庫管理員可以通過執(zhí)行下述語句：

select name,value from v$sysstat where name in ('redo entries','redo log space requests');查看日志緩沖區(qū)的使用情況。查詢出的結(jié)果可以計(jì)算出日志緩沖區(qū)的申請(qǐng)失敗率：

申請(qǐng)失敗率＝requests/entries，申請(qǐng)失敗率應(yīng)該接近于0，否則說明日志緩沖區(qū)開設(shè)太小，需要增加ORACLE數(shù)據(jù)庫的日志緩沖區(qū)。

四、怎么樣提高千萬級(jí)SQL數(shù)據(jù)庫查詢速度