資料庫索引使用

① 資料庫基礎：講解Mysql索引的概念及資料庫索引的應用[2]

五 索引分類

直接創建索引和間接創建索引

直接創建索引 CREATE INDEX mycolumn_index ON mytable (myclumn)

間接創建索引 定義主鍵約束或者唯一性鍵約束 可以間接創建索引

普通索引和唯一性索引

普通索引 CREATE INDEX mycolumn_index ON mytable (myclumn)

唯一性索引 保證在索引列中的全部數據是唯一的 對聚簇索引和非聚簇索引都可以使用

CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

單個索引和復合索引

單個索引 即非復合索引

復合索引 又叫組合索引 在索引建立語句中同時包含多個欄位名 最多 個欄位

CREATE INDEX name_index ON username(firstname lastname)

聚簇索引和非聚簇索引(聚集索引 群集索引)

聚簇索引 物理索引 與基表的物理順序相同 數據值的順序總是按照順序排列

CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH

ALLOW_DUP_ROW(允許有重復記錄的聚簇索引)

非聚簇索引 CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

六 索引的使用

當欄位數據更新頻率較低 查詢使用頻率較高並且存在大量重復值是建議備裂使用聚簇索引

經常同時存取多列 且每列都含有重復值可考慮建立組合索引

復合索引的前導列一定好控制好 否則無法起到索引的效果 如果查詢時前導列不在查詢條件中則該復合索引不會被使用 前導列一定是使用最頻繁的列

多表操作在被實際執行前 查詢優化器會根據連接條件 列出幾組可能的連接方案並從中找出系統開銷最小的最佳方案 連接條件要充份考慮帶有索引的表 行數多的表;內外表的選擇可由公式 外層表中的匹配行數*內層表中每一次查找的次數確定 乘積最小為最佳方案

where子句中對列的任何操作結果都是在sql運行時逐列計算得到的 因此它不得不進行表搜索 而沒有使用該列上面的索引;如果這些結果在查詢編譯時就能得到 那麼就可以被sql優化器優化 使用索引 避免表搜索(例 select * from record where substring(card_no )=

&& select * from record where card_no like % )任何對列的操作都將導致表掃描 它包括資料庫函數 計算表達式等等 查詢時要盡可能將操困彎作移至等號右邊

where條件中的 in 在邏輯上相當於 or 所以語法分析器會將in ( ′ ′)轉化為column= ′ or column= ′來執行 我們期望它會根據每個or子句分別查找 再將結果相加 這樣仿尺閉可以利用column上的索引;但實際上它卻採用了 or策略 即先取出滿足每個or子句的行 存入臨時資料庫的工作表中 再建立唯一索引以去掉重復行 最後從這個臨時表中計算結果 因此 實際過程沒有利用column上索引 並且完成時間還要受tempdb資料庫性能的影響 in or子句常會使用工作表 使索引失效;如果不產生大量重復值 可以考慮把子句拆開;拆開的子句中應該包含索引

要善於使用存儲過程 它使sql變得更加靈活和高效

lishixin/Article/program/MySQL/201311/29603

② 資料庫建立索引怎麼利用索引查詢

