搜尋
首頁資料庫mysql教程MySql索引怎麼創建

1、B 樹索引

顧名思義,結構是B 樹的索引就是B 樹索引,一般情況下,InnoDb引擎中建立的常規索引都是B 的結構。

B 樹索引就是以下這幾種。

1.1、聚集索引/叢集索引

定義主鍵時,主鍵上自動追加的索引就是聚集索引,也稱為叢集索引。

在Mysql中,使用元件建立B 樹結構,如圖所示,每個葉子節點對應一個主鍵,同時也對應著其他相關資料。

MySql索引怎麼創建

如果我們建立表格時沒有定義主鍵,Mysql也會自動建立一個主鍵和對應的索引,主鍵名稱是rowId

1.2、輔助索引/二級索引

輔助索引,也稱為二級索引,是指對於非主鍵列column所建立的索引。同樣的,Mysql會為這個索引建立一個B 樹,樹的葉子節點除了包含這個列column的值以外,就只包含這個列所在行的主鍵值,這樣透過列的索引就可以查到葉子節點,然後葉子節點中的主鍵資訊再從主鍵的索引中搜索,最後得到一整行的資料。

透過二級索引找到主鍵,再從主鍵得到一整行資料的行為叫做回表。

MySql索引怎麼創建

1.3、聯合索引/複合索引

1.3.1、什麼是複合索引

聚合索引可以說是二級索引的一種特殊情況。一般二級索引都是只對一個非主鍵的欄位新增索引,而聚合索引則是一次同時對多個欄位同時新增索引。

一般的二級索引用這樣的語句建立:

CREATE INDEX  order_name_index on t_order(order_name);

複合索引則是這樣建立:

CREATE INDEX  order_name_and_order_type_index on t_order(order_name, order_type);

對於複合索引,Mysql會也會建立一個B 樹,但因為是多個欄位的索引,所以B 樹的排序規則比較特殊,是遵循最左原則。下面會講到什麼是最左原則。

之後葉子節點包含的資訊有多個,一個是作為索引的各個列的值,另一個就是主鍵的值。

1.3.2、最左原則

所謂的最左原則是,B 樹的排序規則是根據索引定義時,定義的語句中的列名從左到右進行排序。

例如定義語句如下:

CREATE INDEX  joint_index on t_order(order_name, order_type, submit_time);

那排序規則是先排order_name,如果order_name相同,再排order_type,最後排submit_time

那當我們查詢時,根據定義時列的順序從左到右,where子句或order by等子句應該盡量先從order_name開始,然後以此類推。

比如說,我們已經定義了上面的三個欄位組成的複合索引,那麼查詢或是排序的時候盡量先order_name,再order_type,最後submit_time

select * from t_order where order_name = 'order1'
and order_type = 1
and submit_time = str_to_date('2022-08-02 00:52:26', '%Y-%m-%d %T')

原因很簡單,因為聯合索引的排序規則是先排order_name,如果order_name相同,再排order_type,最後排submit_time。所以只有在查詢排序時也遵循這個規則,我們才能用上索引。

如果我們不完全遵守最左原則,例如查詢排序只排兩個列,忽略中間那個order by order_name, submit_time。那這個時候Mysql會有智慧化的處理,他會自己判斷是用索引快還是不用索引快。

1.3.3、聯合索引的查詢最佳化

盡量使用到組成聯合索引的列,並且保證順序。可以透過查詢索引查看列的順序。查看sql_in_index

show index from t_order;

MySql索引怎麼創建

查詢傳回的欄位盡量只傳回組成聯合索引的欄位和主鍵,不要傳回其它的資料列,以免造成回表。
這應該很容易理解,因為聯合索引的B 樹的葉子節點就只包含主鍵和組成聯合索引的列的值,如果傳回的欄位就這幾列,那在一個B 樹種查詢就完事了。如果還要回傳其它的列的話,就又要去主鍵的索引中查找,有回表操作。

2、雜湊索引

一般資料庫都會用B 樹索引查詢數據,但是當資料庫使用一段時間後,InnoDB 會記錄一些使用頻率較高的熱數據,然後為這些熱數據建立哈希結構的索引,這就是哈希索引的應用場景。

這個索引在Mysql 5.7開始預設為開啟。

2.1、查看哈希索引的命中率等資訊

使用語句:

show engine innodb status;

MySql索引怎麼創建

##其中的

status有許多訊息,其中就包括哈希索引的情況。我們把資訊複製到編輯器中查看。其中的這一段就是哈希索引的資訊。

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 5 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
-- 哈希索引的命中率,可根据这个来决定是否使用哈希索引
0.00 hash searches/s, 0.00 non-hash searches/s
---

3、索引的创建策略

3.1、 单列索引的策略

3.1.1、列的类型占用的空间越小,越适合作为索引

因为B+树也是占用空间的,所以在固定空间中,如果列的类型占用的空间越小,那我们一次就能读取更多的B+树节点,这样自然就加快了效率。

3.1.2、根据列的值的离散性

离散性是指数据的值重复的程度高不高,假如有N条数据的话,那离散性就可以用数值表示,范围是1/N 到 1。

