MySQL中的分區(qū)表和臨時表是什么

這篇文章主要為大家展示了“MySQL中的分區(qū)表和臨時表是什么”，內(nèi)容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“MySQL中的分區(qū)表和臨時表是什么”這篇文章吧。

10年積累的成都做網(wǎng)站、網(wǎng)站設(shè)計經(jīng)驗，可以快速應(yīng)對客戶對網(wǎng)站的新想法和需求。提供各種問題對應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認識你，你也不認識我。但先網(wǎng)站策劃后付款的網(wǎng)站建設(shè)流程，更有岱岳免費網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

臨時表

MySQL中有一種表叫做臨時表，指的是用CREATE TEMPORARY TABLE語句創(chuàng)建的，它是一種特殊類型的表，它允許存儲臨時結(jié)果，可以在單個會話中多次重復(fù)使用，對其他連接是不可見的，當連接中斷后，數(shù)據(jù)表就會丟失，但也可以使用DROP TABLE在不需要它的情況下顯式刪除。

CREATE TEMPORARY TABLE table_name( 
column_1_definition, 
column_2_definition,
....
);

如果想要創(chuàng)建一個與現(xiàn)有表結(jié)構(gòu)相同的臨時表，使用CREATE TEMPORARY TABLE語句那就太麻煩了，可以使用下面語句

CREATE TEMPORARY TABLE temp_table_name SELECT * FROM table_name LIMIT 0;

還有一個特點是，臨時表可以與其他表具有相同的名稱，例如即使數(shù)據(jù)庫中存在user的表，但也可以在數(shù)據(jù)庫中創(chuàng)建user的臨時表。

創(chuàng)建臨時表示例

創(chuàng)建一個名為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
)

向其插入數(shù)據(jù)。

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>

基于現(xiàn)有表結(jié)構(gòu)創(chuàng)建

首先創(chuàng)建兩個表。

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);

創(chuàng)建一個具有姓名和余額的臨時表

create temporary table temp_user_balance select user_name,balance from tb_user left join balance on tb_user.user_id=balance.user_id;

查看臨時表中數(shù)據(jù)。

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;

分區(qū)表

MySQL在5.1的時候開始支持分區(qū)功能，分區(qū)指的是根據(jù)一定規(guī)則，把同一張表中不同行的記錄分配到不同的物理文件中，每個區(qū)都是獨立的，可以獨立處理，也可以作為表的一部分處理，分區(qū)對應(yīng)用來說是透明的，不會影響業(yè)務(wù)。

MySQL只支持水平分區(qū)，不支持垂直分區(qū)，水平分區(qū)是將同一張表不同行的記錄分配到不同的物理文件中，而垂直分區(qū)指將同一張表的不同列記錄分配到不同的物理文件中。

可以通過SHOW PLUGINS命令來查看MySQL是否啟用了分區(qū)功能。

MySQL在創(chuàng)建分區(qū)的時候使用partition by語句定義每個分區(qū)存放的數(shù)據(jù)，在查詢的時候，優(yōu)化器會根據(jù)分區(qū)定義過濾那些沒有我們需要數(shù)據(jù)的分區(qū)，這樣查詢的時候就不用掃描所有分區(qū)，提高效率。

分區(qū)類型

RANGE分區(qū)

他是一種基于一個連續(xù)區(qū)間范圍，把數(shù)據(jù)分配到不同的分區(qū)，是最常用的一種分區(qū)類型，下面創(chuàng)建一個以id列區(qū)間的分區(qū)表。

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));

創(chuàng)建這個表后，表不再由一個ibd組成，而是由建立時候各個分區(qū)的ibd組成，可以先通過下面語句查看data目錄位置，然后查看被分區(qū)后創(chuàng)建的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條數(shù)據(jù)，但是可以看到第三條id為250的卻報錯了，原因是要插入一個在分區(qū)沒有定義的值，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值的分區(qū)，讓大于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表下得到每個分區(qū)具體信息。

select * from information_schema.partitions where table_schema=database() and table_name='user'\G;

由于現(xiàn)在三個分區(qū)，所以會出現(xiàn)三個row，每個row中的TABLE_ROWS表示存放的數(shù)量，故現(xiàn)在都是1，還有PARTITION_METHOD表示分區(qū)類型。

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

             PARTITION_METHOD: RANGE
          SUBPARTITION_METHOD: NULL
         PARTITION_EXPRESSION: `id`
      SUBPARTITION_EXPRESSION: NULL
        PARTITION_DESCRIPTION: 100
                   TABLE_ROWS: 1
