innodb引擎下

數(shù)據(jù)類型

• DECIMAL 用于存儲(chǔ)精確的浮點(diǎn)數(shù)據(jù)，但是通常會(huì)消耗更多的空間和計(jì)算開銷。
• VARCHAR 存儲(chǔ)可變長字符串，如果列長小于等于255字節(jié)，需要額外的1字節(jié)記錄字符串長度，大于255字節(jié)，需要額外的2字節(jié)。varchar類型節(jié)省了空間。但是由于行是變長的，當(dāng)update的時(shí)候可能使得行更長，頁內(nèi)沒有更多的空間存儲(chǔ)。對(duì)于MyISAM來說，會(huì)將行拆成不同的片段存儲(chǔ)。InooDB需要分裂頁。所以VARCHAR適用于：字符串列的最大長度比平均大的多，列的更新少
• DATETIME 精確到秒，與時(shí)區(qū)無關(guān)，8字節(jié)存儲(chǔ)空間。
  TIMESTAMP 4字節(jié)存儲(chǔ)空間，顯示的值依賴于時(shí)區(qū)。默認(rèn)情況下，更新記錄時(shí)會(huì)記錄當(dāng)前時(shí)間，如果不想自動(dòng)更新，需設(shè)置DEFAULT CURRENT_TIMESTAMP。

schema

• 范式和反范式：通常范式更新操作要快，占用內(nèi)存較少，更好的放進(jìn)內(nèi)存；但是需要關(guān)聯(lián)，代價(jià)昂貴，且索引策略可能失效。

鎖

表鎖

MDL鎖(metadata lock) 是 server層的表級(jí)別鎖，表的增刪改查加的是讀鎖，表結(jié)構(gòu)變更加的是寫鎖，讀讀之間不沖突，讀寫、寫寫之間沖突。如：當(dāng)一個(gè)查詢很耗時(shí)的時(shí)候，一個(gè)查詢也是起一個(gè)事務(wù)的，這個(gè)讀鎖還未釋放，此時(shí)去變更表結(jié)構(gòu)如加索引等，都會(huì)引起鎖沖突，此后所有關(guān)于這個(gè)表操作都會(huì)堵著。

行鎖

innodb支持行鎖。為了提高并發(fā)性的，讀寫、寫寫互斥，select默認(rèn)不加鎖，另外鎖是加在索引上的。另外為了解決幻讀，增加了間隙鎖（可重復(fù)讀級(jí)別下），與行鎖配合使用。next-key lock

小技巧

• delete時(shí)，可加limit 減少鎖范圍
• count性能，count(*)~count(1)<count(字段)，前兩者每次遍歷一行，加1，不用取數(shù)據(jù)，count（id）需遍歷取數(shù)據(jù)加1.
• alter table t engine=innoDB

索引

• 覆蓋索引(Using index)

• 延遲關(guān)聯(lián)（主要利用覆蓋索引）（如：前：select * from t_portal_user where create_time > '2012-10:10' and create_time<'2017:10:10' LIMIT 5000,10;延遲關(guān)聯(lián)：SELECT * from t_portal_user INNER JOIN (select id from t_portal_user where create_time > '2012-10:10' and create_time<'2017:10:10' LIMIT 5000,10) as a USING(id)）

• 回表

• 索引下推（如索引ca，查詢時(shí)where c like 'lk%' and a=10, 聯(lián)合索引ca都可以用到，直接在索引里過濾兩個(gè)條件，再回表過濾）

• 最左前綴

• 索引合并（index merge）（k1，k2；where k1=1 or k2=2）

• 索引去重（如聯(lián)合主鍵a、b，普通索引c，聯(lián)合索引ca，聯(lián)合索引cb，那么索引c和索引ca是一樣的，都有cab元素，并且是去重的，不是caab等）

• 表達(dá)式無法使用索引

• 同等條件下，聯(lián)合索引盡量將選擇性高的列放到前面

