當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql 表分区优缺点_Mysql分区表局限性总结

發(fā)布時(shí)間：2023/12/9 数据库 29 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql 表分区优缺点_Mysql分区表局限性总结小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

本文測(cè)試的版本

XML/HTML代碼

mysql>select?version();

+------------+

|?version()?|

+------------+

|?5.1.33-log?|

+------------+

1?row?in?set?(0.00?sec)

一、關(guān)于Partitioning Keys, Primary Keys, and Unique

Keys的限制

在5.1中分區(qū)表對(duì)唯一約束有明確的規(guī)定，每一個(gè)唯一約束必須包含在分區(qū)表的分區(qū)鍵(也包括主鍵約束)。

這句話也許不好理解，我們做幾個(gè)實(shí)驗(yàn)：

SQL代碼

CREATETABLEt1

(?id?INTNOTNULL,

uid?INTNOTNULL,

PRIMARYKEY(id)

)

PARTITION?BYRANGE?(id)

(PARTITION?p0?VALUESLESS?THAN(5)?ENGINE?=?INNODB,

PARTITION?p1?VALUESLESS?THAN(10)?ENGINE?=?INNODB

);

CREATETABLEt1

(?id?INTNOTNULL,

uid?INTNOTNULL,

PRIMARYKEY(id)

)

PARTITION?BYRANGE?(id)

(PARTITION?p0?VALUESLESS?THAN(5)?ENGINE?=?MyISAM?DATA?DIRECTORY='/tmp'INDEXDIRECTORY='/tmp',

PARTITION?p1?VALUESLESS?THAN(10)?ENGINE?=?MyISAM?DATA?DIRECTORY='/tmp'INDEXDIRECTORY='/tmp'

);

mysql>?CREATETABLEt1

->?(?id?INTNOTNULL,

->?uid?INTNOTNULL,

->?PRIMARYKEY(id),

->?UNIQUEKEY(uid)

->?)

->?PARTITION?BYRANGE?(id)

->?(PARTITION?p0?VALUESLESS?THAN(5),

->?PARTITION?p1?VALUESLESS?THAN(10)

->?);

ERROR?1503?(HY000):?A?UNIQUEINDEXmust?includeallcolumnsinthetable's?partitioningfunction

二、關(guān)于存儲(chǔ)引擎的限制

2.1 MERGE引擎不支持分區(qū)，分區(qū)表也不支持merge。

2.2 FEDERATED引擎不支持分區(qū)。這限制可能會(huì)在以后的版本去掉。

2.3 CSV引擎不支持分區(qū)

2.4 BLACKHOLE引擎不支持分區(qū)

2.5 在NDBCLUSTER引擎上使用分區(qū)表，分區(qū)類型只能是KEY(or LINEAR KEY) 分區(qū)。

2.6

當(dāng)升級(jí)MYSQL的時(shí)候，如果你有使用了KEY分區(qū)的表(不管是什么引擎，NDBCLUSTER除外)，那么你需要把這個(gè)表dumped在

reloaded。

2.7 分區(qū)表的所有分區(qū)或者子分區(qū)的存儲(chǔ)引擎必須相同，這個(gè)限制也許會(huì)在以后的版本取消。

不指定任何引擎(使用默認(rèn)引擎)。

所有分區(qū)或者子分區(qū)指定相同引擎。

三、關(guān)于函數(shù)的限制

在mysql5.1中建立分區(qū)表的語句中，只能包含下列函數(shù)：

ABS()

CEILING() and FLOOR() (在使用這2個(gè)函數(shù)的建立分區(qū)表的前提是使用函數(shù)的分區(qū)鍵是INT類型)，例如

XML/HTML代碼

mysql>CREATE?TABLE?t?(c?FLOAT)?PARTITION?BY?LIST(?FLOOR(c)?)(

->PARTITION?p0?VALUES?IN?(1,3,5),

->PARTITION?p1?VALUES?IN?(2,4,6)

->);;

ERROR?1491?(HY000):?The?PARTITION?function?returns?the?wrong?type

