PolarDB-X 云原生分布式数据库 全局二级索引对DML的限制

前提条件

MySQL版本需为5.7或以上，且PolarDB-X实例版本需为5.4.1或以上，关于如何查看实例版本，请参见实例版本概览。

示例

本文将以下表为例介绍全局二级索引对DML的不同限制：

CREATE TABLE t_order(
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `l_i_order` (`order_id`),
  GLOBAL INDEX `g_i_seller` (`seller_id`) dbpartition by hash(`seller_id`) tbpartition by hash(`seller_id`),
  GLOBAL UNIQUE INDEX `g_i_buyer` (`buyer_id`) COVERING (order_snapshot) dbpartition by hash(`buyer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);

• INSERT SELECT、REPLACE SELECT、UPDATE和DELETE的更新行数不超过10000行

  # INSERT SELECT 插入行数超过 10000
  INSERT INTO t_order SELECT * FROM t_order_bak WHERE id BETWEEN 0 AND 20000;
  # DELETE 删除行数超过 10000
  DELETE FROM t_order WHERE id BETWEEN 0 AND 20000;

• INSERT IGNORE、INSERT ON DUPLICATE KEY UPDATE、REPLACE可能报主键冲突

  INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1');
  # IGNORE 仍有可能报主键冲突
  INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_1', 'seller_1');

• 写索引失败后，不允许继续执行其他语句或提交事务

  SET DRDS_TRANSACTION_POLICY='XA';
  INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1');
  # 失败
  INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_1', 'seller_1');
  # 失败不允许继续执行
  INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_2', 'seller_2');
  # 失败后不允许提交事务
  COMMIT;