This topic describes the limits that global secondary indexes (GSIs) in PolarDB-X 1.0PolarDB-X 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 PolarDB-X 1.0 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;