MySQL alter 新增列對dml影響
5.7版本很多ddl都是線上的了，允許並行的dml，允許不代表線上不會有問題，下面看下alter新增欄位過程中的加鎖情況，主要是mdl部分的。
在open_table部分會獲取兩個mdl鎖
MDL_INTENTION_EXCLUSIVE 以及MDL_SHARED_UPGRADABLE，在執行mysql_inplace_alter_table的過程中會進行mdl鎖升級，升級shared 鎖成MDL_EXCLUSIVE 堆疊如下：
mysqld!MDL_context::upgrade_shared_lock(MDL_ticket*, enum_mdl_type, unsigned long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/mdl.cc:3937) mysqld!mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:7458) mysqld!mysql_alter_table(THD*, char const*, char const*, st_ha_create_information*, TABLE_LIST*, Alter_info*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:9807) mysqld!Sql_cmd_alter_table::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_alter.cc:333) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:4845) mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584) mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491) mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032) mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313) mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197) libsystem_pthread.dylib!_pthread_start (Unknown Source:0) libsystem_pthread.dylib!thread_start (Unknown Source:0) 在prepare完成後，會將MDL_EXCLUSIVE降低成MDL_SHARED_UPGRADABLE共享鎖，堆疊如下 mysqld!MDL_ticket::downgrade_lock(enum_mdl_type) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/mdl.cc:4544) mysqld!mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:7583) mysqld!mysql_alter_table(THD*, char const*, char const*, st_ha_create_information*, TABLE_LIST*, Alter_info*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:9807) mysqld!Sql_cmd_alter_table::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_alter.cc:333) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:4845) mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584) mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491) mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032) mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313) mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197) libsystem_pthread.dylib!_pthread_start (Unknown Source:0) libsystem_pthread.dylib!thread_start (Unknown Source:0) 使用inplace方式的alter，進入inplace_alter_table，在執行完後，會再次升級成為exclusive ,堆疊如下： mysqld!wait_while_table_is_used(THD*, TABLE*, ha_extra_function) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_base.cc:2598) mysqld!mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:7595) mysqld!mysql_alter_table(THD*, char const*, char const*, st_ha_create_information*, TABLE_LIST*, Alter_info*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:9807) mysqld!Sql_cmd_alter_table::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_alter.cc:333) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:4845) mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584) mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491) mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032) mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313) mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197) libsystem_pthread.dylib!_pthread_start (Unknown Source:0) libsystem_pthread.dylib!thread_start (Unknown Source:0) 然後提交，下面看下dml mdl鎖的獲取，堆疊如下，可以看到是獲取了MDL_SHARED_WRITE mysqld!open_table(THD*, TABLE_LIST*, Open_table_context*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_base.cc:3672) mysqld!open_and_process_table(THD*, LEX*, TABLE_LIST*, unsigned int*, unsigned int, Prelocking_strategy*, bool, Open_table_context*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_base.cc:5226) mysqld!open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_base.cc:5849) mysqld!open_tables_for_query(THD*, TABLE_LIST*, unsigned int) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_base.cc:6626) mysqld!Sql_cmd_update::try_single_table_update(THD*, bool*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:2890) mysqld!Sql_cmd_update::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:3037) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:3616) mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584) mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491) mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032) mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313) mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197) libsystem_pthread.dylib!_pthread_start (Unknown Source:0) libsystem_pthread.dylib!thread_start (Unknown Source:0) select執行過程中會獲取MDL_SHARED_READ，從下面的相容矩陣來看，在alter獲取x鎖的期間，是會阻塞dml的，如果有慢 sql在執行，那麼ddl會被阻塞，後續該表的操作也會阻塞，所以再次的，慢sql ,長事務是萬惡之首。 在最後的提交階段，執行row_merge_lock_table會對錶加x鎖，row_mysql_lock_data_dictionary 會對資料字典加鎖 在後面會有row_merge_drop_table刪除表上的鎖，堆疊如下： mysqld!lock_remove_all_on_table(dict_table_t*, unsigned long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/lock/lock0lock.cc:4770) mysqld!row_drop_table_for_mysql(char const*, trx_t*, bool, bool, dict_table_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0mysql.cc:4517) mysqld!row_merge_drop_table(trx_t*, dict_table_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0merge.cc:4258) mysqld!ha_innobase::commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/handler0alter.cc:8931) mysqld!handler::ha_commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:4863) mysqld!mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, MDL_request*, Alter_table_ctx*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:7635) mysqld!mysql_alter_table(THD*, char const*, char const*, st_ha_create_information*, TABLE_LIST*, Alter_info*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_table.cc:9807) mysqld!Sql_cmd_alter_table::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_alter.cc:333) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:4845) mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584) mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491) mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032) mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313) mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197) libsystem_pthread.dylib!_pthread_start (Unknown Source:0) libsystem_pthread.dylib!thread_start (Unknown Source:0) MDL鎖型別 MDL_INTENTION_EXCLUSIVE MDL_SHARED MDL_SHARED_HIGH_PRIO MDL_SHARED_READ MDL_SHARED_WRITE MDL_SHARED_WRITE_LOW_PRIO MDL_SHARED_UPGRADABLE MDL_SHARED_READ_ONLY MDL_SHARED_NO_WRITE MDL_SHARED_NO_READ_WRITE MDL_EXCLUSIVE 相容矩陣 Request | Granted requests for lock | type | S SH SR SW SWLP SU SRO SNW SNRW X | ----------+---------------------------------------------+ S | + + + + + + + + + - | SH | + + + + + + + + + - | SR | + + + + + + + + - - | SW | + + + + + + - - - - | SWLP | + + + + + + - - - - | SU | + + + + + - + - - - | SRO | + + + - - + + + - - | SNW | + + + - - - + - - - | SNRW | + + - - - - - - - - | X | - - - - - - - - - - | Request | Pending requests for lock | type | S SH SR SW SWLP SU SRO SNW SNRW X | ----------+--------------------------------------------+ S | + + + + + + + + + - | SH | + + + + + + + + + + | SR | + + + + + + + + - - | SW | + + + + + + + - - - | SWLP | + + + + + + - - - - | SU | + + + + + + + + + - | SRO | + + + - + + + + - - | SNW | + + + + + + + + + - | SNRW | + + + + + + + + + - | X | + + + + + + + + + + |
在online ddl執行期間，所有的修改會被記錄到row_log_t* online_log;
/** @brief Buffer for logging modifications during online index creation
All modifications to an index that is being created will be logged by
row_log_online_op() to this buffer.
All modifications to a table that is being rebuilt will be logged by
row_log_table_delete(), row_log_table_update(), row_log_table_insert()
to this buffer.
When head.blocks == tail.blocks, the reader will access tail.block
directly. When also head.bytes == tail.bytes, both counts will be
reset to 0 and the file will be truncated. */
struct row_log_t {
有興趣學習原始碼的加群一起學習啊 QQ： 700072075
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25719946/viewspace-2911797/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- unusable index對DML/QUERY的影響Index
- oracle點陣圖索引對DML操作的影響Oracle索引
- 驗證資料壓縮對DML的影響
- DDL,DML操作對結果快取的影響快取
- Alter修改表結構對資料儲存的影響PP
- mysql event對主從的影響MySql
- Oracle DML（非select） 操作不commit 對select的影響OracleMIT
- Sql Server之旅——第十站 看看DML操作對索引的影響SQLServer索引
- “影響者研究”系列（一）：社會網路化時代影響者對營銷創新的影響
- MySQL·8.0.0新特性·持久化自增列值MySql持久化
- InnoDB 隔離模式對 MySQL 效能的影響模式MySql
- 新《著作權法》對遊戲將有何影響？遊戲
- mysql自增列MySql
- mysql刪除和更新操作對效能的影響MySql
- 重啟mysql對於auto_increment的影響MySqlREM
- alter table新增欄位操作究竟有何影響？(上篇)
- alter table新增欄位操作究竟有何影響？(下篇)
- 新特性：/dev/shm對Oracle 11g的影響devOracle
- 伺服器IO瓶頸對MySQL效能的影響伺服器MySql
- mysql的DDL操作對業務產生影響測試MySql
- mysql事務對效率的影響分析總結JILEMySql
- table_open_cache引數對mysql效能的影響MySql
- 有關Oracle表分割槽進行(DML)維護後對索引的影響的分析Oracle索引
- alter session enable parallel dml 使DML語句並行執行SessionParallel並行
- 16、MySQL Case-索引key對select count(*)的影響MySql索引
- 【mysql】table中新增列MySql
- MySQL中的自增列MySql
- react 16.8版本新特性以及對react開發的影響React
- 【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響OracleMIT索引
- MYSQL sync_relay_log對I/O thread的影響分析MySqlthread
- KunlunDB對MySQL私有DML語法的支援MySql
- shrink 操作對索引的影響索引
- Update操作對索引的影響索引
- 新冠對工業機器人市場的潛在影響機器人
- 新會計準則實施對財務軟體的影響
- MySQL：查詢欄位數量多少對查詢效率的影響MySql
- MySQL：slave_skip_errors引數對MGR可用性的影響MySqlError
- 【Mysql】master_info 與 relay_info對資料庫的影響MySqlAST資料庫