mysql排错

目录

基础

1.语法错误

2.select返回错误结果

3.当错误可能由之前的更新引起时

4.获取查询信息

5.追踪数据中的错误

6.慢查询

7.当服务器没有响应的时候

8.特定于存储引擎的问题及解决方案

1.myisam损坏

2.innodb数据损坏

9.许可问题

并发问题

1.锁和事务

2.锁

3.事务

1.隐藏查询

2.死锁

3.隐式提交

4.并发如何影响性能

1.为并发问题监控innodb

2.为并发问题监控其他资源

 

---

基础

1.语法错误

sql语句的关键字写错。（把语法错误当作排错的第一步）

如果sql语句是程序代码生成的，应该在调试程序代码的时候将sql语句打印。

如果sql语句是调用第三方库生成的，无法得到sql语句，那么可以开启通用查询日志。

set global general_log = "on";

2.select返回错误结果

现象：看不到更新结果，展示的顺序错误，查询到了非预期的结果。

造成以上现象的原因有如下：

1.sql查询错误。

如果一个sql语句没有按照预期的结果输出，

2.数据库中的数据没有按照预期工作，可以将其拆分为小段，然后分析每一部分直到找到产生错误的原因。

3.当错误可能由之前的更新引起时

当select查询返回非预期结果，也并不总是代表sql查询语句有问题，也有可能是已经执行的insert, update, delete等未生效。

4.获取查询信息

受影响的行

需要在程序代码中查看受影响的行，可以调用如下方法.

mysql_affected_rows()

如果有发生改变，该函数会返回一个正数，如果没有改变返回0，-1代表发生错误。

匹配的行数

输出表明有多少满足where条件。

mysql_info()

返回结果示例：

rows matched :m

对于更新操作，返回结果如下：

Rows matched: # Changed: #  Warnings:#   (#依次代表匹配的行数，修改的行数，警告的行数)

被修改的行数：mysql_info() 解析Changed:#

表明实际有多少行修改了

警告：数目和消息，调用mysql_info(),解析#Warnings，也可以调用mysql_warning_count()

如果有警告，可以执行show warnings。

也可以执行mysql_sqlstate()。返回sql状态，如42000代表语法错误，00000代表没有错误和警告。

错误：mysql_error(),返回最近一次错误的文本信息。

可以在终端通过perror 错误码 方式显示错误信息。

5.追踪数据中的错误

主从数据库数据不一致。

6.慢查询

sql程序常见的问题就是性能退化。

1.通过explain 调优查询本身

2.表调优和索引

与where,join,group by语句相关的列上添加索引可以加速查询。在order by相关的列上添加索引，使服务器排序更高效排序。

索引也有减慢查询的时候（通过explain分析）。

在这种情况下，应该删除索引或者使用忽略索引(ignore index)的语句(如果其他的查询还需要该索引)。

也可以使用强制索引(force index)让优化器知道想要优化的索引。

不过避免在生产环境使用ignore index与force index

3.何时停止调优

不要只依赖与explain的输出，要衡量实际的查询执行时间。

索引提高查询效率，会降低修改数据查询的性能，尤其insert语句。

4.配置选项的影响

如果已经对查询进行了完全的优化，已经找不到进一步优化的方法，但还是很慢。可以根据服务器选项调节对查询的影响。

内存中临时表的大小，排序缓冲区等，针对特定的引擎(innodb).

调整服务器选项是个全局的影响，因为每个修改都可能对该服务器上的每个查询造成影响。

第一：检查缓冲区大小(buffer size)。每个缓冲区都有其存在的特定原因，一般来说大缓冲区意味着高性能（不过仅当请求可以z针对该缓冲区扮演的特定角色使用大容量缓存时）

当然增加缓冲区也是有代价的：

1.交换区：大容量缓冲区可能导致使用到操作系统级别的交换区从而造成性能缓慢，这取决于物理内存大小。一般情况下，mysql服务器在它所需要的内存来自物理内存时运行最快，当使用到交换区的时候，性能显著下降。

当为缓冲区分配的内存大小超过服务器的物理内存大小就会使用交换区。

2.启动时间

mysqlid需要分配的内存越多，启动时间越长。

3.过期数据

伸缩性问题，大部分来自线程间的缓存共享。扩充缓冲区做缓存会产生内存碎片。通常会在服务器运行数小时后发现内存碎片问题，该问题发生在旧的数据需要从缓冲区中移除以给新数据腾出空间的时候，这会导致告诉运转的服务器突然变慢。

总结：当调优分配的时候，把性能作为整体考虑尤为重要，因为每个选项影响到整个服务器。

5.修改数据的查询

优化修改数据的查询，update和delete查询可以使用与select语句一样的条件去限制受影响的行数。

7.当服务器没有响应的时候

有时候收到错误信息：在请求中丢失与服务器的连接   或者   服务器已停止

该错误有可能是如下原因：服务器问题(最优可能是崩溃)，或者滥用连接选项(超时选项或者max_allowed_packet)

确定服务器是否真的崩溃，借助进程状态监控器确认。

