一、索引的概念
索引就是加快檢索表中數據的方法。數據庫的索引類(lèi)似于書(shū)籍的索引。在書(shū)籍中，索引允許用戶(hù)不必翻閱完整個(gè)書(shū)就能迅速地找到所需要的信息。在數據庫中，索引也允許數據庫程序迅速地找到表中的數據，而不必掃描整個(gè)數據庫。
二、索引的特點(diǎn)
1.索引可以加快數據庫的檢索速度
2.索引降低了數據庫插入、修改、刪除等維護任務(wù)的速度
3.索引創(chuàng )建在表上，不能創(chuàng )建在視圖上
4.索引既可以直接創(chuàng )建，也可以間接創(chuàng )建
5.可以在優(yōu)化隱藏中，使用索引
6.使用查詢(xún)處理器執行SQL語(yǔ)句，在一個(gè)表上，一次只能使用一個(gè)索引
7.其他
三、索引的優(yōu)點(diǎn)
1.創(chuàng )建唯一性索引，保證數據庫表中每一行數據的唯一性
2.大大加快數據的檢索速度，這也是創(chuàng )建索引的最主要的原因
3.加速表和表之間的連接，特別是在實(shí)現數據的參考完整性方面特別有意義。
4.在使用分組和排序子句進(jìn)行數據檢索時(shí)，同樣可以顯著(zhù)減少查詢(xún)中分組和排序的時(shí)間。
5.通過(guò)使用索引，可以在查詢(xún)的過(guò)程中使用優(yōu)化隱藏器，提高系統的性能。
四、索引的缺點(diǎn)
1.創(chuàng )建索引和維護索引要耗費時(shí)間，這種時(shí)間隨著(zhù)數據量的增加而增加
2.索引需要占物理空間，除了數據表占數據空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會(huì )更大
3.當對表中的數據進(jìn)行增加、刪除和修改的時(shí)候，索引也要動(dòng)態(tài)的維護，降低了數據的維護速度
五、索引分類(lèi)
1.直接創(chuàng )建索引和間接創(chuàng )建索引
直接創(chuàng )建索引： CREATE INDEX mycolumn_index ON mytable (myclumn)
間接創(chuàng )建索引：定義主鍵約束或者唯一性鍵約束，可以間接創(chuàng )建索引
2.普通索引和唯一性索引
普通索引：CREATE INDEX mycolumn_index ON mytable (myclumn)
唯一性索引：保證在索引列中的全部數據是唯一的，對聚簇索引和非聚簇索引都可以使用
CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
3.單個(gè)索引和復合索引
單個(gè)索引：即非復合索引
復合索引：又叫組合索引，在索引建立語(yǔ)句中同時(shí)包含多個(gè)字段名，最多16個(gè)字段
CREATE INDEX name_index ON username(firstname,lastname)
4.聚簇索引和非聚簇索引(聚集索引，群集索引)
聚簇索引：物理索引，與基表的物理順序相同，數據值的順序總是按照順序排列
CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH
ALLOW_DUP_ROW(允許有重復記錄的聚簇索引)
非聚簇索引：CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)
六、索引的使用
1.當字段數據更新頻率較低，查詢(xún)使用頻率較高并且存在大量重復值是建議使用聚簇索引
2.經(jīng)常同時(shí)存取多列，且每列都含有重復值可考慮建立組合索引
3.復合索引的前導列一定好控制好，否則無(wú)法起到索引的效果。如果查詢(xún)時(shí)前導列不在查詢(xún)條件中則該復合索引不會(huì )被使用。前導列一定是使用最頻繁的列
4.多表操作在被實(shí)際執行前，查詢(xún)優(yōu)化器會(huì )根據連接條件，列出幾組可能的連接方案并從中找出系統開(kāi)銷(xiāo)最小的最佳方案。連接條件要充份考慮帶有索引的表、行數多的表；內外表的選擇可由公式：外層表中的匹配行數*內層表中每一次查找的次數確定，乘積最小為最佳方案
5.where子句中對列的任何操作結果都是在sql運行時(shí)逐列計算得到的，因此它不得不進(jìn)行表搜索，而沒(méi)有使用該列上面的索引；如果這些結果在查詢(xún)編譯時(shí)就能得到，那么就可以被sql優(yōu)化器優(yōu)化，使用索引，避免表搜索(例：select * from record where substring(card_no,1,4)=’5378’
&& select * from record where card_no like ’5378%’)任何對列的操作都將導致表掃描，它包括數據庫函數、計算表達式等等，查詢(xún)時(shí)要盡可能將操作移至等號右邊
6.where條件中的’in’在邏輯上相當于’or’，所以語(yǔ)法分析器會(huì )將in ('0','1')轉化為column='0' or column='1'來(lái)執行。我們期望它會(huì )根據每個(gè)or子句分別查找，再將結果相加，這樣可以利用column上的索引；但實(shí)際上它卻采用了"or策略"，即先取出滿(mǎn)足每個(gè)or子句的行，存入臨時(shí)數據庫的工作表中，再建立唯一索引以去掉重復行，最后從這個(gè)臨時(shí)表中計算結果。因此，實(shí)際過(guò)程沒(méi)有利用column上索引，并且完成時(shí)間還要受tempdb數據庫性能的影響。in、or子句常會(huì )使用工作表，使索引失效；如果不產(chǎn)生大量重復值，可以考慮把子句拆開(kāi)；拆開(kāi)的子句中應該包含索引
7.要善于使用存儲過(guò)程，它使sql變得更加靈活和高效
索引使用原則：
1、使用索引來(lái)更快地遍歷表。
缺省情況下建立的索引是非群集索引，但有時(shí)它并不是最佳的。在非群集索引下，數據在物理上隨機存放在數據頁(yè)上。合理的索引設計要建立在
對各種查詢(xún)的分析和預測上。一般來(lái)說(shuō)：
a.有大量重復值、且經(jīng)常有范圍查詢(xún)（ > ,< ，> =,< =）和order by、group by發(fā)生的列，可考
慮建立群集索引；
b.經(jīng)常同時(shí)存取多列，且每列都含有重復值可考慮建立組合索引；
c.組合索引要盡量使關(guān)鍵查詢(xún)形成索引覆蓋，其前導列一定是使用最頻繁的列。索引雖有助于提高性能但不是索引越多越好，恰好相反過(guò)多的索引會(huì )導致系統低效。用戶(hù)在表中每加進(jìn)一個(gè)索引，維護索引集合就要做相應的更新工作。
2、在海量查詢(xún)時(shí)盡量少用格式轉換。
3、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短語(yǔ)，任何一種索引都有助于SELECT的性能提高。
7、任何對列的操作都將導致表掃描，它包括數據庫函數、計算表達式等等，查詢(xún)時(shí)要盡可能將操作移至等號右邊。
4、IN、OR子句常會(huì )使用工作表，使索引失效。如果不產(chǎn)生大量重復值，可以考慮把子句拆開(kāi)。拆開(kāi)的子句中應該包含索引。
Sql的優(yōu)化原則2：
1、只要能滿(mǎn)足你的需求，應盡可能使用更小的數據類(lèi)型：例如使用MEDIUMINT代替INT
2、盡量把所有的列設置為NOT NULL，如果你要保存NULL，手動(dòng)去設置它，而不是把它設為默認值。
3、盡量少用VARCHAR、TEXT、BLOB類(lèi)型
4、如果你的數據只有你所知的少量的幾個(gè)。最好使用ENUM類(lèi)型
5、正如graymice所講的那樣，建立索引。
以下是我做的一個(gè)實(shí)驗，可以發(fā)現索引能極大地提高查詢(xún)的效率：
我有一個(gè)會(huì )員信息表users，里邊有37365條用戶(hù)記錄：
在不加索引的時(shí)候進(jìn)行查詢(xún)：
sql語(yǔ)句A:
select * from users where username like ’%許%’;
在Mysql-Front中的8次查詢(xún)時(shí)長(cháng)為：1.40,0.54,0.54,0.54,0.53,0.55,0.54 共找到960條記錄
sql語(yǔ)句B:
select * from users where username like ’許%’;
在Mysql-Front中的8次查詢(xún)時(shí)長(cháng)為：0.53,0.53,0.53,0.54,0.53,0.53,0.54,0.54 共找到836條記錄
sql語(yǔ)句C:
select * from users where username like ’%許’;
在Mysql-Front中的8次查詢(xún)時(shí)長(cháng)為：0.51,0.51,0.52,0.52,0.51,0.51,0.52,0.51 共找到7條記錄
為username列添加索引:
create index usernameindex on users(username(6));
再次查詢(xún)：
sql語(yǔ)句A:
select * from users where username like ’%許%’;
在Mysql-Front中的8次查詢(xún)時(shí)長(cháng)為：0.35,0.34,0.34,0.35,0.34,0.34,0.35,0.34 共找到960條記錄
sql語(yǔ)句B:
select * from users where username like ’許%’;
在Mysql-Front中的8次查詢(xún)時(shí)長(cháng)為：0.06,0.07,0.07,0.07,0.07,0.07,0.06,0.06 共找到836條記錄
sql語(yǔ)句C:
select * from users where username like ’%許’;
在Mysql-Front中的8次查詢(xún)時(shí)長(cháng)為：0.32,0.31,0.31,0.32,0.31,0.32,0.31,0.31 共找到7條記錄
在實(shí)驗過(guò)程中，我沒(méi)有另開(kāi)任何程序，以上的數據說(shuō)明在單表查詢(xún)中，建立索引的可以極大地提高查詢(xún)速度。
另外要說(shuō)的是如果建立了索引，對于like ’許%’類(lèi)型的查詢(xún)，速度提升是最明顯的。因此，我們在寫(xiě)sql語(yǔ)句的時(shí)候也盡量采用這種方式查詢(xún)。
對于多表查詢(xún)我們的優(yōu)化原則是：
盡量將索引建立在：left join on/right join on ...?。珬l件，的條件語(yǔ)句中所涉及的字段上。
多表查詢(xún)比單表查詢(xún)更能體現索引的優(yōu)勢。
6、索引的建立原則：
如果一列的中數據的前綴重復值很少，我們最好就只索引這個(gè)前綴。Mysql支持這種索引。我在上面用到的索引方法就是對username最左邊的6個(gè)字符進(jìn)行索引。索引越短，占用的
磁盤(pán)空間越少，在檢索過(guò)程中花的時(shí)間也越少。這方法可以對最多左255個(gè)字符進(jìn)行索引。
在很多場(chǎng)合，我們可以給建立多列數據建立索引。
索引應該建立在查詢(xún)條件中進(jìn)行比較的字段上，而不是建立在我們要找出來(lái)并且顯示的字段上
7、限制索引的使用的避歸。
7.1 IN、OR子句常會(huì )使用工作表，使索引失效。
如果不產(chǎn)生大量重復值，可以考慮把子句拆開(kāi)。拆開(kāi)的子句中應該包含索引。這句話(huà)怎么理解決，請舉個(gè)例子
例子如下:
如果在fields1和fields2上同時(shí)建立了索引，fields1為主索引
以下sql會(huì )用到索引
select * from tablename1 where fields1=’value1’ and fields2=’value2’
以下sql不會(huì )用到索引
select * from tablename1 where fields1=’value1’ or fields2=’value2’
7.2 使用IS NULL 或IS NOT NULL
使用IS NULL 或IS NOT NULL同樣會(huì )限制索引的使用。因為NULL值并沒(méi)有被定義。在SQL語(yǔ)句中使用NULL會(huì )有很多的麻煩。因此建議開(kāi) 發(fā)人員在建表時(shí)，把需要索引的列設成NOT NULL。如果被索引的列在某些行中存在NULL值，就不會(huì )使用這個(gè)索引（除非索引是一個(gè)位圖索引，關(guān)于位圖索引在稍后在詳細討論）。
7.3 使用函數
如果不使用基于函數的索引，那么在SQL語(yǔ)句的WHERE子句中對存在索引的列使用函數時(shí)，會(huì )使優(yōu)化器忽略掉這些索引。下面的查詢(xún)不會(huì )使用索引（只要它不是基于函數的索引）
select empno,ename,deptno
from emp
where trunc(hiredate)='01-MAY-81';
把上面的語(yǔ)句改成下面的語(yǔ)句，這樣就可以通過(guò)索引進(jìn)行查找。
select empno,ename,deptno
from emp
where hiredate<(to_date('01-MAY-81')+0.9999);
7.4 比較不匹配的數據類(lèi)型
比較不匹配的數據類(lèi)型也是比較難于發(fā)現的性能問(wèn)題之一。注意下面查詢(xún)的例子，account_number是一個(gè)VARCHAR2類(lèi)型，在account_number字段上有索引。下面的語(yǔ)句將執行全表掃描。
select bank_name,address,city,state,zip
from banks
where account_number = 990354;
Oracle可以自動(dòng)把where子句變成to_number(account_number)=990354，這樣就限制了索引的使用,改成下面的查詢(xún)就可以使用索引：
select bank_name,address,city,state,zip
from banks
where account_number ='990354';
特別注意：不匹配的數據類(lèi)型之間比較會(huì )讓Oracle自動(dòng)限制索引的使用，即便對這個(gè)查詢(xún)執行Explain Plan也不能讓您明白為什么做了一 次“全表掃描”。
補充:
1.索引帶來(lái)查詢(xún)上的速度的大大提升,但索引也占用了額外的硬盤(pán)空間(當然現在一般硬盤(pán)空間不成問(wèn)題),而且往表中插入新記錄時(shí)索引也要隨著(zhù)更新這也需要一定時(shí)間.
有些表如果經(jīng)常insert,而較少select,就不用加索引了.不然每次寫(xiě)入數據都要重新改寫(xiě)索引,花費時(shí)間;
這個(gè)視實(shí)際情況而定,通常情況下索引是必需的.
2.我在對查詢(xún)效率有懷疑的時(shí)候,一般是直接用Mysql的Explain來(lái)跟蹤查詢(xún)情況.
你用Mysql-Front是通過(guò)時(shí)長(cháng)來(lái)比較,我覺(jué)得如果從查詢(xún)時(shí)掃描字段的次數來(lái)比較更精確一些.
聯(lián)系客服
