- 线程不是越多越好,线程会被数据库连接阻塞
@Component
public class ThreadPoolTest {
@Autowired
private DataSource dataSource;
private final static ExecutorService EXECUTOR_SERVICE = new ThreadPoolExecutor(
Integer.MAX_VALUE,
Integer.MAX_VALUE,
60L,
TimeUnit.SECONDS,
new LinkedBlockingDeque<>()
);
@PostConstruct
public void init() throws SQLException, InterruptedException {
// 线程池尺寸为10
long l1 = System.currentTimeMillis();
CountDownLatch countDownLatch = new CountDownLatch(30);
for (int i = 0; i < 30; i++) {
EXECUTOR_SERVICE.execute(() -> {
try {
Connection connection = dataSource.getConnection();
Thread.sleep(1000);
countDownLatch.countDown();
connection.close();
} catch (SQLException | InterruptedException e) {
e.printStackTrace();
}
});
}
countDownLatch.await();
long l2 = System.currentTimeMillis();
System.out.println("总耗时: " + (l2 - l1) + "ms"); // 总耗时: 3018ms
}
}
如果认为线程越多越好那么就大错特错了。其实我们每个项目的数据库连接是固定的,如果现在已有的线程占用的数据库连接已经超过了最大的连接数,那么后面的线程从数据库连接池中获取连接时就会被阻塞。
比如上面的案例,假如每个线程处理结果的时间是1s,那么理想的完成时间是1s左右,但是结果是3s。原因就是在每10个线程来占用了数据库连接之后,后面的线程来获取数据库连接就只有阻塞了。
- 避免大事务的发生
@Service
public class TransactionTestService {
// update事务,停顿10s模拟大事务,遇见最大的事务执行了几个小时
@Transactional(rollbackFor = Throwable.class)
public void update(String name, Date birthday) {
Flower flower = new Flower();
flower.setName(name);
flower.setBirthday(birthday);
flowerMapper.update(flower);
try {
Thread.sleep(10000);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
@Component
public class ThreadPoolTest {
@Autowired
private DataSource dataSource;
private final static ExecutorService EXECUTOR_SERVICE = new ThreadPoolExecutor(
Integer.MAX_VALUE,
Integer.MAX_VALUE,
60L,
TimeUnit.SECONDS,
new LinkedBlockingDeque<>()
);
@Autowired
private TransactionTestService transactionTestService;
@Autowired
private FlowerMapper flowerMapper;
@PostConstruct
public void init2() {
EXECUTOR_SERVICE.execute(() -> {
transactionTestService.update("烟花", new Date());
});
EXECUTOR_SERVICE.execute(() -> {
try {
// 等待1s,让大事务update先执行
Thread.sleep(1000);
} catch (InterruptedException e) { }
long l1 = System.currentTimeMillis();
Flower flower = new Flower();
flower.setName("烟花");
flower.setBirthday(new Date());
flowerMapper.insert(flower);
long l2 = System.currentTimeMillis();
System.out.println("插入完成: 耗时: " + (l2 - l1) + "ms"); // 插入完成: 耗时: 9207ms
});
}
}
事务虽然好,但是大事务可能会让系统的系统变得很差。可以看见上面的大update方法占用了事务10s的时间,现在第一个线程首先调用了这个方法,占用10s事务。然后第二个线程想要插入一条数据,却必须等到第一个线程的大事务结束之后才能进行插入。这也是为什么第二个线程插入一条数据就需要9s的时间的原因(等待的1s+9s的事务+插入数据的耗时)。
并且如果这个事务的耗费时间很长,那么你就很可能会经常受到 com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction的异常,报告这个线程获取事务失败了。
这种场景通常见于第一个线程执行的是大更新事务、大删除事务,对于大的查询和插入倒是没有影响。
- SQL中传入的批量数太大
在for循环里面进行单个查询是一个很耗费性能是操作,通常会批量地将字段使用 in 的方式传入到SQL中然后进行一次性的查询。
@Component
public class ThreadPoolTest {
@Autowired
private FlowerMapper flowerMapper;
@PostConstruct
public void init3() {
String name = "name";
List<String> nameList = new ArrayList<>();
for (int i = 0; i< 50000;i++) {
String theName = name + "-" + i;
nameList.add(theName);
}
long l1 = System.currentTimeMillis();
List<Flower> flowers = flowerMapper.selectByNames(nameList);
long l2 = System.currentTimeMillis();
System.out.println("查询耗时: " + (l2 - l1) + "ms"); // 查询耗时: 89631ms
}
}
我再这个mapper对应的表里面插入了4千万条数据,表有三个字段,其中查询字段带有索引。接下来使用50000个name的列表传入批量查询,结果耗时了90s。
但是如果我把这个批量的尺寸缩小至10000之后,一次查询耗时用了不到3s,就算是查询5次,耗时也是15s。性能是以前的6倍。
这种情况在大表中会表现得很明显,如果一个表很大,同时它的列数很多,那么当使用批量查询时到达一个阈值之后,性能就会变得特别差。列数较多的表这个批量的数值可能到达几千的数值就会出现。
- 慎用子查询
在一些SQL操作中,关联查询通常可以使用join操作,也可以使用子查询完成,之前,通常情况下我都比较喜欢使用子查询来完成一个这个操作。但是后来遇见了一个慢SQL之后,子查询居然就是导致慢SQL的罪魁祸首。
explain update l_flower
set name = ''
where id in (
select flower_id
from o_flower_owner
where flower_id between 100000 and 102000
);
explain
update l_flower l
left join o_flower_owner o
on l.id = o.flower_id
set l.name = ''
where o.flower_id between 100000 and 102000
l_flower 和 o_flower_owner 都是一张500w左右数据的表。其中o_flower_owner 的flower_id关联了l_flower表的id。 可以看见,使用过子查询的执行计划上面显示SQL会扫描400w行数据以上,但是当使用了join操作之后,这个数据行数就比那成了1行。
原因即是使用子查询 差生了 >>DEPEDENT SUBQUERY<< 的标记,这个标记说明,SQL是将外层的数据先查询出来,再查询子查询的内容,然后将外部查询和子查询的数据进行筛选。这样的SQL基本就扫表操作了。因此速度很慢
- 使用limit的同时需要使用order by 语句
使用数据库为MySQL, 在未使用排序的时候,数据库会使用。扫描整个表,但是当使用索引键加上order by之后,却只扫描了一千行。
explain SELECT * FROM world.l_flower limit 500;
explain SELECT * FROM world.l_flower order by id limit 500;