.....

也可以使用explain 查看查詢時候使用了哪個分區(qū)。

LIST分區(qū)

LIST分區(qū)類似于RANGE，只是分區(qū)列的值只能存放特定的，就是一個枚舉列表的值的集合。而RANGE是連續(xù)區(qū)間值的集合

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)
);

同樣插入一些數(shù)據(jù)，可以看到插入10的時候拋出異常，原因也是插入的數(shù)據(jù)不再分區(qū)定義中。

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兩個分區(qū)中各兩條。

HASH分區(qū)

HASH的目的是將數(shù)據(jù)均勻的分布到定義的各個分區(qū)中，保證各個分區(qū)的數(shù)據(jù)量大致都是一樣的， HASH分區(qū)沒有向RANGE和LIST一樣必須規(guī)定某個值在哪個分區(qū)中保存，HASH分區(qū)是自動完成的，我們只需要指定分區(qū)數(shù)量即可。

create table user (id int(11)) partition by hash(id) partitions 4;

那如何得知這個數(shù)據(jù)在哪個分區(qū)中存儲呢，拿500來說，就是通過mod(500,分區(qū)數(shù)量)來得到，所以500在第1個分區(qū)中。

mysql> select mod(500,4)
    -> ;
+------------+
| mod(500,4) |
+------------+
|          0 |
+------------+

在比如31，那么mod(31,4)就是3，所以在第4個分區(qū)中，如果把這兩個數(shù)插入進去，通過information_schema.partitions查看，那么1、4分區(qū)的TABLE_ROWS都為1。

通過條件查找數(shù)據(jù)時，使用到的分區(qū)也不一樣，比如查找相等的數(shù)，那么首先計算這個值應(yīng)該在哪個分區(qū)，然后在進行查找，如果使用<、>來范圍查找，則會使用所有分區(qū)。

還有HASH可以使用一些函數(shù)或其他有效表達式，比如創(chuàng)建時可以使用partition by hash(abs(id))，但并不是所有函數(shù)都可以使用，可使用的函數(shù)可以參考官網(wǎng) ；

KEY分區(qū)

Key分區(qū)和HASH類似，不同的是， HASH 分區(qū)允許使用用戶自定義的表達式，KEY 分區(qū)不允許使用用戶自定義的表達式，需要使用 HASH 函數(shù)

KEY分區(qū)允許多列，而HASH分區(qū)只允許一列，另外在有主鍵或者唯一鍵的情況下，key中分區(qū)列可不指定，默認為主鍵或者唯一鍵，如果沒有，則必須顯性指定列。

create table user(id int(11)) partition by key(id) partitions 4;

但是我并沒有找到關(guān)于詳細分區(qū)介紹的算法，而是看到一些說通過PASSWORD的運算，沒有搞懂。

Columns分區(qū)

Columns分區(qū)是5.5引入的分區(qū)類型，在此之前，RANGE分區(qū)和LIST分區(qū)只能支持整數(shù)分區(qū)，從而需要額外的函數(shù)來計算，Columns分區(qū)解決了這個問題。

Columns分區(qū)可以細分為RANGE Columns 和LIST Columns分區(qū)，支持的類型如下：

  • 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)
);

現(xiàn)在插入一些數(shù)據(jù)

insert into user (a,b) values (4,11);
insert into user (a,b) values (6,13);

第一條由于(4,11) < (5,12) 所以在p0分區(qū)，而(6,13) < (5,12) ，超出預(yù)期，在p1分區(qū)。

子分區(qū)

子分區(qū)也稱為復(fù)合分區(qū)，可以對分區(qū)表RANGE和LIST上分區(qū)再進分區(qū)。

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可以在分區(qū)鍵上使用NULL，會把他當做最小分區(qū)來處理，也就是會存放到第一個分區(qū)，但是在List分區(qū)中，NULL值必須定義在列表中，否則不能被插入。

以上是“MySQL中的分區(qū)表和臨時表是什么”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

當前題目：MySQL中的分區(qū)表和臨時表是什么
文章位置：http://muchs.cn/article32/pphpsc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供移動網(wǎng)站建設(shè)、網(wǎng)站導(dǎo)航、搜索引擎優(yōu)化、動態(tài)網(wǎng)站、App設(shè)計、微信小程序

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

成都seo排名網(wǎng)站優(yōu)化