ADB表创建参数及性能优化

分布键调整（包含主键和查询键）

创建表、写入数据

DROP TABLE IF EXISTS tb_workorderinfo;
Create Table tb_workorderinfo (
Id varchar NOT NULL COMMENT '主键(WI)',
AppCode varchar COMMENT '申请编码',
SourceType smallint COMMENT '工单来源(0工单下单1智车品2客户接口3LCRM下单4OCRM下单5平安私有云6金融SAAS推送7车务推送)',
OrderType varchar COMMENT '工单类型(sys_ordertypeinfo.Code)',
CreateType smallint COMMENT '工单建单方式(来源二级分类)(1批量导入)',
WorkStatus smallint COMMENT '工单状态(0待提交1正在审核2审核不通过3正在寻找工程师4等待工程师接单5已拒单，重新寻找工程师6等待提交结果7等待客户确认8客户驳回，重新处理9已完成10关闭11质检12质检不通过)',
ServiceProviderCode varchar COMMENT '服务供应商Code',
CustomerId varchar COMMENT '客户Id',
CustomerName varchar COMMENT '客户名称',
CustStoreId varchar COMMENT '门店Id',
CustStoreName varchar COMMENT '门店名称',
CustStoreCode varchar COMMENT '门店Code',
PreCustStoreId varchar COMMENT '客户接口下单门店Id',
PreCustStoreName varchar COMMENT '客户接口下单门店名称',
CustSettleId varchar COMMENT '结算单位Id',
CustSettleName varchar COMMENT '结算单位名称',
IsCustomer tinyint NOT NULL COMMENT '是否为客户(0否1是)',
CustCoopType smallint COMMENT '客户是否有合作商品(1中瑞2非中瑞3中瑞+非中瑞)',
ProCode varchar COMMENT '省份Code',
ProName varchar COMMENT '省份名称',
CityCode varchar COMMENT '城市Code',
CityName varchar COMMENT '城市名称',
AreaCode varchar COMMENT '地区Code',
AreaName varchar COMMENT '地区名称',
InstallAddress varchar COMMENT '加装地址',
InstallTime datetime COMMENT '加装时间',
RequiredTime datetime COMMENT '要求时间',
LinkMan varchar COMMENT '联系人姓名',
LinkTel varchar COMMENT '联系电话',
SecondLinkTel varchar COMMENT '第二联系电话',
SecondLinkMan varchar COMMENT '第二联系人',
WarehouseId varchar COMMENT '发货一级仓Id',
WarehouseName varchar COMMENT '发货一级仓名称',
Remark varchar COMMENT '客户备注',
IsUrgent tinyint NOT NULL DEFAULT '0' COMMENT '检修工单是否紧急(0否1是)',
CustUniqueSign varchar COMMENT '客户唯一标识',
CreatePersonCode varchar COMMENT '建单人Code',
CreatePersonName varchar COMMENT '建单人姓名',
EffectiveTime datetime COMMENT '工单生效时间',
EffectiveSuccessfulTime datetime COMMENT '下单成功生效时间',
CreatedById varchar COMMENT '',
CreatedAt datetime COMMENT '',
UpdatedById varchar COMMENT '',
UpdatedAt datetime COMMENT '',
DeletedById varchar COMMENT '',
DeletedAt datetime COMMENT '',
Deleted tinyint NOT NULL DEFAULT '0' COMMENT '',
primary key (id)
) DISTRIBUTE BY HASH(id) INDEX_ALL='Y' STORAGE_POLICY='HOT' BLOCK_SIZE=8192
;

