當前位置：首頁 > 编程资源 > 综合教程 >内容正文

综合教程

sql索引的建立与使用_sqlserver创建索引语句(廖雪峰的官方网站)

發布時間：2023/12/19 综合教程 35 生活家

生活随笔收集整理的這篇文章主要介紹了 sql索引的建立与使用_sqlserver创建索引语句(廖雪峰的官方网站) 小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

之前在網上看到過很多關于mysql聯合索引最左前綴匹配的文章，自以為就了解了其原理，發現遺漏了些東西，這里自己整理一下這方面的內容。

1 前言

SQL索引有兩種，聚集索引和非聚集索引

聚集索引存儲記錄是物理上連續存在，而非聚集索引是邏輯上的連續，物理存儲并不連續

字典的拼音查詢法就是聚集索引，字典的部首查詢就是一個非聚集索引.

聚集索引和非聚集索引的根本區別是表記錄的排列順序和與索引的排列順序是否一致

聚集索引一個表只能有一個，而非聚集索引一個表可以存在多個。

2 建立索引的原則：

1) 定義主鍵的數據列一定要建立索引。

2) 定義有外鍵的數據列一定要建立索引。

3) 對于經常查詢的數據列最好建立索引。

4) 對于需要在指定范圍內的快速或頻繁查詢的數據列;

5) 經常用在WHERE子句中的數據列。

6) 經常出現在關鍵字order by、group by、distinct后面的字段，建立索引。

如果建立的是復合索引，索引的字段順序要和這些關鍵字后面的字段順序一致，否則索引不會被使用。

7) 對于那些查詢中很少涉及的列，重復值比較多的列不要建立索引。

8) 對于定義為text、image和bit的數據類型的列不要建立索引。

9) 對于經常存取的列避免建立索引

10) 限制表上的索引數目。對一個存在大量更新操作的表，所建索引的數目一般不要超過3個，最多不要超過5個。

索引雖說提高了訪問速度，但太多索引會影響數據的更新操作。

11) 對復合索引，按照字段在查詢條件中出現的頻度建立索引。在復合索引中，記錄首先按照第一個字段排序。

對于在第一個字段上取值相同的記錄，系統再按照第二個字段的取值排序，以此類推。

因此只有復合索引的第一個字段出現在查詢條件中，該索引才可能被使用,因此將應用頻度高的字段，放置在復合索引的前面，會使系統最大可能地使用此索引，發揮索引的作用。

2.1另外原則

1.最左前綴匹配原則

非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c >3 and d = 4. 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

2.=和in可以亂序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式

3.盡量選擇區分度高的列作為索引

區分度的公式是count(distinct col)/count(*)，表示字段不重復的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別字段可能在大數據面前區分度就是0，那可能有人會問，這個比例有什么經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄

4.索引列不能參與計算

保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’);

5.盡量的擴展索引，不要新建索引。

比如表中已經有a的索引，現在要加(a,b)的索引，那么只需要修改原來的索引即可

6．選擇唯一性索引

唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄。例如，學生表中學號是具有唯一性的字段。為該字段建立唯一性索引可以很快的確定某個學生的信息。如果使用姓名的話，可能存在同名現象，從而降低查詢速度。

7．為經常需要排序、分組和聯合操作的字段建立索引

經常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作會浪費很多時間。如果為其建立索引，可以有效地避免排序操作。

8．為常作為查詢條件的字段建立索引

如果某個字段經常用來做查詢條件，那么該字段的查詢速度會影響整個表的查詢速度。因此，為這樣的字段建立索引，可以提高整個表的查詢速度。

9．限制索引的數目

索引的數目不是越多越好。每個索引都需要占用磁盤空間，索引越多，需要的磁盤空間就越大。修改表時，對索引的重構和更新很麻煩。越多的索引，會使更新表變得很浪費時間。

10．盡量使用數據量少的索引

如果索引的值很長，那么查詢的速度會受到影響。例如，對一個CHAR(100)類型的字段進行全文檢索需要的時間肯定要比對CHAR(10)類型的字段需要的時間要多。

11．盡量使用前綴來索引

