索引選擇策略：

索引的選擇性（Selectivity），是指不重復(fù)的索引值（也叫基數(shù)，Cardinality）與表記錄數(shù)（#T）的比值：取值范圍為(0, 1]，選擇性越高的索引價(jià)值越大[即索引列值越離散索引價(jià)值越大，如：性別就男女2個(gè)值，在性別列上建立索引無(wú)法達(dá)到快速定位縮小行范圍目的]

SELECT count(DISTINCT(first_name)) / count(*)AS Selectivity FROM employees;

SELECT count(DISTINCT(concat(first_name,last_name)))/count(*)AS Selectivity FROM employees;

SELECT count(DISTINCT(concat(first_name,left(last_name,4))))/count(*) AS Selectivity FROM employees;

選擇索引字段原則：

數(shù)據(jù)類型： 選范圍小的 & 選類型簡(jiǎn)單的 & 盡量not null

（mysql難以優(yōu)化包含null字段查詢，有必要時(shí)設(shè)置一個(gè)默認(rèn)值）

字段值： 值域盡量離散，區(qū)分度高

索引優(yōu)化策略：

1. 【強(qiáng)制】業(yè)務(wù)上具有唯一特性的字段，即使是組合字段，也必須建成唯一索引。 說(shuō)明：不要以為唯一索引影響了insert速度，這個(gè)速度損耗可以忽略，但提高查找速度是明顯的；另外，即使在應(yīng)用層做了非常完善的校驗(yàn)控制，只要沒(méi)有唯一索引，根據(jù)墨菲定律，必然有臟數(shù)據(jù)產(chǎn)生。

2.【強(qiáng)制】 超過(guò)三個(gè)表禁止join（消耗較多內(nèi)存，產(chǎn)生臨時(shí)表）。需要join的字段，數(shù)據(jù)類型必須絕對(duì)一致；多表關(guān)聯(lián)查詢時(shí)，保證被關(guān)聯(lián)的字段需要有索引。 說(shuō)明：即使雙表join也要注意表索引、SQL性能。

3.【強(qiáng)制】在varchar字段上建立索引時(shí)，必須指定索引長(zhǎng)度，沒(méi)必要對(duì)全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長(zhǎng)度即可。 說(shuō)明：索引的長(zhǎng)度與區(qū)分度是一對(duì)矛盾體，一般對(duì)字符串類型數(shù)據(jù)，長(zhǎng)度為20的索引，區(qū)分度會(huì)高達(dá)90%以上，可以使用count(distinct left(列名, 索引長(zhǎng)度))/count(*)的區(qū)分度來(lái)確定。

4.【強(qiáng)制】頁(yè)面搜索嚴(yán)禁左模糊或者全模糊(造成索引失效)，如果需要請(qǐng)走搜索引擎來(lái)解決。 說(shuō)明：索引文件具有B-Tree的最左前綴匹配特性，如果左邊的值未確定，那么無(wú)法使用此索引。右模糊能命中索引

5.【推薦】如果有order by的場(chǎng)景，請(qǐng)注意利用索引的有序性。order by 最后的字段是組合索引的一部分，并且放在索引組合順序的最后，避免出現(xiàn)file_sort的情況，影響查詢性能。 正例：where a=? and b=? order by c; 索引：a_b_c 反例：索引中有范圍查找，那么索引有序性無(wú)法利用，如：WHERE a>10 ORDER BY b; 索引a_b無(wú)法排序。

6.【推薦】利用覆蓋索引來(lái)進(jìn)行查詢操作，避免回表。 說(shuō)明：如果一本書(shū)需要知道第11章是什么標(biāo)題，會(huì)翻開(kāi)第11章對(duì)應(yīng)的那一頁(yè)嗎？目錄瀏覽一下就好，這個(gè)目錄就是起到覆蓋索引的作用。 正例：能夠建立索引的種類：主鍵索引、唯一索引、普通索引，而覆蓋索引是一種查詢的一種效果，用explain的結(jié)果，extra列會(huì)出現(xiàn)：using index。

