背景本次SQL优化针对javaweb中的表查询。部分网络架构图展示了对业务的简要描述。N台机器向服务器发送业务数据，服务器程序将数据存入MySQL数据库。服务器中的javaweb程序将数据显示在网页上，供用户查看。原来的数据库设计windows单机主从分离已经分表分库，按年分库，按天分表。原来每张表的查询效率在20w左右。原来的查询效率是3天。数据查询是70-80s。分页只能用java来完成。排查前台慢或后台慢如果配置了druid，可以直接在druid页面查看sql的执行时间和uri的请求时间。在后台代码中使用System.currentTimeMillis计算时间差。结论：后台慢，查询sql慢。sql有什么问题？使用explain检查必要字段的执行计划。在where条件下，只有time以外的一个字段使用索引。查询优化去除不必要的字段不是那么明显去除不必要的嵌套查询的效果不是那么明显分解sql分解unionall的操作，例如（一个unionallsql也很长）selectaafrombb_2018_10_01leftjoin...on..leftjoin..on..where..unionallselectaafrombb_2018_10_02leftjoin...on..leftjoin..on..where..unionallselectaafrombb_2018_10_03leftjoin...on..leftjoin..on...where..unionallselectaafrombb_2018_10_04leftjoin...on..leftjoinon。.where..把上面的sql分解成若干条sql执行，最后汇总数据，大概快了20s。selectaafrombb_2018_10_01leftjoin...on..leftjoin..on..where..异步执行分解sql操作使用java异步编程，异步执行分解sql最后汇总数据。这里用到了CountDownLatch和ExecutorService，示例代码如下：//获取时间段内的所有天数Listdays=MyDateUtils.getDays(requestParams.getStartTime(),requestParams.getEndTime());//lengthofdaysintlength=days.size();//初始化组合集合并指定大小，防止数组越界List<你想要的数据类型>list=Lists.newArrayListWithCapacity(length);//初始化线程池ExecutorServicepool=Executors.newFixedThreadPool(length);//初始化计数器CountDownLatchlatch=newCountDownLatch(length);//查询时间并合并for(Stringday:days){Mapparam=Maps.newHashMap();//param组装查询条件pool.submit(newRunnable(){@Overridepublicvoidrun(){try{//mybatisquerysql//汇总结果list.addAll(queryresults);}catch(Exceptione){logger.error("getTimeexception",e);}finally{latch.countDown();}}});}try{//等待所有查询结束latch.await();}catch(InterruptedExceptione){e.printStackTrace();}//list是一个汇总集合//如果有需要，可以组装自己想要的业务数据，计算等，如果没有，结果就没有了。优化MySQL配置需要20-30秒。以下是我的配置示例。添加了skip-name-resolve，快4-5秒。其他配置自行决定[client]port=3306[mysql]no-beepdefault-character-set=utf8[mysqld]server-id=2relay-log-index=slave-relay-bin.indexrelay-log=slave-relay-binslave-skip-errors=all#跳过所有错误skip-name-resolveport=3306datadir="D:/mysql-slave/data"character-set-server=utf8default-storage-engine=INNODBsql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"log-output=FILEgeneral-log=0general_log_file="WINDOWS-8E8V2OD.log"slow-query-log=1slow_query_log_file="WINDOWS-8E8V2OD-slow.log"long_query_time=10#BinaryLogging.#log-bin#ErrorLogging.log-error="WINDOWS-8E8V2OD.err"#整个数据库的***连接数（用户）max_connections=1000#每个客户端连接***允许的错误数max_connect_errors=100#表描述符缓存大小，whichcanreducefilesOpen/closetimestable_open_cache=2000#服务可以处理的请求包的最大大小和服务可以处理的最大请求大小e（在处理大的BLOB字段时是相当有必要的）#每个连接都是独立的大小。size是动态增加的max_allowed_pa??cket=64M#发生排序时由每个线程分配sort_buffer_size=8M#发生fulljoint时，每个线程分配join_buffer_size=8M#保留多少个线程在缓存中重用thread_cache_size=128#这样允许应用程序同时给线程系统一个提示给出一次渴望运行的线程数。thread_concurrency=64#查询缓存query_cache_size=128M#Onlyresultssmallerthanthissettingvalue#InnoDBuses用于保存索引和原始数据的缓冲池#这里设置的越大，访问表中数据所需的磁盘I/O就越少。#在一个独立使用的数据库服务器上，可以将这个变量设置为80%服务器的物理内存大小#不要设置太大，否则可能会因为物理内存的竞争导致操作系统的pagechangethrashing。innodb_buffer_pool_size=1G#用于同步IO操作的IO线程数#这个值在Unix下硬编码为4，但在Windows上磁盘I/O在较大的值下可能表现更好。innodb_read_io_threads=16innodb_write_io_threads=16#InnoDB核心允许的线程数。#***该值取决于应用程序、硬件和操作系统的调度方式。#过高的值可能导致互斥抖动线程。innodb_thread_concurrency=9#0表示日志大约每秒只写入日志文件，日志文件Flush到磁盘。#1、InnoDB会在每次commit后将事务日志flush(fsync)到磁盘#2的意思是每次commit后都会将日志写入日志文件，但是日志文件只会每隔一秒flush到磁盘innodb_flush_log_at_trx_commit=2#的用于缓冲日志数据的缓冲区大小。innodb_log_buffer_size=16M#日志组中每个日志文件的大小。innodb_log_file_size=48M#日志组中的文件总数。innodb_log_files_in_group=3#回滚之前，如何InnoDB事务等待锁被批准的时间应该很长。#InnoDB在自己的锁表中自动检测事务死锁并回滚事务。#如果使用了LOCKTABLES指令，或者在同一个事务中使用了InnoDB以外的事务其他事务安全的存储引擎#那么可能会出现死锁，InnoDB无法注意到。#在这种情况下，这个超时值对解决这个问题很有帮助problem.innodb_lock_wait_timeout=30#启用计时event_scheduler=ON多长时间被批准。#InnoDB在自己的锁表中自动检测事务死锁并回滚事务。#如果使用LOCKTABLES指令，或者在同一个事务中使用InnoDB以外的其他事务安全存储引擎#那么可能会发生死锁，而InnoDB是察觉不到的。#在这种情况下，超时值对于解决这个问题很有帮助。innodb_lock_wait_timeout=30#开启定时event_scheduler=ON根据业务，加上过滤条件Fast4-5swhere条件中对time条件以外的字段创建联合索引的效果不是那么明显使用innerjoin方式来在where条件中关联索引条件对于这一点，我感到很意外。原来的sql，b是索引selectaafrombb_2018_10_02leftjoin...on..leftjoin..on..whereb='xxx'前面应该是unionall，unionall是一一执行的，***的结果概括。改为selectaafrombb_2018_10_02leftjoin...on..leftjoin..on..innerjoin(select'xxx1'asb2unionallselect'xxx2'asb2unionallselect'xxx3'asb2unionallselect'xxx3'asb2)tonb=t.b2结果更快3-4s性能瓶颈根据到上面的Operation，3天的查询效率达到了8s左右，简直不能再快了。查看mysql的cpu使用率和内存使用率都不高，为什么查询这么慢，3天最多60w条数据，而且关联的也是一些字典表，所以不是这样的。继续利用网上提供的资料，一系列的骚操作，基本没用，无计可施。由于sql优化的分析，环境对比已经ok了。试想是不是磁盘读写问题。将优化后的程序部署到不同的现场环境中。一个带ssd，一个不带ssd。发现查询效率差别很大。用软件测试后发现，ssd的读写速度为700-800M/s，普通机械硬盘的读写速度为70-80M/s。优化结果和结论优化结果：预期。优化结论：sql优化不仅仅是sql本身的优化，还取决于自身的硬件条件，其他应用的影响，自身代码的优化。小结优化的过程是个人的经验和考验。珍惜这次机会，不要做只写业务代码的程序员。以上希望对大家的思考有所帮助，不足之处望指正。
