Innodb存儲及索引

1、I INNODBNNODB存儲與索引存儲與索引馮永哲馮永哲 DBADBA2012-08-302012-08-30InnodbInnodb存儲存儲InnodbInnodb表的限制表的限制InnodbInnodb索引索引復(fù)合索引復(fù)合索引索引操作索引操作索引設(shè)計原則索引設(shè)計原則MysqlMysql如何使用索引如何使用索引I INNODBNNODB存儲存儲表空間是邏輯存放所有數(shù)據(jù)的地方，默認情況下會共享一個表空間ibdata1，但如果把innodb_file_per_table=ON后每張表可以單獨放到一個表空間內(nèi)，但還是有很多數(shù)據(jù)保存在共享的表ibdata1中，如undo信息等。表空間由各種段(se

2、gment)組成，常見的段有數(shù)據(jù)段、索引段等。Innodb是索引組織的，數(shù)據(jù)段就是clustered index的葉結(jié)點。需要注意的是，不是每個對象都有段。區(qū)(extend)是由64個連續(xù)的頁組成，每個頁（page）固定為16KB，所以每個區(qū)總共為1M。頁是innodb最小的磁盤管理單位。I INNODBNNODB存儲存儲Innodb是按行進行存放的，每個區(qū)最少可以保存2條記錄，否則就成鏈式結(jié)構(gòu)了。每行數(shù)據(jù)除了自定義列以外，還會增加事務(wù)id和回滾指針列。如果沒有定義primary key也沒有not null的unique,則會增加6字節(jié)的RowId列作為主鍵。I INNODBNNODB存儲存

3、儲I INNODBNNODB表的限制表的限制一個表不能包含超過1000列。內(nèi)部最大鍵長度是3500字節(jié)，但MySQL自己限制這個到1024字節(jié)。除了VARCHAR, BLOB和TEXT列，最大行長度稍微小于數(shù)據(jù)庫頁的一半。即，最大行長度大約8000字節(jié)。LONGBLOB和LONGTEXT列必須小于4GB,總的行長度，頁包括BLOB和TEXT列，必須小于4GB。InnoDB在行中存儲VARCHAR，BLOB或TEXT列的前768字節(jié)，余下的存儲的分散的頁中。雖然InnoDB內(nèi)部地支持行尺寸大于65535，你不能定義一個包含VARCHAR列的，合并尺寸大于65535的行I INNODBNNODB表

4、的限制表的限制在一些更老的操作系統(tǒng)上，數(shù)據(jù)文件必須小于2GB。InnoDB日志文件的合并尺寸必須小于4GB。最小的表空間尺寸是10MB。最大的表空間尺寸是4,000,000,000個數(shù)據(jù)庫頁（64TB）。這也是一個表的最大尺寸。InnoDB表不支持FULLTEXT索引 I INNODBNNODB索引索引默認情況下Memory使用存儲hash索引，但也支持b+tree索引。Hash索引只用于=或者的等式比較，不能用來加速order by操作，只能通過關(guān)鍵字來搜索一行。innodb只支持b+樹索引，進一步分為clustered index與secondary index。在一次查詢中，只能使用一個

5、索引。Innodb是索引組織表，clustered index的葉結(jié)點保存著整行的數(shù)據(jù)。如果定義了primary key，則clustered index就是primary key的索引；如果沒有定義primary key ，mysql會選中第一個僅有not null列的unique索引作為主鍵，并把此索引當作clustered index使用；如果沒找到這樣的列，innodb會創(chuàng)建一個6字節(jié)的RowId作為主鍵。所以每張表有且只有一個clustered index。I INNODBNNODB索引索引I INNODBNNODB索引索引Secondary index的葉結(jié)點不包括行的全部數(shù)據(jù)，包

