|
|
51CTO旗下网站
|
|
移动端

MySQL之SQL优化实战记录

本次SQL优化是针对javaweb中的表格查询做的。N个机台将业务数据发送至服务器,服务器程序将数据入库至MySQL数据库。服务器中的javaweb程序将数据展示到网页上供用户查看。

作者:小祝特烦恼来源:数据库开发|2018-10-19 12:47

背景

本次SQL优化是针对javaweb中的表格查询做的。

部分网络架构图

MySQL之SQL优化实战记录

业务简单说明

N个机台将业务数据发送至服务器,服务器程序将数据入库至MySQL数据库。服务器中的javaweb程序将数据展示到网页上供用户查看。

原数据库设计

  • windows单机主从分离
  • 已分表分库,按年分库,按天分表
  • 每张表大概20w左右的数据

原查询效率

3天数据查询70-80s

目标

3-5s

业务缺陷

无法使用sql分页,只能用java做分页。

问题排查

前台慢 or 后台慢

  • 如果你配置了druid,可在druid页面中直接查看sql执行时间和uri请求时间
  • 在后台代码中用System.currentTimeMillis计算时间差。

结论 : 后台慢,且查询sql慢

sql有什么问题

  • sql拼接过长,达到了3000行,有的甚至到8000行,大多都是union all的操作,且有不必要的嵌套查询和查询了不必要的字段
  • 利用explain查看执行计划,where条件中除时间外只有一个字段用到了索引

备注 : 因优化完了,之前的sql实在找不到了,这里只能YY了。

查询优化

去除不必要的字段

效果没那么明显

去除不必要的嵌套查询

效果没那么明显

分解sql

  • 将union all的操作分解,例如(一个union all的sql也很长)
  1. select aa from bb_2018_10_01 left join ... on .. left join .. on .. where .. 
  2. union all 
  3. select aa from bb_2018_10_02 left join ... on .. left join .. on .. where .. 
  4. union all 
  5. select aa from bb_2018_10_03 left join ... on .. left join .. on .. where .. 
  6. union all 
  7. select aa from bb_2018_10_04 left join ... on .. left join .. on .. where .. 

将如上sql分解成若干个sql去执行,最终汇总数据,最后快了20s左右。

  1. select aa from bb_2018_10_01 left join ... on .. left join .. on .. where .. 

将分解的sql异步执行

利用java异步编程的操作,将分解的sql异步执行并最终汇总数据。这里用到了CountDownLatch和ExecutorService,示例代码如下:

  1. // 获取时间段所有天数 
  2.        List<String> days = MyDateUtils.getDays(requestParams.getStartTime(), requestParams.getEndTime()); 
  3.        // 天数长度 
  4.        int length = days.size(); 
  5.        // 初始化合并集合,并指定大小,防止数组越界 
  6.        List<你想要的数据类型> list = Lists.newArrayListWithCapacity(length); 
  7.        // 初始化线程池 
  8.        ExecutorService pool = Executors.newFixedThreadPool(length); 
  9.        // 初始化计数器 
  10.        CountDownLatch latch = new CountDownLatch(length); 
  11.        // 查询每天的时间并合并 
  12.        for (String day : days) { 
  13.            Map<String, Object> param = Maps.newHashMap(); 
  14.            // param 组装查询条件 
  15.  
  16.            pool.submit(new Runnable() { 
  17.                @Override 
  18.                public void run() { 
  19.                    try { 
  20.                        // mybatis查询sql 
  21.                        // 将结果汇总 
  22.                        list.addAll(查询结果); 
  23.                    } catch (Exception e) { 
  24.                        logger.error("getTime异常", e); 
  25.                    } finally { 
  26.                        latch.countDown(); 
  27.                    } 
  28.                } 
  29.            }); 
  30.        } 
  31.  
  32.  
  33.        try { 
  34.            // 等待所有查询结束 
  35.            latch.await(); 
  36.        } catch (InterruptedException e) { 
  37.            e.printStackTrace(); 
  38.        } 
  39.  
  40.        // list为汇总集合 
  41.        // 如果有必要,可以组装下你想要的业务数据,计算什么的,如果没有就没了 

结果又快了20-30s

优化MySQL配置