DROP TABLE IF EXISTS tb_workcarinfo;
Create Table tb_workcarinfo (
Id varchar NOT NULL COMMENT '主键(WC)',
WorkOrderId varchar COMMENT '工单主单Id(tb_workorderinfo.Id)',
VinNumber varchar COMMENT '车架号',
PlateNumber varchar COMMENT '车牌号',
EngineNumber varchar COMMENT '发动机号',
CarModelId varchar COMMENT '车型Id',
CarModelName varchar COMMENT '车型名称',
CarSeriesId varchar COMMENT '车系Id',
CarSeriesName varchar COMMENT '车系名称',
CarBrandId varchar COMMENT '车辆品牌Id',
CarBrandName varchar COMMENT '车辆品牌名称',
CarFullName varchar COMMENT '车型全称',
Color varchar COMMENT '车辆颜色',
CarPrice decimal(18, 2) COMMENT '车辆价格',
IsNewCar smallint COMMENT '车辆类型(0二手车1新车)',
CarType smallint COMMENT '车辆类型(1新车2二手车)',
UserName varchar COMMENT '车主姓名',
UserTel varchar COMMENT '车主联系方式',
UserCityCode varchar COMMENT '车主所在城市Code',
UserCityName varchar COMMENT '车主所在城市名称',
UserAddress varchar COMMENT '车主地址',
ShortVin varchar COMMENT '车架号后6位',
Remark varchar COMMENT '车辆备注',
CreatedById varchar COMMENT '',
CreatedAt datetime COMMENT '',
UpdatedById varchar COMMENT '',
UpdatedAt datetime COMMENT '',
DeletedById varchar COMMENT '',
DeletedAt datetime COMMENT '',
Deleted tinyint NOT NULL DEFAULT '0' COMMENT '',
primary key (id)
) DISTRIBUTE BY HASH(id) INDEX_ALL='Y' STORAGE_POLICY='HOT' BLOCK_SIZE=8192
;

INSERT INTO tb_workorderinfo
SELECT *
FROM ods_serviceordercenter.tb_workorderinfo ;

INSERT INTO tb_workcarinfo
SELECT *
FROM ods_serviceordercenter.tb_workcarinfo ;

查询性能

SELECT /*+ filter_not_pushdown_columns=[ods_testcenter.tb_workcarinfo:Deleted|WorkOrderId] */ #禁止索引下推，不使用Deleted字段索引，如果不加全部索引的话，这个可以不设置
a.AppCode,b.VinNumber #非必要一定不要写 *
FROM tb_workorderinfo a
LEFT JOIN tb_workcarinfo b
ON b.WorkOrderId=a.Id
AND b.Deleted=0
WHERE a.Deleted=0
AND a.CreatedAt>='2021-01-01'
LIMIT 100;

800ms左右

更换分区方式

DROP TABLE IF EXISTS tb_workorderinfo;
Create Table tb_workorderinfo (
Id varchar NOT NULL COMMENT '主键(WI)',
AppCode varchar COMMENT '申请编码',
SourceType smallint COMMENT '工单来源(0工单下单1智车品2客户接口3LCRM下单4OCRM下单5平安私有云6金融SAAS推送7车务推送)',
OrderType varchar COMMENT '工单类型(sys_ordertypeinfo.Code)',
CreateType smallint COMMENT '工单建单方式(来源二级分类)(1批量导入)',
WorkStatus smallint COMMENT '工单状态(0待提交1正在审核2审核不通过3正在寻找工程师4等待工程师接单5已拒单，重新寻找工程师6等待提交结果7等待客户确认8客户驳回，重新处理9已完成10关闭11质检12质检不通过)',
ServiceProviderCode varchar COMMENT '服务供应商Code',
CustomerId varchar COMMENT '客户Id',
CustomerName varchar COMMENT '客户名称',
CustStoreId varchar COMMENT '门店Id',
CustStoreName varchar COMMENT '门店名称',
CustStoreCode varchar COMMENT '门店Code',
PreCustStoreId varchar COMMENT '客户接口下单门店Id',
PreCustStoreName varchar COMMENT '客户接口下单门店名称',
CustSettleId varchar COMMENT '结算单位Id',
CustSettleName varchar COMMENT '结算单位名称',
IsCustomer tinyint NOT NULL COMMENT '是否为客户(0否1是)',
CustCoopType smallint COMMENT '客户是否有合作商品(1中瑞2非中瑞3中瑞+非中瑞)',
ProCode varchar COMMENT '省份Code',
ProName varchar COMMENT '省份名称',
CityCode varchar COMMENT '城市Code',
CityName varchar COMMENT '城市名称',
AreaCode varchar COMMENT '地区Code',
AreaName varchar COMMENT '地区名称',
InstallAddress varchar COMMENT '加装地址',
InstallTime datetime COMMENT '加装时间',
RequiredTime datetime COMMENT '要求时间',
LinkMan varchar COMMENT '联系人姓名',
LinkTel varchar COMMENT '联系电话',
SecondLinkTel varchar COMMENT '第二联系电话',
SecondLinkMan varchar COMMENT '第二联系人',
WarehouseId varchar COMMENT '发货一级仓Id',
WarehouseName varchar COMMENT '发货一级仓名称',
Remark varchar COMMENT '客户备注',
IsUrgent tinyint NOT NULL DEFAULT '0' COMMENT '检修工单是否紧急(0否1是)',
CustUniqueSign varchar COMMENT '客户唯一标识',
CreatePersonCode varchar COMMENT '建单人Code',
CreatePersonName varchar COMMENT '建单人姓名',
EffectiveTime datetime COMMENT '工单生效时间',
EffectiveSuccessfulTime datetime COMMENT '下单成功生效时间',
CreatedById varchar COMMENT '',
CreatedAt datetime COMMENT '',
UpdatedById varchar COMMENT '',
UpdatedAt datetime COMMENT '',
DeletedById varchar COMMENT '',
DeletedAt datetime COMMENT '',
Deleted tinyint NOT NULL DEFAULT '0' COMMENT '',
primary key (id,CreatedAt),
KEY NON-AppCode (AppCode),
KEY NON-OrderType (OrderType),
KEY NON-ProCode (ProCode),
KEY NON-CustStoreCode (CustStoreCode),
KEY NON-CustStoreId (CustStoreId),
KEY NON-WorkStatus (WorkStatus),
KEY NON-CreatedAt (CreatedAt),
KEY NON-CreatePersonName (CreatePersonName),
KEY NON-LinkMan (LinkMan),
KEY NON-CustomerId (CustomerId),
KEY NON-CreateType (CreateType),
KEY NON-InstallTime (InstallTime),
KEY NON-RequiredTime (RequiredTime),
KEY NON-CustomerName (CustomerName),
KEY NON-CustSettleId (CustSettleId)
) DISTRIBUTE BY HASH(id)
PARTITION BY VALUE (DATE_FORMAT(CreatedAt,'%Y%m')) LIFECYCLE 200
STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=2
BLOCK_SIZE=8192;

