久久精品成人免费看,欧美成人免费观看久久,亚洲中文字幕无码久久精品1

什么是分區(qū)表？什么是臨時表？下面本篇文章帶大家了解一下mysql中的分區(qū)表和臨時表，希望對大家有所幫助！

MySQL搞清什么是分區(qū)表？什么是臨時表？

臨時表

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

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&gt;?insert?into?tblemployee?values(1,'張三','北京',2); Query?OK,?1?row?affected?(0.00?sec)  mysql&gt;?select?*?from?tblemployee; +----+----------+-------------+-------------+ |?id?|?emp_name?|?emp_address?|?emp_dept_id?| +----+----------+-------------+-------------+ |??1?|?張三?????|?北京????????|???????????2?| +----+----------+-------------+-------------+ 1?row?in?set?(0.01?sec)  mysql&gt;

基于現(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&gt;?select?*?from?temp_user_balance; +-----------+---------+ |?user_name?|?balance?| +-----------+---------+ |?張三??????|??200.00?| |?李四??????|??150.00?| |?王五??????|??100.00?| +-----------+---------+ 3?rows?in?set?(0.00?sec)

但是當其他會話查看這個表時，會報錯。

mysql&gt;?select?*?from?temp_user_balance; ERROR?1146?(42S02):?Table?'test.temp_user_balance'?doesn't?exist mysql&gt;

刪除臨時表

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&gt;?insert?user?values(50,"張三"); Query?OK,?1?row?affected?(0.01?sec)  mysql&gt;?insert?user?values(150,"張三"); Query?OK,?1?row?affected?(0.01?sec)  mysql&gt;?insert?user?values(250,"張三"); ERROR?1526?(HY000):?Table?has?no?partition?for?value?250 mysql&gt;

解決辦法是添加一個MAXXXVALUE值的分區(qū)，讓大于200的值全存放在這里面，這下就可以插入大于200的值了。

alter?table?user?add?partition?(partition?user3?values?less?than?maxvalue);  mysql&gt;?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&gt;?insert?user?values(1); Query?OK,?1?row?affected?(0.02?sec)  mysql&gt;?insert?user?values(2); Query?OK,?1?row?affected?(0.01?sec)  mysql&gt;?insert?user?values(6); Query?OK,?1?row?affected?(0.02?sec)  mysql&gt;?insert?user?values(9); Query?OK,?1?row?affected?(0.01?sec)  mysql&gt;?insert?user?values(10); ERROR?1526?(HY000):?Table?has?no?partition?for?value?10 mysql&gt;

而其余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&gt;?select?mod(500,4) ????-&gt;?; +------------+ |?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ù)都可以使用，mysql ；

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)

子分區(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 );