索引結構

索引都是採用B+樹，允許在同1列上重複建立索引；

唯一性索引可以避免下一行額外掃描，普通索引在找到侯選列後，會讀取下一行判斷其是否依舊滿足查詢條件，狀態變數Handler_read_next記錄相應操作；

InnoDB預設頁大小16K，預設預留1/16的空閒；如果順序插入則填充因子為15/16，隨機插入則為15/16 – ½，即預留空間比較多；

InnoDB二級索引都包含主鍵列，且為最後1位；

更確切的說，當二級索引不包含或包含部分主鍵列時，InnoDB會自動補全所有主鍵列，但Mysql對此不知曉故無法在sql執行時使用；

索引型別

MyISAM支援fulltext索引且僅限於char/varchar/text；

memory表支援hash 索引；

對blob/text列必須建立prefix索引，即取前N個位元組，字首最大可為1000(InnoDB則為767)；

不支援基於函式的索引；

Mysql分割槽只支援本地索引；

InnoDB快速建立索引

在之前的版本中，建立索引必須建立一張臨時表，然後更新臨時表後將原表刪除，最後重新命名臨時表，比較耗時；

這個過程很低效，因此5.5(包括採用plugin的5.1)引入一個新功能，叫做fast index creation，僅適用於二級索引；

其直接在原表的基礎上建立索引，從而廢除了臨時表的使用，在建立過程中原表新增共享鎖，不阻塞讀操作，且索引頁的填充率即fill factor更高；

注：如果重建聚集索引，依舊採用重建copy的方式，原表加排他鎖，阻塞所有操作；

如果需要建立多個二級索引，可以呼叫alter table add index一次性執行，避免多次掃描表；

在二級索引快速建立時，需要向$TMPDIR目錄寫臨時檔案；

當呼叫alter table … rename column時，為避免innodb和mysql資料字典不一致，依舊採用重建+copy的方法；

5.5版本暫不對foreign key提供此功能；

此功能僅支援InnoDB，故MyISAM依舊採用建立臨時表 + copy的方式建立索引，但可透過調優幾個引數加速索引的建立：

Myisam_max_sort_file_size：重建MyISAM索引允許使用的臨時檔案最大尺寸，如果超出了此規定值，就改用key cache效率就會比較慢；預設2G；

Myisam_sort_buffer_size：用於排序的buffer大小，repair table或建立索引時用到；

key_buffer_size

倘若碰到比較變態的大表，則可採用曲線救國的方法：

案例

為超過1.8億條資料的表建立索引

需用到memory/merge表，也可使用分割槽替代；

首先將max_heap_table_size/tmp_table_size調大至4G或更大(視自身伺服器而定)，用於儲存記憶體表；

建立記憶體表，透過insert … select將原表資料裝入記憶體表(假定1次可裝載1千萬行)，建立索引；如果只有幾千萬行，可以為記憶體表新增索引然後透過insert … select將其重新整理至目標表，反覆幾次即可；

建立18個merge表，insert … select將資料加入記憶體表 à insert … select從記憶體表重新整理至merge表 à truncate記憶體表反覆18次；

建立一個merge表整合18個表，然後insert … select from merge_table order by index1, index2將此表重新整理至目標表即可；

索引Hint

Using index –指定所用索引

Straight_join – 強制表連線順序

最佳化sql解析

查詢涉及的表越多，最佳化器搜尋最佳執行計劃的時間就會越長，可透過如下兩個引數控制解析時間：

Optimizer_prune_level: 預設為0，設定為1時最佳化器估算每個表返回行數進而自動過濾掉一些執行計劃，但也有可能漏掉最優的

Optimizer_search_depth: 0..63預設為62，與查詢涉及的表數目有關；若大於等於表數，則最佳化器會遍歷每個執行計劃以找出最優解，如小於則解析時間會降低，但不保證得到最優解；設定為0則讓最佳化器自主選擇；

Optimizer_switch　

5.1引入此引數，主要用於控制index merge http://dev.mysql.com/doc/refman/5.1/en/index-merge-optimization.html

mysql> SELECT @@optimizer_switch\G

*************************** 1. row ***************************

@@optimizer_switch: index_merge=on,index_merge_union=on,

index_merge_sort_union=on,

index_merge_intersection=on,

engine_condition_pushdown=on

Engine Condition Pushdown	engine_condition_pushdown	Controls engine condition pushdown
Index Merge	index_merge	Controls all Index Merge optimizations
	index_merge_intersection	Controls the Index Merge Intersection Access optimization
	index_merge_sort_union	Controls the Index Merge Sort-Union Access optimization
	index_merge_union	Controls the Index Merge Union Access optimization

執行計劃

使用explain可檢視sql執行計劃

Explain extended—收集額外資訊

Explain partitions—用於分割槽表

其輸出包含多個列，比較重要的如下

Key_len：使用到的索引鍵的位元組長度，其受資料型別,字符集以及not null影響；Null +1位元組；varchar + 2位元組；

長度	資料型別及約束
4	Int not null
5	Int null
30	Char(30) not null
32	Varchar(30) not null
92	Varchar(30) null charset=utf8
3	Date
4	timestamp
8	datetime

Select type

SIMPLE	Simple SELECT (not using UNION or subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	Result of a UNION.
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DERIVED	Derived table SELECT (subquery in FROM clause)
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)