<h3>思考：</h3>
我們知道m(xù)ysql最好的數(shù)據(jù)存儲量級是百萬級別，是的往往在百萬級別或者幾十萬級別就會出現(xiàn)慢查詢(我對慢查詢的定義是大于1秒)，幾年前我所在的一個做pos機支付的聯(lián)機交易的核心系統(tǒng)組，當(dāng)時就做過一次索引優(yōu)化最終的總的交易時間縮短了300毫秒融痛，約占總耗時的1/3。就在近期我所在的部門的一個非常重要的商品結(jié)構(gòu)組(目標(biāo)是像淘寶商品看齊的)嗡午，頻頻爆出慢查詢據(jù)說最慢的有5-6秒汰扭，總感覺太不應(yīng)該稠肘，雖然最重要的接口都是緩存在redis，但是太多慢查詢有可能會拖垮整個數(shù)據(jù)庫萝毛，當(dāng)緩存被穿透了也是要查db的給后續(xù)埋了隱患项阴，所以就引發(fā)了我想寫這篇文章。
<b>1笆包、目前常用的索引有Hash索引和B+樹</b>
1环揽、Hash 索引結(jié)構(gòu)的特殊性，其檢索效率非常高庵佣，索引的檢索可以一次定位歉胶，不像B-Tree 索引需要從根節(jié)點到枝節(jié)點，最后才能訪問到頁節(jié)點這樣多次的IO訪問巴粪，所以 Hash 索引的查詢效率要遠(yuǎn)高于 B+Tree 索引通今。

但是 Hash 索引本身由于其特殊性也帶來了很多限制和弊端，主要有以下這些肛根。
（1）Hash 索引僅僅能滿足"=","IN"和"<=>"查詢辫塌，不能使用范圍查詢。
（2）Hash 索引無法被用來避免數(shù)據(jù)的排序操作晶通。
（3）Hash 索引不能利用部分索引鍵查詢璃氢。
（4）Hash 索引在任何時候都不能避免表掃描。
（5）Hash 索引遇到大量Hash值相等的情況后性能并不一定就會比B+Tree索引高狮辽。
造成如上的主要原因是：hash是生成一個固定的結(jié)果沒法做區(qū)間的查詢也無法做排序一也，hash值是用所有的值來算的部分索引值是無效的，況且我們也可能遇到大量的hash值相同的情況這

2喉脖、B+樹是目前主流的底層實現(xiàn)原理

B+樹是一個平衡的多叉樹椰苟，從根節(jié)點到每個葉子節(jié)點的高度差值不超過1，而且同層級的節(jié)點間有指針相互鏈接树叽。這樣不會造成數(shù)據(jù)傾斜舆蝴，下面介紹一下InnoDB引擎的B+樹結(jié)構(gòu)(MyISAM實現(xiàn)的B+數(shù)略有不同)，B+樹索引可以分為聚集索引(clustered index)和非聚集索引（即輔助索引题诵，secondary index）洁仗。
<b>聚集索引</b>
索引組織表，即表中數(shù)據(jù)按主鍵B+樹存放性锭，葉子節(jié)點直接存放數(shù)據(jù)赠潦，每張表只能有一個聚集索引。當(dāng)你定義primary key時其是聚集索引草冈，如果你自己沒定義則會生成一個默認(rèn)的自增的長整型數(shù)
<b>輔助索引</b>
輔助索引（也稱非聚集索引）是指葉節(jié)點不包含行的全部數(shù)據(jù)她奥，葉節(jié)點除了包含鍵值之外瓮增，還包含一個書簽連接，通過該書簽再去找相應(yīng)的行數(shù)據(jù)哩俭。下圖顯示了InnoDB存儲引擎輔助索引和聚集索引的關(guān)系：

QQ截圖20160907183233.png

以下所有的分析都是基于InnoDB存儲引擎绷跑。
<b>2、分析索引好壞</b>
1凡资、索引分析利器砸捏，explain：
<pre>

