Mysql⼤数据量查询优化思路详析
⽬录
1. 千万级别⽇志查询的优化
2. ⼏百万⿊名单库的查询优化
3. Mybatis批量插⼊处理问题
项⽬场景：
Mysql⼤表查询优化，理论上千万级别以下的数据量Mysql单表查询性能处理都是可以的。
问题描述：
在我们线上环境中，出现了mysql⼏千万级别的⽇志查询、⼏百万级别的⿊名单库查询分页查询及条件查询都慢的问题，针对Mysql表优化做了⼀些优化处理。
原因分析：
⾸先说⼀下⽇志查询，在Mysql中如果索引加的⽐较合适，⾛索引情况下千万级别查询不会超过⼀秒，Mysql查询的速度和检索的数据条数有关。在Mybatis中，分页查询是先执⾏Count记录总数，再执⾏limit a,b 的⽅式来进⾏的，⽽Mysql的Count计数⽅式是将所有的数据过滤⼀遍进⾏累加，因此当⽇志表数据过千万时，统计⼀次就是⼗⼏秒钟的时间（这⾥是服务器环境，本地情况下甚⾄是⼏分钟）。
limit a,b的⽅式也⼀样，Mysql查询时会先⼀条⼀条数到第a条，然后向后再数b条作为查询结果，因此当起始⾏数越来越⼤时查询同样会变得很慢，也就是当你点第⼀页时可能⼀下就查出来了，当你点最后⼀页的时候可能⼏⼗秒才能查出来。
⿊名单库查询优化同理，也是需要通过条件优化。
在进⾏⼤批量数据落库时，使⽤的Mybatis批量插⼊，发现当批次数据超过3000时速度会急剧变慢，这是⼀个Mybatis娘胎⾥⾃带的问题，也需要进⾏解决。
解决⽅案：
这⾥只简单说明优化的⼏个⽅向。
1. 千万级别⽇志查询的优化
1. ⾸先说下⽇志查询，重点是优化⽆条件是分页查询，在⽆条件时，不使⽤MyBatis的分页插件，⽽是⾃⼰⼿写⼀个分
页查询，由于MySql的count耗时过长，我们先优化他。
2. 优化Count：⽇志表的数据只增，不会出现中间某条删除，所以他的数据可以理解成是连续的，我们可以在内存中
直接进⾏计数，记录count总数，或者给表添加⼀个⾃增的ID字段，直接select max(id)就是总数量，这样count查询的效率会提升到毫秒级别。
3. ⾃定义分页查询：分页查询中使⽤优化后的count记录总数，然后使⽤（page - 1）* pageSize + 1公式计算出当前页的
最⼩ID，然后将limit a,b 的Sql语句改为where ID > 最⼩ID limit b的⽅式，这样查询就会⾛索引先将⼩于最⼩ID的
数据过滤掉，再进⾏查询，经过第⼆步和第三步的优化后分页查询效率缩短到了⼀秒内，并且不会随着页数的增长⽽变慢。
4. 条件查询：条件查询只能设置合适的索引，另外慎⽤like '%条件%‘的⽅式进⾏匹配查询，这样会导致索引失效全局
检索，模糊查询尽量使⽤like '条件%' 的⽅式进⾏最左匹配，也可以使⽤explain+sql语句的⽅式来查看sql语句的执⾏效率，是否⾛了所有啥的来针对性的优化，加好合适的索引、优化查询语句后通常⼀千万以内的数据查询效率会在3秒内。
粘出⾃定义分页查询结果封装：
// ⼿动count
Integer total = logPushService.queryBackCount(resMap);//查询数量
// ⼿动查询结果
List<InterceptInfo> ls = logPushService.queryBackByPage(resMap);
PageInfo pageInfo = new PageInfo();
pageInfo.setTotal(total);
pageInfo.setPageSize(limit);
pageInfo.setList(ls);
pageInfo.setPageNum(pn);
2. ⼏百万⿊名单库的查询优化
⿊名单库查询优化只能通过加合适的索引和优化SQL语句来优化，百万级别数据松松的在Mysql和Mybatis的承受范围内，这⾥是由于⿊名单库不是使⽤递增的，有可能会增加也有可能会删除，所以只能使⽤优化索引和SQL的⽅式进⾏优
化。
另外，Mybatis框架提供了重写分页查询count统计语句的⽅法，只需要将count语句命名为查询⽅法_COUNT即可，例如分页查询的语句⽅法是query，那么重写的统计⽅法即为query_COUNT
<select id="query_COUNT" parameterType="java.util.Map" resultType="java.lang.Long">
SELECT
count(0)
from nms_intercept_info${map.week}
where 1=1
<if test="map.id!=null and map.id!='' ">
AND id>#{map.id}
</if>
<if test="map.url!=null and map.url!='' ">
AND spliturl=#{map.url}
</if>
<if test="map.startTime!=null and map.startTime!='' ">
AND time <![CDATA[>=]]> #{map.startTime}
</if>
<if test="dTime!=null dTime!='' ">
AND time <![CDATA[<=]]> #{dTime}
</if>
<if test="pe!=null pe!='' ">
AND bigType = #{pe}
</if>
</select>
3. Mybatis批量插⼊处理问题
Mybatis批量插⼊语句中的类集合⼤⼩不能超过五千，三千是最佳，这是测试出来的结果，考虑到的原因是Mybatis会将类做反射，这个太影响效率，因此批量插⼊时要注意这个，如果你能够三千三千的批量处理就限制⼀下，不要让每批数据超过3000，数据量过⼤时也可以使⽤异步⾮阻塞的⽅式来插⼊。
异步⾮阻塞代码（只是步骤样例，存在代码缺失）：
// 执⾏全量HMD导⼊任务的线程池
public final static ExecutorService importHasPool = wFixedThreadPool(10);
public final static CompletionService<DoExcelResult> importHasPoolService = new ExecutorCompletionService<>(importHasPool);
public synchronized DoExcelResult example() {
// 开始执⾏导⼊
// 写到这⾥⾯⽅法最后会⾃动关闭
long startTime = System.currentTimeMillis();
sql语句优化方式// 定义⼀个集合，记录Callable的执⾏结果,Callable是带返回值的Runable
List<Future<DoExcelResult>> futures = new ArrayList<>();
while ((str = adLine()) != null) {
if (list.size() > 5000) { // 5000插⼊⼀次
List<String> list1 = pyDepth(list);
list.clear();
// BlackInfoHasImportlCallable是实现了Callable接⼝的实现类，Callable是带返回值的Runable
Future submit = SysThreadPoolCenter.importHasPoolService.submit(new BlackInfoHasImportlCallable(list1, blacklistInfoMapper));
futures.add(submit);
}
}
// 等待执⾏结果
for (Future<DoExcelResult> future : futures) {
try {
// 2. 时会获取返回值，线程没执⾏完毕就等待等待执⾏结果
DoExcelResult doExcelResult = ();
result.SuccessNum() + SuccessNum());
result.ContinueNum() + ContinueNum());
result.ErrorNum() + ErrorNum());
} catch (Exception e) {
<(e);
}
}
// 循环结束代表所有线程执⾏完毕
result.setTimeCon((System.currentTimeMillis() - startTime)/1000.0);
BlacklistService.isDoing = false;
} catch (Exception e) {
BlacklistService.isDoing = false;
<(e);
}
BlacklistService.isDoing = false;
return result;
}
限制每批3000条：
if (ls.size() >= 3000) {
//每次保存3000
double sum = il(ls.size() / 3000f);
for (int i = 0; i < sum; i++) {
total += blacklistDao.saveBatch(ls.subList(i * 3000, ((i + 1) * 3000) > ls.size() ? ls.size() : (i + 1) * 3000));
}
} else {
total = blacklistDao.saveBatch(ls);
}
如果你数据库⽤的不是mysql，⽽是CK或者其他的⼤数据处理数据库，批量插⼊可能要求每秒⼏万条⼏⼗万条，这时就不再适合使⽤Myabtis框架了，建议使⽤JDBC连接的⽅式，⾃⼰写代码拼接sql语句，再使⽤jdbc连接执⾏（使⽤线程池），效率上会快很多。
到此这篇关于Mysql⼤数据量查询优化思路详析的⽂章就介绍到这了，更多相关Mysql⼤数据量查询优化内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。