MySQL不建议delete删除数据的原因是什么?(二)

开课吧开课吧锤锤2021-03-05 11:45

点赞
有用
分享分享

    Java编程语言是一种简单、面向对象、分布式、解释型、健壮安全、与系统无关、可移植、高性能、多线程和动态的语言。如今Java已经广泛应用于各个领域的编程开发。

Java

    Inndob存储分布

    创建空表查看空间变化

    mysql>createtableuser(idbigintnotnullprimarykeyauto_increment,

    ->namevarchar(20)notnulldefault''comment'姓名',

    ->agetinyintnotnulldefault0comment'age',

    ->genderchar(1)notnulldefault'M'comment'性别',

    ->phonevarchar(16)notnulldefault''comment'手机号',

    ->create_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',

    ->update_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'修改时间'

    ->)engine=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'用户信息表';

    QueryOK,0rowsaffected(0.26sec)

———————————————————————————————————————

    #ls-lhuser1.ibd

    -rw-r-----1mysqlmysql96KNov612:48user.ibd

    设置参数innodb_file_per_table=1时,创建表时会自动创建一个segment,同时分配一个extent,包含32个datapage的来存储数据,这样创建的空表默认大小就是96KB,extent使用完之后会申请64个连接页,这样对于一些小表,或者undosegment,可以在开始时申请较少的空间,节省磁盘容量的开销。

    #python2py_innodb_page_info.py-v/data2/mysql/test/user.ibd

    pageoffset00000000,pagetype<FileSpaceHeader>

    pageoffset00000001,pagetype<InsertBufferBitmap>

    pageoffset00000002,pagetype<FileSegmentinode>

    pageoffset00000003,pagetype<B-treeNode>,pagelevel<0000>

    pageoffset00000000,pagetype<FreshlyAllocatedPage>

    pageoffset00000000,pagetype<FreshlyAllocatedPage>

    Totalnumberofpage:6:#总共分配的页数

    FreshlyAllocatedPage:2#可用的数据页

    InsertBufferBitmap:1#插入缓冲页

    FileSpaceHeader:1#文件空间头

    B-treeNode:1#数据页

    FileSegmentinode:1#文件端inonde,如果是在ibdata1.ibd上会有多个inode。

———————————————————————————————————————

    插入数据后的空间变化

    mysql>DELIMITER$$

    mysql>CREATEPROCEDUREinsert_user_data(numINTEGER)

    ->BEGIN

    ->DECLAREv_iintunsignedDEFAULT0;

    ->setautocommit=0;

    ->WHILEv_i<numDO

    ->insertintouser(`name`,age,gender,phone)values(CONCAT('lyn',v_i),mod(v_i,120),'M',CONCAT('152',ROUND(RAND(1)*100000000)));

    ->SETv_i=v_i+1;

    ->ENDWHILE;

    ->commit;

    ->END$$

    QueryOK,0rowsaffected(0.01sec)

    mysql>DELIMITER;

    #插入10w数据

    mysql>callinsert_user_data(100000);

    QueryOK,0rowsaffected(6.69sec)

———————————————————————————————————————

    #ls-lhuser.ibd

    -rw-r-----1mysqlmysql14MNov610:58/data2/mysql/test/user.ibd

    #python2py_innodb_page_info.py-v/data2/mysql/test/user.ibd

    pageoffset00000000,pagetype<FileSpaceHeader>

    pageoffset00000001,pagetype<InsertBufferBitmap>

    pageoffset00000002,pagetype<FileSegmentinode>

    pageoffset00000003,pagetype<B-treeNode>,pagelevel<0001>#增加了一个非叶子节点,树的高度从1变为2.

    ........................................................

    pageoffset00000000,pagetype<FreshlyAllocatedPage>

    Totalnumberofpage:896:

    FreshlyAllocatedPage:493

    InsertBufferBitmap:1

    FileSpaceHeader:1

    B-treeNode:400

    FileSegmentinode:1

———————————————————————————————————————

    delete数据后的空间变化

    mysql>selectmin(id),max(id),count(*)fromuser;

    +---------+---------+----------+

    |min(id)|max(id)|count(*)|

    +---------+---------+----------+

    |1|100000|100000|

    +---------+---------+----------+

    1rowinset(0.05sec)

    #删除50000条数据,理论上空间应该从14MB变长7MB左右。

    mysql>deletefromuserlimit50000;

    QueryOK,50000rowsaffected(0.25sec)

    #数据文件大小依然是14MB,没有缩小。

    #ls-lh/data2/mysql/test/user1.ibd

    -rw-r-----1mysqlmysql14MNov613:22/data2/mysql/test/user.ibd

    #数据页没有被回收。

    #python2py_innodb_page_info.py-v/data2/mysql/test/user.ibd

    pageoffset00000000,pagetype<FileSpaceHeader>

    pageoffset00000001,pagetype<InsertBufferBitmap>

    pageoffset00000002,pagetype<FileSegmentinode>

    pageoffset00000003,pagetype<B-treeNode>,pagelevel<0001>

    ........................................................

    pageoffset00000000,pagetype<FreshlyAllocatedPage>

    Totalnumberofpage:896:

    FreshlyAllocatedPage:493

    InsertBufferBitmap:1

    FileSpaceHeader:1

    B-treeNode:400

    FileSegmentinode:1

    #在MySQL内部是标记删除,

———————————————————————————————————————

    mysql>useinformation_schema;

    Databasechanged

    mysql>SELECTA.SPACEASTBL_SPACEID,A.TABLE_ID,A.NAMEASTABLE_NAME,

FILE_FORMAT,ROW_FORMAT,SPACE_TYPE,B.INDEX_ID,B.NAMEASINDEX_NAME,PAGE_NO,

B.TYPEASINDEX_TYPEFROMINNODB_SYS_TABLESALEFTJOININNODB_SYS_INDEXESBONA.

TABLE_ID=B.TABLE_IDWHEREA.NAME='test/user1';

    +-------------+----------+------------+-------------+------------+------------+----------+------------+---------+------------+

    |TBL_SPACEID|TABLE_ID|TABLE_NAME|FILE_FORMAT|ROW_FORMAT|SPACE_TYPE|INDEX_ID

|INDEX_NAME|PAGE_NO|INDEX_TYPE|

    +-------------+----------+------------+-------------+------------+------------+----------+------------+---------+------------+

    |1283|1207|test/user|Barracuda|Dynamic|Single|2236|PRIMARY|3|3|

    +-------------+----------+------------+-------------+------------+------------+----------+------------+---------+------------+

    1rowinset(0.01sec)

    PAGE_NO=3标识B-tree的rootpage是3号页,INDEX_TYPE=3是聚集索引。INDEX_TYPE取值如下:

    0=nonuniquesecondaryindex;

    1=automaticallygeneratedclusteredindex(GEN_CLUST_INDEX);

    2=uniquenonclusteredindex;

    3=clusteredindex;

    32=full-textindex;

    #收缩空间再后进行观察

    MySQL内部不会真正删除空间,而且做标记删除,即将delflag:N修改为delflag:Y,commit之后会会被purge进入删除链表,如果下一次insert更大的记录,delete之后的空间不会被重用,如果插入的记录小于等于delete的记录空会被重用,这块内容可以通过知数堂的innblock工具进行分析。

    以上内容由开课吧老师敖丙提供,更多Java教程尽在开课吧广场Java教程频道。

有用
分享