Description:
Invisible indexes are not respected by slave sql applier under row binlog format, invisible index will be chose to do updating.
Although optimizer is not invoked for slave row applying, I think INVISIBLE attribute should be treat as DISABLED KEYS(which is respected by SQL applier and skipped). Besides, it can provide a chance to DBA for control index picking up.
How to repeat:
### test case as below:
--source include/master-slave.inc
--source include/have_binlog_format_row.inc
create table t1(id int auto_increment, name varchar(30), key idx_id(id)) engine=innodb;
insert into t1(name) values('MySQL');
insert into t1(name) values('InnoDB');
insert into t1(name) select a.name from t1 a, t1 b;
insert into t1(name) select a.name from t1 a, t1 b;
insert into t1(name) select a.name from t1 a, t1 b;
sync_slave_with_master;
## change idx_id index on slave invisible
connection slave;
alter table t1 alter index idx_id invisible;
connection master;
delete from t1 limit 100;
sync_slave_with_master;
## check whether invisible attribute is respected
connection slave;
select table_name, index_name, is_visible from information_schema.statistics where index_name = 'idx_id';
select * from sys.schema_unused_indexes where index_name = 'idx_id';
select * from sys.schema_index_statistics where index_name = 'idx_id';
==== below is partial result
select table_name, index_name, is_visible from information_schema.statistics where index_name = 'idx_id';
TABLE_NAME INDEX_NAME IS_VISIBLE
t1 idx_id NO
select * from sys.schema_unused_indexes where index_name = 'idx_id';
object_schema object_name index_name
select * from sys.schema_index_statistics where index_name = 'idx_id';
table_schema table_name index_name rows_selected select_latency rows_inserted insert_latency rows_updated update_latency rows_deleted delete_latency
test t1 idx_id 199 21.83 ms 0 0 ps 0 0 ps 100 16.63 ms
From the result we can see that although is invisible on slave, it is still used anyway.
Suggested fix:
/*
- Skip innactive keys
- Skip unique keys without nullable parts
- Skip indices that do not support ha_index_next() e.g. full-text
- Skip primary keys
*/
if (!(table->s->keys_in_use.is_set(key)) ||
((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME) ||
!(table->file->index_flags(key, 0, true) & HA_READ_NEXT) ||
(key == table->s->primary_key))
continue;
I think the fix is simple, we should use table->s->usable_indexes(thd) instead of keys_in_use bitmap to do index filter.
Description: Invisible indexes are not respected by slave sql applier under row binlog format, invisible index will be chose to do updating. Although optimizer is not invoked for slave row applying, I think INVISIBLE attribute should be treat as DISABLED KEYS(which is respected by SQL applier and skipped). Besides, it can provide a chance to DBA for control index picking up. How to repeat: ### test case as below: --source include/master-slave.inc --source include/have_binlog_format_row.inc create table t1(id int auto_increment, name varchar(30), key idx_id(id)) engine=innodb; insert into t1(name) values('MySQL'); insert into t1(name) values('InnoDB'); insert into t1(name) select a.name from t1 a, t1 b; insert into t1(name) select a.name from t1 a, t1 b; insert into t1(name) select a.name from t1 a, t1 b; sync_slave_with_master; ## change idx_id index on slave invisible connection slave; alter table t1 alter index idx_id invisible; connection master; delete from t1 limit 100; sync_slave_with_master; ## check whether invisible attribute is respected connection slave; select table_name, index_name, is_visible from information_schema.statistics where index_name = 'idx_id'; select * from sys.schema_unused_indexes where index_name = 'idx_id'; select * from sys.schema_index_statistics where index_name = 'idx_id'; ==== below is partial result select table_name, index_name, is_visible from information_schema.statistics where index_name = 'idx_id'; TABLE_NAME INDEX_NAME IS_VISIBLE t1 idx_id NO select * from sys.schema_unused_indexes where index_name = 'idx_id'; object_schema object_name index_name select * from sys.schema_index_statistics where index_name = 'idx_id'; table_schema table_name index_name rows_selected select_latency rows_inserted insert_latency rows_updated update_latency rows_deleted delete_latency test t1 idx_id 199 21.83 ms 0 0 ps 0 0 ps 100 16.63 ms From the result we can see that although is invisible on slave, it is still used anyway. Suggested fix: /* - Skip innactive keys - Skip unique keys without nullable parts - Skip indices that do not support ha_index_next() e.g. full-text - Skip primary keys */ if (!(table->s->keys_in_use.is_set(key)) || ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME) || !(table->file->index_flags(key, 0, true) & HA_READ_NEXT) || (key == table->s->primary_key)) continue; I think the fix is simple, we should use table->s->usable_indexes(thd) instead of keys_in_use bitmap to do index filter.