MySQL调优之—— partition分区II

获得分区 信息
MySQL 可以通过如下方式来获取分区表的信息 :
Show create tabe table;      // 表详细结构
show table status;     // 表的各种参数状态
select * from information_schema.partitions ； // 通过数据 字典来查看表的分区信息
explain partitions select * from table;   // 通过此语句 来显示扫描哪些分区，及他们是如何使用的 .


对分区进行修改 ( 修改、合并、重定义分区 )
修改分区
修改部分分区：
由于我们平常使用的数据库 大都是动态 运行的，所以只对某个表分区进行修改就 OK 了。
可以对 range 或 list 表分区进行 add 或 drop ，也可以对 hash 或 key 分区表进行合并或分解。这些动作都在 alter table 语句里进行。
使用 add partition 关键字来对已有分区表进行添加。
Alter
table
orders_range
add
partition
(
Partition p5 values less than(maxvalue)
)

Reorganize partition 关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。
Splitting 即分解一个已有分区：
Alter table orders_range
reorganize partition p0 into

(
partition n0 values less than(5000),
partition n1 values less than(10000)
);

Merge 分区 ：像上面把 p0 分成 n0 和 n1 ，现在在把 2 个合并为一个。
Alter table orders_range reorganize partition n0,n1 into
(
Partition p0 values less than(10000)
);

修改所有的分区 ：在 into 关键字之前或之后都指定多个分区
Alter table orders_range reorganize partition p0,p1,p2,p3,p4,p5 into
(
Partition r0 values less than(25000),
Partition r1 values less than(50000),
Partition r2 values less than(maxvalue)
);

Coalesce 合并分区：
Merge 分区的另一种方法就是 alter table….coalesce partition 语句，你不能对 hash 或 key 分区进行删除
Alter table orders_key coalesce partition1;

Redefine 重定义分区
Alter table orders_range partition by hash(id) partitions 4;

对分区进行删除 ( 删除、删除所有分区 )
Drop 分区：
可以对 range 或 list 类型 的分区通过 drop partition 关键字进行删除
Alter table orders_range drop partition p0;

注意：
1. 对这个分区进行删除时，你会把这个分区的所有数据进行删除，与 delete 语句相等；
2. 在做 alter table..drop partition 时，必须有 drop 权限；
3. 运行这个删除命令 ，它不会返回删除了的行，可以通过 select count() 语句查看。
如果想对多个分区进行删除，可以使用如下命令语句： Alter table orders_range drop partition p1,p2;


删除所有分区
通过如下命令语句删除表中所有分区，最后是一个正规表 .
Alter table orders_range remove partitioning;



当进行分区操作，了解对性能 所产生的影响是非常有帮助的：
1. 创建分区表比无分区的正规表要稍微慢些；
2. 通过 alter table….drop partition 语句进行删除比 delete 语句要快些；
3. 在 range 或 list 分区类型上添加分区 (alter table…add partition 语句 ) 是相当快的，因为没有移动数据到新分区里。
4. 当在一个 key 或 hash 类型的分区上执行 alter table….add partition 语句，要依赖表中已有多少行记录，数据越多，它添加一个新分区的时间就越长。当创建一个表时，使用线性 hash 或线性 key 分区是相当快的。
5. 对成百上千的行记录，进行 alter table …coalesce partition, alter table …reorganize partition, alter table…partition by 操作命令时，是相当慢的。
6. 当使用 add partition 命令时，线性 hash 和线性 key 分区会使 coalesce partition 操作更快， alter table …remove partitioning 比其他都要快，因为 mysql 没有要求哪个文件 来替代行，即使是移动数据。


各种存储引擎 的分区
MySQL 分区可以对所有 MySQL 支持的存储引擎进行分区，比如： myisam, innodb, archive, NDBcluster( 只可以线性 key),falcon ， 不支持分区的引擎： merge, federated, csv, blackhole

注意：所有分区和子分区的表类型要一致；
       索引 维护要依赖表类型；
       锁住某些行，也依赖于存储引擎；
       分区也属于存储引擎的顶层，所以进行 update 和 insert 时，性能不会产生很大的影响。

各种存储引擎使用分区时的限制：
MyISAM 引擎：
Myisam 引擎允许在使用分区时，把表的不同部分存储在不同地方，包括索引目录和数据目录。
下面是一个关于把数据分布到 4 个不同的物理磁盘 上的 myisam 分区。
Create table orders_hash2
(
Id int auto_increment primary key, ……
) engine=myisam

Partition by hash(id)
(
Partition p0 index directory=’/data0/orders/idx’
data directory=’ /data0/orders/data’,
Partition p1 index directory=’/data1/orders/idx’
data directory=’ /data1/orders/data’,
Partition p2 index directory=’/data2/orders/idx’
data directory=’ /data2/orders/data’,
Partition p3 index directory=’/data3/orders/idx’
data directory=’ /data3/orders/data’,
);

注意：上面的具体 4 个分布，在 windows 系统 上目前还不支持。

InnoDB 引擎：
Innodb 的分区管理 与 myisam 引擎的管理是不同的。


分区的限制性
下面讲到的是一些关于 MySQL 分区的限制性约束
常见的限制：
所有的分区必须使用同种引擎；
批量装载很慢；
每个表的最大分区数为 1024 ；
不支持三维数据类型 (GIS);
不能对临时表进行分区；
不可能对日志 表进行分区；

外键和索引方面：
不支持外键；
不支持全文表索引；
不支持 load cache 和 load index into cache ；

子分区方面：
只允许对 range 和 list 类型的分区再进行分区；
子分区的类型只允许是 hash 或 key.

分区表达 式方面：
Range ， list, hash 分区必须是 int 类型；
Key 分区不可以有 text ， blob 类型；
不允许使用 UDF, 存储函数 ，变量，操作符 (|,,^,<<,>>,~) 和一些内置的函数；
在表创建之后 sql mode 不可以改变；
在分区表达式中，不允许子查询 ；
分区表达式中必须包括至少一个列的引用，唯一索引列也可以 ( 包括主键 )

===================================================
Mysql分区表局限性总结

 

Mysql5.1已经发行很久了，本文根据官方文档的翻译和自己的一些测试，对Mysql分区表的局限性做了一些总结，因为个人能力以及测试环境的原因，有可能有错误的地方，还请大家看到能及时指出，当然有兴趣的朋友可以去官方网站 查阅。

本文测试的版本

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中建立分区表的语句中，只能包含下列函数：
ABS()
CEILING() and FLOOR() （在使用这2个函数的建立分区表的前提是使用函数的分区键是INT类型），例如

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 对象限制
下面这些对象在不能出现在分区表达式
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




(




18156629




,




0




,




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。

=================================================

MySQL 分区(Partition)脚本

MySQL 5.1 中新特性分区(partition) shell 脚本。注意 MySQL 只支持小于等于 1024 个分区。

#!/bin/sh

# Set these values
PART=0
ORI=5000
STEP=5000
MAX=3000000

for NUM in `seq -f %f $ORI $STEP $MAX | cut -d. -f1`
do
        echo “PARTITION $PART VALUES LESS THAN ($NUM),” >> /tmp/partition.sql
        part=`expr $PART + 1`
done
echo “PARTITION $PART VALUES LESS THAN MAXVALUE >> /tmp/partition.sql