当前位置：首页 > news >正文

芝法酱学习笔记（2.2）——sql性能优化2

news 来源：原创 2025/5/22 21:57:47

一、前言

在上一节中，我们使用实验的方式，验证了销售单报表的一些sql性能优化的猜想。但实验结果出乎我们的意料，首先是时间查询使用char和datetime相比，char可能更快，使用bigint（转为秒）和char速度相当。其次是最令人不可理解的是，在连表的时候，直接使用主键id做连表，竟然远远比使用多条件联合索引的连表方式更慢。
小编苦思冥想，认为在千万级数据量下，bigint的筛选效率可能没有联合索引高。那么，如果我们把主键设置为联合主键，在连表时会不会更快呢？那么，就开干吧

二、表设计

2.1 item_new表

我们新建一个item表，表结构和索引如下图所示：
在这里插入图片描述
主键：enp_id,id
不添加任何其他索引

2.2 consign_new表

consign表也类似，我们也用联合主键
在这里插入图片描述
主键：enp_id,header_id,id

三、数据同步导入

为了控制变量，我们不重新生成数据，我们把上次的数据查询出来并相应的插入新表

@Overridepublic void syncNewTable() {// 查出所有企业List<GenEnterpriseEntity> enterpriseEntities = mEnterpriseDbService.list();for(GenEnterpriseEntity enterpriseEntity : enterpriseEntities) {log.info("开始导入"+enterpriseEntity.getName()+"数据");List<GenItemEntity> itemEntityList = mItemDbService.listByEnpId(enterpriseEntity.getId());List<GenItemNewEntity> itemNewEntityList = new ArrayList<>();for(GenItemEntity itemEntity : itemEntityList) {GenItemNewEntity itemNewEntity = new GenItemNewEntity();itemNewEntity.createInit();itemNewEntity.setEnpId(itemEntity.getId());itemNewEntity.setEnpCode(itemEntity.getEnpCode());itemNewEntity.setId(itemEntity.getId());itemNewEntity.setName(itemEntity.getName());itemNewEntity.setCost(itemEntity.getCost());itemNewEntity.setTestData(true);itemNewEntityList.add(itemNewEntity);}TransactionTemplate template = new TransactionTemplate(mTransactionManager);template.execute(status ->{mItemNewDbService.saveBatch(itemNewEntityList);return true;});log.info("导入商品完成");final String DAY_BEGIN = "2018-01-01";final String DAY_END = "2024-12-31";LocalDate startDate = LocalDate.parse(DAY_BEGIN);LocalDate endDate = LocalDate.parse(DAY_END);while (!startDate.isAfter(endDate)) {log.info("导入"+startDate+"的销售单数据");LocalDateTime billTimeBeg = startDate.atTime(0, 0, 0);LocalDateTime billTimeEnd = startDate.atTime(23, 59, 59);long billTimeKeyBeg = CommonUtil.LocalDateTimeToSecond(billTimeBeg);long billTimeKeyEnd = CommonUtil.LocalDateTimeToSecond(billTimeEnd);List<GenConsignEntity> consignEntityList = mConsignDbService.findAll(enterpriseEntity.getId(),billTimeKeyBeg,billTimeKeyEnd);List<GenConsignNewEntity> consignNewEntityList = new ArrayList<>();for(GenConsignEntity consignEntity : consignEntityList) {GenConsignNewEntity consignNewEntity = new GenConsignNewEntity();consignNewEntity.createInit();consignNewEntity.setId(consignEntity.getId());consignNewEntity.setEnpId(consignEntity.getEnpId());consignNewEntity.setHeaderId(consignEntity.getHeaderId());consignNewEntity.setBillTimeKey(consignEntity.getBillTimeKey());consignNewEntity.setItemId(consignEntity.getItemId());consignNewEntity.setItemName(consignEntity.getItemName());consignNewEntity.setItemCnt(consignEntity.getItemCnt());consignNewEntity.setPrice(consignEntity.getPrice());consignNewEntity.setDescription(consignEntity.getDescription());consignNewEntity.setTestData(true);consignNewEntityList.add(consignNewEntity);}consignNewEntityList.sort(Comparator.comparing(GenConsignNewEntity::getHeaderId));template = new TransactionTemplate(mTransactionManager);template.execute(status -> {mConsignNewDbService.saveBatch(consignNewEntityList);return true;});log.info(startDate+"的销售单数据导入完成");startDate = startDate.plusDays(1l);}}}

四、mapper改写

4.1 枚举

@RequiredArgsConstructor
@EnumDesc
public enum EHeaderJoinMode {NONE(0,"不连表","不连表，直接使用consign表做查询",null,null),ID_JOIN(1,"id关联","consign_header的id与consign的header_id做关联","id","header_id"),BILL_NO_JOIN(2,"订单号关联","header表的enp_id和bill_no与consin相应字段关联","bill_no","bill_no"),NEW_CONSIGN(3,"新consign表","enp_id和header_id做连接","id","header_id");@EnumValue@Getterprivate final int code;@Getterprivate final String name;@Getterprivate final String desc;@Getterprivate final String headerCol;@Getterprivate final String consignCol;
}

@RequiredArgsConstructor
@EnumDesc
public enum EItemJoinMode {NONE(0,"不连接","不连接item表",null,null),ID_JOIN(1,"id连接","使用id链接item","item_id","id"),STR_ID_JOIN(2,"字符串id连接","使用字符串id做连接","item_str_id","id"),REL_ID_JOIN(3,"关联id连接","不但使用字符串id做连接，item表也不用主键","item_str_id","rel_id"),NEW_TABLE(4,"和item_new做连接","和item_new做连接,不但连id，还连enp_id","item_id","id");@EnumValue@Getterprivate final int code;@Getterprivate final String name;@Getterprivate final String desc;@Getterprivate final String consignCol;@Getterprivate final String itemCol;
}

4.2 xml改写

这里不放完整代码了，就改了2处

<choose><when test="IN.headerJoin.name() == 'ID_JOIN'">consign_header h JOIN consign c ON h.${IN.headerJoin.headerCol} = c.${IN.headerJoin.consignCol}</when><when test="IN.headerJoin.name() == 'BILL_NO_JOIN'">consign_header h JOIN consign c ON h.${IN.headerJoin.headerCol} = c.${IN.headerJoin.consignCol} AND h.enp_id = c.enp_id</when><!--新增模式--><when test="IN.headerJoin.name() == 'NEW_CONSIGN'">consign_header h JOIN consign_new c ON h.${IN.headerJoin.headerCol} = c.${IN.headerJoin.consignCol} AND h.enp_id = c.enp_id</when><otherwise>consign c</otherwise></choose><choose><when test="IN.itemJoin.name() == 'ID_JOIN'">JOIN item i ON c.${IN.itemJoin.consignCol} = i.${IN.itemJoin.itemCol}</when><when test="IN.itemJoin.name() == 'STR_ID_JOIN'">JOIN item i ON c.${IN.itemJoin.consignCol} = i.${IN.itemJoin.itemCol}</when><when test="IN.itemJoin.name() == 'REL_ID_JOIN'">JOIN item i ON c.${IN.itemJoin.consignCol} = i.${IN.itemJoin.itemCol} AND c.enp_id = i.enp_id</when><!--新增模式--><when test="IN.itemJoin.name() == 'NEW_TABLE'">JOIN item i ON c.${IN.itemJoin.consignCol} = i.${IN.itemJoin.itemCol} AND c.enp_id = i.enp_id</when></choose>

五、实验

请求json

{"current": 2,"size": 10,"enterpriseId": 1869035491194941444,"billTimeBeg": "2024-04-01","billTimeEnd": "2024-07-31","headerJoin": "NEW_CONSIGN","itemJoin": "NEW_TABLE","orderBy": "PROFIT","billTimeMode": "BILL_TIME_KEY"
}

生成sql：

explain SELECT c.item_id,c.item_name,
SUM(c.item_cnt) AS total_cnt, 
SUM(c.price * c.item_cnt) AS total_amount, 
SUM((c.price - i.cost) * c.item_cnt) AS total_profit 
FROM consign_header h JOIN consign_new c ON h.id = c.header_id AND h.enp_id = c.enp_id 
JOIN item i ON c.item_id = i.id AND c.enp_id = i.enp_id 
WHERE h.enp_id = 1869035491194941444 
AND h.bill_time_key BETWEEN 1711900800 AND 1722441599 
GROUP BY item_id 
ORDER BY total_profit LIMIT 10,10

explain结果

select_type	table	type	key	ken_ken	rows	filtered
simple	h	rang	idx_time_key	16	11516	100
simple	c	ref	PRIMARY	16	43	100
simple	i	ref	PRIMARY	8	1	100

执行时间

enp_id	enp_code	cnt	time
1869035491194941442	enp_001	248814	2.638s
1869035491194941443	enp_002	263780	2.285s
1869035491194941444	enp_003	120522	1.157s
1869035491194941445	enp_004	84262	1.003s
1869035491194941446	enp_005	174673	4.157s
1869035491194941447	enp_006	342751	4.105s
1869035491194941448	enp_007	52964	0.48s
1869035491194941449	enp_008	172159	3.895s
1869035491194941450	enp_009	181632	4.688s
1869035491194941451	enp_010	188382	5.168s

先前使用id主键连表的执行时间

enp_id	enp_code	cnt	time
1869035491194941442	enp_001	248814	19.311s
1869035491194941443	enp_002	263780	18.534s
1869035491194941444	enp_003	120522	13.849s
1869035491194941445	enp_004	84262	5.782s
1869035491194941446	enp_005	174673	21.158s
1869035491194941447	enp_006	342751	20.927s
1869035491194941448	enp_007	52964	3.087s
1869035491194941449	enp_008	172159	19.982s
1869035491194941450	enp_009	181632	23.256s
1869035491194941451	enp_010	188382	26.057s

结论：
效率比先前，提升了6~8倍！！！

六、经验总结

在设计表的时候，如果有明确的父子表层级关系（1对多），并且数据量很大，子表的主键直接设计成联合主键。
比如本案例中的，enterprise -> item,consign_header; consign_header->consign

七、一些其他筛选参数

上节我们还有一些其他筛选参数的情况没有测试，诸如门店id，业务员id等
群友们可以自行测试，我这里就不耗费篇幅了。
结论就是按照预期走了索引。

八、order by问题

上期由于篇幅原因，还有一个问题没有实验，就是order by id使得主键索引覆盖了正常的索引。
小编懒得写代码做实验了，我们直接写sql吧：

ORDER BY id DESC:

SELECT * FROM consign_header h
WHERE h.enp_id = 1869035491194941447
AND h.bill_time_key BETWEEN 1711900800 AND 1722441599 
ORDER BY id DESC LIMIT 100;

在workbench的执行时间：0.047S
在这里插入图片描述

ORDER BY bill_time_key DESC:

explain SELECT * FROM consign_header h
WHERE h.enp_id = 1869035491194941447
AND h.bill_time_key BETWEEN 1711900800 AND 1722441599 
ORDER BY h.bill_time_key DESC LIMIT 100;

在workbench的执行时间：0.015S，并且第二次执行会因缓存变为0秒
在这里插入图片描述
我们可以看到，order by id 会使得查找条件不走索引，而走了主键，并且速度显著降低。