mysql慢查询如何优化
本篇内容主要讲解“mysql慢查询如何优化”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“mysql慢查询如何优化”吧!
1 慢查询优化思路当发生慢查询的时候,优化的思路为:
利用慢查询日志定位慢查询 SQL
通过 explain 分析慢查询 SQL
修改 SQL,尽量让 SQL 走索引
2 慢查询日志MySQL 提供了一个功能——慢查询日志,会记录查询时间超过指定时间阈值的 SQL 到日志中,便于我们定位慢查询并且优化对应的 SQL 语句。
首先查看 MySQL 中关于慢查询相关的全局变量:
mysql>showglobalvariableslike'%quer%';+----------------------------------------+-------------------------------+|Variable_name|Value|+----------------------------------------+-------------------------------+|binlog_rows_query_log_events|OFF||ft_query_expansion_limit|20||have_query_cache|YES||log_queries_not_using_indexes|OFF||log_throttle_queries_not_using_indexes|0|==========================================================================|long_query_time|10.000000|【1】慢查询的时间阈值==========================================================================|query_alloc_block_size|8192||query_cache_limit|1048576||query_cache_min_res_unit|4096||query_cache_size|16777216||query_cache_type|OFF||query_cache_wlock_invalidate|OFF||query_prealloc_size|8192|==========================================================================|slow_query_log|OFF|【2】慢查询日志是否开启|slow_query_log_file|/var/lib/mysql/Linux-slow.log|【3】慢查询日志文件存储位置==========================================================================+----------------------------------------+-------------------------------+15rowsinset(0.00sec)
这里主要关注三个变量:
long_query_time,慢查询的时间阈值,单位秒,如果一个 SQL 语句的执行时间超过这个值,那么 MySQL 就认定其为慢查询
slow_query_log,慢查询日志功能是否开启,默认关闭,开启后记录慢查询
slow_query_log_file,慢查询日志文件的存储位置
默认慢查询日志功能是关闭的,因此我们需要启动该功能
#开启慢查询日志mysql>setglobalslow_query_log=ON;QueryOK,0rowsaffected(0.00sec)#设置慢查询时间阈值mysql>setlong_query_time=1;QueryOK,0rowsaffected(0.00sec)
这样子设置后,MySQL 重启会丢失这些配置,需要在配置文件中修改才会永久有效。
3 explain我们可以使用 explain 分析 SQL 语句的执行情况,例如:
mysql>explainselectsum(1+2);
执行结果如下,可以看到有很多字段
我们主要看看一些重要的字段:
select_type 表示查询语句的查询类型,包括简单查询、子查询等等
table 表示查询的表,不一定是存在表,可能是本次查询中得到的临时表
type 表示检索类型,使用全表扫描、还是索引扫描等
possible_keys表示可能使用的索引列
keys表示查询中实际使用的索引列,由查询优化器决定
3.1 select_type 字段
3.2 type 字段
对于 InnoDB 存储引擎,type列通常都是all或者index。
关于 type 字段的值,其从上到下对应的 SQL 的执行性能逐渐变差。
3.3 extra 字段
4 慢查询例子准备数据,数据表结构:
createtableuser_info_large(`id`BIGINTUNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'主键',`account`VARCHAR(20)NOTNULLCOMMENT'用户账号',`name`VARCHAR(20)NOTNULLCOMMENT'用户名',`password`VARCHAR(20)notnullCOMMENT'用户密码',`area`VARCHAR(20)NOTNULLCOMMENT'用户地址',`signature`VARCHAR(50)notnullCOMMENT'个性签名',PRIMARYKEY(`id`)COMMENT'主键',UNIQUE(`account`)COMMENT'唯一索引',KEY`index_area_signture`(`area`,`signature`)COMMENT'组合索引');
随机生成 200w 条数据
mysql>selectcount(id)fromuser_info_large;+-----------+|count(id)|+-----------+|2000000|+-----------+1rowinset(0.38sec)
截取部分数据:
执行以下 SQL 语句,没有使用任何索引字段:
SELECTnamefromuser_info_largeORDERBYnamedesclimit0,100000;
Navicat 工具显示的查询时间如下,这并不是 MySQL 真正执行 SQL 的时间,这里面包含了网络传输等时间:
SQL 具体的查询时间可以查看慢查询日志:
#Time:2022-09-26T13:44:18.405459Z#User@Host:root[root]@[ip]Id:1893#Query_time:10.162999Lock_time:0.000113Rows_sent:100000Rows_examined:2100000SETtimestamp=1664199858;SELECTnamefromuser_info_largeORDERBYnamedesclimit0,100000;
关于其中一些信息的说明:
Time:SQL 执行的开始时间
Query_time:SQL 语句查询花费的时间,可以看到花费了 10 秒钟
Lock_time:等待锁表的时间
Rows_sent:语句返回的记录数
Rows_examined:从存储引擎中返回的记录数
正在执行的慢查询是不会被记录到慢查询日志的,只有等待其执行完毕才会记录到日志中。
我们可以使用 show processlist 查看正在执行 SQL 的线程。
再执行以下语句,使用索引 account 字段:
SELECTaccountfromuser_info_largeORDERBYaccountdesclimit0,100000;
查看慢查询日志,并没有被记录下来。
现在分别使用 explain 查看 SQL 语句的执行情况:
explainSELECTnamefromuser_info_largeORDERBYnamedesclimit0,100000;
分析情况如下:
可以看到没有使用到索引,type 为 ALL 表示全表扫描,效率最差,并且 Extra 也是外部排序。
再看看这条 SQL 语句:
explainSELECTaccountfromuser_info_largeORDERBYaccountdesclimit0,100000;
分析情况如下:
type 为 index,使用了索引,使用的索引字段为 account,Extra 显示为使用索引排序。
因此,在实际开发中,我们可以针对慢查询的 SQL,使用 explain 分析语句,根据分析情况以及索引的设计,重新设计 SQL 语句,让 SQL 语句尽量走索引,走合适的索引。
5 优化器与索引在执行 SQL 时,MySQL 的优化器会根据情况选择索引,但并不能保证其执行时间一定最短,我们可以根据实际情况使用 force key (index) 让 SQL 语句强制走某个索引。
例如,以下语句执行后,key 字段为 account,并没有走主键索引。
explainSELECTcount(id)fromuser_info_large;
如果使用 force key,就可以强制令语句走主键索引。
explainSELECTcount(id)fromuser_info_largeforcekey(PRIMARY);
到此,相信大家对“mysql慢查询如何优化”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。