前言概述
最近在找工作泰鸡,mysql因為是項目主要用到的數(shù)據(jù)庫,但由于平時只注重業(yè)務(wù)需求壳鹤,而且數(shù)據(jù)庫操作只停留在表皮上的增刪改查盛龄,面試時根本不夠用。怎么辦芳誓?沒辦法坝嗖啊!锹淌! 只能惡補了匿值,不求啥啥都會,但是索引這塊必須的要有一個大體的了解赂摆。找了很多資料挟憔,感覺都是大牛,我就還是選擇搬運吧烟号,O(∩_∩)O哈哈~
索引概念
索引在MySQL中也叫是一種“鍵”绊谭,是存儲引擎用于快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。
索引原理
通過不斷地縮小想要獲取數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果汪拥,同時把隨機的事件變成順序的事件达传,也就是說,有了這種索引機制迫筑,我們可以總是用同一種查找方式來鎖定數(shù)據(jù)宪赶。
從上面的描述中可以看出索引主要做兩件事:排序 + 查找
如何創(chuàng)建索引
1 #方法一:創(chuàng)建表時
2 CREATE TABLE 表名 (
3 字段名1 數(shù)據(jù)類型 [完整性約束條件…],
4 字段名2 數(shù)據(jù)類型 [完整性約束條件…],
5 [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
6 [索引名] (字段名[(長度)] [ASC |DESC])
7 );
8
9
10 #方法二:CREATE在已存在的表上創(chuàng)建索引
11 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
12 ON 表名 (字段名[(長度)] [ASC |DESC]) ;
13
14
15 #方法三:ALTER TABLE在已存在的表上創(chuàng)建索引
16 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
17 索引名 (字段名[(長度)] [ASC |DESC]) ;
18
19 #刪除索引:
20 DROP INDEX 索引名 ON 表名字;
創(chuàng)建索引需要遵循哪些原則
- 搜索的索引列,不一定是查詢列脯燃,最適合創(chuàng)建索引的列是跟在where條件中的列搂妻;
- 使用唯一索引,列中的數(shù)值差異越大越明顯辕棚,記錄性別的字段叽讳,只包含兩種情況,對其創(chuàng)建索引沒有多大用途坟募;
- 盡量使用短索引;
- 如果數(shù)據(jù)頻繁的更新或刪加邑狸,就不宜建立索引
什么情況下索引會失效
- like '%xx'
select * from tb1 where email like '%cn'; - 使用函數(shù)
select * from tb1 where reverse(email) = 'wupeiqi'; - or
select * from tb1 where nid = 1 or name = 'seven@live.com'; - 類型不一致
如果列是字符串類型懈糯,傳入條件是必須用引號引起來
select * from tb1 where email = 999; - 組合索引最左前綴
如果組合索引為:(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引 - 沒有查詢條件,或者查詢條件沒有建立索引;
- 對索引列進行運算導(dǎo)致索引失效,我所指的對索引列進行運算包括(+单雾,-赚哗,*她紫,/,! 等)
mysql中屿储,索引贿讹,主鍵,唯一索引够掠,聯(lián)合索引的區(qū)別民褂,對數(shù)據(jù)庫的性能有什么影響
普通索引: 允許被索引的數(shù)據(jù)列包含重復(fù)的值。
唯一索引: 唯一索引可以保證數(shù)據(jù)記錄的唯一性疯潭。
主鍵索引:是一種特殊的唯一索引赊堪,在一張表中只能定義一個主鍵索引,主鍵用于唯一標識一條記錄竖哩,使用關(guān)鍵字PRIMARY KEY 來創(chuàng)建哭廉。
聯(lián)合索引:覆蓋多個數(shù)據(jù)列。
正確使用索引可以提高查詢效率相叁,但由于建立索引需要占用物理空間遵绰,當(dāng)對表進行增、刪增淹、改的時候索引也要動態(tài)維護椿访,這樣就降低了數(shù)據(jù)的維護速度。所以建立索引前要考慮埠通。
B+ Tree索引和Hash索引有哪些區(qū)別
存儲格式上赎离,Hash使用key-value形式;b+tree是多路平衡二叉樹端辱。
哈希索引適合等值查詢梁剔,但是無法進行范圍查詢
哈希索引沒辦法利用索引完成排序
哈希索引不支持多列聯(lián)合索引的最左匹配規(guī)則
b+tree的葉子節(jié)點都能存什么
直接存放數(shù)據(jù)的,叫聚簇索引舞蔽。
存放主鍵荣病,叫非聚簇索引。
聚簇索引和非聚簇索引區(qū)別
1渗柿、聚簇索引:
將數(shù)據(jù)存儲與索引放到了一塊个盆,找到索引也就找到了數(shù)據(jù)將數(shù)據(jù)存儲與索引放到了一塊,找到索引也就找到了數(shù)據(jù)朵栖。
a) 一個索引項直接對應(yīng)實際數(shù)據(jù)記錄的存儲頁颊亮,可謂“直達”
b) 主鍵缺省使用它
c) 索引項的排序和數(shù)據(jù)行的存儲排序完全一致,利用這一點陨溅,想修改數(shù)據(jù)的存儲順序终惑,可以通過改變主鍵的方法(撤銷原有主鍵,另找也能滿足主鍵要求的一個字段或一組字段门扇,重建主鍵)
d) 一個表只能有一個聚簇索引(理由:數(shù)據(jù)一旦存儲雹有,順序只能有一種)
2偿渡、非聚簇索引:
將數(shù)據(jù)存儲于索引分開結(jié)構(gòu),索引結(jié)構(gòu)的葉子節(jié)點指向了數(shù)據(jù)的對應(yīng)行霸奕,myisam通過key_buffer把索引先緩存到內(nèi)存中溜宽,當(dāng)需要訪問數(shù)據(jù)時(通過索引訪問數(shù)據(jù)),在內(nèi)存中直接搜索索引质帅,然后通過索引找到磁盤相應(yīng)數(shù)據(jù)适揉,這也就是為什么索引不在key buffer命中時,速度慢的原因临梗。
a) 不能“直達”涡扼,可能鏈式地訪問多級頁表后,才能定位到數(shù)據(jù)頁
b) 一個表可以有多個非聚簇索引
mysql中聚簇索引的設(shè)定
聚簇索引默認是主鍵盟庞,如果表中沒有定義主鍵吃沪,InnoDB 會選擇一個唯一的非空索引代替。如果沒有這樣的索引什猖,InnoDB 會隱式定義一個主鍵來作為聚簇索引票彪。InnoDB 只聚集在同一個頁面中的記錄。包含相鄰健值的頁面可能相距甚遠不狮。
文章引用
https://www.cnblogs.com/Eva-J/articles/10126413.html
他講的非常全面降铸,我這里只針對面試常問的點做總結(jié),如果要更深入的理解索引摇零,還需要了解很多數(shù)據(jù)結(jié)構(gòu)推掸,計算機基礎(chǔ)方面的知識。我們還很年輕驻仅,年輕就得學(xué)傲鲁!噪服!