7.【推薦】利用延遲關(guān)聯(lián)或者子查詢優(yōu)化超多分頁(yè)場(chǎng)景。[優(yōu)化深度分頁(yè)問(wèn)題]

說(shuō)明：MySQL并不是跳過(guò)offset行，而是取offset+N行，然后返回放棄前offset行，返回N行，那當(dāng)offset特別大的時(shí)候，效率就非常的低下，要么控制返回的總頁(yè)數(shù)，要么對(duì)超過(guò)特定閾值的頁(yè)數(shù)進(jìn)行SQL改寫(xiě)。 正例：先快速定位需要獲取的id段，然后再關(guān)聯(lián)：

正例：先快速定位需要獲取的id段，然后再關(guān)聯(lián)：

SELECT a.* FROM 表1 a, (select id from 表1 where 條件 LIMIT 100000,20 ) b where a.id=b.id

8.【推薦】 SQL性能優(yōu)化的目標(biāo)：至少要達(dá)到 range 級(jí)別，要求是ref級(jí)別，如果可以是consts最好。 說(shuō)明： 1）consts 單表中最多只有一個(gè)匹配行（主鍵或者唯一索引），在優(yōu)化階段即可讀取到數(shù)據(jù)。 2）ref 指的是使用普通的索引（normal index）。 3）range 對(duì)索引進(jìn)行范圍檢索。 反例：explain表的結(jié)果，type=index，索引物理文件全掃描，速度非常慢，這個(gè)index級(jí)別比較range還低，與全表掃描是小巫見(jiàn)大巫。

9.【推薦】建組合索引的時(shí)候，區(qū)分度最高的在最左邊。 正例：如果where a=? and b=? ，a列的幾乎接近于唯一值，那么只需要單建idx_a索引即可。 說(shuō)明：存在非等號(hào)和等號(hào)混合判斷條件時(shí)，在建索引時(shí)，請(qǐng)把等號(hào)條件的列前置。如：where a>? and b=? 那么即使a的區(qū)分度更高，也必須把b放在索引的最前列。

10.【參考】創(chuàng)建索引時(shí)避免有如下極端誤解： 1）誤認(rèn)為一個(gè)查詢就需要建一個(gè)索引。 2）誤認(rèn)為索引會(huì)消耗空間、嚴(yán)重拖慢更新和新增速度。 3）誤認(rèn)為唯一索引一律需要在應(yīng)用層通過(guò)“先查后插”方式解決。

11.表必須有主鍵建議使用自增id作為主鍵，建議不要選擇字符串作為主鍵(如：身份證號(hào)、UUID)，空間占用大&索引效率低。

12.合理創(chuàng)建復(fù)合索引(避免冗余)-最左前綴： index(a,b,c) 相當(dāng)于建立index(a),index(a,b),index(a,b,c)

13.不要索引列上進(jìn)行數(shù)學(xué)或函數(shù)運(yùn)算-會(huì)導(dǎo)致索引失效

反例： SELECT c2,c3 FROM t WHERE date(c1) = ‘2016-10-15’

14.對(duì)字符列使用前綴索引：區(qū)分度決定索引長(zhǎng)度[count(distinct left(列名, 索引長(zhǎng)度))/count(*)的區(qū)分度]，一般不超過(guò)20

15.不使用 左模糊或者全模糊(造成索引失效)，右模糊可以命中索引

反例： SELECT c2,c3 FROM t WHERE c4 like ‘%???%’

16.不使用反向查詢，如： not in / not like -無(wú)法使用索引，全表掃描。

17.避免隱士類型轉(zhuǎn)換，導(dǎo)致索引失效： 類型不匹配導(dǎo)致

select user_id from user_info where user_id = '123'

18.最左前綴，最多利用一個(gè)范圍條件：2個(gè)及以上導(dǎo)致索引失效

正例：

SELECT*FROM employees.titles WHERE emp_no<'10010'and title='Senior Engineer';

