Java MySQL慢查询优化

引言

　　本文主要介绍MySQL中，有关慢查询的优化方案。

　　慢查询日志是mysql的一个日志记录，可以用来记录mysql语句执行时间超过指定的long_query_time的SQL语句,long_query_time的默认值是10s
　　慢查询日志默认情况下是不开启的，因为将数据保存到日志会对性能有一定影响，测试环境下可手动打开，但注意手动开启之后只对本次启动生效，mysql关闭之后重启恢复默认状态，要想持久生效要改变my.ini配置文件（Window系统下），其他系统变量也如此。可通过show varaibles like '%slow_query_log%'来查看日志开启情况

查询优化

explain+SQL语句

　　执行这个语句可以让开发人员看到select语句执行的详细信息，开发人员可以将上一步慢查询中捕获的慢查询SQL语句进行分析，判断查询效率低的可能原因

　　　　id：select查询的序号

id相同时，则按照从上到下依次执行  

id不同时，id值越大优先级越高，越先被执行  

id有相同有不同，则相同的id为一个组，不同组的id值按照规则二的优先级执行，同组id则按照规则一依次执行  

　　　　select_type：select查询的类型

simple:表示该查询没有子查询和UNION连接查询  
primary:有子查询时的最外层查询  
subquery:有子查询时的内层嵌套查询  
derived:在from中包含的select就称为derived(衍生)  ，mysql会递归这些子查询，把结果放在临时表中  
union:union的第二个或者最后一个  
union result:union的结果  

　　　　table：执行当前SQL语句中用的表

　　　　partitions：代表当前表所使用的分区

　　　　type：显示使用了何种查询，按照常见的几种查询最好到最坏排序为system>const>eq_ref>ref>range>index>all

system,const:mysql能够对这部分进行查询优化使能够将其转换成一个常量（system只返回一行，const有多行),如某一行的主键放入WHERE子句里的方式来选取此行的主键,MySQL就能将这个查询转换成一个常量。然后就可以高效的将表从联接执行中移除  
eq_ref:使用该索引查找，mysql知道最多返回一条数据，可以在使用主键或者唯一性索引查找时用到  
ref:非唯一性索引的索引查找  
range:范围扫描，例如带有between或者>,<,in等  
index:扫描所有索引行  
all:扫描所有数据行 

　　　　possible_keys/kesy：代表可能用到的索引和实际用到的索引

　　　　key_len：在索引中使用的字节数

　　　　ref：显示了之前的表在key列记录的索引中查找值所用的列或常量

　　　　rows：mysq估计的要找到满足条件的行所需要扫描的行数

show profile

　　show profile是mysql用来分析SQL查询语句的资源使用情况的工具

　　我们执行一些测试的SQL语句之后运行show profiles语句

　　我们可以选择指定项指定SQL语句来分析

　　一般我们查看的属性就是cpu和block io两个模块

　　注意：

若出现以下任意一个情况，都表示这是一个糟糕的SQL语句，需要优化

1. convering heap to MyIsam查询结果过大，内存不够，需要记录到磁盘上
2. creating tmp table创建临时表储存数据，用完之后删除
3. copying to tmp table on disk将临时表中的数据储存到磁盘上
4. locked

索引优化

　　哪些情况需要建索引？

1. 主键，唯一索引
2. 经常用做查询条件的建立索引
3. 经常需要排序、分组、统计的建立索引
4. 查询中与其他表关联，外键关系建立索引

　　哪些情况不要建索引？

1. 表记录太少，百万级一下不需要建索引
2. 经常增删改的表
3. 数据重复且分布均匀的数据：如true、false
4. 频发更新字段
5. where条件里用不到的字段

缓存优化

　　一般Java提供的ORM中都带有相关查询的一二级缓存。