前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >关于mysql 删除数据后物理空间未释

关于mysql 删除数据后物理空间未释

作者头像
明哥的运维笔记
发布2019-01-30 10:02:58
1K0
发布2019-01-30 10:02:58
举报
文章被收录于专栏:运维笔记

[OPTIMIZE TABLE 当您的库中删除了大量的数据后,您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。OPTIMIZE TABLE 是指对表进行优化。如果已经删除了表的一大部分数据,或者如果已经对含有可变长度行的表(含有 VARCHAR 、 BLOB 或 TEXT 列的表)进行了很多更改,就应该使用 [OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并,并且可以消除由于删除或者更新造成的空间浪费] 。[OPTIMIZE TABLE 命令只对 MyISAM 、 BDB 和 InnoDB 表起作用]

[一,原始数据]

  1. [mysql> select count(*) as total from ad_visit_history;  ]
  2. [+---------+  ]
  3. [| total   |  ]
  4. [+---------+  ]
  5. [| [1187096]
  6. [+---------+  ]
  7. [[1]

2,存放在硬盘中的表文件大小]

  1. [[root[@BlackGhost ]
  2. [[382020]
  3. [[127116]
  4. [[12]

3,查看一下索引信息]

  1. [mysql> show index from ad_visit_history from test1;     [//查看一下该表的索引信息]
  2. [+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  ]
  3. [| Table            | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |  ]
  4. [+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  ]
  5. [| ad_visit_history |          [0]
  6. [| ad_visit_history |          [1]
  7. [| ad_visit_history |          [1]
  8. [| ad_visit_history |          [1]
  9. [| ad_visit_history |          [1]
  10. [| ad_visit_history |          [1]
  11. [| ad_visit_history |          [1]
  12. [| ad_visit_history |          [1]
  13. [+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+  ]
  14. [[8]

[索引信息中的列的信息说明。]

[Table :表的名称。 Non_unique :如果索引不能包括重复词,则为0。如果可以,则为1。 Key_name :索引的名称。 Seq_in_index :索引中的列序列号,从1开始。 Column_name :列名称。 Collation :列以什么方式存储在索引中。在MySQLSHOW INDEX语法中,有值'A'(升序)或NULL(无分类)。 Cardinality :索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。 Sub_part :如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。 Packed :指示关键字如何被压缩。如果没有被压缩,则为NULL。 Null :如果列含有NULL,则含有YES。如果没有,则为空。Index_type :存储索引数据结构方法(BTREE, FULLTEXT, HASH, RTREE)]

[二,删除一半数据]

  1. [mysql> delete from ad_visit_history where id>[598000]
  2. [Query OK, [589096]
  3. [  ]
  4. [[root[@BlackGhost ]
  5. [[382020]
  6. [[127116]
  7. [[12]

[按常规思想来说,如果在数据库中删除了一半数据后,相对应的.MYD,.MYI文件也应当变为之前的一半。[但是删除一半数据后,.MYD.MYI尽然连1KB都没有减少 ]

[我们在来看一看,索引信息]

  1. [+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    ]
  2. [| Table            | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |    ]
  3. [+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    ]
  4. [| ad_visit_history |          [0]
  5. [| ad_visit_history |          [1]
  6. [| ad_visit_history |          [1]
  7. [| ad_visit_history |          [1]
  8. [| ad_visit_history |          [1]
  9. [| ad_visit_history |          [1]
  10. [| ad_visit_history |          [1]
  11. [| ad_visit_history |          [1]
  12. [+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    ]
  13. [[8]

[对比一下,这次索引查询和上次索引查询,里面的数据信息基本上是上次一次的一本,这点还是合乎常理。]

[三,用optimize table来优化一下]

  1. [mysql> optimize table ad_visit_history;                                             [//删除数据后的优化]
  2. [+------------------------+----------+----------+----------+  ]
  3. [| Table                  | Op       | Msg_type | Msg_text |  ]
  4. [+------------------------+----------+----------+----------+  ]
  5. [| test1.ad_visit_history | optimize | status   | OK       |  ]
  6. [+------------------------+----------+----------+----------+  ]
  7. [[1]

1,查看一下.MYD,.MYI文件的大小]

  1. [[root[@BlackGhost ]
  2. [[182080]
  3. [[66024]
  4. [[12]

2,查看一下索引信息]

  1. [+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    ]
  2. [| Table            | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |    ]
  3. [+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    ]
  4. [| ad_visit_history |          [0]
  5. [| ad_visit_history |          [1]
  6. [| ad_visit_history |          [1]
  7. [| ad_visit_history |          [1]
  8. [| ad_visit_history |          [1]
  9. [| ad_visit_history |          [1]
  10. [| ad_visit_history |          [1]
  11. [| ad_visit_history |          [1]
  12. [+------------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+    ]
  13. [[8]

[从以上数据我们可以得出,ad_code,ad_code_ind,from_page_url_ind等索引机会差不多都提高了85%,这样效率提高了好多。]

[四,小结]

[结合mysql官方网站的信息,个人是这样理解的。当你删除数据 时,mysql并不会回收,被已删除数据的占据的存储空间,以及索引位。而是空在那里,而是等待新的数据来弥补这个空缺,这样就有一个缺少,如果一时半 会,没有数据来填补这个空缺,那这样就太浪费资源了。所以对于写比较频烦的表,要定期进行optimize,一个月一次,看实际情况而定了。]

[举个例子来说吧。有100个php程序员辞职了,但是呢只是人走了,php的职位还在那里,这些职位不会撤销,要等新的php程序来填补这些空位。招一个好的程序员,比较难。我想大部分时间会空在那里。哈哈。]

[五,手册中关于OPTIMIZE的一些用法和描述]

[OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...]

[如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使用 OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新 利用未使用的空间,并整理数据文件的碎片。]

[在多数的设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新,您也不需要经常运行,每周一次或每月一次 即可,只对特定的表运行。]

[OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。]

[注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。]

[\ ]

[]


[innodb执行]

[ALTER TABLE table.name ENGINE=\'InnoDB\';]

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2015-02-05,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档