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索引
- Alter修改表結構對資料儲存的影響PP
- MySQL ALTER命令MySql
- KunlunDB對MySQL私有DML語法的支援MySql
- mysql事務對效率的影響分析總結JILEMySql
- 伺服器IO瓶頸對MySQL效能的影響伺服器MySql
- 16、MySQL Case-索引key對select count(*)的影響MySql索引
- 【mysql】table中新增列MySql
- MySQL中的自增列MySql
- 新特性：/dev/shm對Oracle 11g的影響devOracle
- Omdia：新冠疫情對專網市場的影響
- 新《著作權法》對遊戲將有何影響？遊戲
- mysql加快alter操作MySql
- mysql的DDL操作對業務產生影響測試MySql
- 新冠疫情對旅遊業的影響還未散去
- Cirium：資料揭示新冠肺炎對中國航空業的影響及對全球航空旅遊增長的影響
- mysql的ALTER TABLE命令MySql
- MySQL：查詢欄位數量多少對查詢效率的影響MySql
- MySQL：slave_skip_errors引數對MGR可用性的影響MySqlError
- Mysql DML 新增資料MySql
- 影響mysql效能的因素都有哪些MySql
- 影響MySQL效能的硬體因MySql
- 影響MySQL效能的硬體因素MySql
- 上海交大：新冠肺炎疫情對若干行業的影響分析行業
- 新冠對工業機器人市場的潛在影響機器人
- Facebook Gaming調查了新冠疫情對工作產生的影響GAM
- react 16.8版本新特性以及對react開發的影響React
- Nologging對恢復的影響(二)
- 語言對思維的影響
- Nologging對恢復的影響(一)
- 海外伺服器對SEO影響？伺服器
- MySQL運維實戰(5.6) 字符集設定對mysqldump的影響MySql運維
- 第49問：如何快速判斷 IO 延遲對 MySQL 效能的影響MySql
- mysql表操作(alter)/mysql欄位型別MySql型別
- MySql給表新增列和註釋MySql
- MySQL自增列ID的面試題MySql面試題
- 網線的分類與對網速的影響 網線對網速影響大嗎？