-?點擊上方“中國統計網”訂閱我吧！-

索引在數據庫中是一個很特殊的存在，它的目的就是為了提高數據查詢得效率。同樣，它也有弊端，更新一個帶索引的表的時間比更新一個沒有帶索引的時間更長。有得有失。我希望做一些研究測試，搞清楚索引對于我們使用數據庫有什么影響，以及如何控制這個影響。

聚集索引和非聚集索引

聚集索引根據數據行的鍵值在表或視圖中排序和存儲這些數據行。索引定義中包含聚集索引列。每個表只能有一個聚集索引，因為數據行本身只能按一個順序排序。只有當表包含聚集索引時，表中的數據行才按排序順序存儲。如果表具有聚集索引，則該表稱為聚集表。如果表沒有聚集索引，則其數據行存儲在一個稱為堆的無序結構中。

聚集索引特殊的方面是：聚集索引的葉級是實際的數據-也就是說，數據重新排序，按照和聚集索引排序條件聲明的相同物理順序存儲。這意味著，一旦到達索引的葉級，就到達了數據。而非聚集索引，到達了葉級只是找到了數據的引用。因此聚集索引帶來的空間消耗是需要額外的120%的空間。

任何新記錄都根據聚集列正確的物理順序插入到聚集索引中。創建新頁的方式隨需要插入記錄的位置而變化。如果新記錄需要插入到索引結構中間，就會發生正常的頁拆分。來自舊頁的后一半記錄被移到新頁，并且在適當的時候，將新記錄插入到新頁或舊頁。如果新記錄在邏輯上位于索引結構的末端，那么創建新頁，但是只將新記錄添加到新頁。在MySQL中，聚集索引可以認為是唯一索引。

非聚集索引中的項目按索引鍵值的順序存儲，而表中的信息按另一種順序存儲(這可以由聚集索引規定)。對于非聚集索引，可以為在表非聚集索引中查找數據時常用的每個列創建一個非聚集索引。非聚集索引中的項目按索引鍵值的順序存儲，而表中的信息按另一種順序存儲(這可以由聚集索引規定)。對于非聚集索引，可以為在表非聚集索引中查找數據時常用的每個列創建一個非聚集索引。在MySQL中，費聚集

引用一下網上常見的使用聚集索引和非聚集索引的使用上的注意事項：

本文并非探索索引的數據結構以及不同數據庫的索引區別，對于數據分析而言也不是很重要，關鍵是搞懂索引的作用、索引的區別以及怎樣使用索引。

測試環境和數據

不介紹測試環境的探究都是耍流氓，下面介紹一下測試所使用的環境和數據。

機器是兩臺IBM的PC，一臺作為client，一臺作為data server；PC的硬件是I5-4590CPU 3.3GHz+4GRAM(client)，I7-4790 3.6GHZ+8GRAM(server)；client和server內網連接；client是win7 32位，server是win7 64位操作系統。

測試的數據庫分別是MS SQLServer 2008R2版本和MySQL Server5.5版本。

分別再MySQL和SQLServer的測試數據庫建了三個表student_no(無索引)，student_in(有非聚集索引)，student_cin(有聚集索引)，表的字段都是一致的，參考下面的建表sql。

CREATE TABLE `student_xx` ( `ID` int(11) NOT NULL, `name` varchar(10) NOT NULL, `age` int(3) NOT NULL, `address` varchar(10) NOT NULL)

注意建表的過程中，我默認有幾個設置，ID是所有數據都不一樣的，是唯一標識符，name基本上所有人也不一樣，age的數據比較一致在[20,30]這個區間，addres只記錄所在省份城市的名字，數據范圍在300個以內。

測試結果

測試使用的手段無非是對數據庫的增刪改查操作(where語句包含對應聚集索引和非聚集索引的)，但是也有細分，我這里進行詳細的定義。

1. 插入操作

I1: 逐條插入，類似insert into student (ID, name,age,address) values(10000001,‘HE KEJUN’,28,’GUANGDONGG-GUANGZHOU’)，每插入100條記錄并記錄耗時

I2：批量插入，即10次每次進行插入100條記錄

2. 修改操作

U1：修改某個字段

U2：修改多個字段，每修改100條記錄并記錄耗時

3. 刪除操作

D1：刪除某條記錄(含where語句)，每刪除100條記錄并記錄耗時。

4. 查詢操作

S1：查詢數據

S2：查詢中帶有COUNT函數

S3：查詢中帶有SUM函數

S4：查詢中帶有ORDER BY

上述操作都是每查詢1000條記錄并記錄耗時。

我們首先看看在小數量級的數據中索引的不同表現。

在小數據集(數據表記錄數=10000)的情況下，各個數據庫操作的耗時如下：

	MSSQL			MYSQL
	無索引	非聚集索引	聚集索引	無索引	非聚集索引	聚集索引
I1	0.80935	0.81045	0.8031	1.07525	0.782	0.79005
I2	2.1868	2.1891	2.13555	0.15565	0.1688	0.1478
D1	0.95685	0.60885	0.7253	0.9924	0.50565	0.748
U1	0.9642	0.78035	0.7368	0.85725	0.8601	0.78205
U2	0.8759	0.74055	0.70125	0.83575	0.53745	0.7721
S1	3.2293	2.5016	1.92195	9.2856	2.22845	1.85555
S2	2.963	2.051	2.0203	9.39085	2.2397	2.62835
S3	3.1109	2.7788	2.0137	9.38075	2.26215	2.74975
S4	9.6427	9.6553	7.2891	10.2789	5.9647	4.9492

在MS SQLSever中，索引不會明顯影響數據庫進行插入和刪除操作。在大多數情況下，聚集索引的效率都比非聚集索引的高。即聚集索引在小規模數量級的數據表中是最佳選擇。

同樣的情況出現在MySQL數據庫，索引對于查詢的耗時的降低是非常明顯的。計劃以后在百萬級的數據表中再重新設計做一次實驗再分享給大家。

End.

作者：柯君

來源：博客園

·?零基礎入職數據分析就業班 ·

課程形式主為“直播+錄播”

課程專享：月考測試通關+課程項目作業+1v1職場生涯規劃+班主任輔導學習+資深講師答疑

課程結束后能熟練掌握SQL、Python、Excel、PPT等數據分析工具

金3銀4招聘季，愛數據帶你成功入職數據分析！

點擊“閱讀原文”入職數據分析

總結

以上是生活随笔為你收集整理的非索引查询如何提高效率_Python数据分析：数据库索引如何提高效率？的全部內容，希望文章能夠幫你解決所遇到的問題。

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