任何软件平台的运行都需要依赖于数据库的存储，数据库存储着业务系统的关键信息，包含基础的组织、人员及业务流程板块信息等。因此在平台运转过程中，数据库的响应速率直接影响平台的回显速度及用户的友好体验。尽管平台提升用户体验有多重方式如：增加缓存、前后端分离等，一旦触发数据库查询，如果数据库回响不及时，那么用户体验就会直线下降，因此在数据库使用过程中，除增加服务器配置资源外，也可以通过数据库的配置及SQL的使用提升数据库的使用效率。

本文是针对MySQL数据库在使用过程中的优化措施的记录及总结，为后续项目作为参考及借鉴。

系统优化

在数据库优化过程中可划分为几个环节，分别是部署数据库服务器的硬件资源优化、服务器中操作系统参数优化、MySQL数据库配置优化及使用查询的SQL优化。对于一个水杯来说，能装多少水，完全取决于你的杯子有多大。硬件资源决定了当前服务器的最大配置，而服务器的操作系统决定了MySQL服务的运行空间，MySQL数据库优化决定了数据库响应的速度，好的SQL能够最有效的发挥出数据库性能空间。下面是对系统本身优化配置的说明：

1.文件位置

/etc/sysctl.conf

2.修改文件

对应调整系统配置文件sysctl.conf中的参数，重点参数修改红色标注如下：

3.注意事项

修改完配置文件不用重启就可以生效。

性能优化

MySQL性能优化分为几个部分，首先对部署MySQL的应用服务器本身的性能进行优化，通常服务器默认的配置较低，需要通过参数来调整服务器的使用区域及范围，其次对MySQL自身的配置进行优化，服务器利用空间增大后MySQL使用服务器的空间及性能同样需要配置增大，加大服务器的利用率。

1.使用核数

调整MySQL配置文件my.cn，添加参数innodb_read_io_threads及innodb_write_io_threads设置MySQL运行过程中使用的系统核数。

> > > >配置调整

修改内容/etc/my.cnf中的内容，（5.7的数据库，没有了my.cnf，直接添加），重点关注红色字体，如下：

配置说明：

read_io、write_io及对应数据库读写过程中使用的CPU核数，默认为4，通常按照服务器配置进行设置，与服务器核数相同，样例为32核。

> > > >注意事项

调整后，要对数据库进行重启，让配置文件生效，重启命令为：

2.缓存分区

MySQL数据查询时同样可以设置缓存空间，将查询数据缓存，在缓存中加快数据查询的速度，当然缓存空间的使用与系统本身的配置有关，详细配置如下：

> > > >配置调整

修改内容/etc/my.cnf中的内容，（5.7的数据库，没有了my.cnf，直接添加），重点关注红色字体，如下：

> > > >注意事项

注意：索引的缓存设置需要依据内存进行配置，默认设置
innodb_buffer_pool_chunk_size为128M，需要调整
innodb_buffer_pool_chunk_size为8G，方可实现chunk_size及instances的设置

计算公式为：

3.连接时长

MySQL数据库连接会话保持时间同样需要限定，时间不宜过短也不宜过长，时间过短可能存在大数据量查询不能完全响应返回，时间过长容易造成连接线程一直被占用，影响MySQL的速率。

> > > >配置调整

修改内容/etc/my.cnf中的内容，（5.7的数据库，没有了my.cnf，直接添加），重点关注红色字体，如下：

> > > >注意事项

1.数据库连接时长结合实际情况需要设置符合运行情况的时间，不适宜太长也不适宜太短，时间太长容易造成死链的情况，时间太短容易不满足查询返回时间，通常为60~360之间，也可根据网络等实际情况行调整。

2.数据库允许最大连接数默认是100，也就是每秒100个线程并发，结合实际应用情况需要酌情调大，本样例为8000并发。

SQL优化

除MySQL本身性能优化外，在撰写SQL语句时也同样需要性能优化，避免因SQL撰写的问题影响整体查询时间，典型优化方案为添加表索引、SQL查询带有条件、不适用子查询、查询数量多的表放在前面等，下面将结合SQL优化点进行说明。

1.添加索引

数据表索引添加是在不调整服务器性能最显著的提升数据库查询性能的方式，在数据表创建时创建索引列，查询使用索引应用，实现SQL查询使用索引查询会很大程度加快查询速度。

> > > >添加方式

添加索引可以使用Navicat设计表选择索引列进行添加。

执行SQL查询查看索引应用情况，点击执行结果中的“解释”，可查看索引应用情况，效果如下：

其中：

1.type：连接类型可划分为几种类型执行效率分别为const>eq_ref>ref>range>index>all；

1)const:查询索引字段，并且表中最多只有一行匹配（只有主键查询只匹配一行才会是const，有些情况唯一索引匹配一行会是ref）；

2)eq_ref主键或者唯一索引；

3)ref非唯一索引（主键也是唯一索引）；

4)range索引的范围查询；

5)index (type=index extra = using index 代表索引覆盖，即不需要回表)；

6)all全表扫描（通常没有建索引的列）。

2.extra额外信息说明，常见参数如下：

1)using temporary(组合查询返回的数据量太大需要建立一个临时表存储数据,出现这个sql应该优化)；

2)using where (where查询条件)；

3)using index(判断是否仅使用索引查询，使用索引树并且不需要回表查询)；

4)using filesort(order by 太占内存，使用文件排序)。

> > > >注意事项

1.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致；

2.设置索引联合查询时数据库字段字符类型需要与联合查询表一致，如下：

3.避免在where子句中对字段进行函数操作，将导致引擎放弃使用索引而进行全表扫描。如：

4.避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

5.避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

2.查询优化

SQL查询优化有多种方式，好的SQL能有效的提升查询性能，下面枚举典型的查询优化注意点：

1.不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段；

2.在SQL查询时尽量不要使用IN或者NOT IN，对于区间查询建议使用BETWEEN AND；

3.查询时尽量不要在where 子句中使用 != 或 <> 操作符，相关条件可使用< 、 <= 、 = 、 > 、 >= 、 BETWEEN AND；

4.查询过程中，如果是多表关联查询，则将数据量较大的表作为SQL查询的主体表，且关联查询时建议使用InnerJoin进行关联；

5.查询count的SQL不要有排序。

监控方式

性能优化之后，要对其进行跟踪排查，对于系统，通过查看top的情况进行定位，查看cup的使用效率，并针对相应的线程进行跟踪。而对于数库，要跟踪数据库的线程情况，查询慢的SQL语句，添加适当的索引。

1.数据库常用命令

数据库性能监控常见命令如下：

2.系统监控常用命令

通常系统典型监控使用命令如下：

本文由@数通畅联原创，欢迎转发，仅供学习交流使用，引用请注明出处！谢谢~