MySQL下應用Inplace和Online方法創立索引的教程。本站提示廣大學習愛好者：（MySQL下應用Inplace和Online方法創立索引的教程）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL下應用Inplace和Online方法創立索引的教程正文
MySQL各版本，關於add Index的處置方法是分歧的，重要有三種：
（1）Copy Table方法
這是InnoDB最早支撐的創立索引的方法。望文生義，創立索引是經由過程暫時表拷貝的方法完成的。
新建一個帶有新索引的暫時表，將原表數據全體拷貝莅臨時表，然後Rename，完成創立索引的操作。
這個方法創立索引，創立進程中，原表是可讀的。然則會消費一倍的存儲空間。
（2）Inplace方法
這是原生MySQL 5.5，和innodb_plugin中供給的創立索引的方法。所謂Inplace，也就是索引創立在原表上直接停止，不會拷貝暫時表。絕對於Copy Table方法，這是一個提高。
Inplace方法創立索引，創立進程中，原表異樣可讀的，然則弗成寫。
（3）Online方法
這是MySQL 5.6.7中供給的創立索引的方法。不管是Copy Table方法，照樣Inplace方法，創立索引的進程中，原表只能許可讀取，弗成寫。對運用有較年夜的限制，是以MySQL最新版本中，InnoDB支撐了所謂的Online方法創立索引。
InnoDB的Online Add Index，起首是Inplace方法創立索引，無需應用暫時表。在遍歷聚簇索引，搜集記載並拔出到新索引的進程中，原表記載可修正。而修正的記載保留在Row Log中。當聚簇索引遍歷終了，並全體拔出到新索引以後，重放Row Log中的記載修正，使得新索引與聚簇索引記載到達分歧狀況。
與Copy Table方法比擬，Online Add Index采取的是Inplace方法，無需Copy Table，削減了空間開支；與此同時，Online Add Index只要在重放Row Log最初一個Block時鎖表，削減了鎖表的時光。
與Inplace方法比擬，Online Add Index接收了Inplace方法的優勢，卻削減了鎖表的時光。
1.Inplace add Index
測試表
create table t1 (a int primary key, b int)engine=innodb; insert into t1 values (1,1),(2,2),(3,3),(4,4);
Inplace Add Index處置流程
SQL
alter table t1 add index idx_t1_b(b);
處置流程
sql_table.cc::mysql_alter_table();
// 斷定以後操作能否可以停止Inplace完成，弗成停止Inplace Alter的包含：
// 1. Auto Increment字段修正；
// 2. 列重定名；
// 3. 行存儲格局修正；等
mysql_compare_tables() -> ha_innobase::check_if_incompatible_data();
// Inplace創立索引第一階段(重要階段)
handler0alter.cc::add_index();
…
// 創立索引數據字典
row0merge.c::row_merge_create_index();
index = dict_mem_index_create();
// 每一個索引數據字典上，有一個trx_id，記載創立此索引的事務
// 此trx_id有何功效，接著往下看
index->trx_id = trx_id;
// 讀取聚簇索引，結構新索引的項，排序並拔出新索引
row0merge.c::row_merge_build_indexes();
// 讀取聚簇索引，留意：只讀取個中的非刪除項
// 跳過一切刪除項，為何可以這麼做？往下看
row_merge_read_clustered_index();
// 文件排序
row_merge_sort();
// 次序讀取排敘文件中的索引項，逐一拔出新建索引中
row_merge_insert_index_tuples();
// 期待翻開以後表的一切只讀事務提交
sql_base.cc::wait_while_table_is_used();
// 創立索引停止，做最初的清算任務
handler0alter.cc::final_add_index();
// Inplace add Index終了
Inplace Add Index完成剖析
在索引創立完成以後，MySQL Server立刻可使用新建的索引，做查詢。然則，依據以下流程，對我小我來講，有三個疑問點：
索引數據字典上，為什麼須要保護一個trx_id？
trx_id有何感化？
遍歷聚簇索引讀取一切記載時，為什麼可跳過刪除項？
只讀取非刪除項，那末新建索引上沒有版本信息，沒法處置原有事務的快照讀；
MySQL Server層，為什麼須要期待翻開表的只讀事務提交？
期待以後表上的只讀事務，可以包管這些事務不會應用到新建索引
依據剖析，期待翻開表的只讀事務停止較好懂得。由於新索引上沒有版本信息，若這些事務應用新的索引，將會讀不到准確的版本記載。
那末InnoDB是若何處置其他那些在創立索引之前曾經開端，但卻一向未提交的老事務呢？這些事務，因為後期為並未讀取以後表，是以不會被期待停止。這些事務在RR隔離級別下，會讀取不到准確的版本記載，由於應用的索引上並沒有版本信息。
固然，InnoDB異樣斟酌到了此成績，並采取了一種比擬簡介的處置計劃。在索引上保護一個trx_id，標識創立此索引的事務ID。如有一個比這個事務更老的事務，盤算應用新建的索引停止快照讀，那末直接報錯。
斟酌以下的並發處置流程(事務隔離級別為RR)：
session 1: session 2:
// 此時創立Global ReadView
select * from t2;
delete from t1 where b = 1;
// idx_t1_b索引上，沒有b = 1的項
alter table t1 add index idx_t1_b(b);
// 因為ReadView在delete之前獲得
// 是以b = 1這一項應當被讀取到
select * from t1 where b = 1;
當session 1履行最初一條select時，MySQL Optimizer會選擇idx_t1_b索引停止查詢，然則索引上並沒有b = 1的項，應用此索引會招致查詢失足。那末，InnoDB是若何處置這個情形的呢？
處置流程：
…
ha_innobase::index_init();
change_active_index();
// 斷定session 1事務的ReadView能否可以看到session 2創立索引的事務
// 此處，session 2事務固然弗成見，那末prebuilt->index_usable = false
prebuilt->index_usable = row_merge_is_index_usable(readview, index->trx_id);
…
ha_innobase::index_read();
// 斷定index_usable屬性，此時為false，前往下層表界說修正，查詢掉敗
if (!prebuilt->index_usable)
return HA_ERR_TABLE_DEF_CHANGED;
MySQL Server收到InnoDB前往的毛病以後，會將毛病報給用戶，用戶會收到以下毛病：
mysql> select * from t1 where b = 1;
ERROR 1412 (HY000): Table definition has changed, please retry transaction
2.Online add Index
測試表
create table t1 (a int primary key, b int)engine=innodb; insert into t1 values (1,1),(2,2),(3,3),(4,4);
Online Add Index處置流程
SQL
alter table t1 add index idx_t1_b(b);
處置流程
sql_table.cc::mysql_alter_table();
// 1. 斷定以後DDL操作能否可以Inplace停止
check_if_supported_inplace_alter();
…
// 2. 開端停止Online創立的後期預備任務
prepare_inplace_alter_table();
…
// 修正表的數據字典信息
prepare_inplace_alter_table_dict();
…
// 期待InnoDB一切的後台線程，停滯操作此表
dict_stats_wait_bg_to_stop_using_tables();
…
// Online Add Index差別與Inplace Add Index的症結
// 在Online操作時，原表同時可以讀寫，是以須要
// 將此進程中的修正操作記載到row log當中
row0log.cc::row_log_allocate();
row_log_t* log = (row_log_t*)&buf[2 * srv_sort_buf_size];
// 標識以後索引狀況為Online創立，那末此索引上的
// DML操作會被寫入Row Log，而不在索引長進行更新
dict_index_set_online_status(index, ONLINE_INDEX_CREATION);
…
// 3. 開端停止真實的Online Add Index的操作(最主要的流程)
inplace_alter_table();
// 此函數的操作，前部門與Inplace Add Index根本分歧
// 讀取聚簇索引、排序、並拔出到新建索引中
// 最年夜的分歧在於，當拔出完成以後，Online Add Index
// 還須要將row log中的記載變更，更新到新建索引中
row0merge.cc::row_merge_build_index();
…
// 在聚簇索引讀取、排序、拔出新建索引的操作停止以後
// 進入Online與Inplace真實的分歧的地方，也是Online操作
// 的精華部門——將這個進程中發生的Row Log重用
row0log.cc::row_log_apply();
// 臨時將新建索引全部索引樹完整鎖住
// 留意：只是臨時性鎖住，其實不是在全部重用Row Log的
// 進程中一向加鎖(避免加鎖時光太長的優化，若何優化？)
rw_lock_x_lock(dict_index_get_lock(new_index));
…
// InnoDB Online操作最主要的處置流程
// 將Online Copy Table中，記載的Row Log重放到新建索引上
// 重放Row Log的算法以下：
// 1. Row Log中記載的是Online創立索引時代，原表上的DML操作
// 這些操作包含：ROW_OP_INSERT；ROW_OP_DELETE_MARK; …
// 2. Row Log以Block的方法存儲，若DML較多，那末Row Logs能夠
// 會占用多個Blocks。row_log_t構造中包括兩個指針：head與tail
// head指針用於讀取Row Log，tail指針用於追加寫新的Row Log；
// 3.在重用Row Log時，算法遵守一個准繩：盡可能削減索引樹加鎖
// 的時光(索引樹加X鎖，也意味著表上制止了新的DML操作)
// 索引樹須要加鎖的場景：
// (一) 在重用Row Log逾越新的Block時，須要長久加鎖；
// (二) 若運用的Row Log Block是最初一個Block，那末一向加鎖
// 運用最初一個Block，因為制止了新的DML操作，是以此
// Block運用終了，新索引記載與聚簇索引到達分歧狀況，
// 重用階段停止；
// (三) 在運用中央Row Log Block上的row log時，無需加鎖，新的
// DML操作仍然可以停止，發生的row log記載到最初一個
// Row Log Block之上；
// 4. 假如是創立Unique索引，那末在運用Row Log時，能夠會湧現
// 違背獨一性束縛的情形，這些情形會被記載到
// row_merge_dup_t構造當中
row_log_apply_ops(trx, index, &dup);
row_log_apply_op();
row_log_apply_op_low();
…
// 將New Index的Online row log設置為NULL，
// 標識New Index的數據曾經與聚簇索引完整分歧
// 在此以後，新的DML操作，無需記載Row Log
dict_index_set_online_status();
index->online_status = ONLINE_INDEX_COMPLETE;
index->online_log = NULL;
rw_lock_x_unlock(dict_index_get_block(new_index));
row_log_free();
…
// 4. Online Add Index的最初步調，做一些後續掃尾任務
commit_inplace_alter_table();
…
Online Add Index完成剖析
在看完後面剖析的InnoDB 5.6.7-RC版本中完成的根本處置流程以後，小我仍然遺留了幾個成績，重要的成績有：
Online Add Index能否支撐Unique索引？
確實的謎底是：支撐(不外存在Bug，前面剖析)。InnoDB支撐Online創立Unique索引。
既然支撐，就會見臨Check Duplicate Key的成績。Row Log中假如存在與索引中雷同的鍵值怎樣處置？怎樣檢測能否存在雷同鍵值？
InnoDB處理此成績的計劃也比擬簡介易懂。其保護了一個row_merge_dup_t的數據構造，存儲了在Row log重放進程中碰到的違背獨一性抵觸的Row Log。運用完Row Log以後，內部斷定能否存在Unique抵觸(有若干Unique抵觸，均會記載)，Online創立Unique索引掉敗。
Row Log是甚麼樣的構造，若何組織的？
在Online Add Index進程中，並發DML發生的修正，被記載在Row Log中。起首，Row Log不是InnoDB的Redo Log，而是每一個正在被Online創立的索引的獨有構造。
Online創立索引，遵守的是先創立索引數據字典，後填湊數據的方法。是以，當索引數據字典創立勝利以後，新的DML操作便可以讀取此索引，測驗考試停止更新。然則，因為索引構造上的status狀況為ONLINE_INDEX_CREATION，是以這些更新不克不及直策應用到新索引上，而是放入Row Log當中，期待被重放到索引之上。
Row Log中，以Block的方法治理DML操作內容的寄存。一個Block的年夜小為由參數innodb_sort_buffer_size掌握，默許年夜小為1M (1048576)。初始化階段，Row Log請求兩個如許的Block。
在Row Log重放的進程中，究竟須要多久的鎖表時光？
後面的流程剖析中，也提到了鎖表的成績(外部為鎖新建索引樹的操作完成)。
在重放Row log時，有兩個情形下，須要鎖表：
情形一：在應用完一個Block，跳轉到下一個Block時，須要長久鎖表，斷定下一個Block能否為Row Log的最初一個Block。若不是最初一個，跳轉終了後，釋放鎖；應用Block內的row log不加鎖，用戶DML操作仍然可以停止。
情形二：在應用最初一個Block時，會一向持有鎖。此時不許可新的DML操作。包管最初一個Block重放完成以後，新索引與聚簇索引記載到達分歧狀況。
綜上剖析兩個鎖臉色況，情形二會連續鎖表，然則因為也只是最初一個Block，是以鎖表時光也較短，只會長久的影響用戶操作，在低峰期，這個影響是可以接收的。
3. Online Add Index能否也存在與Inplace方法一樣的限制？
因為Online Add Index同時也是Inplace方法的，是以Online方法也存在著Inplace方法所存在的成績：新索引上缺少版本信息，是以沒法為老事務供給快照讀。
不只如斯，絕對於Inplace方法，Online方法的束縛愈甚一籌，不只一切小於創立此Index的事務弗成應用新索引，同時，一切在新索引創立進程中開端的事務，也不克不及應用新索引。
這個加強的限制，在rowmerge.cc::row_merge_read_clustered_index()函數中調劑，在聚簇索引遍歷完成以後，將新索引的trx_id，賦值為Online Row Log中最年夜的事務ID。待索引創立完成以後，一切小於此事務ID的事務，均弗成應用新索引。
在遍歷聚簇索引讀取數據時，讀取的是記載的最新版本，那末此記載能否在Row Log也會存在？InnoDB若何處置這類情形？
起首，謎底是確定的。遍歷聚簇索引讀取記載最新版本時，這些記載有能夠是新事務修正/拔出的。這些記載在遍歷階段，曾經被運用到新索引上，於此同時，這些記載的操作，也被記載到Row Log當中，湧現了一筆記錄在新索引上存在，在Row Log中也存在的情形。
固然，InnoDB曾經斟酌到了這個成績。在重放Row Log的進程中，關於Row Log中的每筆記錄，起首會斷定其在新索引中能否曾經存在(row0log.c::row_log_apply_op_low())，若存在，則以後Row Log可以跳過(或許是將操作類型轉換)。
例如：Row Log中記載的是一個INSERT操作，若此INSERT記載在新索引中曾經存在，那末Row Log中的記載，可以直接拋棄(若存在項與INSERT項完整分歧)；或許是將INSERT轉換為UPDATE操作(Row Log記載與新索引中的記載，部門索引列有分歧)；
Online Add Index能否存在Bug？
謎底異樣是確定的，存在Bug。
個中有一個Bug，重現計劃以下：
create table t1 (a int primary key, b int, c char(250))engine=innodb;
insert into t1(b,c) values (1,'aaaaaaa');
// 包管數據量夠多
insert into t1(b,c) select b,c from t1;
insert into t1(b,c) select b,c from t1;
insert into t1(b,c) select b,c from t1;
…
// max(a) = 196591
select max(a) from t1;
// b中異樣沒有雷同項
update t1 set b = a;
session 1 session 2
alter table t1 add unique index idx_t1_b(b);
insert into t1(b,c) values (196592,'b');
// 此update，會發生b=196589的反復項
update t1 set b=196589 where a=196582;
delete from t1 where a = 262127;
在以上的測試中，起首為表預備足夠的數據，目標是session 1做Online Add Index的讀取聚簇索引階段，session 2新的記載也可以或許被讀到。
在session 1的Online Add Index完成以後(勝利)，履行以下兩個敕令，成果以下：
mysql> show create table t1;
+——-+————————————————– | Table | Create Table +——-+————————————————– | t1 | CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) DEFAULT NULL, `c` char(250) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `idx_t1_b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=262129 DEFAULT CHARSET=gbk | +——-+————————————————– mysql> select * from t1 where a in (196582,196589); +——–+——–+———+ | a | b | c | +——–+——–+———+ | 196582 | 196589 | aaaaaaa | | 196589 | 196589 | aaaaaaa | +——–+——–+———+ 2 rows in set (0.04 sec)
可以看到，b上曾經有了一個Unique索引，然則表中卻存在兩個雷同的取值為196589的值。
此Bug，是處置Row Log的重放進程，未詳實斟酌一切情形招致的。是以，在MySQL 5.6版本穩固之前，慎用！
Online Add Index可自創的地方
在MySQL 5.6.7中進修到兩個文件操作函數：一是posix_fadvise()函數，指定POSIX_FADV_DONTNEED參數，可做到讀寫不Cache：Improving Linux performance by preserving Buffer Cache State unbuffered I/O in Linux；二是fallocate()函數，指定FALLOC_FL_PUNCH_HOLE參數，可做到讀時清空：Linux Programmer's Manual FALLOCATE(2) 有相似需求的同伙，可試用。
posix_fadvise函數+POSIX_FADV_DONTNEED參數，重要功效就是拋棄文件在Cache中的clean blocks。是以，若用戶不願望一個文件占用過量的文件體系Cache，可以按期的挪用fdatasync()，然後接著posix_fadvise(POSIX_FADV_DONTNEED)，清空文件在Cache中的clean blocks，不錯的功效！