比如说某个列在数据库中有下面几条数据(1, 2, 3, 4, 5, 5, 3),其中5和3都有重复,去重后应该是(1, 2, 3, 4, 5)。我们将去重后的条数除以总条数就得到离散性。这里是5/7。列中重复数据较多时,对应的数值较小,而重复数据较少时,数值相应较大。

如果一个列的数据的重复性越低,那么这个列就越适合加索引。

因为索引是需要起到筛选的作用。比如我们有个where条件是where id = 1,如果数据重复性较高,那可能根据索引会返回100条数据,然后我们在根据其他where条件在100条数据中再筛选。

如果数据重复性较低,那可能就只返回1条数据,那之后的运算量明显小得多。

所以一个列的数据离散性越高,那这个列越适合添加索引。

我们可以用下面的语句得到某个列的离散性程度。

select count(distinct id)/count(*) form t_table;
3.1.3、前缀索引

前缀索引和后缀索引:

有些列的值比较长,比如一些备注日志信息也会记录在数据库当中,这类信息的长度往往比较长,如果我们需要对这类列加索引,那索引并不是索引字符串的全部长度。这时候我们就可以建立前缀索引,即对字符串的前面几位建立索引。

所以前缀索引就是建立范围更小索引,选择一个好前缀位数就能有一个更好的查询效率。

不过有一些缺点,就是这类索引无法应用到order bygroup语句上。

Mysql没有后缀索引,如果非要实现后缀索引,那在数据存储时我们应该将数据反转,这样就能用前缀索引达到后缀索引的效果。后缀索引的一个经典应用就是邮箱,快速查询某种类型的邮箱。

选择前缀索引的位数:

这里的逻辑和列的离散性类似,我们需要看看字符串的前面几位的子字符串的离散性如何。比如对于下面的表,内容是电影票的相关信息,我们需要对order_note建立前缀索引。

MySql索引怎麼創建

来比较一下各个位的子字符串的离散性。

SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(order_note,4))/COUNT(*)AS sel4,
COUNT(DISTINCT LEFT(order_note,5))/COUNT(*) AS sel5,
COUNT(DISTINCT LEFT(order_note, 6))/COUNT(*) As sel6,
COUNT(DISTINCT LEFT(order_note, 7))/COUNT(*) As sel7,
COUNT(DISTINCT LEFT(order_note, 8))/COUNT(*) As sel8,
COUNT(DISTINCT LEFT(order_note, 9))/COUNT(*) As sel9,
COUNT(DISTINCT LEFT(order_note, 10))/COUNT(*) As sel10,
COUNT(DISTINCT LEFT(order_note, 11))/COUNT(*) As sel11,
COUNT(DISTINCT LEFT(order_note, 12))/COUNT(*) As sel12,
COUNT(DISTINCT LEFT(order_note, 13))/COUNT(*) As sel13,
COUNT(DISTINCT LEFT(order_note, 14))/COUNT(*) As sel14,
COUNT(DISTINCT LEFT(order_note, 15))/COUNT(*) As sel15,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;