mysql>CREATE?TABLE?t?(c?int)?PARTITION?BY?LIST(?FLOOR(c)?)(

->PARTITION?p0?VALUES?IN?(1,3,5),

->PARTITION?p1?VALUES?IN?(2,4,6)

->);

Query?OK,?0?rows?affected?(0.01?sec)

DAY()

DAYOFMONTH()

DAYOFWEEK()

DAYOFYEAR()

DATEDIFF()

EXTRACT()

HOUR()

MICROSECOND()

MINUTE()

MOD()

MONTH()

QUARTER()

SECOND()

TIME_TO_SEC()

TO_DAYS()

WEEKDAY()

YEAR()

YEARWEEK()

四、其他限制

4.1 對(duì)象限制

下面這些對(duì)象在不能出現(xiàn)在分區(qū)表達(dá)式

Stored functions, stored procedures, UDFs, or plugins.

Declared variables or user variables.

4.2 運(yùn)算限制

支持加減乘等運(yùn)算出現(xiàn)在分區(qū)表達(dá)式，但是運(yùn)算后的結(jié)果必須是一個(gè)INT或者NULL。 |, &, ^, <

>>, , ~ 等不允許出現(xiàn)在分區(qū)表達(dá)式。

4.3 sql_mode限制

官方強(qiáng)烈建議你在創(chuàng)建分區(qū)表后，永遠(yuǎn)別改變mysql的sql_mode。因?yàn)樵诓煌哪Ｊ较?#xff0c;某些函數(shù)或者運(yùn)算返回的結(jié)果可能會(huì)不一樣。

4.4 Performance considerations.(省略)

4.5 最多支持1024個(gè)分區(qū)，包括子分區(qū)。

當(dāng)你建立分區(qū)表包含很多分區(qū)但沒有超過1024限制的時(shí)候，如果報(bào)錯(cuò) Got error 24 from storage

engine，那意味著你需要增大open_files_limit參數(shù)。

4.6 不支持外鍵。MYSQL中，INNODB引擎才支持外鍵。

4.7 不支持FULLTEXT indexes(全文索引)，包括MYISAM引擎。

SQL代碼

mysql>CREATETABLEarticles?(

->?id?INTUNSIGNED?AUTO_INCREMENTNOTNULLPRIMARYKEY,

->?title?VARCHAR(200),

->?body?TEXT,

->?FULLTEXT?(title,body)

->?)

->?PARTITION?BYHASH(id)

->?PARTITIONS?4;

ERROR?1214?(HY000):?The?used?tabletype?doesn't?support?FULLTEXT?indexes

4.8

不支持spatial column types。

4.9 臨時(shí)表不能被分區(qū)。

SQL代碼

mysql>CREATETemporaryTABLEt1

->?(?id?INTNOTNULL,

->?uid?INTNOTNULL,

->?PRIMARYKEY(id)

->?)

->?PARTITION?BYRANGE?(id)

->?(PARTITION?p0?VALUESLESS?THAN(5)?ENGINE?=?MyISAM,

->?PARTITION?p1?VALUESLESS?THAN(10)?ENGINE?=?MyISAM

->?);

ERROR?1562?(HY000):?Cannot?createtemporarytablewithpartitions

4.10

log table不支持分區(qū)。

SQL代碼

mysql>altertablemysql.slow_log?PARTITIONBYKEY(start_time)?PARTITIONS?2;

ERROR?1221?(HY000):?Incorrect?usage?ofPARTITIONandlogtable

5.11

分區(qū)鍵必須是INT類型，或者通過表達(dá)式返回INT類型，可以為NULL。唯一的例外是當(dāng)分區(qū)類型為KEY分區(qū)的時(shí)候，可以使用其他類型的列作為分區(qū)鍵(

BLOB or TEXT 列除外)。

SQL代碼

mysql>CREATETABLEtkc?(c1CHAR)

->?PARTITION?BYKEY(c1)

->?PARTITIONS?4;

Query?OK,?0?rowsaffected?(0.00?sec)

mysql>?CREATETABLEtkc2?(c1CHAR)

->?PARTITION?BYHASH(c1)

