sharding-jdbc总结与sql支持说明
目录
总结
重点知识
为什么分库分表？分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提 升数据库性能的目的。
分库分表方式：垂直分表、垂直分库、水平分库、水平分表
分库分表带来问题：由于数据分散在多个数据库，服务器导致了事务一致性问题、跨节点join问题、跨节点分页、 排序、函数，主键需要全局唯一，公共表。
Sharding-JDBC基础概念：逻辑表，真实表，数据节点，绑定表，广播表，分片键，分片算法，分片策略，主键生 成策略
Sharding-JDBC核心功能：数据分片，读写分离
Sharding-JDBC执行流程： SQL 解析 => 查询优化 => SQL路由 => SQL改写 => SQL执行 => 结果归并
最佳实践
系统在设计之初就应该对业务数据的耦合松紧进行考量，从而进行垂直分库、垂直分表（冷热数据分离），使数据层架构清晰明了。 若非必要，无需进行水平切分，应先从缓存技术着手降低对数据库的访问压力。如果缓存使用过后，数据库访问量 还是非常大，可以考虑数据库读、写分离原则。若当前数据库压力依然大，且业务数据持续增长无法估量，最后可考虑水平分库、分表，单表拆分数据控制在1000万以内。
sql支持说明
https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/sql/
支持的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 (?, ?, ….), (?, ?, ….) | |
| INSERT INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = ? | INSERT表和SELECT表必须为相同表或绑定表 |
| REPLACE INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = ? | REPLACE表和SELECT表必须为相同表或绑定表 |
| 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 | |
| SELECT subquery_alias.col1 FROM (select tbl_name.col1 from tbl_name where tbl_name.col2=?) subquery_alias |
不支持的SQL
| SQL | 不支持原因 |
|---|---|
| INSERT INTO tbl_name (col1, col2, …) VALUES(1+2, ?, …) | VALUES语句不支持运算表达式 |
| INSERT INTO tbl_name (col1, col2, …) SELECT * FROM tbl_name WHERE col3 = ? | SELECT子句暂不支持使用*号简写及内置的分布式主键生成器 |
| REPLACE INTO tbl_name (col1, col2, …) SELECT * FROM tbl_name WHERE col3 = ? | SELECT子句暂不支持使用*号简写及内置的分布式主键生成器 |
| SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2 | UNION |
| SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2 | UNION ALL |
| SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name | 详见DISTINCT支持情况详细说明 |
| SELECT * FROM tbl_name WHERE to_date(create_time, ‘yyyy-mm-dd’) = ? | 会导致全路由 |
| (SELECT * FROM tbl_name) | 暂不支持加括号的查询 |
| SELECT MAX(tbl_name.col1) FROM tbl_name | 查询列是函数表达式时,查询列前不能使用表名;若查询表存在别名,则可使用表的别名 |
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 tbl_name.col1), SUM(tbl_name.col1) FROM tbl_name | 查询列是函数表达式时,查询列前不能使用表名;若查询表存在别名,则可使用表的别名 |