MySQL搞清什麼是分區表？什麼是臨時表？
- 青灯夜游轉載
- 2021-09-14 18:43:081780瀏覽
什麼是分區表？什麼是臨時表？以下這篇文章帶大家了解一下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。
通过条件查找数据时，使用到的分区也不一样，比如查找相等的数，那么首先计算这个值应该在哪个分区，然后在进行查找，如果使用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中文網其他相關文章！