一起聊聊Mysql索引底層及優化

這篇文章為大家帶來了關於mysql中索引底層以及優化的相關知識，下面我們就整理一下mysql中索引的知識點，希望對大家有幫助。

Mysql索引篇

最近在許多網站上看了索引的相關知識，各種說法的都有，但又不是很全，有的概念很模糊，下面是由小編整理的Mysql索引知識點。

一.首先我們說下什麼是索引，為什麼要用索引

索引用於快速找出在某個列中有一特定值的行，不使用索引，MySQL必須從第一筆記錄開始讀完整個表，直到找出相關的行，表越大，查詢資料所花費的時間就越多，如果表中查詢的列有一個索引，MySQL能夠快速到達一個位置去搜尋數據文件，而不必查看所有數據，那麼將會節省很大一部分時間。

二.索引類型分為兩類：

1.hash索引

#2.bTree

三.下面我們簡單分析hash索引和bTree索引。

1. 雜湊表是一種以鍵- 值（key-value）儲存資料的結構，我們只要輸入待尋找的鍵即key，就可以找到其對應的值即Value。雜湊的想法很簡單，把值放在陣列裡，用一個雜湊函數把 key 換算成一個確定的位置，然後把 value 放在陣列的這個位置。

不可避免地，多個 key 值經過雜湊函數的換算，會出現同一個值的情況。處理這種情況的一種方法是，拉出一個鍊錶。

2. 說到bTree，就不得不提二元樹，二元樹分為很多，例：二元找出樹，平衡二元樹等。當然還有重點紅黑樹。
1） 二元尋找樹的特點是： 父節點左子樹所有節點的值小於父節點的值。右子樹所有節點的值大於父節點的值。 下面以一張圖為例來體現二元查找樹。

ID	name
#5	張五
6	張六
#7	張七
#2	張二
1	#張一
4	張四

3#######張三############

有一個需求，找張三，如果不使用二元查找樹那麼我們需要找7次，使用二元查找樹我們只需要找4次就可以找到我們想要的值。
根據上面說的使用二元查找樹的確可以減少查詢次數，但是大家有沒有想過，如果資料庫的資料是1，2，3，4，5，6，7這樣依次遞增的資料呢，繼續使用二元查找樹就會變成一個鍊錶了。那這樣如果我們想要找7那麼需要找7次，掃描表也是要7次。這樣跟沒有建立索引沒有差別，這也是弊端之一。下圖為例說明。

2） 平衡二元樹：又被稱為AVL樹，它的左右兩個子樹的高度差的絕對值不超過1，並且左右兩個子樹都是一棵平衡二元樹，AVL樹是最早發明的自平衡二元查找樹。在AVL樹中，任何節點的兩個子樹的高度最大差異只能為1，所以它又被稱為高度平衡樹。查詢、增加和刪除在平均和最壞情況下都是O(log n)。增加和刪除會需要透過一次或多次樹旋轉來重新平衡這個樹。
我們引入二元樹的目的是為了提高二元樹的搜索的效率,從而減少樹的平均搜索長度，為此,就必須在每顆二叉樹插入一個結點時調整樹的結構,讓二叉樹搜索能夠保持平衡,從而可能降低樹的高度,減少的平均樹的搜尋長度。
平衡二元樹特徵如下：
1.它的左子樹和右子樹都是AVL樹
2.左子樹和右子樹的高度差不能超過1
例圖：
3） 紅黑樹：可以理解為紅黑樹是凌駕於平衡二元樹之上的一棵樹，紅黑樹不會追求“完全平衡”，它只會求部分達到平衡要求，降低了對旋轉的要求，從而提高性能。此外，由於它的設計，所有不平衡都能夠在三次旋轉之內解決。在紅黑樹中，它的演算法時間複雜度與AVL相同，且統計效能會強迫AVL樹更高。所以紅黑樹相對於平衡二元樹來說，不是嚴格意義上的平衡二元樹，紅黑樹插入和刪除效率更高一些，查詢的效率比平衡二元樹來說相對低一些，但是二者查詢效率差值做對比，基本可以忽略。紅黑樹特徵如下：
1. 節點是紅色或黑色。
2. 根節點是黑色。
3. 每個紅色節點的兩個子節點都是黑色。 （紅色節點的子節點必須是黑色節點）
4. 從任一節點到其每個葉子的所有路徑都包含相同數目的黑色節點。
故紅黑樹是黑色平衡的樹，左子樹與右子樹高度差不會超過2。紅節點的父節點、子節點只能是黑節點。
例圖：

