数据分片全局索引 SQL 兼容性 #
SphereEx-DBPlusEngine 数据分片全局索引功能支持强一致和弱一致两种模式，强一致模式下业务增量写入依赖 SQL 改写，将主表写入 SQL 改写为主表和索引表的多条写入 SQL，然后依靠分布式事务保证数据的强一致。 弱一致模式下业务增量写入依赖 Pipeline 的同步任务，通过监听 MySQL binlog 实现增量数据同步到索引表，由于 binlog 同步存在延迟，因此主表和索引表只能保证弱一致。
受限于全局索引增量数据的不同同步方式，全局索引功能也存在部分 SQL 运算无法兼容，需要用户在使用过程中进行规避，下面展示了 SphereEx-DBPlusEngine 能够支持以及不支持的常用 SQL 列表，可供用户使用参考。
说明：以下不支持的 SQL 列表中，DML 写操作不支持项仅适用于强一致模式，该模式下需要通过 DBPlusEngine 改写后写入 DB，弱一致模式通过 binlog 同步，支持所有类型的 DML 写操作。 创建全局索引时，主表必须有主键，支持单一主键和复合主键（未来可以支持无主键，有唯一键的场景）。此外，创建全局索引时，索引名称必须和全局索引规则中名称保持一致，此时才会触发全局索引逻辑。 由于全局索引基于分片表实现，因此需要保证全局索引名称在逻辑库内唯一，建议使用逻辑表 + 索引作为全局索引名称以保证唯一性。
支持的常用 SQL 列表 #
MySQL #
- DDL 语句——创建索引
| SQL 示例 | 说明 |
|---|---|
| CREATE INDEX t_order_user_id_idx ON t_order(user_id) | 支持使用 CREATE INDEX 语句创建全局索引 |
| CREATE TABLE t_order (order_id INT, INDEX t_order_order_id_idx(order_id, status), INDEX t_order_user_id_idx(user_id, status)) | 支持使用 CREATE TABLE 语句指定 INDEX 创建全局索引 |
| CREATE TABLE t_order (order_id INT, KEY t_order_order_id_idx(order_id, status), KEY t_order_user_id_idx(user_id, status)) | 支持使用 CREATE TABLE 语句指定 KEY 创建全局索引 |
| CREATE TABLE t_order (order_id INT, CONSTRAINT t_order_order_id_idx UNIQUE INDEX | KEY (order_id, status)) | 支持使用 CREATE TABLE 语句指定 CONSTRAINT … UNIQUE INDEX | KEY 创建全局索引 |
| CREATE TABLE t_order (order_id INT, CONSTRAINT t_order_order_id_idx PRIMARY KEY (order_id, status)) | 支持使用 CREATE TABLE 语句指定 CONSTRAINT … PRIMARY KEY 创建全局索引 |
| ALTER TABLE t_order ADD INDEX | KEY t_order_order_id_idx(order_id, status) | 支持使用 ALTER TABLE 语句指定 ADD INDEX | KEY 创建全局索引 |
| ALTER TABLE t_order ADD CONSTRAINT UNIQUE INDEX | KEY t_order_order_id_idx(order_id, status) | 支持使用 ALTER TABLE 语句指定 ADD CONSTRAINT UNIQUE INDEX | KEY 创建全局索引 |
- DDL 语句——删除索引
| SQL 示例 | 说明 |
|---|---|
| DROP INDEX t_order_user_id_idx ON t_order | 支持使用 DROP INDEX 语句删除全局索引 |
| ALTER TABLE t_order DROP INDEX | KEY t_order_user_id_idx | 支持使用 ALTER TABLE 语句指定 DROP INDEX | KEY 删除全局索引 |
| ALTER TABLE t_order DROP CONSTRAINT t_order_user_id_idx | 支持使用 ALTER TABLE 语句指定 DROP CONSTRAINT 删除全局索引 |
- DML 语句——INSERT
| SQL 示例 | 说明 |
|---|---|
| INSERT INTO t_order (order_id, user_id, status, merchant_id, creation_date) VALUES (?, ?, ?, 1, ‘2017-08-08’) | 支持简单的 INSERT … INTO … VALUES | VALUE 语句 |
| INSERT INTO t_order VALUES ROW(?, ?, ?) | 支持简单的 INSERT … INTO … VALUES ROW() 语句（待实现） |
| INSERT INTO t_order VALUES SET order_id = ?, user_id = ?, status = ? | 支持简单的 INSERT … INTO … SET 语句（待实现） |
| INSERT INTO t_order VALUES (?, ?, 1, ’test’, ‘2017-08-08’) | 支持 INSERT … INTO 语句中使用分布式主键（SQL 中不指定分布式主键列） |
- DML 语句——DELETE
| SQL 示例 | 说明 |
|---|---|
| DELETE FROM t_order WHERE order_id = ? | 支持 DELETE … WHERE … |
- DML 语句——UPDATE
| SQL 示例 | 说明 |
|---|---|
| UPDATE t_order SET user_id = ?, status = ?, merchant_id = ?, remark = ’test’, creation_date = ‘2019-08-08’ | 支持 UPDATE … SET … WHERE … |
| UPDATE t_order SET order_id = ?, user_id = ?, status = ?, merchant_id = ?, remark = ’test’, creation_date = ‘2019-08-08’ WHERE order_id = ? | 支持 UPDATE … SET … 更新分片键 |
- DML 语句——SELECT
| SQL 示例 | 说明 |
|---|---|
| SELECT * FROM t_order | 查询不包含 WHERE 条件，走主表查询 |
| SELECT * FROM t_order WHERE creation_date <= ? | 查询包含 WHERE 条件，但不包含 t_order 表及所有索引表分片键，走主表查询 |
| SELECT * FROM t_order WHERE order_id = ? | 查询包含 WHERE 条件，并且包含 t_order 表分片键，走主表查询 |
| SELECT * FROM t_order WHERE user_id = ? OR merchant_id = ? | 查询包含 WHERE 条件，不包含 t_order 表分片键，包含 t_order_user_id_idx 分片键和 t_order_product_id_idx 分片键，并使用 OR 组合，走主表查询 |
| SELECT * FROM t_order WHERE order_id IN (SELECT order_id FROM t_order WHERE merchant_id = ?) | 子查询，走主表查询 |
| SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.order_id = ? | 关联查询，走主表查询 |
| SELECT user_id, COUNT(1) FROM t_order GROUP BY user_id | 聚合查询，走主表查询 |
| SELECT user_id, ROW_NUMBER() OVER w AS ‘row_number’, RANK() OVER w AS ‘rank’, DENSE_RANK() OVER w AS ‘dense_rank’ FROM t_order WHERE order_id = ? WINDOW w AS (ORDER BY user_id) | 窗口函数，走主表查询 |
| SELECT * FROM t_order WHERE user_id = ? ORDER BY order_id | 排序操作，走主表查询 |
| SELECT * FROM t_order WHERE user_id = ? LIMIT 1, 2 | 分页操作，走主表查询 |
| SELECT user_id, order_id FROM t_order WHERE order_id = ? UNION ALL SELECT user_id, order_id FROM t_order_item WHERE order_id = ? ORDER BY user_id | UNION/INTERSECT/EXCEPT/MINUS 查询，走主表查询 |
| SELECT * FROM t_order WHERE user_id = ? FOR UPDATE | SELECT … FOR UPDATE 查询，走主表查询 |
| SELECT * FROM t_order WHERE user_id = ? | 查询包含 WHERE 条件，不包含 t_order 表分片键，只包含 t_order_user_id_idx 分片键，先查索引表，再回主表查询 |
| SELECT * FROM t_order WHERE ? = user_id AND user_id = ? | 查询包含 WHERE 条件，不包含 t_order 表分片键，只包含 t_order_user_id_idx 分片键，t_order_user_id_idx 分片键使用 AND 组合，先查索引表，再回主表查询 |
| SELECT * FROM t_order WHERE user_id IN (?, ?) | 查询包含 WHERE 条件，不包含 t_order 表分片键，只包含 t_order_user_id_idx 分片键，t_order_user_id_idx 分片键使用 IN 范围查询，先查索引表，再回主表查询 |
| SELECT * FROM t_order WHERE merchant_id = ? | 查询包含 WHERE 条件，不包含 t_order 表分片键，只包含 t_order_merchant_id_idx 分片键，先查索引表，再回主表查询 |
| SELECT * FROM t_order WHERE user_id = ? AND merchant_id = ? | 查询包含 WHERE 条件，不包含 t_order 表分片键，包含 t_order_user_id_idx 分片键和 t_order_merchant_id_idx 分片键，并使用 AND 组合，先查索引表，再回主表查询 |
| SELECT IFNULL(user_id, ?), order_id FROM t_order WHERE user_id = ? | 查询包含 WHERE 条件，user_id_idx 包含所有 WHERE 条件列以及所有投影列，但包含函数，先查索引表，再回主表查询 |
| SELECT user_id + 3, order_id FROM t_order WHERE user_id = ? | 查询包含 WHERE 条件，user_id_idx 包含所有 WHERE 条件列以及所有投影列，但包含表达式，先查索引表，再回主表查询 |
| SELECT user_id, order_id FROM t_order WHERE user_id = ? | 查询包含 WHERE 条件，user_id_idx 包含所有 WHERE 条件列以及所有投影列，索引表查询 |
| SELECT user_id, order_id FROM t_order WHERE user_id IN (?, ?) | 查询包含 WHERE 条件，user_id_idx 包含所有 WHERE 条件列以及所有投影列，使用 IN |
不支持的常用 SQL 列表 #
MySQL #
- DDL 语句——修改索引
| SQL 示例 | 说明 |
|---|---|
| ALTER TABLE t_order RENAME INDEX t_order_order_id_idx TO t_order_user_id_idx | 不支持使用 ALTER TABLE 指定 RENAME INDEX 修改全局索引名称 |
| ALTER TABLE t_order ADD INDEX | KEY t_order_order_id_idx(order_id, status), DROP INDEX | KEY t_order_user_id_idx | 不支持使用 ALTER TABLE 同时指定 ADD INDEX, DROP INDEX 修改全局索引 |
- DML 语句——INSERT
| SQL 示例 | 说明 |
|---|---|
| INSERT INTO t_order VALUES (?, ?, 1, ’test’, ‘2017-08-08’) ON DUPLICATE KEY UPDATE status = ‘OK’ | 不支持 INSERT … INTO … ON DUPLICATE KEY UPDATE … |
| INSERT INTO t_order SELECT order_id, user_id, status FROM t_order_item | 不支持 INSERT … INTO … SELECT … |
| INSERT INTO t_order VALUES (?, ?, (SELECT merchant_id FROM t_order LIMIT 1), ’test’, ‘2017-08-08’) | 不支持 INSERT … INTO … VALUES 中包含标量子查询 |
- DML 语句——DELETE
| SQL 示例 | 说明 |
|---|---|
| DELETE FROM t_order LIMIT 1 | 不支持 DELETE … LIMIT |
| DELETE t_order, t_order_item FROM t_order INNER JOIN t_order_item WHERE t_order.order_id=t_order_item.order_id | 不支持 DELETE 多表关联 |
| DELETE FROM t_order WHERE order_id IN (SELECT order_id FROM t_order_item) | 不支持 DELETE … WHERE … 中包含子查询 |
- DML 语句——UPDATE
| SQL 示例 | 说明 |
|---|---|
| UPDATE t_order SET user_id = ?, status = ?, merchant_id = ?, remark = ’test’, creation_date = ‘2019-08-08’ LIMIT 1 | 不支持 UPDATE … LIMIT |
| UPDATE t_order, t_order_item SET t_order.status = t_order_item.status WHERE t_order.order_id = t_order_item.order_id | 不支持 UPDATE 多表关联 |
| UPDATE t_order SET order_id = ?, user_id = ?, status = ?, merchant_id = ?, remark = ’test’, creation_date = ‘2019-08-08’ WHERE order_id IN (SELECT order_id FROM t_order_item) | 不支持 UPDATE … SET … WHERE … 中包含子查询 |
- DML 语句——REPLACE
| SQL 示例 | 说明 |
|---|---|
| REPLACE INTO t_order (order_id, user_id, status, merchant_id, creation_date) VALUES (?, ?, ?, 1, ‘2017-08-08’) | 不支持 REPLACE 语句 |
- DML 语句——SELECT
| SQL 示例 | 说明 |
|---|---|
| - | 全局索引 SELECT 不支持项和分片表相同，具体参考：https://docs.sphere-ex.com/sphereex-dbplussuite/master/zh/docs/plugin-guide/data-sharding/#%E4%BD%BF%E7%94%A8%E9%99%90%E5%88%B6 |