定義

索引 (在MySQL中也叫"鍵key" )是存儲引擎快速找到記錄的一種數(shù)據(jù)結(jié)構(gòu)。

索引類型

1. 主鍵索引（PRIMARY KEY）：它是一種特殊的唯一索引，不允許有空值。

   創(chuàng)建：一般是在建表的時候同時創(chuàng)建主鍵索引。一個表只能有一個主鍵索引。

2. 唯一索引（UNIQUE）：唯一索引列的值必須唯一，但允許有空值。

   創(chuàng)建： ALTER TABLE 表名 ADD UNIQUE (字段名稱)

3. 普通索引（INDEX）：最基本的索引，它沒有任何限制。

   創(chuàng)建：ALTER TABLE 表名 ADD INDEX 索引名稱(字段名稱)

4. 組合索引（INDEX）：一個索引包含多個列 ,多用于避免回表查詢。

   創(chuàng)建：ALTER TABLE 表名 ADD INDEX 索引名稱(字段名稱1,字段名稱2字段名稱N)

5. 全文索引（FULLTEXT）：全文檢索,是目前搜索引擎使用的一種關(guān)鍵技術(shù)。

   創(chuàng)建：ALTER TABLE 表名 ADD FULLTEXT (字段名)

查看指定索引列表

? show index from表名;

索引的優(yōu)缺點

• 優(yōu)點

  1. 索引大大減小了服務(wù)器需要掃描的數(shù)據(jù)量。
  2. 索引可以幫助服務(wù)器避免排序和臨時表。

• 缺點

  1. 降低更新表的速度,如對表進(jìn)行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數(shù)據(jù),還要保存索引文件。
  2. 建立索引會占用磁盤空間的索引文件。如果在一個大表上創(chuàng)建了多種組合索引,索引文件會很快膨脹。
  3. 如果某個數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒有太大的實際效果。
  4. 對于非常小的表 ，大部分情況下簡單的全表掃描更高效。

索引設(shè)計的原則

1. 適合索引的列是出現(xiàn)在where子句中的列。
2. 更新十分頻繁的字段上不宜建立索引（更新操作會變更B+樹，重建索引，非常消耗數(shù)據(jù)庫性能）。
3. 區(qū)分度不大的字段上不宜建立索引（類似于性別這種區(qū)分度不大的字段，建立索引的意義不大。因為不能有效過濾數(shù)據(jù)，性能和全表掃描相當(dāng)。另外返回數(shù)據(jù)的比例在30%以下的情況下，優(yōu)化器不會選擇使用索引）。
4. 業(yè)務(wù)上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引。 雖然唯一索引會影響insert速度，但是對于查詢的速度提升是非常明顯的。另外，即使在應(yīng)用層做了非常完善的校驗控制，只要沒有唯一索引，在并發(fā)的情況下，依然有臟數(shù)據(jù)產(chǎn)生。
5. 多表關(guān)聯(lián)時，要保證關(guān)聯(lián)字段上一定有索引。(外鍵字段要加索引)

索引存儲類型

? InnoDB引擎使用的是B+Tree。

索引優(yōu)化規(guī)則

1. 如果MySQL估計使用索引比全表掃描還慢 ,則不會使用索引。（返回數(shù)據(jù)的比例是重要的指標(biāo),比例越低越容易命中索引。范圍值：30%以內(nèi)）
2. 前導(dǎo)模糊查詢不能命中索引。（如：SELECT * FROM user WHERE name LIKE '%s%';）
3. 非前導(dǎo)模糊查詢則可以使用索引 ，可優(yōu)化為使用非前導(dǎo)模糊查詢。（SELECT * FROM user WHERE name LIKE 's%:）
4. 數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換的時候不會命中索引，特別是當(dāng)列類型是字符串,一 定要將字符常量值用引號引起來。（錯誤 : SELECT * FROM user WHERE name=l;正確 : SELECT * FROM user WHERE name=T;）
5. 復(fù)合索引的情況下 ，查詢條件不包含索引列最左邊部分(不滿足最左原則)，不會命中符合索引。
6. union、 in、 or都能夠命中索引,建議使用in（效率高）。
7. 用or分割開的條件，如果or前的條件中列有索引：而后面的列中沒有索引，那么涉及到的索引都不會被用到。
8. 負(fù)向條件查詢不能使用索引，可以優(yōu)化為in查詢。
9. 數(shù)據(jù)庫執(zhí)行計算或函數(shù)不會命中索引。