MySQL数据库性能优化_mysql数据空洞表性能优化-CSDN博客

本文链接：https://blog.csdn.net/aeaiesb/article/details/109067850

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

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

1.系统优化

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

1.1文件位置

1.2修改文件

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

1.3注意事项

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

2.性能优化

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

2.1使用核数

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

2.1.1配置调整

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

配置说明：

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

2.1.2注意事项

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

2.2缓存分区

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

2.2.1配置调整

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

2.2.2注意事项

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

计算公式为：

2.3连接时长

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

2.3.1配置调整

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

2.3.2注意事项

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

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

3.SQL优化

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

3.1添加索引

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

3.1.1添加方式

添加索引可以使用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 太占内存，使用文件排序)。