mysql使用教程之分区表的使用方法(删除分区表)
转载 & & 作者：
mysql分区表使用方法,新增分区、删除分区、分区的合并、分区的拆分等使用方法
MySQL使用分区表的好处：
1，可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。2，方便维护，通过删除分区来删除老的数据。3，分区数据可以被分布到不同的物理位置，可以做分布式有效利用多个硬盘驱动器。
MySQL可以建立四种分区类型的分区：
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。& www.jb51.net&
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
一般用得多的是range分区和list分区。RANGE分区这里以一个销售的业务来做测试销售表有日期/商品/销售额三个字段测试数据从日至日以“月”为单位进行分区初期分区定义首先需要查看，当前数据库是否支持分区
代码如下:mysql&SHOW VARIABLES LIKE '%partition%';+-------------------+-------+| Variable_name&&&& | Value |+-------------------+-------+| have_partitioning | YES&& |+-------------------+-------+1 row in set (0.03 sec)
创建分区表，按照年月的方式分区。 代码如下:mysql& CREATE TABLE sale_data (&&& -&&& sale_date& DATETIME NOT NULL,&&& -&&& sale_item& VARCHAR(2) NOT NULL ,&&& -&&& sale_money DECIMAL(10,2) NOT NULL&&& -& )& www.jb51.net& &&& -& PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date)) (&&& -&&& PARTITION p201001 VALUES LESS THAN (201002),&&& -&&& PARTITION p201002 VALUES LESS THAN (201003),&&& -&&& PARTITION p201003 VALUES LESS THAN (201004),&&& -&&& PARTITION p201004 VALUES LESS THAN (201005),&&& -&&& PARTITION p201005 VALUES LESS THAN (201006),&&& -&&& PARTITION p201006 VALUES LESS THAN (201007),&&& -&&& PARTITION p201007 VALUES LESS THAN (201008),&&& -&&& PARTITION p201008 VALUES LESS THAN (201009),&&& -&&& PARTITION p201009 VALUES LESS THAN (201010),&&& -&&& PARTITION pcatchall VLAUES LESS THAN MAXVALUE&&& -& );Query OK, 0 rows affected (0.20 sec)
代码如下:mysql& ALTER TABLE sale_data&&& -&&& ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));Query OK, 0 rows affected (0.36 sec)Records: 0& Duplicates: 0& Warnings: 0
代码如下:--当删除了一个分区，也同时删除了该分区中所有的数据。mysql& ALTER TABLE sale_data DROP PARTITION p201010;Query OK, 0 rows affected (0.22 sec)& www.jb51.net& Records: 0& Duplicates: 0& Warnings: 0
分区的合并
下面的SQL，将p201001 - p201009 合并为3个分区p2010Q1 - p2010Q3
代码如下:mysql& ALTER TABLE sale_data&&& -&&& REORGANIZE PARTITION p,&&& -&&&&&&&&&&&&&&&&&&&&&&&& p,&&& -&&&&&&&&&&&&&&&&&&&&&&&& p INTO&&& -& (&&& -&&& PARTITION p2010Q1 VALUES LESS THAN (201004),&&& -&&& PARTITION p2010Q2 VALUES LESS THAN (201007),&&& -&&& PARTITION p2010Q3 VALUES LESS THAN (201010)&&& -& );Query OK, 0 rows affected (1.14 sec)Records: 0& Duplicates: 0& Warnings: 0
分区的拆分
下面的SQL，将p2010Q1 分区，拆分为s2009 与s2010 两个分区
代码如下:mysql& ALTER TABLE sale_data REORGANIZE PARTITION p2010Q1 INTO (&&& -&&&&& PARTITION s2009 VALUES LESS THAN (201001),&&&&&&&&&&&& www.jb51.net& &&& -&&&&& PARTITION s2010 VALUES LESS THAN (201004)&&& -& );Query OK, 0 rows affected (0.36 sec)Records: 0& Duplicates: 0& Warnings: 0
一个利用不同物理位置数据源做分区的例子：
代码如下:CREATE TABLE ts (id INT, purchased DATE) &&&&ENGINE=innodb &&&&PARTITION BY RANGE(YEAR(purchased)) &&&&SUBPARTITION BY HASH(id) &&&&( &&&&&&&&PARTITION p0 VALUES LESS THAN (1990) &&&&&&&&( &&&&&&&&&&&&SUBPARTITION s0&&&&&&&&&&&&&&&&& //在大的分区下又有小的分区&&&&&&&&&&&&DATA DIRECTORY='/usr/local/mysql/data0'&&&&& //数据源&&&&&&&&&&&&INDEX DIRECTORY='/usr/local/mysql/index0',&& //索引数据源&&&&&&&&&&&&SUBPARTITION s1 &&&&&&&&&&&&DATA DIRECTORY='/usr/local/mysql/data1'&&&&&&&&&&&&INDEX DIRECTORY='/usr/local/mysql/index1'&&&&&&&&), &&&&&&&&PARTITION p1 VALUES LESS THAN (MAXVALUE) &&&&&&&&( &&&&&&&&&&&&SUBPARTITION s2 &&&&&&&&&&&&DATA DIRECTORY='/usr/local/mysql/data1'&&&&&&&&&&&&INDEX DIRECTORY='/usr/local/mysql/index1', &&&&&&&&&&&&SUBPARTITION s3 &&&&&&&&&&&&DATA DIRECTORY='/usr/local/mysql/data2'&&&&&&&&&&&&INDEX DIRECTORY='/usr/local/mysql/index2'&&&&&&&&) &&&&);
分区索引的局限：1，所有分区都要使用同样的引擎。2，分区表的每一个唯一索引必须包含由分区函数引用的列。3，mysql能避免查询所有的分区，但仍然锁定了所有分区。4，分区函数能使用的函数和表达式有限，例如函数有上面的4种。5，分区不支持外键。& www.jb51.net& 6，不能使用LOAD INDEX INTO CACHE7，分区并不能总是改善性能，要进行性能评测。例如可以使用expalin partitions 来查看查询语句是否使用分区过滤了数据： 代码如下:mysql& explain partitions select * from fenqubiao where day&'';+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+| id | select_type | table&&&& | partitions&&& | type | possible_keys | key& | key_len | ref& | rows | Extra&&&&&& |+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+|& 1 | SIMPLE&&&&& | fenqubiao | p_2010,p_2011 | ALL& | NULL&&&&&&&&& | NULL | NULL&&& | NULL |&&& 2 | Using where |+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具Mysql分区表使用的一些限制和需要注意的地方 - grefr - 博客园
随笔 - 278, 文章 - 0, 评论 - 9, 引用 - 0
mysql分区策略都基于两个非常重要的假设：查询都能够过滤（prunning)掉很多额外的分区、分区本身并不会带来很多额外的代价。而事实证明，这两个假设在某些场景下会有问题。下面介绍一些可能会遇到的问题。
NULL位会使分区过滤无效
& 关于分区表一个容易让人误解的地方就是分区的表达式的值可以是NULL：第一个分区是一个特殊分区。假设按照PARTITION BY & & RANGE YEAR(order_date）分区，那么所有order_date为NULL或者是一个非法值的时候，记录都会被存放到第一个分区。现在假设有 & 下面的查询：WHERE order_date BETWEEN &''AND’’。实际上，MySQL会检查两个分区，而不是之前猜想的 & 一个：它会检查2012年这个分区，同时它还会检查这个表的第一个分区。检查第一个分区是因为YEAR（）函数在接收非法值的时候 & 可能会返回NULL值，那么这个范围的值可能会返回NULL而被存放到第一个分区了。这一点对于其他很多函数，例如TO_DAYS（）也一 & 样。
& 如果第一个分区非常大，特别是当使用“全量扫描数据，不要任何索引”的策略时，代价会非常大。而且扫描两个分区来查找列 & & 也不是我们使用分区表的初衷。为了避免这种情况，可以创建一个“无用”的第一个分区，例如，上面的例子中可以使用PARTITION & p_nulls VALUES LESS THAN(0）来创建第一个分区。如果插入表中的数据都是有效的，那么第一个分区就是空的，这样即使需要检 & 测第一个分区，代价也会非常小。
& 在MySQL5.5中就不需要这个优化技巧了，因为可以直接使用列本身而不是基于列的函数进行分区：PARTITION BY RANGE & & & &COLUMNS(order_date).所以这个案例最好的解决方越是能够直接使用MySQL5.5的这个语法。
分区列和索引列不匹配
& 如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。假设在列a上定义了索引，而在列b上进行分区。因为每个分区 & 都有其独立的索引，所以扫描列b上的索引就需要扫描每一个分区内对应的索引。如果每个分区内对应索引的非叶子节点都在内存 & 中，那么扫描的速度还可以接受，但如果能跳过某些分区索引当然会更好。要避免这个问题，应该避免建立和分区列不匹配的索 & & 引，除非查询中还同时包含了可以过滤分区的条件。
& 听起来避免这个问题很简单，不过有时候也会遇到一些意想不到的问题。例如，在一个关联查询中，分区表在关联顺序中是第二个 & 表，井且关联使用的索引和分区条件并不匹配。那么关联时针对第一个表符合条件的每一行，都需要访问并搜索第二个表的所有分 & 区。
选择分区的成本可能很高
& 如前所述分区有很多类型，不同类型分区的实现方式也不同，所以它们的性能也各不相同。尤其是范围分区，对于回答“这一行 & & 属于哪个分区”、“这些符合查询条件的行在哪些分区”这样的问题的成本可能会非常高，因为服务器需要扫描所有的分区定义的 & 列表来找到正确的答案。类似这样的线性搜索的效率不高，所以随着分区数的增长，成本会越来越高。
& 我们所实际碰到的类似这样的最糟牒的一次问题是按行写入大量数据的时候。每写入一行数据到范围分区的表时，都需要扫描分区 & 定义列表来找到合适的目标分区。可以通过限制分区的数量来缓解此问题，根据实践经验，对大多数系统来说，100个左右的分区是 & 没有问题的。其他的分区类型，比如键分区和哈希分区，则没有这样的问题。
打开并锁住所有底层表的成本可能很高
& 当查询访问分区表的时候，MySQL需要打开井锁住所有的底层表，这是分区表的另一个开销。这个操作在分区过滤之前发生，所以无 & 法通过分区过滤降低此开销，并且该开销也和分区类型无关，会影响所有的查询。这一点对一些本身操作非常快的查询，比如根据 & 主键查找单行，会带来明显的额外开销。可以用批量操作的方式来降低单个操作的此类开销，例如使用批量插入或者 & & & & & LOAD DATA INFILE、一次删除多行数据，等等。当然同时还是需要限制分区的个数。
维护分区的成本可能很高
& 某些分区维护操作的速度会非常快，例如新增或者删除分区（当删除一个大分区可能会很慢，不过这是另一回事）。而有些操作， & 例如重组分区或者类似ALTER语句的操作：这类操作需要复制数据。重组分区的原理与ALTER类似，先创建一个临时的分区，然后将 & 数据复制到其中，最后再删除原分区。
如上所述，分区表不是什么“银弹”。下面是目前分区实现中的一些其他限制：如上所述，分区表不是什么“银弹”。下面是目前分区实现中的一些其他限制：
所有分区都必须使用相同的存储引擎。
分区函数中可以使用的函数和表达式也有一些限制。某些存储引擎不支持分区。
对于MyISAM的分区表，不能再使用LOAD INDEXI INTO CACHE操作。
对于MyISAM表，使用分区表时需要打开更多的文件描述符。虽然看起来是一个表其实背后有很多独立的分区，每一个分区对于存储引擎来说都是一个独立的表。这样即使分区表只占用一个表缓存条目，文件描述符还是需要多个。因此，即使已经配置了合适的表缓存，以确保不会超过操作系统的单个进程可o以打开的文件描述符的个数，但对于分区表而言，还是会出现超过文件描述符限制的问题。
最后，需要指出的是较老版本的MySQL问题会更多些。所有的软件都是有bug的。分区表在MySQL5.1中引人，在后面的5.1.40和5.1.50之后修复了很多分区表的b吨。MySQL5.5中，分区表又做了很多改进，这才使得分区表可以逐步考虑用在生产环境了在即将发布的MySQL5.6版本中，分区表做了更多的增强，例如新引火的ALTER TABLE EXCHANGE PARTITION。Mysql分区表局限性总结
我的图书馆
Mysql分区表局限性总结
昨天晚上asers.z问我怎么样使得数据在搜索的时候和58.com差不多，而且展示数据的速度要快。我一直想着用mysql的分区表解决，而乔楚（乔大姐）则认为是采用sphinx来解决。
但后来我找了一个资料才发现，原来分区表还是有局限性的，比如他就不支持全文索引。我是看这里看到的。。
Mysql5.1已经发行很久了，本文根据官方文档的翻译和自己的一些测试，对Mysql分区表的局限性做了一些总结，因为个人能力以及测试环境的 原因，有可能有错误的地方，还请大家看到能及时指出，当然有兴趣的朋友可以去查阅。
本文测试的版本
XML/HTML代码
mysql&&select&version();&&
+------------+&&
|&version()&&|&&
+------------+&&
|&5.1.33-log&|&&&
+------------+&&
1&row&in&set&(0.00&sec)&&
一、关于Partitioning Keys, Primary Keys, and Unique Keys的限制
在5.1中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键（也包括主键约束）。
这句话也许不好理解，我们做几个实验：
CREATE&TABLE&t1&&&&&
(&&&&&&id&INT&NOT&NULL,&&&&&&&&
&&&&&&&uid&INT&NOT&NULL,&&
&&&&&&&PRIMARY&KEY&(id)&&
PARTITION&BY&RANGE&(id)&&&&&
(PARTITION&p0&VALUES&LESS&THAN(5)&ENGINE&=&INNODB,&&
&PARTITION&p1&VALUES&LESS&THAN(10)&ENGINE&=&INNODB&&
CREATE&TABLE&t1&&&&&
(&&&&&&id&INT&NOT&NULL,&&&&&&&&
&&&&&&&uid&INT&NOT&NULL,&&
&&&&&&&PRIMARY&KEY&(id)&&
PARTITION&BY&RANGE&(id)&&&&&
(PARTITION&p0&VALUES&LESS&THAN(5)&ENGINE&=&MyISAM&DATA&DIRECTORY='/tmp'&INDEX&DIRECTORY='/tmp',&&
&PARTITION&p1&VALUES&LESS&THAN(10)&ENGINE&=&MyISAM&DATA&DIRECTORY='/tmp'&INDEX&DIRECTORY='/tmp'&&
mysql&&CREATE&TABLE&t1&&&&&
&&&&-&&(&&&&&&id&INT&NOT&NULL,&&&&&&&&
&&&&-&&&&&&&&&uid&INT&NOT&NULL,&&
&&&&-&&&&&&&&&PRIMARY&KEY&(id),&&
&&&&-&&&&&&&&&UNIQUE&KEY&(uid)&&
&&&&-&&)&&
&&&&-&&PARTITION&BY&RANGE&(id)&&&&&
&&&&-&&(PARTITION&p0&VALUES&LESS&THAN(5),&&
&&&&-&&&PARTITION&p1&VALUES&LESS&THAN(10)&&
&&&&-&&);&&
ERROR&1503&(HY000):&A&UNIQUE&INDEX&must&include&all&columns&in&the&table's&partitioning&function&&
二、关于存储引擎的限制
2.1 MERGE引擎不支持分区，分区表也不支持merge。
2.2 FEDERATED引擎不支持分区。这限制可能会在以后的版本去掉。
2.3 CSV引擎不支持分区
2.4 BLACKHOLE引擎不支持分区
2.5 在NDBCLUSTER引擎上使用分区表，分区类型只能是KEY(or LINEAR KEY) 分区。
2.6 当升级MYSQL的时候，如果你有使用了KEY分区的表（不管是什么引擎，NDBCLUSTER除外），那么你需要把这个表dumped在 reloaded。
2.7 分区表的所有分区或者子分区的存储引擎必须相同，这个限制也许会在以后的版本取消。
不指定任何引擎（使用默认引擎）。
所有分区或者子分区指定相同引擎。
三、关于函数的限制
在mysql5.1中建立分区表的语句中，只能包含下列函数：
CEILING() and FLOOR() （在使用这2个函数的建立分区表的前提是使用函数的分区键是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)&&
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
DATEDIFF()
MICROSECOND()
TIME_TO_SEC()
YEARWEEK()
四、其他限制
4.1 对象限制
下面这些对象在不能出现在分区表达式
Stored functions, stored procedures, UDFs, or plugins.
Declared variables or user variables.
4.2 运算限制
支持加减乘等运算出现在分区表达式，但是运算后的结果必须是一个INT或者NULL。 |, &, ^, &&, &&, , ~ 等不允许出现在分区表达式。
4.3 sql_mode限制
官方强烈建议你在创建分区表后，永远别改变mysql的sql_mode。因为在不同的模式下，某些函数或者运算返回的结果可能会不一样。
4.4 Performance considerations.(省略)
4.5 最多支持1024个分区，包括子分区。
当你建立分区表包含很多分区但没有超过1024限制的时候，如果报错 Got error 24 from storage engine，那意味着你需要增大open_files_limit参数。
4.6 不支持外键。MYSQL中，INNODB引擎才支持外键。
4.7 不支持FULLTEXT indexes（全文索引），包括MYISAM引擎。
mysql&&CREATE&TABLE&articles&(&&
&&&&-&&id&INT&UNSIGNED&AUTO_INCREMENT&NOT&NULL&PRIMARY&KEY,&&
&&&&-&&title&VARCHAR(200),&&
&&&&-&&body&TEXT,&&
&&&&-&&FULLTEXT&(title,body)&&
&&&&-&&)&&
&&&&-&&PARTITION&BY&HASH(id)&&
&&&&-&&PARTITIONS&4;&&
ERROR&1214&(HY000):&The&used&table&type&doesn't&support&FULLTEXT&indexes&&
4.8 不支持spatial column types。
4.9 临时表不能被分区。
mysql&&CREATE&Temporary&TABLE&t1&&&&&
&&&&-&&(&&&&&&id&INT&NOT&NULL,&&&&&&&&
&&&&-&&&&&&&&&uid&INT&NOT&NULL,&&
&&&&-&&&&&&&&&PRIMARY&KEY&(id)&&
&&&&-&&)&&
&&&&-&&PARTITION&BY&RANGE&(id)&&&&&
&&&&-&&(PARTITION&p0&VALUES&LESS&THAN(5)&ENGINE&=&MyISAM,&&
&&&&-&&&PARTITION&p1&VALUES&LESS&THAN(10)&ENGINE&=&MyISAM&&
&&&&-&&);&&
ERROR&1562&(HY000):&Cannot&create&temporary&table&with&partitions&&
4.10 log table不支持分区。
mysql&&alter&table&mysql.slow_log&PARTITION&BY&KEY(start_time)&PARTITIONS&2;&&
ERROR&1221&(HY000):&Incorrect&usage&of&PARTITION&and&log&table&&
5.11 分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键（ BLOB or TEXT 列除外）。
mysql&&CREATE&TABLE&tkc&(c1&CHAR)&&
&&&&-&&PARTITION&BY&KEY(c1)&&
&&&&-&&PARTITIONS&4;&&
Query&OK,&0&rows&affected&(0.00&sec)&&
mysql&&CREATE&TABLE&tkc2&(c1&CHAR)&&
&&&&-&&PARTITION&BY&HASH(c1)&&
&&&&-&&PARTITIONS&4;&&
ERROR&1491&(HY000):&The&PARTITION&function&returns&the&wrong&type&&
mysql&&CREATE&TABLE&tkc3&(c1&INT)&&
&&&&-&&PARTITION&BY&HASH(c1)&&
&&&&-&&PARTITIONS&4;&&
Query&OK,&0&rows&affected&(0.00&sec)&&
5.12 分区键不能是一个子查询。 A partitioning key may not be a subquery, even if that subquery resolves to an integer value or NULL
5.13 只有RANG和LIST分区能进行子分区。HASH和KEY分区不能进行子分区。
5.14 分区表不支持Key caches。
mysql&&SET&GLOBAL&keycache1.key_buffer_size=128*1024;&&
Query&OK,&0&rows&affected&(0.00&sec)&&
mysql&&CACHE&INDEX&login,user_msg,user_msg_p&IN&keycache1;&&
+-----------------+--------------------+----------+---------------------------------------------------------------------+&&
|&Table&&&&&&&&&&&|&Op&&&&&&&&&&&&&&&&&|&Msg_type&|&Msg_text&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&|&&
+-----------------+--------------------+----------+---------------------------------------------------------------------+&&
|&test.login&&&&&&|&assign_to_keycache&|&status&&&|&OK&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&|&&&
|&test.user_msg&&&|&assign_to_keycache&|&status&&&|&OK&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&|&&&
|&test.user_msg_p&|&assign_to_keycache&|&note&&&&&|&The&storage&engine&for&the&table&doesn't&support&assign_to_keycache&|&&&
+-----------------+--------------------+----------+---------------------------------------------------------------------+&&
3&rows&in&set&(0.00&sec)&&
5.15 分区表不支持INSERT DELAYED.
mysql&&insert&&DELAYED&into&user_msg_p&values(,0,0,0,0,0,0,0,0);&&
ERROR&1616&(HY000):&DELAYED&option&not&supported&for&table&'user_msg_p'&&
5.16 DATA DIRECTORY 和 INDEX DIRECTORY 参数在分区表将被忽略。
这个限制应该不存在了：
mysql&&CREATE&TABLE&t1&&&&&
&&&&-&&(&&&&&&id&INT&NOT&NULL,&&&&&&&&
&&&&-&&&&&&&&&uid&INT&NOT&NULL,&&
&&&&-&&&&&&&&&PRIMARY&KEY&(id)&&
&&&&-&&)&&
&&&&-&&PARTITION&BY&RANGE&(id)&&&&&
&&&&-&&(PARTITION&p0&VALUES&LESS&THAN(5)&ENGINE&=&MyISAM&DATA&DIRECTORY='/tmp'&INDEX&DIRECTORY='/tmp',&&
&&&&-&&&PARTITION&p1&VALUES&LESS&THAN(10)&ENGINE&=&MyISAM&DATA&DIRECTORY='/tmp'&INDEX&DIRECTORY='/tmp'&&
&&&&-&&);&&
Query&OK,&0&rows&affected&(0.01&sec)&&
5.17 分区表不支持mysqlcheck和myisamchk
在5.1.33版本中已经支持mysqlcheck和myisamchk
./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&&
-&check&file-size&&
-&check&record&delete-chain&&
-&check&key&delete-chain&&
-&check&index&reference&&
-&check&data&record&references&index:&1&&
Key:&&1:&&Keyblocks&used:&&98%&&Packed:&&&&0%&&Max&levels:&&4&&
Total:&&&&Keyblocks&used:&&98%&&Packed:&&&&0%&&
User&time&0.97,&System&time&0.02&&
Maximum&resident&set&size&0,&Integral&resident&set&size&0&&
Non-physical&pagefaults&324,&Physical&pagefaults&0,&Swaps&0&&
Blocks&in&0&out&0,&Messages&in&0&out&0,&Signals&0&&
Voluntary&context&switches&1,&Involuntary&context&switches&5&&
5.18 分区表的分区键创建索引，那么这个索引也将被分区。分区键没有全局索引一说。
5.19 在分区表使用ALTER TABLE … ORDER BY，只能在每个分区内进行order by。
看了一下，居然有这么多的限制，唉。不是特别敢用了，再加上现在NoSQL开始风行，天知道以后会怎么样。
上面的文章来自：
转发到朋友圈：
本站采用版权协议, 要求署名、非商业和保持一致. 本站欢迎任何非商业应用的转载, 但须注明出自"", 保留原始链接, 此外还必须标注原文标题和链接.
TA的最新馆藏
喜欢该文的人也喜欢博客分类：
本文总结个这段时间研究MySQL水平分区表总结,列举分区表的相关操作.
在网上看了很多文章,都太过于概念,注意集中在介绍分区表的优点,而不注重时间操作,跟大学教授论文似的,唯一由于的一片文章和大家分享一下吧
MySQL分区表操作代码 (本案例按月分区):
1. 创建分区表
CREATE TABLE `表名` (
`EQUIPMENTID` char(17) NOT NULL,
`ATTRIBUTEID` char(4) NOT NULL,
`VALUE` varchar(20) NOT NULL,
`COLLECTTIME` datetime NOT NULL
ENGINE=InnoDB (适用大部分引擎,可根据需要调整)
DEFAULT CHARSET=latin1(编码可根据需要修改)
PARTITION BY RANGE (to_days(COLLECTTIME))
(PARTITION pmin VALUES LESS THAN (to_days('')),
PARTITION p201001 VALUES LESS THAN (to_days('')) ,
PARTITION p201002 VALUES LESS THAN (to_days('')) ,
PARTITION p201003 VALUES LESS THAN (to_days('')) ,
PARTITION p201004 VALUES LESS THAN (to_days('')) ,
PARTITION p201005 VALUES LESS THAN (to_days('')) ,
PARTITION p201006 VALUES LESS THAN (to_days('')) ,
PARTITION p201007 VALUES LESS THAN (to_days('')) ,
PARTITION p201008 VALUES LESS THAN (to_days('')) ,
PARTITION p201009 VALUES LESS THAN (to_days('')) ,
PARTITION p201010 VALUES LESS THAN (to_days('')),
PARTITION p201011 VALUES LESS THAN (to_days('')),
PARTITION p201012 VALUES LESS THAN (to_days('')),
PARTITION p201101 VALUES LESS THAN (to_days('')),
PARTITION p201102 VALUES LESS THAN (to_days('')),
PARTITION p201103 VALUES LESS THAN (to_days('')),
PARTITION p201104 VALUES LESS THAN (to_days('')),
PARTITION p201105 VALUES LESS THAN (to_days('')),
PARTITION p201106 VALUES LESS THAN (to_days('')),
PARTITION p201107 VALUES LESS THAN (to_days('')),
PARTITION p201108 VALUES LESS THAN (to_days('')),
PARTITION p201109 VALUES LESS THAN (to_days('')),
PARTITION p201110 VALUES LESS THAN (to_days('')),
PARTITION p201111 VALUES LESS THAN (to_days('')),
PARTITION p201112 VALUES LESS THAN (to_days('')),
PARTITION pmax VALUES LESS THAN MAXVALUE );
2. 为现有表创建分区
alter table 表名
PARTITION BY RANGE (to_days(COLLECTTIME))
(PARTITION pmin VALUES LESS THAN (to_days('')),
PARTITION p201001 VALUES LESS THAN (to_days('')) ,
PARTITION p201002 VALUES LESS THAN (to_days('')) ,
PARTITION p201003 VALUES LESS THAN (to_days('')) ,
PARTITION p201004 VALUES LESS THAN (to_days('')) ,
PARTITION p201005 VALUES LESS THAN (to_days('')) ,
PARTITION p201006 VALUES LESS THAN (to_days('')) ,
PARTITION p201007 VALUES LESS THAN (to_days('')) ,
PARTITION p201008 VALUES LESS THAN (to_days('')) ,
PARTITION p201009 VALUES LESS THAN (to_days('')) ,
PARTITION p201010 VALUES LESS THAN (to_days('')),
PARTITION p201011 VALUES LESS THAN (to_days('')),
PARTITION p201012 VALUES LESS THAN (to_days('')),
PARTITION p201101 VALUES LESS THAN (to_days('')),
PARTITION p201102 VALUES LESS THAN (to_days('')),
PARTITION p201103 VALUES LESS THAN (to_days('')),
PARTITION p201104 VALUES LESS THAN (to_days('')),
PARTITION p201105 VALUES LESS THAN (to_days('')),
PARTITION p201106 VALUES LESS THAN (to_days('')),
PARTITION p201107 VALUES LESS THAN (to_days('')),
PARTITION p201108 VALUES LESS THAN (to_days('')),
PARTITION p201109 VALUES LESS THAN (to_days('')),
PARTITION p201110 VALUES LESS THAN (to_days('')),
PARTITION p201111 VALUES LESS THAN (to_days('')),
PARTITION p201112 VALUES LESS THAN (to_days('')),
PARTITION pmax VALUES LESS THAN MAXVALUE );
如果表中已有数据,分区时会自动进行分区存储,不必担心数据丢失或者手动分类数据.
3. 删除表中的指定分区
ALTER TABLE 表名 DROP PARTITION 分区名;
删除分区会导致分区数据丢失,建议先备份.
4. 追加表分区
ALTER TABLE 表名 DROP PARTITION
ALTER TABLE 表名
ADD PARTITION (
PARTITION p201201 VALUES LESS THAN (to_days('')),
PARTITION pmax VALUES LESS THAN MAXVALUE);
需要先删除MAXVALUE分区后增加分区后再重建MAXVALUE分区,删除前需要先备份MAXVALUE分区数据.
5. 查看标分区信息
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
INFORMATION_SCHEMA.partitions
TABLE_SCHEMA = schema()
AND TABLE_NAME='表名';
6. 查看查询语句涉及分区信息
explain partitions
select … from 表名 where …;
该操作只查看查询语句相关的分区信息,不会返回查询结果.
以上是我对MySQL的初体验总结,没啥心得体会,只有一点点成就感,希望和大家分享.
另外分区表尚存在问题:
1,是否可将分区表设置在不同硬盘,innodb可行?
2,是否可根据多条件进行水平分区,类似group by 列1,列2...
3,是否能将分区设置成不同引擎,例如当前使用中的分区为innodb,老的分区使用MyISAM.
浏览: 2311 次
来自: 大连
(window.slotbydup=window.slotbydup || []).push({
id: '4773203',
container: s,
size: '200,200',
display: 'inlay-fix'}