mysql实现数据库增加规格限制，控制一张表所占用的磁盘大小【optimize table】 C api demo

【问题背景】

要求对使用的数据库表进行规格限制，对某一张表限制在10MB内，超过限制就对这张表中的陈旧数据进行删除回收空间。判断陈旧数据的依据是其中一个datatime类型的属性相对而言的，对属性排序后删除一部分，并没有一个固定的时间长短。

数据特点：datatime属性更改十分的频繁

 【解决方案过程】

 首先如何查询一张表占用磁盘大小为多少呢？

select 
table_schema as 'DB',
table_name as 'TABLE',
table_rows as 'RECORED',
truncate(data_length/1024/1024,2) as 'DATAMEN(MB)',
truncate(index_length/1024/1024,2) as 'INFOMEN(MB)',
from information_schema.tables
where table_schema = '数据库名称'
order by data_length desc,index_length desc;

这样就可以全局的看到该数据库中每张表存有多少条数据，数据文件占用多大空间，索引文件占多大空间，整张表占用的大小就是数据文件和索引文件之和

---

此时做一个试验：

delete from '表名'，查看大小，发现占用大小为0

那么添加筛选条件呢？

delete from '表名' where id = '...'，添加筛选条件进行删除，查看大小，发现占用大小不变

或许是删除的量小了？

那么试试delete from '表名' limit 10000,留下一部分数据，删除1万条，查大小，发现还是不变。

原因：

这是因为delete语句直观上看确实会删除掉数据，但是并不会整理数据文件，只会删除数据文件中的标识符而已，通俗的说，就是你使用的过得地方，确实会删除数据，但是空间还给你留着，等下次有数据插入的时候直接沿用这块地。

大多数的时候我们删除数据都是会带上筛选条件的，那么这个时候就应该使用optimize table 该条语句会对碎片进行整理回收。

那么optimize table 有什么隐患/不足呢？

在使用这条语句的时候，会对表上锁，所以在功能使用上，可能会造成读写饥饿。在对性能上要求很严格的状态下，可能并不是好的解决方案。（不过当时开发时要求没有那么严格，于是乎就这么定了下来）

另外这个语句只会对MyISAM/InnoDB这两个存储引擎有效。

查看存储引擎语句：

show table status like '表名';

限制规格demo

int check_sql_memory()
{
    MYSQL_ROW row;
    MYSQL_RES *res;
    char tmpsql[512]={0};
    char *query_sql_setence = "select (truncate(data_length/1024/1024,2)+truncate(index_length/1024/1024,2)) as 'MEN(MB)' from information_schema.tables where table_name ='%s';";
    int ret;
    ret = mysql_ping(&sql_fd);
    snprintf(tmpsql,512,query_sql_sentence,tb_name);
    if(mysql_query(&sql_fd),tmpsql)
        return -1;

    res = mysql_store_result(&sql_fd);
    if(NULL == res)    
        return -2;

    double mem = 0;
    while(row = mysql_fetch_row(res))
    {
        mem = atof(row[0]);
    }
    if(res != NULL)
    {
        mysql_free_result(res);
    }
    if(mem > 10)
    {
        char * delete_sql_sentence = "delete from %s order by datatime limit %d;";
        int line = 10000;
        snprintf(tmpsql,512,delete_sql_sentence,tb_name,line);
        if(mysql_query(&sql_fd,tmpsql))
            return -3;
        char * optimize_sql_sentence = "optimize table %s;";
        snprintf(tmpsql,512,optimize_sql_sentence,tb_name);
        if(mysql_query(&sql_tmp,tmpsql))
            return -4;
        res = mysql_store_result(&sql_fd);
        if(res != NULL)
        {
            mysql_free_result(res);
        }
    }    
    return 0;
}

在使用的过程当中，遇到了一个坑点，使用optimize 语句后，发现后续的sql语句都不能正常使用，使用mysql_errno返回了2014:

原因是因为使用optimize 后会返回结果集，结果集需要在下次使用mysql_query前被释放，否则不能正常使用

可供参考的性能测试结果：

使用C api 的状态下：在10万条数据入库的状态下，当时占用大小8MB左右，整个查询大小+删除1万条数据+优化整理碎片的时间花了689ms左右，释放了0.7MB的大小

---

【后话】

对于其他的项目来说这个方式还有可以优化的地方，比如针对datatime建立索引，减少删除操作时候的时间，或者干脆进行分表，按照日期或者内存进行分表/分区，这样能够直接就可以删除一整张分表来进行磁盘回收，因为一个类型的都会存在一张表中，这种方式会更快，有兴趣的可以继续研究。

只是对于本次项目来说这些当时都不适用，对于建立索引这个方式，由于datatime字段本身就会插入和更新的更加的频繁，所以不适合拿来当索引键。

而对于分表装数据的方式，一当面，数据更新频繁，导致需要维护陈旧数据的分表中的过期状态的时间成本比较大，另一方面不能保证总表中不存在数据重复的问题。

所以两者都没有使用，倘若datatime更新的不频繁，或者干脆都不会进行更改，那么对于上述两种方式都是好的。

有兴趣的可以看看MySQL 快速删除大量数据（千万级别）的几种实践方案——附源码 - CoderBaby - 博客园 (cnblogs.com)