SQL优化之count,表的连接顺序、条件顺序,in和exist

本文探讨了SQL查询中的优化技巧,包括COUNT(*)与COUNT(列)的性能比较、IN与EXISTS的区别、多表查询的连接顺序以及WHERE子句中连接条件的顺序。通过实验分析,揭示了在不同场景下优化策略的有效性,并阐述了Oracle优化器在不同版本中的变化。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一、关于count

看过一些关于count(*)和count(列)的文章,count(列)的效率一定比count(*)高吗?

其实个人觉得count(*)和count(列)根本就没有可比性,count(*)统计的是表里面的总条数,而count(列)统计的是当列的非空记录条数。

不过我们可以通过实验来比较一下:

首先创建测试表:

drop table test purge;
create table test as select * from dba_objects;

update test  set object_id =rownum ;
set timing on 
set linesize 1000
set autotrace on 


执行

select count(*) from test;
select count(object_id) from test;

发现耗时是一样的,难道他们的效率其实是一样的吗?

我们在列object_id上创建索引试试看

create index idx_object_id on test(object_id);

然后再执行 

select count(*) from test;
select count(object_id) from test;

发现count(object_id)的速度明显比count(*)高出一大截,难道是因为count(object_id)能用到索引,所以效率才提高了很多?

我们再修改下object_id的列属性

alter table test modify object_id  not  null;

然后再执行 

select count(*) from test;
select count(object_id) from test;

发现其实他们的速度是一样快的,count(*)也可用到索引。

其实效率比较的前提是两个语句的写法要等价,这两种写法根本就不等价,因此不具有可比性。

对于oracle优化器来说,我们可以通过实验发现,count不同的列,统计的时间是不一样的,大致趋势是列越靠后,访问的开销越大,列的偏移量绝地访问的性能。而count(*)的开销与偏移量无关。因此,在某些场合count(*)反而是最快的。


二、关于in和exist

关于in和exist的说法大都是说in的效率比exist高,所以有in的地方必需得换成exist等等。但是真的是这样的吗?

我们可以做个试验:

在10g中;

select * from dept where deptno NOT IN ( select deptno from emp ) ;
select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;

我们发现,exist确实比in的效率高啊。这个说法貌似是成立的啊。

但是我们再执行下面的语句

select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;

你会发现加上非空的约束条件后,in和exist的效率是一样的。

查看三个语句的执行计划你就会发现,没有加上非空约束的in语句和exist语句走的都是ANTI半连接算法,所以效率是一样的,而未加非空约束的in语句用的是filter,而不是ANTI算法,所以效率就差一些。

所以我们可以得出结论:在oracle 10g中,如果可以确保非空,则in约束可以用到ANTI的半连接算法,这时候的效率和exist是一样的。


在11g中:

select * from dept where deptno NOT IN ( select deptno from emp ) ;
select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;

我们发现两个语句的效率是一样的,查看执行计划也是一样的。原来oracle在11g中已经做了优化,所以in和exist的效率是一样的。

由此我们可以得出结论,在11g中,使用in和exist的效率是一样的,因为他们走的都是比较高效的ANTI算法。


三、关于大小表的连接顺序

在网上我们可以看到很多这样的文章,在进行多表查询的时候,用小表或者交叉表做基础表,放在后面,大表放在from后面的位置,因为表的访问顺序是从右往左的。

但是真的是这样的吗?

我们可以做实验验证一下(11g):

create table tab_big  as select * from dba_objects where rownum<=30000;
create table tab_small  as select * from dba_objects where rownum<=10;
set autotrace traceonly
set linesize 1000
set timing on 
select count(*) from tab_big,tab_small   ;  
select count(*) from tab_small,tab_big   ;

我们查看执行计划可以发现,这两个语句的效率是一样的,难道多表查询,表的顺序和效率无关吗?

我们在执行下面的语句:

select /*+rule*/ count(*) from tab_big,tab_small ;  
select /*+rule*/ count(*) from tab_small,tab_big ;

我们可以清楚的发现,小表在右,大表在左的语句,查询效率高很多。

其实,在基于规则时代,查询效率是和表的连接顺序相关的,小表或者交叉表在左,大表在右的执行效率会高一些。但是现在基本上是基于代价的时代,所以大小表的顺序和效率无关,oracle优化器会自动去进行效率优化。


四、where子句中的连接条件顺序

