OceanBase MySQL 的多索引变更分析

数据库技术闲谈 2024-10-10

344

MySQL 表结构变更语句可以包含多个变更操作，包括列、索引的变更。OB 的 MySQL 租户也兼容实现了这一功能。不过由于是分布式数据库架构，内部实现细节会跟 MySQL 有些不一样。不了解这个细节，在日常变更中可能会带来一些风险。尤其是当这些变更语句是研发同学写的并且未经 DBA 审核的时候就上线了。

MySQL 多索引变更

先从 MySQL 说起。场景需求也很简单，性能诊断的时候想替换掉一个表的索引。比如说以 sysbench
的测试表 sbtest1
为例。

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.14 sec)

业务查询 SQL 示例：

mysql> explain select count(*) from sbtest1 where k=456174 and c like '58241701921-%';
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | sbtest1 | NULL       | ref  | k_1           | k_1  | 4       | const |    2 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

目前这个查询也能用上一个索引 k_1(k)
。数据库索引创建原则应当尽量兼顾多个业务查询场景，优先考虑重要业务场景等。假设经过综合分析后，应该创建一个新索引k_2(k,c)
。同时由于这个新索引能覆盖老的单列索引k_1
,所以新索引创建后老的索引也可以删除。

通常来说，严谨一些的索引变更语句应该分三步。

-- 创建新的索引
alter table sbtest1 add key k_2(k,c);

-- 验证业务 SQL 执行计划和性能


-- 删除老的索引
alter table sbtest1 drop key k_1 ;

一般来说，新索引更好的时候，在新索引创建成功后 SQL 的执行计划就会发生变化。不过当类似单列索引很多，且业务查询条件多种复杂组合的时候，有些老的查询不一定能用上新的索引。MySQL SQL 引擎功能比较简单，ORACLE 和 OceanBase 的 SQL 引擎功能就比较复杂，这里变数可能更多。当确认新索引有效时就可以删除老的索引。

实际操作中，如果表非常大（如上亿级别），每次变更时间可能很长，为了缩短变更时间，有时候变更人员会将新增索引和删除索引放一起操作。当然这样做的另外一个原因也是因为非常确认这个新索引一定能顶替老的索引。

变更方案如下：

alter table sbtest1 add key k_2(k,c) ,drop key k_1;

这是 MySQL 的 DDL 语句特色，ORACLE 就没有这个语法。在 MySQL 里，会话默认是不开启事务的（autocommit=0
）。即使会话开启了事务，做了 DDL 后，DDL 成功时也会自动提交事务。所以一个好的经验是尽量不要在事务里使用 DDL，会导致事务被提前提交，可能带来业务异常。撇开会话事务的干扰，DDL 自身也应该是一个事务。多个索引变更操作要么全部成功，要么全部失败。我们可以用下面的方法验证一下这个。

mysql> insert into sbtest1(k,c,pad) select k,c,pad from sbtest1 limit 2;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show indexes from sbtest1;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sbtest1 |          0 | PRIMARY  |            1 | id          | A         |      986400 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sbtest1 |          1 | k_1      |            1 | k           | A         |      181043 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

mysql> alter table sbtest1 add unique key k_2(k,c) ,drop key k_1;
ERROR 1062 (23000): Duplicate entry '501462-68487932199-96439406143-93774651418-41631865787-964060727' for key 'sbtest1.k_2'
mysql> show indexes from sbtest1;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sbtest1 |          0 | PRIMARY  |            1 | id          | A         |      986400 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sbtest1 |          1 | k_1      |            1 | k           | A         |      181043 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

从上面结果看，当新增唯一索引失败的时候，删除索引也不会成功。这个是很重要的。如果新增索引失败，删除索引成功，就会导致表没有合适的索引满足业务查询需求，从而引起严重的业务性能问题。

即使新增索引不失败，这里依然有个问题值得思考。就是数据库是先新增索引后删除索引吗？还是先删除索引后新增索引？跟 SQL 书写的顺序有关吗？MySQL 的观察手段很有限，除非阅读源码否则恐怕没法知道这个问题准确答案。不过如果 DDL 事务保证原子性，这些问题都不存在。实际运维中我们也确实不关心这个。

尽管如此，我还是想了一个办法来观察在 DDL 新增和删除索引不报错期间，是否真的是原子性，或者是否有索引空缺的时机。用下面的脚本循环查看 SQL 执行计划。

for i in `seq 3600`;do echo `date`; mysql -h10.0.0.66 -uu_test -P3306 -p123456 sysbenchdb -c -A -e "explain select count(*) from sbtest1 where k=456174 and c like '0620892854%';"; sleep 0.5; done

刚开始输出如下：