4） BTree（B樹）：當然上面說到了紅黑樹，效能非常高。以上圖為例，樹的高度最高才為4，共9個數據，但是對於Mysql數據庫，動則幾百萬條數據，幾千條數據，那樹的高度就不可估量了，比如說上百萬條數據需要經過30-50次磁碟IO才能查詢到數據，甚至更多的次數，顯然無法滿足Mysql索引高效的查詢效率。那如果我們控制樹的高度呢，那這樣就會大大減少了請求磁碟IO的請求次數，如果高度控制在4，那隻需要經過4次磁碟IO就可以查詢到資料。
但是怎麼樣控制樹的高度呢，紅黑樹是每個節點只儲存一個元素，如果每個節點存多個元素呢，這樣就可以解決高度問題了，肯定有同學有疑問，把所有的元素都放到一個節點上，那高度值就是1了，不是更快嗎？這樣想肯定是錯的，Mysql每一次跟磁碟IO打交道是有大小限制的，Mysql限制每個節點的大小是16K。 想查看自己Mysql限制節點大小的同學可以執行下面的sql。
show global status like 'Innodb_page_size'
下面以圖為例體現BTree
BTree特點如下：
1.所有索引元素不重複
2.節點的資料索引從左到右依序遞增
3.葉節點具有相同的深度，葉節點的指標為空
4.葉子節點和非葉子結點都儲存索引和資料

5） B 樹：上面說到了BTree控制了樹的高度的問題，可以滿足Mysql對於索引的需求，但最終Mysq索引實作不是BTree而是B樹，Mysql對B樹做了一點點改造，得到了B 樹，也可以理解為B 樹是B樹的升級版。
下面以圖為例說明：

從這張圖可以看到，我們的非葉子節點只儲存了索引並沒有儲存data，而且葉子節點間用指針相連。 B樹的葉子節點和非葉子節點都儲存了索引和數據，而且葉子結點的指標為空，B 樹把資料放在了葉子節點上，這樣非葉子節點就可以存放更多的索引，每次從磁碟IO也能取得更多的索引。
B 樹特徵如下：
1.非葉子節點不儲存data，只儲存索引(冗餘)和下層指針，可以放更多的索引
2 .葉子節點包含所有索引字段，和數據
3.葉子節點用雙指針連接，提高區間訪問的性能

在百度上和很多博客上畫的B 樹是錯誤的哦，一定要避坑哦。
有興趣看Mysql官方對B 樹的解釋的可以去看看。
連結: Mysql官網.

四.索引分類

1.依照索引的儲存關聯分類：分為兩大類
# 1.）聚集索義（叢集索引）：葉節點包含了完整的資料記錄，不需要回表。
2.）非聚集索引：需要回表，二次查樹，影響效能。

