非規範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 sys
import re
import getopt

def process(SQL):
    one= "".join(line.strip().replace("
", " ") 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實踐

相關文章