mysql数据清理及常见问题

1、摘要

在系统前期开发过程中（还没有系统拆分），会遇到表空间会越来越大，这时候需要做一些瘦身的操作。

2、首先表空间按大小排序

SELECT * from (
select round(sum(DATA_LENGTH/1024/1024),2) content ,TABLE_NAME
from information_schema.tables where table_schema=‘uat_db’
AND table_name
in
(
SELECT tmp.table_name from (
select table_name from information_schema.TABLES
where TABLE_SCHEMA=‘uat_db’
order by table_rows desc limit 20) tmp
) GROUP BY table_name ) tmp2 ORDER BY tmp2.content desc;

3、根据表空间大小，判断哪些表需要优化。

发现最大两个都是日志表。
以其中的一张表为例描述操作方式：
有两种方式：一种是删除过期日志，还有一种方式就是架构调整，把日志拆分出来放到其他地方，比如 mysql 的ToKuDB引擎，他是高性能高压缩的第三方存储引擎，或者hbase中。

4、备份表，并清除过期日志信息

(1)先将表备份到其他的库中
(2)删除数据

从测试表中删除了几十万的数据吧。

这时奇怪的问题出现：表空间占用磁盘反而增加了。

5、问题描述

看下表结构，发现有 TEXT 类型的字段mediumtext，BLOB 和TEXT 值会引起一些性能问题，特别是在执行了大量的删除操作时。删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。

6、问题解决方法

（1）优化表结构
为了提高性能，建议定期使用OPTIMIZE TABLE 功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。
注意：OPTIMIZE TABLE 会锁表，所以使用OPTIMIZE TABLE时候需要停服务，也可以用navicat 复制一份表，然后 通过修改表名，初始化新的表空间。
优化以后，表空间明显减少很多

7、结论

数据瘦身 有时会导致表空间变大，这时不仅要优化表空间，还要优化表设计，尽量不要使用BLOB和TEXT类型的字段，很容出现碎片。