Appearance
12 开发者须知
12.1 SQL编写原则
12.1.1 正确认识LIBRA
LIBRA的核心理念是将大数据量的表水平拆分为多个数据量比较小的分片，并存储在不同的分库中，这样不仅降低了单库的数据量，同时也增加了实际的数据库运行资源。所以，它更擅长直接命中分片的SQL操作（实际业务系统中这类SQL操作也占绝大多数）；如果没有命中分片，甚至还要跨分片进行复杂的运算才可以得到结果的操作，这种情况下一方面网络的消耗会增加，同时复杂的运算也会比较耗时，尤其在数据量很大的情况下会更为明显，所在这种操作则可以算作是其的短板。所以，开发人员在使用LIBRA的时候，一个大的原则就是扬长避短。
12.1.2 如何编写执行效率比较高的SQL
有如下一些原则可供参考：
- SQL语句尽可能带拆分字段。
- 拆分字段的查询条件尽可能是等值条件。
- 如果拆分字段的条件是IN子句，则IN后面的值的数目应尽可能少。特别注意，随着业务增长，某些IN子句的条件会随之增长。
- 如果SQL语句不带拆分字段，那么DISTINCT、GROUP BY和ORDER BY在同一个SQL语句中尽量只出现一种。
- 数据查询时，应该尽量减少节点返回的结果数量，这样能够使消耗的网络带宽最小，使查询性能能够达到最优状态。
- 更新和删除操作的WHERE条件中，尽可能带拆分字段，否则将会到所有分片中执行，同时还涉及分布式事务。
- 尽可能规避非常复杂的SQL，一般复杂的SQL都会涉及到跨库及LIBRA内部复杂的计算。
12.1.3 跨分片查询的应对策略
跨分片查询通常发生在下列语句中：
- 单表查询语句中不包含分片字段；
- 涉及多个分片的JOIN；
- 涉及多个分片的聚合函数；
- 复杂子查询。
这些语句可以通过以下策略来优化：
- 合理调整查询语句，使查询条件中包含拆分字段的等值条件，这样就能够将语句下推到单一分片中执行。
- 参与JOIN的表配置相同的拆分规则，查询时将拆分字段作为JOIN的关联条件，这样JOIN操作就可以在一个分片内完成，通常配置为ER关系的两个表的JOIN采用这种方式。
- 将参与JOIN的表中数据量较小的表配置成全局表，通过数据冗余避免跨节点。这种情况会转换为在一个分片内的JOIN。
- 如果一定需要跨分片JOIN，尽量对驱动表添加更多的过滤条件，从而使参与跨分片JOIN的数据量尽可能的少。
- 如果查询涉及到了多个分片，尽量避免翻页操作。
- GROUP子句尽量包含拆分字段。
- 对语句进行改写，将复杂子查询分解为多条语句来执行。
- 子查询尽可能改写成JOIN的形式。
12.2 连接LIBRA示例
12.2.1 基于Mybatis连接
利用Mybatis连接LIBRA时，连接方式与连接MySQL相同。下面是一个简单示例仅供参考。
- mybatis-config-db.properties
properties
url=jdbc:mysql://127.0.0.1:8066/TESTDB
drivername=com.mysql.jdbc.Driver
username=root
password=123456- mybatis-config.xml
XML
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${drivername}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 操作数据库表中的数据的映射文件 -->
<mapper resource="clientInfo.xml"/>
</mappers>
</configuration>- clientInfo.xml
XML
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mybatis.clientInfo">
<select id="select" parameterType="java.util.Map" resultType="java.util.Map">
select acct_no,acct_name from acct where acct_no=#{acct_no,jdbcType=VARCHAR}
</select>
<insert id="insert" parameterType="java.util.Map">
insert into acct(acct_no,acct_name)values(#{acct_no,jdbcType=VARCHAR},#{acct_name,jdbcType=VARCHAR})
</insert>
</mapper>- 实现
JAVA
/**
* com.dcits qiqsa
*/
public class JdbcDemo {
private static final String NAMESPACE = "mybatis.clientInfo";
public static void main(String[] args) {
JdbcDemo demo = new JdbcDemo();
demo.execute();
}
public void execute() {
CountDownLatch latch = new CountDownLatch(1);
long start = System.currentTimeMillis();
for (int i = 0; i < 5; i++) {
TestThread testThread = new TestThread(latch);
Thread th = new Thread(testThread);
th.start();
}
try {
latch.await();
long end = System.currentTimeMillis();
System.out.println("total times : " + (end - start));
} catch (InterruptedException e) {
e.printStackTrace();
}
}
class TestThread implements Runnable {
private CountDownLatch latch;
public TestThread(CountDownLatch cdl) {
this.latch = cdl;
}
@Override
public void run() {
SqlSession sqlSession = SqlSessionTemplate.build();
try {
Map param = new HashMap();
param.put("acct_no", 1001);
param.put("acct_name", "zhangsan");
sqlSession.insert(NAMESPACE + ".insert", param);
List<Map<String, Object>> list = sqlSession.selectList(NAMESPACE + ".select", param);
for (Map<String, Object> map : list) {
for (Map.Entry<String, Object> entry : map.entrySet()) {
System.out.println(entry.getKey() + " -----> " + entry.getValue());
}
}
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (sqlSession != null) {
sqlSession.close();
}
this.latch.countDown();
}
}
}
}
/**
* @Authoor dcits.com qiqsa
*/
public class SqlSessionTemplate {
private static final Logger LOGGER = Logger.getLogger("SqlSessionTemplate");
private static final String BD_PATH = "/mybatis-config-db.properties";
private static final String RESOURCE = "mybatis-config.xml";
public static SqlSession build(){
InputStream stream = SqlSessionTemplate.class.getResourceAsStream(BD_PATH);
Properties pts = new Properties();
SqlSession sqlSession = null;
try {
pts.load(stream);
InputStream inputStream = Resources.getResourceAsStream(RESOURCE);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream,pts);
sqlSession = factory.openSession();
} catch (IOException e) {
LOGGER.info("load db properties fail !"+e.getMessage());
}
return sqlSession;
}
}12.2.1 基于jdbc连接
利用JDBC连接LIBRA时，连接方式与连接MySQL相同。下面是一个简单示例，具体实现可根据场景考虑，本示例仅仅作为Demo参考
JAVA
public class SingleMixEngine {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Properties props = new Properties();
props.setProperty("user", "root");
props.setProperty("password", "123456");
SingleMixEngine engine = new SingleMixEngine();
engine.execute(props,"jdbc:mysql://192.168.58.51:8066/testdb");
}
final AtomicLong tmAl = new AtomicLong();
final String tableName="news_table";
public void execute(Properties props,String url) {
CountDownLatch cdl = new CountDownLatch(1);
long start = System.currentTimeMillis();
for (int i = 0; i < 1; i++) {
TestThread insertThread = new TestThread(props,cdl, url);
Thread t = new Thread(insertThread);
t.start();
System.out.println("Test start");
}
try {
cdl.await();
long end = System.currentTimeMillis();
System.out.println("Test end,total cost:" + (end-start) + "ms");
} catch (Exception e) {
}
}
class TestThread implements Runnable {
Properties props;
private CountDownLatch countDownLatch;
String url;
public TestThread(Properties props,CountDownLatch cdl,String url) {
this.props = props;
this.countDownLatch = cdl;
this.url = url;
}
public void run() {
Connection connection = null;
PreparedStatement ps = null;
Statement st = null;
try {
connection = DriverManager.getConnection(url,props);
connection.setAutoCommit(true);
st = connection.createStatement();
String dropSql = "drop table if exists " + tableName;
System.out.println("Execute SQL:\n\t"+dropSql);
st.execute(dropSql);
String createSql = "create table " + tableName + "(id number,title varchar2(20),content varchar2(50))";
System.out.println("Execute SQL:\n\t"+createSql);
st.execute(createSql);
String insertSql = "insert into " + tableName + " (id,title,content) values(?,?,?)";
System.out.println("Prepared SQL:\n\t"+insertSql);
ps = connection.prepareStatement(insertSql);
for (int i = 1; i <= 3; i++) {
ps.setInt(1,i);
ps.setString(2, "测试"+i);
ps.setString(3, "这是第"+i+"条测试数据");
ps.execute();
System.out.println("Insert data:\t"+i+","+"测试"+i+","+"这是第"+i+"条测试数据");
}
String querySQL = "select * from " + tableName + " order by id";
System.out.println("Execute SQL:\n\t"+querySQL);
ResultSet rs = st.executeQuery(querySQL);
int colcount = rs.getMetaData().getColumnCount();
System.out.println("Current Data:");
while(rs.next()){
for(int i=1;i<=colcount;i++){
System.out.print("\t"+rs.getString(i));
}
System.out.println();
}
String updateSql = "update " + tableName + " set title='test1' where id=1";
System.out.println("Execute SQL:\n\t"+updateSql);
st.execute(updateSql);
rs = st.executeQuery(querySQL);
System.out.println("Current Data:");
while(rs.next()){
for(int i=1;i<=colcount;i++){
System.out.print("\t"+rs.getString(i));
}
System.out.println();
}
String deleteSql = "delete from " + tableName + " where id=2";
System.out.println("Execute SQL:\n\t"+deleteSql);
st.execute(deleteSql);
rs = st.executeQuery(querySQL);
System.out.println("Current Data:");
while(rs.next()){
for(int i=1;i<=colcount;i++){
System.out.print("\t"+rs.getString(i));
}
System.out.println();
}
} catch (Exception e) {
System.out.println(new java.util.Date().toString());
e.printStackTrace();
} finally {
if (ps != null)
try {
ps.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
if (connection != null)
try {
connection.close();
} catch (SQLException e1) {
e1.printStackTrace();
}
this.countDownLatch.countDown();
}
}
}
}