首页服务器教程MySQL如何优化慢查询

MySQL如何优化慢查询

2024-09-16 120

MySQL作为一种广泛使用的关系数据库管理系统,能够处理大量的数据查询,但在高负载或复杂查询的情况下,可能会导致慢查询问题。慢查询不仅增加了数据库的响应时间,还可能影响到应用程序的整体性能。本文将详细介绍如何识别慢查询、分析其原因并提供具体的优化建议,以帮助提升数据库性能和查询效率。

一、定位慢查询MySQL

1、确认是否开启慢查询日志

通过以下命令检查慢查询日志的状态:

SHOW VARIABLES LIKE "%slow%";

MySQL如何优化慢查询

如果 “slow_query_log” 为 “OFF”,表示未开启慢查询日志。可以使用以下命令开启:

SET GLOBAL slow_query_log = ON;

注意:上述设置仅在当前会话中生效。若需永久生效,请在 “my.cnf” 配置文件中添加以下内容:

ini
[mysqld]
slow_query_log = ON
slow_query_log_file = /path/to/your/slow-query.log

2、设置时间限制

使用以下命令查看当前的慢查询时间限制:

SHOW VARIABLES LIKE "long_query_time";

MySQL如何优化慢查询

此值表示执行时间超过该值的查询将被记录为慢查询。可以根据需要适当调整此值,测试时可以设置得较小一些。

MySQL如何优化慢查询

3、定位具体的慢查询

MySQL如何优化慢查询

慢查询日志中将记录以下信息:

  • Time:日志记录的时间;
  • User@Host:执行的用户及其主机;
  • Query_time:查询耗时;
  • Lock_time:数据库锁定时间;
  • Rows_sent:发送给请求方的记录条数;
  • Rows_examined:扫描的记录条数;
  • SET timestamp:语句执行的时间点。

注意:如果 MySQL 运行在 Docker 环境中,需要进入到 MySQL 容器内部才能查看慢查询日志。

4、相关MySQL查询

可以使用以下命令查询 MySQL 的操作信息:

  • SHOW STATUS LIKE “com_insert%”; — 获得插入次数
  • SHOW STATUS LIKE “com_delete%”;– 获得删除次数
  • SHOW STATUS LIKE “com_select%”;– 获得查询次数
  • SHOW STATUS LIKE “uptime”; — 获得服务器运行时间
  • SHOW STATUS LIKE ‘connections’;– 获得连接次数

5、分析慢查询使用EXPLAIN

MySQL如何优化慢查询

使用 “EXPLAIN” 分析具体的 SQL 查询,了解查询执行计划:

EXPLAIN SELECT * FROM your_table WHERE your_condition;

输出字段及说明:

  • id:选择标识符;
  • select_type:表示查询的类型;
  • table:输出结果集的表;
  • partitions:匹配的分区;
  • type:表的连接类型;
  • possible_keys:可能使用的索引;
  • key:实际使用的索引;
  • key_len:索引字段的长度;
  • ref:列与索引的比较;
  • rows:估算的扫描行数;
  • filtered:按表条件过滤的行百分比;
  • Extra:执行情况的描述。

6、EXPLAIN字段说明

type 字段说明:

  • “system”:表中仅有一条数据,等于系统表;
  • “const”:使用主键或唯一索引,查询变量被转为常量;
  • “eq_ref”:使用唯一索引,返回匹配的唯一数据;
  • “ref”:使用非唯一索引,返回多行匹配的数据;
  • “range”:范围查询,利用索引返回范围内的行;
  • “index”:以索引顺序全表扫描;
  • “all”:全表扫描,应尽量避免。

extra 字段说明:

  • “using index”:使用了覆盖索引,可以直接返回所需数据;
  • “using index condition”:索引下推,减少回表次数;
  • “using where”:未使用索引,通过 WHERE 过滤获取数据;
  • “using temporary”:使用临时表保存结果,效率较低;
  • “using filesort”:使用文件排序,效率较低,尽量避免。

二、慢MySQL优化建议

1、避免使用子查询

SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');
(优化只针对SELECT有效,对UPDATE/DELETE子 查询无效)

2、读取适当的记录LIMIT M,N

可以改为:

SELECT * FROM t WHERE 1 LIMIT 10;

3、分组统计可以禁止排序

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

默认情况下,MySQL对所有GROUP BY col1,col2…的字段进⾏排序。如果查询包括GROUP BY,想要避免排序结果的消耗,则可以指定ORDER BY NULL禁止排序。

可以改为:

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

4、禁止不必要的ORDER BY排序

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;

可以改为:

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

5、尽量不要超过三个表join

需要join的字段,数据类型保持绝对一致;多表关联查询时,保证被关联的字段需要有索引。

6、在varchar字段上建立索引时,必须指定索引长度

没必要对全字段建立索引,根据实际文本区分度决定索引长度。索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为20的索引,区分度会高达90%以上,可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

7、不要使用 select

只返回需要的字段。

8、排序请尽量使用升序

9、尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。

10、避免索引失效

1)字段类型转换导致不用索引

如字符串类型的不用引号,数字类型的用引号等,这有可能会用不到索引导致全表扫描;

2)根据联合索引的第二个及以后的字段单独查询用不到索引。

3)字段前面不能加函数/加减运算,否则会导致索引失效。

如下面语句将进行全表扫描:

select id from t where num/2=100
SELECT * FROM t WHERE YEAR(d) >= 2016

可以改为:

select id from t where num=100*2
SELECT * FROM t WHERE d >= '2016-01-01';

4)搜索严禁左模糊或者全模糊。

select name from t where name like %s
select name from t where name like %s%

5)避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

select id from t where num != 2

可以改为:

select id from t where num > 2 and  num < 2

6)避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描。

select id from t where num is null

可以改为:设置num的默认值为0,确保没有null值。

select id from t where num=0

7)用IN或UNION来替换OR低效查询。

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

可以改为:

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

或:

SELECT FROM t WHERE LOC_IN = 10 UNION ALL SELECT FROM t WHERE LOC_IN = 20 UNION ALL SELECT * FROM t WHERE LOC_IN = 30

对于连续的数值,能用 between 就不要用 in 了。

select id from t where num between 1 and 3

8)在 where 子句中使用参数,也会导致全表扫描。

因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

select id from t where num=@num

可以改为强制查询使用索引:

select id from t with(index(索引名)) where num=@num

9)删除表所有记录请用 truncate,不要用 delete。

10)存储过程和触发器设置。

在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

  • 广告合作

  • QQ群号:707632017

温馨提示:
1、本网站发布的内容(图片、视频和文字)以原创、转载和分享网络内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。邮箱:2942802716#qq.com(#改为@)。 2、本站原创内容未经允许不得转裁,转载请注明出处“站长百科”和原文地址。

相关文章