本文共 3526 字，大约阅读时间需要 11 分钟。

误操作的恢复

1. 如果误操作的是 delete/insert/update 等语句，可以通过 Flashback 恢复数据，原理是，修改 binlog 的内容，拿回原库执行，如 bin log 中是 delete 操作，就将其改写为 insert 操作，然后在库中执行，不过前提要求，配置 binlog_format=row 和 binlog_row_image=FULL；

2. 如果是 drop/truncate 等误操作，就需要使用 全量备份 + 增量日志 的方式了；假如有人中午 12 点误删了一个库，恢复数据的流程，首先取最近一次全量备份（当天 0 点），使用备份恢复出一个临时库，然后从 bin log 日志备份里面，取出凌晨 0 点之后的日志；除去误删除数据的语句外，全部应用到临时库，这样就完成恢复；

Kill 不掉的查询

1. 在 Mysql 发生锁等待，查询结果长时间没有返回，可能需要 kill query thread_id，或者 kill connection thread_id 来终止；

2. kill 的原理，是设置一个 killed 状态，并唤醒对应的线程，需要线程自己执行到判断状态的埋点，完成停止操作，所以可能出现长时间 kill 不能停止的线程，是因为没有走到埋点，或者停止操作的逻辑比较多；

3. 针对 kill 不掉的查询，只能通过影响系统环境，然该线程尽快走到埋点；

自增主键不保证连续

1. primary key = unique index + no null，并且一张表中，不能有两个 unique 约束；

2. 自增主键 auto_increment，可以保证是递增的，但不能保证是连续递增，当 auto_increment 字段，在插入时指定 0，null，或未指定，就会将表当前的自增值插入，如果指定了具体的值，就会直接使用该值；

3. 因为插入操作，是先获取自增值，然后进行插入，所以唯一键冲突，导致数据无法插入，但自增值已经累加 1；

4. 语句执行失败，导致事务回滚，自增值也已完成累加，不会回退自增值，这也会导致不能连续自增；

insert into t values(null,1,1);	begin;	insert into t values(null,2,2);	rollback;	insert into t values(null,2,2);	// 自增id的初始值是1，最后一次插入的行是(3,2,2)

5. 批量插入语句，Mysql 不知道一次申请多少个自增值，所以同一语句的执行会多次申请，第一次申请 1 个，第二次申请 2个，随后每次申请是上一次的 2 倍；

6. 批量插入 insert … select，eplace … select 和 load data 的时候，Mysql 提供了 innodb_autoinc_lock_mode 参数设置，表示申请自增值时的加锁粒度，建议设置成 2，且 binlog_format=row，这样加锁是申请完之后立即释放，而不用等到语句结束，并且不会出现数据不一致的问题；

自增主键的上限

1. 表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误；

2. 当表没有主键，InnoDB 默认会添加一个自增的 row_id 作为主键，当 row_id 达到上限后，则会归 0 再重新递增，并且如果出现相同的 row_id，后写的数据会覆盖之前的数据，这往往是不能接受的，所以这也是创建表要有主键的一个原因；

3. Xid 是用来关联 bin log 和 redo log的，是一个内存变量，重启后会清零，但是 Mysql 重启之后会重新生成新的 binlog 文件，这就保证了，同一个 binlog 文件里，Xid 一定是惟一的；

Insert 语句加锁规则

1. insert … select 语句，在可重复读隔离级别下，会给 select 的表中扫描到的记录和间隙加读锁；

2. insert 语句，如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)，因此碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长；

复制表数据

1. 复制表数据，在扫描行数和加锁范围很小的情况，可以使用 insert … select 语句实现，如果为避免对源表加读锁，可以先将数据写到外部文本文件，然后再写回目标表；

2. 一种方法是，使用 mysqldump 命令将数据导出成一组 INSERT 语句，然后在另一个环境执行导入，这种方式不能使用 join 等复杂 where 条件；

// 导出 insert 语句	mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t 		--where="a>900" --result-file=/client_tmp/t.sql	// 执行 t.sql	mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"

3. 另一种方法，直接将结果导出成.csv 文件，然后使用 load data 来导入，可以添加 local，表示读取客户端文件，否则默认是服务端文件，这种方式支持所有 sql 语法，但表结构需要单独生成；

// 导出	select * from db1.t where a>900 into outfile '/server_tmp/t.csv';	// 导入	load data infile '/server_tmp/t.csv' into table db2.t;	// 同时导出表结构定义文件和 csv 数据文件	mysqldump -h$host -P$port -u$user ---single-transaction  --set-gtid-purged=OFF db1 t 		--where="a>900" --tab=$secure_file_priv

4. 还有一种方式，是物理拷贝，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上；

数据库表空间

1. 表空间：表结构 + 表数据，其中表结构占比很小，表数据，既可以存放在共享表空间里，也可以是单独的文件，由参数 innodb_file_per_table 控制的，默认是 ON，即存放在.ibd 为后缀的单独文件中，也是推荐做法；

2. delete 语句删除记录，InnoDB 会把相应记录位置标记为可复用，如果整个数据页被删除了，整个数据页也会被标记为可复用，结果就是不会回收表空间，磁盘文件大小不会改变，这部分可复用空间被称为空洞；

3. 另外插入和更新操作，也可能造成空洞，因为更新是删除原索引位置数据，然后在新索引位置插入数据，插入数据可能分配新的数据页，导致原数据页留有空洞；

4. 重建表，可以缩小表空间，通过命令 alter table A engine=InnoDB，内部逻辑，是新建一个与原来表结构相同的表，然后按照主键 ID 递增的顺序，把数据一行一行地从原表读出来再插入到新表中；

5. 重建表，如果是线上服务表，重建过程中有数据插入，更安全的做法，推荐使用开源的 gh-ost；

乐观锁和悲观锁

1. 乐观锁，是假想线程之间的操作冲突概率很小，然后通过一个版本号字段，更新前先获取版本号，更新操作带上版本号进行更新，这样做就保证了更新不会覆盖；

// 通过 version ，保证了查询到更新的过程，该行记录没有被改变，如果发生了改变，update语句是找不到记录的	select status,version from t_goods where id= #{
   id}    update t_goods set status=2,version=version+1 where id=#{
   id} and version=#{
   version};

2. 悲观锁，是假想线程之间的操作都会发生冲突，所以操作时需要对记录加锁，有 in share mode 的共享锁和 for update 的排他锁；

Tips

1. 如果要删除某个表的10万行数据，使用单条语句删除，还是10条语句，每条1万行，建议多条语句删除，避免长事务和锁时间长；

2. 查看 Sql 执行效率，可以 explain 命令，或者将慢查询时间设置为0，set long_query_time = 0；

转载地址：http://nsfin.baihongyu.com/