DROP TABLE IF EXISTS tb_workcarinfo;
Create Table tb_workcarinfo (
Id varchar NOT NULL COMMENT '主键(WC)',
WorkOrderId varchar COMMENT '工单主单Id(tb_workorderinfo.Id)',
VinNumber varchar COMMENT '车架号',
PlateNumber varchar COMMENT '车牌号',
EngineNumber varchar COMMENT '发动机号',
CarModelId varchar COMMENT '车型Id',
CarModelName varchar COMMENT '车型名称',
CarSeriesId varchar COMMENT '车系Id',
CarSeriesName varchar COMMENT '车系名称',
CarBrandId varchar COMMENT '车辆品牌Id',
CarBrandName varchar COMMENT '车辆品牌名称',
CarFullName varchar COMMENT '车型全称',
Color varchar COMMENT '车辆颜色',
CarPrice decimal(18, 2) COMMENT '车辆价格',
IsNewCar smallint COMMENT '车辆类型(0二手车1新车)',
CarType smallint COMMENT '车辆类型(1新车2二手车)',
UserName varchar COMMENT '车主姓名',
UserTel varchar COMMENT '车主联系方式',
UserCityCode varchar COMMENT '车主所在城市Code',
UserCityName varchar COMMENT '车主所在城市名称',
UserAddress varchar COMMENT '车主地址',
ShortVin varchar COMMENT '车架号后6位',
Remark varchar COMMENT '车辆备注',
CreatedById varchar COMMENT '',
CreatedAt datetime COMMENT '',
UpdatedById varchar COMMENT '',
UpdatedAt datetime COMMENT '',
DeletedById varchar COMMENT '',
DeletedAt datetime COMMENT '',
Deleted tinyint NOT NULL DEFAULT '0' COMMENT '',
primary key (id,WorkOrderId,CreatedAt),
KEY NON-PlateNumber (PlateNumber),
KEY NON-UserName (UserName),
KEY NON-WorkOrderId (WorkOrderId),
KEY NON-VinNumber (VinNumber),
KEY NON-ShortVin (ShortVin)
) DISTRIBUTE BY HASH(WorkOrderId)
PARTITION BY VALUE (DATE_FORMAT(CreatedAt,'%Y%m')) LIFECYCLE 200
STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=2STORAGE_POLICY='HOT' BLOCK_SIZE=8192;

INSERT INTO tb_workorderinfo
SELECT *
FROM ods_serviceordercenter.tb_workorderinfo ;

INSERT INTO tb_workcarinfo
SELECT *
FROM ods_serviceordercenter.tb_workcarinfo ;

可以看出差距不大；