在基于规则时代,oracle采用自下而上的顺序来解析where子句,根据这个原理,我们一般会将可能返回行数最少的表放在最后面,where子句中有过滤条件的子句放在最后面。

但是在现在基于代价时代,这种优化都有oracle优化器帮忙优化了,所以关于表的顺序和条件的顺序已经不会影响我们的查询效率了。




<think>我们使用MyBatis-Flex作为ORM框架,重新设计序列号生成方案。主要优化点: 1. 支持多个固定值(固定字符串)的任意顺序拼接 2. 使用MyBatis-Flex的注解BaseMapper简化数据库操作 3. 重构序列号配置,增加字段用于定义各部分顺序类型 设计思路: 在配置中,我们不再使用固定的prefix、datePattern等字段,而是使用一个JSON格式的配置字段,定义序列号的各个部分。 例如: [{"type": "FIXED", "value": "ORD"}, {"type": "DATE", "pattern": "yyyyMMdd"}, {"type": "SEQUENCE", "length": 6}] 这样,我们可以灵活配置任意顺序的多个部分。 步骤: 1. 重新设计数据库 2. 定义实体类 3. 实现序列号生成服务 4. 使用MyBatis-Flex进行数据访问 详细实现: 1. 数据库设计 ```sql CREATE TABLE serial_rule ( id BIGINT AUTO_INCREMENT PRIMARY KEY, biz_type VARCHAR(50) NOT NULL COMMENT '业务类型', rule_config JSON NOT NULL COMMENT '规则配置(JSON数组)', reset_mode VARCHAR(10) DEFAULT 'NONE' COMMENT '重置模式(NONE,DAILY,MONTHLY,YEARLY)', UNIQUE KEY (biz_type) ); CREATE TABLE serial_record ( id BIGINT AUTO_INCREMENT PRIMARY KEY, biz_type VARCHAR(50) NOT NULL COMMENT '业务类型', date_key VARCHAR(20) NOT NULL COMMENT '日期键(根据重置模式生成)', current_seq BIGINT NOT NULL COMMENT '当前序列号', UNIQUE KEY (biz_type, date_key) ); ``` 2. 实体类 ```java // 序列规则实体 @Table("serial_rule") public class SerialRule { @Id(keyType = KeyType.Auto) private Long id; private String bizType; private String ruleConfig; // 存储JSON字符串 private String resetMode; // 非数据库字段,用于存储解析后的规则配置 @TableColumn(exist = false) private List<SerialRulePart> ruleParts; // getters and setters } // 序列记录实体 @Table("serial_record") public class SerialRecord { @Id(keyType = KeyType.Auto) private Long id; private String bizType; private String dateKey; private Long currentSeq; // getters and setters } // 规则部分的Java对象(用于解析JSON) public class SerialRulePart { private String type; // FIXED, DATE, SEQUENCE private String value; // 当type为FIXED时,示固定字符串 private String pattern; // 当type为DATE时,示日期格式 private Integer length; // 当type为SEQUENCE时,示序列号长度 // getters and setters } ``` 3. 序列号生成服务 ```java @Service public class SerialNumberService { @Autowired private SerialRuleMapper serialRuleMapper; @Autowired private SerialRecordMapper serialRecordMapper; @Transactional public String generate(String bizType) { // 1. 查询规则(使用MyBatis-Flex的BaseMapper,注意加锁) SerialRule rule = serialRuleMapper.selectOneByCondition(Conditions.create().eq(SerialRule::getBizType, bizType).forUpdate()); if (rule == null) { throw new RuntimeException("Serial rule not found for bizType: " + bizType); } // 解析规则配置JSON List<SerialRulePart> ruleParts = parseRuleConfig(rule.getRuleConfig()); // 2. 生成日期键(根据重置模式) String dateKey = generateDateKey(rule.getResetMode()); // 3. 获取序列记录(加锁) SerialRecord record = getOrCreateRecord(bizType, dateKey); // 4. 构建序列号 StringBuilder serial = new StringBuilder(); for (SerialRulePart part : ruleParts) { switch (part.getType()) { case "FIXED": serial.append(part.getValue()); break; case "DATE": serial.append(LocalDate.now().format(DateTimeFormatter.ofPattern(part.getPattern()))); break; case "SEQUENCE": // 序列号部分需要递增 long currentSeq = record.getCurrentSeq() + 1; serial.append(String.format("%0" + part.getLength() + "d", currentSeq)); record.setCurrentSeq(currentSeq); break; default: throw new RuntimeException("Unsupported rule part type: " + part.getType()); } } // 5. 更新序列记录 serialRecordMapper.update(record); return serial.toString(); } private List<SerialRulePart> parseRuleConfig(String ruleConfig) { // 使用Jackson或Gson解析JSON字符串 ObjectMapper objectMapper = new ObjectMapper(); try { return objectMapper.readValue(ruleConfig, new TypeReference<List<SerialRulePart>>(){}); } catch (JsonProcessingException e) { throw new RuntimeException("Failed to parse rule config", e); } } private String generateDateKey(String resetMode) { LocalDate now = LocalDate.now(); switch (resetMode) { case "DAILY": return now.format(DateTimeFormatter.BASIC_ISO_DATE); case "MONTHLY": return now.format(DateTimeFormatter.ofPattern("yyyyMM")); case "YEARLY": return now.format(DateTimeFormatter.ofPattern("yyyy")); default: // "NONE" return "FIXED"; } } private SerialRecord getOrCreateRecord(String bizType, String dateKey) { SerialRecord record = serialRecordMapper.selectOneByCondition(Conditions.create() .eq(SerialRecord::getBizType, bizType) .eq(SerialRecord::getDateKey, dateKey) .forUpdate()); if (record == null) { record = new SerialRecord(); record.setBizType(bizType); record.setDateKey(dateKey); record.setCurrentSeq(0L); serialRecordMapper.insert(record); } return record; } } ``` 4. Mapper接口(MyBatis-Flex风格) ```java @Mapper public interface SerialRuleMapper extends BaseMapper<SerialRule> { } @Mapper public interface SerialRecordMapper extends BaseMapper<SerialRecord> { } ``` 5. 规则配置示例 在serial_rule中插入一条记录: ```sql INSERT INTO serial_rule (biz_type, rule_config, reset_mode) VALUES ( 'ORDER', '[{"type":"FIXED","value":"ORD"},{"type":"DATE","pattern":"yyyyMMdd"},{"type":"SEQUENCE","length":6}]', 'DAILY' ); ``` 生成的序列号示例:ORD20240510000001 6. 优化点 - 使用JSON配置,支持任意顺序多个固定值 - 通过MyBatis-Flex的`forUpdate()`实现悲观锁 - 事务管理保证原子性 注意:在高并发场景下,使用数据库锁可能成为瓶颈,可以考虑使用分布式锁或缓存优化。 §§相关问题§§: 1. 如何避免在高并发下数据库锁成为性能瓶颈? 2. 如何使用Redis实现分布式序列号生成器? 3. 如何设计支持步长的序列号生成(例如每次增加10)? 4. 如何实现序列号生成规则的可视化配置界面?</think>### Spring Boot 3 + MyBatis-Flex 序列号生成优化方案 我将优化序列号生成方案,支持任意顺序的固定值拼接,并使用 MyBatis-Flex 作为 ORM 框架。以下是完整实现: #### 核心优化点 1. **灵活拼接**:支持任意顺序、任意数量的固定值/日期/流水号组合 2. **MyBatis-Flex 集成**:简化数据访问层 3. **配置驱动**:通过数据库配置定义序列号格式 4. **零锁设计**:使用乐观锁避免数据库锁竞争 #### 1. 优化后的数据库结构 ```sql -- 序列号规则配置 CREATE TABLE serial_rule ( id BIGINT AUTO_INCREMENT PRIMARY KEY, biz_type VARCHAR(50) NOT NULL COMMENT '业务类型', rule_name VARCHAR(100) COMMENT '规则名称', components JSON NOT NULL COMMENT '序列号组件配置', reset_mode ENUM('NONE','DAILY','MONTHLY','YEARLY') DEFAULT 'NONE' COMMENT '重置模式', seq_length INT DEFAULT 6 COMMENT '流水号长度', version INT DEFAULT 0 COMMENT '乐观锁版本号', UNIQUE KEY uniq_biz_type (biz_type) ); -- 序列号记录 CREATE TABLE serial_record ( id BIGINT AUTO_INCREMENT PRIMARY KEY, biz_type VARCHAR(50) NOT NULL COMMENT '业务类型', date_key VARCHAR(20) NOT NULL COMMENT '日期键', current_seq BIGINT NOT NULL COMMENT '当前序列值', last_used DATETIME NOT NULL COMMENT '最后使用时间', version INT DEFAULT 0 COMMENT '乐观锁版本号', UNIQUE KEY uniq_biz_date (biz_type, date_key) ); ``` #### 2. MyBatis-Flex 实体类 ```java // 序列规则实体 @Table("serial_rule") public class SerialRule { @Id(keyType = KeyType.Auto) private Long id; @Column(value = "biz_type") private String bizType; private String ruleName; @Column(typeHandler = FastjsonTypeHandler.class) private List<SerialComponent> components; private String resetMode; private Integer seqLength; private Integer version; // Getters & Setters } // 序列记录实体 @Table("serial_record") public class SerialRecord { @Id(keyType = KeyType.Auto) private Long id; @Column(value = "biz_type") private String bizType; private String dateKey; private Long currentSeq; private Date lastUsed; private Integer version; // Getters & Setters } // 序列号组件配置类 public class SerialComponent { private String type; // FIXED, DATE, SEQUENCE private String value; // 固定值或日期格式 private Integer length; // 序列号长度(仅对SEQUENCE有效) // Getters & Setters } ``` #### 3. 序列号生成服务(支持任意拼接) ```java @Service public class SerialNumberService { @Autowired private SerialRuleMapper serialRuleMapper; @Autowired private SerialRecordMapper serialRecordMapper; @Transactional public String generateSerialNumber(String bizType) { // 1. 获取配置规则 SerialRule rule = serialRuleMapper.selectOneByQuery(QueryWrapper.create() .where(SERIAL_RULE.BIZ_TYPE.eq(bizType))); if (rule == null) { throw new RuntimeException("序列规则未配置: " + bizType); } // 2. 生成日期键 String dateKey = generateDateKey(rule.getResetMode()); // 3. 获取序列记录(使用乐观锁) SerialRecord record = getSerialRecordWithRetry(bizType, dateKey, rule.getSeqLength()); // 4. 构建序列号 return buildSerialNumber(rule.getComponents(), dateKey, record.getCurrentSeq()); } private String generateDateKey(String resetMode) { LocalDate now = LocalDate.now(); return switch (resetMode) { case "DAILY" -> now.format(DateTimeFormatter.BASIC_ISO_DATE); case "MONTHLY" -> now.format(DateTimeFormatter.ofPattern("yyyyMM")); case "YEARLY" -> now.format(DateTimeFormatter.ofPattern("yyyy")); default -> "FIXED"; }; } private SerialRecord getSerialRecordWithRetry(String bizType, String dateKey, int defaultLength) { final int MAX_RETRY = 3; for (int i = 0; i < MAX_RETRY; i++) { // 查询记录 SerialRecord record = serialRecordMapper.selectOneByQuery(QueryWrapper.create() .where(SERIAL_RECORD.BIZ_TYPE.eq(bizType)) .and(SERIAL_RECORD.DATE_KEY.eq(dateKey))); if (record == null) { // 创建新记录 record = new SerialRecord(); record.setBizType(bizType); record.setDateKey(dateKey); record.setCurrentSeq(0L); record.setLastUsed(new Date()); record.setVersion(0); if (serialRecordMapper.insert(record) > 0) { return record; } } else { // 更新序列号(乐观锁) Long newVersion = record.getVersion() + 1L; int updated = serialRecordMapper.updateByQuery( UpdateWrapper.create() .set(SERIAL_RECORD.CURRENT_SEQ, record.getCurrentSeq() + 1) .set(SERIAL_RECORD.LAST_USED, new Date()) .set(SERIAL_RECORD.VERSION, newVersion) .where(SERIAL_RECORD.ID.eq(record.getId())) .and(SERIAL_RECORD.VERSION.eq(record.getVersion())) ); if (updated > 0) { record.setCurrentSeq(record.getCurrentSeq() + 1); return record; } } } throw new RuntimeException("获取序列号失败,请重试"); } private String buildSerialNumber(List<SerialComponent> components, String dateKey, long sequence) { StringBuilder sb = new StringBuilder(); for (SerialComponent comp : components) { switch (comp.getType()) { case "FIXED": sb.append(comp.getValue()); break; case "DATE": if (!"FIXED".equals(dateKey)) { sb.append(LocalDate.now().format(DateTimeFormatter.ofPattern(comp.getValue()))); } break; case "SEQUENCE": int length = comp.getLength() != null ? comp.getLength() : 6; sb.append(String.format("%0" + length + "d", sequence)); break; default: throw new IllegalArgumentException("未知组件类型: " + comp.getType()); } } return sb.toString(); } } ``` #### 4. MyBatis-Flex Mapper 接口 ```java @Mapper public interface SerialRuleMapper extends BaseMapper<SerialRule> { // MyBatis-Flex 自动生成基础方法 } @Mapper public interface SerialRecordMapper extends BaseMapper<SerialRecord> { // MyBatis-Flex 自动生成基础方法 } ``` #### 5. 配置示例(支持任意顺序组合) ```sql -- 订单号:固定值 + 年月日 + 流水号 (ORD-20240510-0001) INSERT INTO serial_rule (biz_type, rule_name, components, reset_mode, seq_length) VALUES ('ORDER', '订单号', '[{"type": "FIXED", "value": "ORD-"}, {"type": "DATE", "value": "yyyyMMdd"}, {"type": "FIXED", "value": "-"}, {"type": "SEQUENCE", "length": 4}]', 'DAILY', 4); -- 发票号:年月 + 固定值 + 流水号 (202405-INV-000001) INSERT INTO serial_rule (biz_type, rule_name, components, reset_mode, seq_length) VALUES ('INVOICE', '发票号', '[{"type": "DATE", "value": "yyyyMM"}, {"type": "FIXED", "value": "-INV-"}, {"type": "SEQUENCE", "length": 6}]', 'MONTHLY', 6); -- 客户ID:固定值 + 年 + 流水号 (CUST-24-00000001) INSERT INTO serial_rule (biz_type, rule_name, components, reset_mode, seq_length) VALUES ('CUSTOMER', '客户ID', '[{"type": "FIXED", "value": "CUST-"}, {"type": "DATE", "value": "yy"}, {"type": "FIXED", "value": "-"}, {"type": "SEQUENCE", "length": 8}]', 'YEARLY', 8); ``` #### 6. 使用示例 ```java @RestController @RequestMapping("/api/serial") public class SerialNumberController { @Autowired private SerialNumberService serialNumberService; // 生成订单号:ORD-20240510-0001 @GetMapping("/order") public String generateOrderNo() { return serialNumberService.generateSerialNumber("ORDER"); } // 生成发票号:202405-INV-000001 @GetMapping("/invoice") public String generateInvoiceNo() { return serialNumberService.generateSerialNumber("INVOICE"); } } ``` ### 关键优化说明 1. **灵活组件配置**: - 使用 JSON 数组存储组件配置 - 支持任意顺序的组件组合(固定值、日期、流水号) - 每个组件可独立配置属性(长度、格式等) 2. **MyBatis-Flex 集成**: - 使用 `@Table` `@Column` 注解简化实体映射 - 利用 `QueryWrapper` `UpdateWrapper` 构建类型安全的查询 - 自动生成基础 CRUD 方法 3. **乐观锁实现**: ```java // 更新时检查版本号 int updated = serialRecordMapper.updateByQuery( UpdateWrapper.create() .set(...) .where(SERIAL_RECORD.ID.eq(record.getId())) .and(SERIAL_RECORD.VERSION.eq(record.getVersion())) ); ``` - 通过版本号避免并发冲突 - 失败时自动重试(最多3次) 4. **零锁设计优势**: - 避免数据库行锁导致的性能瓶颈 - 支持更高并发场景 - 减少死锁风险 ### 性能优化建议 1. **本地缓存**:使用 Caffeine 缓存频繁访问的规则配置 ```java @Cacheable(value = "serialRules", key = "#bizType") public SerialRule getRule(String bizType) { return serialRuleMapper.selectOneByQuery(...); } ``` 2. **批量生成**:支持一次性生成多个序列号 ```java public List<String> batchGenerate(String bizType, int count) { // 实现逻辑类似单号生成,但一次获取多个序列号 } ``` 3. **监控告警**:添加序列号使用监控 ```java // 在生成方法中添加监控点 Metrics.counter("serial.generate", "bizType", bizType).increment(); ``` 4. **流水号预分配**:使用 Redis 或数据库序列预生成批号 ### 示例输出 - 订单号:`ORD-20240510-0001` - 发票号:`202405-INV-000001` - 客户ID:`CUST-24-00000001`
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值