1.合理使用索引x0dx0a索引是資料庫中重要的數據結構，它的根本目的就是為了提高查詢效率。現在大多數的資料庫產品都採用IBM最先提出的ISAM索引結構。x0dx0a索喚漏引的使用要恰到好處，其使用原則如下：x0dx0a在經常進行連接，但是沒有指定為外鍵的列上建立索引，而不經常連接的欄位則由優化器自動生成索引。x0dx0a在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引。x0dx0a在條件表達式中經常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引。比如在雇員表的「性別」列上只有「男」與「女」兩個不同值，因此就無必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度。x0dx0a如果待排序的列有多個，可以在這些列上建立復合索引（compound index）。x0dx0a使用系統工具。如Informix資料庫有一個tbcheck工具，可以在可疑的索引上進行檢查。在一些資料庫伺服器上，索引可能失效或者因為頻繁操作而 使得讀取效率降低，如果一個使用索引的查詢不明不白地慢下來，可以試著用tbcheck工具檢查索引的完整性，必要時進行修復。另外，當資料庫表更新大量 數據後，刪除並重建索引可以提高查詢速度。x0dx0a(1)在下面兩條select語句中:x0dx0aSELECT * FROM table1 WHERE field1<=10000 AND field1>=0; x0dx0aSELECT * FROM table1 WHERE field1>=0 AND field1<=10000;x0dx0a如果數據表中的數據field1都>=0,則第一條select語句要比第二條select語句效率高的多，因為第二條select語句的第一個條件耗費了大量的系統資源。x0dx0a第一個原則：在where子句中應把最具限制性的條件放在最前面。x0dx0a(2)在下面的select語句中:x0dx0aSELECT * FROM tab WHERE a=? AND b=? AND c=?;x0dx0a若有索引index(a,b,c)，則where子句中欄位的順序應和索引中欄位順序一致。x0dx0a第二個原則：where子句中欄位的順序應和索引中欄位順序一致。x0dx0a—————————————————————————— x0dx0a以下假設在field1上有唯一索引I1，在field2上有非唯一索引I2。 x0dx0a—————————————喊鏈段————————————— x0dx0a(3) SELECT field3,field4 FROM tb WHERE field1='sdf' 快 x0dx0aSELECT * FROM tb WHERE field1='sdf' 慢[/cci]x0dx0a因為後者在索引掃描後要多一步ROWID表訪問。x0dx0a(4) SELECT field3,field4 FROM tb WHERE field1>='sdf' 快 x0dx0aSELECT field3,field4 FROM tb WHERE field1>'sdf' 慢x0dx0a因為前者可以迅速定位索引。x0dx0a(5) SELECT field3,field4 FROM tb WHERE field2 LIKE 'R%' 快 x0dx0aSELECT field3,field4 FROM tb WHERE field2 LIKE '%R' 慢，x0dx0a因為後者不使用索引。x0dx0a(6) 使用函鄭譽數如： x0dx0aSELECT field3,field4 FROM tb WHERE upper(field2)='RMN'不使用索引。x0dx0a如果一個表有兩萬條記錄，建議不使用函數；如果一個表有五萬條以上記錄，嚴格禁止使用函數！兩萬條記錄以下沒有限制。x0dx0a(7) 空值不在索引中存儲，所以 x0dx0aSELECT field3,field4 FROM tb WHERE field2 IS[NOT] NULL不使用索引。x0dx0a(8) 不等式如 x0dx0aSELECT field3,field4 FROM tb WHERE field2!='TOM'不使用索引。 x0dx0a相似地， x0dx0aSELECT field3,field4 FROM tb WHERE field2 NOT IN('M','P')不使用索引。x0dx0a(9) 多列索引，只有當查詢中索引首列被用於條件時，索引才能被使用。x0dx0a(10) MAX，MIN等函數，使用索引。 x0dx0aSELECT max(field2) FROM tb 所以，如果需要對欄位取max，min，sum等，應該加索引。x0dx0a一次只使用一個聚集函數，如： x0dx0aSELECT 「min」=min(field1), 「max」=max(field1) FROM tb x0dx0a不如：SELECT 「min」=(SELECT min(field1) FROM tb) , 「max」=(SELECT max(field1) FROM tb)x0dx0a(11) 重復值過多的索引不會被查詢優化器使用。而且因為建了索引，修改該欄位值時還要修改索引，所以更新該欄位的操作比沒有索引更慢。x0dx0a(12) 索引值過大（如在一個char(40)的欄位上建索引），會造成大量的I/O開銷（甚至會超過表掃描的I/O開銷）。因此，盡量使用整數索引。 Sp_estspace可以計算表和索引的開銷。x0dx0a(13) 對於多列索引，ORDER BY的順序必須和索引的欄位順序一致。x0dx0a(14) 在sybase中，如果ORDER BY的欄位組成一個簇索引，那麼無須做ORDER BY。記錄的排列順序是與簇索引一致的。x0dx0a(15) 多表聯結（具體查詢方案需要通過測試得到） x0dx0awhere子句中限定條件盡量使用相關聯的欄位，且盡量把相關聯的欄位放在前面。 x0dx0aSELECT a.field1,b.field2 FROM a,b WHERE a.field3=b.field3x0dx0afield3上沒有索引的情況下: x0dx0a對a作全表掃描，結果排序 x0dx0a對b作全表掃描，結果排序 x0dx0a結果合並。 x0dx0a對於很小的表或巨大的表比較合適。x0dx0afield3上有索引 x0dx0a按照表聯結的次序，b為驅動表，a為被驅動表 x0dx0a對b作全表掃描 x0dx0a對a作索引范圍掃描 x0dx0a如果匹配，通過a的rowid訪問x0dx0a(16) 避免一對多的join。如： x0dx0aSELECT tb1.field3,tb1.field4,tb2.field2 FROM tb1,tb2 WHERE tb1.field2=tb2.field2 AND tb1.field2=『BU1032』 AND tb2.field2= 『aaa』 x0dx0a不如： x0dx0adeclare @a varchar(80) x0dx0aSELECT @a=field2 FROM tb2 WHERE field2=『aaa』 x0dx0aSELECT tb1.field3,tb1.field4,@a FROM tb1 WHERE field2= 『aaa』x0dx0a(16) 子查詢 x0dx0a用exists/not exists代替in/not in操作 x0dx0a比較： x0dx0aSELECT a.field1 FROM a WHERE a.field2 IN(SELECT b.field1 FROM b WHERE b.field2=100) x0dx0aSELECT a.field1 FROM a WHERE EXISTS( SELECT 1 FROM b WHERE a.field2=b.field1 AND b.field2=100) x0dx0aSELECT field1 FROM a WHERE field1 NOT IN( SELECT field2 FROM b) x0dx0aSELECT field1 FROM a WHERE NOT EXISTS( SELECT 1 FROM b WHERE b.field2=a.field1)x0dx0a(17) 主、外鍵主要用於數據約束，sybase中創建主鍵時會自動創建索引，外鍵與索引無關，提高性能必須再建索引。x0dx0a(18) char類型的欄位不建索引比int類型的欄位不建索引更糟糕。建索引後性能只稍差一點。x0dx0a(19) 使用count(*)而不要使用count(column_name)，避免使用count(DISTINCT column_name)。x0dx0a(20) 等號右邊盡量不要使用欄位名，如： x0dx0aSELECT * FROM tb WHERE field1 = field3x0dx0a(21) 避免使用or條件，因為or不使用索引。x0dx0a2.避免使用order by和group by字句。x0dx0a因為使用這兩個子句會佔用大量的臨時空間(tempspace),如果一定要使用，可用視圖、人工生成臨時表的方法來代替。 x0dx0a如果必須使用，先檢查memory、tempdb的大小。 x0dx0a測試證明，特別要避免一個查詢里既使用join又使用group by，速度會非常慢！x0dx0a3.盡量少用子查詢，特別是相關子查詢。因為這樣會導致效率下降。x0dx0a一個列的標簽同時在主查詢和where子句中的查詢中出現，那麼很可能當主查詢中的列值改變之後，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應當盡量避免子查詢。如果子查詢不可避免，那麼要在子查詢中過濾掉盡可能多的行。x0dx0a4．消除對大型錶行數據的順序存取x0dx0a在 嵌套查詢中，對表的順序存取對查詢效率可能產生致命的影響。 x0dx0a比如採用順序存取策略，一個嵌套3層的查詢，如果每層都查詢1000行，那麼這個查詢就要查詢 10億行數據。 x0dx0a避免這種情況的主要方法就是對連接的列進行索引。 x0dx0a例如，兩個表：學生表（學號、姓名、年齡??）和選課表（學號、課程號、成績）。如果兩個 表要做連接，就要在「學號」這個連接欄位上建立索引。 x0dx0a還可以使用並集來避免順序存取。盡管在所有的檢查列上都有索引，但某些形式的where子句強迫優化器使用順序存取。 x0dx0a下面的查詢將強迫對orders表執行順序操作： x0dx0aSELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008 x0dx0a雖然在customer_num和order_num上建有索引，但是在上面的語句中優化器還是使用順序存取路徑掃描整個表。因為這個語句要檢索的是分離的行的集合，所以應該改為如下語句： x0dx0aSELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001 x0dx0aUNION x0dx0aSELECT ＊ FROM orders WHERE order_num=1008 x0dx0a這樣就能利用索引路徑處理查詢。x0dx0a5．避免困難的正規表達式x0dx0aMATCHES和LIKE關鍵字支持通配符匹配，技術上叫正規表達式。但這種匹配特別耗費時間。例如：SELECT ＊ FROM customer WHERE zipcode LIKE 「98_ _ _」 x0dx0a即使在zipcode欄位上建立了索引，在這種情況下也還是採用順序掃描的方式。如果把語句改為SELECT ＊ FROM customer WHERE zipcode >「98000」，在執行查詢時就會利用索引來查詢，顯然會大大提高速度。 x0dx0a另外，還要避免非開始的子串。例如語句：SELECT ＊ FROM customer WHERE zipcode[2，3] >「80」，在where子句中採用了非開始子串，因而這個語句也不會使用索引。x0dx0a6．使用臨時表加速查詢x0dx0a把表的一個子集進行排序並創建臨時表，有時能加速查詢。它有助於避免多重排序操作，而且在其他方面還能簡化優化器的工作。例如： x0dx0aSELECT cust.name，rcvbles.balance，??other COLUMNS x0dx0aFROM cust，rcvbles x0dx0aWHERE cust.customer_id = rcvlbes.customer_id x0dx0aAND rcvblls.balance>0 x0dx0aAND cust.postcode>「98000」 x0dx0aORDER BY cust.name x0dx0a如果這個查詢要被執行多次而不止一次，可以把所有未付款的客戶找出來放在一個臨時文件中，並按客戶的名字進行排序： x0dx0aSELECT cust.name，rcvbles.balance，??other COLUMNS x0dx0aFROM cust，rcvbles x0dx0aWHERE cust.customer_id = rcvlbes.customer_id x0dx0aAND rcvblls.balance>;0 x0dx0aORDER BY cust.name x0dx0aINTO TEMP cust_with_balance x0dx0a然後以下面的方式在臨時表中查詢： x0dx0aSELECT ＊ FROM cust_with_balance x0dx0aWHERE postcode>「98000」 x0dx0a臨時表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁碟I/O，所以查詢工作量可以得到大幅減少。 x0dx0a注意：臨時表創建後不會反映主表的修改。在主表中數據頻繁修改的情況下，注意不要丟失數據。x0dx0a7．用排序來取代非順序存取x0dx0a非順序磁碟存取是最慢的操作，表現在磁碟存取臂的來回移動。SQL語句隱藏了這一情況，使得我們在寫應用程序時很容易寫出要求存取大量非順序頁的查詢。

