mysql表碎片怎么产生 mysql查看碎片程度

php怎么显示mysql数据库的碎片

php是无法显示mysql数据库的碎片的。

创新互联长期为成百上千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为兴业企业提供专业的成都网站建设、成都网站设计，兴业网站改版等技术服务。拥有十多年丰富建站经验和众多成功案例,为您定制开发。

原因如下：

1、当索引所在页面的基于主关键字的逻辑顺序，和数据文件中的物理顺序不匹配时，碎片就产生了。所有的叶级页包含了指向前一个和后一个页的指针。这样就形成一个双链表。理想情况下，数据文件中页的物理顺序会和逻辑顺序匹配。整个磁盘的工作性能在物理顺序匹配逻辑顺序时将显著提升。对某些特定的查询而言，这将带来极佳的性能。当物理排序和逻辑排序不匹配时，磁盘的工作性能会变得低效，这是因为磁头必须向前和向后移动来查找索引，而不是只象某个单一方向来搜索。

2、碎片会影响I/O性能，不过对于位于SQL Server数据缓冲内的数据页而言，碎片并不会带来任何影响。

3、在索引碎片整理前，请确保系统资源的一些问题，比如物理磁盘碎片，不合理的基础结构等因素会给性能带来负面影响，参看KB935089：使用 Defrag 管理器可以 such as Exchange Server 或 SQL Server 数据库服务器的卷进行碎片整理。

DBCC SHOWCONTIG是显示指定的表的数据和索引的碎片信息。当运行该命令时，要特别注意逻辑碎片(Logical Fragmentation)和页密度(Page Density)两个指标。

Page Scanned-扫描页数：如果你知道行的近似尺寸和表或索引里的行数，那么你可以估计出索引里的页数。看看扫描页数，如果明显比你估计的页数要高，说明存在内部碎片。

4、Extents Scanned-扫描扩展盘区数：用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高，说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。

Extent Switches-扩展盘区开关数：该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。

Avg. Pages per Extent-每个扩展盘区上的平均页数：该数是扫描页数除以扫描扩展盘区数，一般是8。小于8说明有外部碎片。

Scan Density [Best Count:Actual Count]-扫描密度［最佳值:实际值］：DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100％。低了则说明有外部碎片。

Logical Scan Fragmentation-逻辑扫描碎片：无序页的百分比。该百分比应该在0％到10％之间，高了则说明有外部碎片。

Extent Scan Fragmentation-扩展盘区扫描碎片：无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0％，高了则说明有外部碎片。

Avg. Bytes Free per Page-每页上的平均可用字节数：所扫描的页上的平均可用字节数。越高说明有内部碎片，不过在你用这个数字决定是否有内部碎片之前，应该考虑fill factor（填充因子）。

Avg. Page Density (full)-平均页密度（完整）：每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片。

MySQL—碎片整理

删除数据必然会在数据文件中造成不连续的空白空间,而当插入数据时,这些空白空间则会被利用起来.于是造成了数据的存储位置不连续,以及物理存储顺序与理论上的排序顺序不同,这种是数据碎片.实际上数据碎片分为两种,一种是单行数据碎片,另一种是多行数据碎片.前者的意思就是一行数据,被分成N个片段,存储在N个位置.后者的就是多行数据并未按照逻辑上的顺序排列.当有大量的删除和插入操作时,必然会产生很多未使用的空白空间,这些空间就是多出来的额外空间.索引也是文件数据,所以也会产生索引碎片,理由同上,大概就是顺序紊乱的问题.Engine 不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引。这样不但会浪费空间，并且查询速度也更慢。

查看碎片信息：

Index_length 代表索引的总量

Data_free 代表碎片数量

从information_schema中获取信息：

碎片整理：

过程时间长短取决于表大小和碎片多少，

返回结果optimize status OK则整理完成；

MySQL 5.6 整理表的碎片

可以看到，当前表的碎片率超高了，50.6%。

有三种办法整理碎片

这三种操作都是先创建一个临时表复制完成后再删除旧表，所以在执行操作的过程中磁盘会先增大。

会锁表

会锁表

pt-online-schema-change - ALTER tables 无需锁表。

整理结果很明显，整理后碎片率0.3%。

这里有几个参数需要介绍一下：

--dry-run

这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。

--execute

表明你已经阅读了文档，并且确认要 alter the table。你必须配置这个参数来 alter the table。如果你不配置，那么工具将只进行一些安全检查然后就退出了。这帮助确保你已经阅读了文档，并且了解如何使用该工具。如果你没有阅读这些文档，那么不会设置该参数。

--critical-load

每次chunk操作前后，会根据show global status统计指定的状态量的变化，默认是统计Thread_running。目的是为了安全，防止原始表上的触发器引起负载过高。这也是为了防止在线DDL对线上的影响。超过设置的阀值，就会终止操作，在线DDL就会中断。提示的异常如上报错信息。

--max-lag

type: time; default: 1s

lag 滞后偏移

暂停数据拷贝，直到所有replicas的lag值低于该值。在每个 data-copy query (each chunk)后，工具会通过Seconds_Behind_Master查询所有replica的 replication lag 。如果任何replica lag大于该值，那么工具会sleep --check-interval 秒，然后再次检查所有replica。如果你指定 --check-slave-lag ，那么工具会检查那台server，而不是所有server。如果你想控制哪个提供工具的监控，配置DSN值 --recursion-method 。

工具会等待直到replicas停止lagging。如果任一replica停止，工具会一直处于等待状态直到该replica启动。 在所有replicas运行并且lagging不大的情况下，数据拷贝继续。

工具在等待的时候，会打印进程报告。如果replica停止了，会立即打印进程报告，然后在每个进程报告期间重复。

--check-interval

type: time; default: 1

Sleep time between checks for --max-lag .

--max-load

选项定义一个阀值，在每次chunk操作后，查看show global status状态值是否高于指定的阀值。该参数接受一个mysql status状态变量以及一个阀值，如果没有给定阀值，则定义一个阀值为为高于当前值的20%。注意这个参数不会像--critical-load终止操作，而只是暂停操作。当status值低于阀值时，则继续往下操作。是暂停还是终止操作这是--max-load和--critical-load的差别。

--charset

简写: -A; type: string

设置默认字符集。如果值为 utf8，设置 Perl’s binmode on STDOUT to utf8，传送 mysql_enable_utf8 参数到 DBD::mysql，然后在连接到MySQL后运行 SET NAMES UTF8 。其他的值也是在STDOUT设置 binmode，然后在连到MySQL后运行 SET NAMES 。

--check-replication-filters

检查复制中是否设置了过滤条件，如果设置了，程序将退出

--nocheck-replication-filters

不检查复制中是否设置了过滤条件

--set-vars

设置mysql的变量值

--check-slave-lag

检查主从延迟

--no-version-check

不检查版本，在阿里云服务器中一般加入此参数，否则会报错