写点什么

Oracle 与 GreatSQL 差异:更改唯一索引列

作者:GreatSQL
  • 2024-11-01
    福建
  • 本文字数:3239 字

    阅读完需:约 11 分钟

Oracle 与 GreatSQL 差异:更改唯一索引列

1.问题来源

在从 Oracle 迁移到 GreatSQL 的应用系统中,一条普通的 update 语句在 GreatSQL 中却报错,需要进行 SQL 语句的改写。把实际问题简化为下面简单情况进行说明。


在 Oracle 下,可以正常执行的 update 语句。


-- 建表CREATE TABLE test.test1 (    id INT PRIMARY KEY,    k INT NOT NULL,    c CHAR(120) NOT NULL,    pad CHAR(60) NOT NULL);
-- 创建唯一索引CREATE UNIQUE INDEX ui_test1_k ON test.test1 (k);
-- 插入数据INSERT INTO test.test1 VALUES (1, 1, 'cc', 'pad'),(2, 2, 'cc', 'pad'),(3, 3, 'cc', 'pad'),(4, 4, 'cc', 'pad');
-- 执行 UPDATE 语句UPDATE test.test1 SET k = k + 1;UPDATE test.test1 SET k = k - 1;
复制代码


在 GreatSQL 下准备测试表和数据:


