MySQL故障排除工具详解

本文由编程笔记小编整理，主要内容涉及MySQL问题排查工具的相关知识，旨在为读者提供有价值的参考。 ### 知识准备 #### 索引 - **定义**：索引是存储引擎用于快速查找记录的数据结构。 - **类型**： - B-Tree：适用于全键值、键值范围或键最左前缀查询。 - **建议创建索引的列**： - WHERE、JOIN、GROUP BY、ORDER BY等语句中使用的列。 - **选择索引列的顺序**： 1. 经常被使用到的列优先。 2. 选择性高的列优先（选择性 = distinct(col) / count(col)）。 3. 宽度小的列优先（宽度 = 列的数据类型）。 #### 慢查询 ##### 原因 - 未使用索引。 - 索引不优。 - 服务器配置不佳。 - 死锁。 ##### 命令 ###### 查看版本 - `mysql -V`：客户端版本。 - `SELECT VERSION()`：服务器版本。 ###### EXPLAIN执行计划 - **Type**： - const, system：最多匹配一个行，使用主键或唯一索引。 - eq_ref：返回一行数据，通常在联接时出现，使用主键或唯一索引。 - ref：使用索引的最左前缀，但不是主键或唯一索引。 - range：索引范围扫描，从某一点开始返回匹配的行。 - index：按索引顺序进行全表扫描，无需排序但需全表扫描。 - all：全表扫描，不推荐。 - **Extra**： - using index：索引覆盖，仅使用索引，避免访问表。 - using where：在存储引擎检索行后再过滤。 - using temporary：使用临时表，通常在GROUP BY、ORDER BY时出现，应避免。 - using filesort：进行额外排序，当ORDER BY未使用索引时发生，应避免。 - **Possible_keys**：显示查询可能使用的索引。 - **Key**：优化器选择的索引。 - **Rows**：MySQL估算的为了找到所需行需要检索的行数（不是结果集的行数）。 - **Key_len**：使用的索引左前缀的长度（字节数），表示使用了索引中的哪些字段。 - 定长字段：int占4个字节，date占3个字节，timestamp占4个字节，char(n)占n个字节。 - NULL字段：需要加1个字节，建议设为NOT NULL。 - 变长字段：varchar(n)，需要 (n * 编码字符所占字节数 + 2) 个字节，如utf8编码，每个字符占3个字节，则长度为 n * 3 + 2。 - **强制使用索引**：USE INDEX（建议）或FORCE_INDEX（强制）。 ###### SHOW命令 - **SHOW STATUS**： - 查看SELECT语句的执行次数：`SHOW GLOBAL STATUS LIKE 'Com_select';` - 查看慢查询的个数：`SHOW GLOBAL STATUS LIKE 'Slow_queries';` - 表扫描情况：`SHOW GLOBAL STATUS LIKE 'Handler_read%';` 如果 `Handler_read_rnd_next / Com_select > 4000`，需要考虑优化索引。 - **SHOW VARIABLES**： - 查看慢查询相关配置：`SHOW VARIABLES LIKE 'long_query_time';` - 将慢查询时间线设置为2秒：`SET GLOBAL long_query_time=2;` - 查看InnoDB缓存：`SHOW VARIABLES LIKE 'innodb_buffer_pool_size';` - 查看InnoDB缓存的使用状态： - 缓存命中率 = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100% - 缓存率 = (Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total) * 100% - `SHOW PROFILES;`：跟踪整个执行过程中的资源消耗情况（会话级）。 - `SHOW PROCESSLIST;`：查看当前正在运行的线程及其状态。 - `SHOW ENGINE INNODB STATUS;`：分析死锁，但需要super权限。