MySQL Partitioning technology (1)

MySQL Partitioning technology (1)

4: MySQL Partitioning technology (mysql 5.1 is used after version-> it is the technology that the maintenance personnel of the Oracle mysql Technical Team inserted into mysql as plug-ins)

Currently, there are two main methods to optimize massive data:

1: large tables are split into small tables (physically)

I. Vertical table sharding-> vertical table sharding

Ii. Horizontal table sharding (general focus)-> cross-cutting, which means that a table has 100 data cross-cutting 10 tables, and a table has 10 (Consistent fields)

2: optimize SQL statements (you can adjust indexes by adding indexes, but increasing the data volume will increase the index maintenance cost)

The horizontal Partitioning technology Splits a table into multiple tables. The common method is to split the records in the table according to a hash algorithm. A simple splitting method is as follows:

Method. Similarly, this partitioning method must be used to modify the SQL statements in the front-end application. In addition, for an SQL statement, it may modify two

Table, then you must write two SQL statements to complete a logical transaction, making the judgment logic of the program more and more complex, which also leads to the cost of program Maintenance

High, it will lose the advantage of using the database.

* Therefore, the Partitioning technology can effectively avoid the above drawbacks and become a powerful solution to massive data storage.

Partitioning technology:

-> Effective solution: Multiple tables are physically split, and logical operations on one table remain unchanged.

-> MySQL partition technology introduction (* mainly uses range and list partitions *):

----- A partition is a table logically, and multiple tables are physically or hardware, that is, splitting table indexes and data -----

The partition technology of MySQL is different from the previous table sharding technology. It is a bit similar to the horizontal table sharding technology, but it performs horizontal table sharding at the logic layer,

For applications, it is still a table,

In MySQL, there are four partition types:

1. RANGE partitioning (most commonly used): allocates multiple rows to partitions Based on the column values (fields) in a given continuous interval --> partitions Based on female fields as reference points

-- Split a table into index files and data file shard Storage

Ii. LIST partitioning: similar to partitioning by range. The difference is that list partitioning selects a value based on a column value matching a value in a discrete value set (the values in the column are partitioned when they are fixed values, the enumerated values are suitable for list partitioning. For example, Gender: male and female)

3. HASH partition: select a partition based on the return value of the User-Defined expression. Change the expression to use the column values of the rows to be inserted into the table for calculation, this function can contain any expressions that are valid in MySQL and generate negative integer values.

---> Randomly distribute the inserted data to multiple partitions. The data in multiple partitions is evenly distributed, but the values in each partition are likely to be different, because it is randomly allocated (it can be used for MySQL Partition Testing)

4. KEY partitioning: similar to hash partitioning, the difference is that key partitioning only supports the calculation of one or more columns, and the MySQL server provides its own hash function.

Test (the hash type is used for testing):-> myisam is fast In addition, deletion, modification, and query.
Create table t2 (id int) engine = myisam
Partition by hash (id)
Partitions 5;-> yes. When you insert data, it will be randomly allocated and inserted into each partition.
Create a storage
\ D //-> yes before the end symbol is modified; the number is changed //
Create procedure p5 ()
Begin
Set @ I = 1;
While @ I <100000 do
Insert into t4 values (@ I );
Set @ I = @ I + 1;
End while;
End //

Execute the storage just created
Call p3 ()-> table p3 inserts 9999 data records.

Innodb data structure:

Divided into: Shared tablespace and its exclusive tablespace

I. the innodb table structure shared tablespace cannot be partitioned:

The data and indexes of all files are in ibddata1 (for example, if you create two tables, the frm file is generated, but all the data and indexes of the two tables are shared in this file, all partitions cannot be used to partition the table. The initial value is 10 MB)

Cause: Data and indexes are all stored in a file. ibddata1 file.

II. the innodb table structure must be an exclusive table space for partitioned tables"

Cause: Data and indexes are all independent files.

Enable exclusive space: (* You must enable the file in the configuration file to make exclusive tablespaces for partition tables *)
Innodb_data_home_dir = C: \ mysql \ data \
Innodb_data_file_path = ibdata1: 10 M: autoextend
Innodb_log_group_home_dir = C: \ mysql \ data \
Innodb_file_per_table = 1-> Add
Restart: MySQL --> pkill mysqld close process restart MySQL-bin/mysqld_safe -- user = mysql &

Test:
Create table t4 (id int) engine = innodb
Partition by RANGE (id )(
Partition p0 values less than (10000 ),
Partition p1 values less than (20000 ),
PARTITION p2 VALUES less than MAXVALUE );

After you create an innodb data table, you will find that the x. frm x. ibd file exists when you create an x table, and it will not be put together with other tables for Table Partitioning.

* Summary: only after innodb is set as an independent tablespace can the table partitions of the innodb table engine be created.

Related commands:
/S; view the detailed information, such as version, encoding, and so on...
Show engines; view the default table Engine
Show plugins; view all the current MySQL plug-ins and check whether partition partitions are supported
Show index from tabName; view index
Show procedure status; view resume Storage