③ 在SQLServer中使用索引的技巧

在SQL Server中 為了查詢性能的優化 有時我們就需要對數據表通過建立索引的方式 目的主要是根據查詢要求 迅速縮小查詢范圍 避免全表掃描

索引有兩種類型 分別是聚集索引(clustered index 也稱聚類索引 簇集索引)和非聚集索引(nonclustered index 也稱非聚類索引 非簇集索引)

聚集索引在一個表中只能有一個 默認情況下在主鍵建立的時候創建 它是規定數據在表中的物理存儲順序 我們也可以取消主鍵的聚集索引 所以必須考慮資料庫可能用到的查詢類型以及使用的最為頻繁的查詢類型 對其最常用的一個欄位或者多個欄位建立聚集索引或者組合的聚集索引 它就是SQL Server會在物理上按升序(默認)或者降序重排數據列 這樣就可以迅速的找到被查詢的數據

非聚集索主要是數據存儲在一個地方 索引存儲在另一個地方 索引帶有指針指向數據的存儲位置 索引中的項目按索引鍵值的順序存儲 而表中的信息按另一種順序存儲 可以在一個表格中使用高達 個非聚集的索引 在查詢的過程中先對非聚集索引進行搜索 找到數據值在表中的位置 然後從該位置直接檢索數據 這使非聚集索引成為精確匹配查詢的最佳方法 因為索引包含描述查詢所搜索的數據值在表中的精確位置的條目