8.特定于存储引擎的问题及解决方案

1.myisam损坏

myisam按照三个文件一组保存每张表。

table_name.frm文件包含表的结构(schema),

table_name.MYD 文件存储结构

table_name.MYI 文件保存索引

可以通过检索错误信息关键字 repair或者crashed判断是否表损坏。

check table 表名;
repair table 表名;

sql语句中check table，repair table专门针对数据损坏问题。

shell中使用myisamchk 修复myisam

2.innodb数据损坏

innodb在共享的表空间中存储其数据和索引。如果服务器在创建表时是以选项innodb_file_per_table启动的，那么它也会有自己的数据文件，不过表的定义仍然在共享空间中。

理解表文件是如何存储的，有助于高效地维护数据目录和备份。

innodb是带有事务的存储引擎，并且其内部机制会自动修复大部分数据损坏的错误。会在服务器启动时进行修复。

当数据损坏严重且innodb无法在没有用户交互的情况下完成修复，此时可以设置innodb_force_recovery启动项，取值范围0-6(0不强制修复，1最低级别，6最高级别)。

当修复成功时，可在已修复的表上执行特定类型的请求，不过应避免执行某些特定的命令。不能执行修改数据库的操作不过该选项仍然允许特定的select选择语句和drop语句。

9.许可问题

Mysql有复杂权限方案。

通常遇到的两类权限：

1.有权限连接到服务器的用户无法连接，或者没有权限的用户可以连接。

2.用户可以连接到服务器，但是无法使用他们本该访问的对象，或者可以访问他们无法访问的对象。

应该首先确定是否能连接到服务器，服务器连接成功以后执行以下查询：

select user();
select current_user();

并发问题

并发问题的典型特征：本来优化良好的查询突然变慢。这种变慢可能不是一直发生，偶尔随机出现几次。

并发问题可能会影响主从服务器的sql线程。

1.锁和事务

mysql服务器有内部机制避免用户损坏其他用户插入的数据。

mysql的并发控制机制

mysql使用锁和事务来处理对表的并发访问。

当线程请求数据集的时候就会加锁。在Mysql中，这可以是表，行，页或者元数据。当线程结束处理特定的数据集之后，就会释放锁。

数据库事务：是处理一致性和可靠性工作的最小单位，使得用户可以避免与其他事务交互时可能产生的风险。事务的隔离登记控制其他并发操作中的变化对本事务是否可见。

2.锁

mysql服务器和独立的存储引擎都可以设置锁。读锁(或者叫做共享锁)允许并发线程读取加锁的数据，但是禁止写数据。相反，写锁(又叫做排他锁)组织其他线程的读写操作。在存储引擎里，这些锁的实现方式可以不同，不过一些规则的基本原理是稳定的。

当用select语句从表中查询数据或者通过lock table ... read 语句显示加锁的时候，数据库将会设置读锁。当修改表或者使用lock table ... write语句显示加锁的时候，数据库将会设置写锁。

innodb使用S代表读锁/共享锁，用X代表写锁/排他锁。

mysql4中类型的锁。

表锁：锁住整个表，并发线程不能使用表。当访问表并且该表使用的存储引擎支持表锁的时候产生表锁，直到持有锁的线程解锁该表。

在查询运行缓慢的时候执行:

show processlist;

字段解释如下：

id:mysql服务器运行中的连接线程的id，

user、host、db:客户端连接到服务器时使用的连接选项。

commond:线程中当前执行的命令

time:从线程开始执行命令到现在消耗的时间。

state:线程内部的状态。

info:表明线程当前正在进行的工作。如果展示的是查询语句，表明该语句正在执行，如果值为null,表明线程正在休眠，并等待下一条用户命令。

行锁：粒度更细。仅会锁住一行或者正在被线程访问的任何几行，因此同一个表中的其他行可以被其他并发线程访问。可以修改表中没有被锁住的行。行锁在存储引擎级别进行设置，innodb是当前使用行锁的主要存储引擎。

innodb监控器排错：可以通过show engine innodb status命令调用。

行锁的实现：

1.必须有索引才能实现，否则会自动锁全表，就变成表锁了。

2.两个事务不能锁同一个索引。

参考资料：行锁和表锁

          

页锁：锁住一页，仅存在与比较少见的bdb存储引擎中。

元数据锁：5.5版本的特性。仅对表中的元数据启用，当线程开始使用表的时候，元数据锁会锁住表的所有元数据。

为了确保数据一致性，在有其他事务使用表的情况下，对该表的ddl操作应该阻塞。

当事务开始的时候，会获取所有需要使用的表上的元数据锁，并在事务结束后释放。所有他想要修改这些表定义的线程都需要等待事务结束。

元数据：是数据定义语言或数据描述语言（DDL）语句修改的信息，如create,drop,alter等修改方案的语句。

3.事务

mysql在存储引擎级别提供事务支持。innodb提供事务支持。

通过以下启动事务：

start transaction;  (或者begin)

通过commit提交事务，通过rollback回滚事务。

另一种启动多语句事务的方法是将autocommit变量的值设置为0.将覆盖mysql的默认行为：在每条语句后发送一个隐式的提交命令。当autocommit设置为0以后，需要显示调用commit或rollback命令。

1.隐藏查询

innodb存储引擎把对数据的每个请求都作为事务进行处理。无论事务是单语句还是多语句均可。

2.死锁

当两个或多个竞争事务彼此等待对方释放锁，从而导致事务永远无法终止的情况。在行锁级别，死锁是无法100%避免的。

innodb引擎内部有死锁探测器，当发现死锁的时候，会回滚其中的一个事务，并会报告一个立即可见的错误。

死锁信息可以通过以下命令获取：

show engine innodb status;

这个命令会打印很多信息，只需要关注以下即可：

waiting for this lock to be granted的相关部分(该部分表明事务在等待哪个锁)以及holds the locks 部分(表明阻塞事务的锁的信息)

3.隐式提交

有些语句没有显示调用commit也会提交事务，这种情况称为隐式提交，并且如果你没有意识到你正在提交事务，可能会导致很多问题。

4.并发如何影响性能

如果一个查询突然开始执行缓慢，第一步应该确认是否合理优化过。最简单的方式是在一个隔离的，单线程的环境里去执行该查询。如果该查询仍然执行缓慢，那么它或者需要优化或者最近的大量更新操作导致索引统计数据过期了。

如果一个查询在单线程环境很快完成但是在多线程执行缓慢，基本可以确定遇到了并发问题

1.为并发问题监控innodb

要打开innodb监控器，需要在任何数据库中创建一个叫做innodb_monitor的表。

调试与并发问题相关的时候加上-A 选项。

mysql提供的Innodb监控机制，用于周期性(每15秒)输出innodb运行相关状态(innodb运行状态，表空间状态，表状态等)到mysqlid服务标准错误输出。另外，innodb标准监控和锁监控，也可通过命令：show enigin innodb status输出到控制太。

此部分内容一般输出到mysql error log里。

开启监控状态

1.标准监控

mysql通过检查是否存在名为innodb_monitor的数据表，来判断是否开启标准监控，并打印日志。

需要开启则创建表，需要关闭则删除表。

开启innodb监控

create table innodb_monitor (a int) engine = innodb;

关闭innodb监控

drop table innodb_monitor;

从5.6.16版本以后可以通过设置系统参数(innodb_status_output)方式开启或者关闭标准监控。

set global innodb_status_output = on;
set global innodb_status_output = off;

2.锁监控

基于系统表innodb_lock_monitor，mysql会检查是否存在innodb_lock_monitors的数据表，判断是否开启锁监控，并打印日志。

开启锁监控与关闭锁监控的命令如下：

create table innodb_lock_monitor (a  int) engine = innodb;
drop table innodb_lock_monitor;

自5.6/16版本后，可以通过设置系统参数innodb_status_output_locks开启或者关闭锁监控。

set global innodb_status_output = on;(前提是首先要开启标准监控)
set gloabl innodb_status_output_locks = on;
set gloabl innodb_status_output_locks = off;

3.开启表空间监控

mysql通过检查系统表innodb_tablespace_monitor来判断是否开启表空间监控，并打印日志。

开启与关闭表空间监控命令如下

create table innodb_tablespace_monitor (a int) engine = innodb;
drop table innodb_tablespace_monitor;

注意：表空间监控暂不支持参数方式配置。

4.表监控

mysql检查系统表innodb_table_monitor来判断开启表监控并打印日志。

开启关闭表监控的命令如下：

create table innodb_table_monitor (a int) engine = innodb;
drop table innodb_table_monitor;

注意：表监控也不支持参数配置。

监控复位

mysql服务重启后，之前配置的所有监控都被复位，处于关闭状态。基于系统表方式开启的监控，在mysql重启后即使表存在，监控也不会生效，需要drop表后再create才会生效。基于系统参数方式开启的监控，mysql重启后，相关参数值都是off，需要重新设置对应参数。

错误日志大小

在不停机或者重庆情况下，没15秒会输出一次innodb运行状态信息到错误日志。会使日志变得越来越大，建议在需要的时候开启，不需要的时候关闭。

2.为并发问题监控其他资源

使用show processlist

或者select ... from information_schema.processlist.

并发问题并不总是停留在sql或者存储引擎，mysql服务器的多个线程也会共享内存和cpu。

性能问题还可能是由于操作系统的资源引起的，如文件描述符和cpu。系统上文件描述符的数量会限制服务器可以创建的连接数量，可以同时打开的表的数量，甚至同一个表分区的数量。如果表的分区数大于可用文件操作符的数量，会发现打开一个表都不可能。

 

高效使用mysql问题排查工具

1.show processlist命令与查看information_schema.processlist库

2.show engine innodb status与开启innodb监控

3.information_schema中的表与并发问题相关的部分如下：

innodb_trx:当前运行的所有事务的列表

innodb_locks:包含事务持有的当前锁的相关信息以及每个事务等待锁的信息。

innodb_lock_waits:包含事务正在等待锁的信息。

------------------从mysql排错指南整理。