登录  /  注册

MySQL搞清什么是分区表?什么是临时表?

青灯夜游
发布: 2021-09-14 18:43:08
转载
1464人浏览过

什么是分区表?什么是临时表?下面本篇文章带大家了解一下mysql中的分区表和临时表,希望对大家有所帮助!

MySQL搞清什么是分区表?什么是临时表?

临时表

MySQL中有一种表叫做临时表,指的是用CREATE TEMPORARY TABLE语句创建的,它是一种特殊类型的表,它允许存储临时结果,可以在单个会话中多次重复使用,对其他连接是不可见的,当连接中断后,数据表就会丢失,但也可以使用DROP TABLE在不需要它的情况下显式删除。【相关推荐:mysql视频教程

CREATE TEMPORARY TABLE table_name( 
column_1_definition, 
column_2_definition,
....
);
登录后复制

如果想要创建一个与现有表结构相同的临时表,使用CREATE TEMPORARY TABLE语句那就太麻烦了,可以使用下面语句

CREATE TEMPORARY TABLE temp_table_name SELECT * FROM table_name LIMIT 0;
登录后复制

还有一个特点是,临时表可以与其他表具有相同的名称,例如即使数据库中存在user的表,但也可以在数据库中创建user的临时表。

创建临时表示例

创建一个名为tblemployee的新临时表,此时使用SHOW TABLES是无法看到这张表的。

create temporary table tblemployee
(
id int auto_increment Primary key,
emp_name varchar(500),
emp_address varchar(500),
emp_dept_id int
)
登录后复制

向其插入数据。

