数据库内核月报－ 2018/09 - MySQL · myrocks · collation 限制 - 《数据库内核月报》

背景
问题
原因
总结

背景

MyRocks中的数据是按索引列以memcmp方式进行排序的。对于一些数字类型，需要进行转化才能直接通过memcmp进行比较，例如有符号数在计算机中是用补码表示的，那么如果负数和正数直接按字节比较，结果负数会比正数大，实际存储时会将符号会反转存储，读取时再转化回来。对于字符类型，处理更加复杂，涉及到字符集的转换。记录格式可以参考[1], [2]

MyRocks索引字段如果包含字符类型，默认只支持binary collation，binary、latin1_bin、 utf8_bin其中的一种


1. ## Error
2. create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set gbk;
3. ERROR 3046 (HY000): Unsupported collation on string indexed column test.t1.c2 Use binary collation (binary, latin1_bin, utf8_bin).

5. ## Error
6. create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set utf8 collate utf8_general_ci;
7. ERROR 3046 (HY000): Unsupported collation on string indexed column test.t1.c2 Use binary collation (binary, latin1_bin, utf8_bin).

9. ## OK
10. create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set utf8 collate utf8_bin;
11. Query OK, 0 rows affected (0.00 sec)

通过设置rocksdb_strict_collation_check参数为OFF可以跳出binary collation的限制


1. set global rocksdb_strict_collation_check=OFF;

3. ## OK
4. create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set gbk;
5. Query OK, 0 rows affected (0.01 sec)

问题

MyRocks和InnoDB一样支持covering index. MyRocks在使用二级索引查询的时候，应尽量使用covering index, 因为MyRocks回表通过主键随机查询数据的开销比较大。

例如以下场景，idx1作为convering index被使用


2. create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set utf8 collate utf8_bin;

4. insert into t1 values(1,'ab');
5. insert into t1 values(2,'cd');

7. ## covering index
8. explain select c2 from t1 where c2='ab';
9. +----+-------------+-------+-------+---------------+------+---------+-------+------+-------------+
10. | id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra       |
11. +----+-------------+-------+-------+---------------+------+---------+-------+------+-------------+
12. |  1 | SIMPLE      | t1    | const | c2            | c2   | 33      | const |    1 | Using index |
13. +----+-------------+-------+-------+---------------+------+---------+-------+------+-------------+

然而设置set global rocksdb_strict_collation_check=OFF;在某些情况下会导致我们无法使用covering index.


1. set global rocksdb_strict_collation_check=ON;

3. ## Error
4. 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;
5. ERROR 3046 (HY000): Unsupported collation on string indexed column test.t1.c3 Use binary collation (binary, latin1_bin, utf8_bin).

7. set global rocksdb_strict_collation_check=OFF;

9. ## OK
10. create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set utf8 collate utf8_general_ci;
11. Query OK, 0 rows affected (0.00 sec)

13. insert into t1 values(1,1,'ab');
14. insert into t1 values(2,2,'cd');

17. insert into t1 values(1,'ab');
18. insert into t1 values(2,'cd');

20. ## non-covering index
21. explain select c2 from t1 where c2='ab';
22. +----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
23. | id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra |
24. +----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
25. |  1 | SIMPLE      | t1    | const | c2            | c2   | 33      | const |    1 | NULL  |
26. +----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
27. 1 row in set (0.00 sec)

原因

MyRocks二级索引由于collation的关系导致查询没有走covering index. MyRocks中索引列需要转化为memcomparable的形式，转化分为以下三种情况

1) 直接转换，不需要存储额外信息

例如 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使用


1. create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set latin1 collate latin1_general_ci;

3. insert into t1 values(1,'ab');
4. insert into t1 values(2,'cd');

6. ## covering index
7. explain select c2 from t1 where c2='ab';
8. +----+-------------+-------+-------+---------------+------+---------+-------+------+-------------+
9. | id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra       |
10. +----+-------------+-------+-------+---------------+------+---------+-------+------+-------------+
11. |  1 | SIMPLE      | t1    | const | c2            | c2   | 13      | const |    1 | Using index |
12. +----+-------------+-------+-------+---------------+------+---------+-------+------+-------------+
13. 1 row in set (0.00 sec)

3) 无法转换

除1，2两种情况外的collation, 例如utf8_general_ci

此时从二级索引中获取不到key的完整信息，需要从主键索引上获取，因此不能走covering index


1. create table t1(c1 int primary key, c2 varchar(10) unique) engine =rocksdb character set utf8 collate utf8_general_ci;

3. insert into t1 values(1,'ab');
4. insert into t1 values(2,'cd');

6. ## non-covering index
7. explain select c2 from t1 where c2='ab';
8. +----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
9. | id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows | Extra |
10. +----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
11. |  1 | SIMPLE      | t1    | const | c2            | c2   | 33      | const |    1 | NULL  |
12. +----+-------------+-------+-------+---------------+------+---------+-------+------+-------+
13. 1 row in set (0.00 sec)

此时的数据获取路径如下


1. 0  myrocks::ha_rocksdb::convert_record_from_storage_format
2. 1  myrocks::ha_rocksdb::get_row_by_rowid
3. 2  myrocks::ha_rocksdb::get_row_by_rowid
4. 3  myrocks::ha_rocksdb::read_row_from_secondary_key
5. 4  myrocks::ha_rocksdb::index_read_map_impl
6. 5  myrocks::ha_rocksdb::index_read_map
7. 6  handler::index_read_idx_map
8. 7  handler::ha_index_read_idx_map
9. 8  join_read_const
10. 9  join_read_const_table
11. 10 make_join_statistics
12. 11 JOIN::optimize
13. 12 mysql_execute_select
14. 13 mysql_select
15. 14 handle_select
16. 15 execute_sqlcom_select
17. 16 mysql_execute_command

转换的具体实现可以参考函数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


1. bool Rdb_key_def::covers_lookup(TABLE *const table,
2. const rocksdb::Slice *const unpack_info,
3. const MY_BITMAP *const lookup_bitmap) const {
4. DBUG_ASSERT(lookup_bitmap != nullptr);
5. if (!use_covered_bitmap_format() || lookup_bitmap->bitmap == nullptr) {
6. return false;
7. }

9. Rdb_string_reader unp_reader = Rdb_string_reader::read_or_empty(unpack_info);

11. // Check if this unpack_info has a covered_bitmap
12. const char *unpack_header = unp_reader.get_current_ptr();
13. const bool has_covered_unpack_info =
14. unp_reader.remaining_bytes() &&
15. unpack_header[0] == RDB_UNPACK_COVERED_DATA_TAG;
16. if (!has_covered_unpack_info ||
17. !unp_reader.read(RDB_UNPACK_COVERED_HEADER_SIZE)) {
18. return false;
19. }

21. MY_BITMAP covered_bitmap;
22. my_bitmap_map covered_bits;
23. bitmap_init(&covered_bitmap, &covered_bits, MAX_REF_PARTS, false);
24. covered_bits = rdb_netbuf_to_uint16((const uchar *)unpack_header +
25. sizeof(RDB_UNPACK_COVERED_DATA_TAG) +
26. RDB_UNPACK_COVERED_DATA_LEN_SIZE);

28. return bitmap_is_subset(lookup_bitmap, &covered_bitmap);
29. }

总结

MyRocks在字符collation上的限制需要引起我们关注，使用不当会影响查询效率。

原文：http://mysql.taobao.org/monthly/2018/09/09/