![在这里插入图片描述](https://img-blog.csdnimg.cn/33a12fadd99944098e91f883d6bfaa2f.png #pic_center =x80)
可以看出,前面几位的子字符串的离散程度较低,后面sel13开始就比较高,那我们可以根据实际情况,建立13~15位的前缀索引。

建立前缀索引SQL语句:

alter table order_exp add key(order_note(13));
3.1.2、只为搜索、排序和分组的列建索引

这个理由很简单,不解释了。

3.2、 多列索引的策略

3.2.1、离散性最高的列放前面

原因很简单,查询时根据定义复合索引时的列的顺序来查询的,离散性高的列放在前面的话,就能更早的将更多的数据排除在外。

3.2.2、三星索引

三星索引是一种策略。有三种条件,满足一条则索引获得一颗星,三颗星则是很好的索引。

三条策略分别是

索引将相关记录放在一起。

意思是查询需要的数据在索引树的叶子节点中连续或者足够靠近。举个例子,下面是某个索引的B+树。查询所需数据仅在叶节点的前两个范围内,即0000至a。这很明显,后面的片我们就没必要再去查询了,这无疑增加了效率。当所需数据分布在每个片上时,查询次数就会显著增加。

所以查询需要的数据在叶子节点上越连续,越窄就越好。

MySql索引怎麼創建

索引中的数据顺序与查找中的数据排序一致。

这容易理解,讲解联合索引中说过,B+树的排序顺序和索引中的数据一样,所以查询时的where的数据顺序越贴近索引中的顺序,就越能更好地利用B+树。

索引的列包含查询中的所有列。

这个可以避免回文操作,不多解释。

三星索引的权重:

一般来说第三个策略权重占到50%,之后是第一个策略27%, 第二个策略23%。

三星索引实例:

CREATE TABLE customer (
	cno INT,
	lname VARCHAR (10),
	fname VARCHAR (10),
	sex INT,
	weight INT,
	city VARCHAR (10)
);

CREATE INDEX idx_cust ON customer (city, lname, fname, cno);

我们创建以上的索引,那么对于下面的查询语句,这个索引就是三星索引。

select cno,fname from customer where lname='xx' and city ='yy' order by fname;

首先,查询条件中有lname=’xx’ and city =’yy’,这条件让我们这需要在lname=’xx’ and city =’yy’的那一片B+树的叶子节点中查询,让我们的查询变窄了很多,并且这部分的数据是连续的,因为B+树是先根据city排序,再根据lname查询。

另外,因为已经锁定lname=’xx’ and city =’yy’,所以这部分的数据是根据fname和cno排序。查询语句正好是根据`fname```排序,所以第二点也满足。

最后是查询的结果都包含正在索引中,不会有回文,第三点也满足,所以这个索引是三星索引。

以上是MySql索引怎麼創建的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述
本文轉載於:亿速云。如有侵權,請聯絡admin@php.cn刪除
您如何處理MySQL中的數據庫升級?您如何處理MySQL中的數據庫升級?Apr 30, 2025 am 12:28 AM

MySQL數據庫升級的步驟包括:1.備份數據庫,2.停止當前MySQL服務,3.安裝新版本MySQL,4.啟動新版本MySQL服務,5.恢復數據庫。升級過程需注意兼容性問題,並可使用高級工具如PerconaToolkit進行測試和優化。

您可以使用MySQL的不同備份策略是什麼?您可以使用MySQL的不同備份策略是什麼?Apr 30, 2025 am 12:28 AM

MySQL備份策略包括邏輯備份、物理備份、增量備份、基於復制的備份和雲備份。 1.邏輯備份使用mysqldump導出數據庫結構和數據,適合小型數據庫和版本遷移。 2.物理備份通過複製數據文件,速度快且全面,但需數據庫一致性。 3.增量備份利用二進制日誌記錄變化,適用於大型數據庫。 4.基於復制的備份通過從服務器備份,減少對生產系統的影響。 5.雲備份如AmazonRDS提供自動化解決方案,但成本和控制需考慮。選擇策略時應考慮數據庫大小、停機容忍度、恢復時間和恢復點目標。

什麼是mySQL聚類?什麼是mySQL聚類?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

如何優化數據庫架構設計以在MySQL中的性能?如何優化數據庫架構設計以在MySQL中的性能?Apr 30, 2025 am 12:27 AM

在MySQL中優化數據庫模式設計可通過以下步驟提升性能:1.索引優化:在常用查詢列上創建索引,平衡查詢和插入更新的開銷。 2.表結構優化:通過規範化或反規範化減少數據冗餘,提高訪問效率。 3.數據類型選擇:使用合適的數據類型,如INT替代VARCHAR,減少存儲空間。 4.分區和分錶:對於大數據量,使用分區和分錶分散數據,提升查詢和維護效率。

您如何優化MySQL性能?您如何優化MySQL性能?Apr 30, 2025 am 12:26 AM

tooptimizemysqlperformance,lofterTheSeSteps:1)inasemproperIndexingTospeedUpqueries,2)使用ExplaintplaintoAnalyzeandoptimizequeryPerformance,3)ActiveServerConfigurationStersLikeTlikeTlikeTlikeIkeLikeIkeIkeLikeIkeLikeIkeLikeIkeLikeNodb_buffer_pool_sizizeandmax_connections,4)

如何使用MySQL的函數進行數據處理和計算如何使用MySQL的函數進行數據處理和計算Apr 29, 2025 pm 04:21 PM

MySQL函數可用於數據處理和計算。 1.基本用法包括字符串處理、日期計算和數學運算。 2.高級用法涉及結合多個函數實現複雜操作。 3.性能優化需避免在WHERE子句中使用函數,並使用GROUPBY和臨時表。

MySQL批量插入數據的高效方法MySQL批量插入數據的高效方法Apr 29, 2025 pm 04:18 PM

MySQL批量插入数据的高效方法包括:1.使用INSERTINTO...VALUES语法,2.利用LOADDATAINFILE命令,3.使用事务处理,4.调整批量大小,5.禁用索引,6.使用INSERTIGNORE或INSERT...ONDUPLICATEKEYUPDATE,这些方法能显著提升数据库操作效率。

給MySQL表添加和刪除字段的操作步驟給MySQL表添加和刪除字段的操作步驟Apr 29, 2025 pm 04:15 PM

在MySQL中,添加字段使用ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column,刪除字段使用ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop。添加字段時,需指定位置以優化查詢性能和數據結構;刪除字段前需確認操作不可逆;使用在線DDL、備份數據、測試環境和低負載時間段修改表結構是性能優化和最佳實踐。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱工具

MantisBT

MantisBT

Mantis是一個易於部署的基於Web的缺陷追蹤工具,用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。

EditPlus 中文破解版

EditPlus 中文破解版

體積小,語法高亮,不支援程式碼提示功能

SublimeText3 英文版

SublimeText3 英文版

推薦:為Win版本,支援程式碼提示!

SublimeText3 Linux新版

SublimeText3 Linux新版

SublimeText3 Linux最新版

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器