前言：

一個(gè)優(yōu)秀開發(fā)的必備技能：性能優(yōu)化，包括：JVM調(diào)優(yōu)、緩存、Sql性能優(yōu)化等。本文主要講基于Mysql的索引優(yōu)化。
首先我們需要了解執(zhí)行一條查詢SQL時(shí)Mysql的處理過程：

其次我們需要知道，我們寫的SQL在Mysql的執(zhí)行順序是怎么樣的？sql的執(zhí)行順序?qū)ql的性能優(yōu)化很有幫助，很重要。在建立復(fù)合索引的時(shí)候需要考慮到這點(diǎn)。

例：

在tb_dept中建立一個(gè)復(fù)合索引 idx_parent_id_code：

然后看下兩個(gè)sql 解釋的結(jié)果：

1）在當(dāng)前索引下，哪一個(gè)sql索引利用率高？

借助于上文中查詢SQL的執(zhí)行順序，是先執(zhí)行 WHERE再執(zhí)行 GROUP BY 的，即：

第一個(gè)sql執(zhí)行的順序是先執(zhí)行了 where后的 parent_id然后執(zhí)行了 group by 后的 dept_code，順序是和索引的順序是一致的，type等級為ref，掃描行數(shù)rows為 4；

而第二個(gè)sql是先執(zhí)行了 where后的 dept_code然后執(zhí)行了 group by 后的 parent_id，順序是和索引的順序是不一致的，type等級為index，掃描行數(shù)rows為 19；

從解釋結(jié)果看，第一條的sql索引利用率高于第二條的。（后文會(huì)講到：索引type從優(yōu)到差：System-->const-->eq_ref-->ref-->ref_or_null-->index_merge-->unique_subquery-->index_subquery-->range-->index-->all.）

或者從掃描的行數(shù)rows對比數(shù)據(jù)源也可直觀的看出，兩個(gè)語句的性能：

2）怎么優(yōu)化？

如果業(yè)務(wù)中用到第二個(gè)sql，那么就需要調(diào)整索引的順序和sql執(zhí)行順序一致。

或者兩個(gè)sql都用到了，那么就再建一個(gè)復(fù)合索引 （idx_code_parent_id）

然后再看下第二條的執(zhí)行計(jì)劃：

執(zhí)行計(jì)劃分析（下面就是本文的重點(diǎn)內(nèi)容了）：

通過explain可以知道m(xù)ysql是如何處理語句的，并分析出查詢或是表結(jié)構(gòu)的性能瓶頸，其實(shí)就是在干查詢優(yōu)化器的事，通過expalin可以得到：

1. 表的讀取順序
2.表的讀取操作的操作類型
3.哪些索引可以使用
4. 哪些索引被實(shí)際使用
5.表之間的引用
6.每張表有多少行被優(yōu)化器查詢

從上文的例子中我們可以看到執(zhí)行explain時(shí)，結(jié)果會(huì)有一個(gè)表格，這個(gè)表格就是分析結(jié)果，下面我們來一個(gè)一個(gè)說明下這個(gè)表的表頭：

Id: MySQL QueryOptimizer 選定的執(zhí)行計(jì)劃中查詢的序列號。表示查詢中執(zhí)行select 子句或操作表的順序,id 值越大優(yōu)先級越高,越先被執(zhí)行。id 相同,執(zhí)行順序由上至下。

Select_type: 一共有9中類型,只介紹常用的4種:

SIMPLE: 簡單的 select 查詢,不使用 union 及子查詢

PRIMARY: 最外層的 select 查詢

UNION: UNION 中的第二個(gè)或隨后的 select 查詢,不 依賴于外部查詢的結(jié)果集

DERIVED: 用于 from 子句里有子查詢的情況。 MySQL 會(huì) 遞歸執(zhí)行這些子查詢, 把結(jié)果放在臨時(shí)表里。

Table: 輸出行所引用的表

Type: 從優(yōu)到差的順序如下:（紅色標(biāo)識的是常見的級別。）

system-->const-->eq_ref-->ref-->ref_or_null-->index_merge-->unique_subquery-->index_subquery-->range-->index-->all.

各自的含義如下:

system: 表僅有一行。這是 const 連接類型的一個(gè)特例。

const: const 用于用常數(shù)值比較 PRIMARY KEY 時(shí)。

eq_ref: 查詢使用了索引為主鍵或唯一鍵的全部時(shí)使用。即：通過索引關(guān)鍵字可能查找到一個(gè)符合條件的行。

ref: 通過索引關(guān)鍵字可能查找到多個(gè)符合條件的行。

ref_or_null: 如同 ref, 但是 MySQL 必須在初次查找的結(jié)果里找出 null 條目,然后進(jìn)行二次查找。

