索引問題 - 簡書

1.索引存儲分類
索引是在MySQL的存儲引擎層實現(xiàn)的酝蜒，每個存儲引擎的索引不一定相同绍坝。MySQL提供以下4種索引：

B-Tree索引：最常見的索引類型撕蔼，大部分引擎都支持B數(shù)索引屯援。
HASH索引：只有Memory引擎支持剂公。
R-Tree索引（空間索引）：MyISAM的一個特殊索引類型，主要用于地理空間數(shù)據(jù)類型典挑。
Full-text（全文索引）：MyISAM的一個特殊類型酥宴，用于全文索引，Innodb5.6版本開始也支持您觉。

MySQL支持前綴索引拙寡，缺點是在排序order by和分組group by時無法使用。

2.MySQL如何索引
可以利用B-Tree索引進行全關(guān)鍵字琳水、關(guān)鍵字范圍和關(guān)鍵字前綴查詢肆糕。

（1） MySQL能夠使用索引的典型場景：

匹配全值，對索引中所有列都指定具體值在孝，即對索引所有列都有等值匹配條件诚啃。
匹配值的范圍查詢，對索引值能夠進行范圍查找私沮。
匹配最左前綴始赎，僅僅使用索引中的最左邊列進行查找。最左匹配原則可以算B-Tree索引使用的首要原則仔燕。
僅僅對索引進行查詢造垛，當查詢列都在索引的字段中時，查詢效率更高晰搀。
匹配列前綴五辽，僅僅使用索引中的第一列，并且只包含索引第一列開頭一部分進行查找厕隧。
能夠?qū)崿F(xiàn)索引匹配部分精確而其他部分進行范圍匹配奔脐。
如果列名是索引，那么使用column_name is null 就會使用索引吁讨。（區(qū)別于Oracle）
5.6引入Index Condition Pushdown（ICP）特性髓迎，優(yōu)化查詢。

（2）存在索引但不能使用索引典型場景：

以%開頭的like查詢不能夠利用B-Tree索引建丧。
數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)換的時候不會使用索引排龄，特別是當列類型是字符串，那么一定記得在where條件中八字符串常量用引號引起來翎朱，否則即便這個列上有索引橄维，MySQL也不會用到，因為MySQL默認把輸入的常量值進行轉(zhuǎn)換后才進行檢索拴曲。
復(fù)合索引時争舞，假如查詢條件不包含索引列最左邊部分，不會使用復(fù)合索引澈灼。
若使用索引比全表掃描更慢竞川，則不使用索引店溢。篩選性越高越容易使用到索引，篩選性越低越不容易使用索引委乌。
用or分隔的條件床牧，若or前的條件中的列有索引，而后面沒有遭贸，那么涉及的索引不會被用到戈咳。因為后面的列沒有索引，一定會全表掃描壕吹，不必多作一次索引掃描著蛙。

（3）查看索引使用情況
若索引正在工作，則Handler_read_key的值將很高算利，這個值表示一個行被索引值讀的次數(shù)册踩，值低表明增加索引性能改善不高，即該索引并不經(jīng)常使用。

Handler_read_rnd_next值高意味著查詢運行低效，并且應(yīng)該建立索引補救罗晕。這個值含義是在數(shù)據(jù)文件中讀下一行的請求數(shù)谎势。

3.優(yōu)化方法

定期分析表和檢查表：
分析表語法如下：

analyze [LOCAL|NO_WRITE_BINLOG|TABLE] tbl_name [,tbl_name]...

分析和存儲表的關(guān)鍵字分布，分析結(jié)果將可以使得系統(tǒng)得到準確的統(tǒng)計信息，使得SQL能夠生成正確的執(zhí)行計劃。分析期間，使用一個讀取鎖定對表進行鎖定肮街。對MyISAM、DBD和InnoDB表有作用判导。
檢查表語法如下：

check table tbl_name [,tbl_name] ... [option]...option={QUICK|FAST|MEDIUM|EXDIUM|EXTENDED|CHANGED}

作用是檢查一個或多個表是否有誤嫉父，對MyISAM、InnoDB表有作用眼刃，也可以用于檢查視圖绕辖。

定期優(yōu)化：
優(yōu)化表語法如下：

optimize [LOCAL|NO_WRITE_TO_BINLOG] table tbl_name[,tbl_name]...

如果已經(jīng)刪除表的一大部分，或者已經(jīng)對含有可變長度行的表進行了很大更改擂红，則應(yīng)使用optimize table進行表優(yōu)化仪际。該命令可以將表空間碎片進行合并，支隊MyISAM昵骤、BDB树碱、InnoDB表有作用。在刪除大量數(shù)據(jù)后变秦，InnoDB表可以用alter table但不修改引擎方式回收不用空間成榜。

4.常用SQL優(yōu)化

大批量插入數(shù)據(jù)：當使用load命令導(dǎo)入數(shù)據(jù)時，設(shè)當設(shè)置可以提高導(dǎo)入速度蹦玫。對于MyISAM表赎婚，可以通過以下方式快速導(dǎo)入大量數(shù)據(jù)雨饺。

alter table name disable keys;
loading the data
alter table name enable keys;

disable/enable keys用來打開/關(guān)閉MyISAM表非唯一索引的更新。

