阿里云DRDS使用点滴

2016年的时候使用阿里云DRDS服务的总结记录，当时的数据量比较大，如果不分库分表，就会导致单张车辆表超过1亿，未来还会持续增加，所以在规划系统的时候就考虑到了如果上亿，MySQL根本没法支撑，速度会慢的要死，这让我们在设计的时候就要考虑解决的方案。

我们用的数据库已经是云服务商提供的MySQL实例了，所以直接购买了它的DRDS服务，省却了自己用mycat等中间件去做分库分表，当然了现在阿里云上的DRDS产品已经没有了，取而代之的是它的PolarDB-X产品，换汤不换药，还贴了个云原生分布式数据库的标签，也是有点醉了。

另外一个细节是车辆的字段很多，用户需要支持多条件查询，而且查询的条件互相组合没有规律，这就导致不好直接通过在数据库层面使用索引来解决查询问题，所以当时准备分2步走来解决：

1. 上分库分表
2. 条件查询时，不用数据库查询，而是通过阿里云的opensearch服务，opensearch服务基于luceue实现的，和elastic search很类似。数据如何从MySQL同步到OpenSearch里面去，这个不需要我们在业务层面实现，而是通过监控MySQL的binlog，然后自动同步到OpenSearch里面去，这是一个通用的底层解决方案，不需要业务层做任何处理。

看看这张数据库表，字段超多，sql如下

/*------- CREATE SQL---------*/
CREATE TABLE `core_car` (
    `id` varchar(50)  NOT NULL COMMENT 'UUID，主键',
    `brandname` varchar(64)  NULL COMMENT '品牌名称，比如宝马',
    `seriesname` varchar(50)  NULL COMMENT '系列名称，比如5系、3系',
    `modelName` varchar(50)  NULL,
    `modelDescr` varchar(300)  NULL COMMENT '车辆的配置型号描述，比如：荣威550 1.8L 手动档 2010款 启逸版 5座  市场参考价：121800元、2014款 朗行 1.6 手自一体 运动版',
    `img` varchar(2000)  NULL COMMENT '车的图片路径，是文件系统路径',
    `vehicleNumber` varchar(10)  NOT NULL COMMENT '车牌号，全局唯一',
    `vin` varchar(17)  NULL COMMENT '车架号',
    `engineNo` varchar(30)  NULL COMMENT '发动机号',
    `exhaustScale` float(9,3)  NULL DEFAULT 0.000 COMMENT '汽车排量，单位升，比如：1.6、1.8、2.0、2.4、3.0',
    `seatCount` tinyint UNSIGNED NULL DEFAULT 5 COMMENT '座位数',
    `modelCode` varchar(20)  NULL COMMENT '车型代码，比如：RWAABD0029',
    `enrollDate` date NULL COMMENT '车辆在车管所的上牌日期，精确到日，指行驶证的初始登记日期',
    `makeDate` date NULL COMMENT '转移登记日期，格式：年月日，YYYY-MM-DD，如果车辆没有过户过，这个字段是空的',
    `startDate` date NULL COMMENT '车险起保日期',
    `purchasePrice` float(11,3)  UNSIGNED NULL DEFAULT 0.000 COMMENT '新车市场参考价',
    `guohu` tinyint NULL DEFAULT 0 COMMENT '上年是否是过户车辆',
    `owner` varchar(50)  NULL COMMENT '车主姓名',
    `ownerId` varchar(25)  NULL COMMENT '车主身份证',
    `certType` smallint NULL DEFAULT 1,
    `carLevel` tinyint NULL DEFAULT 1 COMMENT '车主的汽车级别',
    `cityCode` smallint UNSIGNED NOT NULL DEFAULT 0 COMMENT '车辆所在城市的城市编码',
    `ecdemicVehicleFlag` tinyint NULL COMMENT '外地车标志，1是，0否',
    `vehicleModelId` int UNSIGNED NOT NULL DEFAULT 0,
    `createDate` datetime NULL COMMENT '添加车辆的时间',
    `lastYearEndDate` datetime NULL COMMENT '上年商业险终保日期',
    `lastYearCIEndDate` datetime NULL COMMENT '上年交强险终保日期',
    `lastYearICName` varchar(32)  NULL DEFAULT 'YYYY' COMMENT '上年度投保保险公司名称，比如：中国人寿、太保等等',
    `phone` varchar(20)  NULL COMMENT '联系电话',
    `merchantCode` int NULL DEFAULT 0 COMMENT '商户id',
    `companyCar` tinyint NULL DEFAULT 0 COMMENT '是否是公司车辆',
    `comments` varchar(2000)  NULL COMMENT '车辆备注',
    `attachmentFlag` tinyint NULL DEFAULT 0 COMMENT '是否有附件',
    `accidents` int NULL DEFAULT 0 COMMENT '上年商业险出险次数',
    `vehicleWeight` float(9,4)  NULL DEFAULT 0.0000 COMMENT '整备质量，单位吨',
    `licenseKindCode` varchar(4)  NULL COMMENT '号牌种类',
    `oriCarKindName` varchar(4)  NULL COMMENT '交管车辆类型',
    `fuelType` varchar(4)  NULL COMMENT '燃油种类',
    `useNature` varchar(4)  NULL COMMENT '使用性质，如85表示家庭自用，83表示非营业企业用车',
    `carKindCode` varchar(4)  NULL COMMENT '车辆种类代码，如A0表示客车，H0表示货车',
    `platVehicleCode` varchar(100)  NULL COMMENT '行业车型编码',
    `platPublicMode` varchar(100)  NULL COMMENT '公告型号',
    `platModelName` varchar(100)  NULL COMMENT '车款名称',
    `subMerchantId` varchar(32)  NULL DEFAULT '-1' COMMENT '子帐号ID',
    `taskRemind` tinyint NOT NULL DEFAULT 0 COMMENT '是否跟单',
    `address` varchar(100)  NULL COMMENT '车主地址，配送时使用',
    `appointmentDate` datetime NULL COMMENT '预约时间',
    `communicateFlag` tinyint NULL DEFAULT 0 ,
    `foursId` varchar(32)  NULL COMMENT '车辆所属4S店ID',
    `cpicLevel` smallint NOT NULL DEFAULT 0 COMMENT '太保车辆分类标记',
    `region` varchar(16)  NULL COMMENT '区域',
    `sex` tinyint NOT NULL DEFAULT 0 COMMENT '性别',
    `ownerAge` smallint NOT NULL DEFAULT 0 COMMENT '车主年龄',
    PRIMARY KEY (`id`),
    KEY `auto_shard_key_merchantCode`(`merchantCode`) USING BTREE,
    KEY `core_car_idx1`(`merchantCode`,`lastYearEndDate`) USING BTREE,
    KEY `core_car_idx2`(`merchantCode`,`vehicleNumber`) USING BTREE,
    KEY `core_car_idx3`(`merchantCode`,`subMerchantId`,`communicateFlag`) USING BTREE,
    KEY `core_car_idx4`(`merchantCode`,`enrollDate`) USING BTREE,
    KEY `core_car_idx5`(`merchantCode`,`vin`) USING BTREE,
    KEY `core_car_idx6`(`merchantCode`,`id`) USING BTREE
) ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=Compact
dbpartition by hash(`merchantCode`)
tbpartition by hash(`merchantCode`) tbpartitions 20

建表语句重点看最后2行红色部分，意思是按照车辆所属的商户进行分库，同时分库后还继续进行分表，分成20份，这样下来的话，一张表的数据总量就彻底降低了。

写这篇文章的核心是有一个注意事项，因为DRDS后面我们放了2台RDS，但是RDS的版本是MySQL5.6，我们有的功能实现是需要跨表的事务的，也就是说一个业务逻辑方法中会同时更新2张或者3张表，这导致出现了跨RDS的分布式事务。

DRDS的表，如果不分库分表的话，这张表是存放着第一个RDS上的第一个数据库里面的

所以这里涉及到查资料：https://help.aliyun.com/document_detail/118061.html?spm=a2c4g.11186623.6.627.782951acM0b474

我们用的MySQL 5.6，默认不支持分布式事务，需要手动改一些代码，对于 MySQL 5.6 版本，由于 MySQL XA 协议实现尚不成熟，DRDS 自主实现了 2PC 事务策略用于分布式事务。MySQL 5.7 及更高版本中，推荐您使用 XA 事务策略。

如果某个事务可能涉及多个数据分库，则需要将当前事务声明为分布式事务。如果事务仅涉及单个数据分库，则无需开启分布式事务，直接像 MySQL 单机事务那样即可，无需额外操作

SET AUTOCOMMIT=0;
SET drds_transaction_policy = '2PC'; -- 建议 MySQL 5.6 用户使用
.... -- 业务 SQL
COMMIT; -- 或 ROLLBACK

JDBC代码示例：

conn.setAutoCommit(false);
try (Statement stmt = conn.createStatement()) {
stmt.execute("SET drds_transaction_policy = '2PC'");
}
// ... 运行业务 SQL ...
conn.commit(); // 或 rollback()

但是我们不能每次操作，都手动写一行代码发送SET drds_transaction_policy = '2PC'，所以改动一下代码如下：

import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class DrdsTransactionManager extends DataSourceTransactionManager {

    public DrdsTransactionManager(DataSource dataSource) {
        super(dataSource);
    }

    @Override
    protected void prepareTransactionalConnection(Connection con, TransactionDefinition     definition) throws SQLException {
        try (Statement stmt = con.createStatement()) {
            stmt.executeUpdate("SET drds_transaction_policy = '2PC'"); // 以 2PC 为例
        }
    }
}

<bean id="drdsTransactionManager" class="my.app.DrdsTransactionManager">
    <property name="dataSource" ref="yourDataSource" />
</bean>

对于需要开启 DRDS 分布式事务的类，加上注解 @Transactional("drdsTransactionManager") 即可。

关于它的全局二级索引

什么叫二级索引，那有没有一级索引？是这样的，数据库的主键就叫一级索引，用一级索引，也就是根据主键查询，速度都是很快的，这种都是点查，所谓的点查，就是查询一个点，也就是查询一条记录的意思。

二级索引就是根据其它字段进行查询，但是呢，这个在单机MySQL中非常常见，但是呢，为什么又要叫全局二级索引呢？因为PolorDB-X是一个分库分表的多机数据库，并不是单机MySQL，如果一张表进行了分库分表，这张表在物理上是分散在多台物理主机上的，那么这张表上的索引也必然得分散存放在多台物理主机上，所以称之为全局的意思。

这个示例是数据表是根据订单ID计算hash后分库的，同时又支持按照卖家ID查询，那么如果不在卖家ID上创建索引，因为这个卖家的订单肯定是散落在多个物理主机MySQL实例上的，如果不创建索引，那么必然会造成这样一种情况，假设一共分为100个库，跑在10个物理主机上，每个物理主机上跑着10个database实例，此时如果按卖家ID查询，必然要把这100个库进行全表扫描，这个性能是超级低下的。

所以必须给卖家ID创建二级索引，同时因为订单表本身被分库分表了，所以索引表也必须是通过特殊语法在所有这100个库上创建索引。

# 建表时定义 GSI
CREATE TABLE t_order (
 `id` bigint(11) NOT NULL AUTO_INCREMENT,
 `order_id` varchar(20) DEFAULT NULL,
 `buyer_id` varchar(20) DEFAULT NULL,
 `seller_id` varchar(20) DEFAULT NULL,
 `order_snapshot` longtext DEFAULT NULL,
 `order_detail` longtext DEFAULT NULL,
 PRIMARY KEY (`id`),
 GLOBAL INDEX `g_i_seller`(`seller_id`) COVERING (`id`, `order_id`, `buyer_id`, `order_snapshot`) dbpartition by hash(`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
# 添加 GSI
CREATE UNIQUE GLOBAL INDEX `g_i_buyer` ON `t_order`(`buyer_id`) 
    COVERING(`seller_id`, `order_snapshot`) 
    dbpartition by hash(`buyer_id`) tbpartition by hash(`buyer_id`) tbpartitions 3

FORCE INDEX({index_name})

SELECT a.*, b.order_id 
 FROM t_seller a 
   JOIN t_order b FORCE INDEX(g_i_seller) ON a.seller_id = b.seller_id 
 WHERE a.seller_nick="abc";

基本上看下来，分库分表后使用上还是蛮麻烦的，注意它这个COVERING覆盖列，意思就是：

1、创建全局二级索引，意味着会自动创建出索引表，索引表的列就是创建索引时指定的索引列和覆盖列，覆盖列不能太多，否则会造成索引表数据量很大，比如数据表有20列，你不能把索引表的列也全部指定成数据表中的列，这岂不是全部重复了？而且如果索引表中列太多，更新数据表中某个字段的时候还要更新索引表中对应的字段，这个就是很不好的做法了。一般来说，索引表中的列都是几乎不怎么变化的列，比如上面的主键ID、订单ID、买家ID、订单快照（订单快照就是淘宝上商品下降后还能通过订单找到当时的商品详情，省的卖家买家扯皮）

2、如果查询的时候，比如查询ABCD 四个字段，但是呢D这个字段不再索引表的列中，这就涉及到回表查询，回表这2个字，是DBA领域的专有名词，意思就是既然索引表中不存在D字段，就需要回到数据表中去查询，这就造成了性能低下，鱼和熊掌不可兼得。

3、如果查询需要使用索引中未包含的列，则首先查询索引表取得所有记录的主键和主表分库分表键，然后回查主表中取得缺少列的值