🌏索引(上)回顧

1. MySQL選擇使用B+樹這種數據結構進行索引

可以有效的控制樹高
非葉子節點僅具有索引功能,葉子結點保存真實數據
所有葉子結點構成一個有序鏈表 實現范圍查找

2. B+樹與B樹對比

葉子結點中的數據連續,相互連接,便于區間查找和搜索
非葉子結點的值都包含在葉子結點中
樹高相同的情況下,查找任一元素時間復雜度都一樣,性能均衡

3. 頁是內存與磁盤交互的最小單元,默認大小為16KB

讀取數據時,不是單單讀取一條數據,而是讀取一整頁查遍歷到相應的數據行
空間不夠了 InooDB會提前申請好一頁的空間,所以頁的磁盤空間是連續的,便于數據的遍歷

4. B+樹在MySQL索引中的應用
   

比如查找id 為 6的數據行

現在遍歷槽1 通過槽拿到主鍵值進行判斷, 能看到圖中槽1的主鍵值是4 5 > 4 就到槽2中遍歷
槽2 中存在主鍵值為6 的值
先比對槽中記錄的主鍵值，定位到最后?個槽2，再從最后?個槽中的第?條記錄遍歷，第?條記錄就是我們要查詢的目標行

想要查詢頁里面的內容,先將頁加載到內存,根節點一頁,二級節點一頁,葉子結點的數據頁也是一頁,所以說通過三次IO就可以把我們想要的數據找到 --三層樹高的B+樹

5. 三層樹高的B+樹可以存多少記錄

一個數據頁默認為16KB。假設一條數據為1KB，一頁中至多可以存16條數據
索引頁中存的是主鍵值和子節點的引用，也就是說下一個節點的偏移（地址）
主鍵 bigint類型 占8Byte 下一頁地址 6Byte 也就是說一條索引記錄占 8 + 6 = 14Byte
一個索引頁可以存 16 * 1024 / 14 = 1170
理論上一個三層樹高的B+樹可以存：1170 * 1170 * 16 = 21,902,400 條記錄
在當前的場景下，表中有21，902,400條記錄的情況下，通過3次IO就可以完成數據的查詢

6. 索引分類

創建索引之前考慮需不需要創建索引,創建一個索引就會生成一個索引樹占磁盤空間,對數據的增刪改效率影響較大
如果某一列的重復度過高,像是gender 這種只有兩個值的列 數據一多,重復度就會很高,就非常不適合創建索引來提高查詢效率
如果要存儲文檔類的數據,我們會專門使用文檔類的數據庫,全文索引用的并不多

🌏使用索引

🪐自動創建索引

• 當我們為一張表加主鍵約束(PRIMARY KEY),外鍵約束(FOREIGN KEY),唯一約束(UNIQUE)時,MySQL會為對應的列自動創建一個索引
• 如果表中不指定任何約束,MySQL會自動為每一列生成一個索引并用ROW_ID字段進行標識

🪐手動創建索引

🚀主鍵索引

1. 方式一:創建表時指定主鍵

-- 創建表的時候指定主鍵
create table t_pk1(id bigint PRIMARY KEY auto_increment,name varchar(20)
);
desc t_pk1;

2. 創建表時單獨指定主鍵列
   

-- 創建表時單獨指定主鍵列
create table t_pk2(id bigint auto_increment,name varchar(20),PRIMARY KEY (id)
);
show index from t_pk2;

3. 方式三:修改表中的列為主鍵索引

修改表結構和列語法:

alter table 表名 [add | modify | drop] 要修改的內容

🚀普通索引

創建的時機:
1.創建表的時候,明確的知道某些列需要頻繁查詢,就創建好(當表中數據過少時,全表掃描效率可能比索引還高)
2.隨著業務的不斷發展,在版本迭代的過程中會添加索引

1.方式一:創建表時指定索引列

create table t_index1(id bigint PRIMARY KEY auto_increment,name varchar(20) UNIQUE,sno varchar(20),index (sno)
);desc t_index1;

或者使用show keys from 表名

查看關系

2. 方式二:修改表中的列為普通索引列

create table t_index2(id bigint PRIMARY KEY auto_increment,name varchar(20) UNIQUE,sno varchar(20)
);
alter  table t_index2 add index (sno);
desc t_index2;

3. 方式三:單獨創建索引并指定索引名
   

create table t_index3(id bigint PRIMARY KEY auto_increment,name varchar(20) UNIQUE,sno varchar(20)
);create index idx_t_index3_sno on t_index3 (sno); 
desc t_index3;
show keys from t_index3;

🚀唯一索引

1. 方式一:創建表時指定索引列

create table t_test_index (id bigint primary key auto_increment,name varchar(20) uniquesno varchar(10),index(sno)
);

2. 方式二:修改表中的列為普通索引

create table t_test_index1 (id bigint primary key auto_increment,name varchar(20),sno varchar(10)
);
alter table t_test_index1 add index (sno) ;

3. 方式三:單獨創建索引并指定索引名

create table t_test_index2 (id bigint primary key auto_increment,name varchar(20),sno varchar(10)
);
create index index_name on t_test_index2(sno);

使用create index 創建索引

🚀復合索引

索引中包含多個列
創建語法和創建普通索引的方式相同,只不過指定多個列,列與列之間用逗號隔開

1. 方式一:創建表時指定索引列
   

create table t_index4(id bigint PRIMARY KEY auto_increment,name varchar(20),sno varchar(20),class_id bigint,index(sno,name)
);

2. 方式二:修改表中的列為復合索引
   

create table t_index5 (id bigint primary key auto_increment,name varchar(20),sno varchar(10),class_id bigint
);
alter table t_index5 add index (sno, class_id);

3. 方式三:單獨創建索引并指定索引名
   

create table t_index6 (id bigint primary key auto_increment,name varchar(20),sno varchar(10),class_id bigint
);
create index id_t_index6_sno_name on t_index6 (sno,name);

🪐查看索引

1. 方式一:show keys from 表名\G;
   

2. 方式二: show index from 表名;
   

3. 方式三:簡要信息 desc 表名;
   

🪐刪除索引

🚀刪除主鍵索引

語法:

alter table 表名 drop  primary key ;

🚀刪除其他索引

語法:

alter table 表名 drop index 索引名;

🌏查看執行計劃

怎么查看自己寫的SQL走沒走索引?
–查看執行計劃

• 先為學生表創建一個索引(復合索引)

• 1.不加條件,查詢所有(全表掃描)
  
• 2.使用主鍵查詢
  
• 3. 子查詢中使用索引
     

type類型:

• 4.使用普通索引
  

• 5.使用復合索引

回表查詢

索引覆蓋

Extra: 執行情況的說明和描述,包含不適合在其他列中顯示但十分重要的額外信息。
1.Using index: 表示使用索引，如果只有Using index，說明他沒有查詢到數據表，只用索引表就完成了這個查詢，這個叫覆蓋索引。
2.Using where: 表示條件查詢，如果不讀取表的所有數據，或不是僅僅通過索引就可以獲取所有需要的數據，則會出現Using where。