关于数据库:ShardingSphere-4x-数据分片使用规范之SQL

背景

尽管ShardingSphere心愿可能齐全兼容所有的SQL以及单机数据库，但分布式为数据库带来了更加简单的场景。ShardingSphere心愿可能优先解决海量数据OLTP的问题，OLAP的相干反对，会一点一点的逐步欠缺。

请进一步浏览ShardingSphere所反对和不反对的SQL类型以及针对分页这类性能相关度很高的问题。

因为SQL语法灵便简单，分布式数据库和单机数据库的查问场景又不完全相同，不免有和单机数据库不兼容的SQL呈现。

本文具体列举出已明确可反对的SQL品种以及已明确不反对的SQL品种，尽量让使用者防止踩坑。

其中必然有未波及到的SQL欢送补充，未反对的SQL也尽量会在将来的版本中反对。

反对项

路由至单数据节点

100%全兼容（目前仅MySQL，其余数据库欠缺中）。

路由至少数据节点

全面反对DML、DDL、DCL、TCL和局部DAL。反对分页、去重、排序、分组、聚合、关联查问（不反对跨库关联）。以下用最为简单的DML举例：

SELECT主语句

SELECT select_expr [, select_expr ...] FROM table_reference [, table_reference ...][WHERE predicates][GROUP BY {col_name | position} [ASC | DESC], ...][ORDER BY {col_name | position} [ASC | DESC], ...][LIMIT {[offset,] row_count | row_count OFFSET offset}]

select_expr

* | [DISTINCT] COLUMN_NAME [AS] [alias] | (MAX | MIN | SUM | AVG)(COLUMN_NAME | alias) [AS] [alias] | COUNT(* | COLUMN_NAME | alias) [AS] [alias]

table_reference

tbl_name [AS] alias] [index_hint_list]| table_reference ([INNER] | {LEFT|RIGHT} [OUTER]) JOIN table_factor [JOIN ON conditional_expr | USING (column_list)]

不反对项

路由至少数据节点

不反对CASE WHEN、HAVING、UNION (ALL)，无限反对子查问。

除了分页子查问的反对之外(详情请参考分页)，也反对等同模式的子查问。无论嵌套多少层，ShardingSphere都能够解析至第一个蕴含数据表的子查问，一旦在上层嵌套中再次找到蕴含数据表的子查问将间接抛出解析异样。

例如，以下子查问能够反对：

SELECT COUNT(*) FROM (SELECT * FROM t_order o)

以下子查问不反对：

SELECT COUNT(*) FROM (SELECT * FROM t_order o WHERE o.id IN (SELECT id FROM t_order WHERE status = ?))

简略来说，通过子查问进行非性能需要，在大部分状况下是能够反对的。比方分页、统计总数等；而通过子查问实现业务查问以后并不能反对。

因为归并的限度，子查问中蕴含聚合函数目前无奈反对。

不反对蕴含schema的SQL。因为ShardingSphere的理念是像应用一个数据源一样应用多数据源，因而对SQL的拜访都是在同一个逻辑schema之上。

对分片键进行操作

运算表达式和函数中的分片键会导致全路由。

假如create_time为分片键，则无奈准确路由形如SQL：

SELECT * FROM t_order WHERE to_date(create_time, 'yyyy-mm-dd') = '2019-01-01';

因为ShardingSphere只能通过SQL字面提取用于分片的值，因而当分片键处于运算表达式或函数中时，ShardingSphere无奈提前获取分片键位于数据库中的值，从而无奈计算出真正的分片值。

当呈现此类分片键处于运算表达式或函数中的SQL时，ShardingSphere将采纳全路由的模式获取后果。

示例

反对的SQL

SQL	必要条件
SELECT * FROM tbl_name
SELECT * FROM tbl_name WHERE (col1 = ? or col2 = ?) and col3 = ?
SELECT * FROM tbl_name WHERE col1 = ? ORDER BY col2 DESC LIMIT ?
SELECT COUNT(*), SUM(col1), MIN(col1), MAX(col1), AVG(col1) FROM tbl_name WHERE col1 = ?
SELECT COUNT(col1) FROM tbl_name WHERE col2 = ? GROUP BY col1 ORDER BY col3 DESC LIMIT ?, ?
INSERT INTO tbl_name (col1, col2,...) VALUES (?, ?, ....)
INSERT INTO tbl_name VALUES (?, ?,....)
INSERT INTO tbl_name (col1, col2, ...) VALUES (?, ?, ....), (?, ?, ....)
UPDATE tbl_name SET col1 = ? WHERE col2 = ?
DELETE FROM tbl_name WHERE col1 = ?
CREATE TABLE tbl_name (col1 int, ...)
ALTER TABLE tbl_name ADD col1 varchar(10)
DROP TABLE tbl_name
TRUNCATE TABLE tbl_name
CREATE INDEX idx_name ON tbl_name
DROP INDEX idx_name ON tbl_name
DROP INDEX idx_name
SELECT DISTINCT * FROM tbl_name WHERE col1 = ?
SELECT COUNT(DISTINCT col1) FROM tbl_name

不反对的SQL

SQL	不反对起因
INSERT INTO tbl_name (col1, col2, ...) VALUES(1+2, ?, ...)	VALUES语句不反对运算表达式
INSERT INTO tbl_name (col1, col2, ...) SELECT col1, col2, ... FROM tbl_name WHERE col3 = ?	INSERT .. SELECT
SELECT COUNT(col1) as count_alias FROM tbl_name GROUP BY col1 HAVING count_alias > ?	HAVING
SELECT FROM tbl_name1 UNION SELECT FROM tbl_name2	UNION
SELECT FROM tbl_name1 UNION ALL SELECT FROM tbl_name2	UNION ALL
SELECT * FROM ds.tbl_name1	蕴含schema
SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name	详见DISTINCT反对状况具体阐明
SELECT * FROM tbl_name WHERE to_date(create_time, 'yyyy-mm-dd') = ?	会导致全路由

DISTINCT反对状况具体阐明

反对的SQL

SQL
SELECT DISTINCT * FROM tbl_name WHERE col1 = ?
SELECT DISTINCT col1 FROM tbl_name
SELECT DISTINCT col1, col2, col3 FROM tbl_name
SELECT DISTINCT col1 FROM tbl_name ORDER BY col1
SELECT DISTINCT col1 FROM tbl_name ORDER BY col2
SELECT DISTINCT(col1) FROM tbl_name
SELECT AVG(DISTINCT col1) FROM tbl_name
SELECT SUM(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1
SELECT COUNT(DISTINCT col1 + col2) FROM tbl_name
SELECT COUNT(DISTINCT col1), SUM(DISTINCT col1) FROM tbl_name
SELECT COUNT(DISTINCT col1), col1 FROM tbl_name GROUP BY col1
SELECT col1, COUNT(DISTINCT col1) FROM tbl_name GROUP BY col1

不反对的SQL

SQL	不反对起因
SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name	同时应用一般聚合函数和DISTINCT聚合函数