最近項目剛剛上線，所以有時間對項目進行優(yōu)化，索引首當(dāng)其沖，但自己對數(shù)據(jù)庫這方面知識了解很少，只能網(wǎng)上學(xué)習(xí)，發(fā)現(xiàn)了一篇非常好的文章。反復(fù)閱讀加測試。把一點心得寫在這里。

先放文章的鏈接：

https://tech.meituan.com/2014/06/30/mysql-index.html

建索引的幾大原則

1.最左前綴匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。

2.=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會幫你優(yōu)化成索引可以識別的形式。

3.盡量選擇區(qū)分度高的列作為索引，區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0，那可能有人會問，這個比例有什么經(jīng)驗值嗎？使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄。

4.索引列不能參與計算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進行檢索時，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’)。

5.盡量的擴展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可。

查詢優(yōu)化神器 - explain命令

關(guān)于explain命令相信大家并不陌生，具體用法和字段含義可以參考官網(wǎng)explain-output，這里需要強調(diào)rows是核心指標，絕大部分rows小的語句執(zhí)行一定很快（有例外，下面會講到）。所以優(yōu)化語句基本上都是在優(yōu)化rows。

慢查詢優(yōu)化基本步驟

0.先運行看看是否真的很慢，注意設(shè)置SQL_NO_CACHE

1.where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應(yīng)用到表中返回的記錄數(shù)最小的表開始查起，單表每個字段分別查詢，看哪個字段的區(qū)分度最高

2.explain查看執(zhí)行計劃，是否與1預(yù)期一致（從鎖定記錄較少的表開始查詢）

3.order by limit 形式的sql語句讓排序的表優(yōu)先查

4.了解業(yè)務(wù)方使用場景

5.加索引時參照建索引的幾大原則

6.觀察結(jié)果，不符合預(yù)期繼續(xù)從0分析

關(guān)于對explain的使用，可以結(jié)合mysql官方文檔

這里需要強調(diào)rows是核心指標，絕大部分rows小的語句執(zhí)行一定很快（有例外）。所以優(yōu)化語句基本上都是在優(yōu)化rows。

下面為我測試的一個表：
sql:

explain
select sql_no_cache *
from test_index
where a = 1 and   c = 1 and  b = 1;

結(jié)果：

建表語句：

CREATE TABLE `test_index` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `f` int(11) DEFAULT NULL,
  `char1` char(10) DEFAULT NULL,
  `varchar1` varchar(10) DEFAULT NULL,
  KEY `abcd` (`a`,`b`,`c`,`d`),
  KEY `idx_char1` (`char1`),
  KEY `idx_varchar1` (`varchar1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

select_type: 說明這是一個簡單的sql語句，沒有使用連接等操作。
possible_keys: 說明可能使用的索引
key：使用的索引，如果沒有使用索引則為空
key_len：是索引的長度，這里要簡單說下key_len計算
為上面例子為例：key_len為14. 那么這個14是如何出來的呢？
14 = 4 + 4 + 4 + 2
索引走的是abcd。這個索引是由4個int類型的字段組成的。一個int類型占4字節(jié)（byte), 根據(jù)sql的where條件，其中a,b,c三個字段使用了索引所以有4 + 4 + 4 ，mysql中如果允許為null，null需要一個標志位，占一個字節(jié)，字段a不為null, b,c可以為null所以有了+2 。從上面可以看出，有時根據(jù)ken_len是可以判斷哪些字段走了索引的。
在測一下，如下sql，ken_len應(yīng)該是多少呢？

explain
select sql_no_cache *
from test_index
where a = 1 and  b= 1 and  c in (1) and  d = 1 ;

ken_len = 19 這是符合我們的預(yù)期的。
如果字段是字符類型的呢，字符類型與數(shù)據(jù)庫所使用的編碼集是有關(guān)系的。其中如果是可變字符（varchar）則要在額外增加兩個字節(jié)。

key_len的長度計算公式：
varchr(10)變長字段且允許NULL : 10(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(變長字段)
varchr(10)變長字段且不允許NULL : 10(Character Set：utf8=3,gbk=2,latin1=1)+2(變長字段)
char(10)固定字段且允許NULL : 10(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允許NULL : 10(Character Set：utf8=3,gbk=2,latin1=1)

對于是索引的字段，最好使用定長和NOT NULL定義，提高性能。原因與b+樹性質(zhì)有關(guān)：IO次數(shù)取決于b+數(shù)的高度h，假設(shè)當(dāng)前數(shù)據(jù)表的數(shù)據(jù)為N，每個磁盤塊的數(shù)據(jù)項的數(shù)量是m，則有h=㏒(m+1)N，當(dāng)數(shù)據(jù)量N一定的情況下，m越大，h越??；而m = 磁盤塊的大小 / 數(shù)據(jù)項的大小，磁盤塊的大小也就是一個數(shù)據(jù)頁的大小，是固定的，如果數(shù)據(jù)項占的空間越小，數(shù)據(jù)項的數(shù)量越多，樹的高度越低。這就是為什么每個數(shù)據(jù)項，即索引字段要盡量的小，比如int占4字節(jié)，要比bigint8字節(jié)少一半。這也是為什么b+樹要求把真實的數(shù)據(jù)放到葉子節(jié)點而不是內(nèi)層節(jié)點，一旦放到內(nèi)層節(jié)點，磁盤塊的數(shù)據(jù)項會大幅度下降，導(dǎo)致樹增高。當(dāng)數(shù)據(jù)項等于1時將會退化成線性表。

ref字段是說明：The columns compared to the index即與索引比較的列，const表示為常量
rows說明：預(yù)估檢查了多少行
filtered ： Percentage of rows filtered by table condition

接下來記錄下常用mysql中不同類型字段占用字節(jié)（byte）大小

范圍查詢之Like使用：

explain select sql_no_cache * from test_index where varchar1 like '%1%' ;

結(jié)果：

explain select sql_no_cache * from test_index where varchar1 like '%1';

結(jié)果：

explain select sql_no_cache * from test_index where varchar1 like '1%';

結(jié)果：

原因還是最左匹配，你要讓mysql知道跟什么值進行對比

其它：
查詢mysql版本：select version();