最近将5.7的开发测试环境升级到了8.0，开发测试同学反馈在执行ddl的时候变慢了，并反馈5.7 是正常的，要看下原因，所以对8.0的代码进行下分析，期间走了很多弯路，浪费了不少的时间。在这里介绍下正确的分析问题思路。 首先对alter的语句执行流程不清楚，所以也不清楚在哪里打断点，如果从一开始断电debug,无疑会浪费很多时间。所以正确的思路是使用mysql的trace功能，将sql 的执行过程全部记录下来，然后分析trace，在trace中找对应的函数 ./mysqld --debug=d,info,error,query,general,where:O,/tmp/mysqld.trace 在trace中找到可疑函数后，然后在函数处打断点，debug，通过分析trace，可以看到有很多的check_table_acess,将视图的每个基表都打开进行了check_table_acess,所以这个视图如果包含了很多的表，alter就会很慢。下面是8.0的sql 堆栈
mysqld!check_table_access(THD*, unsigned long, TABLE_LIST*, bool, unsigned int, bool) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/auth/sql_authorization.cc:2387) mysqld!parse_view_definition(THD*, TABLE_LIST*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/sql_view.cc:1362) mysqld!open_table(THD*, TABLE_LIST*, Open_table_context*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/sql_base.cc:3262) mysqld!open_and_process_table(THD*, LEX*, TABLE_LIST*, unsigned int*, Prelocking_strategy*, bool, Open_table_context*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/sql_base.cc:5018) mysqld!open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int, Prelocking_strategy*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/sql_base.cc:5805) mysqld!open_views_and_update_metadata(THD*, std::__1::vector<TABLE_LIST*, std::__1::allocator<TABLE_LIST*> > const*, bool, Uncommitted_tables_guard*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/dd_sql_view.cc:483) mysqld!bool update_view_metadata<dd::View_table>(THD*, char const*, char const*, bool, Uncommitted_tables_guard*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/dd_sql_view.cc:716) mysqld!update_referencing_views_metadata(THD*, char const*, char const*, char const*, char const*, bool, Uncommitted_tables_guard*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/dd_sql_view.cc:733) mysqld!update_referencing_views_metadata(THD*, TABLE_LIST const*, char const*, char const*, bool, Uncommitted_tables_guard*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/dd_sql_view.cc:753) mysqld!update_referencing_views_metadata(THD*, TABLE_LIST const*, bool, Uncommitted_tables_guard*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/dd_sql_view.cc:762) mysqld!mysql_inplace_alter_table(THD*, dd::Schema const&, dd::Schema const&, dd::Table const*, dd::Table*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, enum_alter_inplace_result, Alter_table_ctx*, std::__1::set<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::less<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > >, Stateless_allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, histograms::Histogram_psi_key_alloc, My_free_functor> >&, FOREIGN_KEY*, unsigned int, Foreign_key_parents_invalidator*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/sql_table.cc:13219) mysqld!mysql_alter_table(THD*, char const*, char const*, HA_CREATE_INFO*, TABLE_LIST*, Alter_info*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/sql_table.cc:16910) mysqld!Sql_cmd_alter_table::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/sql_alter.cc:349) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/sql_parse.cc:4407) mysqld!dispatch_sql_command(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/sql_parse.cc:4988) mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/sql_parse.cc:1836) mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/sql_parse.cc:1320) mysqld!handle_connection(void*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/sql/conn_handler/connection_handler_per_thread.cc:301) mysqld!pfs_spawn_thread(void*) (/Users/xiaoyu.bai/Downloads/mysql-8.0.23/storage/perfschema/pfs.cc:2900) libsystem_pthread.dylib!_pthread_start (Unknown Source:0)
下面看下5.7的处理逻辑，可以看到5.7上并没有去检查视图相关的操作，也没有去打开视图中的表并进行权限的检查。所以升级后，mysql8执行alter的速度比5.7慢
mysqld!MYSQL_BIN_LOG::gtid_end_transaction(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/binlog.cc:1513) mysqld!binlog_gtid_end_transaction(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:2428) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5096) 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)
有兴趣学习源码的加群一起学习啊 QQ： 700072075
