MySQL索引的用途有哪些

這篇文章給大家分享的是有關MySQL索引的用途有哪些的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

　　MySQL 索引的作用是什么?一般回答，加速查詢，減少磁盤 IO.

　　索引為什么可以加速查詢，減少磁盤 IO 呢?

　　因為索引就像一份字典的目錄，可以幫你找到數據的位置。

　　嗯，這只是一個比喻，你知道 MySQL 索引的數據結構是長什么樣子的嗎?

　　呃，MySQL 大多數索引都是用的基于 B-tree 的變種，我們習慣叫它 B+ tree，其他的索引還有 hash 索引、R-trees 索引這些。

　　好，那你能把 B+tree 畫出來，然后講一下它是如何幫助 MySQL 更快的查詢數據的嗎?

　　很多同學都知道索引的作用是什么、索引是 B+ tree 等等，但是卻很少有人能清楚的畫出 B+ tree 是如何幫助 MySQL 加速查詢的，而不知道這個，也就意味著你無法判斷，什么樣的 sql 語句會走索引，什么樣的 sql 語句走不了索引，而知道這個，則從此一通百通。

　　MySQL 的 B+tree 什么樣子?

　　聚簇索引

　　事實上，在你還沒有執行 create index 語句的時候，MySQL 里面就已經有棵 B+ tree了。

　　執行建表語句：

　　CREATE TABLE `student` (

　　`id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '主鍵id',

　　`student_no` VARCHAR(64) COMMENT '學號',

　　`name` VARCHAR(64) COMMENT '學生姓名',

　　`age` INT COMMENT '學生年齡',

　　PRIMARY KEY (`id`)

　　) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='學生信息表';

　　插入 5 條數據：

　　insert into student(student_no,name,age) values(101,"Alice",18);

　　insert into student(student_no,name,age) values(102,"Bob",19);

　　insert into student(student_no,name,age) values(104,"Brandt",15);

　　insert into student(student_no,name,age) values(105,"David",19);

　　insert into student(student_no,name,age) values(109,"David",18);

　　在你插入的過程中，MySQL就會用你指定的主鍵，在這里是遞增主鍵，維護起一顆 B+ 樹，用 BPlusTree Visualization 的模擬工具來模擬這顆樹的樣子：

　　如果你有時間，也建議你到這個網站去，從 1 到 5，一個一個插入，你會看到 B+ tree 在插入的過程中是怎么維護它的幾個特性的：

　　有序：節點左邊比右邊小;

　　自平衡：左右兩邊數量趨于相等;

　　節點分裂：在遇到節點元素過載時，是如何分裂成兩個的，其實這個也是 MySQL 頁分裂的原理。

　　模擬工具只能支持插入一個值，所以你看不到主鍵之外的其他數據，實際上，B+ tree 的葉子節點是帶有行的全部數據的。

　　如果沒有這棵 B+ tree，你要根據主鍵查詢，比如：

　　select * from student where id = 5;

　　對不起，數據是無序的，你只能全表掃描。

　　現在有了這棵 B+ tree，數據被有規律的存儲起來的，查找 id=5 。

　　你要訪問磁盤的次數，是由這棵樹的層數決定的。為了方便說明，我在文章里舉的例子的數據量不會太大，所以用不用索引，性能提升的效果不明顯，但是你可以自行腦補大數據量的畫面。

　　如果你沒有指定主鍵呢?沒關系， 唯一鍵 也可以。

　　連唯一鍵也沒有?也沒關系，mysql會給你建一個 rowid字段 ，用它來組織這棵 B+ tree.

　　反正 MySQL 就一個目的，數據要有規律的存儲起來，就像之前在 link 數據庫是什么 里說的，這是數據庫和文件系統的不一樣的地方。

　　這個 MySQL 無論如何都會建起來，并且存儲有完整行數據的索引，就叫 聚簇索引 (clustered index)。

　　二級索引

　　聚簇索引只能幫你加快主鍵查詢，但是如果你想根據姓名查詢呢?

　　對不起，看看上面這棵樹你就知道，數據并沒有按照姓名進行組織，所以，你還是只能全表掃描。

　　不想全表掃描，怎么辦?那就給姓名字段也加個索引，讓數據按照姓名有規律的進行組織：

　　create index idx_name on student(name);

　　這時候 MySQL 又會建一棵新的 B+ tree。

　　你會發現這棵樹的葉子節點，只有主鍵ID，沒有完整數據，這時候你執行：

　　select * from student where name = "David";

　　MySQL 到你剛剛創建的這棵 B+ tree 查詢，快速的查到有兩條姓名是“David”的記錄，并且拿到它們的主鍵，分別是 4 和 5，但是你是要 select * 呀，怎么辦?

　　別忘了，MySQL 在一開始就給你建了一顆 B+ tree 了，把這兩棵樹，放在一起，拿著這兩個主鍵ID，去聚簇索引找，事情不就解決了?

　　這個不帶行數據信息的索引，就叫 二級索引 (secondary index)，也叫輔助索引。

　　復合索引

　　如果我在根據姓名和年齡同時查詢呢?

　　select * from student where name = "David" and age = 18;

　　還是那個道理，數據雖然按照 name 有規律的組織了，但是沒有按照 age 有規律組織，所以我們要給 name 和 age 同時建索引：

　　create index idx_name_age on student(name,age);

　　這時候 MySQL 又會建一棵 B+ tree，這下 B+ tree 的節點里面，不只有name，還有age了，而且排序時，是先用 name 比較大小，如果 name 相同，則用 age 比較。

　　還是那句話，這里舉的例子數據量很少，你可以想象下有一萬個叫“David”的學生，年齡隨機分布在13到20之間，這時候如果沒有按照 age 進行有規律的存儲，你還是得掃描一萬行數據。

　　別人都只會用公式，他卻時刻牢記這些公式是怎么來的，別人考試就只會套用公司，他卻可以用這些公式以外的知識解決問題。

　　MySQL 索引也是，很多人都知道索引就像字典的目錄，索引是 B+ tree，但是知道這些有什么用呢?

　　知識是需要往深里學，才能轉化為能力的，你知道的多，并不代表你能解決的問題就多，反而那些知道的沒那么多，但是對他知道的東西，都研究透徹的人，才能一通百通。

　　當你知道了 MySQL 的聚簇索引-二級索引長成這個樣子后，還用去背什么“最左匹配”嗎?

　　隨便問個問題，只給 student 表建 idx_name_age 這個復合索引，這兩個 sql 語句，會走索引嗎?

　　select * from student where name = "David";

　　select * from student where age = 18;

　　甚至，這么精妙的數據結構設計，就知道用來加速查詢嗎?

　　至少現在我能想到的，索引可以拿來干的事情，就至少有四種。

感謝各位的閱讀！關于“MySQL索引的用途有哪些”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！