所以我們在選擇創建聚集索引的時候要注意以下幾個方面

) 對表建立主鍵時 就會為主鍵自動添加了聚集索引 如自動編號欄位 而我們沒有必要把聚集索引浪費在主鍵上 除非你只按主鍵查詢 所以會把聚集索引設置在按條件查詢頻率最高的那個欄位或者組合的欄位

) 索引的建立要根據實際應用的需求來進行 並非是在任何欄位上建立索引就能提高查詢速度 聚集索引建立遵循下面幾個原則

包含大量非重復值的列

使用下列運算符返回一個范圍值的查詢 BEEEN > >= < 和 <=

被連續訪問的列

返回大型結果集的查詢

經常被使用聯接或 GROUP BY 子句的查詢訪問的列;一般來說 這些是外鍵列 對ORDER BY 或 GROUP BY 子句中指定的列進行索引 可以使 SQL Server 不必對數據進行排序 因為這些行已經排序 這樣可以提高查詢性能

OLTP 類型的應用程序 這些程序要求進行非常快速的單行查找(一般通過主鍵) 應在主鍵上創建聚集索引

舉例來說 銀行交易日誌中對交易日期建立聚合索引 數據物理上按順序存於數據頁上 重復值也排列在一起 因而在范圍查找時 可以先找到這個范圍的起末點 且只在這個范圍內掃描數據頁 避免了大范圍掃描 提高了查詢速度 而如果我們對員工的基本信息表中性別的欄位列上建立聚集索引 就完全沒有必要 因為內容里只涉及到 男 與 女 兩個不同值

) 在聚集索引中按常用的組合欄位建立索引 形成復合索引 一般在為表建立多個主鍵的時候就會產生 如果一個表中的數據在查詢時有多個欄位總是同時出現則這些欄位就可以作為復合索引 這樣能形成索引覆蓋 提高where語句的查詢效率

)索引對查詢有一這的優化 但由於改變一個表的內容 將會引起索引的變化 頻繁的對數據操作如insert update delete語句將導致系統花費較大的代價進行索引更新 引起整體性能的下降 一般來講 在對查詢性能的要求高於對數據維護性能要求時 應該盡量使用索引 有時在這種操作資料庫比較頻繁的某些極端情況下 可先刪除索引 再對資料庫表更新大量數據 最後再重建索引 新建立的索引總是比較好用

