亿级数据mysql优化

野性酷女 2022-06-05 06:23 342阅读 0赞

# 亿级数据优化 #

## 情况简介 ##

用户分析系统以用户的心跳数据为依据，统计查询用户的各种情况。心跳数据很多，经过去重，去无效，数据量还是在2亿/月的水平。普通的查询在这个量级的数据库上根本查不出来，为此，分表分库、优化查询、多线程查询就很有必要了。

## 分表分库 ##

对于大数据查询，我的第一反应就是分表分库，我之前对分表分库已经听的很多了，但自己之前并没有真的做过分表分库。我经常说分表分库，经常听分表分库，对分表分库的技术充满了兴趣，所以这次，我的意见就是上分表分库。分库分表就是按一定规则把一张大表的数据切成多个小表，查询时分组查询多个小表再把结果集集合起来。

分表其实只是对数据表的数据量的减少。比如一个1亿的表，经过分表后，会分成4个千万级别的表，对于查询sql，会在千万级别的表中执行，并把结果合并起来。在亿级查询的时间比千万级会提高很多，这个就是分表的作用。但是分表仅仅是数据量的减少，并没有去解决查询慢的问题，如果一个查询慢，及时使用分表，它还是很慢。

我们最终用mycat这个数据库中间件来进行分库分表，mycat启动后，会开启一个模拟mysql的服务端，对于插入的sql，它可以按规则自主放入对应的表中，对应查询的sql，它会自动的去各个数据库查询，并自动整合结果。

## 优化查询 ##

经过对数据库的了解后，我发现，数据库查询的最该优化的地方还是数据库优化。首先就是

> 加索引

索引要加给需要查询的列，对于执行的sql，我们要使用`EXPLAIN`进行查询分析，看查询是否走的索引。

如果查询返回的数据过多，会导致cpu和内存占用过大，用`show profile for query`去查看查询状态时，sending data过大时，就可能是返回数据过多。sending data的耗时来源于sending和sort的时间之和，去掉排序，时间也能快很多。还要关注一下limit这里，比如`limit 2000,60`它这里实际会走一个扫描前2000个，如果有条件就比较好了，比如`id>2000 limit 1,60`这样其实就好的很多。所以对于返回结果比较大的查询，引出了第二个策略

> 切分条件

切分条件就是查询的时候将条件分的细一些，这样查出的每段数据都很少一些，limit的时候，扫描也少一些。

## 多线程查询 ##

既然做了切分条件，那么多线程查询也是必不可少的。多线程查询再聚合数据，这才能将时间效率提到最高，对与多线程，主要还是java后台做的，多线程的常见操作有，

### CountDownLatch同步 ###

即在CountDownLatch中，代码多线程执行，主线程挂起，等CountDownLatch的那些多线程全部执行完毕后，在去执行主线程。

### LinkedBlockingQueue/ConcurrentLinkedDeque队列 ###

LinkedBlockingQueue/ConcurrentLinkedDeque是一个线程安全队列，那个block单词是锁的意思，其实就是堵塞的。在这里的查询就需要队列呀，查询条件是一个list，要保证list的每一个条件查询一次，不能重复查询，这里，就必须要用队列了。queue有两个方法add()、pull()，一个出对，一个入队。