mysql> insert into tblemployee values(1,'张三','北京',2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tblemployee;
+----+----------+-------------+-------------+
| id | emp_name | emp_address | emp_dept_id |
+----+----------+-------------+-------------+
|  1 | 张三     | 北京        |           2 |
+----+----------+-------------+-------------+
1 row in set (0.01 sec)

mysql>
登录后复制

基于现有表结构创建

首先创建两个表。

create table tb_user(user_name varchar(255),user_id int(11));

insert tb_user values("张三",1);
insert tb_user values("李四",2);
insert tb_user values("王五",3);

create table balance(user_id int(11),balance decimal(5,2));
insert balance values(1,200);
insert balance values(2,150);
insert balance values(3,100);
登录后复制

创建一个具有姓名和余额的临时表

create temporary table temp_user_balance select user_name,balance from tb_user left join balance on tb_user.user_id=balance.user_id;
登录后复制

查看临时表中数据。

mysql> select * from temp_user_balance;
+-----------+---------+
| user_name | balance |
+-----------+---------+
| 张三      |  200.00 |
| 李四      |  150.00 |
| 王五      |  100.00 |
+-----------+---------+
3 rows in set (0.00 sec)
登录后复制

但是当其他会话查看这个表时,会报错。

mysql> select * from temp_user_balance;
ERROR 1146 (42S02): Table 'test.temp_user_balance' doesn't exist
mysql>
登录后复制

删除临时表

DROP TEMPORARY TABLE table_name;
登录后复制

分区表

MySQL在5.1的时候开始支持分区功能,分区指的是根据一定规则,把同一张表中不同行的记录分配到不同的物理文件中,每个区都是独立的,可以独立处理,也可以作为表的一部分处理,分区对应用来说是透明的,不会影响业务。

MySQL只支持水平分区,不支持垂直分区,水平分区是将同一张表不同行的记录分配到不同的物理文件中,而垂直分区指将同一张表的不同列记录分配到不同的物理文件中。

可以通过SHOW PLUGINS命令来查看MySQL是否启用了分区功能。

MySQL在创建分区的时候使用partition by语句定义每个分区存放的数据,在查询的时候,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询的时候就不用扫描所有分区,提高效率。

分区类型

RANGE分区

他是一种基于一个连续区间范围,把数据分配到不同的分区,是最常用的一种分区类型,下面创建一个以id列区间的分区表。

create table user(id int(11),user_name varchar(255))
partition by range(id)(
partition user0 values less than (100),
partition user1 values less than (200));
登录后复制

创建这个表后,表不再由一个ibd组成,而是由建立时候各个分区的ibd组成,可以先通过下面语句查看data目录位置,然后查看被分区后创建的ibd。

show global variables like "%datadir%"
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
登录后复制
root@hxl-PC:/var/lib/mysql/test# ls
user#p#user0.ibd  user#p#user1.ibd
root@hxl-PC:/var/lib/mysql/test#
登录后复制

然后我们向里面插入3条数据,但是可以看到第三条id为250的却报错了,原因是要插入一个在分区没有定义的值,MySQL则抛出异常。

mysql> insert user values(50,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(150,"张三");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(250,"张三");
ERROR 1526 (HY000): Table has no partition for value 250
mysql>
登录后复制

解决办法是添加一个MAXXXVALUE值的分区,让大于200的值全存放在这里面,这下就可以插入大于200的值了。

alter table user add partition (partition user3 values less than maxvalue);

mysql> insert user values(250,"张三");
Query OK, 1 row affected (0.02 sec)
登录后复制

可以通过下面语句查询PARTITIONS表下得到每个分区具体信息。

select * from information_schema.partitions where table_schema=database() and table_name='user'\G;
登录后复制

由于现在三个分区,所以会出现三个row,每个row中的TABLE_ROWS表示存放的数量,故现在都是1,还有PARTITION_METHOD表示分区类型。

.....
*************************** 1. row ***************************

             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: `id`
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 100
                   TABLE_ROWS: 1
.....
登录后复制

也可以使用explain 查看查询时候使用了哪个分区。

LIST分区

LIST分区类似于RANGE,只是分区列的值只能存放特定的,就是一个枚举列表的值的集合。而RANGE是连续区间值的集合

create table user (id int(11)) 
partition by list(id)(
partition user0 values in(1,3,5,7,9),
partition user1 values in(0,2,4,6,8)
);
登录后复制

同样插入一些数据,可以看到插入10的时候抛出异常,原因也是插入的数据不再分区定义中。

mysql> insert user values(1);
Query OK, 1 row affected (0.02 sec)

mysql> insert user values(2);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(6);
Query OK, 1 row affected (0.02 sec)

mysql> insert user values(9);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(10);
ERROR 1526 (HY000): Table has no partition for value 10
mysql>
登录后复制

而其余1、2、6、9在user0、user1两个分区中各两条。

HASH分区

HASH的目的是将数据均匀的分布到定义的各个分区中,保证各个分区的数据量大致都是一样的, HASH分区没有向RANGE和LIST一样必须规定某个值在哪个分区中保存,HASH分区是自动完成的,我们只需要指定分区数量即可。

create table user (id int(11)) partition by hash(id) partitions 4;
登录后复制

那如何得知这个数据在哪个分区中存储呢,拿500来说,就是通过mod(500,分区数量)来得到,所以500在第1个分区中。

mysql> select mod(500,4)
    -> ;
+------------+
| mod(500,4) |
+------------+
|          0 |
+------------+
登录后复制

在比如31,那么mod(31,4)就是3,所以在第4个分区中,如果把这两个数插入进去,通过information_schema.partitions查看,那么1、4分区的TABLE_ROWS都为1。

通过条件查找数据时,使用到的分区也不一样,比如查找相等的数,那么首先计算这个值应该在哪个分区,然后在进行查找,如果使用<、>来范围查找,则会使用所有分区。

还有HASH可以使用一些函数或其他有效表达式,比如创建时可以使用partition by hash(abs(id)),但并不是所有函数都可以使用,可使用的函数可以参考官网

KEY分区

Key分区和HASH类似,不同的是, HASH 分区允许使用用户自定义的表达式,KEY 分区不允许使用用户自定义的表达式,需要使用 HASH 函数

KEY分区允许多列,而HASH分区只允许一列,另外在有主键或者唯一键的情况下,key中分区列可不指定,默认为主键或者唯一键,如果没有,则必须显性指定列。

create table user(id int(11)) partition by key(id) partitions 4;
登录后复制

但是我并没有找到关于详细分区介绍的算法,而是看到一些说通过PASSWORD的运算,没有搞懂。

Columns分区

Columns分区是5.5引入的分区类型,在此之前,RANGE分区和LIST分区只能支持整数分区,从而需要额外的函数来计算,Columns分区解决了这个问题。

Columns分区可以细分为RANGE Columns 和LIST Columns分区,支持的类型如下:

  • TINYINT、 SMALLINT、 MEDIUMINT、 INT ( INTEGER) 和 BIGINT,但是不支持DECIMAL或 FLOAT。

  • DATE和 DATETIME。

  • CHAR, VARCHAR, BINARY,和 VARBINARY,TEXT和 BLOB列不支持。

create table user (
    a int,
    b int
)
partition by range columns(a, b) (
    partition p0 values less than (5, 12),
    partition p1 values less than (maxvalue, maxvalue)
);
登录后复制

现在插入一些数据

insert into user (a,b) values (4,11);
insert into user (a,b) values (6,13);
登录后复制

第一条由于(4,11) < (5,12) 所以在p0分区,而(6,13) < (5,12) ,超出预期,在p1分区。

子分区

子分区也称为复合分区,可以对分区表RANGE和LIST上分区再进分区。

create table user (id int, purchased date)
    partition by range( year(purchased) )
    subpartition by hash( to_days(purchased) )
    subpartitions 2 (
        partition p0 values less than (1990),
        partition p1 values less than (2000),
        partition p2 values less than maxvalue
);
登录后复制

对NULL的处理

MySQL可以在分区键上使用NULL,会把他当做最小分区来处理,也就是会存放到第一个分区,但是在List分区中,NULL值必须定义在列表中,否则不能被插入。

更多编程相关知识,请访问:编程视频!!

以上就是MySQL搞清什么是分区表?什么是临时表?的详细内容,更多请关注php中文网其它相关文章!

智能AI问答
PHP中文网智能助手能迅速回答你的编程问题,提供实时的代码和解决方案,帮助你解决各种难题。不仅如此,它还能提供编程资源和学习指导,帮助你快速提升编程技能。无论你是初学者还是专业人士,AI智能助手都能成为你的可靠助手,助力你在编程领域取得更大的成就。
来源:掘金社区网
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
作者最新文章
最新问题
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2024 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号