如无特别说明,以下题目均可用各种技术方案来解答。

Posted by & filed under 技术干货.

这篇文章主要介绍了MySQL的表分区,例如什么是表分区、为什么要对表进行分区、表分区的4种类型详解等,需要的朋友可以参考下

一、什么是表分区
通俗地讲表分区是将一大表,根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。
如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区。当然也可根据其他的条件分区。

二、为什么要对表进行分区
为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率。
分区的一些优点包括:
1)、与单个磁盘或文件系统分区相比,可以存储更多的数据。
2)、对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。通常和分区有关的其他优点包括下面列出的这些。MySQL分区中的这些功能目前还没有实现,但是在我们的优先级列表中,具有高的优先级;我们希望在5.1的生产版本中,能包括这些功能。
3)、一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。
4)、涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;”。通过“并行”,这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。
5)、通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。

三、分区类型

· RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
· LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
· HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
· KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

1.RANGE分区

基于属于一个给定连续区间的列值,把多行分配给分区。这些区间要连续且不能相互重叠,使用VALUES LESS THAN操作符来进行定义。以下是实例。

 

按照这种分区方案,在商店1到5工作的雇员相对应的所有行被保存在分区P0中,商店6到10的雇员保存在P1中,依次类推。注意,每个分区都是按顺序进行定义,从最低到最高。这是PARTITION BY RANGE 语法的要求;在这点上,它类似于C或Java中的“switch … case”语句。
对于包含数据(72, ‘Michael’, ‘Widenius’, ‘1998-06-25′, NULL, 13)的一个新行,可以很容易地确定它将插入到p2分区中,但是如果增加了一个编号为第21的商店,将会发生什么呢?在这种方案下,由于没有规则把store_id大于20的商店包含在内,服务器将不知道把该行保存在何处,将会导致错误。 要避免这种错误,可以通过在CREATE TABLE语句中使用一个“catchall” VALUES LESS THAN子句,该子句提供给所有大于明确指定的最高值的值:

 

MAXVALUE 表示最大的可能的整数值。现在,store_id 列值大于或等于16(定义了的最高值)的所有行都将保存在分区p3中。在将来的某个时候,当商店数已经增长到25, 30, 或更多 ,可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区。
在几乎一样的结构中,你还可以基于雇员的工作代码来分割表,也就是说,基于job_code 列值的连续区间。例如——假定2位数字的工作代码用来表示普通(店内的)工人,三个数字代码表示办公室和支持人员,四个数字代码表示管理层,你可以使用下面的语句创建该分区表:

 

在这个例子中, 店内工人相关的所有行将保存在分区p0中,办公室和支持人员相关的所有行保存在分区p1中,管理层相关的所有行保存在分区p2中。
在VALUES LESS THAN 子句中使用一个表达式也是可能的。这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (<)比较的一部分;因此,表达式的值不能为NULL 。由于这个原因,雇员表的hired, separated, job_code,和store_id列已经被定义为非空(NOT NULL)。
除了可以根据商店编号分割表数据外,你还可以使用一个基于两个DATE (日期)中的一个的表达式来分割表数据。例如,假定你想基于每个雇员离开公司的年份来分割表,也就是说,YEAR(separated)的值。实现这种分区模式的CREATE TABLE 语句的一个例子如下所示:

 

在这个方案中,在1991年前雇佣的所有雇员的记录保存在分区p0中,1991年到1995年期间雇佣的所有雇员的记录保存在分区p1中, 1996年到2000年期间雇佣的所有雇员的记录保存在分区p2中,2000年后雇佣的所有工人的信息保存在p3中。
RANGE分区在如下场合特别有用:
1)、 当需要删除一个分区上的“旧的”数据时,只删除分区即可。如果你使用上面最近的那个例子给出的分区方案,你只需简单地使用 “ALTER TABLE employees DROP PARTITION p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如“DELETE FROM employees WHERE YEAR (separated) <= 1990;”这样的一个DELETE查询要有效得多。
2)、想要使用一个包含有日期或时间值,或包含有从一些其他级数开始增长的值的列。
3)、经常运行直接依赖于用于分割表的列的查询。例如,当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。
注释:这种优化还没有在MySQL 5.1源程序中启用,但是,有关工作正在进行中。