index_merge: 說明索引合并優(yōu)化被使用了。

unique_subquery: 在某些 IN 查詢中使用此種類型,而不是常規(guī)的 ref:valueIN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery: 在 某 些 IN 查 詢 中 使 用 此 種 類 型 , 與unique_subquery 類似,但是查詢的是非唯一 性索引

range: 檢索給定范圍的行。當(dāng)使用 <>、>、>=、<、<=、BETWEEN 或者 IN 操作符時(shí),會(huì)使用到range。

index: 全表掃描,只是掃描表的時(shí)候按照索引次序進(jìn)行而不是行。主要優(yōu)點(diǎn)就是避免了排序, 但是開銷仍然非常大。

all: 最壞的情況,從頭到尾全表掃描。

possible_keys : 哪些索引可能有助于查詢。如果為空,說明沒有可用的索引。

key: 實(shí)際從 possible_key 選擇使用的索引，如果為 NULL,則沒有使用索引。很少的情況 下,MYSQL 會(huì)選擇優(yōu)化不足的索引。這種情 況下,可以在 SELECT語句中使用 USE INDEX (indexname)來強(qiáng)制使用一個(gè)索引或者用IGNORE INDEX(indexname)來強(qiáng)制 MYSQL 忽略索引

key_len: 使用的索引的長度。在不損失精確性的情況 下,長度越短越好。

ref: 顯示索引的哪一列被使用了

rows: 請求數(shù)據(jù)返回的大概行數(shù)

extra: 其他信息，出現(xiàn)Using filesort、Using temporary 意味著不能使用索引,效率會(huì)受到重大影響。應(yīng)盡可能對此進(jìn)行優(yōu)化。

Using filesort: 沒有辦法利用現(xiàn)有索引進(jìn)行排序，需要額外排序，建議：根據(jù)排序需要，創(chuàng)建相應(yīng)合適的索引

Using temporary: 需要用臨時(shí)表存儲(chǔ)結(jié)果集，通常是因?yàn)間roup by的列列上沒有索引。也有可能是因?yàn)橥?br /> 時(shí)有g(shù)roup by和order by，但group by和order by的列又不一樣

Using index ： 利用覆蓋索引，無需回表即可取得結(jié)果數(shù)據(jù)（即數(shù)據(jù)直接從索引文件中讀取），這種結(jié)果是好的。

其中重要的幾個(gè)就是 key、type 、rows、extra，其中key為null、all 、index時(shí)，需要調(diào)整、優(yōu)化索引。一般需要達(dá)到 ref、eq_ref 級別，范圍查找需要達(dá)到 range，extra有Using filesort、Using temporary 的一定需要優(yōu)化，根據(jù)rows可以直觀看出優(yōu)化結(jié)果。

優(yōu)化手段：

① SQL優(yōu)化

• 避免 SELECT *，只查詢需要的字段。
• 小表驅(qū)動(dòng)大表，即小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集：
  當(dāng)B表的數(shù)據(jù)集比A表小時(shí)，用in優(yōu)化 exist兩表執(zhí)行順序是先查B表再查A表查詢語句：SELECT * FROM tb_dept WHERE id in (SELECT id FROM tb_dept) ;
  當(dāng)A表的數(shù)據(jù)集比B表小時(shí)，用exist優(yōu)化in ，兩表執(zhí)行順序是先查A表，再查B表，查詢語句：SELECT * FROM A WHERE EXISTS (SELECT id FROM B WHERE A.id = B.ID) ;
• 盡量使用連接代替子查詢，因?yàn)槭褂?join 時(shí)，MySQL 不會(huì)在內(nèi)存中創(chuàng)建臨時(shí)表。

② 優(yōu)化索引的使用

• 盡量使用主鍵查詢，而非其他索引，因?yàn)橹麈I查詢不會(huì)觸發(fā)回表查詢。
• 不做列運(yùn)算，把計(jì)算都放入各個(gè)業(yè)務(wù)系統(tǒng)實(shí)現(xiàn)
• 查詢語句盡可能簡單，大語句拆小語句，減少鎖時(shí)間
• or 查詢改寫成 union 查詢
• 不用函數(shù)和觸發(fā)器
• 避免 %xx 查詢，可以使用：select * from t where reverse(f) like reverse('%abc');
• 少用 join 查詢
• 使用同類型比較，比如 '123' 和 '123'、123 和 123
• 盡量避免在 where 子句中使用 != 或者 <> 操作符，查詢引用會(huì)放棄索引而進(jìn)行全表掃描
• 列表數(shù)據(jù)使用分頁查詢，每頁數(shù)據(jù)量不要太大
• 避免在索引列上使用 is null 和 is not null

③ 表結(jié)構(gòu)設(shè)計(jì)優(yōu)化

