| 数据库优化之常用的show table status及ALTER TABLE 重建表 | 您所在的位置：网站首页 › mysql修改数据表存储引擎怎么改名字 › 数据库优化之常用的show table status及ALTER TABLE 重建表 |
数据库优化之常用的show table status及ALTER TABLE 重建表
|
文章目录
⭐️ MySQL优化-碎片优化一、show table status from db_name【 如何判断是否有碎片？】1、碎片查询分析2、产生碎片的原因
二、MySQL 的表空间设置和优化策略【 如何清理碎片？】1、innodb_file_per_table 参数设置为 ON（基本上是默认打开的）2、定期执行 OPTIMIZE TABLE tableName;3、定期执行 ALTER TABLE tableName ENGINE = InnoDB;
当面试官让你聊聊MySQL数据库性能优化，你还是只能回答优化sql，建索引吗？ 让我们看看还可以从哪些方面聊聊吧。其实你还可以从网速、数据量、数据库日志、内存等问题、硬件配置，当前占用资源、硬盘碎片或索引碎片等等诸多方面分析聊聊。 传送门：MySQL数据库优化看这一篇就够了（最全干货篇） ⭐️ MySQL优化-碎片优化 一、show table status from db_name【 如何判断是否有碎片？】 1、碎片查询分析show status可以查看MySQL服务器运行状态值。而show table status from db_name可以查询db_name 数据库里所有表的信息，是否有索引碎片等。MySQL 的表空间设置和优化策略有什么？
show table status 查询结果中各列字段含义： Name 表名称Engine 表的存储引擎，对于分区表，Engine 显示所有分区使用的存储引擎的名称。Version 版本；此列未使用。在 MySQL 8.0 中删除 .frm 文件后，此列现在报告硬编码值 10，这是 MySQL 5.7 中使用的最后一个 .frm 文件版本。Row_format 行格式。对于MyISAM引擎，这可能是Dynamic，Fixed或Compressed。动态行的行长度可变，例如Varchar或Blob类型字段。固定行是指行长度不变，例如Char和Integer类型字段。Rows 行数。一些存储引擎，例如 MyISAM，存储确切的计数。对于其他存储引擎，例如 InnoDB，这个值是一个近似值，可能与实际值相差 40% 到 50%。在这种情况下，请使用 SELECT COUNT(*) 获得准确的计数。 对于 INFORMATION_SCHEMA 表，Rows 值为 NULL。 对于 InnoDB 表，行数只是 SQL 优化中使用的粗略估计。 （如果 InnoDB 表是分区的，这也是如此。）Avg_row_length 平均每行包括的字节数Data_length 对于 MyISAM，Data_length 是数据文件的长度，以字节为单位。 对于 InnoDB，Data_length 是为聚集索引分配的近似空间量，以字节为单位。具体来说，它是聚集索引大小（以页面为单位）乘以 InnoDB 页面大小。 对于 MyISAM，Max_data_length 是数据文件的最大长度。给定使用的数据指针大小，这是可以存储在表中的数据总字节数。Max_data_length 对于 MyISAM，Max_data_length 是数据文件的最大长度。给定使用的数据指针大小，这是可以存储在表中的数据总字节数。 未用于 InnoDB。Index_length 对于 MyISAM，Index_length 是索引文件的长度，以字节为单位。 对于 InnoDB，Index_length 是为非聚集索引分配的近似空间量，以字节为单位。具体来说，它是非聚集索引大小的总和，以页面为单位，乘以 InnoDB 页面大小。Data_free 已分配但未使用的字节数。 InnoDB 表报告该表所属表空间的可用空间。对于位于共享表空间中的表，这是共享表空间的空闲空间。如果您使用多个表空间并且该表有自己的表空间，则可用空间仅用于该表。空闲空间是指完全空闲范围中的字节数减去安全裕度。即使可用空间显示为 0，只要不需要分配新的扩展区，就可以插入行。也可以使用 select table_schema db,table_name,engine,table_rows,data_free,data_length+index_length length from information_schema.tables where data_free>0 列出所有已经产生碎片的表 经常进行 delete 操作 经常进行 delete 操作，产生空白空间，久而久之就产生了碎片； update 更新 update 更新可变长度的字段(例如 varchar 类型)，将长的字符串更新成短的。之前存储的内容长，后来存储是短的，即使后来插入新数据，那么有一些空白区域还是没能有效利用的。 由于碎片空间是不连续的，导致这些空间不能充分被利用；由于碎片的存在，导致数据库的磁盘 I/O 操作变成离散随机读写，加重了磁盘 I/O 的负担。 二、MySQL 的表空间设置和优化策略【 如何清理碎片？】如何清理碎片，加快索引扫描，给表空间瘦身呢，常用的方法可以从以下几个方面进行处理： 1、innodb_file_per_table 参数设置为 ON（基本上是默认打开的）
MyISAM： optimize table 表名；（OPTIMIZE 可以整理数据文件,并重排索引） 对于MYISAM表，OPTIMIZE TABLE 的工作原理： 如果表已删除或分隔行，就修复该表。 如果索引页没有排序，就排序它们。 如果表的统计信息不是最新的(而且修复不能通过对索引进行排序)，就更新它们。 Innodb： optimize table 表名； 对于InnoDB的表，OPTIMIZE TABLE 的工作原理如下 对于InnoDB表， OPTIMIZE TABLE映射到ALTER TABLE … FORCE（或者这样翻译：在InnoDB表中等价 ALTER TABLE … FORCE），它重建表以更新索引统计信息并释放聚簇索引中未使用的空间。 3、定期执行 ALTER TABLE tableName ENGINE = InnoDB;Innodb： 方法一：ALTER TABLE tablename ENGINE=InnoDB；(重建表存储引擎，重新组织数据) 或：ALTER TABLE tbl_name FORCE; 方法二：进行一次数据的导入导出 使用 mysqldump 将表转储到文本文件，删除表，然后从转储文件重新加载 注意：第optimize、ALTER TABLE是高危操作，会锁表，影响业务，建议在低峰期操作。
|
这个命令中 Data_free 字段，如果该字段不为 0，则产生了数据碎片。
【本文地址】