MySQL查看表碎片大小排序

1. 前言

在使用MySQL数据库的过程中，表的碎片化是一个普遍存在的问题。表的碎片化会导致数据库性能下降，影响查询和更新的速度。因此，及时检查和优化表的碎片化是数据库维护的重要工作之一。

本文将详细解释如何使用MySQL来查看表的碎片大小，并按照大小进行排序，以便帮助我们快速发现碎片较大的表。

2. 什么是表的碎片化

在MySQL中，数据存储在表中的数据页（Data Page）中。当我们执行更新和删除操作时，MySQL会将数据从数据页中删除或修改，但是这些操作并不会立即释放被删除或修改的空间。这样，表中将会存在很多不连续的空间片段，这些片段就是表的碎片。

当表的碎片较多时，会导致磁盘读取效率降低，同时也增加了磁盘的IO负载。因此，定期进行碎片整理是非常有必要的。

3. 查看表碎片大小

要查看表的碎片大小，我们可以使用MySQL提供的information_schema数据库中的系统视图INNODB_SYS_TABLES和INNODB_SYS_TABLESTATS。

INNODB_SYS_TABLES视图用于存储表空间（tablespace）和文件（file）的信息。
INNODB_SYS_TABLESTATS视图用于存储表的详细统计信息，包括碎片大小。

接下来，我们通过以下步骤来查看表的碎片大小：

步骤1：连接到MySQL数据库

首先，我们需要使用合适的MySQL客户端连接到数据库。可以使用MySQL命令行客户端、Navicat、DBeaver等应用程序。在本文中，我们将使用MySQL命令行客户端。

mysql -u username -p password

步骤2：选择使用的数据库

接下来，选择要查看表的碎片大小的数据库。使用以下命令：

USE your_database_name;

步骤3：执行查询语句

运行以下SQL查询语句，获取表的碎片大小信息并按照大小进行排序：

SELECT
    t1.name AS table_name,
    t1.file_format AS file_format,
    t2.NAME AS file_name,
    t2.PATH AS file_path,
    t2.FILE_SIZE AS file_size,
    t2.SPACE AS table_space,
    t2.PAGE_NO AS page_no,
    t2.PAGE_TYPE AS page_type,
    t2.IS_FRAGMENTED AS is_fragmented
FROM
    information_schema.INNODB_SYS_TABLES AS t1
    INNER JOIN information_schema.INNODB_SYS_TABLESTATS AS t2 ON t1.TABLE_ID = t2.TABLE_ID
WHERE
    t1.table_schema = DATABASE()
ORDER BY
    t2.IS_FRAGMENTED DESC,
    t2.FILE_SIZE DESC;

这条查询语句将返回表的名称、文件格式、文件名、文件路径、文件大小、表空间、页码、页类型和是否碎片化的信息，并按照碎片化和文件大小进行降序排列。

示例结果

下面是一个示例查询结果的部分输出：

+--------------------+-------------+---------------------------+-----------------------+------------+-------------+----------+-----------------------+----------------+
| table_name         | file_format | file_name                 | file_path             | file_size  | table_space | page_no  | page_type             | is_fragmented  |
+--------------------+-------------+---------------------------+-----------------------+------------+-------------+----------+-----------------------+----------------+
| orders             | Barracuda   | your_database_name/orders.ibd                            | ../your_database_name |   20971520 |            0 |        5 | Unknown(Relational)   |              1 |
| customers          | Barracuda   | your_database_name/customers.ibd                         | ../your_database_name |   18874368 |            0 |       18 | Unknown(Relational)   |              1 |
| products           | Barracuda   | your_database_name/products.ibd                          | ../your_database_name |   16777216 |            0 |       29 | Unknown(Relational)   |              1 |
| orders             | Barracuda   | your_database_name/#PK_Orders_17_______________________________________________________.ibd                               | ../your_database_name |   16777216 |            0 |       17 | Index(IDX_ORDERS_622) |              1 |
| customers          | Barracuda   | your_database_name/#PK_Customers_20_______________________________________________________.ibd                            | ../your_database_name |   14680064 |            0 |       20 | Index(IDX_CUSTOMERS_12) |              1 |
| products           | Barracuda   | your_database_name/#PK_Products_23_______________________________________________________.ibd                             | ../your_database_name |   12582912 |            0 |       23 | Index(IDX_PRODUCTS_18) |              1 |
+--------------------+-------------+---------------------------+-----------------------+------------+-------------+----------+-----------------------+----------------+

上述查询结果中，我们可以看到一些列的信息，如表名、文件格式、文件名、文件路径、文件大小、表空间、页码、页类型和是否碎片化。