# 索引設計的原則 1）適合索引的列是出現在where子句中的列，或者連接子句中指定的列； 2）基數較小的類，索引效果較差，沒有必要在此列建立索引； 3）使用短索引，如果對長字符串列進行索引，應該指定一個前綴長度，這樣能夠節省大量索引空間； 4）不要過度索引。索引需要額外的磁盤空間，并降低寫操作的性能。在修改表內容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長。所以只保持需要的索引有利于查詢即可。 # 索引設計的原則索引優化規則： 1）如果MySQL估計使用索引比全表掃描還慢，則不會使用索引。 返回數據的比例是重要的指標，比例越低越容易命中索引。記住這個范圍值——30%，后面所講的內容都是建立在返回數據的比例在30%以內的基礎上。 2）前導模糊查詢不能命中索引。 前導模糊查詢不能命中索引： EXPLAIN SELECT * FROM user WHERE name LIKE '%s%';  非前導模糊查詢則可以使用索引，可優化為使用非前導模糊查詢： EXPLAIN SELECT * FROM user WHERE name LIKE 's%';  3）數據類型出現隱式轉換的時候不會命中索引，特別是當列類型是字符串，一定要將字符常量值用引號引起來。 EXPLAIN SELECT * FROM user WHERE name=1;  EXPLAIN SELECT * FROM user WHERE name='1';  4）復合索引的情況下，查詢條件不包含索引列最左邊部分（不滿足最左原則），不會命中符合索引。 name,age,status列創建復合索引： ALTER TABLE user ADD INDEX index_name (name,age,status);  根據最左原則，可以命中復合索引index_name EXPLAIN SELECT * FROM user WHERE name='swj' AND status=1;  **注意：** 最左原則并不是說是查詢條件的順序。 EXPLAIN SELECT * FROM user WHERE name='swj' AND status=1;  **注意：** 而是查詢條件中是否包含索引最左列字段： EXPLAIN SELECT * FROM user WHERE status=2 ;  5）union、in、or都能夠命中索引，建議使用in。 **注意：** 查詢的CPU消耗：or>in>union。 6）用or分割開的條件，如果or前的條件中列有索引，而后面的列中沒有索引，那么涉及到的索引都不會被用到。 EXPLAIN SELECT * FROM payment WHERE customer_id = 203 OR amount = 3.96;  7）負向條件查詢不能使用索引，可以優化為in查詢。 負向條件有：!=、<>、not in、not exists、not like等。 負向條件不能命中索引： EXPLAIN SELECT * FROM user WHERE status !=1 AND status != 2;  可以優化為in查詢，但是前提是區分度要高，返回數據的比例在30%以內： EXPLAIN SELECT * FROM user WHERE status IN (0,3,4);  8）范圍條件查詢可以命中索引。范圍條件有：、>=、between等。 范圍條件查詢可以命中索引： EXPLAIN SELECT * FROM user WHERE status>5;  **注意：** 范圍列可以用到索引（聯合索引必須是最左前綴），但是范圍列后面的列無法用到索引，索引最多用于一個范圍列，如果查詢條件中有兩個范圍列則無法全用到索引： EXPLAIN SELECT * FROM user WHERE status>5 AND age<24;  **注意：** 如果是范圍查詢和等值查詢同時存在，優先匹配等值查詢列的索引： EXPLAIN SELECT * FROM user WHERE status>5 AND age=24;  8）數據庫執行計算不會命中索引。 EXPLAIN SELECT * FROM user WHERE age>24;  EXPLAIN SELECT * FROM user WHERE age+1>24;  計算邏輯應該盡量放到業務層處理，節省數據庫的CPU的同時最大限度的命中索引。 9）利用覆蓋索引進行查詢，避免回表。 被查詢的列，數據能從索引中取得，而不用通過行定位符row-locator再到row上獲取，即“被查詢列要被所建的索引覆蓋”，這能夠加速查詢速度。 EXPLAIN SELECT status FROM user where status=1;  **注意：** 當查詢其他列時，就需要回表查詢，這也是為什么要避免SELECT*的原因之一： EXPLAIN SELECT * FROM user where status=1;  10）建立索引的列，不允許為null。 單列索引不存null值，復合索引不存全為null的值，如果列允許為null，可能會得到“不符合預期”的結果集，所以，請使用not null約束以及默認值。 # 索引總結： a. 更新十分頻繁的字段上不宜建立索引：因為更新操作會變更B+樹，重建索引。這個過程是十分消耗數據庫性能的。 b. 區分度不大的字段上不宜建立索引：類似于性別這種區分度不大的字段，建立索引的意義不大。因為不能有效過濾數據，性能和全表掃描相當。另外返回數據的比例在30%以外的情況下，優化器不會選擇使用索引。 c. 業務上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引。雖然唯一索引會影響insert速度，但是對于查詢的速度提升是非常明顯的。另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，在并發的情況下，依然有臟數據產生。 d. 多表關聯時，要保證關聯字段上一定有索引。 e. 創建索引時避免以下錯誤觀念：索引越多越好，認為一個查詢就需要建一個索引；寧缺勿濫，認為索引會消耗空間、嚴重拖慢更新和新增速度；抵制唯一索引，認為業務的唯一性一律需要在應用層通過“先查后插”方式解決；過早優化，在不了解系統的情況下就開始優化。