MySQL 系列教程之（十）索引原理：B+ 樹與索引丨【綻放吧！數據庫】

索引與B+Tree

一、Mysql中索引的語法

創建索引

在創建表的時候添加索引

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

在創建表以后添加索引

ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name); -- 或者 CREATE INDEX index_name ON my_table(column_name);

注意：

1、索引需要占用磁盤空間，因此在創建索引時要考慮到磁盤空間是否足夠

2、創建索引時需要對表加鎖，因此實際操作中需要在業務空閑期間進行

刪除索引

DROP INDEX my_index ON tablename； -- 或者 ALTER TABLE table_name DROP INDEX index_name;

查看表中的索引

SHOW INDEX FROM tablename;

二、索引的優缺點

**優勢：**可以快速檢索，減少I/O次數，加快檢索速度；根據索引分組和排序，可以加快分組和排序；

**劣勢：**索引本身也是表，因此會占用存儲空間，一般來說，索引表占用的空間的數據表的1.5倍；索引表的維護和創建需要時間成本，這個成本隨著數據量增大而增大；構建索引會降低數據表的修改操作（刪除，添加，修改）的效率，因為在修改數據表的同時還需要修改索引表;

三、索引的分類

常見的索引類型有：主鍵索引、唯一索引、普通索引、全文索引、組合索引

1、主鍵索引：即主索引，根據主鍵pk_clolum（length）建立索引，不允許重復，不允許空值；

-- 直接修改時添加主鍵和自增 alter table users modify uid int primary key AUTO_INCREMENT; -- 刪除主鍵索引 注意需要先取消 自增,再刪除主鍵 -- 先取消自增,修改字段 alter table users modify uid int; -- 刪除主鍵 alter table users drop primary key; -- 添加主鍵索引 -- alter table users add primary key(uid) -- alter table users modify uid int AUTO_INCREMENT;

2、唯一索引：用來建立索引的列的值必須是唯一的，允許空值

-- 添加唯一索引 UNIQUE 當前列要求唯一,但允許為空 alter table users add unique u_name(uname); -- 刪除唯一索引 根據當前索引名去進行刪除 alter table users drop index u_name;

3、普通索引：用表中的普通列構建的索引，沒有任何限制

-- 添加索引 alter table users add index in_name(email); -- 刪除索引 drop index in_name on users;

4、全文索引：用大文本對象的列構建的索引

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')； -- 5.6版本前的Mysql自帶的全文索引只能用于MyISAM存儲引擎，如果是其它數據引擎，那么全文索引不會生效。--- 5.6版本之后InnoDB存儲引擎開始支持全文索引 -- 在MySQL中，全文索引支隊英文有用，目前對中文還不支持。5.7版本之后通過使用ngram插件開始支持中文。

5、組合索引：用多個列組合構建的索引，這多個列中的值不允許有空值

-- 添加索引 alter table users add index in_x(email,phone,uname); -- 刪除索引 alter table users drop index in_x;

*遵循“最左前綴”原則，把最常用作為檢索或排序的列放在最左，依次遞減，組合索引相當于建立了col1,col1col2,col1col2col3三個索引，而col2或者col3是不能使用索引的。

*在使用組合索引的時候可能因為列名長度過長而導致索引的key太大，導致效率降低，在允許的情況下，可以只取col1和col2的前幾個字符作為索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))； --表示使用col1的前4個字符和col2的前3個字符作為索引

四、索引的實現原理

1、哈希索引：

只有memory（內存）存儲引擎支持哈希索引，哈希索引用索引列的值計算該值的hashCode，然后在hashCode相應的位置存執該值所在行數據的物理位置，因為使用散列算法，因此訪問速度非常快，但是一個值只能對應一個hashCode，而且是散列的分布方式，因此哈希索引不支持范圍查找和排序的功能

2、B+Tree索引

正常情況下，如果不指定索引的類型，那么一般是指B+Tree索引（或者B+Tree索引）。

