数据库优化之常用的show variables、show status配置优化

当面试官让你聊聊MySQL数据库性能优化，你还是只能回答优化sql，建索引吗？
让我们看看还可以从哪些方面聊聊吧。其实你还可以从网速、数据量、数据库日志、内存等问题、硬件配置，当前占用资源、硬盘碎片或索引碎片等等诸多方面分析聊聊。
传送门：MySQL数据库优化看这一篇就够了（最全干货篇）

⭐️ MySQL优化-配置优化

1、show variables查看MySQL服务器配置参数

1）查看及调整系统配置变量值

show variables;--查看一些系统配置变量
show variables like 'key_%';
show variables like '%cache%';--查看缓存相关参数
show variables like 'innodb_buffer_pool%';--查看缓冲池信息
show variables like 'innodb_file_per_table';--选择是否将表数据和系统表空间独立成单个文件。

2）查询缓存相关参数：

2、show status查看MySQL服务器运行状态值

通过下面的命令可以了解MySQL服务器运行状态值。

show status;
show status like 'com_%';
show status like 'innodb_%';
show status like 'connections';
show status like 'slow_queries';

1）调整max_connections：

MySQL最大连接数量，默认151。
在Linux系统上，如果内存足够且不考虑用户等待响应时间这些问题，MySQL理论上可以支持到万级连接，但是通常情况下，这个值建议控制在1000以内。

2）调整back_log：

TCP连接的积压请求队列大小，通常是max_connections的五分之一，最大不能超过900。

3）调整table_open_cache：

这个值应该设置为max_connections的N倍，其中N代表每个连接在查询时打开的表的最大个数。

4）调整innodb_lock_wait_timeout：

该参数可以控制InnoDB事务等待行锁的时间，默认值是50ms，对于反馈响应要求较高的应用，可以将这个值调小避免事务长时间挂起；
对于后台任务，可以将这个值调大来避免发生大的回滚操作。

5）调整innodb_buffer_pool_size：

InnoDB数据和索引的内存缓冲区大小，以字节为单位，这个值设置得越高，访问表数据需要进行的磁盘I/O操作就越少，如果可能甚至可以将该值设置为物理内存大小的80%。

调优参考计算方法：
val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%

val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75%

val < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (102410241024)

设置命令：set global innodb_buffer_pool_size = 2097152; //缓冲池字节大小，单位kb，如果不设置，默认为128M

设置要根据自己的实际情况来设置，如果设置的值不在合理的范围内，并不是设置越大越好，可能设置的数值太大体现不出优化效果，反而造成系统的swap空间被占用，导致操作系统变慢，降低sql查询性能。

修改配置文件的调整方法，修改my.cnf配置：

innodb_buffer_pool_size = 2147483648 #设置2G

innodb_buffer_pool_size = 2G #设置2G

innodb_buffer_pool_size = 500M #设置500M

MySQL5.7及以后版本，改参数时动态的，修改后，无需重启MySQL，但是低版本，静态的，修改后，需要重启MySQL。