在 《“分库分表” ?选型和流程要慎重,否则会失控》 中,我们谈到处于驱动层的 sharding-jdbc
。开源做到这个水平,已经超棒了,不像 tddl
成了个太监。但还是有坑。
<select id="getCodes" resultMap="BaseResultMap" parameterType="java.util.Map"> <foreach collection="orderCodes" index="index" item="item" open="" separator="union all" close=""> select <include refid="Base_Column_List"/> from order where orderCode = #{item} </foreach></select>
分库分表后,就成为了一个阉割型的数据库。很多sql的特性是不支持的,需要使用其他手段改进。以下以3.0.0版本进行描述。
sharding-jdbc不支持 distinct
,单表可使用 group by
进行替代。多表联查可使用exists替代
select DISTINCT a, b, c, d from table where df=0
改成
select a, b, c, d from table where df=0 group by a, b, c, d
sharding-jdbc不支持having,可使用嵌套子查询进行替代
sharding-jdbc不支持union(all),可拆分成多个查询,在程序拼接
sharding-jdbc不支持在子查询中出现同样的表,如
以下可以⇒
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 = ?))
由于归并的限制,子查询中包含聚合函数目前无法支持。
sharding-jdbc不支持sql中的 <!-- – >
注释,如必须使用则写在sql前,或使用 /* */
改为 varchar
,好几年的bug了,但是没改
某些case when是不支持的,比如不在聚合函数中的case when,需要将这部分sql逻辑写到程序里。
case when不应该是DBA禁用的函数么?我们在填坑
这个是可以的
select a-b from dual
但这个不可以…
select (a-b)c from dual
sharding 也不支持如下形式查询,解析紊乱
and (1=1 or 1=1)
严禁无切分键的深分页!因为会对SQL进行以下解释,然后在内存运行。
select * from a limit 10 offset 1000
=====⇒
Actual SQL:db0 ::: select * from a limit 1010 offset 0
表名需与 sharding-jdbc
配置一致,推荐均为小写。因为路由是放在hashmap里的,没有区分大小写…所以如果你的sql写错了会找不到。
每一张表都要配置路由信息才能够被正确解析,如果你库里的表太多,这个配置文件会膨胀的特别大,上千行也是有的。所以在 yml
中可以将配置文件分开。
spring.profiles.include: sharding
比如一些定时任务,需要遍历所有库。
使用以下方式拿到真正的数据库列表
Map<String, DataSource> map = ShardingDataSource.class.cast(dataSource).getDataSourceMap();
然后在每一个库上执行扫描逻辑。这种情况下无法使用mybaits,需要写原生jdbc
此种方法会拿到一个切分键的列表,比如日期等。然后通过遍历这个列表执行业务逻辑。此种方法在列表特别大的时候执行会比较缓慢。
分库分表很危险,因为一旦数据入错库,后续的修理很麻烦。所以刚开始可以将路由信息指向到源表,即:只验证SQL路由的准确性。等待所有的SQL路由都验证通过,再切换到真正的分库或者表。
确保能够打印SQL
sharding.jdbc.config.sharding.props.sql.show: true
将sql打印到单独的文件(logback)
<appender name="SQL" class="ch.qos.logback.core.rolling.RollingFileAppender"> <file>${LOG_HOME}/sharding.log</file> <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy"> <fileNamePattern>${LOG_HOME}/backup/sharding.log.%d{yyyy-MM-dd} </fileNamePattern> <maxHistory>100</maxHistory> </rollingPolicy> <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder"> <pattern>${ENCODER_PATTERN}</pattern> </encoder> </appender>
写一些脚本进行SQL文件的验证。我这里有个通用的,你可以改下你的逻辑。
import sys import re import getopt def process(SQL): one= "".join(line.strip().replace("/n", " ") for line in SQL) place = [m.groups()[0] if m.groups()[0] else m.groups()[1] for m in re.finditer(r"[ ]+(/w+)[ ]*=[ ]*/?|(/?)", one)] if len(place): mat = re.search(r"::: /[/[(.*)/]/]", one) if mat is not None: vals = [str(i).strip() for i in str(mat.groups()[0]).split(',')] if "splitKey" in place: for i in range(len(place)): part = place[i] //这里写你的逻辑 else: print("no splitKey", one) SQL = [] def process_line(line): global SQL if "Actual SQL" in line: SQL = [] SQL.append(line) else: if line.strip().endswith("]]"): SQL.append(line) process(SQL) SQL = [] else: SQL.append(line) opts, args = getopt.getopt(sys.argv[1:], "bf") for op, value in opts: if op == "-b": print("enter comman mode , such as 'python x.py -b sharding.log > result'") with open(args[0], "rb") as f: for line in f: process_line(line) elif op== "-f": print("enter stream scroll mode , such as 'python x.py -f sharding.log '") with open(args[0], "rb") as f: f.seek(0,2) while True: last_pos = f.tell() line = f.readline() if line: process_line(line)
你可能要经常切换路由,所以某些时候路由信息要放在云端能够动态修改。
哦对了,我这里还有一段开发阶段的验证代码,能让你快速验证SQL能否正确解析。
@RunWith(SpringRunner.class) @SpringBootTest(classes = App.class) public class ShardingTest { @Autowired DataSource dataSource; @Test public void testGet() { try { Connection conn = dataSource.getConnection(); PreparedStatement stmt; ResultSet rs; String sql = new String(Files.readAllBytes(Paths.get("/tmp/a.sql"))); stmt = conn.prepareStatement(sql); rs = stmt.executeQuery(); printRS(rs); } catch (Exception ex) { ex.printStackTrace(); } } public static void printRS(ResultSet rs) throws Exception { ResultSetMetaData rsmd = rs.getMetaData(); int columnsNumber = rsmd.getColumnCount(); while (rs.next()) { for (int i = 1; i <= columnsNumber; i++) { if (i > 1) System.out.print(", "); String columnValue = rs.getString(i); System.out.print(columnValue + " " + rsmd.getColumnName(i)); } System.out.println(""); } } }
有SQL规范的团队是幸福的,分库分表简单的很。而动辄几百行,有各种复杂函数的SQL,就只能一步一个坑了。
话说回来,如果不是为了事务这个特性,为了支持老掉牙的业务,谁会用这分完后人不像人,鬼不像鬼的东西。