Mysql Optimization partition Table _mysql

When the data volume of the database rises to a certain amount, the performance becomes the question which we cannot but pay attention, how optimizes? There are only a few ways to use them:

1, the table, that is, a large number of expression data into a few tables, so that each table data is not much.

Advantages: Increase the concurrent volume, reduce the size of the lock
Disadvantage: High cost of code maintenance, need to change related SQL

2, partition, all the data is still in a table, but the physical storage data according to a certain rules stored in different files, files can also be placed on another disk

Advantages: Small code maintenance, basically no changes, improve IO throughput
Disadvantage: No increase in the degree of concurrency of tables

3, split the business, this essence or a table.

Benefits: Better long-term support
Cons: Code logic refactoring, a lot of work

Of course, each situation has a suitable application scenario, which needs to be selected according to the specific business. Since the relationship between the partitioned and split business and MySQL itself is not at the business level, we only say the most closely related to the database: Table partitioning. However, using table partitioning has the premise that your database must be supported. So, how do I know if my database supports table partitioning? Please execute the following command

Copy Code code as follows:

Show plugins; ---executed in the MySQL console

It is said that the 5.4 version is another order, but I did not test

Copy Code code as follows:

Show variables like '%part% ';

There are generally two ways to table partitions of a database: portrait and landscape. Vertical is the division of different fields in the table into different data files. Landscape is to put the first part of the table data into one file, the other part of the data into a file. MySQL only supports a later way, split horizontally.

1, create the partition table

If you want to use the table's partitioning advantage, not only to the database version support partition, the key to build the partition table, this table and ordinary table is not the same, and must be set up when the table to specify the partition, otherwise the normal table can not be changed to partition table. So, what if you create a partitioned table? The other is very simple, please see the following table statement

CREATE TABLE ' T_part ' (
  ' f_id ' INT DEFAULT null,
  ' F_name ' VARCHAR () default null,
  PRIMARY KEY (' f_id ')
ENGINE = MyISAM DEFAULT CHARSET = UTF8 
PARTITION by RANGE (f_id) (    -----Specify partition mode
  PARTITION p0 VALUES less THAN (a),--divided two districts
  PARTITION p1 VALUES less THAN
)

The above statement builds a "t_part" table with two fields f_id and F_name, and divides the table into two sections P0 and P1 according to the range, when f_id is less than 10 into the P0 partition, when the f_id is greater than 0 less than 20 into the partition P1. So where does the data of f_id greater than 20 fit into? You guessed right, the INSERT statement will have an error.

See, it's so easy to create a partitioned table! Of course, you can add a delete partition at any time, but note that deleting the partition will delete all the data under the current partition.

Copy Code code as follows:

ALTER TABLE T_part ADD partition (partition P2 values less than (MAXVALUE)); ---new partitions
ALTER TABLE T_part DROP partition p2; ----Delete a partition

2, table partitioning of several ways
MySQL supports 5 partitioning methods: Range partition, list partition, hash partition, LINEAR hash partition, and key partition. Each partition has its own usage scenario.

1) Range Partition:

The table for a range partition is partitioned in one of the following ways, and each partition contains the rows of the values of the partition expression in a given contiguous interval. These intervals are contiguous and cannot overlap, and are defined using values less than operators.

The example above is the range partition.

2) List partition:

The list partition in MySQL is similar in many ways to a range partition. As with the range partition, each partition must be clearly defined. The main difference is that the definition and selection of each partition in the list partition is based on the value of a column that belongs to a value in a list set of values, and a range partition is a collection of contiguous interval values. The LIST partition is implemented by using PARTITION by list (expr), where "expr" is a column value or an expression based on a column value, and returns an integer value, and then defines each partition by means of "values in" (value_list). where "Value_list" is a comma-delimited list of integers.

CREATE TABLE ' t_list ' (
  ' f_id ' INT DEFAULT null,
  ' F_name ' VARCHAR () default null,
  PRIMARY KEY (' f_id ')
ENGINE = MyISAM Default CHARSET = UTF8 
PARTITION by list (f_id)
(
 PARTITION p0 values in (1,2,3),----interval values cannot be repeated C8/>partition p1 VALUES in (4,5,6)
);

3) Hash Partition:

A hash partition is primarily used to ensure that data is distributed evenly across predetermined numbers of partitions. In the range and list partitions, you must explicitly specify which partition the given column value or set of column values should be saved in, and in a hash partition, MySQL does this automatically, all you have to do is specify a column value or expression based on the column value that will be hashed. and specifies the number of partitions that the partitioned table will be split into. To split a table using a HASH partition, add a PARTITION by hash (expr) clause to the CREATE TABLE statement, where "expr" is an expression that returns an integer. It can simply be the name of a column in which the field type is a MySQL integral type. In addition, you will most likely need to add a "partitions num" clause later, where num is a non-negative integer that indicates how much of the table will be divided into partitions.

CREATE TABLE ' T_hash ' (
  ' f_id ' INT DEFAULT null,
  ' F_name ' VARCHAR () default null,
  PRIMARY KEY (' f_id ')
ENGINE = MyISAM DEFAULT CHARSET = UTF8 
PARTITION by HASH (f_id)---can specify multiple columns
partitions 4;---Number of partitions

"Expr" can also be any function or other expression that is valid in MySQL, as long as they return an integer that is both very number and not a random number. (In other words, it is both a change and a certainty). It should be remembered, however, that every time you insert or update (or possibly delete) a row, the expression is evaluated once; This means that very complex expressions can cause performance problems, especially when performing operations that affect a large number of rows simultaneously, such as bulk inserts. The most efficient hash function is to compute only a single table column, and its value increases or decreases consistently with the column values, since this takes into account the "trim" on the partition scope. That is, the closer the value of an expression value to the column it is based on, the more effectively MySQL can use the expression for the hash partition.

4) LINEAR Hash partition:

MySQL also supports the linear hashing function, which differs from a regular hash in that the linear hash function uses a linear 2 power (powers-oftwo) algorithm, whereas a regular hash uses a modulus for the value of the hash function. The only difference in syntax between a linear hash partition and a regular hash partition is that the "LINEAR" keyword is added to the "PARTITION by" clause.

5) Key partition:

Partitioning according to key is similar to following a hash partition, except for a user-defined expression used by the hash partition, and the hash function of the key partition is provided by the MySQL server. The MySQL cluster (Cluster) uses function MD5 () to implement the key partition, and for tables using other storage engines, the server uses its own internal hash function, which is based on the same algorithm as password ().

The syntax of the key partition is similar to the hash syntax, except that the keyword is changed to key.

CREATE TABLE ' T_key ' (
  ' f_id ' INT DEFAULT null,
  ' F_name ' VARCHAR () default NULL,
  PRIMARY key (' f_id ')
) ENGINE = MyISAM DEFAULT CHARSET = UTF8 
PARTITION by LINEAR Key (f_id)
partitions 3;

6) Sub Partition:

A sub partition means to partition again on the basis of a partition. And each partition must have the same number of sub partitions.

CREATE TABLE ' T_part ' (
  ' f_id ' INT DEFAULT null,
  ' F_name ' VARCHAR () default null,
  PRIMARY KEY (' f_id ') 
   ) 
PARTITION by RANGE (f_id)
subpartition by HASH (f_id)
subpartitions 2
(PARTITION
  p0   Values     less THAN (a),
  PARTITION P1  values    less THAN
)

The above statement means that two range partitions are established, each partition has two sub partitions according to the hash, and the entire table is divided into 2x2=4 partitions. Of course, it is also possible to define each partition attribute in detail

CREATE TABLE ' T_part ' (
  ' f_id ' INT DEFAULT null,
  ' F_name ' VARCHAR () default null,
  PRIMARY KEY (' f_id ')
) 
PARTITION by RANGE (f_id)
subpartition by HASH (f_id)
(
  PARTITION p0   VALUES less
  THAN C11/>subpartition s0 
      DATA directory = '/disk0/data ' 
      INDEX DIRECTORY = '/disk0/idx ',
    subpartition s1 
      DATA DIRECTORY = '/disk1/data ' 
      INDEX DIRECTORY = '/disk1/idx '
  ),
  PARTITION p1  VALUES less THAN (
    subpartition S2
      Data Directory = '/disk0/data ' 
      INDEX directory = '/disk0/idx ',
    subpartition s3 
      Data directory = '/disk1/ Data ' 
      INDEX DIRECTORY = '/disk1/idx '
  )
)

This allows specific storage disks to be specified for each partition. The prerequisite disk is present.

The partition in MySQL is not processed on a null value (NULL), whether it is a column value or a user-defined expression value. In general, MySQL treats null as 0 in this case. If you want to avoid this, you should not allow null values in the design of the table, most likely by declaring the column "not NULL" to achieve this.