2.LIST分区

类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
LIST分区通过使用“PARTITION BY LIST(expr)”来实现,其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUES IN (value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。
注释:在MySQL 5.1中,当使用LIST分区时,有可能只能匹配整数列表。

 

 

假定有20个音像店,分布在4个有经销权的地区,如下表所示:
====================
地区      商店ID 号
————————————
北区      3, 5, 6, 9, 17
东区      1, 2, 10, 11, 19, 20
西区      4, 12, 13, 14, 18
中心区   7, 8, 15, 16
====================
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表,可以使用下面的“CREATE TABLE”语句:

 

这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如,假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录(行)可以使用查询“ALTER TABLE employees DROP PARTITION pWest;”来进行删除,它与具有同样作用的DELETE (删除)查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18);”比起来,要有效得多。
【要点】:如果试图插入列值(或分区表达式的返回值)不在分区值列表中的一行时,那么“INSERT”查询将失败并报错。例如,假定LIST分区的采用上面的方案,下面的SQL将失败:

 

这是因为“store_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是,LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

LIST分区除了能和RANGE分区结合起来生成一个复合的子分区,与HASH和KEY分区结合起来生成复合的子分区也是可能的。

3.HASH分区

基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
要使用HASH分区来分割一个表,要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num 是一个非负的整数,它表示表将要被分割成分区的数量。

 

如果没有包括一个PARTITIONS子句,那么分区的数量将默认为1。 例外: 对于NDB Cluster(簇)表,默认的分区数量将与簇数据节点的数量相同,
这种修正可能是考虑任何MAX_ROWS 设置,以便确保所有的行都能合适地插入到分区中。
1.)LINER HASH
MySQL还支持线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规 哈希使用的是求哈希函数值的模数。
线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY” 子句中添加“LINEAR”关键字。

 

假设一个表达式expr, 当使用线性哈希功能时,记录将要保存到的分区是num 个分区中的分区N,其中N是根据下面的算法得到:
1.    找到下一个大于num.的、2的幂,我们把这个值称为V ,它可以通过下面的公式得到:
2.    V = POWER(2, CEILING(LOG(2, num)))
(例如,假定num是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4,则V = POWER(2,4), 即等于16)。
3.    设置 N = F(column_list) & (V – 1).
4.    当 N >= num:
·         设置 V = CEIL(V / 2)
·         设置 N = N & (V – 1)
例如,假设表t1,使用线性哈希分区且有4个分区,是通过下面的语句创建的:
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
现在假设要插入两行记录到表t1中,其中一条记录col3列值为’2003-04-14’,另一条记录col3列值为’1998-10-19’。第一条记录将要保存到的分区确定如下:

 

(3 >= 6 为假(FALSE): 记录将被保存到#3号分区中)
第二条记录将要保存到的分区序号计算如下:

 

(2 >= 4 为假(FALSE): 记录将被保存到#2分区中)
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷,有利于处理含有极其大量(1000吉)数据的表。它的缺点在于,与使用
常规HASH分区得到的数据分布相比,各个分区间数据的分布不大可能均衡。

4.KSY分区

类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

 

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法。

Posted by & filed under 每日一题.

【四脚猫】每日一题(2015年10月27日):说一张表里有1千万条数据,有一个字段status有两个值(1待审核、2审核通过),然后呢有两个列表即待审核列表与审核通过的列表,那么如何优化查询SQL使其列表的查询速度达到最快?

如果给status字段加索引,但由于它的值重复性太多,即使加了索引效果也不明显。

 

参考解释:

本题并没有完全标准的答案,旨在考察数据库全面的知识水平。

针对如此大的数据量,问题的关键在于你如何去获取数据。所以这里的优化重点在于业务方,尽量的压缩查询返回数据,只要查询的数据量小(如果业务不复杂,数据库不身是不适合处理复杂计算的),查询性能才能更高,至于分表/分区,个人感觉还是要看业务,综合来看,总不能说审核通过的记录从这个表在移动到另外一个表的,另外如果有需要获取全部的需求,那岂不是要连表查询,性能更低。 至于如何让查询的数据量小,有很多做法,比如结合缓存(redis-nosql),保证mysql只处理我们需要的数据或者只是保存数据,往往业务的性能瓶颈并不在数据库,所以不要把压力放到数据库这里。

 

MySQL表分区的介绍看:http://blog.sijiaomao.com/?p=1592

周末休息,不做题 :)

Posted by & filed under 每日一题.

【四脚猫】每日一题(2016年10月24日):  凯撒密码(caeser)是罗马扩张时期朱利斯•凯撒(Julius Caesar)创造的,用于加密通过信使传递的作战命令。它将字母表中的字母移动一定位置而实现加密。原理图如下:

20110928102006760

 

 

 

 

 

 

这个算法对于古代的人来说,加密解密都是要手工对照去对比,比较繁琐,现在有了计算机,尤其是有了史上最好的PHP语言,应该能轻松应对吧 :) ,试试看,用PHP来实现凯撒密码的加密解密。

 

以下是asun 提供的实现方法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<div class="ds-comment-header"></div>
function caeser($str,$type='ENCODE'){
$str = strtoupper($str);
//先创建一套密码表
$table = array();
$m = 0;
for($i=ord('A');$i&lt;=ord('Z');$i++){
$n = $i+3;
if($n&gt;ord('Z')){
$n = ord('A')+$m;
$m++;
}
$table[chr($i)] = chr($n);
}
//加密
if($type == 'ENCODE'){
$encrypt = '';
for($i=0;$i&lt;strlen($str);$i++){
$encrypt .= !isset($table[$str{$i}]) ? $str{$i} : $table[$str{$i}]; //其他字符原样输出
}
return $encrypt;
}else{ //解密
$decrypt = '';
for($i=0;$i&lt;strlen($str);$i++){
$decrypt .= !isset($table[$str{$i}]) ? $str{$i} : array_search($str{$i}, $table); //其他字符原样输出
}
return $decrypt;
}
}

echo caeser('hello world!');exit;// KHOOR ZRUOG!
echo caeser('KHOOR ZRUOG!','decode');exit;//HELLO WORLD!

 

这个解法中,密钥是固定的,可以继续优化下,让密钥可以变化 :)

Posted by & filed under 每日一题.

【四脚猫】每日一题(2016年10月25日):  用PHP的环视为数值添加金额所需的逗号:5345678986变为5,345,678,986.  。

 

光阴的故事采用顺序肯定环视实现了此功能:

1
echo preg_replace( '/\B(?=(\d{3})+$)/' , ',', '5345678986' );

 

动心的五月天采用逆序肯定环视实现了此功能:

1
echo preg_replace('/(?&lt;=\d{3})(\d{3})/',',$1',preg_replace('/^(\d{1,3})((\d{3})+)$/','$1,$2',5345678986));

 

青菜虫另辟蹊跷,给出了更容易掌握的PHP内置函数实现方法:

1
number_format($number, 0, '.', ',')

 

环视只进行子表达式的匹配,不占有字符,匹配到的内容不保存到最终的匹配结果,是零宽度的。环视匹配的最终结果就是一个位置。
环视的作用相当于对所在位置加了一个附加条件,只有满足这个条件,环视子表达式才能匹配成功。
环视按照方向划分有顺序和逆序两种,按照是否匹配有肯定和否定两种,组合起来就有四种环视。顺序环视相当于在当前位置右侧附加一个条件,而逆序环视相当于在当前位置左侧附加一个条件。
表达式   说明
(?<=Expression)
逆序肯定环视,表示所在位置左侧能够匹配Expression
(?<!Expression)
逆序否定环视,表示所在位置左侧不能匹配Expression
(?=Expression)
顺序肯定环视,表示所在位置右侧能够匹配Expression
(?!Expression)
顺序否定环视,表示所在位置右侧不能匹配Expression
对于环视的叫法,有的文档里叫预搜索,有的叫什么什么断言的,这里使用了更多人容易接受的《精通正则表达式》中“环视”的叫法,其实叫什么无所谓,只要知道是什么作用就是了,就这么几个语法规则, 还是很容易记的
环视匹配原理

环视是正则中的一个难点,对于环视的理解,可以从应用和原理两个角度理解,如果想理解得更清晰、深入一些,还是从原理的角度理解好一些,正则匹配基本原理参考 NFA引擎匹配原理。
上面提到环视相当于对“所在位置”附加了一个条件,环视的难点在于找到这个“位置”,这一点解决了,环视也就没什么秘密可言了。
顺序环视匹配过程

对于顺序肯定环视(?=Expression)来说,当子表达式Expression匹配成功时,(?=Expression)匹配成功,并报告(?=Expression)匹配当前位置成功。
对于顺序否定环视(?!Expression)来说,当子表达式Expression匹配成功时,(?!Expression)匹配失败;当子表达式Expression匹配失败时,(?!Expression)匹配成功,并报告(?!Expression)匹配当前位置成功;
顺序肯定环视的例子已在NFA引擎匹配原理中讲解过了,这里再讲解一下顺序否定环视。

源字符串:aa<p>one</p>bb<div>two</div>cc
正则表达式:<(?!/?p\b)[^>]+>
这个正则的意义就是匹配除<p…>或</p>之外的其余标签。
匹配过程:
首先由字符“<”取得控制权,从位置0开始匹配,由于“<”匹配“a”失败,在位置0处整个表达式匹配失败,第一次迭代匹配失败,正则引擎向前传动,由位置1处开始尝试第二次迭代匹配。
重复以上过程,直到位置2,“<”匹配“<”成功,控制权交给“(?!/?p\b)”;“(?!/?p\b)”子表达式取得控制权后,进行内部子表达式的匹配。首先由“/?”取得控制权,尝试匹配“p”失败,进行回溯,不匹配,控制权交给“p”;由“p”来尝试匹配“p”,匹配成功,控制权交给“\b”;由“\b”来尝试匹配位置4,匹配成功。此时子表达式匹配完成,“/?p\b”匹配成功,那么环视表达式“(?!/?p\b)”就匹配失败。在位置2处整个表达式匹配失败,新一轮迭代匹配失败,正则引擎向前传动,由位置3处开始尝试下一轮迭代匹配。
在位置8处也会遇到一轮“/?p\b”匹配“/p”成功,而导致环视表达式“(?!/?p\b)”匹配失败,从而导致整个表达式匹配失败的过程。
重复以上过程,直到位置14,“<”匹配“<”成功,控制权交给“(?!/?p\b)”;“/?”尝试匹配“d”失败,进行回溯,不匹配,控制权交给“p”;由“p”来尝试匹配“d”,匹配失败,已经没有备选状态可供回溯,匹配失败。此时子表达式匹配完成,“/?p\b”匹配失败,那么环视表达式“(?!/?p\b)”就匹配成功。匹配的结果是位置15,然后控制权交给“[^>]+”;由“[^>]+”从位置15进行尝试匹配,可以成功匹配到“div”,控制权交给“>”;由“>”来匹配“>”。
此时正则表达式匹配完成,报告匹配成功。匹配结果为“<div>”,开始位置为14,结束位置为19。其中“<”匹配“<”,“(?!/?p\b)”匹配位置15,“[^>]+”匹配字符串“div”,“>”匹配“>”。
对于逆序肯定环视(?<=Expression)来说,当子表达式Expression匹配成功时,(?<=Expression)匹配成功,并报告(?<=Expression)匹配当前位置成功。
对于逆序否定环视(?<!Expression)来说,当子表达式Expression匹配成功时,(?<!Expression)匹配失败;当子表达式Expression匹配失败时,(?<!Expression)匹配成功,并报告(?<!Expression)匹配当前位置成功;
顺序环视相当于在当前位置右侧附加一个条件,所以它的匹配尝试是从当前位置开始的,然后向右尝试匹配,直到某一位置使得匹配成功或失败为止。而逆序环视的特殊处在于,它相当于在当前位置左侧附加一个条件,所以它不是在当前位置开始尝试匹配的,而是从当前位置左侧某一位置开始,匹配到当前位置为止,报告匹配成功或失败。
顺序环视尝试匹配的起点是确定的,就是当前位置,而匹配的终点是不确定的。逆序环视匹配的起点是不确定的,是当前位置左侧某一位置,而匹配的终点是确定的,就是当前位置。
所以顺序环视相对是简单的,而逆序环视相对是复杂的。这也就是为什么大多数语言和工具都提供了对顺序环视的支持,而只有少数语言提供了对逆序环视支持的原因。
JavaScript中只支持顺序环视,不支持逆序环视。
Java中虽然顺序环视和逆序环视都支持,但是逆序环视只支持长度确定的表达式,逆序环视中量词只支持“?”,不支持其它长度不定的量词。长度确定时,引擎可以向左查找固定长度的位置作为起点开始尝试匹配,而如果长度不确定时,就要从当前位置向左逐个位置开始尝试匹配,不成功则回溯,再向左侧位置进行尝试匹配,然后重复以上过程,直到匹配成功,或是尝试到位置0处以后,报告匹配失败,处理的复杂度是显而易见的。
 

Posted by & filed under 每日一题.

【四脚猫】每日一题(2016年10月24日): 给定一个字符串,判断它是否是一个回文? 只考虑字母字符的情况,并且忽略大小写。

所谓回文字符串,就是一个字符串,从左到右读和从右到左读是完全一样的,比如”level” 、 “aaabbaaa” 。

 

惊鲵第一个给出了算法描述:先获取字符串长度,然后除2,获取字符串后一半,然后反转过来看是否等于前面,等于就是一个回文。

爪的速度果然快,光速提交了另一种算法的code!

1
2
3
4
5
function is_palindrome($str)
{
$str = strtolower($str);
return $str === strrev($str);
}

asun依据题意提炼出数学模型:问题的关键在于  $arr[$n] == $arr[$len-1-$n],并且给出了解法。

1
2
3
4
5
6
7
8
9
10
11
function is_hui_wenzi($str){
$str = strtolower($str);
$len = strlen($str);
$n = 0;
for($i=0;$i&lt;$len;$i++){
if($str{$i} == $str{$len-1-$n}){
$n++;
}
}
return $len == $n;
}

 

blackpuppy是个非常严谨的工程师,不仅仅实现了算法,还加上了测试代码!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
function isReverse($string)
{
$tmp = strtolower($string);
return $tmp === strrev($tmp);
}

function test()
{
$strings = ['level', 'aaabbaaa', 'abcd', 'ABCdcba'];
foreach ($strings as $string) {
$result = isReverse($string);
echo '"' . $string . '"' . ($result ? '是' : '不是') . '回文' . PHP_EOL;
}
}

test();

 

没想到每日一题停了这么久,还是这么多朋友积极参与,非常感谢大家。

技术水平从来不是一朝一夕就能登峰造极的,所有牛逼的人物都是一步步走出来的。

愿我们一起努力,每天提高一点点,共同成长为更牛逼的工程师!

 

欢迎各位攻城狮,各位大牛给每日一题投稿,大家可以把自己碰到的有趣问题,工作中碰到的难题等…发送到

稿件邮箱:2313427189@qq.com

或者直接在群里 @2313427189 也行。

 

Posted by & filed under 每日一题.

【四脚猫】每日一题(2015年05月15日): 1张表里有1千万条数据,有一个字段status有两个值(1待审核、2审核通过),然后呢有两个列表即待审核列表与审核通过的列表,那么如何优化查询SQL使其列表的查询速度达到最快?

 

参考答案:

我自己做的审批系统内是这样处理的,所有发生的事件都会把(id,event_id,status,desc,url,time) 这些东西,单独弄个内存表(cache),status符合一定条件或者超时,就从这个表里删除(按时间顺序归档)了,所以不可能出现那么大的表。

Posted by & filed under 每日一题.

【四脚猫】每日一题(2015年05月14日): 以下js代码能正常执行吗,如果不正确,正确操作是怎样的?

 

Posted by & filed under 每日一题.

【四脚猫】每日一题(2015年05月13日): 如何用curl伪造IP地址去访问网站?

参考答案: