<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="partitioning-overview">
      </a>
      26.1 Overview of Partitioning in MySQL
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045084973440">
  </a>
  <p>
   This section provides a conceptual overview of partitioning in
      MySQL 8.4.
  </p>
  <p>
   For information on partitioning restrictions and feature
      limitations, see
   <a class="xref" href="partitioning-limitations.html" title="26.6 Restrictions and Limitations on Partitioning">
    Section 26.6, “Restrictions and Limitations on Partitioning”
   </a>
   .
  </p>
  <p>
   The SQL standard does not provide much in the way of guidance
      regarding the physical aspects of data storage. The SQL language
      itself is intended to work independently of any data structures or
      media underlying the schemas, tables, rows, or columns with which
      it works. Nonetheless, most advanced database management systems
      have evolved some means of determining the physical location to be
      used for storing specific pieces of data in terms of the file
      system, hardware or even both. In MySQL, the
   <code class="literal">
    InnoDB
   </code>
   storage engine has long supported the
      notion of a tablespace (see
   <a class="xref" href="innodb-tablespace.html" title="17.6.3 Tablespaces">
    Section 17.6.3, “Tablespaces”
   </a>
   ),
      and the MySQL Server, even prior to the introduction of
      partitioning, could be configured to employ different physical
      directories for storing different databases (see
   <a class="xref" href="symbolic-links.html" title="10.12.2 Using Symbolic Links">
    Section 10.12.2, “Using Symbolic Links”
   </a>
   , for an explanation of how this
      is done).
  </p>
  <p>
   <span class="firstterm">
    Partitioning
   </span>
   takes this
      notion a step further, by enabling you to distribute portions of
      individual tables across a file system according to rules which
      you can set largely as needed. In effect, different portions of a
      table are stored as separate tables in different locations. The
      user-selected rule by which the division of data is accomplished
      is known as a
   <span class="firstterm">
    partitioning
      function
   </span>
   , which in MySQL can be the modulus, simple
      matching against a set of ranges or value lists, an internal
      hashing function, or a linear hashing function. The function is
      selected according to the partitioning type specified by the user,
      and takes as its parameter the value of a user-supplied
      expression. This expression can be a column value, a function
      acting on one or more column values, or a set of one or more
      column values, depending on the type of partitioning that is used.
  </p>
  <p>
   In the case of
   <code class="literal">
    RANGE
   </code>
   ,
   <code class="literal">
    LIST
   </code>
   ,
      and [
   <code class="literal">
    LINEAR
   </code>
   ]
   <code class="literal">
    HASH
   </code>
   partitioning, the value of the partitioning column is passed to
      the partitioning function, which returns an integer value
      representing the number of the partition in which that particular
      record should be stored. This function must be nonconstant and
      nonrandom. It may not contain any queries, but may use an SQL
      expression that is valid in MySQL, as long as that expression
      returns either
   <code class="literal">
    NULL
   </code>
   or an integer
   <em class="replaceable">
    <code>
     intval
    </code>
   </em>
   such that
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-simple"><div class="docs-select-all right" id="sa70818585"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-simple"><span class="token operator">-</span>MAXVALUE <span class="token operator">&lt;=</span> <em class="replaceable">intval</em> <span class="token operator">&lt;=</span> MAXVALUE</code></pre>
  </div>
  <p>
   (
   <code class="literal">
    MAXVALUE
   </code>
   is used to represent the least upper
      bound for the type of integer in question.
   <code class="literal">
    -MAXVALUE
   </code>
   represents the greatest lower bound.)
  </p>
  <p>
   For [
   <code class="literal">
    LINEAR
   </code>
   ]
   <code class="literal">
    KEY
   </code>
   ,
   <code class="literal">
    RANGE COLUMNS
   </code>
   , and
   <code class="literal">
    LIST
      COLUMNS
   </code>
   partitioning, the partitioning expression
      consists of a list of one or more columns.
  </p>
  <p>
   For [
   <code class="literal">
    LINEAR
   </code>
   ]
   <code class="literal">
    KEY
   </code>
   partitioning, the partitioning function is supplied by MySQL.
  </p>
  <p>
   For more information about permitted partitioning column types and
      partitioning functions, see
   <a class="xref" href="partitioning-types.html" title="26.2 Partitioning Types">
    Section 26.2, “Partitioning Types”
   </a>
   ,
      as well as
   <a class="xref" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    Section 15.1.20, “CREATE TABLE Statement”
   </a>
   , which provides
      partitioning syntax descriptions and additional examples. For
      information about restrictions on partitioning functions, see
   <a class="xref" href="partitioning-limitations-functions.html" title="26.6.3 Partitioning Limitations Relating to Functions">
    Section 26.6.3, “Partitioning Limitations Relating to Functions”
   </a>
   .
  </p>
  <p>
   This is known as
   <span class="firstterm">
    horizontal
      partitioning
   </span>
   —that is, different rows of a table
      may be assigned to different physical partitions. MySQL
      8.4 does not support
   <span class="firstterm">
    vertical partitioning
   </span>
   , in
      which different columns of a table are assigned to different
      physical partitions. There are no plans at this time to introduce
      vertical partitioning into MySQL.
  </p>
  <p>
   For creating partitioned tables, you must use a storage engine
      that supports them. In MySQL 8.4, all partitions of
      the same partitioned table must use the same storage engine.
      However, there is nothing preventing you from using different
      storage engines for different partitioned tables on the same MySQL
      server or even in the same database.
  </p>
  <p>
   In MySQL 8.4, the only storage engines that support
      partitioning are
   <code class="literal">
    InnoDB
   </code>
   and
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   . Partitioning cannot be used with
      storage engines that do not support it; these include the
   <code class="literal">
    MyISAM
   </code>
   ,
   <code class="literal">
    MERGE
   </code>
   ,
   <code class="literal">
    CSV
   </code>
   , and
   <code class="literal">
    FEDERATED
   </code>
   storage
      engines.
  </p>
  <p>
   Partitioning by
   <code class="literal">
    KEY
   </code>
   or
   <code class="literal">
    LINEAR
      KEY
   </code>
   is possible with
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   ,
      but other types of user-defined partitioning are not supported for
      tables using this storage engine. In addition, an
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   table that employs user-defined
      partitioning must have an explicit primary key, and any columns
      referenced in the table's partitioning expression must be
      part of the primary key. However, if no columns are listed in the
   <code class="literal">
    PARTITION BY KEY
   </code>
   or
   <code class="literal">
    PARTITION BY
      LINEAR KEY
   </code>
   clause of the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
      TABLE
    </code>
   </a>
   or
   <a class="link" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
    <code class="literal">
     ALTER
      TABLE
    </code>
   </a>
   statement used to create or modify a
      user-partitioned
   <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
    <code class="literal">
     NDB
    </code>
   </a>
   table, then the
      table is not required to have an explicit primary key. For more
      information, see
   <a class="xref" href="mysql-cluster-limitations-syntax.html" title="25.2.7.1 Noncompliance with SQL Syntax in NDB Cluster">
    Section 25.2.7.1, “Noncompliance with SQL Syntax in NDB Cluster”
   </a>
   .
  </p>
  <p>
   When creating a partitioned table, the default storage engine is
      used just as when creating any other table; to override this
      behavior, it is necessary only to use the
   <code class="literal">
    [STORAGE]
      ENGINE
   </code>
   option just as you would for a table that is not
      partitioned. The target storage engine must provide native
      partitioning support, or the statement fails. You should keep in
      mind that
   <code class="literal">
    [STORAGE] ENGINE
   </code>
   (and other table
      options) need to be listed
   <span class="emphasis">
    <em>
     before
    </em>
   </span>
   any
      partitioning options are used in a
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
      TABLE
    </code>
   </a>
   statement. This example shows how to create a
      table that is partitioned by hash into 6 partitions and which uses
      the
   <code class="literal">
    InnoDB
   </code>
   storage engine (regardless of the
      value of
   <a class="link" href="server-system-variables.html#sysvar_default_storage_engine">
    <code class="literal">
     default_storage_engine
    </code>
   </a>
   ):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa24054923"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> ti <span class="token punctuation">(</span>id <span class="token datatype">INT</span><span class="token punctuation">,</span> amount <span class="token datatype">DECIMAL</span><span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> tr_date <span class="token datatype">DATE</span><span class="token punctuation">)</span>
    <span class="token keyword">ENGINE</span><span class="token operator">=</span>INNODB
    <span class="token keyword">PARTITION</span> <span class="token keyword">BY</span> <span class="token keyword">HASH</span><span class="token punctuation">(</span> <span class="token function">MONTH</span><span class="token punctuation">(</span>tr_date<span class="token punctuation">)</span> <span class="token punctuation">)</span>
    <span class="token keyword">PARTITIONS</span> <span class="token number">6</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Each
   <code class="literal">
    PARTITION
   </code>
   clause can include a
   <code class="literal">
    [STORAGE] ENGINE
   </code>
   option, but in MySQL
      8.4 this has no effect.
  </p>
  <p>
   Unless otherwise specified, the remaining examples in this
      discussion assume that
   <a class="link" href="server-system-variables.html#sysvar_default_storage_engine">
    <code class="literal">
     default_storage_engine
    </code>
   </a>
   is
   <code class="literal">
    InnoDB
   </code>
   .
  </p>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    Partitioning applies to all data and indexes of a table; you
        cannot partition only the data and not the indexes, or vice
        versa, nor can you partition only a portion of the table.
   </p>
  </div>
  <p>
   Data and indexes for each partition can be assigned to a specific
      directory using the
   <code class="literal">
    DATA DIRECTORY
   </code>
   and
   <code class="literal">
    INDEX DIRECTORY
   </code>
   options for the
   <code class="literal">
    PARTITION
   </code>
   clause of the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement used to
      create the partitioned table.
  </p>
  <p>
   Only the
   <code class="literal">
    DATA DIRECTORY
   </code>
   option is supported for
      individual partitions and subpartitions of
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   tables. The directory
      specified in a
   <code class="literal">
    DATA DIRECTORY
   </code>
   clause must be
      known to
   <code class="literal">
    InnoDB
   </code>
   . For more information, see
   <a class="xref" href="innodb-create-table-external.html#innodb-create-table-external-data-directory" title="Using the DATA DIRECTORY Clause">
    Using the DATA DIRECTORY Clause
   </a>
   .
  </p>
  <a class="indexterm" name="idm46045084907280">
  </a>
  <a class="indexterm" name="idm46045084905792">
  </a>
  <p>
   All columns used in the table's partitioning expression must
      be part of every unique key that the table may have, including any
      primary key. This means that a table such as this one, created by
      the following SQL statement, cannot be partitioned:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97884470"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tnp <span class="token punctuation">(</span>
    id <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    ref <span class="token datatype">BIGINT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">name</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> pk <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">UNIQUE</span> <span class="token keyword">KEY</span> uk <span class="token punctuation">(</span><span class="token keyword">name</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Because the keys
   <code class="literal">
    pk
   </code>
   and
   <code class="literal">
    uk
   </code>
   have no columns in common, there are no columns available for use
      in a partitioning expression. Possible workarounds in this
      situation include adding the
   <code class="literal">
    name
   </code>
   column to the
      table's primary key, adding the
   <code class="literal">
    id
   </code>
   column
      to
   <code class="literal">
    uk
   </code>
   , or simply removing the unique key
      altogether. See
   <a class="xref" href="partitioning-limitations-partitioning-keys-unique-keys.html" title="26.6.1 Partitioning Keys, Primary Keys, and Unique Keys">
    Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”
   </a>
   ,
      for more information.
  </p>
  <p>
   In addition,
   <code class="literal">
    MAX_ROWS
   </code>
   and
   <code class="literal">
    MIN_ROWS
   </code>
   can be used to determine the maximum
      and minimum numbers of rows, respectively, that can be stored in
      each partition. See
   <a class="xref" href="partitioning-management.html" title="26.3 Partition Management">
    Section 26.3, “Partition Management”
   </a>
   , for
      more information on these options.
  </p>
  <a class="indexterm" name="idm46045084894976">
  </a>
  <p>
   The
   <code class="literal">
    MAX_ROWS
   </code>
   option can also be useful for
      creating NDB Cluster tables with extra partitions, thus allowing
      for greater storage of hash indexes. See the documentation for the
   <a class="link" href="mysql-cluster-ndbd-definition.html#ndbparam-ndbd-datamemory">
    <code class="literal">
     DataMemory
    </code>
   </a>
   data node
      configuration parameter, as well as
   <a class="xref" href="mysql-cluster-nodes-groups.html" title="25.2.2 NDB Cluster Nodes, Node Groups, Fragment Replicas, and Partitions">
    Section 25.2.2, “NDB Cluster Nodes, Node Groups, Fragment Replicas, and Partitions”
   </a>
   , for more
      information.
  </p>
  <a class="indexterm" name="idm46045084890192">
  </a>
  <p>
   Some advantages of partitioning are listed here:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Partitioning makes it possible to store more data in one table
          than can be held on a single disk or file system partition.
     </p>
    </li>
    <li class="listitem">
     <p>
      Data that loses its usefulness can often be easily removed
          from a partitioned table by dropping the partition (or
          partitions) containing only that data. Conversely, the process
          of adding new data can in some cases be greatly facilitated by
          adding one or more new partitions for storing specifically
          that data.
     </p>
    </li>
    <li class="listitem">
     <p>
      Some queries can be greatly optimized in virtue of the fact
          that data satisfying a given
      <code class="literal">
       WHERE
      </code>
      clause
          can be stored only on one or more partitions, which
          automatically excludes any remaining partitions from the
          search. Because partitions can be altered after a partitioned
          table has been created, you can reorganize your data to
          enhance frequent queries that may not have been often used
          when the partitioning scheme was first set up. This ability to
          exclude non-matching partitions (and thus any rows they
          contain) is often referred to as
      <span class="firstterm">
       partition pruning
      </span>
      . For
          more information, see
      <a class="xref" href="partitioning-pruning.html" title="26.4 Partition Pruning">
       Section 26.4, “Partition Pruning”
      </a>
      .
     </p>
     <p>
      In addition, MySQL supports explicit partition selection for
          queries. For example,
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT * FROM t
          PARTITION (p0,p1) WHERE c &lt; 5
       </code>
      </a>
      selects only those
          rows in partitions
      <code class="literal">
       p0
      </code>
      and
      <code class="literal">
       p1
      </code>
      that match the
      <code class="literal">
       WHERE
      </code>
      condition. In this case, MySQL does not check any other
          partitions of table
      <code class="literal">
       t
      </code>
      ; this can greatly
          speed up queries when you already know which partition or
          partitions you wish to examine. Partition selection is also
          supported for the data modification statements
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      ,
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      ,
      <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
       <code class="literal">
        REPLACE
       </code>
      </a>
      ,
      <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
       <code class="literal">
        UPDATE
       </code>
      </a>
      , and
      <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
       <code class="literal">
        LOAD DATA
       </code>
      </a>
      ,
      <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
       <code class="literal">
        LOAD XML
       </code>
      </a>
      . See the descriptions
          of these statements for more information and examples.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