反例：

SELECT*FROM employees.titles

WHERE emp_no<'10010'

AND title='Senior Engineer'

AND from_date BETWEEN'1986-01-01'AND'1986-12-31';

19.in 代替or ,in的值不超過(guò)1000個(gè)。

20.禁用select * ,使用哪些列就查詢哪些列。（選擇更多列意味占用更多buffer緩沖區(qū)，如果超過(guò)最大buffer緩存區(qū)能容納size則會(huì)進(jìn)行IO讀取建立臨時(shí)表等操作，速度降低。所以盡量查我們需要的列）無(wú)法使用覆蓋索引&降低解析成本。

21.能UNION ALL就不要UNION（UNION需要去重，會(huì)產(chǎn)生臨時(shí)表）

22.不要使用外鍵：高并發(fā)場(chǎng)景影響性能。

23.盡量不使用TEXT、BLOB類型，如果使用 拆分大字段和訪問(wèn)頻率低的字段，分離冷熱數(shù)據(jù)。

24.分表策略： hash or 日期時(shí)間(yyyy-mm-dd格式)

運(yùn)維好習(xí)慣：

1.關(guān)閉QUERY CACHE

–絕大多數(shù)情況下雞肋，最好關(guān)閉

–QC鎖是全局鎖，每次更新QC的內(nèi)存塊鎖代價(jià)高，出現(xiàn)Waiting for query cache lock狀態(tài)的頻率很高

–實(shí)例啟動(dòng)前設(shè)置query_cache_type = 0 & query_cache_size =0

2.使用獨(dú)立undo表空間

–避免ibdata1文件存儲(chǔ)空間暴漲

–MySQL 5.6開(kāi)始支持獨(dú)立表空間

–MySQL 5.7還可以回收已經(jīng)purge的表空間

–提高file i/o能力，并適當(dāng)增加purge線程數(shù)innodb_purge_threads

–事務(wù)及時(shí)提交，不要積壓。并且默認(rèn)打開(kāi)autocommit = 1

3.啟用thread pool

–應(yīng)對(duì)突發(fā)短連接

–extra port

?沒(méi)thread pool怎么辦

–想辦法啟用連接池或其他替代方案

–適當(dāng)調(diào)低超時(shí)閾值，減少空閑連接

4.幾個(gè)關(guān)鍵選項(xiàng)

–innodb_buffer_pool_size，約物理內(nèi)存的50% ~ 70%

–innodb_log_file_size，5.5及以上2G+，5.5以下建議不超512M

–innodb_flush_log_at_trx_commit，0=>最快數(shù)據(jù)最不安全，1=>最慢最安全，2=>折中

–innodb_max_dirty_pages_pct，25%~50%為宜

–max_connections，突發(fā)最大連接數(shù)的80%為宜，過(guò)大容易導(dǎo)致全部卡死

5.啟用輔助監(jiān)控機(jī)制

–干掉超過(guò)N秒的SQL

–干掉疑似注入SQL

–干掉長(zhǎng)時(shí)間不活躍的sleep連接

6.autocommit

–避免某些行鎖被長(zhǎng)時(shí)間持有，影響tps

–更嚴(yán)重時(shí)，可能連接數(shù)暴漲，導(dǎo)致整個(gè)實(shí)例掛掉

–采用gui客戶端連接時(shí)，記得及時(shí)關(guān)閉連接，或設(shè)置超時(shí)閾值以及自動(dòng)提交，否則容易發(fā)生行鎖等待問(wèn)題

關(guān)于EXPLAIN：

–關(guān)鍵業(yè)務(wù)SQL上線前，都要EXPLAIN確認(rèn)其執(zhí)行計(jì)劃

–或提前分析slow query log，防患未然

–EXPLAIN中如果有Using temporary、Using filesort、或type=ALL時(shí)，盡量想辦法進(jìn)行優(yōu)化

參見(jiàn)：

http://www.cnblogs.com/hellojesson/p/6001685.html