面试题：MySQL间歇性查询性能问题的诊断与优化

诊断问题

慢查询日志：
- 开启慢查询日志：通过修改MySQL配置文件（如my.cnf或my.ini），设置slow_query_log = 1，并指定slow_query_log_file路径来记录慢查询日志。也可以在运行时通过SET GLOBAL slow_query_log = 1;开启。
- 分析日志：使用mysqldumpslow工具对慢查询日志进行分析。例如，mysqldumpslow -s r -t 10 /var/log/mysql/slow - query.log，这将按照查询执行次数排序，显示前10条慢查询语句，从中找出执行时间长的查询语句及其问题所在，如是否缺少索引等。
性能模式（Performance Schema）：
- 启用性能模式：通过修改配置文件设置performance_schema = on，或者运行时SET GLOBAL performance_schema = on;。
- 分析数据：性能模式提供了丰富的性能视图。例如，可以查询performance_schema.events_statements_summary_by_digest视图，查看不同查询摘要的执行次数、平均执行时间等统计信息，找出性能不佳的查询语句。还可以查询performance_schema.file_summary_by_instance视图分析文件I/O情况，看是否存在I/O瓶颈。
SHOW STATUS命令：
- 获取服务器状态信息：执行SHOW STATUS;，重点关注与查询性能相关的参数，如Threads_connected（当前连接数），若连接数过高可能导致性能问题；Queries（执行的查询总数）、Qcache_hits（查询缓存命中次数）等。如果Qcache_hits较低，说明查询缓存利用率不高，需要考虑调整查询缓存策略。
SHOW ENGINE INNODB STATUS：
- 查看InnoDB引擎状态：执行SHOW ENGINE INNODB STATUS;，关注InnoDB buffer pool相关信息，如Buffer pool hit rate（缓冲池命中率），若命中率低，可能需要增加缓冲池大小。还可以查看row operations部分，分析行锁争用情况，若存在大量行锁争用，可能导致查询性能下降。

优化方案

索引优化：
- 添加缺失索引：根据慢查询日志和性能模式分析结果，对于经常作为查询条件但未建立索引的列添加索引。例如，对于SELECT * FROM users WHERE age > 30;，如果查询缓慢，且age列未建立索引，可以通过CREATE INDEX idx_age ON users(age);添加索引。
- 删除冗余和无效索引：使用SHOW INDEX FROM table_name;查看表上的索引，对于冗余（如已经有CREATE INDEX idx_name_age ON users(name, age);，再创建CREATE INDEX idx_age ON users(age);，后者就是冗余索引）和无效（很少被使用）的索引进行删除，以减少索引维护开销。
查询优化：
- 重写复杂查询：对于复杂的JOIN查询或子查询，可以尝试重写为更高效的形式。例如，将子查询改写为JOIN，有时能提高查询效率。
- 避免全表扫描：通过合理使用索引，避免在查询中出现全表扫描的情况。比如在LIKE查询中，如果以通配符开头（如LIKE '%keyword'）会导致全表扫描，尽量改为LIKE 'keyword%'并建立前缀索引。
配置优化：
- 调整缓冲池大小：根据服务器内存情况和Buffer pool hit rate调整innodb_buffer_pool_size。如果服务器内存充足且缓冲池命中率低，可以适当增大该值，以提高数据缓存能力，减少磁盘I/O。
- 优化查询缓存：如果查询缓存命中率低，可以考虑关闭查询缓存（query_cache_type = 0），或者根据业务特点优化查询缓存配置，如设置合适的query_cache_limit（单个查询结果可缓存的最大大小）等参数。
硬件和系统优化：
- 检查磁盘I/O：如果SHOW ENGINE INNODB STATUS中显示磁盘I/O等待时间长，检查磁盘性能，考虑更换为更快的磁盘（如SSD），或者优化磁盘I/O设置，如调整文件系统参数。
- 调整服务器资源：确保服务器有足够的CPU、内存等资源分配给MySQL。例如，根据业务负载情况，合理调整max_connections（最大连接数），避免因连接过多耗尽系统资源。

星途面试题库

面试题：MySQL间歇性查询性能问题的诊断与优化

知识考点

面试题答案

诊断问题

优化方案