以下是我的配置示例。加了skip-name-resolve,快了4-5s。其他配置自行断定

  1.  [client] 
  2. port=3306 
  3. [mysql] 
  4. no-beep 
  5. default-character-set=utf8 
  6. [mysqld] 
  7. server-id=2 
  8. relay-log-index=slave-relay-bin.index 
  9. relay-log=slave-relay-bin  
  10. slave-skip-errors=all #跳过所有错误 
  11. skip-name-resolve 
  12.  
  13. port=3306 
  14. datadir="D:/mysql-slave/data" 
  15. character-set-server=utf8 
  16. default-storage-engine=INNODB 
  17. sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 
  18.  
  19. log-output=FILE 
  20. general-log=0 
  21. general_log_file="WINDOWS-8E8V2OD.log" 
  22. slow-query-log=1 
  23. slow_query_log_file="WINDOWS-8E8V2OD-slow.log" 
  24. long_query_time=10 
  25.  
  26. Binary Logging. 
  27. # log-bin 
  28.  
  29. # Error Logging. 
  30. log-error="WINDOWS-8E8V2OD.err" 
  31.  
  32.  
  33. # 整个数据库最大连接(用户)数 
  34. max_connections=1000 
  35. # 每个客户端连接最大的错误允许数量 
  36. max_connect_errors=100 
  37. # 表描述符缓存大小,可减少文件打开/关闭次数 
  38. table_open_cache=2000 
  39. # 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB字段一起工作时相当必要)   
  40. # 每个连接独立的大小.大小动态增加 
  41. max_allowed_packet=64M 
  42. # 在排序发生时由每个线程分配 
  43. sort_buffer_size=8M 
  44. # 当全联合发生时,在每个线程中分配  
  45. join_buffer_size=8M 
  46. # cache中保留多少线程用于重用 
  47. thread_cache_size=128 
  48. # 此允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量. 
  49. thread_concurrency=64 
  50. # 查询缓存 
  51. query_cache_size=128M 
  52. # 只有小于此设定值的结果才会被缓冲   
  53. # 此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖 
  54. query_cache_limit=2M 
  55. # InnoDB使用一个缓冲池来保存索引和原始数据 
  56. # 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.   
  57. # 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%   
  58. # 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.   
  59. innodb_buffer_pool_size=1G 
  60. # 用来同步IO操作的IO线程的数量 
  61. # 此值在Unix下被硬编码为4,但是在Windows磁盘I/O可能在一个大数值下表现的更好.  
  62. innodb_read_io_threads=16 
  63. innodb_write_io_threads=16 
  64. # 在InnoDb核心内的允许线程数量.   
  65. # 最优值依赖于应用程序,硬件以及操作系统的调度方式.   
  66. # 过高的值可能导致线程的互斥颠簸. 
  67. innodb_thread_concurrency=9 
  68.  
  69. # 0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘.   
  70. # 1 ,InnoDB会在每次提交后刷新(fsync)事务日志到磁盘上 
  71. # 2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上 
  72. innodb_flush_log_at_trx_commit=2 
  73. # 用来缓冲日志数据的缓冲区的大小.   
  74. innodb_log_buffer_size=16M 
  75. # 在日志组中每个日志文件的大小.   
  76. innodb_log_file_size=48M 
  77. # 在日志组中的文件总数.  
  78. innodb_log_files_in_group=3 
  79. # 在被回滚前,一个InnoDB的事务应该等待一个锁被批准多久.   
  80. # InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务.   
  81. # 如果你使用 LOCK TABLES 指令, 或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎   
  82. # 那么一个死锁可能发生而InnoDB无法注意到.   
  83. # 这种情况下这个timeout值对于解决这种问题就非常有帮助.  
  84. innodb_lock_wait_timeout=30 
  85. # 开启定时 
  86. event_scheduler=ON 

被批准多久. # InnoDB在其拥有的锁表中自动检测事务死锁并且回滚事务. # 如果你使用 LOCK TABLES 指令, 或者在同样事务中使用除了InnoDB以外的其他事务安全的存储引擎 # 那么一个死锁可能发生而InnoDB无法注意到. # 这种情况下这个timeout值对于解决这种问题就非常有帮助. innodb_lock_wait_timeout=30# 开启定时event_scheduler=ON

根据业务,再加上筛选条件

快4-5s

将where条件中除时间条件外的字段建立联合索引

效果没那么明显

将where条件中索引条件使用inner join的方式去关联

针对这条,我自身觉得很诧异。原sql,b为索引

  1. select aa from bb_2018_10_02 left join ... on .. left join .. on .. where b = 'xxx' 

应该之前有union all,union all是一个一个的执行,最后汇总的结果。修改为

  1. select aa from bb_2018_10_02 left join ... on .. left join .. on .. inner join 
  2.     select 'xxx1' as b2 
  3.     union all 
  4.     select 'xxx2' as b2 
  5.     union all 
  6.     select 'xxx3' as b2 
  7.     union all 
  8.     select 'xxx3' as b2 
  9. ) t on b = t.b2 

结果快了3-4s

性能瓶颈

根据以上操作,3天查询效率已经达到了8s左右,再也快不了了。查看mysql的cpu使用率和内存使用率都不高,到底为什么查这么慢了,3天最多才60w数据,关联的也都是一些字典表,不至于如此。继续根据网上提供的资料,一系列骚操作,基本没用,没辙。

环境对比

因分析过sql优化已经ok了,试想是不是磁盘读写问题。将优化过的程序,分别部署于不同的现场环境。一个有ssd,一个没有ssd。发现查询效率悬殊。用软件检测过发现ssd读写速度在700-800M/s,普通机械硬盘读写在70-80M/s。

优化结果及结论

  • 优化结果:达到预期。
  • 优化结论:sql优化不仅仅是对sql本身的优化,还取决于本身硬件条件,其他应用的影响,外加自身代码的优化。

小结

优化的过程是自身的一个历练和考验,珍惜这种机会,不做只写业务代码的程序员。希望以上可以有助于你的思考,不足之处望指正。

【编辑推荐】

  1. 分布式数据库TiDB在商业银行的设计与实践
  2. 数据库两大必备神器:索引和锁底层原理是什么!
  3. 继续深入数据库 了解一下数据库的锁机制
  4. 想用数据库“读写分离” 请先明白“读写分离”解决什么问题
  5. 数据库常用的事务隔离级别都有哪些?都是什么原理?
【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

读 书 +白菜送彩金大全

SUN Solaris 9/10系统管理员所有白菜免费彩金网址,指南

本书专门根据SUN官方的SCSA for Solaris 9&10考试大纲撰写而成,全面覆盖了SCSA for Solaris 9/10的所有白菜免费彩金网址,考点,除此之外本书还有大量的非考...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊
博聚网