一文了解 MySQL 索引及其原理

Mysql索引

1.數(shù)據(jù)庫(kù)中有哪些索引類(lèi)型?

2.數(shù)據(jù)庫(kù)索引底層實(shí)現(xiàn)

3. 為什么選用B+樹(shù)？

4. 哈希索引與B+樹(shù)如何選用？

5. 聚集索引和非聚集索引的區(qū)別

6. 什么情況下設(shè)置了索引但是會(huì)失效？

MySQL索引

1.數(shù)據(jù)庫(kù)中有哪些索引類(lèi)型?

2.數(shù)據(jù)庫(kù)索引底層實(shí)現(xiàn)

3. 為什么選用B+樹(shù)？

4. 哈希索引與B+樹(shù)如何選用？

5. 聚集索引和非聚集索引的區(qū)別

6. 什么情況下設(shè)置了索引但是會(huì)失效？

MySQL索引

1.數(shù)據(jù)庫(kù)中有哪些索引類(lèi)型?

索引的幾種類(lèi)型：唯一索引、主鍵索引、聚集索引、普通索引、組合索引、全文索引

唯一索引：在創(chuàng)建唯一索引時(shí)要不能給具有相同的索引值。在MySQL中創(chuàng)建索引：

CREATE UNIQUE INDEX indexName ON mytable(username(length));

主鍵索引：在我們給一個(gè)字段設(shè)置主鍵的時(shí)候，它就會(huì)自動(dòng)創(chuàng)建主鍵索引，用來(lái)確保每一個(gè)值都是唯一的。

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

聚集索引：我們?cè)诒碇刑砑訑?shù)據(jù)的順序，與我們創(chuàng)建的索引鍵值相同，而且一個(gè)表中只能有一個(gè)聚集索引。

普通索引：它的結(jié)構(gòu)主要以B+樹(shù)和哈希索引為主，主要是對(duì)數(shù)據(jù)表中的數(shù)據(jù)進(jìn)行精確查找。

CREATE INDEX indexName ON mytable(username(length)); ALTER TABLE mytable ADD INDEX username(length);

**組合索引：**一個(gè)表中含有多個(gè)單列索引不代表是組合索引,通俗一點(diǎn)講組合索引是:包含多個(gè)字段但是只有索引名稱(chēng)

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

全文索引：它的作用是搜索數(shù)據(jù)表中的字段是不是包含我們搜索的關(guān)鍵字，就像搜索引擎中的模糊查詢(xún)。

ALTER TABLE tablename ADD FULLTEXT(column1, column2)

使用索引的優(yōu)點(diǎn)：

提高數(shù)據(jù)的搜索速度

加快表與表之間的連接速度

在信息檢索過(guò)程中，若使用分組及排序子句進(jìn)行時(shí)，通過(guò)建立索引能有效的減少檢索過(guò)程中所需的分組及排序時(shí)間，提高檢索效率。

使用索引的缺點(diǎn):

在我們建立數(shù)據(jù)庫(kù)的時(shí)候，需要花費(fèi)的時(shí)間去建立和維護(hù)索引，而且隨著數(shù)據(jù)量的增加，需要維護(hù)它的時(shí)間也會(huì)增加。

在創(chuàng)建索引的時(shí)候會(huì)占用存儲(chǔ)空間。

在我們需要修改表中的數(shù)據(jù)時(shí)，索引還需要進(jìn)行動(dòng)態(tài)的維護(hù)，所以對(duì)數(shù)據(jù)庫(kù)的維護(hù)帶來(lái)了一定的麻煩。

2.數(shù)據(jù)庫(kù)索引底層實(shí)現(xiàn)

不懂?dāng)?shù)據(jù)庫(kù)索引的底層原理？

MySQL中，索引的底層實(shí)現(xiàn)構(gòu)主要是：哈希索引和B+樹(shù)索引。

哈希索引的底層數(shù)據(jù)結(jié)構(gòu)就是哈希表，查詢(xún)性能最快；

B樹(shù)索引：B樹(shù)實(shí)現(xiàn)

B+樹(shù)索引：B+樹(shù)實(shí)現(xiàn)

3. 為什么選用B+樹(shù)？

紅黑樹(shù)等數(shù)據(jù)結(jié)構(gòu)也可以用來(lái)實(shí)現(xiàn)索引，但是文件系統(tǒng)以及數(shù)據(jù)庫(kù)系統(tǒng)普遍采用B-Tree/B+Tree作為索引結(jié)構(gòu)。

原因：索引本身也很大，因此索引往往是以索引文件的形式存儲(chǔ)在磁盤(pán)上。所以，索引查找的過(guò)程就會(huì)產(chǎn)生磁盤(pán)的I/O操作，相比于內(nèi)存存取，I/O存取消耗要高幾個(gè)數(shù)量級(jí)，所以索引的優(yōu)劣最重要的指標(biāo)就是在查找過(guò)程中的磁盤(pán)I/O存取次數(shù)。

4. 哈希索引與B+樹(shù)如何選用？

Hash索引是無(wú)序的，所以只適用于等值查詢(xún)，而不能用于范圍查詢(xún)。大多數(shù)場(chǎng)景下，都會(huì)有范圍查詢(xún)、排序、分組等查詢(xún)特征，用B+樹(shù)索引就可以了。

5. 聚集索引和非聚集索引的區(qū)別

聚集索引：數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個(gè)表中只能擁有一個(gè)聚集索引。

非聚集索引：該索引中索引的邏輯順序與磁盤(pán)上行的物理存儲(chǔ)順序不同，一個(gè)表中可以擁有多個(gè)非聚集索引。

區(qū)別：

使用聚集索引的查詢(xún)效率要比非聚集索引的效率要高，但是如果需要頻繁去改變聚集索引的值，寫(xiě)入性能并不高，因?yàn)樾枰苿?dòng)對(duì)應(yīng)數(shù)據(jù)的物理位置。

非聚集索引在查詢(xún)的時(shí)候可以的話(huà)就避免二次查詢(xún)，這樣性能會(huì)大幅提升。

不是所有的表都適合建立索引，只有數(shù)據(jù)量大表才適合建立索引，且建立在選擇性高的列上面性能會(huì)更好。

6. 什么情況下設(shè)置了索引但是會(huì)失效？

條件中有or

對(duì)于多列索引，不是使用的第一部分，則不會(huì)使用索引

like查詢(xún)時(shí)以%開(kāi)頭

如果列類(lèi)型是字符串，那一定要在條件中將數(shù)據(jù)使用引號(hào)引用起來(lái),否則不使用索引

如果mysql估計(jì)使用全表掃描要比使用索引快,則不使用索引

MySQL 數(shù)據(jù)庫(kù)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶(hù)投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。