阜阳做网站的商户,网站建设对于企业的重要性,酷奇趣wordpress邀请码,江门专业网站建设价格定位慢sql
工具排查慢sql
调试工具#xff1a;Arthas运维工具#xff1a;Skywalking
通过以上工具可以看到哪个接口比较慢#xff0c;并且可以分析SQL具体的执行时间#xff0c;定位到哪个sql出了问题。
启用慢查询日志
慢查询日志记录了所有执行时间超过指定参数(lon…定位慢sql
工具排查慢sql
调试工具Arthas运维工具Skywalking
通过以上工具可以看到哪个接口比较慢并且可以分析SQL具体的执行时间定位到哪个sql出了问题。
启用慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time单位:秒默认10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
# 开启MySQL慢日志查询开关
slow_query_log1# 设置慢日志的时间为1秒SQL语句执行时间超过1秒就会视为慢查询记录到慢查询日志中
long_query_time2配置完成后重启MySQL服务保证配置生效。
慢查询日志一般的返回结果如下
# Time:2024-08-01T12:00:00.123456Z
# UserHost: root[root] localhost [] Id: 8
# Query time:2.345678 Lock_time:0.012345 Rows sent:10 Rows examined: 100
SET timestamp1650000000;
SELECT * FROM orders WHERE status pending ORDER BY gmt created DEsc;需要关注以下内容 Query_time查询时间查询执行的总时间单位为秒。是关键的指标用于判断查询的性能。 Lock_time锁定时间表被锁定的时间单位为秒。可以帮助判断是否存在锁等待问题。 Rows_sent发送的行数查询返回的行数。 Rows_examined检查的行数查询过程中检查的行数用于判断查询的效率。
分析慢sql
profile详情
SHOW PROFILE 是 MySQL 提供的一种用于查看查询语句执行的详细步骤和资源消耗的工具。使用 SHOW PROFILE 命令可以帮助找出查询语句的瓶颈优化查询性能。 启用 Profiling 在使用 SHOW PROFILE 之前需要先启用 Profiling
SET profiling 1;执行查询 执行你想分析的查询语句
SELECT * FROM your_table WHERE some_column some_value;查看 Profile 列表 使用以下命令查看刚才执行的查询的 Profile
SHOW PROFILES;这将显示一个查询 ID 列表及其对应的查询语句和总执行时间。 查看详细的 Profile 信息 使用 SHOW PROFILE 查看某个查询 ID 的详细信息
SHOW PROFILE FOR QUERY query_id;查看CPU信息 SHOW PROFILE CPU FOR QUERY query_id;explain执行计划
explain 是 MySQL 提供的一种用于分析和调试 SQL 查询的工具。
通过使用 explain可以了解 MySQL 在执行查询时采用的具体执行计划包括访问数据表的方式、使用的索引、连接表的顺序等信息。这些信息对于优化查询性能至关重要。
基本概念
EXPLAIN 执行计划支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。我们一般多用于分析 SELECT 查询语句要获取一条sql语句的执行计划只需要在语句前加上explain关键字即可。
explain sql语句;执行计划的返回结果一般是这样的
----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | dept_emp | NULL | ALL | NULL | NULL | NULL | NULL | 331143 | 100.00 | Using where |
----------------------------------------------------------------------------------------------------------------------返回结果中各字段的含义解释如下
列名含义idSELECT 查询的序列标识符select_typeSELECT 关键字对应的查询类型table用到的表名partitions匹配的分区对于未分区的表值为 NULLtype表的访问方法possible_keys可能用到的索引key实际用到的索引key_len所选索引的长度ref当使用索引等值查询时与索引作比较的列或常量rows预计要读取的行数filtered按表条件过滤后留存的记录数的百分比Extra附加信息
字段释意
id
查询的序列标识符用于表示查询的执行顺序。值越大优先级越低执行顺序越靠后。
select_type
查询的类型主要用于区分普通查询、联合查询、子查询等复杂的查询常见的值有
SIMPLE: 简单查询不包含子查询或 UNION。PRIMARY: 最外层的 SELECT 查询。SUBQUERY: 子查询中的第一个 SELECT。DERIVED: 派生表子查询中的 FROM 子句。UNION: UNION 操作中的第二个或后续的 SELECT 查询。UNION RESULT: UNION 的结果集。
table
查询用到的表名。
type重要
查询执行的类型描述了查询是如何执行的。常见的类型如下这些类型的性能从最优到最差排序为system const eq_ref ref range index ALL
system如果表使用的引擎对于表行数统计是精确的如MyISAM且表中只有一行记录的情况下访问方法是 system 是 const 的一种特例。const表中最多只有一行匹配的记录一次查询就可以找到常用于使用主键或唯一索引的所有字段作为查询条件。eq_ref当连表查询时前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式常用于使用主键或唯一索引的所有字段作为连表条件。ref使用普通索引作为查询条件查询结果可能找到多个符合条件的行。range对索引列进行范围查询执行计划中的 key 列表示哪个索引被使用了。index查询遍历了整棵索引树与 ALL 类似只不过扫描的是索引而索引一般在内存中速度更快。ALL全表扫描。
possible_keys
possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL 则表示没有索引可以使用。
key重要
key 列表示 MySQL 实际使用到的索引。如果为 NULL则表示未用到索引。
Extra重要
这列包含了 MySQL 解析查询的额外信息通过这些信息可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下
Using index表明查询使用了覆盖索引不用回表查询效率非常高。Using index condition表示查询优化器选择使用了索引下推这个特性。Using where表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。Using filesort在排序时使用了文件排序而不是索引排序通常是因为无法使用索引进行排序。Using temporaryMySQL 需要创建临时表来存储查询的结果常见于 ORDER BY 和 GROUP BY。Using join buffer (Block Nested Loop)连表查询的方式表示当被驱动表的没有使用索引的时候MySQL 会先将驱动表读出来放到 join buffer 中再遍历被驱动表与驱动表进行查询。
优化慢sql
sql优化方案
根据explain执行计划的返回结果我们可以根据以下字段进行sql优化
通过key和key_len检査是否命中了索引索引本身存在是否有失效的情况通过type字段查看sql是否有进一步的优化空间是否存在全索引扫描或全表扫描通过extra字段判断是否出现了回表的情况如果出现了可以尝试添加索引或修改返回字段来修复
深分页优化查询
传统分页
传统分页通常使用 OFFSET 和 LIMIT 来实现
SELECT * FROM table_name ORDER BY column_name LIMIT 10 OFFSET 1000;这种方法对于小数据集或页数较小时效果较好但在数据量非常大的情况下OFFSET 的值越大数据库需要扫描的行数就越多性能会急剧下降。
深分页
深分页通过避免使用 OFFSET 来提高性能
1.覆盖索引子查询 这种方法通过子查询使用覆盖索引快速定位到分页的起始位置外部查询从该位置获取实际数据避免大量数据扫描和回表操作。
如本例中通过子查询定位到了第100001页的起始位置向后获取100行数据。
SELECT * FROM users WHERE id (SELECT id FROM users ORDER BY id LIMIT 100000, 1) LIMIT 100;这种方法避免了大量数据扫描适用于有索引列的情况。
2.存储分页结果 另一种方法是将分页结果存储在缓存如 Redis或临时表中从而避免频繁查询数据库。例如
-- 第一次查询并缓存结果
SELECT * FROM table_name ORDER BY column_name LIMIT 1000;
-- 将结果缓存起来随后从缓存中进行分页这种方法适用于需要多次访问相同分页结果的场景。