如果索引字段的值很長，最好使用值的前綴來索引。例如，TEXT和BLOG類型的字段，進行全文檢索會很浪費時間。如果只檢索字段的前面的若干個字符，這樣可以提高檢索速度。

12．刪除不再使用或者很少使用的索引

表中的數據被大量更新，或者數據的使用方式被改變后，原有的一些索引可能不再需要。數據庫管理員應當定期找出這些索引，將它們刪除，從而減少索引對更新操作的影響。

2.2 最左前綴匹配原則

在mysql建立聯合索引時會遵循最左前綴匹配的原則，即最左優先，在檢索數據時從聯合索引的最左邊開始匹配，示例：
對列col1、列col2和列col3建一個聯合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

聯合索引 test_col1_col2_col3 實際建立了(col1)、(col1,col2)、(col,col2,col3)三個索引。

SELECT*FROMtestWHEREcol1=“1”ANDclo2=“2”ANDclo4=“4”

上面這個查詢語句執行時會依照最左前綴匹配原則，檢索時會使用索引(col1,col2)進行數據匹配。

注意

索引的字段可以是任意順序的，如：

SELECT * FROM test WHERE col1=“1” AND clo2=“2”
SELECT * FROM test WHERE col2=“2” AND clo1=“1”

這兩個查詢語句都會用到索引(col1,col2)，mysql創建聯合索引的規則是首先會對聯合合索引的最左邊的，也就是第一個字段col1的數據進行排序，在第一個字段的排序基礎上，然后再對后面第二個字段col2進行排序。其實就相當于實現了類似 order by col1 col2這樣一種排序規則。

有人會疑惑第二個查詢語句不符合最左前綴匹配：首先可以肯定是兩個查詢語句都包含索引(col1,col2)中的col1、col2兩個字段，只是順序不一樣，查詢條件一樣，最后所查詢的結果肯定是一樣的。既然結果是一樣的，到底以何種順序的查詢方式最好呢？此時我們可以借助mysql查詢優化器explain，explain會糾正sql語句該以什么樣的順序執行效率最高，最后才生成真正的執行計劃。

2.3 為什么要使用聯合索引

減少開銷。建一個聯合索引(col1,col2,col3)，實際相當于建了(col1),(col1,col2),(col1,col2,col3)三個索引。每多一個索引，都會增加寫操作的開銷和磁盤空間的開銷。對于大量數據的表，使用聯合索引會大大的減少開銷！
覆蓋索引。對聯合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通過遍歷索引取得數據，而無需回表，這減少了很多的隨機io操作。減少io操作，特別的隨機io其實是dba主要的優化策略。所以，在真正的實際應用中，覆蓋索引是主要的提升性能的優化手段之一。
效率高。索引列越多，通過索引篩選出的數據越少。有1000W條數據的表，有如下sql:selectfrom table where col1=1 and col2=2 and col3=3,假設假設每個條件可以篩選出10%的數據，如果只有單值索引，那么通過該索引能篩選出1000W10%=100w條數據，然后再回表從100w條數據中找到符合col2=2 and col3= 3的數據，然后再排序，再分頁；如果是聯合索引，通過索引篩選出1000w10%10% *10%=1w，效率提升可想而知！

引申

對于聯合索引(col1,col2,col3)，查詢語句SELECT * FROM test WHERE col2=2;是否能夠觸發索引？
大多數人都會說NO，實際上卻是YES。
原因：

EXPLAIN SELECT * FROM test WHERE col2=2;
EXPLAIN SELECT * FROM test WHERE col1=1;

觀察上述兩個explain結果中的type字段。查詢中分別是：

type: index
type: ref

index：這種類型表示mysql會對整個該索引進行掃描。要想用到這種類型的索引，對這個索引并無特別要求，只要是索引，或者某個聯合索引的一部分，mysql都可能會采用index類型的方式掃描。但是呢，缺點是效率不高，mysql會從索引中的第一個數據一個個的查找到最后一個數據，直到找到符合判斷條件的某個索引。所以，上述語句會觸發索引。
ref：這種類型表示mysql會根據特定的算法快速查找到某個符合條件的索引，而不是會對索引中每一個數據都進行一一的掃描判斷，也就是所謂你平常理解的使用索引查詢會更快的取出數據。而要想實現這種查找，索引卻是有要求的，要實現這種能快速查找的算法，索引就要滿足特定的數據結構。簡單說，也就是索引字段的數據必須是有序的，才能實現這種類型的查找，才能利用到索引。

