在扫描操作中,mysql优化器只扫描保护数据的那个分区以减少扫描范围获得性能的提高。
分区技术使得数据管理变得简单,删除某个分区不会对另外的分区造成影响,分区有系统直接管理不用手工干预。
查询当前的mysql数据库版本是否支持分区
show variables like '%partition%';
分区类型
【RANGE 分区】:
基于属于一个给定连续区间的列值,把多行分配给分区。
【LIST 分区】:
类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
【HASH分区】:
基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
【KEY分区】
分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
【复合分区】:RANGE—HASH, LIST—HASH, RANGE—Key, LIST—Key
[root@node1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.28 Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit
Bye
[root@node1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.28 Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> show variables like '%partition%';
Empty set (0.00 sec)
【RANGE 分区】:
mysql> create table emp_age_range
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> age int
-> )
-> partition by range(age)
-> (
-> partition p1 values less than (10),
-> partition p2 values less than (20),
-> partition p3 values less than maxvalue
-> );
Query OK, 0 rows affected (0.80 sec)
mysql> create table emp_birthdate_range
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by range(year(birthdate))
-> (
-> partition p1 values less than (1980),
-> partition p2 values less than (1990),
-> partition p3 values less than maxvalue
-> );
Query OK, 0 rows affected (0.50 sec)
【LIST 分区】
mysql> create table emp_deptno_list
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by list(deptno)
-> (
-> partition p1 values in (10),
-> partition p2 values in (20),
-> partition p3 values in (30)
-> );
Query OK, 0 rows affected (0.66 sec)
【HASH分区】
mysql> create table emp_birthday_hash
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by hash(year(birthdate))
-> partitions 4;
Query OK, 0 rows affected (0.41 sec)
【KEY分区】
mysql> create table emp_birthdate_key
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by key(birthdate)
-> partitions 4;
Query OK, 0 rows affected (1.00 sec)
【复合分区】
mysql> create table emp_birthdate_range_hash
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by range(salary)
-> subpartition by hash(year(birthdate))
-> subpartitions 3
-> (
-> partition p1 values less than (2000),
-> partition p2 values less than maxvalue
-> );
Query OK, 0 rows affected (0.56 sec)
mysql> create table emp_salary_range_key
-> (empno varchar(20) not null ,
-> empname varchar(20),
-> deptno int,
-> birthdate date not null,
-> salary int
-> )
-> partition by range(salary)
-> subpartition by key(birthdate)
-> subpartitions 3
-> (
-> partition p1 values less than (2000),
-> partition p2 values less than maxvalue
-> );
Query OK, 0 rows affected (0.62 sec)
mysql> CREATE TABLE emp_birthdate_list_hash (
-> empno varchar(20) NOT NULL,
-> empname varchar(20) ,
-> deptno int,
-> birthdate date NOT NULL,
-> salary int
->
-> )
-> PARTITION BY list (deptno)
-> subpartition by hash(year(birthdate))
-> subpartitions 3
-> (
-> PARTITION p1 VALUES in (10),
-> PARTITION p2 VALUES in (20)
-> );
Query OK, 0 rows affected (0.55 sec)
mysql> CREATE TABLE emp_list_key (
-> empno varchar(20) NOT NULL,
-> empname varchar(20) ,
-> deptno int,
-> birthdate date NOT NULL,
-> salary int
-> )
-> PARTITION BY list (deptno)
-> subpartition by key(birthdate)
-> subpartitions 3
-> (
-> PARTITION p1 VALUES in (10),
-> PARTITION p2 VALUES in (20)
-> );
Query OK, 0 rows affected (0.88 sec)
mysql> show tables;
+--------------------------+
| Tables_in_test |
+--------------------------+
| emp_age_range |
| emp_birthdate_key |
| emp_birthdate_list_hash |
| emp_birthdate_range |
| emp_birthdate_range_hash |
| emp_birthday_hash |
| emp_deptno_list |
| emp_list_key |
| emp_salary_range_key |
+--------------------------+
9 rows in set (0.00 sec)
1.可以查看创建分区表的create语句 show create table 表名
2.可以查看表是不是分区表 show table status
3.查看information_schema.partitions表 ,可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息
select
*
from information_schema.partitions where
table_schema = schema()
and table_name='test';
4.查看SQL执行计划,explain partitions select语句
通过此语句来显示扫描哪些分区,及他们是如何使用的.
mysql> desc emp_age_range;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| empno | varchar(20) | NO | | NULL | |
| empname | varchar(20) | YES | | NULL | |
| deptno | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> show create table emp_age_range;
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_age_range | CREATE TABLE `emp_age_range` (
`empno` varchar(20) NOT NULL,
`empname` varchar(20) DEFAULT NULL,
`deptno` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (age)
(PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> select
-> partition_name part,
-> partition_expression expr,
-> partition_description descr,
-> table_rows
-> from information_schema.partitions where
-> table_schema = schema() ;
+------+-----------------+----------+------------+
| part | expr | descr | table_rows |
+------+-----------------+----------+------------+
| p1 | age | 10 | 0 |
| p2 | age | 20 | 0 |
| p3 | age | MAXVALUE | 0 |
| p0 | `birthdate` | NULL | 0 |
| p1 | `birthdate` | NULL | 0 |
| p2 | `birthdate` | NULL | 0 |
| p3 | `birthdate` | NULL | 0 |
| p1 | deptno | 10 | 0 |
| p1 | deptno | 10 | 0 |
| p1 | deptno | 10 | 0 |
| p2 | deptno | 20 | 0 |
| p2 | deptno | 20 | 0 |
| p2 | deptno | 20 | 0 |
| p1 | year(birthdate) | 1980 | 0 |
| p2 | year(birthdate) | 1990 | 0 |
| p3 | year(birthdate) | MAXVALUE | 0 |
| p1 | salary | 2000 | 0 |
| p1 | salary | 2000 | 0 |
| p1 | salary | 2000 | 0 |
| p2 | salary | MAXVALUE | 0 |
| p2 | salary | MAXVALUE | 0 |
| p2 | salary | MAXVALUE | 0 |
| p0 | year(birthdate) | NULL | 0 |
| p1 | year(birthdate) | NULL | 0 |
| p2 | year(birthdate) | NULL | 0 |
| p3 | year(birthdate) | NULL | 0 |
| p1 | deptno | 10 | 0 |
| p2 | deptno | 20 | 0 |
| p3 | deptno | 30 | 0 |
| p1 | deptno | 10 | 0 |
| p1 | deptno | 10 | 0 |
| p1 | deptno | 10 | 0 |
| p2 | deptno | 20 | 0 |
| p2 | deptno | 20 | 0 |
| p2 | deptno | 20 | 0 |
| p1 | salary | 2000 | 0 |
| p1 | salary | 2000 | 0 |
| p1 | salary | 2000 | 0 |
| p2 | salary | MAXVALUE | 0 |
| p2 | salary | MAXVALUE | 0 |
| p2 | salary | MAXVALUE | 0 |
+------+-----------------+----------+------------+
41 rows in set (0.01 sec)
mysql> select
-> *
-> from information_schema.partitions where
-> table_schema = schema() ;
相关推荐
用存储过程实现了MySQL数据库分区表的自动创建和自动删除功能。亲测有效。希望有用。
表分区是最近才知道的哦 ,以前自己做都是分表来实现上亿级别的数据了,下面我来给大家介绍一下mysql表分区创建与使用吧,希望对各位同学会有所帮助。表分区的测试使用,主要内容来自于其他博客文章以及mysql5.1的...
MYSQL分区表测试过程详细说明。MYSQL分区表测试过程详细说明。
实现mysql按时间分区方式自动创建与删除分区,包括创建/删除日志记录,通过存储过程与事件联合实现,自动创建数量与删除数量可动态配置
互联网公司为啥不使用mysql分区表
python3.7.4版本,文件包含excel文件和py文件。 py文件中需要手动设置excel字段在mysql中的类型、index索引及写入时校验的字段...执行py文件,若不存在数据库及表会自动创建,并写入数据(对于指定字段重复的不会写入)
php for mysql的表分区类,为mysql的大数据查询提供分区支持. 该类包含追加 创建 删除分区方法.可以通过索引 分类等字段为条件对数据库物理文件分离. 数据表的查询不受任何影响
mysql表分区策略,包含range分区、list分区、hash分区等方法介绍及详解
mysql数据库表分区教程,详细说明mysql表分区的每一个步骤
实现mysql 每天定时自动给数据库表追加分区,包含存储计划和存储过程
MySQL中创建数据表Range分区.pdf
本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景,有需要的朋友可以参考一下,希望对你有所帮助。 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据。...
MySQL分区分表方案实践手册
分库什么的,如果是垂直的话,dba 没法一个人搞的,肯定要和研发的做下来谈的。水平可以自己搞!
数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果,但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间,同时对于应用来说分区完全是透明的。 MYSQL的分区主要有两种...
同时有时候可能出现数据剥离什么的,分区表就更有用处了! MySQL 5.1 中新增的分区(Partition)功能就开始增加,优势也越来越明显了: 与单个磁盘或文件系统分区相比,可以存储更多的数据 很容易就能删除不用或者...
- 在上述代码中,我们首先创建了一个名为`orders`的分区表,该表包含三个字段:`id`、`order_date`和`customer_id`。然后,我们使用`PARTITION BY RANGE`子句对表进行分区,根据`order_date`字段的值将数据划分为四...
本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景,有需要的朋友可以参考一下,希望对你有所帮助。 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据...
测试结果 博文链接:https://hawthorstein.iteye.com/blog/888755
禁止构建 分区表达式不支持以下几种构建: 存储过程,存储函数,UDFS或者...在MySQL 5.7.1之前的分区表不支持HANDLER语句,以后的版本取消了这一限制。 服务器SQL模式 如果要用用户自定义分区的表的话,需要注意的是