MySQL e二级索引上的一致性读
二级索引上没有rollpointer,所以只有当前版本，如果sql要通过二级索引查找数据，在存在多版本的情况下，还是要回到cluster index上进行多版本的查找。所以即使是select，性能也会出现不稳定的情况。
下面是代码中的例子：
create table t1(a int not null, b int, primary key(a), index(b));
insert into t1 values (1,1),(2,2);
commit;
sessin 1
select b from t1 where b >= 1;
session 2
update t1 set b = 5 where b = 2;
这样不能通过只是扫描二级索引获取结果了，还需要回表去查cluster index的历史版本。
堆栈如下：在row_search_mvcc中可以看到针对二级索引查询，是有判断的，获取二级索引页上的max_trx_id 进行可见性的判断。
mysqld!page_get_max_trx_id(unsigned char const*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/include/page0page.ic:80) mysqld!lock_sec_rec_cons_read_sees(unsigned char const*, dict_index_t const*, ReadView const*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/lock/lock0lock.cc:428) mysqld!row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0sel.cc:5673) mysqld!ha_innobase::general_fetch(unsigned char*, unsigned int, unsigned int) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/ha_innodb.cc:9052) mysqld!ha_innobase::index_next(unsigned char*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/ha_innodb.cc:9123) mysqld!handler::ha_index_next(unsigned char*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:3132) mysqld!join_read_next(READ_RECORD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_executor.cc:2676) mysqld!sub_select(JOIN*, QEP_TAB*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_executor.cc:1287) mysqld!do_select(JOIN*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_executor.cc:957) mysqld!JOIN::exec() (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_executor.cc:206) mysqld!handle_query(THD*, LEX*, Query_result*, unsigned long long, unsigned long long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_select.cc:191) mysqld!execute_sqlcom_select(THD*, TABLE_LIST*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5155) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:2826) 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)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25719946/viewspace-2916935/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
