面试题：MySQL中SHOW语句与information_schema库在性能优化排查中的应用

Question

在一个复杂的MySQL应用场景中，系统出现性能问题。请阐述如何利用SHOW语句和information_schema库来排查可能存在的性能瓶颈，例如索引使用不合理、锁争用等问题，并举例说明相关的查询语句。

Answer 1

1. 利用 SHOW 语句排查性能瓶颈

• 查看慢查询日志：

  -- 查看慢查询日志是否开启
  SHOW VARIABLES LIKE 'slow_query_log';
  -- 查看慢查询的时间阈值
  SHOW VARIABLES LIKE 'long_query_time';
  

  若慢查询日志未开启，可通过修改配置文件或执行 SET GLOBAL slow_query_log = 'ON'; 开启，设置合适的 long_query_time 可捕获慢查询。慢查询日志记录了执行时间超过阈值的SQL语句，通过分析这些语句可找到性能瓶颈。
• 查看当前状态：

  SHOW STATUS;
  

  此命令可获取MySQL服务器的各种状态信息，例如 Threads_connected 表示当前连接数，Innodb_row_lock_current_waits 可查看当前InnoDB行锁等待数，通过监控这些指标可发现锁争用等性能问题。
• 查看表状态：

  SHOW TABLE STATUS LIKE 'your_table_name';
  

  能获取表的详细信息，如数据行数（Rows）、平均行长度（Avg_row_length）等，有助于评估表的大小和数据分布情况，分析是否因表数据量过大导致性能问题。
• 查看索引使用情况：

  EXPLAIN SELECT * FROM your_table_name WHERE some_column = 'value';
  

  EXPLAIN 关键字可分析查询计划，key 字段显示实际使用的索引，若显示为 NULL 可能表示索引使用不合理。另外，可通过 SHOW INDEX FROM your_table_name; 查看表的索引结构，确认索引是否正确创建。

2. 利用 information_schema 库排查性能瓶颈

• 查询索引相关信息：

  SELECT * FROM information_schema.statistics WHERE table_schema = 'your_database_name' AND table_name = 'your_table_name';
  

  此查询可获取指定数据库中指定表的所有索引信息，包括索引名称、列名、索引类型等，通过分析这些信息可判断索引是否合理，是否存在冗余索引。
• 查询锁争用信息：

  SELECT * FROM information_schema.innodb_trx;
  SELECT * FROM information_schema.innodb_locks;
  SELECT * FROM information_schema.innodb_lock_waits;
  

  innodb_trx 表记录了当前正在执行的InnoDB事务信息，innodb_locks 表包含当前持有的锁信息，innodb_lock_waits 表记录了锁等待的信息。通过联合查询这些表，可深入分析锁争用的具体情况，如哪个事务持有锁，哪个事务在等待锁等，从而定位锁争用导致的性能瓶颈。

示例：查找索引使用不合理的查询

假设我们有一个 users 表，包含 id、name、email 等字段，且在 name 字段上创建了索引。

-- 查看查询计划
EXPLAIN SELECT * FROM users WHERE name = 'John';

若 key 字段显示为 NULL，说明该查询未使用 name 字段上的索引，可能需要优化查询或索引结构。

示例：查找锁争用问题

假设存在锁争用问题，可通过以下查询分析：

-- 查看当前事务
SELECT * FROM information_schema.innodb_trx;
-- 查看当前持有的锁
SELECT * FROM information_schema.innodb_locks;
-- 查看锁等待情况
SELECT * FROM information_schema.innodb_lock_waits;

通过分析这些查询结果，可定位到具体是哪些事务在争用锁，从而采取相应措施解决锁争用导致的性能问题。