java系统性能优化之mysql数据库优化

java开发的应用系统经常面临系统响应时间过长的情况，可能的原因有很多方面，比如网络延迟、代码逻辑不合理，数据量太大、架构设计不合理、慢查询等。在优化的时候不能一上来就扑倒sql调优上，要整体查看性能损耗最大的地方在哪里，然后对症下药。

今天主要探讨的是从数据库下手可以做哪些优化。对mysql数据库进行优化也有以下几个维度来进行。从上到下，优化的投入产出比越来越小，意味着sql与索引层面的优化消耗的成本低，带来的回报大，而硬件与操作系统层面的优化成本高，回报相对没有那么大。

1.SQL与索引

type由上至下，查询效率system>const>eq_ref>ref>range>index>all，一般要达到range级别，最好达到ref，否则需要优化

ALL 全表扫描； index 索引全扫描； range 索引范围扫描，常用语<,<=,>=,between,in等操作； ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中； eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询； const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询； null MySQL不访问任何表或索引，直接返回结果； 虽然上至下，效率越来越高，但是根据cost模型，假设有两个索引idx1(a, b, c),idx2(a, c)，SQL为"select * from t where a = 1 and b in (1, 2) order by c";如果走idx1，那么是type为range，如果走idx2，那么type是ref；当需要扫描的行数，使用idx2大约是idx1的5倍以上时，会用idx1，否则会用idx2。

b.show profile分析

了解SQL执行的线程的状态及消耗的时间。默认是关闭的，开启语句“set profiling = 1;”

SHOW PROFILES ;SHOW PROFILE FOR QUERY  #{id};

c.trace

trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么选择A执行计划而不选择B执行计划。

set optimizer_trace="enabled=on";set optimizer_trace_max_mem_size=1000000;select * from information_schema.optimizer_trace;

d.确定问题并采用相应的措施

创建索引或联合索引 用小表驱动大表 select时候用具体字段代替*号 避免导致索引时效的表达式，如NOT、!=、<>、!<、!>、NOT IN、NOT LIKE）和模糊查询% 避免返回大量数据，增加分页 避免属性隐式转换

2.表结构与存储引擎

1）为了减少关联查询，可以增加冗余字段

2）将大量数据按日期或分类拆分成小表

3）修改表字段属性为not null

4）存储引擎InnoDB适合并发大更新多场景，MyIASM适合查询插入操作多

3.MYSQL配置

1)max_connections：多个应用或请求同时访问数据库，导致连接数不够，可以增加最大连接数

2)wait_timeout：及时释放不活动的连接，客户端默认超时时间是28800秒，即8小时，可以把这个值调小

4.数据库架构

1)缓存，增加redis缓存

2)集群，主从复制，读写分离

3)分库分表

5.硬件与操作系统

增加硬件数量或者提升硬件配置