MyRocks索引字段如果包含字符类型,默认只支持binary collation,binary、latin1_bin、 utf8_bin其中的一种
通过设置rocksdb_strict_collation_check参数为OFF可以跳出binary collation的限制
## OK
create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set gbk;
Query OK, 0 rows affected (0.01 sec)
MyRocks和InnoDB一样支持covering index. MyRocks在使用二级索引查询的时候,应尽量使用covering index, 因为MyRocks回表通过主键随机查询数据的开销比较大。
例如以下场景,idx1作为convering index被使用
然而设置set global rocksdb_strict_collation_check=OFF;在某些情况下会导致我们无法使用covering index.
set global rocksdb_strict_collation_check=ON;
## Error
create table t1(c1 int primary key, c2 int, c3 varchar(10), key idx1(c2,c3)) engine =rocksdb character set utf8 collate utf8_general_ci;
ERROR 3046 (HY000): Unsupported collation on string indexed column test.t1.c3 Use binary collation (binary, latin1_bin, utf8_bin).
set global rocksdb_strict_collation_check=OFF;
## OK
create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
insert into t1 values(1,1,'ab');
insert into t1 values(2,2,'cd');
insert into t1 values(1,'ab');
insert into t1 values(2,'cd');
explain select c2 from t1 where c2='ab';
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | t1 | const | c2 | c2 | 33 | const | 1 | NULL |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)
MyRocks二级索引由于collation的关系导致查询没有走covering index. MyRocks中索引列需要转化为memcomparable的形式,转化分为以下三种情况
-
例如 binary、latin1_bin、 utf8_bin三种collation可以直接转换
这种情况二级索引列数据可以完整的从二级索引中取到,不影响covering index使用
2) 间接转换,需在value中增加unpack_info
例如latin1_general_ci,latin2_general_ci, ascii_general_ci,greek_general_ci等collation,具体可以参考函数rdb_is_collation_supported
这种情况二级索引列数据可以从key和unpack_info中解析取到,也不影响covering index使用
3) 无法转换
此时从二级索引中获取不到key的完整信息,需要从主键索引上获取,因此不能走covering index
create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set utf8 collate utf8_general_ci;
insert into t1 values(1,'ab');
insert into t1 values(2,'cd');
## non-covering index
explain select c2 from t1 where c2='ab';
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | t1 | const | c2 | c2 | 33 | const | 1 | NULL |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)
此时的数据获取路径如下
转换的具体实现可以参考函数Rdb_field_packing::setup
MyRocks从索引读取数据时,不能仅通过keyread_only来判断是否可以使用covering index, 还需要判断是否存在collation列数据转换的问题,如果访问的列无法转换就不能使用covering index.
MyRocks会在value中存储covered_bitmap,表示索引列是否可以转换, read_row_from_secondary_key/secondary_index_read读取时会根据covered_bitmap来决定是否能使用covering index
bool Rdb_key_def::covers_lookup(TABLE *const table,
const rocksdb::Slice *const unpack_info,
if (!use_covered_bitmap_format() || lookup_bitmap->bitmap == nullptr) {
return false;
}
Rdb_string_reader unp_reader = Rdb_string_reader::read_or_empty(unpack_info);
// Check if this unpack_info has a covered_bitmap
const char *unpack_header = unp_reader.get_current_ptr();
const bool has_covered_unpack_info =
unp_reader.remaining_bytes() &&
unpack_header[0] == RDB_UNPACK_COVERED_DATA_TAG;
if (!has_covered_unpack_info ||
!unp_reader.read(RDB_UNPACK_COVERED_HEADER_SIZE)) {
return false;
}
MY_BITMAP covered_bitmap;
my_bitmap_map covered_bits;
bitmap_init(&covered_bitmap, &covered_bits, MAX_REF_PARTS, false);
covered_bits = rdb_netbuf_to_uint16((const uchar *)unpack_header +
sizeof(RDB_UNPACK_COVERED_DATA_TAG) +
RDB_UNPACK_COVERED_DATA_LEN_SIZE);
}
MyRocks在字符collation上的限制需要引起我们关注,使用不当会影响查询效率。