This topic describes the limits that global secondary indexes (GSIs) in DRDS have on data manipulation language (DML).

Prerequisites

The versions of the ApsaraDB RDS for MySQL instances are 5.7 or later, and the versions of the DRDS instances are 5.4.1 or later.

Examples

This topic uses the following table to describe the limits that GSIs have on 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`);
  • The number of rows that are affected by INSERT SELECT, REPLACE SELECT, UPDATE, and DELETE statements cannot exceed 10,000.
    # The number of rows that are affected by the INSERT SELECT statement exceeds 10,000.
    INSERT INTO t_order SELECT * FROM t_order_bak WHERE id BETWEEN 0 AND 20000;
    # The number of rows that are affected by the DELETE statement exceeds 10,000.
    DELETE FROM t_order WHERE id BETWEEN 0 AND 20000;
  • Primary key conflicts may occur when INSERT IGNORE, INSERT ON DUPLICATE KEY UPDATE, and REPLACE statements are executed.
    INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1');
    # A primary key conflict may occur even if the IGNORE modifier is contained.
    INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_1', 'seller_1');
  • After an index fails to be written, other statements cannot be executed, or transactions cannot be committed.
    SET DRDS_TRANSACTION_POLICY='XA';
    INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1');
    # Failed.
    INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_1', 'seller_1');
    # Other statements cannot be executed after the failure occurs.
    INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_2', 'seller_2');
    # Transactions cannot be committed after the failure occurs.
    COMMIT;