CREATE TABLE `test1` (  `id` int NOT NULL AUTO_INCREMENT,  `k` int NOT NULL DEFAULT '0',  `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',  `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',  PRIMARY KEY (`id`),  UNIQUE KEY `ui_k` (`k`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
greatsql> INSERT INTO test1 VALUES (1, 1, 'cc', 'pad');greatsql> INSERT INTO test1 VALUES (2, 2, 'cc', 'pad');greatsql> INSERT INTO test1 VALUES (3, 3, 'cc', 'pad');greatsql> INSERT INTO test1 VALUES (4, 4, 'cc', 'pad');
复制代码


同样的 update 语句,在 GreatSQL 下执行报错:


greatsql> UPDATE test1 SET k = k + 1;ERROR 1062 (23000): Duplicate entry '2' for key 'test1.ui_k'
复制代码


在 GreatSQL 下,UPDATE 语句为什么报错呢?使用什么方法可以高效执行呢?

2.解决方法

由 UPDATE 语句的报错可知是唯一键重复的问题,将 k=1 的行更改 k=k+1=2,与 k=2 的行重复。解决方法有 2 个方向:


  • 让唯一索引暂时失效,update 完成后再启用

  • 让数据按一定的顺序执行,避免数据重复

尝试设置 unique_checks 为 0

设置会话系统变量 unique_checks=0,则允许存储引擎假定输入数据中不存在重复的键。如果您确定您的数据不包含唯一性冲突,那么您可以将它设置为 0,以加快将大型表导入 InnoDB 的速度。将此变量设置为 0 并不要求存储引擎忽略重复的键。仍然允许引擎检查它们,并且如果它检测到它们,就发出重复索引的错误。


实际测试,设置 UNIQUE_CHECKS=0,update 语句仍然报错。


greatsql> SET UNIQUE_CHECKS=0;Query OK, 0 rows affected (0.00 sec)
greatsql> UPDATE test1 SET k = k + 1;ERROR 1062 (23000): Duplicate entry '2' for key 'test1.ui_k'
复制代码

方法 1:删除唯一索引,update 后重建

删除唯一索引,update 后重建唯一索引的方法,有 2 个 DDL 操作,由于 DDL 前会自动提交事务,这种处理方法不能和其他操作在同一个事务中,同时也存在 update 后(有重复值)无法创建唯一索引的风险。适合数据的手工一次性处理。


greatsql> ALTER TABLE test1 DROP index kc;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0
greatsql> UPDATE test1 SET k=k+1 ;Query OK, 100 rows affected (0.01 sec)Rows matched: 100 Changed: 100 Warnings: 0
greatesql> ALTER TABLE test1 ADD UNIQUE key ui_k(k);Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0
复制代码

方法 2:update 按顺序执行

可以在 update 语句中使用 order by 子句,按照 k 值顺序执行,避免 update 后的数据与原有数据重复。


#k=k+1时,需要按照降序执行greatsql> UPDATE test1 SET k=k+1 ORDER BY k DESC; Query OK, 4 rows affected (0.01 sec)Rows matched: 4  Changed: 4  Warnings: 0
greatsql> UPDATE test1 SET k=k+1 ORDER BY k ; ERROR 1062 (23000): Duplicate entry '2' for key 'test1.ui_k'
#k=k-1时,需要按照升序执行greatsql> UPDATE test1 SET=k-1 ORDER BY k ;Query OK, 4 rows affected (0.01 sec)Rows matched: 4 Changed: 4 Warnings: 0
greatsql> UPDATE test1 SET k=k-1 ORDER BY k DESC; ERROR 1062 (23000): Duplicate entry '4' for key 'test1.ui_k'
复制代码

执行效率对比

对比方法 1 和方法 2 的执行效率。


#方法1:删除唯一索引,UPDATE后重建greatsql> ALTER TABLE test1 DROP index k;Query OK, 0 rows affected (0.17 sec)Records: 0  Duplicates: 0  Warnings: 0
greatsql> UPDATE test1 SET k=K+1;Query OK, 1000000 rows affected (35.08 sec)Rows matched: 1000000 Changed: 1000000 Warnings: 0
greatsql> ALTER TABLE test1 ADD UNIQUE index kc(k,c);Query OK, 0 rows affected (12.35 sec)Records: 0 Duplicates: 0 Warnings: 0
#方法2:UPDATE按顺序执行greatsql> UPDATE test1 set k=K+1 ORDER BY k DESC;Query OK, 1000000 rows affected (1 min 36.81 sec)Rows matched: 1000000 Changed: 1000000 Warnings: 0
复制代码


总结:执行时间 方法 1:方法 2=47.50 : 96.81 = 1 : 2.04 ,方法 1(删除唯一索引,update 后重建)比方法 2(update 按顺序执行)执行行效率高 1 倍。

3.GreatSQL 源码分析

通过对 GreatSQL 源码的分析,了解到上面 update 语句从 server 层调用 InnoDB 存储引擎层函数的调用关系如下,每 update 1 行数据调用 ha_update_row()一次,每 update 1 行数据后都要检查唯一索引是否发生冲突。


#server层Sql_cmd_update::update_single_table()->ha_update_row() //更新一行数据                                                                                 #innodb 存储引擎层    -> ha_innobase::update_row() //更新innodb一行数据        -> row_update_for_GreatSQL() //修改或删除数据            -> row_update_for_GreatSQL_using_upd_graph() //更新行                ->row_upd_step()  //更新行                        ->row_upd()  //更新索引                        ->row_upd_sec_step()  //更新索引                            ->row_ins_sec_index_entry()  //向索引中插入记录                                ->row_ins_sec_index_entry_low()  //向索引中插入记录                                    ->row_ins_scan_sec_index_for_duplicate()  //检查索引重复值                                        ->row_ins_dupl_error_with_rec() //检查唯一索引冲突
复制代码


  1. handler::ha_update_row 函数的主要功能是更新表中的一行数据,并记录该操作到二进制日志中。

  2. ha_innobase::update_row函数的主要功能是更新 InnoDB 表中的一行数据。

  3. row_update_for_GreatSQL 修改或删除数据行。

  4. row_update_for_GreatSQL_using_upd_graph 函数的主要功能是处理 GreatSQL 的行更新操作。

  5. row_upd_step 函数的主要功能是处理行更新操作。

  6. row_upd函数是更改数据行影响的索引。

  7. row_upd_sec_step函数是根据记录行的更改或删除,更改二级索引或删除二级索引。

  8. row_ins_sec_index_entry 函数的主要功能是向二级索引中插入一条记录。

  9. row_ins_sec_index_entry_low 函数的主要功能是向二级索引中插入一个索引项。它首先进行一些初始化和检查,然后根据索引类型(空间索引或普通索引)进行搜索。在搜索过程中,它会检查唯一性约束,并根据需要执行插入或修改操作。

  10. row_ins_scan_sec_index_for_duplicate函数的主要功能是扫描非聚集唯一索引,以检查是否存在与要插入的索引条目重复的记录。

  11. row_ins_dupl_error_with_rec 函数的主要功能是检查在插入索引条目时是否会发生唯一键冲突。它通过比较要插入的条目和现有记录的字段来确定是否存在重复。

4.总结

在更改唯一索引列时,Oracle 是完成 SQL 语句全部数据的更改后,再检查唯一索引的冲突;GreatSQL 则是在 SQL 语句更改每 1 条数据后,在更新索引数据检查唯一索引的冲突。在应用系统从 Oracle 迁移到 GreatSQL 时,需注意予以改写。

5.延伸阅读


发布于: 刚刚阅读数: 5
用户头像

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。 社区:https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

评论

发布
暂无评论
Oracle与GreatSQL差异:更改唯一索引列_GreatSQL_InfoQ写作社区