数据库调优-数据库优化

数据库优化

如何发现复杂的SQL有问题？ 一个个去explain吗？你有没有这样的困惑，开发代码运行顺畅丝滑，上生产了却卡的一逼？

哈哈，相信大家都会遇到这样的问题！

sql

复制代码

# 举个栗子：查询 20 秒 explain select tk.id,ts.* from t_goods ts LEFT JOIN t_sku tk ON tk.id=ts.id where ts.id>100 order by ts.price;

2.1 慢查询日志

数据库查询快慢是影响项目性能的一大因素，对于数据库，我们除了要优化 SQL，更重要的是得先找到需要优化的SQL。

MySQL数据库“ 慢查询日志 ”功能，用来记录查询时间超过某个设定值的SQL语句，这将极大程度帮助我们快速定位到症结所在，以便对症下药。

嗯嗯，好像很有道理喔！！！

至于查询时间的多少才算慢，每个项目、业务都有不同的要求。MySQL的慢查询日志功能默认是关闭的，需要手动开启。

查看是否开启慢查询功能

sql

复制代码

# 查看是否开启慢查询日志 show variables like '%slow_query%'; show variables like 'long_query_time%';

【slow_query_log】：是否开启慢查询日志， 1 为开启， 0 为关闭。
【log-slow-queries】：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
【slow-query-log-file】：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
【long_query_time】：慢查询阈值，当查询时间多于设定的阈值时，记录日志，【单位为秒】。

开启慢查询功能

注意：打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主-从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。

哈哈，这个大多数人都不会考虑到，还不拿个小本本记下来？

ini

复制代码

# 开启慢查询日志 set global slow_query_log=on; # 大于 1 秒钟的数据记录到慢日志中，如果设置为默认 0 ，则会有大量的信息存储在磁盘中，磁盘很容易满掉 set global long_query_time=1; # 记录没有索引的查询。 set global log_queries_not_using_indexes=on;

慢查询日志

ini

复制代码

# Time: 2022-07-29T23:59:41.539068Z # User@Host: root[root] @ [192.168.4.1] Id: 10 # Query_time: 2.000222 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1621900781; SELECT SLEEP(2);

日志解析：

第一行,SQL查询执行的具体时间

第二行,执行SQL查询的连接信息，用户和连接IP

第三行,记录了一些我们比较有用的信息，如下解析

scss

复制代码

Query_time,这条SQL执行的时间,越长则越慢 Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间 Rows_sent,查询返回的行数 Rows_examined,查询检查的行数，越长就当然越费时间

第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间。
第五行及后面所有行（第二个# Time:之前）,执行的sql语句记录信息，因为sql可能会很长。

2.2 连接数max_connections

同时连接客户端的最大数量，默认值 151 ，最小值1.

连接数导致问题：ERROR 1040，TooManyConnections原因如下

第一：访问确实太高，MySQL有点扛不住了，考虑扩容
第二：MySQL的max_connection配置少了

csharp

复制代码

# 查看 max_connections show global variables like 'max_connections' # 设置 max_connections（立即生效重启后失效） set global max_connections=800;

sql

复制代码

# 这台MySQL服务器最大连接数是 256 ，然后查询一下服务器使用过的最大连接数： show global status like 'Max_used_connections';

erlang

复制代码

# MySQL服务器过去的最大连接数是 245 ，没有达到服务器连接数上限 256 ，应该没有出现 1040 错误，比较理想的设置是：Max_used_connections / max_connections * 100% ≈ 85% 最大连接数占上限连接数的85%左右，如果发现比例在10%以下，MySQL服务器连接数上限设置的过高了。

这里：最大使用过连接数/最大连接数 ，这个比值保持在85%，就比较理想。

干货喔！！！

2.3 线程使用情况

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

根据测试发现，以上服务器线程缓存thread_cache_size没有进行设置，或者设置过小，MySQL服务器一直在创建线程销毁线程。增加这个值可以改善系统性能。通过比较 Connections 和 Threads_created状态的变量，可以看到这个变量的作用。

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

sql

复制代码

# 查询线程使用情况 show global status like 'Thread%'; # 查询线程缓存 show variables like 'thread_cache_size'; # 增加thread_cache_size的值 set global thread_cache_size = 64;

根据物理内存建议设置规则如下：

1G ---> 8
2G ---> 16
3G ---> 32
大于3G ---> 64

又是一大干货喔！！！

2.4 数据库优化-结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。这句话是什么意思呢？

就是说我们的数据库优化不仅仅要局限于查询优化，要从这块跳出来做好最开始的设计优化，如果你这个主要设计是不合理的这些个查询优化效果也只是杯水车薪。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

2.6.1. 将字段很多的表分解成多个表（分表）

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

项目实战的时候会将一个完全信息的表里面的数据拆分出来形成多个新表每个新表负责那一块的数据查询然后这个拆分是定时的

2.6.2. 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

通常都是在统计当中有使用啊，每次统计报表的时候都是离线统计啊，后台有有一个线程对你这统计结果查询号放入一个中间表，然后你对这个中间表查询就行了。

2.6.3. 增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。

但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

2.7 MySQL其他的配置参数【my.conf/my.ini文件】

通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。

MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中，常用的参数如下：

ini

复制代码

# 01-缓冲区，将数据保存在内存中，保证从内存读取数据。建议innodb_buffer_pool_size设置为总内存大小的3/4或者4/5. innodb_buffer_pool_size= # 02-降低磁盘写入次数。推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size innodb_log_file_size= # 03-表示缓冲池字节大小。推荐值为物理内存的50%~80%。 innodb_buffer_pool_size= # 04-用来控制redo log刷新到磁盘的策略。 innodb_flush_log_at_trx_commit=1 # 05-每提交 1 次事务同步写到磁盘中，可以设置为n。 sync_binlog=1 # 06-脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。推荐值为25%~50%。 innodb_max_dirty_pages_pct=30 # 07-后台进程最大IO性能指标。默认 200 ，如果SSD，调整为5000~20000 innodb_io_capacity=200 # 08-指定innodb共享表空间文件的大小。 innodb_data_file_path # 09-慢查询日志的阈值设置，单位秒。 long_qurey_time=0.3 # 10-mysql复制的形式，row为MySQL8.0的默认形式。 binlog_format=row # 11-调高该参数则应降低interactive_timeout、wait_timeout的值。 max_connections=200 # 12-过大，实例恢复时间长；过小，造成日志切换频繁。 innodb_log_file_size # 13-全量日志建议关闭。默认关闭。 general_log=0