innodb索引概念 總結記錄下innodb的索引概念，以備查看 innodb索引分類： 聚簇索引(clustered index) 1) 有主鍵時，根據主鍵創建聚簇索引 2) 沒有主鍵時，會用一個唯一且不為空的索引列做為主鍵，成為此表的聚簇索引 3) 如果以上兩個都不滿足那innodb自己創建一個虛擬的聚集索引 輔助索引(secondary index) 非聚簇索引都是輔助索引，像復合索引、前綴索引、唯一索引 myisam索引：因為myisam的索引和數據是分開存儲存儲的，myisam通過key_buffer把索引先緩存到內存中，當需要訪問數據時（通過索引訪問數據），在內存中直接搜索 索引，然後通過索引找到磁盤相應數據，這也就是為什麼索引不在key buffer命中時，速度慢的原因 innodb索引：innodb的數據和索引放在一起，當找到索引也就找到了數據 自適應哈希索引：innodb會監控表上的索引使用情況，如果觀察到建立哈希索引可以帶來速度的提升，那就建立哈希索引，自 適應哈希索引通過緩沖池的B+樹構造而來， 因此建立的速度很快，不需要將整個表都建哈希索引，InnoDB 存儲引擎會自動根據訪問的頻率和模式來為某些頁建立哈希索引。自適應哈希索引不需要 存儲磁盤的，當停庫內容會丟失，數據庫起來會自己創建，慢慢維護索引。 聚簇索引： MySQL InnoDB一定會建立聚簇索引，把實際數據行和相關的鍵值保存在一塊，這也決定了一個表只能有一個聚簇索引，即MySQL不會一次把數據行保存在二個地方。 1) InnoDB通常根據主鍵值(primary key)進行聚簇 2) 如果沒有創建主鍵，則會用一個唯一且不為空的索引列做為主鍵，成為此表的聚簇索引 3) 上面二個條件都不滿足，InnoDB會自己創建一個虛擬的聚集索引 優點： 聚簇索引的優點,就是提高數據訪問性能。聚簇索引把索引和數據都保存到同一棵B+樹數據結構中，並且同時將索引列與相關數據行保存在一起。這意味著，當你訪問同一數據頁不同行記錄時，已經把頁加載到了Buffer中，再次訪問的時候，會在內存中完成訪問，不必訪問磁盤。不同於MyISAM引擎，它將索引和數據沒有放在一塊，放在不同的物理文件中，索引文件是緩存在key_buffer中，索引對應的是磁盤位置，不得不通過磁盤位置訪問磁盤數據。 缺點： 1) 維護索引很昂貴，特別是插入新行或者主鍵被更新導至要分頁(page split)的時候。建議在大量插入新行後，選在負載較低的時間段，通過OPTIMIZE TABLE優化表，因為必須被移動的行數據可能造成碎片。使用獨享表空間可以弱化碎片 2) 表因為使用UUId作為主鍵，使數據存儲稀疏，這就會出現聚簇索引有可能有比全表掃面更慢，所以建議使用int的auto_increment作為主鍵 3) 如果主鍵比較大的話，那輔助索引將會變的更大，因為輔助索引的葉子存儲的是主鍵值；過長的主鍵值，會導致非葉子節點占用占用更多的物理空間 輔助索引 在聚簇索引之上創建的索引稱之為輔助索引，輔助索引訪問數據總是需要二次查找。輔助索引葉子節點存儲的不再是行的物理位置，而是主鍵值。通過輔助索引首先找到的是主鍵值，再通過主鍵值找到數據行的數據葉，再通過數據葉中的Page Directory找到數據行。 復合索引 由多列創建的索引稱為符合索引，在符合索引中的前導列必須出現在where條件中，索引才會被使用 ALTER TABLE `test`.`users` ADD INDEX `idx_users_id_name` (`name`(10) ASC, `id` ASC) ; 前綴索引 當索引的字符串列很大時，創建的索引也就變得很大，為了減小索引體積，提高索引的掃描速度，就用索引的前部分字串索引，這樣索引占用的空間就會大大減少，並且索引的選擇性也不會降低很多。而且是對BLOB和TEXT列進行索引，或者非常長的VARCHAR列，就必須使用前綴索引，因為MySQL不允許索引它們的全部長度。 使用： 列的前綴的長度選擇很重要，又要節約索引空間，又要保證前綴索引的選擇性要和索引全長度選擇性接近。 唯一索引 唯一索引比較好理解，就是索引值必須唯一，這樣的索引選擇性是最好的 主鍵索引 主鍵索引就是唯一索引，不過主鍵索引是在創建表時就創建了，唯一索引可以隨時創建。 說明 主鍵和唯一索引區別 1) 主鍵是主鍵約束+唯一索引 2) 主鍵一定包含一個唯一索引，但唯一索引不是主鍵 3) 唯一索引列允許空值，但主鍵列不允許空值 4) 一個表只能有一個主鍵，但可以有多個唯一索引 索引掃描方式： 緊湊索引掃描(dense index)： 在最初，為了定位數據需要做權表掃描，為了提高掃描速度，把索引鍵值單獨放在獨立的數據的數據塊裡，並且每個鍵值都有個指向原數據塊的指針，因為索引比較小，掃描索引的速度就比掃描全表快，這種需要掃描所有鍵值的方式就稱為緊湊索引掃描 松散索引掃描(sparse index)： 為了提高緊湊索引掃描效率，通過把索引排序和查找算法（B+trre），發現只需要和每個數據塊的第一行鍵值匹配，就可以判斷下一個數據塊的位置或方向，因此有效數據就是每個數據塊的第一行數據，如果把每個數據塊的第一行數據創建索引，這樣在這個新創建的索引上折半查找，數據定位速度將更快。這種索引掃描方式就稱為松散索引掃描。 覆蓋索引掃描(covering index)： 包含所有滿足查詢需要的數據的索引稱為覆蓋索引，即利用索引返回select列表中的字段，而不必根據索引再次讀取數據文件 索引相關常用命令： 1) 創建主鍵 CREATE TABLE `pk_tab2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a1` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2) 創建唯一索引 create unique index indexname on tablename(columnname); alter table tablename add unique index indexname(columnname); 3) 創建單列一般索引 create index indexname on tablename(columnname); alter table tablename add index indexname(columnname); 4) 創建單列前綴索引 create index indexname on tablename(columnname(10)); //單列的前10個字符創建前綴索引 alter table tablename add index indexname(columnname(10)); //單列的前10個字符創建前綴索引 5) 創建復合索引 create index indexname on tablename(columnname1，columnname2); //多列的復合索引 create index indexname on tablename(columnname1，columnname2(10)); //多列的包含前綴的復合索引 alter table tablename add index indexname(columnname1，columnname2); //多列的復合索引 alter table tablename add index indexname(columnname1，columnname(10)); //多列的包含前綴的復合索引 6) 刪除索引 drop index indexname on tablename;; alter table tablename drop index indexname; 7) 查看索引 show index from tablename; show create table pk_tab2; --------end--------
