前言
mysql调优金字塔
越向上难度越大,回报减少。
1、硬件和os调优特别复杂。
2、mysql调优包括调整表结构、优化sql、恰当的使用索引、清除多余的索引等等。
3、架构调优属于开发的事情,需要考虑实际的业务场景。
可以将非数据库的任务放到缓存。考虑是否使用分布式。考虑是否读写分离。
一、什么是慢查询日志
记录运行时间超过阙值的sql的日志就是慢日志。
long_query_time参数:该参数会设定一个阙值,超过该值的sql,就是慢查询sql。
二、sql查询性能下降的原因
1.扫描了过多的额外记录
如果确定了查询只返回需要的数据以后,接下来就应该看看查询时为了返回需要的数据,是否扫描了过多的数据,对于mysql最简单衡量查询开销的三个指标:
1)响应时间:服务时间+排队时间
服务时间:数据库处理这个查询时,真正花了多少时间
排队时间:指的时服务器因为等待某些资源而没有真正执行查询的时间,有可能时等待行锁。
2)扫描的行数和返回的行数
理想状况下,扫描的行数和返回的行数应该是相同的。
3)扫描的行数和访问的类型
在explain分析结果中,type反映了访问的类型。
这三个指标都记录在慢日志中。
三、启动慢日志
慢查询相关的参数:
mysql> show variables like '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
参数解释:
slow_query_log:是否开启慢日志,on表示开启,off表示关闭。刚建的数据库慢日志是默认关闭的。
slow_query_log_file:慢日志的存储路径
long_query_time:慢查询的阙值,当查询时间大于设定的阙值的时候,会记录到慢日志中。
可以通过设置slow_query_log的值,来开启慢查询日志
上面的设置,只对当前的窗口有效,mysql重启后就会失效,想要永久生效,需要修改/etc/my.cnf
并且可以设置慢日志文件的路径和名字:slow_query_log_file=/var/lib/mysql/test-slow.log
重启mysql让配置生效:
service mysqld restart
开启慢查询日志之后,什么样的sql才会记录到慢日志中?
由long_query_time来控制的,默认情况下值为10秒。
重新设置值:
mysql> set global long_query_time=1;
设置后,需要重新连接mysql才能看到修改后的值:
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
参数log_queries_not_using_indexes说明:开启后,未使用索引的查询,也会被记录到慢日志中。
mysql> show variables like '%log_queries%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
四、慢日志参数解释:
# Time: 2023-11-09T09:57:29.624741Z
# User@Host: root[root] @ localhost [] Id: 13
# Query_time: 0.001473 Lock_time: 0.000181 Rows_sent: 1 Rows_examined: 408
SET timestamp=1699523849;
select count(*) from `performance_schema`.variables_by_thread;
Time:执行时间
User:用户信息
Query_time:查询语句执行时间
Lock_time:等待锁的时间
Rows_sent:查询结果行数
Rows_examined:查询扫描的行数
SET timestamp:时间戳
sql的具体信息