Understanding Indexing

Understanding Indexing

Without Needing to Understand Data Structures

MySQL UC 2011 – April 12, 2011

Zardosht Kasheff

什么是表?

(key,value)对集合的词典

  1. 确保你可以  修改 这个词典(插入、删除、修改)和  查询 (点查询、范围查询)词典
  2. B-Tree和Fractal Tree是两个词典的例子
  3. 哈希则不是(不支持范围查询)

例子:

CREATE TABLE foo (a INT, b INT, c INT, PRIMARY KEY(a));

然后我们插入一批数据

a b c
100 5 45
101 92 2
156 56 45
165 6 2
198 202 56
206 23 252
256 56 2
412 43 45

一个key定义了词典的排序规则

1、对于数据结构和存储引擎我们会认为,在排序上进行范围查询是  快速 的

2、在其它顺序上的范围查询会进行表的扫描,这个操作是  很慢 的

3、点查询需要检索一个特定的值也是   的

一个点查询是快速的,但是读取一批行用这种方法会比使用按顺序的范围查询慢2个数量级

词典 T 上的索引 I 也是一个词典

1、同样我们需要定义一个(key,value)对

2、索引上的key是主词典上的列的子集

3、索引I的值是T的主KEY

还有其它的方法去定义这个值,但我们还是坚持使用T的主KEY来定义这个值

例子:

ALTER TABLE foo ADD KEY(b);

然后我们得到

Primary
a b c
100 5 45
101 92 2
156 56 45
165 6 2
198 202 56
206 23 252
256 56 2
412 43 45
Key(b)
b a
5 100
6 165
23 206
43 412
56 156
56 256
92 101
202 198

问题:COUNT( * ) WHERE a<120;

100 5
101 92
=> 2

问题:COUNT( * ) WHERE b>50;

56 156
56 256
92 101
202 198
=> 4

问题:SUM( c ) WHERE b >50;

56 156
56 256
92 101
202 198
=>
156 56 45
256 56 2
101 92 2
198 202 56
=> 105

索引的好处?

1、索引使得查询变得快速

索引会提升部分查询请求的速度

2、需要在心里根据查询设计索引

选出最重要的查询给它们设计索引

考虑索引本身的代价

设计一个好的索引有3个规则可以参考

1、避免任何数据结构的细节

B树和分形树对于计算机科学家来说是有趣和好玩的,但这三个规则同样适用于其它数据结构

所有我们需要考虑的是范围查询是快速的(对每行)而点查询则慢的多(对行)

2、世界上没有绝对的规则

索引像是一个数据问题

规则有帮助,但每个方案都有自己的问题,需要分析解决问题的方法

3、也就是说规则有很大的帮助

三个规则

1、查询较少的数据

少的带宽,少的处理…

2、减少点查询

数据访问成本是不一样的

顺序访问数据比无序访问  **快的多**

3、避免排序

GROUP BY和ORDER BY查询需要后期的检索工作

索引在这种查询中可以帮助获得RowID

我们来分别看下这三种规则

规则1:查询较少的数据

Rule1:慢查询的例子

表:(100万行数据,没有索引)

CREATE TABLE foo (a INT, b INT, c INT);

查询(匹配1000行数据)

SELECT SUM(c) FROM foo WHERE b=10 and a&lt;150;

查询计划:

行b=10 AND a&lt;150可以在表的任何地方

没有索引的帮助,整个表都会被扫描

执行速度慢:

检索100万行数据仅仅查询1000行数据

Rule1:如何去添加一个索引

1、我们应该怎么去做?

减少检索到的数据

分析远少于100万的数据行

2、怎样做(对于一个简单的查询)?

设计索引时着眼于WHERE子句

    由查询关注那些行来决定

    其它行的数据对于这个查询来说并不重要

对于查询 SELECT SUM( c ) FROM foo WHERE b=10 and a<150;

Rule1:那个索引?

选择1:Key(a)

选择2:Key(b)

那个更好?由select来决定:

如果WHERE a&lt;150的数据行更少,key(a)更好

如果WHERE b=10的数据更少,key(b)更好

选择3:key(a) AND key(b),然后MERGE我们稍后会进行讨论

Rule1:选择最好的索引

key(a)与key(b)都不是最佳的

考虑:

WHERE a&lt;150有20万行数据

WHERE b=10有10万行数据

WHERE b=10 AND a&lt;150有1000行数据

然后key(a)和key(b)都会检查很多的数据

为了获得更好的性能,索引必须尝试尽量优化WHERE条件

我们需要复合索引

复合索引可以减少数据检索

WHERE条件:b=5 AND a<150

选择1:key(a, b)

选择2:key(b, a)

问题又来了那种选择更好?

Key(b, a)!

索引规则:

当创建一个复合索引,需要对每个列进行检查,条件b是相等判断,但在a上不是。

问题:WHERE b=5 and a>150;

a b c
100 5 45
101 6 2
156 5 45
165 6 2
198 6 56
206 5 252
256 5 2
412 6 45
b,a a
5,100 100
5,156 156
5,206 206
5,256 256
6,101 101
6,165 165
6,198 198
6,412 412

复合索引:没有平等的条件

如果WHERE条件是这样的:

WHERE a>100 AND a<200 AND b>100;

那个更好?

key(a),key(b),key(a,b),key(b,a)?

索引规则:

只要复合索引不被用于相等查询,复合索引的其它部分不会减少数据检索量

key(a,b)不再比key(a)好

key(b,a)不再比key(b)好

问题:WHERE b>=5 AND a>150;

