ToB企服应用市场:ToB评测及商务社交产业平台

标题: Linux上慢SQL查询与优化 [打印本页]

作者: 小秦哥 时间: 2024-12-17 17:24
标题: Linux上慢SQL查询与优化
1. 网络慢查询日志：

确认慢查询日志开启：首先要确保数据库已经开启了慢查询日志功能。在 MySQL 中，可以通过查看设置文件（如 my.cnf 或 my.ini）中的相干设置参数来确认，或者利用 SHOW VARIABLES LIKE 'slow_query_log'; 命令查看慢查询日志是否开启以及日志文件的存储位置。如果没有开启，需要按照数据库的设置方法举行开启，并设置符合的查询时间阈值（例如，long_query_time 参数），超过该阈值的 SQL 语句会被记录到慢查询日志中。
获取慢查询日志文件：根据设置文件中指定的日志文件路径，找到慢查询日志文件。如果不确定文件路径，可以再次利用 SHOW VARIABLES LIKE 'slow_query_log_file'; 命令来查看。

2.初步筛选和分类：

按时间排序：查看日志文件中记录的 SQL 语句的执行时间，按照时间从长到短举行排序，以便先关注那些执行时间最长、对系统性能影响最大的 SQL 语句。
按频率分类：有些 SQL 语句可能执行次数很多，但每次执行时间不一定很长；而有些语句可能执行次数较少，但每次执行都非常耗时。可以根据 SQL 语句在日志中出现的频率和总执行时间举行分类，以便分别举行分析。

3. 利用工具分析：

mysqldumpslow：这是 MySQL 自带的一个工具，用于分析慢查询日志。常用的命令选项如下：
-s：用于指定排序方式，如 c（按照记录次数排序）、t（按照时间排序）、l（按照查询时间排序）、r（按照返回的记录数排序）；加上 a 则表示相应的倒序，例如 -sc 表示按照记录次数降序排列。
-t：指定返回的记录条数，例如 -t 10 表示返回前 10 条数据。
-g：后面可以写一个正则匹配模式，用于筛选符合条件的 SQL 语句，巨细写不敏感。例如，mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log 可以查询出耗时最长的前 10 条语句，并按查询时间排序。
pt-query-digest：这是一个功能更强盛的第三方工具，需要下载安装后利用。它可以对慢查询日志举行更具体的分析，常用的选项包括：
- --create-review-table：当利用 --review 参数时，把分析效果输出到表中。
- --create-history-table：利用 --history 参数，把分析效果输出到表中。
- --filter：对输入的慢查询按指定的字符串举行匹配过滤后再举行分析。
- --limit：限制输出效果的百分比或数目，默认值是 20，即将最慢的 20 条语句输出。

4. 分析 SQL 语句自己：

查看执行筹划：利用 EXPLAIN 命令查看 SQL 语句的执行筹划。EXPLAIN 会返回关于 MySQL 如那边理该语句的信息，包括如何连接表、以何种顺序连接表以及是否利用了索引等。重点关注 type、rows、filtered、extra、key 等字段：
type：表示连接范例，性能从好到坏依次为 system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all。例如，all 表示全表扫描，通常性能较差，如果可能应只管制止。
rows：表示 MySQL 估算要找到所需记录需要读取的行数，对于 InnoDB 表，此数字是估计值。
filtered：是一个百分比的值，表示表中符合条件的记录数的比例。
extra：包含有关 MySQL 如何分析查询的其他信息，例如 using filesort（表示按文件排序，一样平常在指定的排序和索引排序不划一时出现）、using index（表示是否利用了覆盖索引）、using temporary（表示是否利用了暂时表，性能较差，需要重点优化）、using where（表示利用了 WHERE 条件过滤）、using index condition（MySQL 5.6 之后新增的索引下推，在存储引擎层举行数据过滤，淘汰回表的数据）等。
key：表示现实用到的索引，一样平常要结合 possible_keys 列一起看，确认是否利用了预期的索引。
检查索引利用环境：确认 SQL 语句中是否利用了符合的索引，以及索引是否有效。如果没有利用索引或者索引利用不当，可能会导致查询性能低下。例如，在条件查询中，如果字段没有建立索引，或者索引的选择性不高，都会影响查询效率。对于经常用于查询条件的字段，应该思量建立索引；对于数据量较大的表，公道的索引设计尤为重要。
分析查询逻辑：检查 SQL 语句的查询逻辑是否公道。例如，是否存在不必要的子查询、复杂的连接操纵或者过多的 OR 条件等。偶然间，优化查询逻辑可以显着提高查询性能。例如，将复杂的 OR 条件转换为 UNION 操纵，或者利用暂时表来处置惩罚复杂的查询逻辑。
查看数据量和数据分布：了解相干表的数据量巨细以及数据的分布环境。如果表中的数据量非常大，纵然利用了索引，查询性能也可能受到影响。此时，可以思量对表举行分区、分表或者数据归档等操纵，以淘汰数据量。同时，数据的分布环境也会影响索引的利用效果，如果某些值在表中出现的频率非常高，索引的选择性可能会降低，从而影响查询性能。

5. 结合系统资源和性能指标分析：

查看系统资源利用环境：在分析慢查询日志时，还需要结合系统的资源利用环境，如 CPU、内存、磁盘 I/O 等。如果某个 SQL 语句执行时占用了大量的系统资源，可能是导致性能标题的原因之一。可以利用系统监控工具（如 top、vmstat、iostat 等）来查看系统资源的利用环境，确认是否存在资源瓶颈。
分析数据库连接和事务处置惩罚：检查数据库的连接数是否过多，以及事务的处置惩罚是否公道。过多的数据库连接会斲丧系统资源，影响数据库的性能；而不公道的事务处置惩罚，如长时间持有事务、事务回滚等，也可能导致性能标题。可以通过数据库的监控工具或者查看数据库的日志来分析连接和事务的处置惩罚环境。

6. 记录和跟踪分析效果：

建立文档：将分析过程和效果记录下来，包括 SQL 语句、执行时间、分析过程中发现的标题以及优化建议等。这样可以方便后续的跟踪和参考，也有助于团队成员之间的交流和协作。
跟踪优化效果：在对 SQL 语句举行优化后，需要再次查看慢查询日志，确认优化后的效果。如果优化后的 SQL 语句仍然出现在慢查询日志中，或者性能没有显着提升，需要重新分析和优化。

7.如何优化慢sql：

利用索引：索引可以加快查询的速度。在频繁查询的列上创建索引，可以淘汰数据库的扫描次数，提高查询效率。
制止利用通配符：%：通配符在查询中会导致全表扫描，影响查询性能。只管制止在查询条件中利用通配符。
制止利用子查询：子查询在一些环境下会导致性能标题，可以尝试利用连接或者其他方式来优化子查询。
利用符合的数据范例：选择符合的数据范例可以淘汰存储空间的占用，提高查询性能。
制止利用 SELECT *：只选择需要的列，不利用 SELECT * 可以淘汰数据的传输量，提高查询效率。
利用 LIMIT 来限制效果集巨细：如果只需要查询部分效果，可以利用 LIMIT 来限制效果集的巨细，淘汰传输数据的量。
利用 EXPLAIN 分析查询筹划：利用 EXPLAIN 命令可以分析查询的执行筹划，查看是否公道利用了索引。
将频繁执行的查询效果举行缓存：将频繁执行的查询效果缓存起来，可以淘汰对数据库的访问，提高查询性能。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)