mysql 清除自增要花几个小时_解决项目中MySQL数据库执行删除语句时间太长的问题...

问题描述：

由于业务需求，需要删除一些重复数据。即删除openid对应的2条数据，最终只保留一行。

MySQL数据库，sql语句比较简单：删除数据前，备份数据是必须的！

先来错误的演示：

1.先把错误数据查询出来

SELECT

id,

openid,

COUNT(*) as recCount

FROM tb_wx_user_info_bak

GROUP BY openid

HAVING COUNT( * ) > 1

2.根据要删除的数据的ID，进行删除

DELETE FROM tb_wx_user_info_bak WHERE id IN

(

SELECT

id,

openid,

COUNT(*) AS recCount

FROM tb_wx_user_info_bak

GROUP BY openid

HAVING COUNT( * ) > 1

)

一看就会，一做就废。

执行了上面的sql，果然，把sqlyog客户端的操作界面卡住了。

于是再开了个新的窗口。也不知道这个sql要执行多久，只能等着了。

3小时过后，这个sql窗口还是老样子，也不知道删除成功了多少数据。

在新的窗口中查询数据表，还是一样的数据量，应该是在卡住的窗口删除了一些数据但是没有提交，因此在新的窗口，数据量没有变化。

*****

在等待的时间中，想了想卡住的原因：

1.目标数据库表，同时执行新增数据与删除数据。

2.表的内存不足

3.删除语句没有使用索引。

4.删除数据的sql太差，一次删除的数据量太大(76330)

和同事请教后，得到建议：

1.优化sql

--建立要删除的数据ID构成的临时表id_temp

id：自增主键，id_data：要删除的数据ID

--把要删除的数据的ID插入表id_temp

Insert into id_temp(id_data) ( SELECT id FROM tb_wx_user_info_bak GROUP BY openid HAVING COUNT( * ) > 1 )

--把id_temp表中的id_data字段作为参数传给删除的sql

DELETE FROM tb_wx_user_info_bak WHERE id IN ( select id_data from id_temp where id > 0 and id < 5000 )

每次只删除5000条数据。避免批量处理导致MySQL无法处理数据的问题。

2.要优化具体的sql，需要结合实际场景。

譬如，要模拟大量用户操作一张表的数据，从而实现同时删除数据和插入数据到表中的情景。

达到了上述条件，再对sql的每一部分进行优化，了解sql的性能瓶颈在哪一块。

以上面的sql为例：

DELETE FROM tb_wx_user_info_bak WHERE id IN ( select id_data from id_temp where id > 0 and id < 5000 )

该sql的性能瓶颈可能处于子查询中，要解决查询的性能瓶颈，可以从索引入手，建立索引提高查询效率。

修改好sql以后，发布服务器操作记录

实际操作记录

[unicorn@unicomm auaa-audit]#

[unicorn@unicomm auaa-audit]# ./app.sh startDev

-bash: ./app.sh: Permission denied ###错误1：缺少操作权限

[unicorn@unicomm auaa-audit]#

[unicorn@unicomm auaa-audit]#

[unicorn@unicomm auaa-audit]# sudo ./app.sh startDev

sudo: ./app.sh: command not found

[unicorn@unicomm auaa-audit]#

[unicorn@unicomm auaa-audit]# chmod +x app.sh

[unicorn@unicomm auaa-audit]#

[unicorn@unicomm auaa-audit]#

---

[unicorn@unicomm auaa-audit]# sudo ./app.sh startDev

sudo: unable to execute ./app.sh: No such file or directory ###错误2：编码格式错误

[unicorn@unicomm auaa-audit]# pwd

/u02/auaa-audit/tmp/auaa-audit

[unicorn@unicomm auaa-audit]# file -i app.sh

app.sh: text/x-shellscript; charset=utf-8

[unicorn@unicomm auaa-audit]#

[unicorn@unicomm auaa-audit]#

[unicorn@unicomm auaa-audit]# iconv app.sh -f utf-8 -t UNICODE -o app.sh --verbose

app.sh:

[unicorn@unicomm auaa-audit]#

[unicorn@unicomm auaa-audit]#

[unicorn@unicomm auaa-audit]# file -i app.sh

app.sh: text/plain; charset=utf-16le #修改编码格式成功

---

[unicorn@unicomm auaa-audit]# ./app.sh startDev

-bash: ./app.sh: cannot execute binary file ###错误3：缺少可执行jar包

---

检查服务的启动参数等等

本次范爷检查出来的问题是：启动参数中定义的jdk的路径，找不到正确版本的jdk。

范爷把app.sh文件重新配置后，在185服务器商安装了jdk1.8后，配置了该服务器的JAVA_HOME环境变量等等。

[unicorn@unicomm jdk1.8.0_201]# pwd

/usr/local/jdk1.8.0_201

[unicorn@unicomm jdk1.8.0_201]# vi /etc/profile

---

修改profile文件内容如下：

#set java environment

export JAVA_HOME=/usr/local/jdk1.8.0_201

export PATH=$JAVA_HOME/bin:$PATH

谨以此自勉：还有什么是比什么也不做更困难的呢！