a b c
100 5 45
101 6 2
156 5 45
165 6 2
198 6 56
206 5 252
256 5 2
412 6 45
b,a a
5,100 100
5,156 156
5,206 206
5,256 256
6,101 101
6,165 165
6,198 198
6,412 412
=>
5,156 156
5,206 206
5,256 256
6,101 101
6,165 165
6,198 198
6,412 412

复合索引:另一个例子

WHERE条件:b=5 AND c=100

key(b,a,c)和key(b)一样好
因为a没有在条件中使用,所以在索引中包含c并不会有帮助,key(b,c,a)会更好
a b c
100 5 100
101 6 200
156 5 200
165 6 100
198 6 100
206 5 200
256 5 100
412 6 100
b,a,c a
5,100,100 100
5,156,200 156
5,206,200 206
5,256,100 256
6,101,200 101
6,165,100 165
6,198,100 198
6,412,100 412
=>
5,100,100 100
5,156,200 156
5,206,200 206
5,256,100 256

规则1:总结

根据查询条件设计复合索引

把相等条件的查询列放在复合索引的开始位置

保证第一个非相等条件的列在索引中越有选择性越好

如果复合索引的第一个列没有被相等条件来使用,或者没有在条件中使用,复合索引的其它列对于减少数据的检索没有帮助

    是否就表明它们是无用的呢?

    它们在规则2中可能有用

规则2:避免点查询

表:

