Innodb存儲(chǔ)
表空間是邏輯存放所有數(shù)據(jù)的地方，默認(rèn)情況下會(huì)共享一個(gè)表空間——ibdata1，但如果把innodb_file_per_table=ON后每張表可以單獨(dú)放到一個(gè)表空間內(nèi)，但還是有很多數(shù)據(jù)保存在共享的表ibdata1中，如undo信息等。
表空間由各種段(segment)組成，常見(jiàn)的段有數(shù)據(jù)段、索引段等。Innodb是索引組織的，數(shù)據(jù)段就是clustered index的葉結(jié)點(diǎn)。需要注意的是，不是每個(gè)對(duì)象都有段。
區(qū)(extend)是由64個(gè)連續(xù)的頁(yè)組成，每個(gè)頁(yè)（page）固定為16KB，所以每個(gè)區(qū)總共為1M。頁(yè)是innodb最小的磁盤管理單位。
Innodb是按行進(jìn)行存放的，每個(gè)區(qū)最少可以保存2條記錄，否則就成鏈?zhǔn)浇Y(jié)構(gòu)了。每行數(shù)據(jù)除了自定義列以外，還會(huì)增加事務(wù)id和回滾指針列。如果沒(méi)有定義primary key也沒(méi)有not null的unique,則會(huì)增加6字節(jié)的RowId列作為主鍵。
圖片來(lái)自：http://www.cnblogs.com/chjw8016/archive/2011/03/08/1976891.html
Innodb表的限制
一個(gè)表不能包含超過(guò)1000列。
內(nèi)部最大鍵長(zhǎng)度是3500字節(jié)，但MySQL自己限制這個(gè)到1024字節(jié)。
除了VARCHAR, BLOB和TEXT列，最大行長(zhǎng)度稍微小于數(shù)據(jù)庫(kù)頁(yè)的一半。即，最大行長(zhǎng)度大約8000字節(jié)。LONGBLOB和LONGTEXT列必須小于4GB, 總的行長(zhǎng)度，頁(yè)包括BLOB和TEXT列，必須小于4GB。InnoDB在行中存儲(chǔ)VARCHAR，BLOB或TEXT列的前768字節(jié)，余下的存儲(chǔ)的分散的頁(yè)中。
雖然InnoDB內(nèi)部地支持行尺寸大于65535，你不能定義一個(gè)包含VARCHAR列的，合并尺寸大于65535的行。
· mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
· -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
· -> f VARCHAR(10000), g VARCHAR(10000));
· ERROR 1118 (42000): Row size too large. The maximum row size for the
· used table type, not counting BLOBs, is 65535. You have to change some
· columns to TEXT or BLOBs
在一些更老的操作系統(tǒng)上，數(shù)據(jù)文件必須小于2GB。
InnoDB日志文件的合并尺寸必須小于4GB。
最小的表空間尺寸是10MB。最大的表空間尺寸是4,000,000,000個(gè)數(shù)據(jù)庫(kù)頁(yè)（64TB）。這也是一個(gè)表的最大尺寸。
InnoDB表不支持FULLTEXT索引
Innodb索引
默認(rèn)情況下Memory使用存儲(chǔ)hash索引，但也支持b+tree索引。Hash索引只用于=或者<=>的等式比較，不能用來(lái)加速order by操作，只能通過(guò)關(guān)鍵字來(lái)搜索一行。innodb只支持b+樹(shù)索引，進(jìn)一步分為clustered index 與 secondary index。在一次查詢中，只能使用一個(gè)索引。
Innodb是索引組織表，clustered index的葉結(jié)點(diǎn)保存著整行的數(shù)據(jù)。如果，定義了primary key，則clustered index就是primary key的索引；如果沒(méi)有定義primary key mysql會(huì)選中第一個(gè)僅有not null列的unique索引作為主鍵，并把此索引當(dāng)作clustered index使用；如果沒(méi)找到這樣的列，innodb會(huì)創(chuàng)建一個(gè)6字節(jié)的RowId作為主鍵。所以每張表有且只有一個(gè)clustered index。
Secondary index的葉結(jié)點(diǎn)不包括行的全部數(shù)據(jù)，包含鍵值以外還包括一個(gè)bookmark，可以告訴innodb到什么地方可以找到相對(duì)應(yīng)的完整行數(shù)據(jù)，還保存了主鍵的健值。Secondary index包含主鍵，但不包含完整的行數(shù)據(jù)，所以innodb總是會(huì)先從secondary index的葉節(jié)點(diǎn)判斷是否能得到所需的數(shù)據(jù)。如,
Create table t(a int, b varchar(20), primary key(a), key(b));
Explain select * from t;
會(huì)發(fā)現(xiàn)mysql選擇了索引b，而不是a.
復(fù)合索引
復(fù)合索引是在多列（>=2）上建立的索引，又叫多列索引或聯(lián)合索引。Innodb中的復(fù)合索引也是b+ tree結(jié)構(gòu)。索引的數(shù)據(jù)包含多列(col1, col2, col3…)，在索引中依次按照col1, col2, col3排序。如(1, 2), (1, 3),(2,0)…
使用復(fù)合索引要充分利用最左前綴原則，顧名思義，就是最左優(yōu)先。如創(chuàng)建索引ind_col1_col2(col1, col2)，那么在查詢where col1 = xxx and col2 = xx或者where col1 = xxx都可以走ind_col1_col2索引。
在創(chuàng)建多列索引時(shí)，要根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁且過(guò)濾效果好的的一列放在最左邊。
索引操作
可以通過(guò)DML語(yǔ)句操作innodb索引。因?yàn)?/span>innodb是索引組織的表，對(duì)索引的操作會(huì)造成鎖表，先生成一張臨時(shí)表，將數(shù)據(jù)從原始表中寫到臨時(shí)表，再將原始表刪除，最后將臨時(shí)表表名改為原始表表名！因增加、刪除、修改字段會(huì)對(duì)主索引產(chǎn)生影響，所以也會(huì)鎖表。對(duì)secondary index從Innodb plugin開(kāi)始，支持快速索引創(chuàng)建的方法，在創(chuàng)建的過(guò)程中不需要重建表，所以速度會(huì)很快，同時(shí)引擎會(huì)在表上加S鎖，在創(chuàng)建過(guò)程中只能進(jìn)行讀操作。
索引設(shè)計(jì)原則
1. 搜索的索引列，不一定是所要選擇的列。也就是說(shuō)，最適合索引的列是出現(xiàn)在where子句中的列，或者連接子句中指定的列，而不是出現(xiàn)在select關(guān)鍵字后的選擇列表中的列。
2. 使用唯一索引?？紤]某列的分布，索引的列的基數(shù)越大，索引的效果越好。例如，對(duì)性別M/F列做索引沒(méi)多大用處。
3. 使用短索引。如果是對(duì)字符串進(jìn)行索引，如果有可能應(yīng)該指定前綴長(zhǎng)度。
4. 利用最左前綴。盡量將使用頻繁且過(guò)濾效果好的字段放“左邊”
5. 不要過(guò)度索引。
6. Innodb默認(rèn)會(huì)按照一定的順序保存數(shù)據(jù)，如果明確定義了主鍵，則按照主鍵順序保存。如果沒(méi)有主鍵，但有唯一索引，就按照唯一索引的順序保存。如果有幾個(gè)列都是唯一的，都可以作為主鍵的時(shí)候，為了提高查詢效率，應(yīng)選擇最常用訪問(wèn)的列作為主鍵。另外，innodb的secondary index都會(huì)保存主鍵的鍵值，所有主鍵要盡可能選擇較短的數(shù)據(jù)類型。可以看出，應(yīng)當(dāng)盡量避免對(duì)主鍵的修改。經(jīng)過(guò)dba的測(cè)試，保證主鍵的遞增可以提高插入性能。
Mysql如何使用索引
1. 對(duì)于創(chuàng)建的多列索引，只要查詢的條件中用到了最左邊的列，索引一般就會(huì)被使用。
2. 對(duì)于使用like的查詢，后面如果是常量并且只有%號(hào)不在第一個(gè)字符，索引才可能被使用。
3. 如果對(duì)大文本進(jìn)行搜索，應(yīng)該使用全文索引，而不是使用like ‘%...%’. 但不幸的是innodb不支持全文索引。
4. 如果列名是索引，使用 index_column is null將使用索引。Oracle是不行的。
5. 如果mysql估計(jì)使用索引比全表掃描更慢，最不會(huì)使用索引。
6. 如果使用memory/head表并且where條件中不使用”=”進(jìn)行索引列，那么不會(huì)用到索引。Head表只有在”=”的時(shí)候才會(huì)使用索引。
7. 用or分割開(kāi)的條件，如果or前的條件中的列有索引，而后面列中沒(méi)有索引，那么涉及到的索引都不會(huì)被用到。
8. 不是多列索引的第一部分不會(huì)走索引。
9. 以%開(kāi)始的like不會(huì)走索引
10. 如果列是字符串，那么一定要在where條件中把字符串常量值用引號(hào)引起來(lái)，否則不能走索引。因?yàn)椋?/span>mysql默認(rèn)把輸入的常量值進(jìn)行轉(zhuǎn)換以后才進(jìn)行檢索。
11. 經(jīng)過(guò)普通運(yùn)算或函數(shù)運(yùn)算后的索引字段不能使用索引
12. 不等于操作不能使用索，<>、not in等
13. Order by 優(yōu)化：某些情況下，mysql可以使用一個(gè)索引滿足order by,而不需要額外的排序。Where條件與order by 使用相同的索引，并且order by的順序和索引順序相同，并且order by的字段都是升序或者都是降序。
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2
DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
但是以下情況不使用索引：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC ；
--order by 的字段混合 ASC 和 DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1 ；
-- 用于查詢行的關(guān)鍵字與 ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2 ；
-- 對(duì)不同的關(guān)鍵字使用 ORDER BY
可以使用explain查看sql的執(zhí)行計(jì)劃。