MySQL搞清什麼是分區表？什麼是臨時表？-mysql教程-PHP中文網

什麼是分區表？什麼是臨時表？以下這篇文章帶大家了解一下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
);

登入後複製