本节讨论如何获取现有分区的信息，这可以通过多种方式进行。获取此类信息的方法包括以下内容：This section discusses obtaining information about existing partitions, which can be done in a number of ways. Methods of obtaining such information include the following:

当对分区表进行插入、删除或更新时，二进制日志会记录关于行事件发生的分区以及（如果有的话）子分区的信息。即使涉及的是同一个表，如果修改发生在不同的分区或子分区，也会创建一个新的行事件。因此，如果一个事务涉及三个分区或子分区，将生成三个行事件。对于更新事件，分区信息会记录在“前”图像和“后”图像中。如果在使用mysqlbinlog查看二进制日志时指定了-v或--verbose选项，将显示分区信息。只有在使用基于行的日志记录时才会记录分区信息（binlog_format=ROW）。When insertions, deletions, or updates are made to partitioned tables, the binary log records information about the partition and (if any) the subpartition in which the row event took place. A new row event is created for a modification that takes place in a different partition or subpartition, even if the table involved is the same. So if a transaction involves three partitions or subpartitions, three row events are generated. For an update event, the partition information is recorded for both the “before” image and the “after” image. The partition information is displayed if you specify the -v or --verbose option when viewing the binary log using mysqlbinlog. Partition information is only recorded when row-based logging is in use (binlog_format=ROW).

如本章其他部分所讨论的，SHOW CREATE TABLE 在其输出中包括用于创建分区表的 PARTITION BY 子句。例如：As discussed elsewhere in this chapter, SHOW CREATE TABLE includes in its output the PARTITION BY clause used to create a partitioned table. For example:

mysql> SHOW CREATE TABLE trb3\G
*************************** 1. row ***************************
       Table: trb3
Create Table: CREATE TABLE `trb3` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `purchased` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (YEAR(purchased))
(PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB) */
0 row in set (0.00 sec)

分区表的 SHOW TABLE STATUS 输出与非分区表相同，只是 Create_options 列包含字符串 partitioned。Engine 列包含该表所有分区使用的存储引擎名称。（有关该语句的更多信息，请参见 第 15.7.7.38 节，“SHOW TABLE STATUS 语句”。）The output from SHOW TABLE STATUS for partitioned tables is the same as that for nonpartitioned tables, except that the Create_options column contains the string partitioned. The Engine column contains the name of the storage engine used by all partitions of the table. (See Section 15.7.7.38, “SHOW TABLE STATUS Statement”, for more information about this statement.)

您还可以从 INFORMATION_SCHEMA 获取有关分区的信息，该模式包含一个 PARTITIONS 表。参见 第 28.3.21 节，“INFORMATION_SCHEMA PARTITIONS 表”。You can also obtain information about partitions from INFORMATION_SCHEMA, which contains a PARTITIONS table. See Section 28.3.21, “The INFORMATION_SCHEMA PARTITIONS Table”.

可以使用 EXPLAIN 来确定分区表的哪些分区涉及给定的 SELECT 查询。EXPLAIN 输出中的 partitions 列列出了查询将匹配记录的分区。It is possible to determine which partitions of a partitioned table are involved in a given SELECT query using EXPLAIN. The partitions column in the EXPLAIN output lists the partitions from which records would be matched by the query.

假设创建并填充了一个表 trb1 如下：Suppose that a table trb1 is created and populated as follows:

CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
    PARTITION BY RANGE(id)
    (
        PARTITION p0 VALUES LESS THAN (3),
        PARTITION p1 VALUES LESS THAN (7),
        PARTITION p2 VALUES LESS THAN (9),
        PARTITION p3 VALUES LESS THAN (11)
    );

INSERT INTO trb1 VALUES
    (1, 'desk organiser', '2003-10-15'),
    (2, 'CD player', '1993-11-05'),
    (3, 'TV set', '1996-03-10'),
    (4, 'bookcase', '1982-01-10'),
    (5, 'exercise bike', '2004-05-09'),
    (6, 'sofa', '1987-06-05'),
    (7, 'popcorn maker', '2001-11-22'),
    (8, 'aquarium', '1992-08-04'),
    (9, 'study desk', '1984-09-16'),
    (10, 'lava lamp', '1998-12-25');

您可以查看查询中使用了哪些分区，例如 SELECT * FROM trb1;，如下所示：You can see which partitions are used in a query such as SELECT * FROM trb1;, as shown here:

mysql> EXPLAIN SELECT * FROM trb1\G
*************************** 1. row ***************************
           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

在这种情况下，所有四个分区都会被搜索。然而，当在查询中添加使用分区键的限制条件时，可以看到只有包含匹配值的那些分区会被扫描，如下所示：In this case, all four partitions are searched. However, when a limiting condition making use of the partitioning key is added to the query, you can see that only those partitions containing matching values are scanned, as shown here:

mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where

EXPLAIN 还提供有关使用的键和可能的键的信息：EXPLAIN also provides information about keys used and possible keys:

mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM trb1 WHERE id < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 7
        Extra: Using where

如果使用 EXPLAIN 来检查非分区表的查询，不会产生错误，但 partitions 列的值始终为 NULL。If EXPLAIN is used to examine a query against a nonpartitioned table, no error is produced, but the value of the partitions column is always NULL.

EXPLAIN 输出的 rows 列显示表中行的总数。The rows column of EXPLAIN output displays the total number of rows in the table.

另请参见 第 15.8.2 节，“EXPLAIN 语句”。See also Section 15.8.2, “EXPLAIN Statement”.