• order by排序時(shí)，索引順序列要和order by字句順序一樣；如果關(guān)聯(lián)多個(gè)表，則order by字句全部是第一個(gè)表的字段時(shí)，才能使用索引排序 如（索引k(a,b,c), where a=1 order by b,c 可以用到k，但是where a=> order by b,c就不能了）

explain 執(zhí)行計(jì)劃

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

id

包含一組數(shù)字，表示查詢中執(zhí)行select子句或操作表的順序

• 原則上從大到小，從上至下，即相同先上后下，不同，先大后小

select_type

查詢中每個(gè)select子句的類型（簡單OR復(fù)雜）

• SIMPLE：查詢中不包含子查詢或者UNION
• 查詢中若包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為：PRIMARY
• 在SELECT或WHERE列表中包含了子查詢，該子查詢被標(biāo)記為：SUBQUERY
• 在FROM列表中包含的子查詢被標(biāo)記為：DERIVED（衍生）用來表示包含在from子句中的子查詢的select，mysql會(huì)遞歸執(zhí)行并將結(jié)果放到一個(gè)臨時(shí)表中。服務(wù)器內(nèi)部稱為"派生表"，因?yàn)樵撆R時(shí)表是從子查詢中派生出來的
• 若第二個(gè)SELECT出現(xiàn)在UNION之后，則被標(biāo)記為UNION；若UNION包含在FROM子句的子查詢中，外層SELECT將被標(biāo)記為：DERIVED
• 從UNION表獲取結(jié)果的SELECT被標(biāo)記為：UNION RESULT

注意：

• SUBQUERY和UNION還可以被標(biāo)記為DEPENDENT和UNCACHEABLE。
• DEPENDENT意味著select依賴于外層查詢中發(fā)現(xiàn)的數(shù)據(jù)。
• UNCACHEABLE意味著select中的某些 特性阻止結(jié)果被緩存于一個(gè)item_cache中。

type

ALL, index, range, ref, eq_ref, const, system, NULL
從左至右，由差至好

• ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行
• index：Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹
• range:索引范圍掃描，對(duì)索引的掃描開始于某一點(diǎn)，返回匹配值域的行。
• ref：使用非唯一索引掃描或者唯一索引的前綴掃描，返回匹配某個(gè)單獨(dú)值的記錄行
• eq_ref：類似ref，區(qū)別就在使用的索引是唯一索引，對(duì)于每個(gè)索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件。
• const、system：當(dāng)MySQL對(duì)查詢某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個(gè)常量時(shí)，使用這些類型訪問。system是const類型的特例，當(dāng)查詢的表只有一行的情況下，使用system
• NULL：MySQL在優(yōu)化過程中分解語句，執(zhí)行時(shí)甚至不用訪問表或索引， 例如從一個(gè)索引列里選取最小值可以通過單獨(dú)索引查找完成。

possible_keys

指出MySQL能使用哪個(gè)索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用

key

顯示MySQL在查詢中實(shí)際使用的索引，若沒有使用索引，顯示為NULL

key_len

表示索引中使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中使用的索引的長度（key_len顯示的值為索引字段的最大可能長度，并非實(shí)際使用長度，即key_len是根據(jù)表定義計(jì)算而得，不是通過表內(nèi)檢索出的）。

ref

表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值。

Extra

• Using index（覆蓋索引）
• Using where
  表示mysql服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過濾。許多where條件里涉及索引中的列，當(dāng)（并且如果）它讀取索引時(shí)，就能被存儲(chǔ)引擎檢驗(yàn)，因此不是所有帶where字句的查詢都會(huì)顯示"Using where"。
• Using temporary 表示MySQL需要使用臨時(shí)表來存儲(chǔ)結(jié)果集，常見于排序和分組查詢
• Using filesort MySQL中無法利用索引完成的排序操作稱為“文件排序”
• Using join buffer該值強(qiáng)調(diào)了在獲取連接條件時(shí)沒有使用索引，并且需要連接緩沖區(qū)來存儲(chǔ)中間結(jié)果。
• Impossible where這個(gè)值強(qiáng)調(diào)了where語句會(huì)導(dǎo)致沒有符合條件的行。

工具

• percona toolkit 工具箱