happyenjoylife

Innodb存儲(chǔ)

表空間是邏輯存放所有數(shù)據(jù)的地方，默認(rèn)情況下會(huì)共享一個(gè)表空間——ibdata1，但如果把innodb_file_per_table=ON后每張表可以單獨(dú)放到一個(gè)表空間內(nèi)，但還是有很多數(shù)據(jù)保存在共享的表ibdata1中，如undo信息等。

表空間由各種段(segment)組成，常見的段有數(shù)據(jù)段、索引段等。Innodb是索引組織的，數(shù)據(jù)段就是clustered index的葉結(jié)點(diǎn)。需要注意的是，不是每個(gè)對(duì)象都有段。

區(qū)(extend)是由64個(gè)連續(xù)的頁組成，每個(gè)頁（page）固定為16KB，所以每個(gè)區(qū)總共為1M。頁是innodb最小的磁盤管理單位。

Innodb是按行進(jìn)行存放的，每個(gè)區(qū)最少可以保存2條記錄，否則就成鏈?zhǔn)浇Y(jié)構(gòu)了。每行數(shù)據(jù)除了自定義列以外，還會(huì)增加事務(wù)id和回滾指針列。如果沒有定義primary key也沒有not null的unique,則會(huì)增加6字節(jié)的RowId列作為主鍵。
        
            圖片來自：http://www.cnblogs.com/chjw8016/archive/2011/03/08/1976891.html

Innodb表的限制

        一個(gè)表不能包含超過1000列。

  內(nèi)部最大鍵長(zhǎng)度是3500字節(jié)，但MySQL自己限制這個(gè)到1024字節(jié)。

  除了VARCHAR, BLOB和TEXT列，最大行長(zhǎng)度稍微小于數(shù)據(jù)庫頁的一半。即，最大行長(zhǎng)度大約8000字節(jié)。LONGBLOB和LONGTEXT列必須小于4GB, 總的行長(zhǎng)度，頁包括BLOB和TEXT列，必須小于4GB。InnoDB在行中存儲(chǔ)VARCHAR，BLOB或TEXT列的前768字節(jié)，余下的存儲(chǔ)的分散的頁中。

雖然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ù)庫頁（64TB）。這也是一個(gè)表的最大尺寸。

 InnoDB表不支持FULLTEXT索引

Innodb索引

默認(rèn)情況下Memory使用存儲(chǔ)hash索引，但也支持b+tree索引。Hash索引只用于=或者<=>的等式比較，不能用來加速order by操作，只能通過關(guān)鍵字來搜索一行。innodb只支持b+樹索引，進(jìn)一步分為clustered index 與　secondary index。在一次查詢中，只能使用一個(gè)索引。

Innodb是索引組織表，clustered index的葉結(jié)點(diǎn)保存著整行的數(shù)據(jù)。如果，定義了primary key，則clustered index就是primary key的索引；如果沒有定義primary key mysql會(huì)選中第一個(gè)僅有not null列的unique索引作為主鍵，并把此索引當(dāng)作clustered index使用；如果沒找到這樣的列，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子句中使用最頻繁且過濾效果好的的一列放在最左邊。

索引操作

         可以通過DML語句操作innodb索引。因?yàn)?/span>innodb是索引組織的表，對(duì)索引的操作會(huì)造成鎖表，先生成一張臨時(shí)表，將數(shù)據(jù)從原始表中寫到臨時(shí)表，再將原始表刪除，最后將臨時(shí)表表名改為原始表表名！因增加、刪除、修改字段會(huì)對(duì)主索引產(chǎn)生影響，所以也會(huì)鎖表。對(duì)secondary index從Innodb plugin開始，支持快速索引創(chuàng)建的方法，在創(chuàng)建的過程中不需要重建表，所以速度會(huì)很快，同時(shí)引擎會(huì)在表上加S鎖，在創(chuàng)建過程中只能進(jìn)行讀操作。

索引設(shè)計(jì)原則

1.       搜索的索引列，不一定是所要選擇的列。也就是說，最適合索引的列是出現(xiàn)在where子句中的列，或者連接子句中指定的列，而不是出現(xiàn)在select關(guān)鍵字后的選擇列表中的列。

2.       使用唯一索引。考慮某列的分布，索引的列的基數(shù)越大，索引的效果越好。例如，對(duì)性別M/F列做索引沒多大用處。

3.       使用短索引。如果是對(duì)字符串進(jìn)行索引，如果有可能應(yīng)該指定前綴長(zhǎng)度。

4.       利用最左前綴。盡量將使用頻繁且過濾效果好的字段放“左邊”

5.       不要過度索引。

6.       Innodb默認(rèn)會(huì)按照一定的順序保存數(shù)據(jù)，如果明確定義了主鍵，則按照主鍵順序保存。如果沒有主鍵，但有唯一索引，就按照唯一索引的順序保存。如果有幾個(gè)列都是唯一的，都可以作為主鍵的時(shí)候，為了提高查詢效率，應(yīng)選擇最常用訪問的列作為主鍵。另外，innodb的secondary index都會(huì)保存主鍵的鍵值，所有主鍵要盡可能選擇較短的數(shù)據(jù)類型。可以看出，應(yīng)當(dāng)盡量避免對(duì)主鍵的修改。經(jīng)過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分割開的條件，如果or前的條件中的列有索引，而后面列中沒有索引，那么涉及到的索引都不會(huì)被用到。

8.       不是多列索引的第一部分不會(huì)走索引。

9.       以%開始的like不會(huì)走索引

10.   如果列是字符串，那么一定要在where條件中把字符串常量值用引號(hào)引起來，否則不能走索引。因?yàn)椋?/span>mysql默認(rèn)把輸入的常量值進(jìn)行轉(zhuǎn)換以后才進(jìn)行檢索。

11.   經(jīng)過普通運(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ì)劃。