非規範SQL的sharding-jdbc實踐

小姐姐味道發表於2018-12-19
SQLJDBC

在《“分庫分表” ？選型和流程要慎重，否則會失控》中，我們談到處於驅動層的sharding-jdbc。開源做到這個水平，已經超棒了，不像tddl成了個太監。但還是有坑。

不過不能怪框架，畢竟有些sql，只有程式和鬼能懂。

<
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替代

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複製程式碼

having

sharding-jdbc不支援having，可使用巢狀子查詢進行替代

union

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 = ?))複製程式碼

由於歸併的限制，子查詢中包含聚合函式目前無法支援。

mybatis 註釋

sharding-jdbc不支援sql中的<
!-- – >
註釋，如必須使用則寫在sql前，或使用/* */

不支援text欄位

改為varchar，好幾年的bug了，但是沒改

case when

某些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複製程式碼

如何掃多庫

比如一些定時任務，需要遍歷所有庫。

方法1：遍歷所有庫

使用以下方式拿到真正的資料庫列表

Map<
String, DataSource>
 map = ShardingDataSource.class.cast(dataSource).getDataSourceMap();
複製程式碼

然後在每一個庫上執行掃描邏輯。這種情況下無法使用mybaits，需要寫原生jdbc

方法2：根據切分鍵遍歷

此種方法會拿到一個切分鍵的列表，比如日期等。然後通過遍歷這個列表執行業務邏輯。此種方法在列表特別大的時候執行會比較緩慢。

如何驗證

分庫分表很危險，因為一旦資料入錯庫，後續的修理很麻煩。所以剛開始可以將路由資訊指向到源表，即：只驗證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 sysimport reimport 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能否正確解析。

@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，就只能一步一個坑了。

話說回來，如果不是為了事務這個特性，為了支援老掉牙的業務，誰會用這分完後人不像人，鬼不像鬼的東西。

非規範SQL的sharding-jdbc實踐

來源：https://juejin.im/post/5c19e9e75188257f9242a88d

相關文章