当表很大的时候，如果存在索引先删除后新增并且中间有空档的时机，就可以观察到 SQL 没有用索引的执行计划。表索引变更如下：

mysql> alter table sbtest1 add  key k_2(k,c) ,drop key k_1;
Query OK, 0 rows affected (5.75 sec)
Records: 0  Duplicates: 0  Warnings: 0

观察结果如下：

可以看到，SQL 的执行计划里的索引从 k_1
平滑迁移到 k_2
。

以上分析纯粹研究，结论依然是 MySQL 的 DDL 事务是保证的。所以 MySQL 这种用法平常也不见有什么问题。但是，可以替换 MySQL 的各个分布式数据库在这个测试案例上表现是否一定一样呢，这个是需要 DBA 去验证的。

OB 多索引变更观察

首先也是用 sysbench 在 OB MySQL 租户里初始化一个数据量在 100w 以上的表 sbtest1
。然后同样的运行上面的观察语句。

for i in `seq 36000`;do echo `date`; obclient -h127.1 -uu_test@obmysql -P2881 -p'123456' -c -A sysbenchdb -e "explain select count(*) from sbtest1 where k=456174 and c like '58241701921-98684430766-6682457442%';"; sleep 0.5 ;done

当前运行结果，SQL执行计划使用的是索引 k_1
。

然后执行多索引变更语句：

obclient [sysbenchdb]> alter table sbtest1 add unique key k_2(k,c) ,drop key k_1;
ERROR 1062 (23000): Duplicate entry '501462-68487932199-96439406143-93774651418-41631865787-96406072701-20604855487-25459966574-28203206787-41238978918-19503783441' for key 'k_2'
obclient [sysbenchdb]> show indexes from sbtest1;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| sbtest1 |          0 | PRIMARY  |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
1 row in set (0.003 sec)

由输出可见，唯一索引冲突创建失败，但是老的索引删除成功了。

再观察一下普通索引创建成功时的执行计划变化。

obclient [sysbenchdb]> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-10-09 14:30:17 |
+---------------------+
1 row in set (0.000 sec)

obclient [sysbenchdb]> alter table sbtest1 add key k_2(k,c) ,drop key k_1;
Query OK, 0 rows affected (8.673 sec)

中间出现执行计划没有用上索引的情形。

最后执行计划用上了新的索引。

由此可见，在多索引变更上 OB MySQL 租户跟 MySQL 的行为在特殊案例上并不完全一样。这是一个使用风险点。

OB DDL 观察

OB 的观察手段很多，业务租户提供了一个内部视图 __all_ddl_operation
用于观察 DDL 的执行细节过程。

select o.tenant_id, o.gmt_create,  o.database_id, d.database_name, o.table_id, t.table_name, o.operation_type, o.ddl_stmt_str 
from oceanbase.__all_ddl_operation o left join oceanbase.`__all_database` d on (o.database_id=d.database_id)
left join oceanbase.__all_table t on (o.table_id=t.table_id)
where o.gmt_create >='2024-10-09 14:30:17'
order by o.gmt_create ;

结果如下：

这个结果解读起来有点不是那么直观。operation_type
是枚举类型，含义不明。此外，部分记录的列 table_name
为空（有些是索引名。）。

查官网文档得到枚举的含义，节选如下。

类型ID	类型标识符	类型描述
3	OB_DDL_ALTER_TABLE	ALTER TABLE 语句
13	OB_DDL_MODIFY_INDEX_STATUS	修改索引状态
14	OB_DDL_MODIFY_TABLE_SCHEMA_VERSION	修改SCHEMA版本
15	OB_DDL_MODIFY_TABLE_OPTION	修改表的选项
18	OB_DDL_DROP_INDEX	删除索引
20	OB_DDL_CREATE_INDEX	创建索引
32	OB_DDL_RENAME_INDEX	重命名索引
1503	OB_DDL_END_SIGN	DDL结束信号

结合上面有限的信息推测（纯粹推测）：

DROP INDEX
比 ADD INDEX
先结束。DROP INDEX
之所以看不到索引名，是因为索引已经 DROP 了，无法关联获取索引名称。
有 4 次 DDL 结束信号。OB 没有去保证 DDL 事务原子性，而可能是将 DDL 语句多个变更操作分拆为多个独立的子任务，每个子任务有独立的事务。子任务（原子变更任务）之间的依赖关系由用户自己负责。

OB 的这个做法初衷应该是为了发挥分布式架构的优势，提升 DDL 的效率（特别是大表变更）。这个变化带来的风险确实很大。这个设计后期估计会调整，具体行为会如何到时候再看。

OceanBase MySQL 的多索引变更分析

MySQL 多索引变更

OB 多索引变更观察

OB DDL 观察

更多思考

评论