mysql - 如何识别缺少索引的慢查询？

问题描述

使用SET GLOBAL log_slow_verbosity='query_plan,explain'; 在缓慢的日志中，我得到了很多输出，但我很难理解解释。

# User@Host: root[root] @  [10.0.1.5]
# Thread_id: 31  Schema: enterprise  QC_hit: No
# Query_time: 0.654855  Lock_time: 0.000245  Rows_sent: 50  Rows_examined: 279419
# Rows_affected: 0
# Full_scan: Yes  Full_join: Yes  Tmp_table: Yes  Tmp_table_on_disk: Yes
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  external_property_groups_areas  ALL     unique_id_area,search_id_area,search_country    NULL    NULL    NULL    20      20.00   100.00  100.00  Using temporary; Using filesort
# explain: 1    SIMPLE  external_property_level_1_buildings     ref     unique_id_building,building_id_area_id  building_id_area_id     5       enterprise.external_property_groups_areas.id_area        3  6.00     100.00  100.00
# explain: 1    SIMPLE  external_property_level_2_units ref     unit_building_id,property_level_2_created_by    unit_building_id        4       enterprise.external_property_level_1_buildings.id_building  25.13    100.00  100.00  Using index condition
# explain: 1    SIMPLE  ut_unit_types   eq_ref  unique_property_type,query_optimization_designation      unique_property_type     1022    enterprise.external_property_level_2_units.unit_type  1       1.00    100.00  100.00  Using where; Using index
# explain: 1    SIMPLE  property_level_2_units  eq_ref  PRIMARY,property_level_2_organization_id        PRIMARY 1530    enterprise.external_property_level_2_units.external_id,enterprise.external_property_level_2_units.external_system_id,enterprise.external_property_level_2_units.external_table,const   1       0.98    100.00  100.00
# explain: 1    SIMPLE  a       eq_ref  unique_id_unit,unit_building_id unique_id_unit  4       enterprise.property_level_2_units.system_id_unit 1       0.98    100.00  100.00  Using where
# explain: 1    SIMPLE  c       eq_ref  unique_id_building      unique_id_building      4       enterprise.a.building_system_id  1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  b       ref     property_property_type  property_property_type  4       const   142     458.00  100.00  0.17    Using where
# explain: 1    SIMPLE  property_groups_countries       ALL     country_names,coutnry_codes     NULL    NULL    NULL    245     245.00  100.00  0.31    Using where; Using join buffer (flat, BNL join)
#

我如何识别他们查询的慢部分？
是否有快速识别缺失索引的捷径？

还有我的会议的截屏视频

如果您能指出一些资源来帮助我弄清楚如何提高这些 SQL 查询的性能，那就太好了。

标签： mysqlperformancemariadb

解决方案

您的问题很笼统，所以我将回答您的具体问题，然后将您发送到可以找到更多信息的地方。

日志上的解释是好的，但你并不是唯一一个无法阅读它们的人。使用日志来识别您的慢查询。稍后使用EXPLAIN（和其他工具）来调试正在发生的事情。很高兴将它记录在日志中，但是为了更好的可读性，请像这样在您的数据库中对其进行格式化：

回答您的问题：

我如何知道索引是否未被使用？

type（和key）列会告诉你。typeALL表示正在使用全表扫描，可能的键/键将是NULL. 那是为了扫描。type const，ref或range通常是首选（有更多策略）。对于排序（和其他问题），您会在Extra:string上找到Using filesort。这意味着需要对结果进行第二次排序，并且在某些情况下，索引将有助于自动按顺序获取结果。

这是另一个查询的示例，这次使用索引：

这是一种简化，因为有很多方法可以使用索引来加速结果（ICP、覆盖索引、max()、...）。

这里没有足够的空间来讨论JOINs 和子查询，其中可以通过排序和重写来获得更好的策略。

我如何识别他们查询的慢部分？

有2个选项：

分析查询（这将为您提供在每个查询步骤上花费的每个阶段的时间），这可以通过某些查询来完成show profile或启用它performance_schema。典型输出：

SHOW PROFILE CPU FOR QUERY 5;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000042 | 0.000000 |   0.000000 |
| checking permissions | 0.000044 | 0.000000 |   0.000000 |
| creating table       | 0.244645 | 0.000000 |   0.000000 |
| After create         | 0.000013 | 0.000000 |   0.000000 |
| query end            | 0.000003 | 0.000000 |   0.000000 |
| freeing items        | 0.000016 | 0.000000 |   0.000000 |
| logging slow query   | 0.000003 | 0.000000 |   0.000000 |
| cleaning up          | 0.000003 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+

handler statistics，它将为您提供与时间无关的扫描策略指标和每个扫描的行数：

最后一个可能看起来有点不友好，但是一旦你理解了它，你就可以很容易地看到索引的使用和全扫描，通过知道内部引擎调用是什么。

是否有快速识别缺失索引的捷径？

是的，如果您已启用performance_schema并有权访问sys数据库SELECT * FROM sys.statement_analysis;，将为您提供一个名为“ full_scan”的列，该列将为您提供执行完整扫描的查询（不使用索引的扫描）。然后，您可以按rows_examined、rows_examined_avg、avg_latency等按重要性顺序排列。

如果您不想或不能使用 performance_schema，请使用日志pt-query-digest从 percona-toolkit获取这些数字：

如果检查的行与发送的行相比非常大，则索引可能是原因。

总之，日志可用于识别查询——使用它们与 performance_schema 或 pt-query-digest 聚合它们。但是，一旦您确定了最严重的违规查询，请使用其他工具进行调试。

在我的幻灯片“使用 MySQL 8.0 和 MariaDB 10.3进行查询优化”中，我更多地讨论了如何识别慢查询以及如何进行查询优化的详细信息。我这样做是为了谋生，查询优化是我的热情所在，我建议你看看它们（我不是在卖给你一本书，它们是免费的，并且具有知识共享许可）。

mysql - 如何识别缺少索引的慢查询？

问题描述

解决方案

推荐阅读