->?PARTITIONS?4;

ERROR?1491?(HY000):?The?PARTITION?functionreturnsthe?wrong?type

mysql>?CREATETABLEtkc3?(c1INT)

->?PARTITION?BYHASH(c1)

->?PARTITIONS?4;

Query?OK,?0?rowsaffected?(0.00?sec)

5.12 分區(qū)鍵不能是一個(gè)子查詢。 A partitioning key may not be a subquery, even if

that subquery resolves to an integer value or NULL

5.13 只有RANG和LIST分區(qū)能進(jìn)行子分區(qū)。HASH和KEY分區(qū)不能進(jìn)行子分區(qū)。

5.14 分區(qū)表不支持Key caches。

SQL代碼

mysql>SETGLOBALkeycache1.key_buffer_size=128*1024;

Query?OK,?0?rowsaffected?(0.00?sec)

mysql>?CACHE?INDEXlogin,user_msg,user_msg_pINkeycache1;

+-----------------+--------------------+----------+---------------------------------------------------------------------+

+-----------------+--------------------+----------+---------------------------------------------------------------------+

+-----------------+--------------------+----------+---------------------------------------------------------------------+

3?rowsinset(0.00?sec)

5.15

分區(qū)表不支持INSERT DELAYED.

SQL代碼

mysql>insertDELAYEDintouser_msg_pvalues(18156629,0,0,0,0,0,0,0,0,0);

ERROR?1616?(HY000):?DELAYED?optionnotsupportedfortable'user_msg_p'

5.16

DATA DIRECTORY 和 INDEX DIRECTORY 參數(shù)在分區(qū)表將被忽略。

這個(gè)限制應(yīng)該不存在了：

SQL代碼

mysql>CREATETABLEt1

->?(?id?INTNOTNULL,

->?uid?INTNOTNULL,

->?PRIMARYKEY(id)

->?)

->?PARTITION?BYRANGE?(id)

->?(PARTITION?p0?VALUESLESS?THAN(5)?ENGINE?=?MyISAM?DATA?DIRECTORY='/tmp'INDEXDIRECTORY='/tmp',

->?PARTITION?p1?VALUESLESS?THAN(10)?ENGINE?=?MyISAM?DATA?DIRECTORY='/tmp'INDEXDIRECTORY='/tmp'

->?);

Query?OK,?0?rowsaffected?(0.01?sec)

5.17

分區(qū)表不支持mysqlcheck和myisamchk

在5.1.33版本中已經(jīng)支持mysqlcheck和myisamchk

SQL代碼

./mysqlcheck?-u?-p?-r?test?user_msg_p;

test.user_msg_p?OK

./myisamchk?-i?/u01/data/test/user_msg_p#P#p0.MYI

Checking?MyISAM?file:?/u01/data/test/user_msg_p#P#p0.MYI

Data?records:?4423615?Deleted?blocks:?0

-?checkfile-size

-?checkrecorddelete-chain

-?checkkeydelete-chain

-?checkindexreference

-?checkdata?recordreferencesindex:?1

Key:?1:?Keyblocks?used:?98%?Packed:?0%Maxlevels:?4

Total:?Keyblocks?used:?98%?Packed:?0%

Usertime0.97,?Systemtime0.02

Maximum?resident?setsize0,?Integral?residentsetsize0

Non-physical?pagefaults?324,?Physical?pagefaults?0,?Swaps?0

Blocks?in0out0,?Messagesin0out0,?Signals?0

Voluntary?context?switches?1,?Involuntary?context?switches?5

5.18

分區(qū)表的分區(qū)鍵創(chuàng)建索引，那么這個(gè)索引也將被分區(qū)。分區(qū)鍵沒有全局索引一說。

5.19 在分區(qū)表使用ALTER TABLE … ORDER BY，只能在每個(gè)分區(qū)內(nèi)進(jìn)行order by。

總結(jié)

以上是生活随笔為你收集整理的mysql 表分区优缺点_Mysql分区表局限性总结的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。

上一篇： JMeter扩展JMeter插件获取更多
下一篇：运行xv6系统