MySQL Partitioning technology (I), mysql Partitioning technology

MySQL Partitioning technology (I), mysql Partitioning technology

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: Split large tables into small tables (physically) 1: vertical table sharding-> split a table vertically into several sheets (horizontal table sharding (general focus) ->, which means that a table contains 100 pieces of data and 10 pieces of data (with the same fields). 2. Optimize the SQL statement (you can add indexes to adjust it, however, a large amount of data increases the index maintenance cost.) Horizontal Partitioning technology Splits a table into multiple tables. A common method is to split the records in the table according to a hash algorithm, A simple splitting method is as follows. Similarly, this partitioning method must be used to modify the SQL statements in the front-end application. In addition, an SQL statement may modify two tables. Therefore, you must write two SQL statements to complete a logical transaction, making the judgment logic of the program more and more complex, in this way, the program maintenance cost is high, and the advantage of using the database is lost. * Therefore, the Partitioning technology can effectively avoid the above drawbacks and become a powerful solution to massive data storage. Partition technology:-> effective solution: physically split multiple tables, logically, operations on a table are unchanged-> MySQL partition technology introduction (* mainly used for range and list partitions *): ----- partitions are logically a table, in hardware/physics, there are multiple tables, that is, sharding table indexes and data. The Partitioning technology of MySQL is different from that of the previous table sharding technology. It is a bit similar to that of horizontal table sharding, however, the horizontal table sharding in the logic layer is still a table for the application. In MySQL, there are four partition types: 1: RANGE partition (most commonly used ): based on the column value (field) that belongs to a given continuous interval, allocate multiple rows to the partition --> partition based on the female field as the reference point -- split a table into an index file, data file partition storage 2: LIST partition: similar to partitioning by range, the difference is that list partitions are selected based on a column value matching a value in a discrete value set (the values in the column are fixed values for partitioning, in addition, the enumerated values are suitable for list partitioning --> for example, Gender: male, female) 3: HASH partitioning: Based on The return value of a user-defined expression is used to calculate the selected partition. The expression is calculated using the column values of the rows to be inserted into the table, this function can contain any expression that is valid in MySQL and generates negative integer values ---> randomly distributes the data inserted each time to multiple partitions, in the end, data in multiple partitions is evenly distributed, but the values in each partition may be different because they are randomly allocated (usually 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 for testing (the hash type is used for testing) :-> myisam adds, deletes, modifies, and queries faster. create table t2 (id int) engine = myisampartition by hash (id) partitions 5; -> If yes, when you insert data, it will be randomly allocated and inserted into each partition to create a storage \ d //-> before the end symbol is changed; the number is changed to // create proc Edure p5 () beginset @ I = 1; while @ I <100000 doinsert into t4 values (@ I); set @ I = @ I + 1; end while; end // execute the created storage call p3 ()-> table p3 inserts 9999 pieces of data into the innodb Data Structure: divided into: Shared tablespace and its exclusive tablespace 1: the innodb table structure shared tablespace cannot be partitioned: Data and indexes of all files are stored in ibddata1. (For example, if you create two tables, the frm file is generated, however, all the data and indexes of the two tables are shared in this file, and the original value is 10 MB: both data and indexes are stored in a file. ibddata1 file 2: the innodb table structure must be an exclusive tablespace for partitioned tables: data and indexes are all independent files that enable exclusive space: (* Exclusive tablespace can be created only when files are enabled in the configuration file. *) innodb_data_hom E_dir = C: \ mysql \ data \ innodb_data_file_path = ibdata1: 10 M: autoextendinnodb_log_group_home_dir = C: \ mysql \ data \ innodb_file_per_table = 1-> Add restart under innodb in the configuration file: mySQL --> pkill mysqld close 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 creating an innodb data table, you will find that creating an x table contains x. frm x. ibd file, it will not be put together with other tables for Table Partitioning * Key Note: only after innodb is set to an independent tablespace can the table partition commands of the innodb table engine be created: /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, you can view whether the partition partitionshow index from tabName; view the index show procedure status; view the resume Storage

How does mysql process hundreds of millions of data?

Stage 1:
1. You must design indexes correctly.
2. You must avoid full table scan of SQL statements. Therefore, you must use an index (for example, everything> <! = And so on will cause full table scanning)
3. Do not query limit or 20.
4. You must avoid queries such as left join and do not submit such logic to the database.
5. Do not create too many indexes for each table. Writing pressure on the database will be increased during big data processing.

Stage 2:

1. Use the table sharding Technology (large tables and small tables)

A) Vertical table sharding: separates some fields, designs a component table, and associates them with the primary key of the primary table.
B) horizontal table sharding: splits records in the same field table into multiple table shards according to a Hash algorithm.

2. mysql Partitioning technology (must be later than version 5.1, which can completely defend against Oracle) is similar to horizontal table sharding, but it is a horizontal table sharding at the logic layer.

Stage 3 (server ):

1. Use Memory Object cache systems such as memcached to reduce database read Operations
2. The Master/Slave database design is used to separate the read/write pressure of the database.
3. uses the proxy server and Web Cache server technologies such as Squid.

PS: Due to space limitations, I only want to talk about some basic concepts. In fact, each knowledge point involves a lot of content. Especially in the first stage, programmers who have been working for several years cannot fully understand it. I think the best way to really understand indexes is to test SQL statements with data of more than 1000 million to million, and then check the index of SQL statements with the explain command.

Is mysql 55 Partitioned Tables mature?

I think it is mature. The reason is that the partition feature has appeared in mysql 5.1. After sun was acquired by oracle, many things of rewriting mysql have been improved, which cannot be compared with the performance of oracle partition tables, however, it can meet basic requirements.
However, what I want to say is that there is no difference between the two. Although there is less data in each partition after the partition, if the partition index (that is, the partition field) is more efficient, if the index is not removed, the efficiency may be even worse.