CREATE TABLE foo (a INT,b INT,c INT,PRIMARY KEY(a), KEY(b);

查询:

SELECT SUM( c ) FROM foo WHERE b>50;

查询计划:使用key(b)

因为随机的点查询的原因,对每个行都进行检索的代价很大

问题:SUM( c ) WHERE b>50;

a b c
100 5 45
101 92 2
156 56 45
165 6 2
198 202 56
206 23 252
256 56 2
412 43 45
b a
5 100
6 165
23 206
43 412
56 156
56 256
92 101
202 198
56 156
56 256
92 101
202 198
=>
156 56 45
256 56 2
101 92 2
198 202 56
=> 105

还是这张表,但查询计划不同了

SUM(c) WEHRE b>50;

查询计划:扫描主表

每行的检索成本低

但是需要检索很多行

问题:SUM( c ) WEHRE b>50;

a b c
100 5 45
101 92 2
156 56 45
165 6 2
198 202 56
206 23 252
256 56 2
412 43 45
=> 105

如果我们添加了另一个索引会怎么样?

如果添加key(b, c)呢?

由于我们在b上有索引,我们只检索我们需要的行

由于索引包含C的信息,我们不再需要再去检索主表了。  **没有点查询了**

覆盖索引:索引覆盖一个查询,如果这个索引包含足够的信息来回答这个查询。

例子:

问:SELECT SUM( c ) FROM foo WHERE b<100;

问:SELECT SUM( b ) FROM foo WHERE b<100;

索引:

key(b, c): 对第一个查询是覆盖索引

key(b, d):对第二个查询是覆盖索引

key(b, c, d):对每个索引都是覆盖索引

如何去构建一个覆盖索引

把检索的每个列都包含进去,并不仅仅是查询条件

问:SELECT c,d FROM foo WHERE a=10 AND b=100;

错误:ADD INDEX(a, b)

并不是覆盖索引。仍然需要点查询去检索c和d的值

正确:ADD INDEX(a, b, c, d)

包含所有相关的列

按照规则1规定把 a 和 b 放在索引开始位置

如果主键匹配WHERE条件呢?

问题:SELECT sum(c) FROM foo WHERE b>100 AND b<200;

SCHEMA:CREATE table foo (a INT, b INT, c INT, ai INT AUTO_INCREMENT, PRIMARY key(b, ai));

查询在主词典上做了范围查询

只有一个词典会按顺序访问到

这个查询很快

主键覆盖所有查询

如果排序规则匹配查询条件,问题得到解决

什么是聚簇索引

如果主键不匹配查询条件呢?

理想的情况下,必须确保辅助索引包含所有列

存储引擎不会让你去这样做

有一个例外。。。。TokuDB可以

TokuDB允许你定义任何聚簇索引

一个聚簇索引包含所有的查询,就像主键做的一样

聚簇索引的实践

问:SELECT SUM(c) FROM foo WHERE b<100;

问:SELECT SUM(b) FROM foo WEHRE b>200;

问:SELECT c,e FROM foo WEHRE b=1000;

索引:

key(b, c):第一个查询

key(b, d):第二个查询

key(b, c, e):第一个和第三个查询

key(b, c, d, e):所有的三个查询

索引需要大量的查询分析

考虑那个会涵盖所有的查询:

聚簇索引 b

聚簇索引可以让你更加关注于查询条件

它们减少了点查询并且使查询更加快

聚簇索引更多的信息:索引合并

例子:

CREATE TABLE foo (a INT, b INT, c INT);

SELECT SUM(c) FROM foo WHERE b=10 AND a<150;

假设:

a<150有20万行数据

b=10有10万行数据

b=10 AND a<150有1000行数据

如果我们使用key(a)和key(b)然后把结果集合合并会怎样?

合并计划:

查询20万行数据从key(a)中,where a<150

查询10万行数据从key(b)中,where b=10

合并结果集合,然后找到查询标识的1000行数据

执行1000行数据的点查询去得到c

这比没有索引好一点

和没有索引相比,减少了扫描行的数量

和没有合并相比,减少了点查询的数量

那么聚簇索引会对合并有帮助么?

考虑key(a)是个聚簇索引

查询计划:

扫描key(a)20万行数据,where a<150

扫描结果集合得到b=10的结果

使用得到的1000行的数据去检索C的值

一次得到,没有点查询

更好的选择还有没有?

聚簇索引(b, a)!

规则2总结:

避免点查询

确保索引覆盖查询

包含查询涉及到的所有列,并不仅仅是查询条件中的

使用聚簇索引

使用聚簇索引包含所有查询
允许用户把关注点集中在查询条件上
给多个查询提速,包括还没有预见到的查询--简化数据库设计

规则3:避免排序

简单的查询不需要后续的处理

select * from foo where b=100;

仅仅取得数据然后返回给用户

复杂的查询需要对数据进行后续的处理

GROUP BY 和 ORDER BY 会排序数据

选择正确的索引可以避免这些排序的步骤

考虑:

问:SELECT COUNT(c) FROM foo;

问:SELECT COUNT(c) FROM foo GROUP BY b, ORDER BY b;

查询计划1:

当进行表扫描时,给C计数

查询计划2:

扫描表把数据写到临时表中

对临时表按B进行排序

重新扫描排序后的数据,对每个b,使用c进行计数

如果我们使用key(b, c)会怎么样呢?

通过添加国所有需要的字段,我们覆盖了查询。 快速

通过提前对B进行排序,我们避免了排序 快速

总结:

通过给GROUP BY或者ORDER BY使用预先排序了的索引

把它们都放到一起:

简单查询

*SELECT COUNT() FROM foo WHERE c=5, ORDER BY b;**

key(c, b):

把c放在索引第一个位置去减少行检索 R1

然后通过剩余的行排序去避免排序 R3

因为相等的检查在c上,所以剩余的行数据会被按b排好序

SELECT SUM(d) FROM foo WHERE c=100, GROUP BY b;

key(c, b, d):

c在索引的第一个位置可以减少行数据的检索 R1

然后其它的数据在b上排好序去避免查询 R3

确保了查询覆盖所有的查询,避免了点查询 R2

在一些情况下,并没有明确的答案

最优的索引是和数据相关的

*问:SELECT COUNT() FROM foo WHERE c<100, GROUP BY b;**

索引:

key(c, b)

key(b, c)

key(c, b)的查询计划:

使用c<100对数据进行过滤

仍然需要对数据进行排序

    *检索的行不会被b进行排序

    *查询条件不需要对c进行相等的检查,因此b的值分布在不同的c值块中

key(b, c)的查询计划

按b进行排序,R3

列WHERE c>=100同样需要处理,因此没有R1的优势

那个更好一些呢?

答案依赖于数据是什么样的

如果c>=100有更多的数据,节省时间不去检索无用的行。使用key(c, b)

如果c>=100没有多少行,执行查询的时间是以排序为主,那么使用key(b, c)

问题的关键是,通常情况下,规则会有帮助,但它通常只是帮助我们去思考查询和索引,而不是给我们一个配方。

另一个重要的问题:为什么不把所有的加载都添加上索引呢?

需要跟上插入的负荷更多的索引 = 更小的系统负荷

索引的代价:

空间:

问题
    每个索引都会增加存储的需求

选项
    使用压缩

性能:

问题
    B-trees在某些索引任务中执行的很快(内存中,顺序的key),但是在其它类型的索引(辅助索引)会慢20倍

选项
    分形树索引对于所有的索引类型都很快速
        很容易索引
        可经常索引

范围查询性能:

问题
    规则2依赖于范围查询足够快
    B-tree会比较容易碎片化(删除、随机插入…),碎片化的B-tee在范围查询上会变慢

选项
    对于B-tee可以优化表,导出然后导入(时间和离线)
    对于Fractal Tree索引,不是问题,它不会碎片化

PS. 文档翻译自:

Understanding Indexing

Without Needing to Understand Data Structures

MySQL UC 2011 – April 12, 2011

Zardosht Kasheff

For more information…

• Please contact me at zardosht@tokutek.com for any

thoughts or feedback

• Please visit  Tokutek.com for a copy of this presentation

to learn more about the power of indexing, read about

Fractal Tree indexes, or to download a free eval copy of

TokuDB

PS. 正确设计数据库索引并不只是DBA的事情,每个合格开发者都必须具备这样的能力,但最近老是发现乱使用索引和不使用索引的情况,很明显是不理解索引,希望这篇译文能够帮助到这部分开发者吧。另外还有一篇不错的文章,有时间也会翻译出来,敬请期待。

Python操作

操作json

dump和dumps的区别是dump会生成一个类文件对象,dumps会生成字符串load和loads区别是load解析类文件对象,loads解析字符串格式的JSON

把json object的value由字符串改为[],load与loads,dump与dumps的区别

import json
def changejson(jstr):
  sdict = json.loads(jstr)
  # print sdict
  rdict = {}
  for k in sdict:
    item = []
    item.append(sdict[k])
    rdict[k] = item
  rjson = json.dumps(rdict)
  return rjson
print changejson('{}')

操作文件

# example 1:
t_file=open("/xxxx/xxx.txt", "r")
while 1:
  sline = t_file.readline().strip('\n')
  if not sline:
    break
  print "The Line is: %s" % (sline)
t_file.close()
# example 2:
# split file and hash with one filed
def splitfile(sfile, index, pre):
  f=open(sfile)
  files=[]
  for i in range(0, 64):
    files.append(open("/tmp/tmp/%s_%s" % (pre, i), "w"))
  while 1:
    line = f.readline().strip('\n')
    if not line:
      break
    row = line.split('\t')
    tid = row[index]
    htid = hash(tid)
    if (htid <= 0):
      htid = 0 - htid
    files[htid % 64].write("%s\n" %  (line))
  f.close()
  for i in range(0, 64):
    files[i].close()
#f_split("/tmp/tmp/jiaguo20150108_cpalog.dat", 1, "detail")

操作数据库

安装数据连接池模块DBUtils

# tar zxvf DBUtils-0.9.4.tar.gz

# cd DBUtils-0.9.4/

# python setup.py install

编写python脚本 Test.py

#!/usr/bin/python
import MySQLdb
from DBUtils.PooledDB import PooledDB
# 创建连接池
dbpool = PooledDB(creator = MySQLdb, maxusage=100, host="localhost",user="user",passwd="passwd",db="db",port=3306, charset="utf8")
# 使用
def findFromDb(sql):
  try:
    conn = dbpool.connection()
    cur = conn.cursor()
    count = cur.execute(sql)
    if (count > 0):
      # results = cur.fetchall()
      # for result in results:
        # ......
    cor.close()
    conn.close()
  except MySQLdb.Error,e:
    print "Mysql Error %d: %s" % (e.args[0], e.args[1])
    result = {}
    return result
print findFromDb("......")

Sqoop源码分析

Sqoop的Mysql数据导出实现分两种,一种是使用JDBC方式从Mysql中获取数据,一种是使用MysqlDump命令从MySql中获取数据,默认是 JDBC方式获取数据,如果要使用dump方式获取数据,需要添加 -direct 参数。

使用JDBC方式从Mysql中获取数据

配置语句时,需要添加 $CONDITIONS 点位符,比如:SELECT id FROM user WHERE $CONDITIONS,Sqoop在内部实现时会把它替换成需要的查询条件。

Sqoop启动后会先查询元数据,它会把 $CONDITIONS 替换为 (1=0) ,然后用得到的SQL语句查询数据表对应的Meta信息对于导出一个表的情况,Sqoop会使用这个SQL查询三次数据库,分别是: 1、获取 colInfo(最终得到columnTypes信息)2、查询ColumnNames信息3、生成QueryResult类执行 generateFields操作获取columnTypeNames时。

Sqoop会对获取的Fields做校验,列不能重复,它还会处理数据库的字段到Java属性名的转换

QueryResult类是通过构建java类文件,然后获取JavaCompiler,然后编译加载,为了提高处理性能,不是使用反射实现的,这个生成类内部处理mysql到hdfs属性值为空和分隔符的处理。

接着它会进行下面一个Sql查询操作,查询结果集为MIN(split列),MAX(split列),查询条件的处理逻辑为 $CONDITIONS 替换为(1=1),然后再添加外面SELECT查询 (举例:SELECT MIN(id), MAX(id) FROM (SELECT ID,NAME,PASSPORT WHERE (1=1) ) AS t1 ),这样就查询出来此次导出数据最大的split列值和最小的split列值。

对于为整数、布尔值、时间格式、Float等 的分区列,进行split时直接根据对应值的大小进行Split,Text文本的处理方式比较特殊,Sqoop先会对之前获取到的Min和Max的字串寻找它们最大的相同前缀子字串,然后把后面的字段转化为BigDecimal,结合时char占两个字节(65536),算法在 TextSplitter类中,比较简单,本质上就是一个进制转换。拆分好后,需要把Split的值再转换为String,然后加上相同前缀子字段,就构成了查询区间了(注意中文可能会被拆分)。

Sqoop对数据的获取是在DataDrivenDBRecordReader中,在查询时会把 $CONDITIONS 替换成 split 的范围比如 ( id >= 1) && (id<10),使用JDBC获取到结果集游标,然后移动游标处理数据。

使用MysqlDump命令从MySql中获取数据

第二种方法与第一种方式有下面的差别:

初始化元数据,它是在构建的查询语句后面添加 limit 1 ,比如:SELECT t. FROM user AS t LIMIT 1,因为dump方式在查询指定获取列时使用的是 t.,当使用limit 0时,数据库不会给它返回必须的元数据信息。

dump方式在map进行数据的获取,其会构建mysqldump命令,然后使用java程序调用,获取输入输出流和错误流,其实现了 org.apache.sqoop.util.AsyncSink 抽象类,用来处理输入输出流和错误流。

优化策略:

Sqoop查询无数据会进行三次相同的Sql查询,可以合并查询,不过由于仅返回Meta信息,查询很快,不需要修改实现。

分区列选择对于查询元数据和导出的查询影响很大,应该对索引做调优,避免对分区列的排序操作,加快元数据查询速度和导出数据的速度,尽量选择自增加的主键ID做Split列,区分度好并且可以顺序读取数据。

导出操作的查询语句中,$CONDITIONS 会被替换为范围区间,创建索引时,要考虑做这个查询的优化。

索引建议,考虑三个规则(使查询数据集较少、减少点的查询、避免排序操作),Sqoop场景下,如果分区列不是主键(自增加)时,把分区列做为联合索引的第一个字段,其它被选择的查询条件做为索引的其它字段。

分区列的选择,要避免Split后数据不均衡。

从实现上来看-m参数是可以增加任务的并行度的,但数据库的读线程是一定的,所以 -m 过大对于数据库会是一个压力,在Sqoop的场景下,数据库是一个影响并发的瓶颈。增加job数意义不大。

问题

下面列出Sqoop目前1.4.6版本存在的两个问题。

查看Sqoop源码,发现其存在两个比较严重的问题。

问题 1、数据分片与Mapper设定不合理

Sqoop在抽取时可以指定 -m 的参数,但这个 -m 的参数是控制mapper的数量的,但它也决定了最后能够生成的文件的数目,调节这个值可以实现对结果文件大小的控制,但问题是,如果产生的文件的格式不能够被分割,那么对这个数据的下游性能有很大影响,同时Sqoop在启动时会启动 -m 个 MapperTask,会对数据库产生m的并发读取。个人认为Sqoop做为一个数据导出框架,对数据的控制应该再细至一些,-m 只是控制MR的并行度,而数据分片数目应该由另外的参数控制。

个人建议可以加个 -split-per-map 参数,比如设置-m=4 -split-per-map=2,则对结果集分 8 片,每个Mapper处理两片数据,最后共产生 8 个文件。

问题 2、分片效率低

Sqoop在做分片处理时有问题,其实现会使用 “Select Max(splitKey),Min(splitKey) From ( –select参数 ) as t1” 的语句来查询分片信息,在Mysql下,这样的查询会产生一个以split-id为主键,包含需要导出的其它所有字段的临时表,如果数据量不大,临时表数据可以在内存中,处理速度还可以保证。但如果数据量很大,内存中已经存放不下时,这些数据会被保存为MyISAM表存放到磁盘文件中,如果数据量再大一些,磁盘文件已经存放不下临时表时,拆分数据会失败。如果数据量很大,这个查询可以占到整个导出时间的45%,优化空间很大,如果不修改实现的话,不适合做大数据量表的全量数据导出操作。

解决方案一:

修改所有导出脚本,分片语句自定义

解决方案二:

修改:org.apache.sqoop.mapreduce.DataDrivenImportJob的

@Contract(“null, _ -> !null”)private String buildBoundaryQuery(String col, String query)

修改代码如下

/**
   * Build the boundary query for the column of the result set created by
   * the given query.
   * @param col column name whose boundaries we're interested in.
   * @param query sub-query used to create the result set.
   * @return input boundary query as a string
   */
  private String buildBoundaryQuery(String col, String query) {
    if (col == null || options.getNumMappers() == 1) {
      return "";
    }

    // Replace table name with alias 't1' if column name is a fully
    // qualified name.  This is needed because "tableName"."columnName"
    // in the input boundary query causes a SQL syntax error in most dbs
    // including Oracle and MySQL.
    String alias = "t1";
    int dot = col.lastIndexOf('.');
    String qualifiedName = (dot == -1) ? col : alias + col.substring(dot);

    ConnManager mgr = getContext().getConnManager();
    String ret = mgr.getInputBoundsQuery(qualifiedName, query);
    if (ret != null) {
      return ret;
    }

//    return "SELECT MIN(" + qualifiedName + "), MAX(" + qualifiedName + ") "
//        + "FROM (" + query + ") AS " + alias;
    return initBoundaryQuery(qualifiedName, query, alias);
  }

  private String initBoundaryQuery(String qualifiedName, String query, String alias) {
    StringBuilder regex = new StringBuilder();
    regex.append("(\\s[A|a][S|s][\\s][`]?");
    for (char c : qualifiedName.toCharArray()) {
      regex.append('[').append(c).append(']');
    }
    regex.append("[`|\\s|,])");
    final Matcher matcher1 = Pattern.compile(regex.toString()).matcher(query);
    final boolean asCheckOk = !matcher1.find();
    if(asCheckOk) {
      final Matcher matcher2 = Pattern.compile("(\\s[F|f][R|r][O|o][M|m]\\s)").matcher(query);
      int count = 0;
      while (matcher2.find()) {
        count++;
      }
      boolean fromCheckOk = count == 1;
      if(fromCheckOk) {
        final Matcher matcher = Pattern.compile("(\\s[F|f][R|r][O|o][M|m]\\s[\\s\\S]*)").matcher(query);
        while (matcher.find()) {
          return "SELECT MIN(" + qualifiedName + "), MAX(" + qualifiedName + ") "
                  + matcher.group();
        }
      }
    }
    return "SELECT MIN(" + qualifiedName + "), MAX(" + qualifiedName + ") "
            + "FROM (" + query + ") AS " + alias;
  }

问题三:无法自定义特殊字符替换

解决方案一:

通过SQL的替换功能,修改脚本代价高,并且脚本可读性、可维护性都大大降低

解决文案二:

修改Sqoop实现,增加自定义特殊字符替换功能

twemproxytwo em proxy

twemproxy (nutcracker) Build Status

twemproxy (pronounced “two-em-proxy”), aka nutcracker is a fast and lightweight proxy for memcached and redis protocol. It was primarily built to reduce the connection count on the backend caching servers.

memcached和redis协议快速和轻量级的代理.它的创建主要是为了减少到后台缓存服务器的连接数量.

Features

Fast.

Lightweight.

Maintains persistent server connections.

Keeps connection count on the backend caching servers low.

Enables pipelining of requests and responses.

Supports proxying to multiple servers.

Supports multiple server pools simultaneously.

Implements the complete memcached ascii and redis protocol.

Easy configuration of server pools through a YAML file.

Supports multiple hashing modes including consistent hashing and distribution. Observability through stats exposed on stats monitoring port.

特点:

快速

轻量级

保持持久的服务连接

保存少量的连接到后端缓存服务器

请求和响应启用流水线

支持代理多个服务器

支持多台服务器池

实现了完整的memcached ascii和redis协议

通过yaml文件容易的配置服务器池

支持多种hash模式,包括一致性hash

通过监控端口暴露可以观测的统计数据

Zero Copy

In nutcracker, all the memory for incoming requests and outgoing responses is allocated in mbuf. Mbuf enables zero-copy because the same buffer on which a request was received from the client is used for forwarding it to the server. Similarly the same mbuf on which a response was received from the server is used for forwarding it to the client.

请求流入和响应流出所使用的内存都在mbuf中申请,mbuf支持零拷贝因为同样的buffer从请求的client处得到的会被使用来连接server.同样的,后端服务器响应的buf也会被使用来传给客户端.

Furthermore, memory for mbufs is managed using a reuse pool. This means that once mbuf is allocated, it is not deallocated, but just put back into the reuse pool. By default each mbuf chunk is set to 16K bytes in size. There is a trade-off between the mbuf size and number of concurrent connections nutcracker can support. A large mbuf size reduces the number of read syscalls made by nutcracker when reading requests or responses. However, with large mbuf size, every active connection would use up 16K bytes of buffer which might be an issue when nutcracker is handling large number of concurrent connections from clients. When nutcracker is meant to handle a large number of concurrent client connections, you should set chunk size to a small value like 512 bytes using the -m or –mbuf-size=N argument.

此外,mbufs中的内存使用了复用池.一个mbuf被申请,它就不会被销毁,只是会把它扔回复用池.默认的每个mbuf块被设置为16K.在mbuf大小和所能支持的连接数上需要做权衡.一个大的mbuf值可减少nutcracker从请求和响应处读数据的系统调用数,但是,一个大的mbuf值,每个活动的连接都会使用16k的buffer,这就会引起nutcracker处理大量的客户端连接成为一个问题.当它需要处理大量的client连接时,应该使用 -m 或 –mbuf-size=N 的命令把块大小设置的小一些,比如512字节.

Configuration

nutcracker can be configured through a YAML file specified by the -c or –conf-file command-line argument on process start. The configuration file is used to specify the server pools and the servers within each pool that nutcracker manages. The configuration files parses and understands the following keys:

在启动时使用-c或–conf-file命令行参数把yaml文件传入.这个配置文件用来指定服务池和每个池中nutcracker管理的服务器,它解析和理解下面的键:

listen: The listening address and port (name:port or ip:port) for this server pool.

监听:服务池的监听地址和端口

hash: The name of the hash function. Possible values are:

哈希:哈希函数的名字,可能的值为:

one_at_a_time

md5

crc32

fnv1_64

fnv1a_64

fnv1_32

fnv1a_32

hsieh

murmur

jenkins

hash_tag: A two character string that specifies the part of the key used for hashing. Eg “{}” or “$$”. Hash tag enable mapping different keys to the same server as long as the part of the key within the tag is the same.

哈希标签:两个字符的字串分隔,用来指定哈希使用的key值.比如”{}”"$$”.不同的键可以映射到同一台服务器,只要它们在标签中的key是一样的。

distribution: The key distribution mode. Possible values are:

分布:key分布模式.

ketama

modula

random

timeout: The timeout value in msec that we wait for to establish a connection to the server or receive a response from a server. By default, we wait indefinitely.

超时时间:超时值以毫秒为单位,我们等待建立一个连接到服务器或从服务器接收响应的值。默认情况,我们无限期地等待。

backlog: The TCP backlog argument. Defaults to 512. preconnect: A boolean value that controls if nutcracker should preconnect to all the servers in this pool on process start. Defaults to false.

是否启动池中所有的服务器的连接在启动时,默认不启动

redis: A boolean value that controls if a server pool speaks redis or memcached protocol. Defaults to false.

指定使用redis协议还是使用memcached协议,默认false,使用memcached协议. server_connections: The maximum number of connections that can be opened to each server. By default, we open at most 1 server connection.

对于每个服务器使用的最大连接数,默认情况下我们至少启动1个服务连接

auto_eject_hosts: A boolean value that controls if server should be ejected temporarily when it fails consecutively server_failure_limit times. Defaults to false.

是否暂时弹出服务器

server_retry_timeout: The timeout value in msec to wait for before retrying on a temporarily ejected server, when auto_eject_host is set to true. Defaults to 30000 msec.

server_failure_limit: The number of conseutive failures on a server that would leads to it being temporarily ejected when auto_eject_host is set to true. Defaults to 2.

servers: A list of server address, port and weight (name:port:weight or ip:port:weight) for this server pool.

Finally, to make writing syntactically correct configuration file easier, nutcracker provides a command-line argument -t or –test-conf that can be used to test the YAML configuration file for any syntax error.

保证写正确的配置文件简单,nutcracker提供一个命令行参数-t或者–test-conf可以用来测试yaml配置文件是否存在语法错误.

Observability

Observability in nutcracker is through logs and stats.

Nutcracker exposes stats at the granularity of server pool and servers per pool through the stats monitoring port. The stats are essentially JSON formatted key-value pairs, with the keys corresponding to counter names. By default stats are exposed on port 22222 and aggregated every 30 seconds. Both these values can be configured on program start using the -c or –conf-file and -i or –stats-interval command-line arguments respectively. You can print the description of all stats exported by nutcracker using the -D or –describe-stats command-line argument.

Logging in nutcracker is only available when nutcracker is built with logging enabled. By default logs are written to stderr. Nutcracker can also be configured to write logs to a specific file through the -o or –output command-line argument. On a running nutcracker, we can turn log levels up and down by sending it SIGTTIN and SIGTTOU signals respectively and reopen log files by sending it SIGHUP signal.

Pipelining

Nutcracker enables proxying multiple client connections onto one or few server connections. This architectural setup makes it ideal for pipelining requests and responses and hence saving on the round trip time.

For example, if nutcracker is proxing three client connections onto a single server and we get requests – ‘get key\r\n’, ‘set key 0 0 3\r\nval\r\n’ and ‘delete key\r\n’ on these three connections respectively, nutcracker would try to batch these requests and send them as a single message onto the server connection as ‘get key\r\nset key 0 0 3\r\nval\r\ndelete key\r\n’.

Pipelining is the reason why nutcracker ends up doing better in terms of throughput even though it introduces an extra hop between the client and server. Deployment

If you are deploying nutcracker in production, you might consider reading through the recommendation document to understand the parameters you could tune in nutcracker to run it efficiently in the production environment. Users

Pinterest Tumblr Twitter

Issues and Support

Have a bug or a question? Please create an issue here on GitHub!

https://github.com/twitter/twemproxy/issues

twemproxy recommendation document

If you are deploying nutcracker in your production environment, here are a few recommendations that might be worth considering.

建议,如果你想部署它到生产环境

Log Level

By default debug logging is disabled in nutcracker. However, it is worthwhile running nutcracker with debug logging enabled and verbosity level set to LOG_INFO (-v 6 or –verbosity=6). This in reality does not add much overhead as you only pay the cost of checking an if condition for every log line encountered during the run time.

默认情况下debug日志是没有打开的.然而打开debug日志并把日志级别设置到LOG_INFO(-v 6或者–verbosity=6)是值得的,它不会增加过多的开销.你只需要支付检查是否每个日志行的条件都在运行时发生了的时间成本.

At LOG_INFO level, nutcracker logs the life cycle of every client and server connection and important events like the server being ejected from the hash ring and so on. Eg.

在LOG_INFO的日志级别上,它记录了每个客户端的生命周期,每个服务器连接和重要的事件比如服务器被从hash环中退出等等.

[Thu Aug  2 00:03:09 2012] nc_proxy.c:336 accepted c 7 on p 6 from '127.0.0.1:54009'
[Thu Aug  2 00:03:09 2012] nc_server.c:528 connected on s 8 to server '127.0.0.1:11211:1'
[Thu Aug  2 00:03:09 2012] nc_core.c:270 req 1 on s 8 timedout
[Thu Aug  2 00:03:09 2012] nc_core.c:207 close s 8 '127.0.0.1:11211' on event 0004 eof 0 done 0 rb 0 sb 20: Connection timed out
[Thu Aug  2 00:03:09 2012] nc_server.c:406 close s 8 schedule error for req 1 len 20 type 5 from c 7: Connection timed out
[Thu Aug  2 00:03:09 2012] nc_server.c:281 update pool 0 'alpha' to delete server '127.0.0.1:11211:1' for next 2 secs
[Thu Aug  2 00:03:10 2012] nc_connection.c:314 recv on sd 7 eof rb 20 sb 35
[Thu Aug  2 00:03:10 2012] nc_request.c:334 c 7 is done
[Thu Aug  2 00:03:10 2012] nc_core.c:207 close c 7 '127.0.0.1:54009' on event 0001 eof 1 done 1 rb 20 sb 35
[Thu Aug  2 00:03:11 2012] nc_proxy.c:336 accepted c 7 on p 6 from '127.0.0.1:54011'
[Thu Aug  2 00:03:11 2012] nc_server.c:528 connected on s 8 to server '127.0.0.1:11212:1'
[Thu Aug  2 00:03:12 2012] nc_connection.c:314 recv on sd 7 eof rb 20 sb 8
[Thu Aug  2 00:03:12 2012] nc_request.c:334 c 7 is done
[Thu Aug  2 00:03:12 2012] nc_core.c:207 close c 7 '127.0.0.1:54011' on event 0001 eof 1 done 1 rb 20 sb 8
To enable debug logging, you have to compile nutcracker with logging enabled using –enable-debug=log configure option.

要打开日志,需要在编译的时候使用–enable-debug=log的配置选项

Liveness

活跃度

Failures are a fact of life, especially when things are distributed. To be resilient against failures, it is recommended that you configure the following keys for every server pool. Eg:

在分布式的环境中,失败是常见的事情.要灵活的处理失败,建议在每个server pool中都添加如下的配置

resilient_pool:
  auto_eject_hosts: true
  server_retry_timeout: 30000
  server_failure_limit: 3

Enabling auto_eject_hosts: ensures that a dead server can be ejected out of the hash ring after server_failure_limit: consecutive failures have been encountered on that said server. A non-zero server_retry_timeout: ensures that we don’t incorrectly mark a server as dead forever especially when the failures were really transient. The combination of server_retry_timeout: and server_failure_limit: controls the tradeoff between resiliency to permanent and transient failures.

auto_eject_hosts,保证死掉的服务器可以在server_failure_limit后被从hash环中退出来.

server_failure_limit:在上面的服务器上记录的连续失败次数

server_retry_timeout:确保我们不会错误的标记一个服务器为死掉,尤其当失败是相当短的时候.

这三个的组合:在永久故障和临时故障之间做权衡.

To ensure that requests always succeed in the face of server ejections (auto_eject_hosts: is enabled), some form of retry must be implemented at the client layer since nutcracker itself does not retry a request. This client-side retry count must be greater than server_failure_limit: value, which ensures that the original request has a chance to make it to a live server.

为了确保请求每次都成功,在开启了auto_eject_hosts的情况下,因为它并不提供重发请求,所以实现某种形式的重发必须在客户端.客户端级别的重发次数必须大于server_failure_limit的值,以确保这个原始请求有机会发到一个活着的服务器.

Timeout

It is always a good idea to configure nutcracker timeout: for every server pool, rather than purely relying on client-side timeouts. Eg:

配置nutcracker过期时间总是一个好的主意:对于每个服务器池配置,而不是单纯依靠客户端超时.

resilient_pool_with_timeout:
  auto_eject_hosts: true
  server_retry_timeout: 30000
  server_failure_limit: 3
  timeout: 400
# 带超时的弹性池

Relying only on client-side timeouts has the adverse effect of the original request having timedout on the client to proxy connection, but still pending and outstanding on the proxy to server connection. This further gets exacerbated when client retries the original request.

仅仅在客户端请求超时的回复对客户端到代理端的原始请求超时有相反的效应,但代理端到客户端的链接仍然会处于等待或未解决状态。当客户端有重试原始请求时它会被进一步加剧.

By default, nutcracker waits indefinitely for any request sent to the server. However, when timeout: key is configured, a requests for which no response is received from the server in timeout: msec is timedout and an error response SERVER_ERROR Connection timed out\r\n is sent back to the client.

默认情况下,任何请求发送给服务器后,它会无限期的等待.当timeout被设置后,如果一个请求在timeout的时间过后还没有从服务器上得到响应,这时一个错误信息的响应SERVER_ERROR会被发送给客户端.

Error Response

Whenever a request encounters failure on a server we usually send to the client a response with the general form – SERVER_ERROR \r\n (memcached) or -ERR (redis).

每当一个请求在服务器端遇到了失败,我们通常会给客户端发送一个错误的响应. SERVER_ERROR \r\n 或者 ERR

For example, when a memcache server is down, this error response is usually:

SERVER_ERROR Connection refused\r\n or, SERVER_ERROR Connection reset by peer\r\n

When the request timedout, the response is usually:

SERVER_ERROR Connection timed out\r\n

Seeing a SERVER_ERROR or -ERR response should be considered as a transient failure by a client which makes the original request an ideal candidate for a retry.

SERVER_ERROR或者-ERR响应应该被认为是一个客户端传输失败,这时客户端是一个理想的重发的候选人.

read, writev and mbuf

All memory for incoming requests and outgoing responses is allocated in mbuf. Mbuf enables zero copy for requests and responses flowing through the proxy. By default an mbuf is 16K bytes in size and this value can be tuned between 512 and 65K bytes using -m or –mbuf-size=N argument. Every connection has at least one mbuf allocated to it. This means that the number of concurrent connections nutcracker can support is dependent on the mbuf size. A small mbuf allows us to handle more connections, while a large mbuf allows us to read and write more data to and from kernel socket buffers.

….每一个连接至少拥有一个mbuf申请给它.这就表示它能支撑多少连接依赖于mbuf的大小.小的mbuf值允许我们处理更多的连接,大的mbuf允许我们读写更多的数据从或者去内核套间字缓冲区中.

If nutcracker is meant to handle a large number of concurrent client connections, you should set the mbuf size to 512 or 1K bytes.

如果它要处理大量的客户端连接,你应该把mbuf值设置为512或者1k bytes.

Maximum Key Length

The memcache ascii protocol specification limits the maximum length of the key to 250 characters. The key should not include whitespace, or ‘\r’ or ‘\n’ character. For redis, we have no such limitation. However, nutcracker requires the key to be stored in a contiguous memory region. Since all requests and responses in nutcracker are stored in mbuf, the maximum length of the redis key is limited by the size of the maximum available space for data in mbuf (mbuf_data_size()). This means that if you want your redis instances to handle large keys, you might want to choose large mbuf size set using -m or –mbuf-size=N command-line argument.

memcache ascii协议:250字节,不能有空白,\r \n字,而在redis中我们没有这些限制.然而,nutcraker需要把这个key存储在一个连续的内存空间.因为所有的请求和响应被存储在mbuf中,所以最大的redis的key的长度被mbuf中最大的可用数据空间的大小所限制(mbuf_data_size()).,这个mbuf大小可以用 -m 或者 –mbuf-size=N 的命令行参数来设置.

Node Names for Consistent Hashing

The server cluster in twemproxy can either be specified as list strings in format ‘host:port:weight’ or ‘host:port:weight name’.

servers:
– 127.0.0.1:6379:1
– 127.0.0.1:6380:1
– 127.0.0.1:6381:1
– 127.0.0.1:6382:1

Or,

servers:
– 127.0.0.1:6379:1 server1
– 127.0.0.1:6380:1 server2
– 127.0.0.1:6381:1 server3
– 127.0.0.1:6382:1 server4

In the former configuration, keys are mapped directly to ‘host:port:weight’ triplet and in the latter they are mapped to node names which are then mapped to nodes i.e. host:port pair. The latter configuration gives us the freedom to relocate nodes to a different server without disturbing the hash ring and hence makes this configuration ideal when auto_eject_hosts is set to false. See issue 25 for details.

…后者的配置给了我们自由迁移到不同的服务器节点,而不会干扰的哈希环,从而使得这种配置的理想时的auto_eject_hosts设置为false。有关详细信息,请参见问题25。

Note that when using node names for consistent hashing, twemproxy ignores the weight value in the ‘host:port:weight name’ format string.

当配置节点名字给hash环时,它会忽略配置中的weight值

Hash Tags

Hash Tags enables you to use part of the key for calculating the hash. When the hash tag is present, we use part of the key within the tag as the key to be used for consistent hashing. Otherwise, we use the full key as is. Hash tags enable you to map different keys to the same server as long as the part of the key within the tag is the same.

hash标签允许你使用部分key去计算hash值.当hash tag存在的时候,我们使用tag标签中的key去计算hash值.其它情况,我们使用全部的key去计算.它允许你去把不同的keys哈希到相同的服务器,只要它们在tag内的值是相同的.

For example, the configuration of server pool beta, aslo shown below, specifies a two character hash_tag string – “{}”. This means that keys “user:{user1}:ids” and “user:{user1}:tweets” map to the same server because we compute the hash on “user1″. For a key like “user:user1:ids”, we use the entire string “user:user1:ids” to compute the hash and it may map to a different server.

beta:

listen: 127.0.0.1:22122
hash: fnv1a_64
hash_tag: “{}”
distribution: ketama
auto_eject_hosts: false
timeout: 400
redis: true
servers:
– 127.0.0.1:6380:1 server1
– 127.0.0.1:6381:1 server2
– 127.0.0.1:6382:1 server3
– 127.0.0.1:6383:1 server4