索引是一種特殊的文件(InnoDB 數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分)驯遇，它們包含著對(duì)數(shù)據(jù)表里的所有記錄的引用指針惨寿。更通俗的說硕勿，數(shù)據(jù)庫索引就好比一本書的目錄，能夠加快數(shù)據(jù)庫的查詢速度钓试。
首先感謝網(wǎng)上的那些前輩和大神們的無私分享
最近在學(xué)習(xí)mysql的優(yōu)化問題,在查詢中正確使用索引,對(duì)查詢效率的提升有非常大的幫助,使用不當(dāng)會(huì)使索引失效,起不到索引該有的作用。把這兩天學(xué)到的知識(shí)記錄一下副瀑。
使用索引的優(yōu)勢(shì)
- 提高數(shù)據(jù)的檢索速度弓熏，降低數(shù)據(jù)庫IO成本：使用索引的意義就是通過縮小表中需要查詢的記錄的數(shù)目從而加快搜索的速度。
- 降低數(shù)據(jù)排序的成本糠睡，降低CPU消耗：索引之所以查的快挽鞠，是因?yàn)橄葘?shù)據(jù)排好序，若該字段正好需要排序狈孔，則真好降低了排序的成本信认。
使用索引帶來的問題
1.占用存儲(chǔ)空間：索引實(shí)際上也是一張表，記錄了主鍵與索引字段均抽，一般以索引文件的形式存儲(chǔ)在磁盤上嫁赏。
- 降低更新表的速度：表的數(shù)據(jù)發(fā)生了變化，對(duì)應(yīng)的索引也需要一起變更油挥，從而減低的更新速度潦蝇。否則索引指向的物理數(shù)據(jù)可能不對(duì)，這也是索引失效的原因之一深寥。
索引的類型
1.normal：
表示普通索引,它沒有任何限制攘乒，MyISAM 中默認(rèn)的 B-tree 類型的索引
2.unique：
表示唯一的，不允許重復(fù)的索引惋鹅，但是允許有空值则酝。如果該字段信息保證不會(huì)重復(fù)例如身份證號(hào)用作索引時(shí)，可設(shè)置為unique负饲。
3.full textl:
表示全文搜索的索引堤魁。 FULLTEXT 用于搜索很長(zhǎng)一篇文章的時(shí)候喂链，效果最好。注意僅 MyISAM 引擎支持
4.組合索引（最左前綴）
平時(shí)用的SQL查詢語句一般都有比較多的限制條件妥泉，所以為了進(jìn)一步榨取MySQL的效率椭微，就要考慮建立組合索引。使用組合索引時(shí)注意最左匹配原則盲链。
比如新建索引ALTER TABLE testADD INDEX 'id_name_age' ('id','name','age')蝇率。
在查詢的時(shí)SELECT * FROM user WHERE id =1 AND name='bruce'索引起作用。
但是查詢時(shí)SELECT * FROM user WHERE name='bruce' AND age = 18這時(shí)候索引不起作用刽沾。
至于原因,因?yàn)檩o助索引是B+樹實(shí)現(xiàn)的本慕，雖然可以指定多個(gè)列，但是每個(gè)列的比較優(yōu)先級(jí)不一樣侧漓，寫在前面的優(yōu)先比較锅尘。一旦出現(xiàn)遺漏，在B+樹上就無法繼續(xù)搜索了（通過補(bǔ)齊等措施解決的除外）布蔗，因此是按照最左連續(xù)匹配來的藤违。既然是在B+樹上搜索，對(duì)于條件的比較自然是要求精確匹配（即"="和"IN"）纵揍。不過順序倒是可以顛倒顿乒，因?yàn)椴樵儍?yōu)化器重排序一下就好了。
索引的優(yōu)化
- 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷泽谨，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描璧榄，如：
select id from t where num is null,可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值吧雹，然后這樣查詢：
select id from t where num=0- 應(yīng)盡量避免在 where 子句中使用!=或<>操作符骨杂，否則將引擎放棄使用索引而進(jìn)行全表掃描。優(yōu)化器將無法通過索引來確定將要命中的行數(shù),因此需要搜索該表的所有行吮炕。
- 應(yīng)盡量避免在 where 子句中使用 or 來連接條件腊脱，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：
select id from t where num=10 or num=20- 盡量避免在索引過的字符數(shù)據(jù)中龙亲，使用非打頭字母搜索陕凹。這也使得引擎無法利用索引。
見如下例子：
SELECT * FROM T1 WHERE NAME LIKE ‘%L%’
SELECT * FROM T1 WHERE SUBSTING(NAME,2,1)=’L’
SELECT * FROM T1 WHERE NAME LIKE ‘L%’- 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作鳄炉，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描
- 應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作杜耙，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描
- 不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算拂盯，否則系統(tǒng)將可能無法正確使用索引
總結(jié)
哪些情況需要建索引：
1 主鍵佑女，唯一索引
2 經(jīng)常用作查詢條件的字段需要?jiǎng)?chuàng)建索引
3 經(jīng)常需要排序、分組和統(tǒng)計(jì)的字段需要建立索引
4 查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
哪些情況不要建索引：
1 表的記錄太少团驱，百萬級(jí)以下的數(shù)據(jù)不需要?jiǎng)?chuàng)建索引,數(shù)據(jù)量很少的時(shí)候,索引帶來的提升不夠明顯
2 經(jīng)常增刪改的表不需要?jiǎng)?chuàng)建索引(在修改表的內(nèi)容時(shí)摸吠，索引必須進(jìn)行更新，有時(shí)可能需要重構(gòu)嚎花，因此寸痢，索引越多，所花的時(shí)間越長(zhǎng)紊选。)
3 數(shù)據(jù)重復(fù)且分布平均的字段不需要?jiǎng)?chuàng)建索引啼止，如 true,false 之類。(體現(xiàn)不出索引帶來的價(jià)值)
4 頻發(fā)更新的字段不適合創(chuàng)建索引(和2同理)
5 where條件里用不到的字段不需要?jiǎng)?chuàng)建索引(索引起不到作用)
使用注意:
- 性能優(yōu)化過程中兵罢，選擇在哪個(gè)列上創(chuàng)建索引是最重要的步驟之一献烦。可以考慮使用索引的主要有兩種類型的列：在where子句中出現(xiàn)的列卖词，在join子句中出現(xiàn)的列巩那。
- 考慮列中值的分布，索引的列的基數(shù)越大此蜈，索引的效果越好拢操。
- 使用短索引，如果對(duì)字符串列進(jìn)行索引舶替，應(yīng)該指定一個(gè)前綴長(zhǎng)度，可節(jié)省大量索引空間杠园，提升查詢速度顾瞪。
- 不要過度索引，只保持所需的索引抛蚁。每個(gè)額外的索引都要占用額外的磁盤空間陈醒，并降低寫操作的性能。
- 在修改表的內(nèi)容時(shí)瞧甩，索引必須進(jìn)行更新钉跷，有時(shí)可能需要重構(gòu)，因此肚逸，索引越多爷辙，所花的時(shí)間越長(zhǎng)。
- MySQL只對(duì)一下操作符才使用索引：<,<=,=,>,>=,between,in, 以及某些時(shí)候的like(不以通配符%或_開頭的情形)朦促。
再次感謝前輩們的無私分享
我也是在學(xué)習(xí)中,文中如有錯(cuò)誤的地方,歡迎在評(píng)論區(qū)指出,方便共同學(xué)習(xí)
