索引概念和作用
索引是一種使記錄有序化的技術，它可以指定按某列/某幾列預先排序，從而大大提高查詢速度（類似於漢語詞典中按照拼音或者筆畫查找）。
索引的主要作用是加快數據查找速度，提高數據庫的性能。
MySQL 索引類型
從物理存儲角度上，索引可以分為聚集索引和非聚集索引。
1. 聚集索引（Clustered Index）
聚集索引決定數據在磁盤上的物理排序，一個表只能有一個聚集索引。
2. 非聚集索引（Non-clustered Index）
非聚集索引並不決定數據在磁盤上的物理排序，索引上只包含被建立索引的數據，以及一個行定位符 row-locator，這個行定位符，可以理解為一個聚集索引物理排序的指針，通過這個指針，可以找到行數據。
從邏輯角度，索引可以分為以下幾種。
普通索引：最基本的索引，它沒有任何限制。
唯一索引：與普通索引類似，不同的就是索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。
主鍵索引：它是一種特殊的唯一索引，用於唯一標識數據表中的某一條記錄，不允許有空值，一般用 primary key 來約束。主鍵和聚集索引的關係詳見“問題詳解”中的第4題。
聯合索引（又叫複合索引）：多個字段上建立的索引，能夠加速複合查詢條件的檢索。
全文索引：老版本 MySQL 自帶的全文索引只能用於數據庫引擎為 MyISAM 的數據表，新版本 MySQL 5.6 的 InnoDB 支持全文索引。默認 MySQL 不支持中文全文檢索，可以通過擴展 MySQL，添加中文全文檢索或為中文內容表提供一個對應的英文索引表的方式來支持中文。
MySQL索引優化規則
可以通過以下規則對 MySQL 索引進行優化。
1.前導模糊查詢不能使用索引。
例如下面 SQL 語句不能使用索引。
select * from doc where title like '%XX'
而非前導模糊查詢則可以使用索引，如下面的 SQL 語句。
select * fromdoc where title like 'XX%'
頁面搜索嚴禁左模糊或者全模糊，如果需要可以用搜索引擎來解決。
2.union、in、or 都能夠命中索引，建議使用 in。
union：能夠命中索引。
示例代碼如下：
select * fromdoc where status=1
unionall
select * fromdoc where status=2
直接告訴 MySQL 怎麼做，MySQL 耗費的 CPU 最少，但是一般不這麼寫 SQL。
in：能夠命中索引。
示例代碼如下：
select * fromdoc where status in (1, 2)
查詢優化耗費的 CPU 比 union all 多，但可以忽略不計，一般情況下建議使用 in
or：新版的 MySQL 能夠命中索引。
示例代碼如下：
select * fromdoc where status = 1 or status = 2
查詢優化耗費的 CPU 比 in 多，不建議頻繁用 or。
3.負向條件查詢不能使用索引，可以優化為 in 查詢。
負向條件有：!=、<>、not in、not exists、not like 等。
例如下面代碼：
select * fromdoc where status != 1 and status != 2
可以優化為 in 查詢：
select * fromdoc where status in (0,3,4)
4.聯合索引最左前綴原則（又叫最左側查詢）
如果在(a,b,c)三個字段上建立聯合索引，那麼它能夠加快 a | (a,b) | (a,b,c) 三組查詢速度。
例如登錄業務需求，代碼如下。
selectuid, login_time from user wherelogin_name=? andpasswd=?
可以建立(login_name, passwd)的聯合索引。
因為業務上幾乎沒有 passwd 的單條件查詢需求，而有很多 login_name 的單條件查詢需求，所以可以建立(login_name, passwd)的聯合索引，而不是(passwd, login_name)。
建聯合索引的時候，區分度最高的字段在最左邊。
如果建立了(a,b)聯合索引，就不必再單獨建立 a 索引。同理，如果建立了(a,b,c)聯合索引，就不必再單獨建立 a、(a,b) 索引。
存在非等號和等號混合判斷條件時，在建索引時，請把等號條件的列前置。如 where a>? and b=?，那麼即使 a 的區分度更高，也必須把 b 放在索引的最前列。
最左側查詢需求，並不是指 SQL 語句的 where 順序要和聯合索引一致。
下面的 SQL 語句也可以命中 (login_name, passwd) 這個聯合索引。
selectuid, login_time from user where passwd=?andlogin_name=?
但還是建議 where 後的順序和聯合索引一致，養成好習慣。
5.範圍列可以用到索引（聯合索引必須是最左前綴）。
範圍條件有：、>=、between等。
範圍列可以用到索引（聯合索引必須是最左前綴），但是範圍列後面的列無法用到索引，索引最多用於一個範圍列，如果查詢條件中有兩個範圍列則無法全用到索引。
假如有聯合索引 (empno、title、fromdate)，那麼下面的 SQL 中 emp_no 可以用到索引，而 title 和 from_date 則使用不到索引。
select * fromemployees.titles where emp_no <10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'
6.把計算放到業務層而不是數據庫層。
在字段上進行計算不能命中索引。
例如下面的 SQL 語句。
select * fromdoc where YEAR(create_time) <='2016'
即使 date 上建立了索引，也會全表掃描，可優化為值計算，如下：
select * fromdoc where create_time <= '2016-01-01'
把計算放到業務層。
這樣做不僅可以節省數據庫的 CPU，還可以起到查詢緩存優化效果。
比如下面的 SQL 語句：
select * fromorder where date < = CURDATE()
可以優化為：
select * fromorder where date < = '2018-01-2412:00:00'
優化後的 SQL 釋放了數據庫的 CPU 多次調用，傳入的 SQL 相同，才可以利用查詢緩存。
7.強制類型轉換會全表掃描
如果 phone 字段是 varchar 類型，則下面的 SQL 不能命中索引。
select * fromuser where phone=13800001234
可以優化為：
select * fromuser where phone='13800001234'
8.更新十分頻繁、數據區分度不高的字段上不宜建立索引。
更新會變更 B+ 樹，更新頻繁的字段建立索引會大大降低數據庫性能。
“性別”這種區分度不大的屬性，建立索引是沒有什麼意義的，不能有效過濾數據，性能與全表掃描類似。
-
一般區分度在80%以上的時候就可以建立索引，區分度可以使用 count(distinct(列名))/count(*) 來計算。
9.利用覆蓋索引來進行查詢操作，避免回表。
被查詢的列，數據能從索引中取得，而不用通過行定位符 row-locator 再到 row 上獲取，即“被查詢列要被所建的索引覆蓋”，這能夠加速查詢速度。
例如登錄業務需求，代碼如下。
selectuid, login_time from user wherelogin_name=? andpasswd=?
可以建立(login_name, passwd, login_time)的聯合索引，由於 login_time 已經建立在索引中了，被查詢的 uid 和 login_time 就不用去 row 上獲取數據了，從而加速查詢。
10.如果有 order by、group by 的場景，請注意利用索引的有序性。
-
order by 最後的字段是組合索引的一部分，並且放在索引組合順序的最後，避免出現 file_sort 的情況，影響查詢性能。
例如對於語句 where a=? and b=? order by c，可以建立聯合索引(a,b,c)。
如果索引中有範圍查找，那麼索引有序性無法利用，如 WHERE a>10 ORDER BY b;，索引(a,b)無法排序。
11.使用短索引（又叫前綴索引）來優化索引。
前綴索引，就是用列的前綴代替整個列作為索引 key，當前綴長度合適時，可以做到既使得前綴索引的區分度接近全列索引，同時因為索引 key 變短而減少了索引文件的大小和維護開銷，可以使用 count(distinct left(列名, 索引長度))/count(*) 來計算前綴索引的區分度。
前綴索引兼顧索引大小和查詢速度，但是其缺點是不能用於 ORDER BY 和 GROUP BY 操作，也不能用於覆蓋索引（Covering Index，即當索引本身包含查詢所需全部數據時，不再訪問數據文件本身），很多時候沒必要對全字段建立索引，根據實際文本區分度決定索引長度即可。
例如對於下面的 SQL 語句：
SELEC *FROM employees.employees WHERE first_name='Eric'AND last_name='Anido';
我們可以建立索引：(firstname, lastname(4))。
12.建立索引的列，不允許為 null。
單列索引不存 null 值，複合索引不存全為 null 的值，如果列允許為 null，可能會得到“不符合預期”的結果集，所以，請使用 not null 約束以及默認值。
13.利用延遲關聯或者子查詢優化超多分頁場景。
MySQL 並不是跳過 offset 行，而是取 offset+N 行，然後返回放棄前 offset 行，返回 N 行，那當 offset 特別大的時候，效率就非常的低下，要麼控制返回的總頁數，要麼對超過特定閾值的頁數進行 SQL 改寫。
示例如下，先快速定位需要獲取的 id 段，然後再關聯:
selecta.* from 表1 a,(select id from 表1 where 條件 limit100000,20 ) b where a.id=b.id
14.業務上具有唯一特性的字段，即使是多個字段的組合，也必須建成唯一索引。
不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，但提高查找速度是明顯的。另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，根據墨菲定律，必然有髒數據產生。
15.超過三個表最好不要 join。
需要 join 的字段，數據類型必須一致，多表關聯查詢時，保證被關聯的字段需要有索引。
16.如果明確知道只有一條結果返回，limit 1 能夠提高效率。
比如如下 SQL 語句：
select * fromuser where login_name=?
可以優化為：
select * fromuser where login_name=? limit 1
自己明確知道只有一條結果，但數據庫並不知道，明確告訴它，讓它主動停止遊標移動。
17.SQL 性能優化 explain 中的 type：至少要達到 range 級別，要求是 ref 級別，如果可以是 consts 最好。
consts：單表中最多隻有一個匹配行（主鍵或者唯一索引），在優化階段即可讀取到數據。
ref：使用普通的索引（Normal Index）。
range：對索引進行範圍檢索。
當 type=index 時，索引物理文件全掃，速度非常慢。
18.單表索引建議控制在5個以內。
19.單索引字段數不允許超過5個。
字段超過5個時，實際已經起不到有效過濾數據的作用了。
20.創建索引時避免以下錯誤觀念
索引越多越好，認為一個查詢就需要建一個索引。
寧缺勿濫，認為索引會消耗空間、嚴重拖慢更新和新增速度。
抵制惟一索引，認為業務的惟一性一律需要在應用層通過“先查後插”方式解決。
過早優化，在不瞭解系統的情況下就開始優化。
閱讀更多 IT界的菜鳥 的文章
