MySQL은 파티션 테이블이 무엇인지 이해합니까? 임시 테이블이란 무엇입니까?
- 青灯夜游앞으로
- 2021-09-14 18:43:081717검색
파티션 테이블이란 무엇인가요? 임시 테이블이란 무엇입니까? 다음 문서에서는 MySQL의 파티션 테이블과 임시 테이블을 안내합니다. 도움이 되기를 바랍니다.
임시 테이블
MySQL에는 임시 테이블이라는 일종의 테이블이 있는데, 이는 CREATE TEMPORARY TABLE 문으로 생성된 테이블을 의미합니다. 저장을 허용하는 테이블 임시 결과는 단일 세션에서 여러 번 재사용될 수 있으며 연결이 중단되면 데이터 테이블이 손실되지만 다음과 같은 경우 DROP TABLE을 사용할 수도 있습니다. 명시적으로 삭제하는 경우에는 필요하지 않습니다. [관련 권장사항: 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]
create table user(id int(11)) partition by key(id) partitions 4;🎜만약 당신이 기존 테이블과 동일한 구조로 임시 테이블을 생성하려면
CREATE TEMPORARY TABLE 문을 사용하는 것이 너무 번거롭습니다. 다음 명령문을 사용할 수 있습니다🎜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)
);🎜또 다른 특징은 임시 테이블입니다. 동일한 이름을 가진 다른 테이블과 결합될 수 있다. 예를 들어, 데이터베이스에 사용자의 테이블이 존재하더라도 데이터베이스에는 사용자의 임시 테이블이 생성될 수 있다. 🎜🎜🎜임시 테이블 생성 예🎜🎜🎜이 테이블은 현재 SHOW TABLES를 사용하여 볼 수 없습니다. 🎜insert into user (a,b) values (4,11); insert into user (a,b) values (6,13);🎜데이터를 삽입하세요. 🎜
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
);🎜🎜기존 테이블 구조를 기반으로 생성🎜🎜🎜먼저 두 개의 테이블을 생성합니다. 🎜rrreee🎜이름과 잔액이 포함된 임시 테이블을 만듭니다.🎜rrreee🎜임시 테이블의 데이터를 봅니다. 🎜rrreee🎜하지만 다른 세션에서 이 표를 보면 오류가 보고됩니다. 🎜rrreee🎜🎜임시 테이블 삭제🎜🎜rrreee🎜🎜파티션 테이블🎜🎜🎜MySQL은 5.1부터 파티셔닝 기능을 지원하기 시작했습니다. 특정 규칙에 따라 동일한 테이블의 서로 다른 행의 레코드를 서로 다른 실제 파일에 할당합니다. 각 영역은 독립적이며 독립적으로 또는 테이블의 일부로 처리될 수 있으며 파티션은 애플리케이션에 투명하며 비즈니스에 영향을 미치지 않습니다. 🎜🎜MySQL은 수평 분할만 지원하고 수직 분할은 지원하지 않습니다. 수평 분할은 동일한 테이블의 서로 다른 행의 레코드를 서로 다른 물리적 파일에 할당하는 반면, 수직 분할은 동일한 테이블의 서로 다른 열의 레코드를 서로 다른 중간 물리적 파일에 할당하는 것을 의미합니다. . 🎜🎜SHOW PLUGINS 명령을 통해 MySQL에 파티셔닝 기능이 활성화되어 있는지 확인할 수 있습니다. 🎜🎜MySQL은 파티션을 생성할 때 각 파티션에 저장된 데이터를 정의하기 위해 파티션별 문을 사용합니다. 쿼리 시 최적화 프로그램은 파티션 정의를 기반으로 필요한 데이터가 없는 파티션을 필터링하므로 필요가 없습니다. 쿼리 시 모든 파티션을 검색하여 효율성을 높입니다. 🎜🎜🎜파티션 유형🎜🎜🎜🎜RANGE 파티션🎜🎜🎜연속적인 범위를 기준으로 여러 파티션에 데이터를 할당하는 방식이 가장 일반적입니다. 파티셔닝 유형을 사용하면 id 열 간격으로 파티셔닝된 테이블을 생성합니다. 🎜rrreee🎜이 테이블을 생성한 후에는 테이블이 더 이상 하나의 IBD로 구성되지 않고 생성 당시 각 파티션의 IBD로 구성됩니다. 먼저 다음 명령문을 통해 데이터 디렉터리의 위치를 확인한 후 생성된 IBD를 확인할 수 있습니다. 분할중입니다. 🎜rrreeerrreee🎜 그런 다음 3개의 데이터를 삽입했는데 ID가 250인 세 번째 조각에서 오류가 보고된 것을 볼 수 있습니다. 그 이유는 파티션에 정의되지 않은 값을 삽입하려고 하고 MySQL에서 오류가 발생하기 때문입니다. 예외. 🎜rrreee🎜해결 방법은 MAXXXVALUE 값이 있는 파티션을 추가하여 200보다 큰 모든 값이 여기에 저장되도록 하는 것입니다. 이제 200보다 큰 값을 삽입할 수 있습니다. 🎜rrreee🎜다음 문을 통해 PARTITIONS 테이블을 쿼리하여 각 파티션의 구체적인 정보를 얻을 수 있습니다. 🎜rrreee🎜이제 파티션이 3개가 있으므로 각 행의 TABLE_ROWS는 저장소 수를 나타내므로 이제 모두 1이고 PARTITION_METHOD는 파티션 유형을 나타냅니다. 🎜rrreee🎜 explain을 사용하여 쿼리에 어떤 파티션이 사용되었는지 확인할 수도 있습니다. 🎜🎜🎜LIST 파티션 🎜🎜🎜LIST 파티션은 파티션 열의 값이 열거 목록의 값 모음인 특정 값만 저장할 수 있다는 점을 제외하면 RANGE와 유사합니다. RANGE는 연속된 간격 값의 모음입니다 🎜rrreee🎜 또한 일부 데이터를 삽입하면 10을 삽입하면 예외가 발생하는 것을 볼 수 있습니다. 그 이유는 삽입된 데이터가 더 이상 파티션 정의에 없기 때문입니다. 🎜rrreee🎜나머지 1, 2, 6, 9는 user0과 user1 파티션에 각각 2개씩 있습니다. 🎜🎜🎜HASH 파티션🎜🎜🎜HASH의 목적은 정의된 각 파티션에 데이터를 균등하게 분배하여 각 파티션의 데이터 양이 대략 동일하도록 하는 것입니다. HASH 파티션은 RANGE 및 LIST와 같은 특정 값을 지정할 필요가 없습니다. 어떤 파티션에 저장할지, HASH 파티셔닝이 자동으로 이루어지므로 파티션 수만 지정하면 됩니다. 🎜rrreee🎜이 데이터가 어느 파티션에 저장되어 있는지 어떻게 알 수 있나요? 500을 예로 들면, mod(500, 파티션 수)를 통해 얻으므로 첫 번째 파티션에 500이 있습니다. 🎜rrreee🎜예를 들어 31이면 mod(31,4)는 3이므로 네 번째 파티션에서 이 두 숫자를 삽입하고 information_schema.partitions를 통해 보면 1입니다. TABLE_ROWS 4개 파티션 중 모두 1개입니다. 🎜通过条件查找数据时，使用到的分区也不一样，比如查找相等的数，那么首先计算这个值应该在哪个分区，然后在进行查找，如果使用f539a70d3ea090bac4faa80192f58ccc来范围查找，则会使用所有分区。
还有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 중국어 웹사이트의 기타 관련 기사를 참조하세요!