DROP TABLE IF EXISTS tb_alarminfo;
CREATE TABLE tb_alarminfo (
Id varchar COMMENT '主键(AI)',
CarId varchar COMMENT '车辆Id',
DeviceId varchar COMMENT '设备Id',
DeviceNumber varchar COMMENT '设备号',
AlarmType smallint COMMENT '预警类型:1 光敏;2 断电;3 翻转;4 伪基站;5 SOS;6 急加速;7 急减速;8 急转弯;9 震动;10 碰撞;11 电瓶低电压;12 硬件超速预警;13 事故预警;14 摔车报警;15 倾倒预警',
DeviceLat decimal(11,8) COMMENT '原始纬度',
DeviceLng decimal(11,8) COMMENT '原始经度',
Speed decimal(10,2) COMMENT '速度',
FaultCode varchar COMMENT '故障码',
AlarmAmount int COMMENT '当前预警次数',
AlarmTime datetime COMMENT '预警时间',
IsRead tinyint(1) DEFAULT '0' COMMENT '是否已读: 0 否;1 是',
IsSend tinyint(1) DEFAULT '0' COMMENT '是否推送:0 否;1 是',
IsFrozen tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否冻结:0 否(最新版本);1 是(历史版本)',
InsertTime datetime DEFAULT NULL COMMENT '预警生成时间',
Deleted tinyint(1) DEFAULT '0' COMMENT '是否删除:0 否;1 是',
PRIMARY KEY (Id,CarId,AlarmTime),
KEY NON-AlarmType (AlarmType),
KEY NON-DeviceNumber (DeviceNumber),
KEY NON-IsRead (IsRead),
KEY NON-IsSend (IsSend),
KEY NON-DeviceId (DeviceId),
KEY NON-IsFrozen (IsFrozen),
KEY NON-InsertTime (InsertTime),
KEY NON-AlarmTime (AlarmTime)
) COMMENT='预警域_硬件预警记录'
DISTRIBUTE BY HASH(CarId)
PARTITION BY VALUE (DATE_FORMAT(AlarmTime,'%Y%m')) LIFECYCLE 200
STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=3 BLOCK_SIZE=8192;

DROP TABLE IF EXISTS tb_distalarminfo;
CREATE TABLE tb_distalarminfo (
Id varchar COMMENT '主键(DA)倒序',
CarId varchar COMMENT '车辆Id',
DeviceId varchar COMMENT '设备Id',
DeviceNumber varchar COMMENT '设备号',
AlarmGrade smallint COMMENT '危险等级:0 安全;1 低(跨市);2 中(跨省);3 高(跨国)',
DeviceLat decimal(11,8) COMMENT '原始纬度',
Devicelng decimal(11,8) COMMENT '原始经度',
AlarmTime datetime COMMENT '预警时间',
SafeProCode varchar COMMENT '安全省份Code',
SafeCityCode varchar COMMENT '安全城市Code',
NowProCode varchar COMMENT '目前省份Code',
NowCityCode varchar COMMENT '目前城市Code',
AlarmAmount int COMMENT '当前预警次数',
UnAlarmAmount int COMMENT '当前解除预警次数',
IsAlarm tinyint(1) DEFAULT '1' COMMENT '是否报警:0 解除报警;1 报警',
IsRead tinyint(1) DEFAULT '0' COMMENT '是否已读:0 否;1 是',
IsSend tinyint(1) DEFAULT '0' COMMENT '是否推送:0 否;1 是',
IsFrozen tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否冻结:0 否(最新数据);1 是(历史数据)',
InsertTime datetime COMMENT '预警生成时间',
Deleted tinyint(1) DEFAULT '0' COMMENT '是否删除:0 否;1 是',
PRIMARY KEY (Id,CarId,AlarmTime),
KEY NON-DeviceNumber (DeviceNumber),
KEY NON-AlarmGrade (AlarmGrade),
KEY NON-IsFrozen (IsFrozen),
KEY NON-IsSend (IsSend),
KEY NON-DeviceId (DeviceId),
KEY NON-AlarmTime (AlarmTime),
KEY NON-SafeCityCode (SafeCityCode),
KEY NON-NowCityCode (NowCityCode),
KEY NON-SafeProCode (SafeProCode),
KEY NON-NowProCode (NowProCode)
) COMMENT='预警域_跨区预警信息'
DISTRIBUTE BY HASH(CarId)
PARTITION BY VALUE (DATE_FORMAT(AlarmTime,'%Y%m')) LIFECYCLE 200
STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=3 BLOCK_SIZE=8192;