When the data of a single table is too large, the index is invalid.
Divide a single table into several areas, and through the partition function, you can quickly locate the data area. Moreover, compared with indexes, partitions do not need additional data structures to record the data of each partition, and the cost is lower. Just a simple expression is needed to point to the correct partition.
? You can store tables in a simple partition way without any index. As long as the query is located to the required approximate data location, and the required data is limited to several partitions through the where condition, the efficiency is very high. Warning: The number of partitions that need to be scanned by the query is limited to a very small number.
? If the data has obvious "hot spots", you can put the hot spot data in a separate partition, so that the data in this partition can be cached in memory.
? If the value of partition expression can be empty: the first partition will create a special partition. Take the partition by range year (order_date) as an example. All data with empty or illegal values in the order _ date column will be put into the first partition. Then all queries will scan the first partition after locating the partition. If the first partition is very large, the query cost will be ruthlessly increased by this "bottle dragging" partition.
? Creating a useless first partition can solve this problem, and the partition p _ nulls value is less than (0);
? For a query whose partition column and index column don't match, although the query can use indexes, it can't locate the partitions of the target data through partitions (that is, the data distribution is relatively scattered), and it is necessary to traverse the indexes in each partition unless the conditions in the query also include partition conditions. Therefore, the range of partition conditions is expected to be included in popular query indexes.
? For the range partition technology, it is necessary to appropriately limit the number of partitions, otherwise the cost of selecting partitions is too high for the scene of importing a large amount of data in batches. For most systems, about 100 partitions is not a problem.