3 聚集索引和非聚集索引

聚集索引和非聚集索引的根本區別是數據記錄的排列順序和索引的排列順序是否一致，聚集索引表記錄的排列順序與索引的排列順序一致，優點是查詢速度快，因為一旦具有第一個索引值的紀錄被找到，具有連續索引值的記錄也一定物理的緊跟其后，從而縮小了搜索范圍，對于返回某一范圍的數據效果最好。

聚集索引的缺點是對表進行修改速度較慢，這是為了保持表中的記錄的物理順序與索引的順序一致，而把記錄插入到數據頁的相應位置，必須在數據頁中進行數據重排，降低了執行速度。
　　非聚集索引指定了表中記錄的邏輯順序，數據記錄的物理順序和索引的順序不一致，聚集索引和非聚集索引都采用了B樹的結構，但非聚集索引的葉子層順序并不與實際的數據頁相同，而采用指向表中的記錄在數據頁中位置的方式。非聚集索引比聚集索引層次多，添加記錄不會引起數據順序的重組。在有大量不同數據的列上建立非聚集索引，可以提高數據的查詢和修改速度。

在對聚集索引列查詢時，聚集索引的速度要比非聚集索引速度快。

在對聚集索引列排序時，聚集索引的速度要比非聚集索引速度快。但是如果數據量比較大時，如10萬以上，則二者的速度差別不明顯。

3.1 聚集索引和非聚集的建立原則

在創建索引時要做到三個適當，即在適當的表上、適當的列上創建適當數量的索引。雖然這可以通過一句話來概括優化的索引的基本準則，但是要做到這一點的話，需要做出很大的努力。具體的來說，要做到這個三個適當有如下幾個要求。

3.1.1根據表的大小來創建索引。

雖然給表創建索引，可以提高查詢的效率。但是需要注意的是，索引也需要一定的開銷的。為此并不是說給所有的表都創建索引，那么就可以提高數據庫的性能。這個認識是錯誤的。給所有的表都創建了索引，那么其反而會給數據庫的性能造成負面的影響。因為此時濫用索引的開銷可能已經遠遠大于由此帶來的性能方面的收益。所以，數據庫管理員首先需要做到，為合適的表來建立索引，而不是為所有的表建立索引。

一般來說，不需要為比較小的表創建索引。因為即使建立了索引，其性能也不會得到很大的改善。相反索引建立的開銷，如維護成本等等，要比這個要大。也就是說，付出的要比得到的多，顯然違反常理。

另外，就是對于超大的表，也不一定要建立索引。有些表雖然比較大，記錄數量非常的多。但是此時為這個表建立索引并一定的合適。對于一些超大的表，建立索引有時候往往不能夠達到預計的效果。而且在大表上建立索引，其索引的開銷要比普通的表大的多。那么到底是否給大表建立索引呢？主要是看兩個方面的內容。首先是需要關注一下，在這張大表中經常需要查詢的記錄數量。一般來說，如果經常需要查詢的數據不超過10%到15%的話，那就沒有必要為其建立索引的必要。因為此時建立索引的開銷可能要比性能的改善大的多。如果數據庫管理員需要得出一個比較精確的結論，那么就需要進行測試分析。

3.1.2根據列的特征來創建索引

列的特點不同，索引創建的效果也不同。需要了解為哪些列創建索引可以起到事半功倍的效果。同時也需要了解為哪些列創建索引反而起到的是事倍功半的效果。

索引設置的是否恰當，不僅跟數據庫設計架構有關，而且還跟企業的經濟業務相關。雖然一開始已經做了索引的優化工作。但是隨著后來經濟數據的增加，這個索引的效果會越來越打折扣。所以需要隔一段時間，對數據庫的索引進行優化。該去掉的去掉，該調整的調整，以提高數據庫的性能。

3.1.3在一個表上創建多少索引合適

通常來說，表的索引越多，其查詢的速度也就越快。但是，表的更新速度則會降低。這主要是因為表的更新同時也是索引的更新。到底在表中創建多少索引合適，就需要在這個更新速度與查詢速度之間取得一個均衡點。如對于一些數據倉庫或者決策型數據庫系統，其主要用來進行查詢。相關的記錄往往是在數據庫初始化的時候導入。此時，設置的索引多一點，可以提高數據庫的查詢性能。同時因為記錄不怎么更新，所以索引比較多的情況下，也不會影響到更新的速度。相反，如果那些表中經常需要更新記錄，如一些事務型的應用系統，數據更新操作是家常便飯的事情。此時如果在一張表中建立過多的索引，則會影響到更新的速度。由于更新操作比較頻繁，所以對其的負面影響，要比查詢效率提升要大的多。此時就需要限制索引的數量，只在一些必要的字段上建立索引。

4 索引的不足之處

雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。

建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重，但如果你在一個大表上創建了多種組合索引，索引文件的會膨脹很快。

索引只是提高效率的一個因素，如果你的MySQL有大數據量的表，就需要花時間研究建立最優秀的索引，或優化查詢語句。

5 使用索引時，有以下一些技巧和注意事項：
5.1 索引不會包含有NULL值的列

只要列中包含有NULL值都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那么這一列對于此復合索引就是無效的。

所以我們在數據庫設計時不要讓字段的默認值為NULL。

5.2 使用短索引

對列進行索引，如果可能應該指定一個前綴長度。

例如，如果有一個CHAR(255)的列，如果在前10個或20個字符內，多數值是惟一的，那么就不要對整個列進行索引。

短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。

5.3 索引列排序

MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那么order by中的列是不會使用索引的。

因此數據庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創建復合索引。

5.4 like語句操作

一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。

like “%aaa%” 不會使用索引，而like “aaa%”可以使用索引。

5.5 不要在列上進行運算

select * from users where YEAR(adddate)<2007;

將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成

select * from users where adddate<‘2007-01-01’;

5.6 不使用NOT IN和<>操作

7 索引的建立時機

一般來說，在WHERE和JOIN中出現的列需要建立索引，但也不完全如此，

因為MySQL只對<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE才會使用索引。

因為在以通配符%和_開頭作查詢時，MySQL不會使用索引。

8 索引的使用

8.1 建立索引

create [UNIQUE|FULLTEXT] index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);

示例：

alter table table_name ADD INDEX [index_name] (index_col_name,…)

CREATE INDEX paywayid_index ON pay_order_trade (paywayid)

8.2 組合索引

CREATE INDEX idx_example ON table1 (col1 ASC, col2 DESC, col3 ASC)

示例：

ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

最左前綴：顧名思義，就是最左優先，

上例中我們創建了lname_fname_age多列索引,相當于創建了(lname)單列索引，(lname,fname)組合索引以及(lname,fname,age)組合索引。

8.3 刪除索引

DROP INDEX index_name ON tbl_name;

alter table table_name drop index index_name;

8.4 刪除主鍵(索引)比較特別:

alter table t_b drop primary key;

8.5 查詢索引(均可)

show index from table_name;

show keys from table_name;

desc table_Name;

9查詢慢分析

9.1 回到開始的慢查詢

根據最左匹配原則，最開始的sql語句的索引應該是status、operator_id、type、operate_time的聯合索引；其中status、operator_id、type的順序可以顛倒，所以我才會說，把這個表的所有相關查詢都找到，會綜合分析；比如還有如下查詢：

select * from task where status = 0 and type = 12 limit 10;

select count(*) from task where status = 0 ;

那么索引建立成(status,type,operator_id,operate_time)就是非常正確的，因為可以覆蓋到所有情況。這個就是利用了索引的最左匹配的原則

9.2 查詢優化神器 – explain命令

關于explain命令相信大家并不陌生，具體用法和字段含義可以參考官網explain-output，這里需要強調rows是核心指標，絕大部分rows小的語句執行一定很快（有例外，下面會講到）。所以優化語句基本上都是在優化rows。

9.3 慢查詢優化基本步驟

先運行看看是否真的很慢，注意設置SQL_NO_CACHE
where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應用到表中返回的記錄數最小的表開始查起，單表每個字段分別查詢，看哪個字段的區分度最高
explain查看執行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢）
order by limit 形式的sql語句讓排序的表優先查
了解業務方使用場景
加索引時參照建索引的幾大原則
觀察結果，不符合預期繼續從0分析

索引的優化方法

1，何時使用聚簇索引或非聚簇索引：

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

2，索引不會包含有NULL值的列：只要列中包含有NULL值，都將不會被包含在索引中，組合索引中只要有一列有NULL值，那么這一列對于此條組合索引就是無效的。所以我們在數據庫設計時，不要讓索引字段的默認值為NULL。

3，使用短索引：假設，如果有一個數據類型為CHAR(255)的列，在前10個或20個字符內，絕大部分數據的值是唯一的，那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省I/O操作。

4，索引列排序：MySQL查詢只使用一個索引，因此如果WHERE子句中已經使用了索引的話，那么ORDER BY中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下，不要使用排序操作；盡量不要包含多個列的排序，如果需要，最好給這些列也創建組合索引。

5，LIKE語句操作：一般情況下，不建議使用LIKE操作；如果非使用不可，如何使用也是一個研究的課題。LIKE “%aaaaa%”不會使用索引，但是LIKE “aaa%”卻可以使用索引。

6，不要在索引列上進行運算：在建立索引的原則中，提到了索引列不能進行運算，這里就不再贅述了。

mysql索引失效情況

1、最佳左前綴原則——如果索引了多列，要遵守最左前綴原則。指的是查詢要從索引的最左前列開始并且不跳過索引中的列。

前提條件：表中已添加復合索引（username,password,age）

分析：該查詢缺少username，查詢條件復合索引最左側username缺少，違反了最佳左前綴原則，導致索引失效，變為ALL，全表掃描

分析：查詢條件缺少username，password，查詢條件復合索引最左側username，password缺少，違反了最佳左前綴原則，導致索引失效，變為ALL，全表掃描

分析：該查詢只有一個username條件，根據最佳左前綴原則索引能夠被使用到，但是是部分使用

2、不在索引列上做任何操作（計算，函數，（自動或者手動）類型裝換），會導致索引失效而導致全表掃描

分析：第一個圖索引列不使用函數，遵循左前綴原則，能夠使用索引。第二張圖索引列上使用了函數，即使遵循左前綴原則，索引還是失效

3、存儲引擎不能使用索引中范圍條件右邊的列，范圍之后索引失效。（< ,> between and）

分析：圖一索引全部使用到。圖二索引使用到username和age，但是username是使用索引檢索，而age著重索引排序，這時age為范圍查找，password索引將失效

4、mysql使用不等于(!= 或者<>)的時候，無法使用索引，會導致索引失效

5、mysql中使用is not null 或者 is null會導致無法使用索引

分析：對username列做了普通索引，查詢帶is not null，結果索引不生效

6、mysql中like查詢是以%開頭，索引會失效變成全表掃描，覆蓋索引。

分析：對username列做了普通索引，以%開頭進行查詢，結果索引失效被覆蓋

7、mysql中，字符串不加單引號索引會失效。正確寫法：select * from t_user where username = ‘lujin’;

8、mysql中，如果條件中有or，即使其中有條件帶索引也不會使用(這也是為什么盡量少用or的原因)。要想使用or，又想讓索引生效，只能將or條件中的每個列都加上索引

9、如果mysql使用全表掃描要比使用索引快,則不會使用到索引

參考：

https://blog.csdn.net/fly910905/article/details/78690074
https://tech.meituan.com/2014/06/30/mysql-index.html
https://segmentfault.com/a/1190000015416513
https://www.cnblogs.com/Jessy/p/3543063.html
https://blog.csdn.net/qq_34258346/article/details/80272198

總結

以上是生活随笔為你收集整理的sql索引的建立与使用_sqlserver创建索引语句(廖雪峰的官方网站)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：【图嵌入】DeepWalk原理与代码实战
下一篇：【图嵌入】Graph Embedding