mysql> explain select * from user_test;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | user_test | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
</pre>
explain結(jié)果解釋：
<ul>
<li>table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的</li>
<li>type：這是重要的列，顯示連接使用了何種類型讳苦。從最好到最差的連接類型為const（記住一定是用到primary key 或者unique）带膜、eq_reg（一般是設(shè)置primary key或者unique在多表關(guān)聯(lián)情況下）、ref(除了在primary key 或者unique以外的索引或者索引組合)鸳谜、range（如where XXX in(1,2)索引在某個區(qū)間）、index和all(我們的最終的目標(biāo)是往const上靠式廷，上面的結(jié)果All就是沒用過索引的)
<pre>
<h4>const:</h4>
mysql> explain select * from user_test where id = 1;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | user_test | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
<h4>eq_req：</h4>
mysql> explain select * from user_test a inner join user_score b on a.id = b.id ;
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------+
| 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 2 | |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test_copy.b.id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+-------+
<h4>ref：</h4>
mysql> explain select * from user_test where name = 'hahah';
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | user_test | ref | iddex_name | iddex_name | 258 | const | 1 | Using where |
+----+-------------+-----------+------+---------------+------------+---------+-------+------+-------------+
<h4>range:</h4>
mysql> explain select * from user_test where name in ('hahah','hahah22');
+----+-------------+-----------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | user_test | range | iddex_name | iddex_name | 258 | NULL | 2 | Using where |
+----+-------------+-----------+-------+---------------+------------+---------+------+------+-------------+
<h4>index</h4>
mysql> explain select id from user_test;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | user_test | index | NULL | PRIMARY | 4 | NULL | 2 | Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
<h4>All</h4>
mysql> explain select * from user_test;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | user_test | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
</pre>
</li>
<li>possible_keys：顯示可能應(yīng)用在這張表中的索引咐扭。如果為空，沒有可能的索引滑废』确荆可以為相關(guān)的域從where語句中選擇一個合適的語句</li>
<li>key： 實際使用的索引。如果為null蠕趁，則沒有使用索引薛闪。很少的情況下，mysql會選擇優(yōu)化不足的索引俺陋。這種情況下豁延，可以在select語句中使用use index（indexname）來強制使用一個索引或者用ignore index（indexname）來強制mysql忽略索引</li>
<li>key_len：使用的索引的長度。在不損失精確性的情況下腊状，長度越短越好</li>
<li>ref：顯示索引的哪一列被使用了诱咏，如果可能的話，是一個常數(shù)</li>
<li>rows：mysql認(rèn)為必須檢查的用來返回請求數(shù)據(jù)的行數(shù)</li>
<li>extra：關(guān)于mysql如何解析查詢的額外信息缴挖〈看到的壞的例子是using temporary(使用臨時表)和using filesort，意思是mysql根本不能使用索引映屋，結(jié)果是檢索很慢</li>
</ul>
<h4>3苟鸯、一般怎么建索引</h4>
1、充分發(fā)揮like的作用
<pre>
如：select id from t where substring(name,1,3)='abc' 棚点，name以abc開頭的id**
應(yīng)改為:select id from t where name like 'abc%' 這樣當(dāng)name有索引的時候是可以用上索引的早处，如果改成like '%abc'能索引上么，答案是不能
</pre>
2乙濒、索引字段盡量不要設(shè)置為NULL并且進(jìn)行值的where判斷陕赃，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描
3卵蛉、不要在索引字段上使用mysql的函數(shù)，如where substr(date,1,10) = '2016-09-07' 這樣索引是會失效的么库，對于這種情況可以改寫為 date between '2016-09-07 00:00:00' and '2016-09-07 23:59:59'
4傻丝、復(fù)合索引建立以后如index_a_b_c建立在a、b诉儒、c3個字段上：
<pre>
where a=XX and b=XX and c=XX能被索引
where a=XX能被索引
where a=XX and b=XX能被索引
where b=XX 不被索引
where c=XX不被索引
where b=XX and c=XX不被索引
where a=XX and c=XX索引較差
where b=XX and c=XX and a= XX不被索引
你可以理解為當(dāng)順序不一樣時葡缰，索引指向就變了。
如果不是這種情況怎么辦呢忱反？還能怎么辦泛释，修改where順序啊，總比不同的順序再建個索引好
</pre>
5温算、在= 怜校、group by 和 order by字段上面加上索引
6、在join的時候中結(jié)果集更小的部分join更大的部門注竿，這樣可以減少緩存的開銷
7茄茁、索引并不是越多越好不要每一個字段建一個索引，即使這樣mysql也會自身優(yōu)化也只會選擇其中的一個索引來執(zhí)行巩割，索引固然可 以提高相應(yīng)的 select 的效率裙顽，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引宣谈，所以怎樣建索引需要慎重考慮愈犹，視具體情況而定。一個表的索引數(shù)最好不要超過6個闻丑，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有必要漩怎。
8、在使用in的時候可以嘗試使用exists試試
9梆掸、在join的時候減少extra字段中臨時表的數(shù)量扬卷。

<h4>4、結(jié)束語</h4>
中國有句古話"天下武功唯快不破",我想的是mysql查詢優(yōu)化用好explain分析器酸钦，提前建好索引可以減少很多不必要的麻煩怪得。當(dāng)出現(xiàn)慢查詢的時候看好索引或者修改join的寫法，最終用explain來分析孰優(yōu)孰劣卑硫，或許你會有意想不到的收獲徒恋。