mysq数据库大表清理

近期，服务器数据库cpu使用率狂涨，每天发送好几次预警邮件，但是每日的用户并没有怎么增加，一些业务表都增加了索引了呀，这里写一点简单的思路，因为近期后台的java代码也长期没有更新了，预测瓶颈应该是出在了数据库上面，先统计数据库百万级别以上的表有哪些。这里提供一个脚本。统计所有数据库的表行数。

#!/bin/bash
###get every database's table's lines
USER=root
PASSWD=123456
MYSQLLOGIN="mysql -u$USER -p$PASSWD"
DATABASE="$($MYSQLLOGIN -e "show databases;"|egrep -vi "Data|_schema|mysql")"
for dbname in $DATABASE
do
  TABLES=$($MYSQLLOGIN -e "show tables from $dbname" |egrep -vi "tables")
  #echo "!!!!"
  #echo $TABLES
  #echo "!!!!"
  for table in $TABLES
  do
  lines=$($MYSQLLOGIN -e "select count(*) from $dbname.$table" | egrep -vi "count")
  #echo $lines
  #echo $table
  echo "$dbname.$table $lines">>lines.txt
  done
done

    因为我们公司业务数据并不涉及敏感信息，处于实际情况数据不需要保留那么久，一年前的数据可以适当清理。将大表500w以上的表都有好几个，还有千万级别的表也有一些，这样表来几个查询可能就把数据库拖死了 。将这些表整理出来删除部分数据,最好根据索引删除，否则删除的会很慢。

   花了大半天将数据删除后，查看了一下数据库服务器磁盘，并没有减少，晚上服务器不出意外的又预警了，插了相关资料，有以下几种情况：

1、drop table table_name 立刻释放磁盘空间 ，不管是 Innodb和MyISAM ;

2、truncate table table_name 立刻释放磁盘空间 ，不管是 Innodb和MyISAM 。truncate table其实有点类似于drop table 然后creat,只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度;

3、delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间 （应该是做了特别处理，也比较合理）,InnoDB 不会释放磁盘空间;

4、对于delete from table_name where xxx带条件的删除, 不管是innodb还是MyISAM都不会释放磁盘空间;

5、delete操作以后使用optimize table table_name 会立刻释放磁盘空间。不管是innodb还是myisam 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。

6、delete from表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。 

    不仅如此删除数据后如果不释放磁盘空间，标的查询速度也不会提升，原来的数据并没有实际删除，只是mysql标记为删除，下次查询的时候还是会扫描这些磁盘空间，所以最好需要optimize 重建表和索引。清理磁盘后可以选择适当机会重启数据库。

optimize自动清理脚本参考：https://blog.csdn.net/qq_38125626/article/details/93874637