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

我愿为卒

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

1.问题来源

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

1. -- 建表
2. CREATE TABLE test.test1 (
3.     id INT PRIMARY KEY,
4.     k INT NOT NULL,
5.     c CHAR(120) NOT NULL,
6.     pad CHAR(60) NOT NULL
7. );
9. -- 创建唯一索引
10. CREATE UNIQUE INDEX ui_test1_k ON test.test1 (k);
12. -- 插入数据
13. INSERT INTO test.test1 VALUES
14. (1, 1, 'cc', 'pad'),
15. (2, 2, 'cc', 'pad'),
16. (3, 3, 'cc', 'pad'),
17. (4, 4, 'cc', 'pad');
19. -- 执行 UPDATE 语句
20. UPDATE test.test1 SET k = k + 1;
21. UPDATE test.test1 SET k = k - 1;

复制代码

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

1. CREATE TABLE `test1` (
2.   `id` int NOT NULL AUTO_INCREMENT,
3.   `k` int NOT NULL DEFAULT '0',
4.   `c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
5.   `pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
6.   PRIMARY KEY (`id`),
7.   UNIQUE KEY `ui_k` (`k`)
8. ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
10. greatsql> INSERT INTO test1 VALUES (1, 1, 'cc', 'pad');
11. greatsql> INSERT INTO test1 VALUES (2, 2, 'cc', 'pad');
12. greatsql> INSERT INTO test1 VALUES (3, 3, 'cc', 'pad');
13. greatsql> INSERT INTO test1 VALUES (4, 4, 'cc', 'pad');

复制代码

同样的update语句，在GreatSQL下执行报错：

1. greatsql> UPDATE test1 SET k = k + 1;
2. 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语句仍然报错。

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

复制代码

方法1：删除唯一索引，update后重建

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

1. greatsql> ALTER TABLE test1 DROP index kc;
2. Query OK, 0 rows affected (0.01 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
5. greatsql> UPDATE test1 SET k=k+1 ;
6. Query OK, 100 rows affected (0.01 sec)
7. Rows matched: 100  Changed: 100  Warnings: 0
9. greatesql> ALTER TABLE test1 ADD UNIQUE key ui_k(k);
10. Query OK, 0 rows affected (0.09 sec)
11. Records: 0  Duplicates: 0  Warnings: 0

复制代码

方法2：update按顺序执行

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

1. #k=k+1时，需要按照降序执行
2. greatsql> UPDATE test1 SET k=k+1 ORDER BY k DESC;
3. Query OK, 4 rows affected (0.01 sec)
4. Rows matched: 4  Changed: 4  Warnings: 0
6. greatsql> UPDATE test1 SET k=k+1 ORDER BY k ;
7. ERROR 1062 (23000): Duplicate entry '2' for key 'test1.ui_k'
9. #k=k-1时，需要按照升序执行
10. greatsql> UPDATE test1 SET=k-1 ORDER BY k ;
11. Query OK, 4 rows affected (0.01 sec)
12. Rows matched: 4  Changed: 4  Warnings: 0
14. greatsql> UPDATE test1 SET k=k-1 ORDER BY k DESC;
15. ERROR 1062 (23000): Duplicate entry '4' for key 'test1.ui_k'

复制代码

执行效率对比

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

1. #方法1：删除唯一索引，UPDATE后重建
2. greatsql> ALTER TABLE test1 DROP index k;
3. Query OK, 0 rows affected (0.17 sec)
4. Records: 0  Duplicates: 0  Warnings: 0
6. greatsql> UPDATE test1 SET k=K+1;
7. Query OK, 1000000 rows affected (35.08 sec)
8. Rows matched: 1000000  Changed: 1000000  Warnings: 0
10. greatsql> ALTER TABLE test1 ADD UNIQUE index kc(k,c);
11. Query OK, 0 rows affected (12.35 sec)
12. Records: 0  Duplicates: 0  Warnings: 0
14. #方法2：UPDATE按顺序执行
15. greatsql> UPDATE test1 set k=K+1 ORDER BY k DESC;
16. Query OK, 1000000 rows affected (1 min 36.81 sec)
17. 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行数据后都要检查唯一索引是否发生冲突。

1. #server层
2. Sql_cmd_update::update_single_table()
3. ->ha_update_row() //更新一行数据                                                                             
4.     #innodb 存储引擎层
5.     -> ha_innobase::update_row() //更新innodb一行数据
6.         -> row_update_for_GreatSQL() //修改或删除数据
7.             -> row_update_for_GreatSQL_using_upd_graph() //更新行
8.                 ->row_upd_step()  //更新行   
9.                     ->row_upd()  //更新索引
10.                         ->row_upd_sec_step()  //更新索引
11.                             ->row_ins_sec_index_entry()  //向索引中插入记录
12.                                 ->row_ins_sec_index_entry_low()  //向索引中插入记录
13.                                     ->row_ins_scan_sec_index_for_duplicate()  //检查索引重复值
14.                                         ->row_ins_dupl_error_with_rec() //检查唯一索引冲突

复制代码

• handler::ha_update_row 函数的主要功能是更新表中的一行数据，并记录该操作到二进制日志中。
  
• ha_innobase::update_row函数的主要功能是更新InnoDB表中的一行数据。
  
• row_update_for_GreatSQL 修改或删除数据行。
  
• row_update_for_GreatSQL_using_upd_graph 函数的主要功能是处理 GreatSQL 的行更新操作。
  
• 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 函数的主要功能是检查在插入索引条目时是否会发生唯一键冲突。它通过比较要插入的条目和现有记录的字段来确定是否存在重复。
  

4.总结

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

• UPDATE 时主键冲突引发的思考(https://imysql.com/2008_06_17_sth_about_update_duplicate_key)
  

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/18519779
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！