Suppose you need to perform a range query on a valuable, time-series-based, super-large order data table that contains years of historical data. We need to run a statistical query on nearly a month’s worth of data, involving 100 million rows. Perhaps the new version of MySQL can support, but in the future the amount of data that will be more exaggerated, assuming that a hardware application data of more than 10 TB, which is much bigger than the server memory, the server hardware or current, hard disk drives, Flash (SSD hard drive is still difficult to achieve storage so large capacity level), How do you query at this point, even without considering performance?

Why partition?

When faced with large tables, at least one thing is certain, the table is too large to do a full table scan on every query. At this time, indexes can’t be used, or they don’t make sense, not to mention the maintenance cost and space footprint of indexes are very high. Relying on indexes can result in a large amount of fragmentation and low aggregation of data, which can lead to thousands of random I/O calls at query time and lead to downtime. In this case, only 1-2 indexes will be used, not more. In this case, there are two possible options: the query must either look sequentially from a specified part of the table or the desired part of the data and its index match the server’s memory.

Again, with too much storage, binary tree indexes don’t work unless the index covers the entire query. The server needs to look up an entire row of data in the table and performs random I/O operations over a large space span, resulting in unacceptable query response times. Maintaining indexes (disk space, I/O operations) is also expensive.

This is a problem that partitioning can solve. The key here is that partitioning is a rudimentary form of index that is low-load and allows us to retrieve results from neighboring data. In this case, we can scan adjacent data successively or load adjacent data into memory for retrieval. The partition is low load because it has no Pointers to corresponding rows and does not need to be updated. Partitioning does not precisely divide data into rows, nor does it involve so-called data structures. In effect, partitioning is a sort of data.

Partitioning strategy

For large data tables, there are two strategies for partitioning:

  • No indexes: Instead of adding indexes when creating a data table, partitions are used to locate the desired data rows. As long as you use the WHERE condition to shred the query into a small partition range, this is sufficient. At this point, you need to mathematically calculate whether the response time of the query is acceptable. Of course, the assumption here is that no data will be put into memory, but that all data will be read from disk. As a result, the data is quickly overwritten by other queries and using caching makes little sense. This is generally the case when the cardinality of a large data table is conventional. Note that you need to limit the number of partitions to a few hundred.
  • Use indexes and isolate hot data: If most of the data is unused except for hot data, separate partitions of hot data can be loaded into memory with indexes. At this point, indexes can be used to optimize performance, just as they would with a normal data table.

Partition hidden trouble

Both partitioning strategies are based on two key assumptions: that partitioning can be filtered to narrow the search scope during queries, and that partitioning itself is inexpensive. However, these two assumptions are not always valid, and here are the problems you may encounter:

  • NULL values can cause partition filtering to fail: When the partition function can be NULL, the results of partitioning work are strange. It will assume that the first partition is special. If the order_date column is NULL or invalid, the date will be stored in the first PARTITION. Let’s write a query WHERE order_date BETWEEN ‘2021-01-01’ AND ‘2021-01-31’. MySQL actually checks for two partitions, one where the YEAR function may return NULL if it receives invalid input, and the other where the eligible value may be NULL (stored in the first partition). This is also possible for other functions, such as TO_DAYS. This can cause problems if the first partition is large, especially if the first no-index strategy is used. The effect of looking up data from two partitions instead of one is completely unexpected. To avoid this, create a “fake” first PARTITION, such as PARTITION P_NULls VALUES LESS THAN (0). If no invalid data is stored in the table, the first partition will be empty, even though it will be scanned, but because it is empty or the amount of data is small, the performance impact is insignificant. This is a situation that after MySQL 5.5, if you partition directly with columns you don’t need to deal with, but if you use functions you do.
  • Index and partition mismatch: If an index is defined that does not match the partition condition, the query may not be able to filter the partition. Suppose an index that defines field A is partitioned using field B. Since each partition has its own index, a query against this index traverses the index tree for all partitions. If the non-leaf nodes of the index tree are resident in memory, the query is faster, but there is no way to avoid a scan of all indexes. To avoid this, you should avoid using index columns that are not partitioned unless the WHERE condition itself specifies partitions. This may seem easy to avoid, but it’s actually surprising. For example, suppose a partitioned table is used after a second table queries a union query that uses an index other than the index of the partition. Each row of the federated query accesses and scans the partition of the second table.
  • Deciding which partition to use can be costly: partitions are implemented in different ways, so actual performance is not always consistent. This is especially true when it comes to questions like “Which partition does this row belong to?” or “How can I find rows that match the query criteria?” Answering such questions in the case of many partitions is difficult. Linear search is not always efficient, and the result is that the cost increases as the number of partitions increases. The worst form is line-by-line insertion. Each time a row is inserted into a partitioned table, the server needs to scan which partition to use to store the new row. This problem can be mitigated by limiting the number of partitions; in fact, more than 100 partitions are generally not recommended. Of course, there are no such restrictions for other partition types, such as key-value and hash partitions.
  • Opening and locking partitions can also be costly: a downside of partitioned tables is that each partition needs to be opened and locked for query purposes. This is done before the partition is filtered. This cost is independent of the partition type and affects all operation statements. This effect is especially noticeable for queries with short data volumes, such as one row of data. This defect can be reduced by batch operations instead of single operations, such as inserting multiple rows at once, or loading DATA INFILE, deleting DATA by range at once, and so on. Of course, limiting the number of partitions also works.
  • Maintenance can be costly: some partition maintenance is quick, such as creating or deleting partitions. Other operations, such as resizing partitions, are similar to what ALTER does to a table: you need to iterate over rows. For example, adjusting a partition creates a temporary partition, moves data into the new partition, and deletes the old partition.

As mentioned above, partitioning is not a perfect solution, and the current version of MySQL has some other constraints:

  • All partitions must use the same storage engine.
  • Partition functions are limited in the number of functions or expressions they can choose.
  • Some storage engines do not support partitioning.
  • LOAD INDEX INTO CACHE cannot be used for MYISAM tables.
  • For MYISAM tables, more open file descriptors are required for partitioned tables, which means that the cache entry for a single table may correspond to multiple file descriptors. So the base configuration limits the cache of the data table to avoid exceeding the amount of processing required by the server operating system, and partitioning the table may actually cause this limit to be exceeded.

Of course, partition support is getting better and better with newer iterations of MySQL versions, and many partition issues have been fixed.