1.1） 大家都知道Mysql常用的儲存引擎有兩種MyISAM和InnoDB，但大家實際了解過兩種儲存引擎底層的資料儲存結構嗎？
以下以圖為例為大家說明：
其中test.myisam表是MyISAM儲存引擎，actor表是InnoDB儲存引擎，可以看到MyISAM儲存引擎有三個文件，分別是frm、MYD 、MYI，很容易理解frm-frame的簡稱，存的是表的結構，MYD-MYData存的是數據，MYI-MYIndex存的是索引，索引和數據是分開存儲的，再看InnoDB只有frm、IBD ，其中frm一樣也是存的表的結構，IBD檔案存的是索引和數據，這點InnoDB和MyISAM不一樣。
以下以圖為例說明MyISAM儲存引擎主鍵索引是需要回表操作（非聚集索引）
其中15存的是主鍵索引，0x07存的是15所在行記錄的磁碟檔案位址指針，例如我們想找到15的數據，那首先應該先透過主鍵索引樹，找到15所對應的指針，然後找到了這個指針再去MyD檔案中找具體的數據，需要進行二次查找，這個過程稱為回表操作。
2.1） 以下以圖為例說明InnoDB儲存引擎主鍵索引不需要進行回表操作。 （聚集索引）
InnoDB儲存引擎子節點首先15那一行存放的是索引，15下面的那一列存放的是索引所在行的其他所有字段，如果我們想要查15的數據，直接就可以找到，不需要在經過二次查樹。

2. 依照功能分類：主要分為五大類別
2.1 主鍵索引：InnoDB主鍵索引不需要回表運算
# 2.2 普通索引（二級索引）：InnoDB普通索引需要回表操作，對於二級索引，會預設和主鍵做聯合索引。
2.3 唯一索引
2.4 全文索引
2.5 共同索引：需要滿足最左前綴原則

#3. 在2.2中提到了普通索引需要回表操作，那有沒有不需要回表的普通索引呢，答案是有的，在某個查詢裡面，索引已經覆蓋了我們的查詢需求，我們稱為覆蓋索引。這時是不需要回表操作的。
由於覆寫索引可以減少樹的搜尋次數，顯著提升查詢效能，所以使用覆蓋索引是一個常用的效能優化手段。

舉個例子：下面是這個表格的初始化語句。

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),
(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

在上面這個表 T 中，如果我執行 select * from T where k between 3 and 5，需要執行幾次樹的搜尋操作，會掃描多少行？
現在，我們一起來看看這個 SQL 查詢語句的執行流程。看下圖。

1.） 在k 索引樹上找到k=3 的記錄，取得ID = 300;
2.） 再到ID索引樹查到ID=300 對應的R3；
3.） 在k 索引樹取下一個值k=5，取得ID=500；
4.） 再回到ID 索引樹查到ID=500 對應的R4；
5.） 在k 索引樹取下一個值k=6，不滿足條件，循環結束。

在這個過程中，回到主鍵索引樹搜尋的過程，我們稱為回表。可以看到，這個查詢過程讀了 k 索引樹的 3 筆記錄（步驟 1、3 和 5），回表了兩次（步驟 2 和 4）。

在這個範例中，由於查詢結果所需的資料只在主鍵索引上有，所以不得不回表。

如果執行的語句是select ID from T where k between 3 and 5，這時只需要查ID 的值，而ID 的值已經在k 索引樹上了，因此可以直接提供查詢結果，不需要回表。也就是說，在這個查詢裡面，索引 k 已經「覆蓋了」我們的查詢需求，我們稱為覆蓋索引。

在 InnoDB 中，表都是根據主鍵順序以索引的形式存放的，這種儲存方式的表稱為索引組織表。又因為前面我們提到的，InnoDB 使用了 B 樹索引模型，所以資料都是儲存在 B 樹中的。每一個索引在 InnoDB 裡面對應一棵 B 樹。

五.索引最佳化

1.上面描述了索引基本概念、分類以及底層的基本結構相關知識。下面聊聊索引優化的相關知識吧。

1.) 當組合索引只要有一列含有null值，索引失效
2.） 在列上做計算索引失效，範圍之後的索引全部失效
3.) 在查詢條件上使用函數會造成索引失效
4.） 在where字句中使用!= 或運算符，導致索引失效
5.） 避免使用or，導致索引失效
6.） 使用模糊查詢也會造成索引失效，可以用like ' a%'而不是like '%a%'
7.） 盡量使用覆蓋索引，減少select * 語句
8.） 滿足最左前綴法則，最左前列開始並且不跳過索引中的列
9.） 字串不加單引號索引失效

2.下面以實戰說明索引優化。

