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

最新推荐文章于 2024-07-06 19:05:22 发布

原创最新推荐文章于 2024-07-06 19:05:22 发布 · 420 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #python #java #大数据 #数据库

本文探讨了在MySQL中大量插入数据后磁盘空间的占用情况。通过Python脚本模拟插入100万条记录到数据库，观察数据_free的变化。在数据插入失败的情况下，磁盘空间占用并未显著减少。文章还提到了使用OPTIMIZE TABLE进行空间优化，并通过查询信息表获取数据库的详细占用情况。

最近有人问,在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 .

下面我们来进行这个测试