這篇文章主要為大家展示了“MySQL中的分區(qū)表和臨時表是什么”，內(nèi)容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學習一下“MySQL中的分區(qū)表和臨時表是什么”這篇文章吧。
10年積累的成都做網(wǎng)站、網(wǎng)站設計經(jīng)驗，可以快速應對客戶對網(wǎng)站的新想法和需求。提供各種問題對應的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡服務。我雖然不認識你，你也不認識我。但先網(wǎng)站策劃后付款的網(wǎng)站建設流程，更有岱岳免費網(wǎng)站建設讓你可以放心的選擇與我們合作。
MySQL中有一種表叫做臨時表，指的是用CREATE TEMPORARY TABLE語句創(chuàng)建的，它是一種特殊類型的表，它允許存儲臨時結(jié)果，可以在單個會話中多次重復使用，對其他連接是不可見的，當連接中斷后，數(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;
MySQL在5.1的時候開始支持分區(qū)功能，分區(qū)指的是根據(jù)一定規(guī)則，把同一張表中不同行的記錄分配到不同的物理文件中，每個區(qū)都是獨立的，可以獨立處理，也可以作為表的一部分處理，分區(qū)對應用來說是透明的，不會影響業(yè)務。
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ù)，那么首先計算這個值應該在哪個分區(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) ，超出預期，在p1分區(qū)。
子分區(qū)
子分區(qū)也稱為復合分區(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 );
MySQL可以在分區(qū)鍵上使用NULL，會把他當做最小分區(qū)來處理，也就是會存放到第一個分區(qū)，但是在List分區(qū)中，NULL值必須定義在列表中，否則不能被插入。
以上是“MySQL中的分區(qū)表和臨時表是什么”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學習更多知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！