6、含鍵值以外還包括一個bookmark，可以告訴innodb到什么地方可以找到相對應(yīng)的完整行數(shù)據(jù)，還保存了主鍵的健值。Secondary index包含主鍵，但不包含完整的行數(shù)據(jù)，所以innodb總是會先從secondary index的葉節(jié)點判斷是否能得到所需的數(shù)據(jù)。如Create table t(a int, b varchar(20), primary key(a), key(b);Explain select * from t;會發(fā)現(xiàn)mysql選擇了索引b，而不是a.I INNODBNNODB索引索引復(fù)合索引復(fù)合索引復(fù)合索引是在多列（=2）上建立的索引，又叫多列索引或聯(lián)合索引。Innod

7、b中的復(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)建多列索引時，要根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁且過濾效果好的的一列放在最左邊。索引操作索引操作可以通過DML語句操作innodb

8、索引。因為innodb是索引組織的表，對索引的操作會造成鎖表，先生成一張臨時表，將數(shù)據(jù)從原始表中寫到臨時表，再將原始表刪除，最后將臨時表表名改為原始表表名！因增加、刪除、修改字段會對主索引產(chǎn)生影響，所以也會鎖表。對secondary index從Innodb plugin開始，支持快速索引創(chuàng)建的方法，在創(chuàng)建的過程中不需要重建表，所以速度會很快，同時引擎會在表上加S鎖，在創(chuàng)建過程中只能進行讀操作。索引設(shè)計原則索引設(shè)計原則1.搜索的索引列，不一定是所要選擇的列。也就是說，最適合索引的列是出現(xiàn)在where子句中的列，或者連接子句中指定的列，而不是出現(xiàn)在select關(guān)鍵字后的選擇列表中的列。2.使用唯

9、一索引?？紤]某列的分布，索引的列的基數(shù)越大，索引的效果越好。例如，對性別M/F列做索引沒多大用處。3.使用短索引。如果是對字符串進行索引，如果有可能應(yīng)該指定前綴長度。4.利用最左前綴，盡量將使用頻繁且過濾效果好的字段放“左邊”5.不要過度索引。6.Innodb默認會按照一定的順序保存數(shù)據(jù)，如果明確定義了主鍵，則按照主鍵順序保存。如果沒有主鍵，但有唯一索引，就按照唯一索引的順序保存。如果有幾個列都是唯一的，都可以作為主鍵的時候，為了提高查詢效率，應(yīng)選擇最常用訪問的列作為主鍵。另外，innodb的secondary index都會保存主鍵的鍵值，所有主鍵要盡可能選擇較短的數(shù)據(jù)類型?？梢钥闯觯瑧?yīng)當盡

10、量避免對主鍵的修改。經(jīng)過dba的測試，保證主鍵的遞增可以提高插入性能。MMYSQLYSQL如何使用索引如何使用索引1.對于創(chuàng)建的多列索引，只要查詢的條件中用到了最左邊的列，索引一般就會被使用。2.對于使用like的查詢，后面如果是常量并且只有%號不在第一個字符，索引才可能被使用。3.如果列名是索引，使用index_column is null將使用索引。4.如果mysql估計使用索引比全表掃描更慢，最不會使用索引。5.如果使用memory/head表并且where條件中不使用”=”進行索引列，那么不會用到索引。Head表只有在”=”的時候才會使用索引。MMYSQLYSQL如何使用索引如何使用索

11、引6.用or分割開的條件，如果or前的條件中的列有索引，而后面列中沒有索引，那么涉及到的索引都不會被用到。7.不是多列索引的第一部分不會走索引。8.如果列是字符串，那么一定要在where條件中把字符串常量值用引號引起來，否則不能走索引。因為，mysql默認把輸入的常量值進行轉(zhuǎn)換以后才進行檢索。9.經(jīng)過普通運算或函數(shù)運算后的索引字段不能使用索引。10.不等于操作不能使用索引，、not in等MMYSQLYSQL如何使用索引如何使用索引11.Order by優(yōu)化：某些情況下，mysql可以使用一個索引滿足order by,而不需要額外的排序。Where條件與order by使用相同的索引，并且or

12、der by的順序和索引順序相同，并且order by的字段都是升序或者都是降序。MMYSQLYSQL如何使用索引如何使用索引MMYSQLYSQL如何使用索引如何使用索引MMYSQLYSQL如何使用索引如何使用索引SELECT * FROM a2 ORDER BY a2,a3 ;SELECT * FROM a2 WHERE a2=1 ORDER BY a2 DESC, a3 DESC;SELECT * FROM a2 ORDER BY a2 DESC, a3 DESC;不當?shù)氖褂肧ELECT * FROM a2 ORDER BY a2 DESC, a3 ASC；-order by的字段混合ASC和D