對于InnoDB類型惑淳，可以用以下幾種方式提高導(dǎo)入效率：
（1）因為InnoDB類型表是按照主鍵順序保存的，所以將導(dǎo)入的數(shù)據(jù)按照主鍵順序排列饺窿，可以有效提高導(dǎo)入數(shù)據(jù)的效率歧焦。
（2）在導(dǎo)入數(shù)據(jù)前執(zhí)行set unique_checks=0，關(guān)閉唯一性校驗肚医，導(dǎo)入完成后恢復(fù)唯一性校驗绢馍。
（3）如果應(yīng)用使用自動提交方式，導(dǎo)入前執(zhí)行set autocommit=0肠套，關(guān)閉自動提交舰涌，導(dǎo)入結(jié)束后打開自動提交。

優(yōu)化insert語句
（1）如果同時從同一客戶插入很多行你稚，應(yīng)該盡量使用多個值表的insert語句瓷耙，將縮減客戶端于數(shù)據(jù)庫間的連接、關(guān)閉等消耗刁赖。例如：

insert into name values(1,1),(2,2)...

(2)如果從不同客戶端插入很多行搁痛，可以通過使用insert delayed語句得到更高速度。delayed含義是讓insert語句馬上執(zhí)行宇弛，其實數(shù)據(jù)都被放在內(nèi)存隊列中鸡典，并沒有真正寫入磁盤；low_priority剛好相反枪芒，在所有其他用戶線程對表的讀寫完成后進行彻况。
（3）將索引和數(shù)據(jù)文件分布在不同磁盤是存儲。
（4）如果進行批量插入舅踪，可以增加bulk_insert_buffer_size提高速度纽甘，只能對MyISAM使用。
（5）當從一個文本文件裝入一個表時硫朦，使用load data infile贷腕。通常比使用insert快。

優(yōu)化order by 語句
（1）MySQL中兩者排序方式：第一種通過有序索引直接返回有序數(shù)據(jù)咬展，這種方法在使用explain分析程序是顯示為using index泽裳，不需要額外排序。
（2）第二種通過對返回數(shù)據(jù)進行排序破婆，所有不是通過索引直接返回排序結(jié)果的排序都叫Filesort排序

盡量減少額外排序涮总，通過索引直接返回有序數(shù)據(jù)。

Filesort優(yōu)化
通過創(chuàng)建合適的索引能夠減少Filesort出現(xiàn)祷舀。對于Filesort瀑梗，MySQL有兩種排序算法烹笔。
（1）兩次掃描：先根據(jù)條件排序和行指針信息第二次讀取存在可能導(dǎo)致大量隨機I/O，有點是內(nèi)存開銷少抛丽。
（2）一次掃描算法：一次性取出索引滿足條件行的索引字段谤职，內(nèi)存消耗較。

MySQL通過比較亿鲜，max_length_for _data大小和query語句取出字段中大小來判斷使用哪種排序允蜈。如果max_length_for _data值更大，使用第二種優(yōu)化后的算法蒿柳，否則就是要第一章算法饶套。

優(yōu)化group by語句
默認MySQL對所有g(shù)roup by的字段進行排序，想要避免排序消耗可以指定order by null禁止排序垒探。
優(yōu)化嵌套查詢
連接（join）更有效一些妓蛮，是因為MySQL不需要在內(nèi)存中創(chuàng)建臨時表來完成邏輯上需要兩個步驟的查詢工作。
MySQL如何優(yōu)化or條件
對于含有or的查詢子句圾叼，如果要利用索引蛤克，則or之間的每個條件都必須用到索引。在建有復(fù)合索引的列上面做or操作時褐奥，卻不能用到索引咖耘。
優(yōu)化分頁查詢
一般分頁查詢時，通過創(chuàng)建覆蓋索引能夠比較好提高性能撬码。
（1）第一種優(yōu)化思路：在索引上完成排序分頁操作儿倒，最后根據(jù)主鍵關(guān)聯(lián)回原表查詢所需要的其他內(nèi)容。讓MySQL掃描盡可能少的頁面來提高分頁效率呜笑。

（2）第二種優(yōu)化思路：把limit 查詢轉(zhuǎn)換成某個位置的查詢夫否，和開發(fā)協(xié)商，在翻頁過程中增加一個參數(shù)last_page_record,用來記錄上一頁最后一行租賃編號叫胁。把limit m凰慈，n轉(zhuǎn)換成limit n查詢，只適合在排序字段不會出現(xiàn)重復(fù)值的特定環(huán)境驼鹅，能夠減輕分頁翻頁壓力微谓；如果排序字段出現(xiàn)大量重復(fù)值，不適合這種方式優(yōu)化输钩。

使用SQL提示
sql提示是在SQL語句中加入一些人為的提示來達到優(yōu)化操作的目的豺型。
（1）use index：在查詢語句中表名后面，添加use index來提供希望MySQL去參考索引列表买乃，就可以讓MySQL不在考慮其他索引姻氨。
（2）ignore index：單純的想讓MySQL忽略一個或多個索引，則可以使用ignore index作為hint剪验。
（3）force index：為強制MySQL使用一個特定的索引肴焊，可以在查詢中使用force index作為hint前联。