• 使用可以存下數(shù)據(jù)最小的數(shù)據(jù)類型。
• 盡量使用 tinyint、smallint、mediumint 作為整數(shù)類型而非 int。
• 盡可能使用 not null 定義字段，因?yàn)?null 占用 4 字節(jié)空間。數(shù)字可以默認(rèn) 0 ，字符串默認(rèn) “”
• 盡量少用 text 類型，非用不可時(shí)最好獨(dú)立出一張表。
• 盡量使用 timestamp，而非 datetime。
• 單表不要有太多字段，建議在 20 個(gè)字段以內(nèi)。

Mysql常用數(shù)據(jù)類型存儲(chǔ)大小及范圍：https://blog.csdn.net/HXNLYW/article/details/100104768

3.如果以上優(yōu)化還是有問題，可以使用show profiles 分析sql 性能

show profiles

show profile for query [queryId]

具體請查看：https://blog.csdn.net/aeolus_pu/article/details/7818498

結(jié)尾：

本文是最近學(xué)習(xí)Mysql索引優(yōu)化的一些總結(jié)和記錄，如有不對的地方，歡迎評論吐槽。

---

附：

索引相關(guān)知識：

———— 查看表索引：
show index from 【table】

———— 直接創(chuàng)建索引
CREATE INDEX indexName ON table(column(length))

———— 修改表結(jié)構(gòu)的方式添加索引
ALTER tableADD INDEX indexName ON (column(length))
---主鍵索引
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
---唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column` )
---普通索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column`(length) )
---復(fù)合索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

length的確定：
如果索引列長度過長,這種列索引時(shí)將會(huì)產(chǎn)生很大的索引文件,不便于操作,可以使用前綴索引方式進(jìn)行索引，前綴索引應(yīng)該控制在一個(gè)合適的點(diǎn),控制在0.31黃金值即可(大于這個(gè)值就可以創(chuàng)建)。
SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; -- 這個(gè)值大于0.31就可以創(chuàng)建前綴索引,Distinct去重復(fù)

———— 刪除索引：
1）ALTER TABLE table_name DROP INDEX index_name
2）DROP INDEX index_name ON table_name;

MyISAM 和 InnoBD區(qū)別：

MyISAM

InnoDB

主鍵

允許沒有任何索引和主鍵的表存在，

myisam的索引都是保存行的地址。

如果沒有設(shè)定主鍵或者非空唯一索引，就會(huì)自動(dòng)生成一個(gè)6字節(jié)的主鍵(用戶不可見)

innodb的數(shù)據(jù)是主索引的一部分，其他索引保存的是主索引的值。

事務(wù)處理上方面:

MyISAM類型的表強(qiáng)調(diào)的是性能，其執(zhí)行數(shù)度比InnoDB類型更快，但是不提供事務(wù)支持、不支持外鍵 InnoDB提供事務(wù)支持事務(wù)，外部鍵（foreign key）等高級數(shù)據(jù)庫功能

DML操作
如果執(zhí)行大量的SELECT，MyISAM是更好的選擇

1.如果你的數(shù)據(jù)執(zhí)行大量的INSERT或UPDATE，出于性能方面的考慮，應(yīng)該使用InnoDB表
2.DELETE FROM table時(shí)，InnoDB不會(huì)重新建立表，而是一行一行的刪除。自動(dòng)增長

myisam引擎的自動(dòng)增長列必須是索引，如果是組合索引，自動(dòng)增長可以不是第一列，他可以根據(jù)前面幾列進(jìn)行排序后遞增。

innodb引擎的自動(dòng)增長必須是索引，如果是組合索引也必須是組合索引的第一列。count()函數(shù)myisam保存有表的總行數(shù)，如果select count(*) from table;會(huì)直接取出出該值innodb沒有保存表的總行數(shù)，如果使用select count(*) from table；就會(huì)遍歷整個(gè)表，消耗相當(dāng)大，但是在加了wehre 條件后，myisam和innodb處理的方式都一樣。 鎖
表鎖

提供行鎖，另外，InnoDB表的行鎖也不是絕對的，如果在執(zhí)行一個(gè)SQL語句時(shí)MySQL不能確定要掃描的范圍，InnoDB表同樣會(huì)鎖全表， 例如update table set num=1 where name like "%aaa%"

mysql相關(guān)配置參數(shù)優(yōu)化：

? sort-buffer-size/join-buffer-size / read-rnd-buffer-size，4～8MB為宜
? optimizer_switch=“index_condition_pushdown=on,mrr=on,mrr_cost
_based=off,batched_key_access=on”
? tmp-table-size = max-heap-table-size，100MB左右為宜
? log-queries-not-using-indexes & log_throttle_queries_not_using_indexes