求可列出MySQL中全表扫描语句及对应执行用户的脚本

没问题，我明白你的需求——sys.x$statements_with_full_table_scans确实能帮我们找出全表扫描的语句，但它没关联执行用户信息，对于ERP这种多用户场景来说确实不够用。下面我给你几个实用的脚本，结合MySQL的performance_schema和information_schema来实现这个需求：

脚本1：查询历史全表扫描语句及对应执行用户 #

这个脚本会关联历史执行记录，能覆盖过去一段时间内的全表扫描操作（前提是events_statements_history_long有足够的保留时长）：

SELECT
  s.user AS 执行用户,
  t.query AS 全表扫描语句,
  t.digest AS 语句摘要ID,
  t.exec_count AS 执行次数,
  t.total_latency AS 总延迟,
  t.rows_examined AS 扫描行数,
  t.tables_full_scanned AS 涉及全表扫描的表数量
FROM
  sys.x$statements_with_full_table_scans t
JOIN
  performance_schema.events_statements_history_long h ON t.digest = h.digest
JOIN
  performance_schema.threads th ON h.thread_id = th.thread_id
JOIN
  information_schema.processlist s ON th.processlist_id = s.id
GROUP BY
  s.user, t.digest, t.query
ORDER BY
  t.total_latency DESC;

脚本说明： #

• 用sys.x$statements_with_full_table_scans获取全表扫描语句的核心统计数据（执行次数、总延迟等）
• 通过events_statements_history_long关联到执行该语句的线程，再通过threads和processlist拿到执行用户
• 分组去重避免同一用户的同一语句重复显示，最后按总延迟降序，优先排查最耗时的全表扫描

脚本2：查询当前活跃的全表扫描语句及用户 #

如果只关注当前正在执行的全表扫描操作，可以用这个更轻量化的脚本：

SELECT
  pl.user AS 执行用户,
  t.query AS 全表扫描语句,
  t.digest AS 语句摘要ID,
  t.exec_count AS 累计执行次数,
  t.total_latency AS 累计总延迟,
  t.rows_examined AS 累计扫描行数,
  t.tables_full_scanned AS 涉及全表扫描的表数量
FROM
  sys.x$statements_with_full_table_scans t
JOIN
  performance_schema.events_statements_current c ON t.digest = c.digest
JOIN
  performance_schema.threads th ON c.thread_id = th.thread_id
JOIN
  information_schema.processlist pl ON th.processlist_id = pl.id
ORDER BY
  t.total_latency DESC;

关键注意事项 #

• 确保performance_schema相关功能开启
  先检查消费者是否启用：

  SELECT NAME, ENABLED FROM performance_schema.setup_consumers WHERE NAME LIKE '%statements%';
  

  如果events_statements_current、events_statements_history、events_statements_history_long的ENABLED是NO，执行以下命令开启（重启MySQL后会失效，要永久生效需在my.cnf中配置performance_schema_setup_consumers='events_statements_current,events_statements_history,events_statements_history_long'）：

  UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%statements%';
  

• 关于x$前缀
  脚本里用了x$statements_with_full_table_scans，它返回的是原始数值（比如延迟是纳秒），如果想要可读性更好的格式化数值（比如毫秒、秒），可以去掉x$，改用sys.statements_with_full_table_scans。
• 查看具体语句
  query列显示的是参数化后的语句摘要，如果想看某条语句的具体执行实例，可以通过digest关联events_statements_history_long的sql_text字段查询。

内容的提问来源于stack exchange，提问作者Marco Antônio Borges