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

Partition clipping

Using explain partitions can show whether the partition has been clipped or not.

Mysql > drop table T2

Query OK, 0 rows affected (2.90 sec)

Mysql > CREATE TABLE T2 (

-> fname VARCHAR (50) NOT NULL

-> lname VARCHAR (50) NOT NULL

-> region_code TINYINT UNSIGNED NOT NULL

-> dob DATE NOT NULL

->)

-> PARTITION BY RANGE (YEAR (dob))

-> PARTITION d0 VALUES LESS THAN (1970)

-> PARTITION D1 VALUES LESS THAN (1975)

PARTITION D2 VALUES LESS THAN (1980)

PARTITION d3 VALUES LESS THAN (1985)

PARTITION d4 VALUES LESS THAN (1990)

PARTITION d5 VALUES LESS THAN (2000)

PARTITION D6 VALUES LESS THAN (2005)

-> PARTITION D7 VALUES LESS THAN MAXVALUE

->)

Query OK, 0 rows affected (1.19 sec)

Mysql > explain partitions SELECT * FROM T2 WHERE dob = '1982-06-23'

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | SIMPLE | T2 | D3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+-- +

1 row in set, 2 warnings (0.10 sec)

Mysql > explain partitions SELECT * FROM T2 WHERE year (dob) = 1972

+- -+

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+- -+

| | 1 | SIMPLE | T2 | d0Magol d1Magic d2Magi d3Magi d4Magi d5Magi d6Magi d7 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+- -+

1 row in set, 2 warnings (0.01sec)

Unlike oracle, there is no need to consider the function of the partitioning key (year). When querying with year (), it cannot be cropped instead.

Partition clipping can be used for delete, update, select. The insert operation also automatically selects partitions.

Mysql > explain partitions UPDATE T2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND' 1997-04-25'

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | UPDATE | T2 | D5 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+-- +

1 row in set, 1 warning (0.38 sec)

Mysql > explain partitions DELETE FROM T2 WHERE dob > = '1984-06-21' AND dob explain partitions SELECT * FROM T2 WHERE dob

< '1982-12-01'; +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t2 | d0,d1,d2,d3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) 使用不合法的日期是，执行计划也进行了分区裁剪，但实际查不到数据： mysql>

Explain partitions SELECT * FROM T2 WHERE dob

< '1982-12-00'; +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t2 | d0,d1,d2,d3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 5 warnings (0.00 sec) mysql>

Select * from T4 where datecol select * from T4 where datecol CREATE TABLE T8 (

-> fname VARCHAR (50) NOT NULL

-> lname VARCHAR (50) NOT NULL

-> region_code TINYINT UNSIGNED NOT NULL

-> dob DATE NOT NULL

->)

-> PARTITION BY KEY (region_code)

-> PARTITIONS 8

Query OK, 0 rows affected (1.07 sec)

Mysql > explain update T8 set fname='1' where region_code=7

+-- +

| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+-- +

| | 1 | UPDATE | T2 | p2 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+-- +

1 row in set (0.09 sec)

# # #

Query of partition table

You can specify a list of partition names in the following statement:

Select 、 delete 、 insert 、 replace 、 update 、 load data 、 load xml

You can specify multiple partitions or subzones at the same time, and the names can be unnecessary and contain each other. Such as:

Mysql > show create table employees_sub\ G

* * 1. Row *

Table: employees_sub

Create Table: CREATE TABLE `employees_ sub` (

`id`int (11) NOT NULL AUTO_INCREMENT

`fname` varchar (25) NOT NULL

`lname` varchar (25) NOT NULL

`store_ id` int (11) NOT NULL

`department_ id` int (11) NOT NULL

PRIMARY KEY (`id`, `lname`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/ * 50100 PARTITION BY RANGE (id)

SUBPARTITION BY KEY (lname)

SUBPARTITIONS 2

(PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB

PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB

PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB

PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) * /

1 row in set (0.00 sec)

Mysql > INSERT INTO employees_sub # re-use data in employees table

-> SELECT * FROM employees

Query OK, 18 rows affected (0.40 sec)

Records: 18 Duplicates: 0 Warnings: 0

Mysql > select table_name,partition_name,subpartition_name,table_rows from information_schema.partitions where table_name='employees_sub'

+-+

| | table_name | partition_name | subpartition_name | table_rows | |

+-+

| | employees_sub | p0 | p0sp0 | 4 | |

| | employees_sub | p0 | p0sp1 | 0 | |

| | employees_sub | P1 | p1sp0 | 5 | |

| | employees_sub | P1 | p1sp1 | 0 | |

| | employees_sub | p2 | p2sp0 | 5 | |

| | employees_sub | p2 | p2sp1 | 0 | |

| | employees_sub | p3 | p3sp0 | 4 | |

| | employees_sub | p3 | p3sp1 | 0 | |

+-+

8 rows in set (0.40 sec)

Mysql > select * from employees_sub partition (p0mai p1sp0mp1)

+-- +

| | id | fname | lname | store_id | department_id | |

+-- +

| | 1 | Bob | Taylor | 3 | 2 | |

| | 2 | Frank | Williams | 1 | 2 |

| | 3 | Ellen | Johnson | 3 | 4 | |

| | 4 | Jim | Smith | 2 | 4 |

| | 5 | Mary | Jones | 1 | 1 |

| | 6 | Linda | Black | 2 | 3 |

| | 7 | Ed | Jones | 2 | 1 | |

| | 8 | June | Wilson | 3 | 1 | |

| | 9 | Andy | Smith | 1 | 3 |

+-- +

9 rows in set (0.01 sec)

Examples of several other statements

UPDATE employees PARTITION (p0) SET store_id = 2 WHERE fname = 'Jill'

REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan',' Jones', 3,2)

# # #

Restrictions on zoning

Stored procedures, functions, etc., are not allowed in partitions, and variables cannot be declared

You can use arithmetic operators in partition expressions, but the result must be an integer or NULL

Modifications to sql mode may cause interruptions or data loss in partitioned tables, so do not modify them

The performance of partition tables is affected by file system type, character set, disk speed, swap space, and so on.

Generally, you should make sure that large_files_support is turned on and open_files_limit is set up reasonably.

The innodb engine turns on innodb_file_per_table to improve performance.

The partitioning operation of the table imposes a write lock on the table

Using MyISAM engine is faster than Innodb and NDB.

In version 5. 7, LOAD DATA uses caching to improve performance, and each partition uses 130KB's buffer to improve performance.

Maximum number of partitions: 8192, including subpartitions

Query caching is not supported

Innodb partition tables do not support foreign keys

Alter table... The order by operation sorts only the data within the partition

The primary key and unique index in the table must contain all columns of the partitioning key.

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.