* 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() //检查唯一索引冲突
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)
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() //检查唯一索引冲突
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)
《GreatSQL运维实战》视频课程
GreatSQL数据库是一款开源免费数据库,可在普通硬件上满足金融级应用场景,具有高可用、高性能、高兼容、高安全等特性,可作为MySQL或Percona Server for MySQL的理想可选替换。
⏩GitHub : https://github.com/GreatSQL/
🆙BiliBili : https://space.bilibili.com/1363850082
(对文章有疑问或见解可去社区官网提出哦~)
加入微信交流群 | 加入QQ交流群 |