索引在使用了長久的時候 就會產生很多的碎片 查詢的性能就會受到影響 這時候有兩種方法解決 一是利用DBCC INDEXDEFRAG整理索引碎片 還有就是利用DBCC DBREINDEX重建索引

DBCC INDEXDEFRAG 命令是聯機操作 所以索引只有在該命令正在運行時才可用 而且可以在不丟失已完成工作的情況下中斷該操作 這種方法的缺點是在重新組織數據方面沒有聚集索引的除去/重新創建操作有效

重新創建聚集索引將對數據進行重新組織 其結果是使數據頁填滿 填滿程度可以使用 FILLFACTOR 選項進行配置 這種方法的缺點是索引在除去/重新創建周期內為離線狀態 並且操作屬原子級 如果中斷索引創建 則不會重新創建該索引

我們來看看索引重建使用的方法

語法 DBCC DBREINDEX ( [ TableName [ index_name [ fillfactor ] ] ] )

參數 TableName

是要重建其指定的索引的表名 資料庫 所有者和表名必須符合標識符的規則 有關更多信息 請參見使用標識符 如果提供 database 或 owner 部分 則必須使用單引號 ( )

將整個 database owner table_name 括起來 如果只指定 table_name 則不需要單引號

index_name 是要重建的索引名 索引名必須符合標識符的規則 如果未指定 index_name 或指定為 就要對表的所有索引進行重建

fillfactor 是創建索引時每個索引頁上要用於存儲數據的空間百分比 fillfactor替換起始填充因子以作為索引或任何其它重建的非聚集索引(因為已重建聚集索引)的新默認值 如果 fillfactor 為 DBCC DBREINDEX 在創建索引時將使用指定的起始fillfactor

我們在查詢分析器中輸入如下的命令

DBCC DBREINDEX ( MyTable )

lishixin/Article/program/SQLServer/201311/22210

④ 資料庫表如何建立索引

可以建立索引的；至於建立聚集索引或者是非聚集索引，那要看你這個時間欄位的具體情況以及使用或變更頻繁程帶清度

一般來說，適合建立聚集索引的要求：「既不能絕大多數都相同頃行基，又雀謹不能只有極少數相同」的規則

先說說一個誤區：有人認為：只要建立索引就能顯著提高查詢速度

這個想法是很錯誤的

建立非聚集索引，確實，一般情況下可以提高速度，但是一般並不會達到你想要的速度

只有在適當的列建立適當的（聚集）索引，才能達到滿意的效果

下面的表總結了何時使用聚集索引或非聚集索引（很重要）

動作描述 使用聚集索引 使用非聚集索引列經常被分組排序 應應返回某范圍內的數據 應 不應一個或極少不同值 不應 不應小數目的不同值 應 不應大數目的不同值 不應 應頻繁更新的列 不應 應外鍵列 應應主鍵列 應應頻繁修改索引列 不應 應別的就要看你的理解了

⑤ navicat如何添加索引如何使用Navicat為資料庫表建立索引

打開Navicat

打開數據表所在的資料庫，右擊需要新增欄位的資料庫表，然後點擊【設計表】

此時進入表鏈帶設計界面

點擊【索引】標簽頁，輸入索引名稱，如newindex

欄位選擇對話框中選擇要索引的欄位，如code欄位，然後點擊【確定】

選毀模擇棚余蘆索引類型，如unique