Mysql-性能优化-数据库优化

慢查询日志

一般来说数据库的性能问题很多时候就是由慢SQL造成的。
我们可以开启慢查询日志，通过下面的参数就进行配置。但是需要注意点 是开启慢查询日志会对系统性能有一定的影响，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。

# 查看是否开启慢查询日志
show variables like '%slow_query%';
#设置多少秒认为是慢查询
show variables like 'long_query_time%';

日志格式

# Time: 2021-07-27T08:32:44.023309Z
# 执行SQL查询的连接信息，用户和连接IP
# User@Host: root[root] @ [172.26.233.201] Id: 1243
#Query_time,这条SQL执行的时间,越长则越慢
#Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
#Rows_sent,查询返回的行数
#Rows_examined,查询检查的行数，越长就当然越费时间
Query_time: 218.295526 Lock_time: 0.000126 Rows_sent: 10959
Rows_examined: 10929597
use hero_all;
SET timestamp=1627374764;
# 慢查询SQL语句
select tk.id,ts.* from tb_seckill_goods ts LEFT JOIN tb_sku tk ON tk.id=ts.id where ts.id>100 order by ts.price;

分析慢查询日志工具

使用mysqldumpslow工具，mysqldumpslow是MySQL自带的慢查询日志工具。可以使用mysqldumpslow工具搜索慢查询日志中的SQL语句。
得到按照时间排序的前10条里面含有左连接的查询语句：

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log

• s ：是表示按照何种方式排序
  al 平均锁定时间
  ar 平均返回记录时间
  at 平均查询时间（默认）
  c 计数
  l 锁定时间
  r 返回记录
  t 查询时间
• t ：是top n的意思，即为返回前面多少条的数据
• g ：后边可以写一个正则匹配模式，大小写不敏感的

连接数

同时连接客户端的最大数量，默认值 151，最小值1.
连接数导致问题：ERROR 1040，TooManyConnections原因如下：
第一：MySQL的max_connection配置少了
第二：访问确实太高，MySQL有点扛不住了，考虑扩容
第三：你的连接池配置有误，MaxActive为0

# 查看 max_connections
show global variables like 'max_connections'
# 设置 max_connections（立即生效重启后失效）
set global max_connections = 800;
# 这台MySQL服务器最大连接数是256，然后查询一下服务器使用过的最大连接数：
show global status like 'Max_used_connections';
# MySQL服务器过去的最大连接数是245，没有达到服务器连接数上限256，应该没有出现1040错误，
比较理想的设置是：Max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

线程使用情况

如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。

根据测试发现，以上服务器线程缓存thread_cache_size没有进行设置或者设置过小，MySQL服务器一直在创建线程销毁线程。增加这个值可以改善系统性能（可以使得RT更趋于平稳）。

通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。

Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器thread_cache_size配置：

# 查询线程使用情况
show global status like 'Thread%';
# 查询线程缓存
show variables like 'thread_cache_size';
# 增加thread_cache_size的值  建议值1G ---> 8  2G ---> 16  3G ---> 32 大于3G ---> 64
set global thread_cache_size = 64;

数据库优化-结构优化

• 对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

• 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。
  通常都是在统计当中使用，每次统计报表的时候都是离线统计，后台有有一个线程对你这统计查询结果放入一个中间表，然后你对这个中间表查询。

• 设计数据表时应尽量遵循关系数据库范式的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是合理的加入冗余字段可以提高查询速度。
  表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。
  注意：冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

缓冲区优化

将数据保存在内存中，保证从内存读取数据
设置足够大的 innodb_buffer_pool_size ，将数据读取到内存中，推荐设置为物理内存的50%~80%。

show global status like 'innodb_buffer_pool_pages%';

这个参数为0表示缓冲池占用完了

MySQL数据库配置优化

• 表示缓冲池字节大小。推荐值为物理内存的50%~80%。

innodb_buffer_pool_size

• 日志组（Redo）中每个日志文件的大小，默认48MB，日志文件越大越节省磁盘IO，但需要注意日志文件变大会增加崩溃恢复时间

innodb_log_file_size=48

• 用来控制redo log刷新到磁盘的策略。

innodb_flush_log_at_trx_commit=1

• 每提交1次事务同步写到二进制日志到磁盘中，可以设置为n。

sync_binlog=1

• 脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。推荐值为25%~50%。

innodb_max_dirty_pages_pct=30

• 后台进程最大IO性能指标。默认200，如果SSD，调整为5000~20000

innodb_io_capacity=200

• 慢查询日志的阈值设置，单位秒。

long_qurey_time=3

• MySQL的binlog复制的形式，MySQL8.0默认为row

binlog_format=row

• 同时连接客户端的最大数量

max_connections=200

• 全量日志建议关闭。默认关闭。

general_log=0

完整配置如下：

# 01-缓冲区，将数据保存在内存中，保证从内存读取数据。推荐值为总物理内存的50%~80%。
innodb_buffer_pool_size=
# 02-日志组（Redo）中每个日志文件的大小，默认48MB，日志文件越大越节省磁盘IO，但需要注意日
志文件变大增加崩溃恢复时间
innodb_log_file_size=48
# 03-用来控制Redo日志刷新到磁盘的策略。
innodb_flush_log_at_trx_commit=1
# 04-每提交1次事务同步写到磁盘中，可以设置为n。
sync_binlog=1
# 05-脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。推荐值为25%~50%。
innodb_max_dirty_pages_pct=30
# 06-后台进程最大IO性能指标。默认200，如果SSD，调整为5000~20000
innodb_io_capacity=200
# 07-指定innodb共享表空间文件及大小。
innodb_data_file_path=
# 08-慢查询日志的阈值设置，单位秒。
long_qurey_time=3
# 09-MySQL的binlog复制的形式，MySQL8.0默认为row
binlog_format=row
# 10-同时连接客户端的最大数量
max_connections=200
# 11-全量日志建议关闭。默认关闭。
general_log=0

服务器硬件优化

提升硬件设备，例如选择尽量高频率的内存（频率不能高于主板的支持）、提升网络带宽、使用SSD高速磁盘、提升CPU性能等。
CPU的选择:
对于数据库并发比较高的场景，CPU的数量比频率重要。
对于CPU密集型场景和频繁执行复杂SQL的场景，CPU的频率越高越好。