MySQL e二级索引上的一致性读

来源：这里教程网时间：2026-03-01 16:49:52 作者：

二级索引上没有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)