MYSQL 大量插入数据失败后,磁盘空间却被占用

近有人问,在MYSQL中大量插入数据失败后,磁盘空间被占用了不少,然后磁盘空间到底怎么样, 我们先模拟一下这个环节.

先找一个大表,或者现生成一个

#!/usr/bin/python3
# -*- coding: UTF-8 -*-
import mysql.connector
from mysql.connector import errorcode
import sys
import threading
import time
def main():
try:
        mysqlconn = mysql.connector.connect(host="192.168.198.66", user="admin", password="1234.Com",database='test')
        mycursor = mysqlconn.cursor()
        mycursor.execute("create database IF NOT EXISTS test_p")
        mycursor.execute("drop table if exists test_p ")
        mycursor.execute(
"create table test_p(id INT auto_increment,name VARCHAR(256), marks smallint,create_date datetime,primary key(id))")
        i = 1
        while i < 1000000:
            value = (i, 1)
            sql_stm = """insert into test_p (name,marks,create_date) values (%s,%s,now())"""
            mycursor.execute(sql_stm, value)
            mysqlconn.commit()
print(i)
            i += 1
        mycursor.close
        mysqlconn.close
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
else:
        mysqlconn.close()

if __name__ == "__main__":
    main()

下面是MYSQL 的页面定义, 以及图形化后的页面形式.

通过上面的信息我们大致知道 这个48MB的磁盘空间里面的数据,共占用了 3072 PAGES ,B-tree node 使用了 2461 , 估计熟悉MYSQL的小伙伴们,头脑里面已经有了那个 树形的图.

 SELECT  table_schema as 'DATABASE',  table_name as 'TABLE',  CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') 'TOTAL',  CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'M') 'DATAFREE'  FROM information_schema.TABLES  where table_schema='test' and table_name='test_p';

从上面的脚本中我们获得,仅仅插入的表中,我们的 data_free 就有6MB .

下面我们来进行这个测试

我们让数据插入,人为的失败.在看磁盘空间的占用方式,的确,数据插入成功和失败占用的磁盘空间并没有差.

由于计算方式,上图给出的datafree 并不准.

我们在证明一下到底实际占用的空间是多少,在操作完  OPTIMIZE TABLE  test_1;  后

页面重新分配