Detailed introduction to table partitioning in MySQL
MySQL table partitioning is the same as database and table partitioning, both to improve the throughput of the database. Partitioning is similar to table partitioning. Table partitioning is to logically divide a table with a large amount of data into multiple tables, which can be divided horizontally or vertically. Partitioning is to split a data file of a table into multiple data files. Different data is split into different files. In this way, for a table with a very large amount of data, multiple data files are used for storage, which improves the IO performance of the database.
Since we are operating on the files of the data table, we need to first understand the storage of the MySQL table. We know that MySQL has multiple storage engines, and different storage engines store different file formats. Here we mainly use the two storage engines InnoDB and MyISAM for explanation.
InnoDB
.frm The structure of the file data table
.idb The data of the file table File, exclusive table space, each table has an .idb file
.ibdata file table data file, shared table space, all tables use this data
File
MyISAM
##.frm File data table structure
.myd file data file
.myi file index file
1 show variables like '%partition%';
How to partition? When performing horizontal segmentation of the database, we know that horizontal segmentation can be divided into different tables according to the modulus of specified fields, or it can be divided according to date, or segmented according to id, 1-1 million In the first table, 1 million and 1 to 2 million in the second table and so on. In short, we have many ways to do segmentation. Then the database also provides us with a variety of options for us to choose from on table partitions.
MySQL table partitioning strategy
RANGE partitioning
is based on belonging to a given Column values in continuous intervals, assign multiple rows to partitions
1 DROP TABLE IF EXISTS `p_range`; 2 CREATE TABLE `p_range` ( 3 `id` int(10) NOT NULL AUTO_INCREMENT, 4 `name` char(20) NOT NULL, 5 PRIMARY KEY (`id`) 6 ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 7 /*!50100 PARTITION BY RANGE (id) 8 (PARTITION p0 VALUES LESS THAN (8) ENGINE = MyISAM) */;
Maximum value
1 PARTITION BY RANGE (id) 2 ( 3 PARTITION p0 VALUES LESS THAN (8), 4 PARTITION p1 VALUES LESS THAN MAXVALUE)
Applicable scenarios:
This means that all data records with ID greater than 7 exist in the p1 partition.
RANGE partitioning is particularly useful in the following situations:
·When "old" data needs to be deleted. If you were using the partitioning scheme shown in the most recent example above, you could simply use "ALTER TABLE employees DROP PARTITION p0;" to delete all rows for employees who stopped working before 1991. For tables with a large number of rows, this is much more efficient than running a DELETE query such as "DELETE FROM employees WHERE YEAR(separated) <=
1990;"
·Want to use a column that contains date or time values, or values that grow from some other series.
Frequently run queries that depend directly on the columns used to split the table. For example, when executing a query such as
“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”
LIST partitioning is similar to partitioning by RANGE. The difference is that LIST partitioning is based on matching a discrete column value. Select a value from a set of values.
1 DROP TABLE IF EXISTS `p_list`; 2 CREATE TABLE `p_list` ( 3 `id` int(10) NOT NULL AUTO_INCREMENT, 4 `typeid` mediumint(10) NOT NULL DEFAULT '0', 5 `typename` char(20) DEFAULT NULL, 6 PRIMARY KEY (`id`,`typeid`) 7 ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 8 /*!50100 PARTITION BY LIST (typeid) 9 (PARTITION p0 VALUES IN (1,2,3,4) ENGINE = MyISAM, PARTITION p1 VALUES IN (5,6,7,8) ENGINE = MyISAM) */;
HASH Partitioning Select partitions based on the return value of a user-defined expression that uses the values to be inserted into the table. Row column values are calculated. This function can contain any expression valid in MySQL that produces a non-negative integer value. HASH partitioning is primarily used to ensure that data is evenly distributed among a predetermined number of partitions. In RANGE and LIST partitioning, you must explicitly specify in which partition a given column value or set of column values should be stored; in HASH partitioning, MySQL does this automatically, and all you have to do is based on the value to be hashed. Column value specifies a column value or expression and specifies the number of partitions into which the partitioned table will be split
简单点说就是数据的存入可以按 partition by hash(expr); 这里的 expr 可以 是键名也可以是表达式比如 YEAR(time)，如果是表达式的情况下 “但是应当记住，每当插入或更新（或者可能删除）一行，这个表达式都要计 算一次；这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时 影响大量行的运算（例如批量插入）的时候。 ” 在执行删除、写入、更新时这个表达式都会计算一次。 数据的分布采用基于用户函数结果的模数来确定使用哪个编号的分区。换句话，对于一个表达式“expr”，将要保存记录的分区编号为 N ，其中“N = MOD(expr, num)”。 比如上面的 storeid 为 10；那么 N=MOD(10,4) ;N 是等于 2 的，那么这条记录就存储在 p2 的分区里面。 如果插入一个表达式列值为'2005-09-15′的记录到表中，那么保存该条记录的分区确定如下：MOD(YEAR('2005-09-01′),4) = MOD(2005,4) = 1 ; 就存储在 p1 分区里面了。 分区注意点 1、重新分区时，如果原分区里面存在 maxvalue 则新的分区里面也必须包含 maxvalue 否则就错误。1 DROP TABLE IF EXISTS `p_hash`;
2 CREATE TABLE `p_hash` (
3 `id` int(10) NOT NULL AUTO_INCREMENT,
4 `storeid` mediumint(10) NOT NULL DEFAULT '0',
5 `storename` char(255) DEFAULT NULL,
6 PRIMARY KEY (`id`,`storeid`)
7 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
8 /*!50100 PARTITION BY HASH (storeid)9 PARTITIONS 4 */;
alter table p_range2x
reorganize partition p1,p2
into (partition p0 values less than (5), partition p1 values less than maxvalue);
[Err] 1520 – Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
2、分区删除时，数据也同样会被删除 alter table p_range drop partition p0;
3、如果 range 分区列表里面没有 maxvalue 则如有新数据大于现在分区 range 数据值那么这个数据是无法写入到数据库表的。
4、修改表名不需要 删除分区后在进行更改，修改表名后分区存储 myd myi 对应也会自动更改。
如果希望从所有分区删除所有的数据，但是又保留表的定义和表的分区模式，使用 TRUNCATE TABLE 命令。（请参见 13.2.9 节，“TRUNCATE 语法”）。
如果希望改变表的分区而又不丢失数据，使用“ALTER TABLE … REORGANIZE PARTITION”语句。参见下面的内容，或者在 13.1.2 节，“ALTER TABLE 语法” 中参考关于 REORGANIZE PARTITION 的信息。
5、对表进行分区时，不论采用哪种分区方式如果表中存在主键那么主键必须在分区列中。表分区的局限性。
6、list 方式分区没有类似于 range 那种 less than maxvalue 的写法，也就是说 list 分区表的所有数据都必须在分区字段的值列表集合中。
7、在 MySQL 5.1 版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用 MyISAM，而对另一个使用 InnoDB。
8、分区的名字是不区分大小写的，myp1 与 MYp1 是相同的。
The above is the detailed content of Detailed introduction to table partitioning in MySQL. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
MySQL's Role: Databases in Web Applications
Apr 17, 2025 am 12:23 AM
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
How to start mysql by docker
Apr 15, 2025 pm 12:09 PM
The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database
Laravel Introduction Example
Apr 18, 2025 pm 12:45 PM
Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.
Solve database connection problem: a practical case of using minii/db library
Apr 18, 2025 am 07:09 AM
I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.
How to install mysql in centos7
Apr 14, 2025 pm 08:30 PM
The key to installing MySQL elegantly is to add the official MySQL repository. The specific steps are as follows: Download the MySQL official GPG key to prevent phishing attacks. Add MySQL repository file: rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Update yum repository cache: yum update installation MySQL: yum install mysql-server startup MySQL service: systemctl start mysqld set up booting
Laravel framework installation method
Apr 18, 2025 pm 12:54 PM
Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.
MySQL and phpMyAdmin: Core Features and Functions
Apr 22, 2025 am 12:12 AM
MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.
Centos install mysql
Apr 14, 2025 pm 08:09 PM
Installing MySQL on CentOS involves the following steps: Adding the appropriate MySQL yum source. Execute the yum install mysql-server command to install the MySQL server. Use the mysql_secure_installation command to make security settings, such as setting the root user password. Customize the MySQL configuration file as needed. Tune MySQL parameters and optimize databases for performance.
