如何在MySQL中查看表分区大小（甚至可能吗？）

I've partitioned my table horizontally and I'd like to see how the rows are currently distributed. Searching the web didn't bring any relevant results.

我已经水平分区我的表，我想看看当前如何分配行。搜索网络没有带来任何相关结果。

Could anyone tell me if this is possible?

谁能告诉我这是否可能？

2 个解决方案

#1

You could get rows of each partitions using information_schema.

您可以使用information_schema获取每个分区的行。

Here are my sample tests.

这是我的样本测试。

mysql> SELECT PARTITION_ORDINAL_POSITION, TABLE_ROWS, PARTITION_METHOD
       FROM information_schema.PARTITIONS 
       WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'tbl_name';
+----------------------------+------------+------------------+
| PARTITION_ORDINAL_POSITION | TABLE_ROWS | PARTITION_METHOD |
+----------------------------+------------+------------------+
|                          1 |          2 | HASH             |
|                          2 |          3 | HASH             |
+----------------------------+------------+------------------+

mysql> SHOW CREATE TABLE tbl_name\G
*************************** 1. row ***************************
       Table: p
Create Table: CREATE TABLE `tbl_name` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (a)
PARTITIONS 2 */
1 row in set (0.00 sec)


mysql> SELECT * FROM tbl_name;
+------+
| a    |
+------+
|    2 |
|    4 |
|    1 |
|    3 |
|    5 |
+------+
5 rows in set (0.00 sec)

UPDATED

From MySQL Manual:

来自MySQL手册：

For partitioned InnoDB tables, the row count given in the TABLE_ROWS column is only an estimated value used in SQL optimization, and may not always be exact.

对于分区的InnoDB表，TABLE_ROWS列中给出的行计数仅是SQL优化中使用的估计值，并且可能并不总是精确的。

Thanks to @Constantine.

感谢@Constantine。

#2

Just to add to Jason's answers, as per reference manual, you have following ways to get information about existing partitions on your table -

只是为了添加Jason的答案，根据参考手册，您可以通过以下方式获取有关桌面上现有分区的信息 -

1. Using Show Create Table - to view the partitioning clauses used in creating a partitioned table; Syntax :
   show create table table_name;
   Sample Output :
   CREATE TABLE 'trb3' ( 'id' int(11) default NULL, 'name' varchar(50) default NULL, 'purchased' date default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (YEAR(purchased)) ( PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM )

   使用“显示创建表” - 查看创建分区表时使用的分区子句;语法：show create table table_name;示例输出：CREATE TABLE'trb3'（'id'int（11）默认为NULL，'name'varchar（50）默认为NULL，'已购买'日期默认为NULL）ENGINE = MyISAM DEFAULT CHARSET = latin1 PARTITION BY RANGE（年份（已购买） ））（分区p0值小于（1990）发动机= MyISAM，分区p1值小于（1995）发动机= MyISAM，分区p2值小于（2000）发动机= MyISAM，分区p3值小于（2005）ENGINE = MyISAM ）

2. Using Show Table Status - to determine whether a table is partitioned;
   Syntax:
   show table status in db_name like table_name;
   Sample Output: Shows lots of information about table like Name, Engine, Version, Data Length etc. You get value 'partitioned' for 'Create_options' parameter in output.

   使用显示表状态 - 确定表是否已分区;语法：在db_name中显示表状态，如table_name;示例输出：显示有关表格的大量信息，如名称，引擎，版本，数据长度等。您将在输出中为“Create_options”参数获取值“已分区”。

3. Querying the INFORMATION_SCHEMA.PARTITIONS table.
   (Refer Jason's answers, you can optionally add SUBPARTITION_NAME, SUBPARTITION_ORDINAL_POSITION, SUBPARTITION_METHOD, PARTITION_EXPRESSION etc Select parameters to get more information. Refer MySQL Ref Manual )

   查询INFORMATION_SCHEMA.PARTITIONS表。 （参考Jason的答案，您可以选择添加SUBPARTITION_NAME，SUBPARTITION_ORDINAL_POSITION，SUBPARTITION_METHOD，PARTITION_EXPRESSION等选择参数以获取更多信息。请参阅MySQL参考手册）

4. Using the statement EXPLAIN PARTITIONS SELECT - see which partitions are used by a given SELECT
   Syntax:
   EXPLAIN PARTITIONS SELECT * FROM trb1
   Sample Output:
   id: 1 select_type: SIMPLE table: trb1 partitions: p0,p1,p2,p3 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 Extra: Using filesort

   使用语句EXPLAIN PARTITIONS SELECT - 查看给定SELECT语法使用的分区：EXPLAIN PARTITIONS SELECT * FROM trb1示例输出：id：1 select_type：SIMPLE表：trb1分区：p0，p1，p2，p3类型：ALL possible_keys： NULL键：NULL key_len：NULL ref：NULL行：10额外：使用filesort

Read More At MySQL Ref Manual

阅读更多MySQL参考手册