非規範SQL的sharding-jdbc實踐
- 2019 年 10 月 6 日
- 筆記
在《「分庫分表」 ？選型和流程要慎重，否則會失控》中，我們談到處於驅動層的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 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，就只能一步一個坑了。
話說回來，如果不是為了事務這個特性，為了支援老掉牙的業務，誰會用這分完後人不像人，鬼不像鬼的東西。
