MYSQL排错指南学习笔记（一）排查及存储引擎修复

翻到了之前整理的学习笔记，跟大家分享一下，虽然书里的版本有点老，但是还有一些是值得借鉴和学习的部分。

1.语法错误(通用查询日志使用) 例如accessible在5.1版本是一个保留字需要语句加引号 5.0版本正常 select id from t1 where accessible=1; 5.1版本应该变为 select ‘id’ from ‘t1’ where ‘accessible’=1; 查询sql可能由应用程序自动生成或者在存储库中由第三方库动态生成

检查mysql通用查询日志：包含mysql服务器收到的每条独立查询（很多产品日常运行不会开启，因为高负载情况下增长迅速，且写入日志会消耗mysql服务器的资源）5.1版本后可以临时打开通用查询日志

打开通用查询日志：

SET GLOBAL general_log='on';

也可以设置将日志记录在表中

SET GLOBAL log_output='table';

现在可以运行程序，迭代执行代码，查询通用日志记录表

select * from mysql.general_log\G

找到问题后记得关闭通用查询日志；

SET GLOBAL general_log='off';

2. explain 的extended 扩展能够在原本explain的基础上额外的提供一些查询优化的信息，这些信息可以通过mysql的show warnings命令得到。

EXPLAIN EXTENDS语句\G

show warnings\G

mysql服务器不总是按照语句输入顺序执行，会调用优化器构造一个更好的执行计划 EXPLAIN EXTEND 后，show warnings 看的是优化后的查询 show warnings输出包含两个主要信息 1.通过哪个表解析值 2.包含了优化后的语句，和服务器从哪个表接受的值

3.当有错误码的时候，可以通过perror工具获取

perror 跟错误码

4.查询操作突然报错，但是确信查询本身没问题 大多数是复制环境中主从节点数据不一致造成的，常见情形是期望唯一值的时候出现重复值。有时候在select才会出错，而insert时候没有问题。 mysql复制不会检查数据一致性，对同一个表，同时使用sql复制和从节点上的用户线程更新会使数据与主服务器不同，导致随后的复制事件失败。

通常建立在从服务器只读的复制环境下，错误产生有两种可能的原因： 要么主服务器插入错误数据，要么数据复制时损坏

先检查主服务器数据是否有错误 master>select * from t1; 主服务器正常，问题原因在复制层。复制看起来运行正常，猜想主节点有逻辑错误

查看表定义

show create table t1\G

5.慢查询 主要三个技巧；优化sql，优化表（包括增加索引），优化服务器

EXPLAIN 第一行 type 展示连接（join）的执行方式 操作单表时，explain也会报告连接，mysql的内部优化器把每个请求当成一个连接

type：连接类型 最关键的一列 效率（const>eq_ref>ref>range>index>all） const:查询索引字段，并且表中最多只有一行匹配（好像只有主键查询只匹配一行才会是const，有些情况唯一索引匹配一行会是ref） eq_ref 主键或者唯一索引 ref 非唯一索引（主键也是唯一索引） range 索引的范围查询 index (type=index extra = using index 代表索引覆盖，即不需要回表) all 全表扫描（通常没有建索引的列）

增加索引

alter table t1 add index(id);

索引有时候也会减慢查询，这时候删除索引或者使用忽略索引（IGNORE INDEX）--------可能其他语句都还需要改索引 也可以使用强制索引（FORCE INDEX）使优化器知道你要使用的索引 应该避免这两种在生产环境使用 对单一表使用这两种方法相对安全。

服务器选项： 内存中临时表的大小，排序缓冲区 针对特定存储引擎（如InnoDB）

缓冲区（buffer size）牢记以下要素 a1.交换区（swapping） mysql服务器在所需内存都来自物理内存时运行最快，当用到交换区时，性能显著下降 有一些缓冲区是针对每个用户线程的，要决定缓冲区需要多少内存，公式： 最大连接数 缓冲区大小（max_connectionsbuffer_size）来计算 这个值不是决定性的，因为mysqld可以分配多于你指定的大小的内存 a2.启动时间 mysqld需要分配的内存越多，其启动时间越长 a3.过期数据 伸缩性问题，大部分时候来自线程间的缓存共享。扩充缓冲区会产生内存碎片，服务器运行数小时后会发现内存碎片问题，旧的数据从缓冲区移除以给新数据腾出空间时候，会导致高速运转的服务器突然变慢。

6.在查询计划前后查询Handler_%的状态检查是否使用索引：

show status like 'Handler_%';

修改例子，使其可以更新为空的列 update items set description = ‘no description’, additional = ‘no description comments’ where description is null; 语句没有执行，因为数据在之前损坏了，每个字段值为0，而不是null 查看处理程序变量 handler_read_rnd_next很高，改值代表从datafile中读取下一个值的频繁程度。过高的值一般使用了全表扫描 headler_read_key表示读取索引的请求数目。这里相对于handler_read_rnd_next很低，意味着大部分的读取都没有使用的索引。 handler_commit 和 handler_read_first增长缓慢，表示事物提交次数和读索引中第一项次数。 header_read_first值为1，表明我们请求服务器读取索引中第一条记录，可以当做全表扫描的标志

索引会影响插入效率，每次插入都需要更新索引。 一种加速插入的方式是批量插入

7.服务器无响应 mysql客户端收到严重错误信息， “在请求中丢失与服务器的连接”或者“服务器已停止” 主要两个原因：服务器问题崩溃，滥用连接选项（超时选项或者 max_allowed_packet） 例如： 先借助进程状态监控器看是否服务器真崩溃 如果服务器崩溃后，运行了mysqld_safe或者其他守护进程重启服务器，错误日志将包含表明服务器重启信息 当mysqld启动，错误日志会有类似如下信息： 如果有这部分信息，则服务器已经重启了。如果没有信息，服务器已经重启并在运行，那么丢失连接问题最有可能是因为滥用连接选项导致的。

可以查询状态变量uptime，查看服务器已启动时间，单位是秒

SHOW GLOBAL STATUS LIKE 'uptime';

错误日志中有如下崩溃原因关键行 意味着mysql服务器在向操作系统申请资源（例如，访问文件或内存）后终止了，得到了错误代码11 在大多数系统中，这个信号代表分段错误 在某个线程中导致服务器崩溃的请求的相关日志周静提取的摘要信息： 重新执行（在测试环境） 这是一个已知的bug#47780，5.0.88之后版本修复了

特定查询会引起崩溃，服务器的运行环境也可能引起崩溃，最常见的原因是缺少可用的内存（RAM）,尤其是用户分配超大缓冲区时。

8.存储引擎问题（MyISAM和InnoDB） 与存储引擎相关的错误要么反馈到客户端，要么记录在错误日志文件中，一般，存储引擎名字也会出现在错误消息中，偶尔用perror也查不到的位置去错误码，问题来自存储引擎。 常见存储引擎问题：数据损坏 不一定总是存储引擎的错误，也可能是磁盘损坏，系统崩溃或者mysql服务器崩溃等外部问题。kill -9终止服务器进程，就可能导致数据损坏，可以尝试CHECK TABLE命令。

a）MyISAM损坏 三个文件一组保存每张表 table_name.frm 保存表的结构（schema） table_name.MYD 文件存储数据 table_name.MYI 文件保存索引 崩溃会损坏数据文件或者索引文件，或者二者都损坏了，这种情况，访问表，获得类似于 的错误消息，可以通过repair或crashed来判断是否表损坏。

SQL语句中用 CHECK TABLE和  REPAIR TABLE针对数据损坏问题，shell里也可以使用 myisamchk工具进行同样的工作（不必访问正在运行的mysql服务器）

通过SQL修复MyISAM CHECK TABLE t2; 这是一个损坏的表的输出示例。解决问题第一步是执行不带参数的REPAIR TABLE命令 REPAIR TABLE t2; 表被修复了，可以再次执行CHECK TABLE 进行确认 CHECK TABLE t2; 如果REPAIR TABLE 没起到效果，可选择其他两个选项： REPAIR TABLE EXTENDED 执行速度比REPAIR TABLE慢很多，但是可以修复99%的错误 作为最后的选择，可以执行 REPAIR TABLE USE_FRM命令，该命令不会相信索引文件中的信息。他会删除索引并利用table_name.frm文件中的描述重建索引，并通过table_name.MYD文件填充键对应的值。

为了达到同样的目的，可以使用mysqlcheck工具，该工具通过向服务器发送check和repair命令进行工作，具有 --all-databases,改参数可以帮助用户高效地执行表的维护。 mysqlcheck像其他用户一样连接到mysql服务器工作，因此可以远程使用。

使用myisamchk修复MyISAM表（不建议使用） 该命令包含许多额外的表维护选项 myisamchk可以直接访问表文件，而无需启动mysql服务器，myisamchk需要对表文件保持独立的，排他的访问，因此用户也应该避免在mysql服务器运行过程中使用该工具。 如果必须在服务器运行期间使用该工具，则先执行FLUSH TABLES 和LOCK TABLE table_name WRITE 语句，然后等待直到最后的查询返回命令提示符，接着在并行会话中执行myisamchk。如果有除了myisamchk之外的进程在myisamchk运行访问期间访问表， 则可能导致更糟糕的损坏 一条基本恢复命令 myisamchk --backup --recover t2 其中，- -backup选项通知myisamchk在尝试恢复表之前进行数据文件备份，- -recover选项执行实际恢复，如果这个命令不够，使用- -safe-recover选项，会使用在早期的mysql版本中存在的恢复模式进行恢复，并且会找到简单的–recover无法发现的问题。此外还有更加严格的- -extend-check 也可以使用- -sort-recover选项，会使用排序来解析键，甚至在临时文件很大的时候 选项- -description 选项比较有用，会输出表的描述信息，结合-v或者等价的-version，输出额外的信息，可以指定两次-v或者三次-v去获得更多的信息。

b）InnoDB数据损坏 InnoDB在共享的表空间中存储器其数据和索引，如果服务器在创建表时以选项- -innodb_file_per_table选项启动，那么它会有自己的数据文件，但是表的定义依然在共享表空间里。 InnoDB是带有事务的存储引擎，并且内部机制会自动修复大部分数据损坏错误，它会在服务器启动时进行修复。 下面摘要了MYSQL企业级备份（MEB）也称作InnoDb热备份，中执行mysqlbackup --copy-back 命令完成备份后，从错误日志中摘录的，展示了典型恢复情况 有时候损坏严重，InnoDB无法在没有用户交互的情况下完成修复。 有–innodb_force_recovery启动选项，可以设置0~6值，（0不强制修复，1最低级别，6最高级别） 如果发生损坏可以从1开始依次济宁尝试–innodb_force_recovery选项，直到可以启动服务器并可以访问有问题的表为止。 检查应该发现哪个表损坏了，用SELECT INTO OUTLINE将表转储到文件中，然后使用DROP和CREATE命令重新创建表，最后用–innodb_force_recovery=0重新启动服务器，然后加载转储数据。如果问题还存在，找到其他损坏的表继续操作。 当需要–innodb_force_recovery的值是正数时修复数据库，错误日志有类似提示 InnoDB在写实际数据前会立即对数据、索引和日志页写校验和，并在从磁盘读数据之后会立即确认校验和。 通常，一旦InnoDB数据损坏，意味着磁盘或者内存有问题。