Shulou(Shulou.com)06/01 Report--

The following together to understand how to distinguish mysql multi-field, I believe you will benefit a lot after reading, the text in the essence is not much, hope that how to distinguish mysql multi-field this short content is what you want.

Partitioning is that according to certain rules, a database breaks down a table into smaller, more manageable parts. As far as accessing database applications is concerned, logically there is only one table or one index, but in fact this table may consist of N physical partition objects, each of which is an independent object that can be processed independently and can be processed as part of the table. Partition is completely transparent to the application and does not affect the business logic of the application.

Partition is conducive to the management of very large tables, it uses divide-and-conquer logic, partition introduces the concept of partition key, partition key is used to perform data aggregation according to a certain interval value (or range value), specific value list or hash function value, so that the data is distributed in different partitions according to rules, so that a large object Beyonc é some small objects.

MySQL partitions can partition both data and indexes.

Note: no matter which kind of partition, either you do not have a primary key / unique key on the partitioned table, or the primary key / unique key of the partitioned table must contain the partitioning key, that is, you cannot use fields other than the primary key / unique key field to partition.

The limitation of MySQL partition mainly includes the following four aspects:

Can store more data than a single disk or file system partition

Optimize the query. When partition conditions are included in the where clause, only one or more necessary partitions can be scanned to improve query efficiency; at the same time, queries involving aggregate functions such as sum () and count () can be easily processed in parallel on each partition, and only need to summarize the results of all partitions

For data that has expired or does not need to be saved, you can delete the data quickly by deleting the partitions associated with the data

Spread data queries across multiple disks for greater query throughput

The function of partitioning and horizontal subtable is similar, dividing the data of a large table into multiple small tables, because the query does not need to scan the whole table, only some partitions need to be scanned, so partitioning can improve the query speed.

Horizontal sub-table requires users to manually and explicitly create multiple sub-tables (such as tbl_user0, tbl_user1, tbl_user2) in advance, physically create multiple tables, through client agents (Sharding-JDBC, etc.) or middleware agents (Mycat, etc.) to achieve sub-table logic.

Partition is a plug-in Plugin function of MySQL, which divides the data of a large table into multiple partition files (such as tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd) at the bottom of the database. Unlike horizontal sub-tables, partitions do not need to explicitly create "sub-tables". The database will automatically create partition files. Users only see an ordinary table, which actually corresponds to multiple partitions. This is shielded and transparent to users, and it is exactly the same as using a table, and does not need any function to achieve. Partitioning is a logical horizontal table, and it is still a table at the physical level.

Before mysql5.5, range and list partitions only supported integer partitions, and an integer could be obtained by additional function operations or additional conversions. Columns partitions are divided into range columns and list columns. Three big data types are supported: integers (tinyint to bigint, but not decimal and float), date (date, datetime), and string (char, varchar, binary, varbinary).

Columns partitions support one or more fields as partition keys, but do not support expressions as partition keys, which is different from range and list partitions. It should be noted that the comparison of range columns partitioning keys is based on tuple comparisons, that is, field group comparisons, which are different from range partitions.

Create talbe rc3 (an int, b int) partition by range columns (a, b) (partition p01 values less than (0,10), partition p02 values less than (10,10), partition p03 values less than (10,20), partition p04 values less than (10,35), partition p05 values less than (10, maxvalue), partition P06 values less than (maxvalue, maxvalue), insert into rc3 (a, b) values (1,10) Select (1,10) < (10,10) from dual;-- is stored on partition p02 according to the result: select partition_name, partition_expression, partition_description, table_rowsfrom information_schema.partitionswhere table_schema = schema () and table_name = 'rc3'

Range columns partition key comparison (tuple comparison) is actually multi-column sorting, first sort according to a field and then sort according to b field, according to the sorting results to partition storage data, and range single field partition sorting rules are actually the same.

After reading this article on how to distinguish between mysql multi-fields, many readers will want to know more about it. For more industry information, you can follow our industry information section.

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.