《Mysql應用MySQL InnoDB索引介紹及優化(一)》要點：
本文介紹了Mysql應用MySQL InnoDB索引介紹及優化(一)，希望對您有用。如果有疑問，可以聯系我們。

一、先說說什么是索引?
索引(index)翻譯為一個目錄,用于快速定位我們想要找的數據的位置.例如：我們把一個數據庫比作一本書,而索引(index)就是書中的目錄,此刻要找到書的某個感興趣的內容,我們一般是不會整本書翻完再去確認該內容在哪里,而是通過書的目錄,定位到該內容章節所在頁數,最后直接翻到該頁面

我們來看看在數據庫中的索引：
全表掃描 VS 索引掃描
以字典為例,全表掃描就是如果我們查找某個字時,那么通讀一遍新華字典,然后找到我們想要找到的字
而跟全表掃描相對應的就是索引查找,索引查找就是在表的索引部分找到我們想要找的數據具體位置,然后會到表里面將我們想要找的數據全部查出

實例：在一張學生表找到一個名字叫Dev的學生



左邊全表掃描：需要從第一行開始一行行的掃描,直到找到100008行Dev這個學生的信息為止,將這個數據返回回來,但有可能該表中還有同名的學生,因此掃描并沒有結束,通常全表掃描要找到一個數據,是需要將整張表的數據遍歷一遍,然后才能確定是否將所有數據返回

右邊索引掃描：索引查找是根據首字母排序找到D開頭的Dev,如果首字母相同,那么再根據第二個字母排序找到,以此類推,我們找到ID為100008,然后回表查出ID為100008的數據

結論：因此索引(對應InnoDB)的索引值對應的是主鍵ID

二、如何找到索引對應的值

InnoDB引擎主要根據
(1)B+tree
(2)二分查找法



B+tree: B+樹擁有整棵樹的根節點、支節點和頁節點,上層會存儲下層節點的管理范圍,直到頁節點的具體信息

二分查找法：根據B+樹存儲的各個節點的范圍,進行比較,逐步縮小范圍,最后定位到頁節點中我們想要的位置

三、介紹下InnoDB表也是一張索引表



如上圖InnoDB表是聚簇表,意思是InnoDB本身是一張大的索引組織表,也是一個根據主鍵排序的大索引的B+樹結構,我們在InnoDB里面另外建立自己想要索引的表的字段

聚簇索引就意味著InnoDB表本身,而我們把這些根據其他字段排序的索引稱為二級索引(secondery class)

四、在數據庫中如何建立索引
在MySQL中主要建立兩種類型的索引

1.單列索引

create index idx_name on tb_student(name);
????????????? 索引名??????? 表名?? 字段名
2.聯合索引

create index idx_name_age on tb_student(name,age);
#索引中先根據name排序,name相同的情況下根據age排序
五、索引維護
首先介紹下什么是索引維護?這是一個關乎性能的重要概念

如果索引所在字段發生了修改、刪除、插入等操作,那么索引項就會發生變化,因此如果不能保證索引的有序,那么就不能索引的準確與效率,而索引的排序發生了變化的這個行為,我們稱為索引維護

在insert/delete/update操作時,為了維護索引的排序,數據庫會自動的完成索引項的維護,索引的排序,這些行為對用戶是透明的,感覺不到的

在一個有索引的表中,創建它時,實際上還同時創建了索引排序的表,因此在DML中,插入等操作不再是普通的插入,MySQL將它封裝成了一個事務,連著索引項的排序表一起操作

因此,我們應當嚴格控制表上的索引數量,否則容易影響數據庫的性能
總結索引維護如下：

1、索引維護由數據庫自動完成
2、插入/修改/刪除每一個索引行都變成一個內部封裝的事務
3、索引越多,事務越大,代價越高
4、索引越多,對表的插入和索引字段的修改就越慢
因此可以看出索引并非是越多越好,在工作中也要慎用,尤其對于寫操作較為頻繁的業務

六、如何正確的使用索引?

1、依據where查詢條件建立索引

eg:
select a,b from tb_test where c = ?;
idx_c(c)?? ->正確

select a,b from tb_test where c = ? and b = ?
idx_cd(c,d)? ->正確
2、根據排序order by ,group by , distinct 字段添加索引

eg:
select * from tb_test order by a;
select a,count(*) from tb_test group by a;
idx_a(a)? ->正確

select * from tb_test order by a,b;
idx_a_b(a,b)? ->正確

select * from tb_test order where c = ? by a;
idx_c_a(c,a)? ->正確

七、到底哪些字段適合創建索引?
1、字段值的重復程度,如圖：


身份證號碼由于基本上不可能重復,因此選擇性非常好,而人的名字重復性較低,選擇性也不錯, 性別選擇性較差,重復度非常高

2、選擇性很差的字段通常不適合創建索引,但也有例外

如：男女比例相仿的表中,性別不適合創建單列索引,如果走索引不如走全表掃描,
因為走索引的I/O開銷更大
???
但如果男女比例極度不平衡,要查詢的又是少數方,如：理工學校、IT公司等可以考慮使用索引
3、聯合索引中選擇性好的字段應該排在前面

select * from tab_a where gender=? and name=?
idx_name_gender(name,gender)?? ->正確
4、聯合索引可以為單列、復列查詢提供幫助

idx_smp(a,b,c)
where a=?;??????????????? ->正確
where a=? and b=?;??????? ->正確
where a=? and c=?;??????? ->正確 (注：需要MySQL5.6版本以上；在5.5及以前版本,可以對a字段進行索引掃描,但c字段不行??? )
where a=? and b=? and c=? ->正確
5、合理創建聯合索引,避免冗余

(a),(a,b),(a,b,c)????? ->不可取
(a,b,c)??????????????? ->正確,可以覆蓋前兩個

今天就到這了...待續

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5800.html