新建立一個員工表，有5個屬性，如下。

create table employees(
id int primary key auto_increment comment '主键自增',
name varchar(30) not null default '' comment'名字',
age int not null default 1 comment '年龄',
id_card varchar(40) not null default '' comment '身份证号',
position varchar(40) not null default '' comment '位置'
);

-- 创建联合索引
create index name_index on employees (name,age,position);

-- 插入一条数据
insert into employees(name,age,id_card,position) values('张三',15,
'201124199011035321','北京');

--  下面以10条sql测试，注意建立的联合索引顺序是 name，age，position
1.explain select * from employees where age=15 and position='北京' and name='张三';

2.explain select * from employees where name='张三' and age=15 and position='北京';

3.explain select * from employees where age=15 and name='张三';

4.explain select * from employees where position='北京' and name='张三';

5.explain select * from employees where position='北京' and age=15;

6.explain select * from employees where position='北京' and age>15 and name='张三';

7.explain select * from employees where position='北京';

8.explain select * from employees where age=15;

9.explain select * from employees where name='张三';

10.explain select * from employees where name != '张三';

以上10条sql有哪些是索引失效，有哪些是索引没有失效的呢？
相信同学们已经有了答案，但是答案对不对呢，下面我们一起分析下。
首先说第1条，查询条件把3个索引全部用上了，但是索引的顺序有变化，由name，age，position变成
了age，position，name，想到这里肯定有很多同学给出的答案就是索引失效，但是事实证明这个结果
是错的，索引生效，肯定有很多同学疑惑，为什么呢，这条sql不满足最左原则法则呀，这就要涉及到sql
的执行流程了，这里博主简单说下，sql执行有1个优化器的过程，优化器的作用之一就是索引的选择优化，
所以优化器帮我们把索引的顺序变成正确的了，所以索引生效。
下面是第1条按照索引顺序sql和第2条没有按照索引顺序sql的执行结果。

执行结果入下图：可以发现全部生效。

第1條sql type的值為ref、位元組是288 且ref有3個const，全部生效。

第2條sql type的值為ref、位元組是288 且ref有3個const，全部生效。

想学习sql的执行流程的可以看博主的另一篇关于sql执行流程的文章哦。
有的同学有疑问了，那最左原则没有用了吗？
答案：有用的。

现在我们说下第3、4、5条sql
第3条：
explain select * from employees where age=15 and name='张三';
sql在执行的时候，优化器替我们把索引的顺序优化了，由 age -> name 变成 name -> age，这时
索引是生效的。
第4条：
explain select * from employees where position='北京' and name='张三';
索引顺序优化为name - > position,但是这时索引只有name索引生效，position没有生效，因为我
们建立的索引顺序是 name  -> age - > position，你会发现跳过了age，索引本质也是一棵树，少
了一个节点，下面的索引当然不会生效了，这就没有满足最左原则法则。
第5条：
explain select * from employees where position='北京' and age=15;
这就和第4条sql一样的道理了，第一个索引都不见了，后面的不可能生效。

执行结果如下：

可以發現第3條sql type的值為ref、位元組是126且ref有2個const，全部生效。

第4條sql只有122位元組且ref只有1個const，只有name索引生效。

第5條sql type的值為all，位元組和ref都是空，全部失效。

下面说第6条sql，剩下的sql都是和之前的sql一样的道理。
explain select * from employees where position='北京' and age>15 and name='张三';
这条sql我们会发现，把索引字段全部使用了并且当作条件查询，不一样的是age是范围查找，优化器替我
们把索引顺序优化成 name  -> age - > position ，按照我们索引优化第2条：在列上做计算索引失效，范围之后的索引全部失效，想必答案同学们都知道了。

执行结果如下：

第6條sql只有126位元組且type的值為range，範圍查找，只有name和age索引生效。

推薦學習：mysql影片教學

#

以上是一起聊聊Mysql索引底層及優化的詳細內容。更多資訊請關注PHP中文網其他相關文章！