程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL下使用Inplace和Online方式創建索引的教程,mysqlinplace

MySQL下使用Inplace和Online方式創建索引的教程,mysqlinplace

編輯:MySQL綜合教程

MySQL下使用Inplace和Online方式創建索引的教程,mysqlinplace


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,不錯的功能!

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved