...常来说,php支持断点续传,主要依靠HTTP协议中 header HTTP_RANGE实现。 HTTP断点续传原理: Http头 Range、Content-Range() HTTP头中一般断点下载时才用到Range和Content-Range实体头, Range用户请求头中,指定...
bitsCN.com
1.查看Mysql版本是否支持分区
SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
如果VALUE 为YES 则支持分区,
2.测试那种存储引擎支持分区INOODB引擎
mysql> Create table engine1(id int) engine=innodb partition by range(id)(partition po values less than(10));
Query OK, 0 rows affected (0.01 sec)
MRG_MYISAM引擎
mysql> Create table engine2(id int) engine=MRG_MYISAM partition by range(id)(partition po values less than(10)); ERROR 1572 (HY000): Engine cannot be used in partitioned tables
blackhole引擎
mysql> Create table engine3(id int) engine=blackhole partition by range(id)(partition po values less than(10)); Query OK, 0 rows affected (0.01 sec)
CSV引擎
mysql> Create table engine4(id int) engine=csv partition by range(id)(partition po values less than(10));
ERROR 1572 (HY000): Engine cannot be used in partitioned tables
Memory引擎
mysql> Create table engine5(id int) engine=memory partition by range(id)(partition po values less than(10));
Query OK, 0 rows affected (0.01 sec)
federated引擎
mysql> Create table engine6(id int) engine=federated partition by range(id)(partition po values less than(10));
Query OK, 0 rows affected (0.01 sec)
archive引擎
mysql> Create table engine7(id int) engine=archive partition by range(id)(partition po values less than(10));
Query OK, 0 rows affected (0.01 sec)
myisam 引擎
mysql> Create table engine8(id int) engine=myisam partition by range(id)(partition po values less than(10));
Query OK, 0 rows affected (0.01 sec)
3.Mysql分区表,分区引擎测试
表分区的存储引擎相同mysql> Create table pengine1(id int) engine=myisam partition by range(id)(partition po values less than(10) engine=myisam, partition p1 values less than(20) engine=myisam); Query OK, 0 rows affected (0.05 sec)
表分区的存储引擎不同mysql> Create table pengine2(id int) engine=myisam partition by range(id)(partition po values less than(10) engine=myisam, partition p1 values less than(20) engine=innodb); ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL
同一个分区表中的所有分区必须使用同一个存储引擎,并且存储引擎要和主表的保持一致。
4.分区类型
Range:基于一个连续区间的列值,把多行分配给分区;
LIST:列值匹配一个离散集合; Hash:基于用户定义的表达式的返回值选择分区,表达式对要插入表中的列值进行计算。这个函数可以包含SQL中有效的,产生非负整数值的任何表达式。
KEY:类似于HASH分区,区别在于KEY 分区的表达式可以是一列或多列,且MYSQL提供自身的HASH函数。
5.RANGE分区MAXVALUE值 及加分区测试;
创建表 PRANGE,最后分区一个分区值是MAXVALUEmysql> Create table prange(id int) engine=myisam partition by range(id)(partition po values less than(10), partition p1 values less than(20),partition p2 values less than maxvalue);Query OK, 0 rows affected (0.06 sec)
加分区mysql> alter table prange add partition (partition p3 values less than (20));ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
在分区P0前面加个分区mysql> alter table prange add partition (partition p3 values less than (1));ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
说明有MAXVALUE值后,直接加分区是不可行的;
创建表PRANGE1,无MAXVALUE值mysql> Create table prange1(id int) engine=myisam partition by range(id)(partition po values less than(10), partition p1 values less than(20),partition p2 values less than (30)); Query OK, 0 rows affected (0.08 sec)
从最大值后加个分区mysql> alter table prange1 add partition (partition p3 values less than (40));Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
从分区的最小值前加个分区mysql> alter table prange1 add partition (partition p43 values less than (1));ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
由此可见,RANGE 的分区方式在加分区的时候,只能从最大值后面加,而最大值前面不可以添加;
6. 用时间做分区测试create table ptime2(id int,createdate datetime) engine=myisam partition by range (to_days(createdate))
(partition po values less than (20100801),partition p1 values less than (20100901));
Query OK, 0 rows affected (0.01 sec)
mysql> create table ptime3(id int,createdate datetime) engine=myisam partition by range (createdate)
(partition po values less than (20100801),partition p1 values less than (20100901));
ERROR 1491 (HY000): The PARTITION function returns the wrong type
直接使用时间列不可以,RANGE分区函数返回的列需要是整型。mysql> create table ptime6(id int,createdate datetime) engine=myisam partition by range (year(createdate))
(partition po values less than (2010),partition p1 values less than (2011));
Query OK, 0 rows affected (0.01 sec)
使用年函数也可以分区。
7.Mysql可用的分区函数DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
DATEDIFF()
EXTRACT()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
WEEKDAY()
YEAR()
YEARWEEK() 等
当然,还有FLOOR(),CEILING() 等,前提是使用这两个分区函数的分区健必须是整型。
要小心使用其中的一些函数,避免犯逻辑性的错误,引起全表扫描。
比如:create table ptime11(id int,createdate datetime) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31));
mysql> insert into ptime11 values (1,'2010-06-17');
mysql> explain partitions select count(1) from ptime11 where createdate>'2010-08-17'/G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ptime11
partitions: po,p1
type: ALLpossible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
1 row in set (0.00 sec)
8.主键及约束测试
分区健不包含在主键内mysql> create table pprimary(id int,createdate datetime,primary key(id)) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31));
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
分区健包含在主键内mysql> create table pprimary1(id int,createdate datetime,primary key(id,createdate)) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31));Query OK, 0 rows affected (0.05 sec)
说明分区健必须包含在主键里面。mysql> create table pprimary2(id int,createdate datetime,uid char(10),primary key(id,createdate),unique key(uid)) engine=myisam partition by range(to_days(createdate))(partition p0 values less than (20100801),partition p1 values less than (20100901));ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
说明在表上建约束索引会有问题,必须把约束索引列包含在分区健内。mysql> create table pprimary3(id int,createdate datetime,uid char(10),primary key(id,createdate),unique key(createdate)) engine=myisam partition by range(to_days(createdate))(partition p0 values less than (20100801),partition p1 values less than (20100901));Query OK, 0 rows affected (0.00 sec)
虽然在表上可以加约束索引,但是只有包含在分区健内,这种情况在实际应用过程中会遇到问题,这个问题点在以后的MYSQL 版本中也许会改进。
9.子分区测试
只有RANGE和LIST分区才能有子分区,每个分区的子分区数量必须相同,mysql> create table pprimary7(id int,createdate datetime,uid char(10),primary key(id,createdate)) engine=myisam partition by range(to_days(createdate)) subpartition by hash(to_days(createdate))(partition p0 values less than (20100801) ( subpartition so,subpartition s1) ,partition p1 values less than (20100901) (subpartition s0,subpartition s1));
ERROR 1517 (HY000): Duplicate partition name s1
提示了重复的分区名称错误,这和MYSQL5.1帮助文档中的说明有出入,不知道是不是这个问题在某个小版本中修改过。
10.MYSQL分区健NULL值测试;
MYSQL将NULL值视为0.自动插入最小的分区中。
11.MYSQL分区管理测试mysql> alter table pprimary4 truncate partition p1;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'truncate partition p1' at line 1
5.1版本中还不支持这个语法,5.5中已经支持,很好的一个命令;
ALTER TABLE reorganize 可以重新组织分区。
...常来说,php支持断点续传,主要依靠HTTP协议中 header HTTP_RANGE实现。 HTTP断点续传原理: Http头 Range、Content-Range() HTTP头中一般断点下载时才用到Range和Content-Range实体头, Range用户请求头中,指定...
一、unicode-range是什么?
一看名称,很多小伙伴可能认为是个什么生僻的东西,实际上,这玩意只是名字怪怪的,功能关键时刻还是很管用的。
unicode-range是一个CSS属性,一般和@font-face规则一起使...
php 支持断点续传,主要依靠HTTP协议中 header HTTP_RANGE实现。HTTP断点续传原理Http头 Range、Content-Range()HTTP头中一般断点下载时才用到Range和Content-Range实体头,Range用户请求头中,指定第一...
...e cellspacing="0" cellpadding="0">
本文介绍下,在php中用range函数,创建包含指定范围单元的数组的例子,有需要的朋友参考下。之前,我们介绍过一篇 php快速创建数组的方法,其中就用到了php range()函数。
本文介绍下,...
...常来说,php支持断点续传,主要依靠HTTP协议中 header HTTP_RANGE实现。
HTTP断点续传原理:
Http头 Range、Content-Range()
HTTP头中一般断点下载时才用到Range和Content-Range实体头,
Range用户请求头中,指定第...
...开始介绍index merge/ROR优化之前,打算先介绍MySQL是如何对range/ref做成本评估的。MySQL是基于成本(cost)模型选择执行计划,在多个range,全表扫描,ref之间会选择成本最小的作为最终的执行计划。仍然强烈建议先阅读登博的slide:《...
本篇文章给大家带来的内容是介绍css3的:out-of-range和:in-range伪类有什么用?(代码示例),让大家了解:out-of-range伪类和:in-range伪类的作用和使用方法。有一定的参考价值,有需要的朋友可以参考一下,希望对你们有所帮助。
... 参数只接受字符串类型的值
private static $range = 'sys';
//7个方法
/**
* 设定配置参数的作用域&nbs
...;
header ( "Content-Transfer-Encoding: binary" );
header ( "Accept-Ranges: bytes" );
$range = 0;
// 如果有$_SERVER['HTTP_RANGE']参数
php 支持断点续传,主要依靠HTTP协议中 header HTTP_RANGE实现。HTTP断点续传原理Http头 Range、Content-Range()HTTP头中一般断点下载时才用到Range和Content-Range实体头,Range用户请求头中,指定第一...