當前位置:首頁 » 編程語言 » sql表索引為什麼只有一個
擴展閱讀
存儲大數據哪個平台好 2022-11-27 20:59:30
webservice讀取資料庫 2022-11-27 20:59:19
台灣空姐艷談迅雷ftp 2022-11-27 20:57:27

sql表索引為什麼只有一個

發布時間: 2022-10-03 17:28:58

A. 為什麼每張表只能建一個聚集索引

因為聚焦索引決定了表的物理排列順序,一個表只能有一個物理排列順序,所以一個表只能建一個聚集索引。
聚集索引是一種索引,該索引中鍵值的邏輯順序決定了表中相應行的物理順序。
聚集索引確定表中數據的物理順序。聚集索引類似於電話簿,按姓氏排列數據。由於聚集索引規定數據在表中的物理存儲順序,因此一個表只能包含一個聚集索引。但該索引可以包含多個列(組合索引),就像電話簿按姓氏和名字進行組織一樣。
聚集索引對於那些經常要搜索范圍值的列特別有效。使用聚集索引找到包含第一個值的行後,便可以確保包含後續索引值的行在物理相鄰。例如,如果應用程序執行的一個查詢經常檢索某一日期范圍內的記錄,則使用聚集索引可以迅速找到包含開始日期的行,然後檢索表中所有相鄰的行,直到到達結束日期。這樣有助於提高此類查詢的性能。同樣,如果對從表中檢索的數據進行排序時經常要用到某一列,則可以將該表在該列上聚集(物理排序),避免每次查詢該列時都進行排序,從而節省成本。

B. 一個表只能有一個主鍵索引,一個主鍵索引可以多個欄位

面試的時候肯定會問這一個問題,mysql為什麼會選擇b+樹作為索引呢?而不選擇其他索引,例如b樹?hash?

下面說的磁碟IO是指數據從硬碟載入到內存中的操作
hash索引的話,不支持范圍查詢,因為hash就是一個鍵對應一個值的,沒辦法范圍查詢
二叉樹的話,它的特點就是左子樹小於根節點小於右子樹,如果根節點取值有問題的話,有可能會退化成鏈表,就是樹不分叉了,樹一直往左或者一直往右,這樣就不能折半查找從而減少IO次數了,不支持范圍查詢,要是范圍查詢的話,每次都要從根部遍歷,樹也太高了,樹越高,IO操作越頻繁,浪費資源
平衡二叉樹的話,它就沒有了二叉樹的這種退化成鏈表的缺點,因為他左右子節點最多相差1層,可是他也不支持范圍查找這一點和二叉樹的問題一樣
b樹的話,和二叉樹比起來樹是很矮胖,IO操作減少了,是個多叉樹,它每個節點都存了對應的行數據,可是如果這一行的數據的列不斷的增加,那麼這一頁存儲的節點就會變少,因為所佔的空間不斷的變大,樹也會越來越高,增加IO操作次數,同時是也不支持范圍查找。要是相同大小的空間可以存很多的節點數據的話就更好了,所以就有了下面的b+樹
b+樹 它非葉子節點只存索引的數據,不存整行數據,但是葉子節點是冗餘的,冗餘了非葉子節點,葉子節點還都用雙向鏈表鏈接起來,這樣有助於順序查找,b+樹和b樹比起來,更加矮胖,磁碟IO次數更少
二、 mysql中索引類型
聚簇索引與非聚簇索引
我們可以簡單的理解為 聚簇索引就是主鍵索引,非聚簇索引就是普通索引
本質的區別是

聚簇索引的葉子節點存儲的是整行數據

innodb是通過主鍵來實現聚簇索引的,如果沒有主鍵的話,那麼他就會選擇一個唯一非空的索引來實現,如果再沒有的話,他就會隱式生成一個主鍵來實現聚簇索引

非聚簇索引存儲的是索引值和主鍵值
普通索引 一張表中可以有多個普通索引,隨便一個欄位都可以建立的索引,我們平常建立的索引大部分都是普通索引
聯合索引 好幾個欄位聯合起來建立的索引
唯一索引 業務中唯一的欄位適合建立唯一索引,一個表中可以有多個唯一索引
主鍵索引 和唯一索引一樣,主鍵索引也是唯一的,不同的就是,一個表只能有一個主鍵索引
三、關於索引的sql
創建主鍵索引

ALTER TABLE test add PRIMARY KEY (id)
創建唯一索引

ALTER TABLE test add UNIQUE idx_id_card(id_card)
創建普通索引

ALTER TABLE test add INDEX idx_name(name)
創建聯合索引

ALTER TABLE test add INDEX idx_age_name(age,name)
修改索引名稱 :先刪除再添加

刪除索引 (兩種方式)

C. SQL中一個表可以有幾個聚集索引和非聚集索引

一個表只能有一個聚集索引,可以有多個非聚集索引

下面是聚集索引和非聚集索引的詳細介紹:
聚集索引基於數據行的鍵值在表內排序和存儲這些數據行。每個表只能有一個聚集索引,因為數據行本身只能按一個順序存儲。有關聚集索引體系結構的詳細信息,請參閱聚集索引結構。

每個表幾乎都對列定義聚集索引來實現下列功能:

可用於經常使用的查詢。

提供高度唯一性。

注意:

創建 PRIMARY KEY 約束時,將在列上自動創建唯一索引。默認情況下,此索引是聚集索引,但是在創建約束時,可以指定創建非聚集索引。

可用於范圍查詢。

如果未使用 UNIQUE 屬性創建聚集索引,資料庫引擎將向表自動添加一個 4 位元組的 uniqueifier
列。必要時,資料庫引擎將向行自動添加一個 uniqueifier 值以使每個鍵唯一。此列和列值供內部使用,用戶不能查看或訪問

查詢注意事項

在創建聚集索引之前,應先了解數據是如何被訪問的。考慮對具有以下特點的查詢使用聚集索引:

使用運算符(如 BETWEEN、>、>=、< 和
<=)返回一系列值。

使用聚集索引找到包含第一個值的行後,便可以確保包含後續索引值的行物理相鄰。例如,如果某個查詢在一系列銷售訂單號間檢索記錄,SalesOrderNumber
列的聚集索引可快速定位包含起始銷售訂單號的行,然後檢索表中所有連續的行,直到檢索到最後的銷售訂單號。

返回大型結果集。

使用 JOIN 子句;一般情況下,使用該子句的是外鍵列。

使用 ORDER BY 或 GROUP BY 子句。

在 ORDER BY 或 GROUP BY
子句中指定的列的索引,可以使資料庫引擎不必對數據進行排序,因為這些行已經排序。這樣可以提高查詢性能。

列注意事項

一般情況下,定義聚集索引鍵時使用的列越少越好。考慮具有下列一個或多個屬性的列:

唯一或包含許多不重復的值

例如,雇員 ID 唯一地標識雇員。EmployeeID 列的聚集索引或 PRIMARY KEY
約束將改善基於雇員 ID 號搜索雇員信息的查詢的性能。另外,可對
LastName、FirstName、MiddleName
列創建聚集索引,因為經常以這種方式分組和查詢雇員記錄,而且這些列的組合還可提供高區分度。

按順序被訪問

例如,產品 ID 唯一地標識 AdventureWorks2008R2 資料庫的
Proction.Proct 表中的產品。在其中指定順序搜索的查詢(如 WHERE ProctID BETWEEN 980
and 999)將從 ProctID 的聚集索引受益。這是因為行將按該鍵列的排序順序存儲。

由於保證了列在表中是唯一的,所以定義為 IDENTITY。

經常用於對表中檢索到的數據進行排序。

按該列對表進行聚集(即物理排序)是一個好方法,它可以在每次查詢該列時節省排序操作的成本。

聚集索引不適用於具有下列屬性的列:

頻繁更改的列

這將導致整行移動,因為資料庫引擎必須按物理順序保留行中的數據值。這一點要特別注意,因為在大容量事務處理系統中數據通常是可變的。

寬鍵

寬鍵是若干列或若干大型列的組合。所有非聚集索引將聚集索引中的鍵值用作查找鍵。為同一表定義的任何非聚集索引都將增大許多,這是因為非聚集索引項包含聚集鍵,同時也包含為此非聚集索引定義的鍵列。

索引選項

創建聚集索引時,可指定若干索引選項。因為聚集索引通常都很大,所以應特別注意下列選項:

SORT_IN_TEMPDB

DROP_EXISTING

FILLFACTOR

ONLINE

非聚集索引包含索引鍵值和指向表數據存儲位置的行定位器。有關非聚集索引體系結構的詳細信息,請參閱非聚集索引結構。

可以對表或索引視圖創建多個非聚集索引。通常,設計非聚集索引是為改善經常使用的、沒有建立聚集索引的查詢的性能。

與使用書中索引的方式相似,查詢優化器在搜索數據值時,先搜索非聚集索引以找到數據值在表中的位置,然後直接從該位置檢索數據。這使非聚集索引成為完全匹配查詢的最佳選擇,因為索引包含說明查詢所搜索的數據值在表中的精確位置的項。例如,為了從
Person.Person 表中查詢具有特定姓氏的人員,查詢優化器可能使用非聚集索引
IX_Person_LastName_FirstName_MiddleName;它以 LastName 作為自己的一個鍵列。查詢優化器能快速找出索引中與指定
LastName
匹配的所有項。每個索引項都指向表或聚集索引中准確的頁和行,其中可以找到相應的數據。在查詢優化器在索引中找到所有項之後,它可以直接轉到准確的頁和行進行數據檢索。

資料庫注意事項

設計非聚集索引時需要注意資料庫的特徵。

更新要求較低但包含大量數據的資料庫或表可以從許多非聚集索引中獲益從而改善查詢性能。與全表非聚集索引相比,考慮為定義完善的數據子集創建篩選索引可以提高查詢性能、降低索引存儲開銷並減少索引維護開銷。

決策支持系統應用程序和主要包含只讀數據的資料庫可以從許多非聚集索引中獲益。查詢優化器具有更多可供選擇的索引用來確定最快的訪問方法,並且資料庫的低更新特徵意味著索引維護不會降低性能。

聯機事務處理應用程序和包含大量更新表的資料庫應避免使用過多的索引。此外,索引應該是窄的,即列越少越好。

一個表如果建有大量索引會影響
INSERT、UPDATE、DELETE 和 MERGE
語句的性能,因為當表中的數據更改時,所有索引都須進行適當的調整。

查詢注意事項

在創建非聚集索引之前,應先了解訪問數據的方式。考慮對具有以下屬性的查詢使用非聚集索引:

使用 JOIN 或 GROUP BY
子句。

應為聯接和分組操作中所涉及的列創建多個非聚集索引,為任何外鍵列創建一個聚集索引。

不返回大型結果集的查詢。

創建篩選索引以覆蓋從大型表中返回定義完善的行子集的查詢。

包含經常包含在查詢的搜索條件(例如返回完全匹配的 WHERE 子句)中的列。

列注意事項

考慮具有以下一個或多個屬性的列:

覆蓋查詢。

當索引包含查詢中的所有列時,性能可以提升。查詢優化器可以找到索引內的所有列值;不會訪問表或聚集索引數據,這樣就減少了磁碟
I/O 操作。使用具有包含列的索引來添加覆蓋列,而不是創建寬索引鍵。有關詳細信息,請參閱
具有包含列的索引


如果表有聚集索引,則該聚集索引中定義的列將自動追加到表上每個非聚集索引的末端。這可以生成覆蓋查詢,而不用在非聚集索引定義中指定聚集索引列。例如,如果一個表在
C 列上有聚集索引,則 B 和 A 列的非聚集索引將具有其自己的鍵值列 B、A 和 C。

大量非重復值,如姓氏和名字的組合(前提是聚集索引被用於其他列)。

如果只有很少的非重復值,例如僅有 1 和
0,則大多數查詢將不使用索引,因為此時表掃描通常更有效。對於這種類型的數據,應考慮對僅出現在少數行中的非重復值創建篩選索引。例如,如果大部分值都是
0,則查詢優化器可以對包含 1 的數據行使用篩選查詢。

索引選項

在創建非聚集索引時,可以指定若干索引選項。要尤其注意以下選項:

FILLFACTOR

ONLINE

D. vfp中給表建立兩個索引為什麼在資料庫中只顯示一個索引呢

是復合
索引文件
,兩個索引包括在一個索引文件中。
Use
<表>
Order
<索引1>
在打開表的同時打開<索引1>
Use
<表>
Order
<索引2>
在打開表的同時打開<索引2>

E. SQL在一個表中可以有多個聚簇索引嗎為什麼

當然不可以,可以有多個非聚集

聚集索引是要排序的,比如10個人,先按大小個排

同時這十個人按年齡排

是不行的

F. MySQL會對一個表只使用一個索引嗎

不是,對於一個表你可以自由創建索引,沒有數量限制,但是使用索引過多會影響數據的更新操作,如Update和delete等等,但是查詢的時候,一個表只能使用一個索引,如果Mysql發現你的查詢語句中使用了多個索引它會通過內部的優化器優化你的SQL語句,只使用其中一個最優的索引。

G. 做一次查詢時,mysql的表上只能使用一個索引嗎

不是,一般資料庫會根據sql語句自己判斷分析執行計劃,選擇最優的執行計劃執行。
如果你的表有多個索引,一般而言只要能提升查詢性能,就會被使用,不僅僅只能使用一個索引。但是索引建多了也會導致insert,delete等開銷增大

H. 資料庫中的每一張表能建立幾個主索引

資料庫中的每一張表只能建立一個主索引。

主索引是指在指定的索引欄位或表達式中不允許出現重復值的索引,檢索關鍵字里包含主關鍵字。主索引主要用於在永久性關系的父表或被引用表裡建立參照完整性,它能確保輸入欄位值的唯一性,並且由該欄位決定處理記錄的順序。一個資料庫表只能有一個主索引。

(8)sql表索引為什麼只有一個擴展閱讀:

如果在已經包含了重復數據欄位中指定主索引,資料庫將返回出錯提示信息,如表中已經存在一個主索引,再建立索引只能用候選索引或者普通索引。

teradata的每一個表都必須要主索引,並且主索引可以是unique和not unique兩種。如果建表的時候不存在,那麼系統將默認第一列為主索引。這關繫到teradata的並行設計理念。

主索引提供指向存儲在表的指定列中的數據值的指針,然後根據指定的排序順序對這些指針排序。資料庫使用索引以找到特定值,然後順指針找到包含該值的行。這樣可以使對應於表的SQL語句執行得更快,可快速訪問資料庫表中的特定信息。

I. 主鍵 外鍵 索引 為什麼主鍵只有一個 主鍵與索引是什麼關系

主鍵:保證數據完整唯一性。 外鍵:是關聯另外一個表主鍵的一個鍵,保證兩個表之間的關聯性 索引:加快搜索效率 首先主鍵和索引從目的上來講不是一個概念,主鍵是為了保證數據唯一性,索引是為了加快檢索速度。但是,從實現方式上主鍵主鍵卻達到了唯一 非空聚類索引的效果。所以 1. 從實現目的來看(約束) 主鍵約束=唯一非空 約束 2. 從實現效果來看 主鍵 = 唯一 外鍵(Foreign Key) 如果公共關鍵字在一個關系中是主關鍵字,那麼這個公共關鍵字被稱為另一個關系的外鍵。由此可見,外鍵表示了兩個關系之間的聯系。以另一個關系的外鍵作主關鍵字的表被稱為主表,具有此外鍵的表被稱為主表的從表。外鍵又稱作外關鍵字。 外鍵的作用: 保持數據一致性,完整性,主要目的是控制存儲在外鍵表中的數據。 使兩張表形成關聯,外鍵只能引用外表中的列的值!例如:a b 兩個表 a表中存有客戶號,客戶名稱 b表中存有每個客戶的訂單有了外鍵後你只能在確信b 表中沒有客戶x的訂單後,才可以在a表中刪除客戶x 建立外鍵的前提: 本表的列必須與外鍵類型相同(外鍵必須是外表主鍵)。 指定主鍵關鍵字: foreign key(列名) 引用外鍵關鍵字: references <外鍵表名(外鍵列名) 事件觸發限制: on delete和on update , 可設參數cascade(跟隨外鍵改動), restrict(限制外表中的外鍵改動),set Null(設空值),set Default(設默認值),[默認]no action例如:outTable表 主鍵 id 類型 int 創建含有外鍵的表: create table temp( id int, name char(20), foreign key(id) references outTable(id) on delete cascade on update cascade); 說明:把id列 設為外鍵 參照外表outTable的id列 當外鍵的值刪除 本表中對應的列篩除 當外鍵的值改變 本表中對應的列值改變。 建鍵幾個原則: 1、 為關聯欄位創建外鍵。 2、 所有的鍵都必須唯一。 3、避免使用復合鍵。 4、外鍵總是關聯唯一的鍵欄位。