mysql迁移导致数据库效率低_MySQL异常故障总结

废话不多说了，直接进入问题主题：

问题一：MySQL单表千万级数据量"add"字段异常分析

问题描述：

项目一个新功能，需要对有1200w+的表"tb_bw_mobile"新增2个字段"batch_num"、"data_status"；执行SQL月5-6分钟后，查询数据，发现未增加字段；约10-13分钟后limit 1、10、100条数据，均为发现有新增的2个字段；此时查询表结构，发现表结构未变化；约24分钟后，MySQL命令行端抛出异常"ERROR 1034 (HY000): Incorrect key file for table ‘tb_ec_bw_mobile‘; try to repair it"；再此核实表结构、数据，发现均未有任何改变；

当时就蒙蔽了，怎么会提示"try to repair it"，表结构怎么会有问题？通知测试人员，将生产的表结构和10w条数据导出来在测试环境进行测试，结果并没有什么问题；此时查看MySQL日志，发现集群出现了异常

执行SQL时抛出异常，尝试4次修改当前执行事务未306；修改失败后，当前事务在从节点的状态从SYNCD->OPEN->CLOSED，再看后面的信息，MySQL从节点服务器异常。

此时重启mysql服务，查看mysql进程；基于rsync服务以及4444端口来同步恢复数据；同时查看进程的线程和io，确定是在做数据同步；约30分钟后，查询mysql日志及系统日志，mysql数据同步成功且成功加入集群。集群正常提供服务

**此时回过头来查看执行sql抛出的异常＂ERROR 1034 (HY000): Incorrect key file for table ‘tb_ec_bw_mobile‘; try to repair it＂；在官网中看到解决办法，需创建一张临时表做数据迁移，而不是直接对千万级数据直接add字段；

问题二：MySQL临时表问题

问题描述：

MySQL应用所在云主机系统磁盘容量不断减小，在tmp目录下不断生成以MAD、MAI为后缀的文件，导致前端web服务mgr在1min对mysql发起请求的连接高于1000+；此时查看mysql的进程，发现有”Copy data to tmp”的相关事务在执行，原因是黑白名单功能把”tb_ec_contact”和”tb_ec_bw_mobile”两张千万级数据表关联起来

问题分析：

Mysql临时表分为”内存临时表”和”磁盘临时表”；一般情况下，MySQL会先创建内存临时表，但是内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表；使用临时表的场景：对Innodb表进行alter和create操作或者是sql语句效率低下事务执行过慢，未做优化，以下为网关说明；

需要注意的是：此配置默认路径是/tmp目录，生成以MAD、MAI为后缀的文件，在短时间内很容易将系统磁盘占慢，导致依赖mysql的程序运行异常；文件删除后系统磁盘空间也不会释放，因为mysql的线程正在运行，除非停止mysql服务；(可以使用lsof查看)；但是好处是如果线程执行完了，会将生成的文件自动删除，释放系统磁盘空间；

此参数配置不能在线进行全局修改，只能先停止mysql服务，在配置文件中写明相关参数重启服务才行；但是临时表的空间和行数可在线修改；

show global variables like “%tmp%”；

tmpdir ##指明mysql磁盘临时表存储路径

slave_load_tmpdir=/data/mysql/slave_tmpdir ##指明从节磁盘临时表路径

max_tmp_tables=128 ##指明支持多少个内存临时表

tmp_table_size=33554432 ##指明临内存时表的大小

max_heap_table_size=33554432 ##指明内存临时表表支持多少行