索引
MySQL官方對(duì)索引的定義為：索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。提取句子主干，就可以得到索引的本質(zhì)：索引是數(shù)據(jù)結(jié)構(gòu)到逊。
索引的存儲(chǔ)分類
索引是在MYSQL的存儲(chǔ)引擎層中實(shí)現(xiàn)的用狱，而不是在服務(wù)層實(shí)現(xiàn)的。所以每種存儲(chǔ)引擎的索引都不一定完全相同译仗，也不是所有的存儲(chǔ)引擎都支持所有的索引類型。MYSQL目前提供了一下4種索引官觅。
- B-Tree 索引：最常見的索引類型纵菌，大部分引擎都支持B樹索引。
- HASH 索引：只有Memory引擎支持休涤，使用場(chǎng)景簡(jiǎn)單咱圆。
- R-Tree 索引(空間索引)：空間索引是MyISAM的一種特殊索引類型，主要用于地理空間數(shù)據(jù)類型功氨。
- Full-text (全文索引)：全文索引也是MyISAM的一種特殊索引類型序苏，主要用于全文索引，InnoDB從MYSQL5.6版本提供對(duì)全文索引的支持捷凄。
MyISAM忱详、InnoDB引擎、Memory三個(gè)常用引擎類型比較
| 索引 | MyISAM引擎 | InnoDB引擎 | Memory引擎 |
|---|---|---|---|
| B-Tree 索引 | 支持 | 支持 | 支持 |
| HASH 索引 | 不支持 | 不支持 | 支持 |
| R-Tree 索引 | 支持 | 不支持 | 不支持 |
| Full-text 索引 | 不支持 | 暫不支持 | 不支持 |
索引的創(chuàng)建跺涤、刪除
索引的類型：
- UNIQUE(唯一索引)：不可以出現(xiàn)相同的值匈睁，可以有NULL值
- INDEX(普通索引)：允許出現(xiàn)相同的索引內(nèi)容
- PROMARY KEY(主鍵索引)：不允許出現(xiàn)相同的值
- fulltext index(全文索引)：可以針對(duì)值中的某個(gè)單詞，但效率確實(shí)不敢恭維
- 組合索引：實(shí)質(zhì)上是將多個(gè)字段建到一個(gè)索引里桶错，列值的組合必須唯一
1航唆、使用ALTER TABLE語(yǔ)句創(chuàng)建索性
? 應(yīng)用于表創(chuàng)建完畢之后再添加。
ALTER TABLE 表名 ADD 索引類型 （unique,primary key,fulltext,index）[索引名]（字段名）
//普通索引
alter table table_name add index index_name (column_list) ;
//唯一索引
alter table table_name add unique (column_list) ;
//主鍵索引
alter table table_name add primary key (column_list) ;
//自增主鍵
alter table_name change column_name int not null primary key auto_increment;
ALTER TABLE可用于創(chuàng)建普通索引院刁、UNIQUE索引和PRIMARY KEY索引3種索引格式糯钙，table_name是要增加索引的表名，column_list指出對(duì)哪些列進(jìn)行索引，多列時(shí)各列之間用逗號(hào)分隔任岸。索引名index_name可選鸳玩，缺省時(shí)，MySQL將根據(jù)第一個(gè)索引列賦一個(gè)名稱演闭。另外不跟，ALTER TABLE允許在單個(gè)語(yǔ)句中更改多個(gè)表，因此可以同時(shí)創(chuàng)建多個(gè)索引米碰。
2窝革、使用CREATE INDEX語(yǔ)句對(duì)表增加索引
? CREATE INDEX可用于對(duì)表增加普通索引或UNIQUE索引，可用于建表時(shí)創(chuàng)建索引吕座。
CREATE INDEX index_name ON table_name(username(length));
如果是CHAR虐译，VARCHAR類型，length可以小于字段實(shí)際長(zhǎng)度吴趴；如果是BLOB和TEXT類型漆诽，必須指定length。
//create只能添加這兩種索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
3锣枝、刪除索引
? 刪除索引可以使用ALTER TABLE或DROP INDEX語(yǔ)句來(lái)實(shí)現(xiàn)厢拭。DROP INDEX可以在ALTER TABLE內(nèi)部作為一條語(yǔ)句處理，其格式如下：
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
其中，在前面的兩條語(yǔ)句中，都刪除了table_name中的索引index_name茫多。而在最后一條語(yǔ)句中未斑，只在刪除PRIMARY KEY索引中使用，因?yàn)?strong>一個(gè)表只可能有一個(gè)PRIMARY KEY索引，因此不需要指定索引名。如果沒有創(chuàng)建PRIMARY KEY索引，但表具有一個(gè)或多個(gè)UNIQUE索引寨闹，則MySQL將刪除第一個(gè)UNIQUE索引。
? 如果從表中刪除某列君账，則索引會(huì)受影響繁堡。對(duì)于多列組合的索引，如果刪除其中的某列杈绸，則該列也會(huì)從索引中刪除帖蔓。如果刪除組成索引的所有列，則整個(gè)索引將被刪除瞳脓。
索引的使用及注意事項(xiàng)
索引選擇
一塑娇、索引選擇原則
- 較頻繁的作為查詢條件的字段應(yīng)該創(chuàng)建索引
- 唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢條件
- 更新非常頻繁的字段不適合創(chuàng)建索引
- 不會(huì)出現(xiàn)在 WHERE 子句中的字段不該創(chuàng)建索引
二 劫侧、索引選擇原則細(xì)述
- 性能優(yōu)化過程中埋酬，選擇在哪個(gè)列上創(chuàng)建索引是最非常重要的哨啃。可以考慮使用索引的主要有 兩種類型的列：在where子句中出現(xiàn)的列写妥，在join子句中出現(xiàn)的列拳球，而不是在SELECT關(guān)鍵字后選擇列表的列；
- 索引列的基數(shù)越大珍特，索引的效果越好祝峻。例如，存放出生日期的列具有不同的值扎筒，很容易區(qū)分行莱找，而用來(lái)記錄性別的列，只有"M"和"F",則對(duì)此進(jìn)行索引沒有多大用處嗜桌，因此不管搜索哪個(gè)值奥溺，都會(huì)得出大約一半的行;
- 使用短索引，如果對(duì)字符串列進(jìn)行索引骨宠，應(yīng)該指定一個(gè)前綴長(zhǎng)度浮定，可節(jié)省大量索引空間，提升查詢速度层亿；
三桦卒、不建議索引的情況
- 表記錄比較少，例如一兩千條甚至只有幾百條記錄的表棕所，沒必要建索引闸盔，讓查詢做全表掃描就好了;
- 索引的選擇性較低。所謂索引的選擇性（Selectivity）琳省，是指不重復(fù)的索引值（也叫基數(shù)，Cardinality）與表記錄數(shù)（#T）的比值躲撰，選擇性越高的索引價(jià)值越大针贬，這是由B+Tree的性質(zhì)決定的
Index Selectivity = Cardinality / #T
索引弊端
索引是完全獨(dú)立于基礎(chǔ)數(shù)據(jù)之外的一部分?jǐn)?shù)據(jù)。假設(shè)在Table ta 中的Column ca 創(chuàng)建了索引 idx_ta_ca拢蛋，那么任何更新 Column ca 的操作桦他，MySQL在更新表中 Column ca的同時(shí)，都須要更新Column ca 的索引數(shù)據(jù)谆棱，調(diào)整因?yàn)楦聨?lái)鍵值變化的索引信息快压。而如果沒有對(duì) Column ca 進(jìn)行索引，MySQL要做的僅僅是更新表中 Column ca 的信息垃瞧。這樣蔫劣，最明顯的資源消耗就是增加了更新所帶來(lái)的 IO 量和調(diào)整索引所致的計(jì)算量。此外个从，Column ca 的索引idx_ta_ca須要占用存儲(chǔ)空間脉幢，而且隨著 Table ta 數(shù)據(jù)量的增加歪沃，idx_ta_ca 所占用的空間也會(huì)不斷增加，所以索引還會(huì)帶來(lái)存儲(chǔ)空間資源消耗的增加嫌松。
