Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve range partition in mysql, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

First, how to start mysqld?

I install mysql in the environment, and the installed home directory homedir is "c:\ Program Files"

\ MySQL\ MySQL Server 5.1\ ", the startup method is very simple: first enter the cmd command line window, as shown below:

Execute the following command: cd c:\ Program Files\ MySQL\ MySQL Server 5.1\ to enter the mysql installation directory

You can see the bin directory, which contains all kinds of executable files for mysql, cd bin. Enter this directory.

Find the executable file of mysqld.exe and execute mysqld, which is to start mysqld, as shown below:

Www.2cto.com

At this point, open the task manager-"process, you can see the mysqld.exe, indicating that the startup was successful.

Second, how to close mysqld?

Run cmd and change the path to: C:\ Program Files\ MySQL\ MySQL Server 5.1\ bin, using the command

Mysqladmin-u root-p shutdown

As shown in the following figure:

Third, innodb_file_per_table settings

The mysql innodb storage engine manages tablespaces by default using shared tablespaces

That is, the indexes and data of all tables are placed in a file with ibdata1, which we can view by executing the following command:

You can see the corresponding Value: OFF. Let's add the following line to the configuration file my.ini:

# tenfy: newly added parameter

Innodb_file_per_table=1

Www.2cto.com

Then, restart mysqld, and at this point you can see:

So, you might ask, the tables that were created earlier in innodb_file_per_table=0

Do you continue to use shared tablespaces at this time? Yes, if we don't modify the table, the previous one can continue to be used, but

Modify shared tablespaces to independent tablespaces, in addition to modifying the innodb_file_per_table parameter

We need to modify all innodb tables to run as follows:

Alter table table_name engine=innodb; (tenfy: note that the red part must be added

Once the execution is completed, we can see in the mytest1 directory of data, according to each partition

The files generated with each .ibd suffix are separate tablespace files, one for each partition.

(we will explain the zoning below.)

Fourth, an overview of mysql partition

Partitioning is not done at the storage engine layer, so in addition to innodb support for partitioning, MyISAM,NDB

All of them support partitioning, while CSV,FEDERATED,MERGE does not support partitioning. While MySQL in version 5.1,

Support for partitioning has been added.

Partitioning is the decomposition of a table or prefix into smaller, more manageable parts, while for applications where users access db

Logically, there is only one table or index (here is different from the access to sub-database and sub-table), but physically this table or

An index may be made up of many physical partitions, each of which is a separate object that can be processed independently.

Www.2cto.com

So how do you tell if partitioning is currently enabled? You can view it by executing the following command:

Or:

Currently, MySQL supports the following partitions:

1Grand partition: as the name implies, an interval partition in which row data is placed into a partition based on the column values of a given continuous interval.

2 list partition: list partition, similar to RANGE partition, except that LIST partition is oriented to discrete enumerable values.

3Partition of Hash: partition according to the return value of the user-defined expression, which cannot be negative.

4Magic key partition: partition according to the hash function provided by MySQL database.

But regardless of the type of partition, you must pay attention to the following two issues:

1. If primary key or unique key exists in the table, the column of the partition must be primary key or

An integral part of unique key, that is, columns of partitioning functions can only fetch subsets from key such as contention or uk. Www.2cto.com

As follows:

The table par_tb1 has a unique key, and their corresponding column is col1,col2. At this point, the column that is partitioned is col3

So there is a 1503 error, at this time we add col3 to unique key or partition with col1 or col2

No problem:

2, if there is no primary key or unique key in the table, you can specify any column as the partition column.

Fifth, RANGE partition.

RANGE partition is the most commonly used partition type, which is mainly characterized by partitioning according to the continuous values of a column.

Therefore, values less than (xxx) is often used in partitions, which is illustrated by specific examples below.

First we create a table called range_par_tbl with an id field of type int when the id is less than 10

Insert the first partition and the second partition when the id is less than 20. As follows:

At this point, we look at the corresponding partition tablespace file (note: because we set up innodb_file_per_table=1 earlier)

After partitioning is enabled at this point, the table is already made up of the partition ibd files when the partition was established, because we are divided into two zones

So you can see the corresponding two files: www.2cto.com

The above is how to implement range partitioning in mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.