存儲引擎以不同的方式使用B+Tree索引。性能也各有不同，但是InnoDB按照原數據格式進行存儲。

B+Tree 索引能夠加快數據的讀取速度，因為存儲引擎不再需要進行全表掃描來獲取需要的數據，相反是從索引的根節點開始進行搜索，通過相應的指針移動，最終存儲引擎要么找到了對應的值，要么該記錄不存在。樹的深度與表的大小直接相關。

B+Tree索引是按照順序組織存儲的，所以適合范圍查找數據

B+Tree索引使用與全鍵值、鍵值范圍或者鍵前綴查找，其中鍵前綴進適用于根據最左前綴的查找。

B-Tree 40 22 56 15 35 45 55 75 5 8 48 58

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-X587wPmE-1627555103000)(./imgs/B-Tree.索引.png)]

B+Tree

為什么使用B+樹而不是B樹

在計算機中,所有與空間相關的東西都是按照塊(block)進行存取和操作的.每次讀取都意味著一次I/O

假設計算機中每個塊的大小為4K,行的大小為1k,索引的大小為0.06K,就可以計算并得出結果

B樹的數據和索引都在同一個節點上,那么意味著每一個塊(block)中包含的索引是少量的,如果想要取出比較深層的數據就意味著要讀取很多的快,才能得到想要的索引和數據,那就是I/O的次數會多

而B+樹中每一個塊能夠存儲的索引數量是B樹的很多倍,那么獲取比較深層的數據只需要讀取少量的快(block)就可以做到.那就是I/O的次數會少很多

隨機I/O是指讀寫操作時間連續，但訪問地址不連續,時長約為 10ms

順序I/O是指讀取和寫入操作基于邏輯塊逐個連續訪問來自相鄰地址的數據,時長約為 0.1ms

在相同情況下,B樹要進行更多的隨機IO,而B+樹需要更多的順序IO,因此B+樹,效率也更快

由于B+Tree非葉子節點不存儲數據（data），因此所有的數據都要查詢至葉子節點，而葉子節點的高度都是相同的，因此所有數據的查詢速度都是一樣的。

在索引的分類中，我們可以按照索引的鍵是否為主鍵來分為“主索引”和“輔助索引”，使用主鍵鍵值建立的索引稱為“主索引”，其它的稱為“輔助索引”。因此主索引只能有一個，輔助索引可以有很多個。

MyISAM存儲引擎采用的是非聚簇索引，非聚簇索引的主索引和輔助索引幾乎是一樣的，只是主索引不允許重復，不允許空值，他們的葉子結點的key都存儲指向鍵值對應的數據的物理地址。

非聚簇索引的主索引和輔助索引的葉子節點的data都是存儲的數據的物理地址，也就是說索引和數據并不是存儲在一起的，數據的順序和索引的順序并沒有任何關系，也就是索引順序與數據物理排列順序無關。

聚簇索引的主索引的葉子結點存儲的是鍵值對應的數據本身，輔助索引的葉子結點存儲的是鍵值對應的數據的主鍵鍵值。因此主鍵的值長度越小越好，類型越簡單越好。

聚簇索引的輔助索引的葉子節點的data存儲的是主鍵的值，主索引的葉子節點的data存儲的是數據本身，也就是說數據和索引存儲在一起，并且索引查詢到的地方就是數據（data）本身，那么索引的順序和數據本身的順序就是相同的；

事務的支持: innodb支持事務,myisam不支持事務

存儲方式: innodb由兩個文件組成,一個存表結構,另一個存 數據和索引.myisam由三個文件組成,一個存結構,一個數據,一個索引

數據與索引的聚簇: innodb中主索引是聚簇類型,輔助索引是非聚簇.myisam由于數據和索引不在同一個文件中,主索引和輔助索引都只存數據物理地址,因此是非聚簇索引

【綻放吧！數據庫】有獎征文火熱進行中：https://bbs.huaweicloud.com/blogs/285617

MySQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。