非规范SQL的sharding-jdbc实践

2019-09-24 16:41:25 浏览数 (1)

在《“分库分表” ？选型和流程要慎重，否则会失控》中，我们谈到处于驱动层的sharding-jdbc。开源做到这个水平，已经超棒了，不像tddl成了个太监。但还是有坑。

不过不能怪框架，毕竟有些sql，只有程序和鬼能懂。

代码语言：javascript复制
<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版本进行描述。

distinct

sharding-jdbc不支持distinct，单表可使用group by进行替代。多表联查可使用exists替代

代码语言：javascript复制
select DISTINCT
       a, b, c, d        from  table
       where df=0

改成

代码语言：javascript复制
select a, b, c, d        from  table
       where df=0
       group by a, b, c, d

having

sharding-jdbc不支持having，可使用嵌套子查询进行替代

union

sharding-jdbc不支持union（all），可拆分成多个查询，在程序拼接

关于子查询

sharding-jdbc不支持在子查询中出现同样的表，如 以下可以⇒

代码语言：javascript复制
SELECT COUNT(*) FROM (SELECT * FROM t_order o)

以下报错⇒

代码语言：javascript复制
SELECT COUNT(*) FROM (SELECT * FROM t_order o WHERE o.id IN (SELECT id FROM t_order WHERE status = ?))

由于归并的限制，子查询中包含聚合函数目前无法支持。

mybatis 注释

sharding-jdbc不支持sql中的<!-- – >注释，如必须使用则写在sql前，或使用/* */

不支持text字段

改为varchar，好几年的bug了，但是没改

case when

某些case when是不支持的，比如不在聚合函数中的case when，需要将这部分sql逻辑写到程序里。

case when不应该是DBA禁用的函数么？我们在填坑

一些奇怪的反应

这个是可以的

代码语言：javascript复制
select  a-b from dual

但这个不可以…

代码语言：javascript复制
select (a-b)c from dual

sharding 也不支持如下形式查询，解析紊乱

代码语言：javascript复制
and (1=1 or 1=1)

关于分页

严禁无切分键的深分页！因为会对SQL进行以下解释，然后在内存运行。

代码语言：javascript复制
select *  from a limit 10 offset 1000

=====⇒

代码语言：javascript复制
Actual SQL:db0 ::: select *  from a limit 1010 offset 0

关于表名

表名需与sharding-jdbc配置一致，推荐均为小写。因为路由是放在hashmap里的，没有区分大小写…所以如果你的sql写错了会找不到。

配置冗余

每一张表都要配置路由信息才能够被正确解析，如果你库里的表太多，这个配置文件会膨胀的特别大，上千行也是有的。所以在yml中可以将配置文件分开。

代码语言：javascript复制
spring.profiles.include: sharding

如何扫多库

比如一些定时任务，需要遍历所有库。

方法1：遍历所有库

使用以下方式拿到真正的数据库列表

代码语言：javascript复制
Map<String, DataSource> map = ShardingDataSource.class.cast(dataSource).getDataSourceMap();

然后在每一个库上执行扫描逻辑。这种情况下无法使用mybaits，需要写原生jdbc

方法2：根据切分键遍历

此种方法会拿到一个切分键的列表，比如日期等。然后通过遍历这个列表执行业务逻辑。此种方法在列表特别大的时候执行会比较缓慢。

如何验证

分库分表很危险，因为一旦数据入错库，后续的修理很麻烦。所以刚开始可以将路由信息指向到源表，即：只验证SQL路由的准确性。等待所有的SQL路由都验证通过，再切换到真正的分库或者表。

确保能够打印SQL

代码语言：javascript复制
sharding.jdbc.config.sharding.props.sql.show: true

将sql打印到单独的文件(logback)

代码语言：javascript复制
<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文件的验证。我这里有个通用的，你可以改下你的逻辑。

代码语言：javascript复制
import sys
import re
import getoptdef 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能否正确解析。

代码语言：javascript复制
@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，就只能一步一个坑了。

话说回来，如果不是为了事务这个特性，为了支持老掉牙的业务，谁会用这分完后人不像人，鬼不像鬼的东西。

jdbc sql 编程算法

0 人点赞