MySQL Partitioning technology (i)

4:mysql Partitioning technology (is MySQL 5.1 version after the start with the Oracle MySQL technical team maintenance personnel plug-in form into the technology inside the MySQL) Currently, There are 2 methods for the optimization of massive data: 1: Large table split into small table (physical) One: Vertical sub-table and a vertical cut into several two: horizontal sub-table (general focus)--crosscutting, meaning that a table has 100 data crosscutting 10 tables, A table 10 (field consistent) 2:sql statement optimization (can be adjusted by increasing the index, but the large increase in the amount of data will result in the maintenance cost of the index). Horizontal partitioning technology splits a table into multiple tables, and the most common way is to split the records in a table into a hash algorithm, A simple method of splitting is a way to take a touch. Similarly, this partitioning method must be modified for SQL in the front-end application to be used. And for a SQL, it may modify two tables, then you have to write 2 SQL statements so that you can complete a logical transaction, so that the logic of the program is more complex, so that the maintenance cost of the program is high, also lost the advantage of using the database. * Therefore: partitioning technology can effectively avoid the drawbacks, as a powerful way to solve the massive data storage. Partitioning technology:->> effectively solves: physically split multiple tables, logically manipulate a table to indicate the invariant->>mysql partitioning technology (* mainly used with range and list partition *):-----partition is logically a table, in hardware/physics is more than one table    is to split the table index and data-----MySQL partitioning technology differs from the previous table technique, which is somewhat similar to a horizontal table, but it is a horizontal table at the logical level, which is still a table for the application. After the MySQL5.1 version, there are 4 partition types: one: Range partition (most used): Assign multiple rows to partitions based on column values (fields) that belong to a given contiguous interval, and partition them based on the female field as a reference point-splitting a table into: Index file, Data File Shard store two: List partition: Similar to by range partition, the difference is that the list partition is selected based on a value in a set of discrete values matching a column value (the value in the column is a fixed value for partitioning, and the value of the enumeration type is appropriate for the list partition--for example Gender: Male, female) Three: hash partition: The partition is selected based on the return value of the user-defined expression, and the expression is evaluated using the column values of those rows that will be inserted into the table, which can contain any expression that is valid in MySQL that produces a negative integer value---> Random data inserted each time The average allocation to multiple partitions inside, the final multiple partitions inside the data whenEvenly distributed, but the values in each partition will be different, because it is randomly allocated (usually used to do MySQL partition test to use) Four: Key partition: Similar to by the hash partition, the difference is that the key partition only supports the calculation of one or more columns, And the MySQL server provides its own hash function test (test hash type):->myisam Delete and change the speed of the fast CREATE TABLE t2 (id int) Engine=myisampartition by hash (ID)                 Partitions 5; When you insert the data, it is randomly assigned to insert each partition to create a storage \d//, modify the end symbol before it is; change to//create procedure P5 () Beginset @i=1;while @i<100 Doinsert into T4 values (@i), set @[email protected]+1;end while;end//execute the storage call P3 () just created, table P3 insert 9,999 data in NODB data structure: Divided into: Shared table space and its exclusive tablespace one: INNODB table structure shared table space can not be partitioned table: all the file data and indexes are in ibddata1 (for example, you build 2 tables will correspond to generate frm file, But all the data and indexes of the 2 tables are all shared within this file, all of which cannot make a true partition of the table, and the initial value is 10M) because the data and indexes are all in one file. ibddata1 File II: INNODB table structure to make a partitioned table must be "exclusive tablespace" Cause: The data and index are all separate files open Exclusive space: (* must be in the configuration file to open the file in order to make the exclusive table space, can be made into partitioned table *) Innodb_data_home_dir = C:\mysql\datainnodb_data_file_ Path = Ibdata1:10m:autoextendinnodb_log_group_home_dir = c:\mysql\datainnodb_file_per_table=1 In the configuration file under InnoDB Plus restart: MySQL-->pkill mysqld shutdown process restart Mysql-bin/mysqld_safe--user=mysql & Test: Create table t4 (id int) engine=innodbpartition by RANGE (ID) (partition p0 values less than (10000), partition P1 values less than (20000) , PARTITION p2 VALUES less than MAXVALUE); After you create the InnoDB type of data table, you will find that an X table will have x.frm x.ibd files, and will not be put together with other tables to make table partitioning * Summary: You can create a table partition-related command for the InnoDB table engine only if you set the InnoDB to a separate tablespace:/s; Read the details version AH coding ah what ... Show engines; View the default table engine show plugins;  View all current MySQL plug-ins to see if the partition Partitionshow index from from TabName; View Index show procedure status; View the storage of resumes