Oracle與GreatSQL差異：更改唯一索引列

GreatSQL發表於2024-11-01
OracleSQL索引

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.延伸閱讀

  • UPDATE 時主鍵衝突引發的思考(https://imysql.com/2008_06_17_sth_about_update_duplicate_key)

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

image-20221030163217640

相關文章