在亿级数据单表sum技巧

一份关于上亿级别单表测试记录，大家可以看看找到优化方案和灵感

这张表大约有1.1亿数据, 是一个订单表，整张表数据22gb；使用mysql8 表格式如下：

CREATE TABLE `cp_orders` (
  `code` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键(订单号)',
  `a_code` int(11) DEFAULT NULL COMMENT '代理商id',
  `u_code` int(11) NOT NULL COMMENT '用户id',
  `trade_no` int(11) DEFAULT NULL COMMENT '订单编号',
  `serial_number` varchar(70) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '',
  `pay_type` tinyint(4) NOT NULL COMMENT '',
  `pay_money` int(11) DEFAULT NULL COMMENT '支付金额',
  `remarks` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',

  `close_date` datetime DEFAULT NULL COMMENT '',
  `charge_model` tinyint(3) DEFAULT NULL COMMENT '',
  `ext` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '',
  `companyId` int(11) DEFAULT NULL COMMENT '',
  `order_type` tinyint(4) NOT NULL COMMENT '',
  `equipment_id` int(11) DEFAULT '0' COMMENT '',
  `powerbank_id` int(11) DEFAULT NULL COMMENT '',
  `type` tinyint(1) DEFAULT '1' COMMENT '',
  `paid` int(2) DEFAULT '1' COMMENT '1:未支付，2已支付',
  `out_time` tinyint(2) DEFAULT '1' COMMENT '',
  `consume_type` tinyint(1) DEFAULT '1' COMMENT '',
  `consume_scheme` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '',
  `status` tinyint(4) NOT NULL COMMENT '',
  `get_mcode` int(11) DEFAULT NULL COMMENT '',
  `repay_mcode` int(11) DEFAULT '0' COMMENT '',
  `create_date` datetime NOT NULL COMMENT '创建时间',
  `update_date` datetime NOT NULL COMMENT '更新时间',

  `trans_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '',
  PRIMARY KEY (`code`) USING BTREE,
  KEY `acode` (`a_code`)
) ENGINE=InnoDB AUTO_INCREMENT=179418339 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='订单表';

这里提出几个常见的业务方向进行索引优化：

1. 对于订单来说, 主要是代理商端和商户端订单查询。第二是订单额的统计

基于以上两点为进行的订单查询，第一就肯定要把a_code添加进索引， 由于代理商进行查询时一般会根据订单状态，支付状态，时间范围进行查询，而且一般管理后台都会统计查询的订单额。

所以这里暂定索引为a_code，添加索引后，亿级别数据getById是完全没有性能问题的。没有其他条件的话，分页的效率也还是不错 所有查询基本返回响应再1S内，还能接受。

接着抛出一个很有有意思的事情，以下sql在业务中做金额统计很常见:

# 耗时1.9s，扫描行数333446
select sum(pay_money) from cp_orders where a_code = 7903 limit 10000

# 耗时30+未出结果，扫描行数333446
select sum(pay_money) from(
select pay_money from cp_orders where a_code = 7903 limit 10000
) t

上面两个SQL看起来都一样，但是后者比前者执行效率高很多倍，特别是当订单数多的时候。后面去找到原因是，第一个sql会先将33W行数据加载 出来后，再取前1W行进行sum, 而后者则是直接取1W数据然后sum.

看下以下效率:

# 11.6s 反应很慢
select * from cp_orders where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
limit 100


# 12.2s  仅仅添加了状态
select * from cp_orders where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
and order_type = 1
limit 100

# 这里尝试统计下金额, 30.2s
select sum(pay_money) from cp_orders where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
and order_type = 1
limit 100


# 还记得前面的结论把,使用第二种方式统计, 10.4s
select sum(pay_money) from (
select pay_money from cp_orders where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
and order_type = 1
limit 100
) t

看起来此时查询很慢，此时我们加上时间索引看下改善情况,也就是索引变为a_code, create_date 增加索引耗时约5分钟。接着再看上面sql执行时间: 看下以下效率:

# 0.137s 这个就基本没啥毛病了
select * from cp_orders where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
limit 100


# 0.106, 此时看起来就没有之前那么离谱了。  仅仅添加了状态条件
select * from cp_orders where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
and order_type = 1
limit 100

# 条件过滤下，模拟分页查询. 17.4s， 有点难受
# 这里由于mysql查询后还需要回表获取 order_type 所以有点慢
select count(*) from cp_orders  where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
and order_type = 1

# 这里尝试统计下金额, 17.51s。 这里看起来已经比之前30s快了很多了
select sum(pay_money) from cp_orders where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
and order_type = 1
limit 100


# 0.075s, 这么看的话似乎就没什么毛病了
select sum(pay_money) from (
select pay_money from cp_orders where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
and order_type = 1
limit 100
) t

上面针对于加了order_type字段时，查询依旧很慢，达到17s了。所以这里我们再增加一个 order_type 索引; 也就是索引变为:a_code, create_date, order_type 增加后再执行上述sql:

# 条件过滤下，模拟分页查询. 0.081， 看样子分页查询没太大问题了
# 这里值得一提的是，范围查询后面的order_type 生效了
select count(*) from cp_orders  where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
and order_type = 1

# 这里再尝试统计下金额, 20.3s, 还是不太理想
select sum(pay_money) from cp_orders where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
and order_type = 1
limit 100

看起来此时查询很慢，此时我们加上支付金额索引看下改善情况,也就是索引变为a_code, create_date, pay_money 增加索引耗时约5分钟。接着再看上面sql执行时间: 看下以下效率:

# 这里尝试统计下金额, 0.048s  现在这个果然就快了
select sum(pay_money) from cp_orders where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
and order_type = 1
limit 100

# 0.20s, 这里可以看到，即使是索引状态下，依然比上述查询方式快了一倍
select sum(pay_money) from (
select pay_money from cp_orders where a_code = 7903 
and create_date >= '2019-06-26 12:38:18' 
and create_date < '2020-06-26 12:38:18' 
and order_type = 1
limit 100
) t

综上述：

• 查询条件最好有默认值，这样联合查询才能生效
• 对于范围查询，后续的联合索引也能生效
• 尽量增加联合索引，因为这次成本低，区分度高
• 对于默认查询全部，不推荐加索引。直接使用id倒排效果几乎和时间倒排是一样的。
• 联合索引可以根据索引做匹配直接在索引中就进行过滤，此时效果最佳
• 订单金额索引在进行聚合操作时，不严格遵守左匹配，即使只配置个创建时间，依然可以使用订单金额

所以对于应该增加的索引是: 员工+时间 订单状态+时间 代理商+时间 订单编号

这里总结下：

创建索引： 每次约5分钟

删除索引： 几乎是及时删除，无任何影响。

关于锁表 在mysql8中，使用alert table 进行索引创建时将会锁表，使用create index 则不会