目录

一.预分区

hbase中

phoneix中

二.设计Rowkey

 rowkey长度原则

 rowkey散列原则

 rowkey唯一原则

三.热点问题

加盐

哈希

 反转

时间戳反转

---

一.预分区

默认情况下，在创建HBase表的时候会自动创建一个region分区，当导入数据的时候， 所有的HBase客户端都向这一个region写数据，直到这个region足够大了才进行切分。 但是region切分是非消耗IO资源的一种操作，对我们写入的速度肯定会产生影响，一种可以加快批量写入速度的方法是通过预先创建一些空的regions，这样当数据写入 HBase时，会按照region分区情况，在集群内做数据的负载均衡。

 那么怎么区实现预分区呢？

hbase中

 假设不进行预分区，最终region的数量为10，那么我们进行预分区的数量就应该是分10块

比如我这里的一组数据，他的rowkey是连续的1500100001-1500101000

一共1000条数据，分10个区

首先创建一个分区信息文件 split.txt,内容如下：

1500100100|
1500100200|
1500100300|
1500100400|
1500100500|
1500100600|
1500100700|
1500100800|
1500100900|

为什么分区后面写'|'，'|'的ascll码是最大 124，所以小于等于'|'前面的内容都会被分配在切分的左边 

1500100100  和  1500100100|

这里两个比较，你看下面的比上面的多出后面一位，按字典顺序肯定是1500100100在上面

1500100100|  和  1500100101，我们看对应位置的大小，这2个数的前9位都是相同的，1500100101最后一位比前面的数大了，所以字典顺序就是比他大，不会再去看下一位

//创建一个表，指定列簇名，分区文件信息要写全路径
create 'student_split','info',{SPLITS_FILE=>'/root/split.txt'}

不用文件导入的话就把得上面的内容一个一个写入
create 'student_split','info',{SPLITS_FILE=>['1500100100|','1500100200|','1500100300|'.......]}

到hbase的网页端口查看一下这张表的信息： 

 插入数据后观察,写入请求分配的很均衡

phoneix中

在创建表的时候指定salting。会再rowkey前面加上一个随机的前缀，这适用于我们不知道rowkey内容到底是怎样的场合

优点：不需要知道rowkey的分布情况
缺点：不能再hbase中对数据进行查询和修改，改变的rowkey我们也不知道到底是什么样子的

CREATE TABLE IF NOT EXISTS STUDENT3 (
 id VARCHAR NOT NULL PRIMARY KEY, 
 name VARCHAR,
 age BIGINT, 
 gender VARCHAR ,
 clazz VARCHAR
)salt_buckets=6;

upsert into STUDENT3 values('1500100004','葛德曜',24,'男','理科三班');
upsert into STUDENT3 values('1500100005','宣谷芹',24,'男','理科六班');
upsert into STUDENT3 values('1500100006','羿彦昌',24,'女','理科三班');

可以看到，这个'\x01','\x02','\x03'...就是给我们的rowkey打上的标记，因为标记随机分配， 我刚才传入的数据连续的rowkey被打上标记后可能就不在一个分区了

二.设计Rowkey

 rowkey长度原则

rowkey是一个二进制码流，可以是任意字符串，最大长度 64kb ，实际应用中一般为10-100bytes，以 byte[] 形式保存，一般设计成定长。

hase中，无论是rowkey,列簇，列名，这些都会占用内存的， 拿写入数据的过程来说，memstore会把部分数据写入内存，hfile存储数据也会包含这些rowkey等，太长了就压缩了其他信息的存储空间

rowkey散列原则

如果rowkey按照时间戳的方式递增，不要将时间放在二进制码的前面，建议将rowkey的高位作为散列字段，由程序随机生成，低位放时间字段，这样将提高数据均衡分布在每个RegionServer，以实现负载均衡的几率。如果没有散列字段，首字段直接是时间信息，所有的数据都会集中在一个RegionServer上，这样在数据检索的时候负载会集中在个别的RegionServer上，造成热点问题，会降低查询效率。

rowkey唯一原则

必须在设计上保证其唯一性， rowkey可以锁定唯一的一行数据，rowkey重复的话后put的数据会覆盖前面插入的数据

三.热点问题

rowkey设计是热点的源头，热点发生在大量的client直接访问集群的一个或极少数个节点（访问可能是读，写或者其他操作）。大量访问会使热点region所在的单个机器超出自身承受能力，引起性能下降甚至region不可用，这也会影响同一个RegionServer上的其他region，由于主机无法服务其他region的请求。 设计良好的数据访问模式以使集群被充分，均衡的利用。

为了避免写热点，设计rowkey使得不同行在同一个region，但是在更多数据情况下，数据应该被写入集群的多个region，而不是一个。

下面是一些常见的避免热点的方法以及它们的优缺点：

加盐

这里所说的加盐不是密码学中的加盐，而是在rowkey的前面增加随机数，具体就是给rowkey分配一个随机前缀以使得它和之前的rowkey的开头不同。分配的前缀种类数量应该和你想使用数据分散到不同的region的数量一致。加盐之后的rowkey就会根据随机生成的前缀分散到各个region上，以避免热点。

10001，10002，10003这样的三个rowkey给他们加上随机的前缀
##$10001,$%#10002,&%$10003
分配region就不会总是在一个region内了

哈希

哈希会使同一行永远用一个前缀加盐。哈希也可以使负载分散到整个集群，但是读却是可以预测的。 根据哈希值可以反推出完整的rowkey，可以使用get操作准确获取某一个行数据

哈希有多种算法，这里就拿md5来计算1002的哈希值，多次计算都是这个结果
fba9d88164f3e2d9109ee770223212a0
那么别人要是看到这个哈希值也可以通过算法，工具计算出你原来的rowkey是什么样的

 反转

第三种防止热点的方法是反转固定长度或者数字格式的rowkey。这样可以使得rowkey中经常改变的部分（最没有意义的部分）放在前面。这样可以有效的随机rowkey，但是牺牲了rowkey的有序性。 

还是拿1001，1002，1003来说，这3个rowkey连续将改变得数据放到前面
1100，2100，3100，这样就不连续了

时间戳反转

一个常见的数据处理问题是快速获取数据的最近版本，使用反转的时间戳作为rowkey的一部分对这个问题十分有用，可以用 Long.Max_Value - timestamp 追加到key的末尾，

简单说：时间戳反转做的是用一个大的数值减去时间戳

原来的roweky
1638711499_user01
1638711500_user02
1638711501_user03
用一个较大数10000000减去时间戳
8361288499_user03
8361288500_user02
8361288501_user01
这样新的时间戳的信息会在被排在前面

MySQL千万级数据分区存储及查询优化

作为传统的关系型数据库，MySQL因其体积小、速度快、总体拥有成本低受到中小企业的热捧，但是对于大数据量(百万级以上)的操作显得有些力不从心，这里我结合之前开发的一个web系统来介绍一下MySQL数据库在千万级数据量的情况下如何优化提升查询速度。

一、基本业务需求

该系统包括硬件系统和软件系统，由中科院计算所开发的无线传感器网络负责实时数据的监测和回传到MySQL数据库，我们开发的软件系统负责对数据进行实时计算，可视化展示及异常事件报警监测。宫殿的温湿度等数据都存储在data表中，由于业务需要，data表中旧的数据要求不能删除，经过初步估算，一年的数据量大概为1200万条，之前的系统当数据量到达百万级时查询响应速度很慢，导致数据加载延迟很大，所以很有必要进行数据库的优化查询，提升响应速度。

结合故宫温湿度监测系统EasiWeb 7.1的data表查询，这里主要从以下三个方面详解MySQL的分区优化技术：

(1)EasiWeb 7.1系统data表基于分表、分区和索引的优化方案对比。

(2)EasiWeb 7.1系统中采用的优化方案及实施步骤

(3)系统模拟产生1500万数据的优化前后对比测试

二、data表优化方案选择

    针对故宫系统大数据量时提升响应速度及运行性能的问题，我们团队通过研究和论证，提出了三种方案：

2.1 data表分表存储，联表查询

原理解释

   分表即将一个表结构分解为多个子表，这些子表可以同一个数据库下，也可以在不同的数据库下，查询的时候通过代码控制，生成多条查询语句，进行多项子表联查，最后汇总结果，整体上的查询结果与单表一样，但平均相应速度更快。

实现方式

   采用merge分表，划分的标准可以选取时间(collectTime)作为参数。主表类似于一个壳子，逻辑上封装了子表，实际上数据都是存储在子表中。我们在每年的1月1日创建一个子表data_20XX，然后将这些子表union起来构成一个主表。对于插入操作，最新的数据将会被插入到最后一个子表中；对于查询操作，通过'data'主表查询的时候，查询引擎会根据查询语句口控制选取要查询的子表集合，实现等效查询。

优缺点

   优点是merge分表可以很方便得实现分表，在进行查询的时候封装了查询过程，用户编写的代码较少。

   缺点是破坏了data表的结构，并且在新建子表的时候由于定时器延迟，可能导致个别数据被错误的存储。

2.2 data表分区存储

原理解释

    分区把存放数据的文件分成了许多小块，存储在磁盘中不同的区域，通过提升磁盘I/O能力来提升查询速度。分区不会更改data表的结构，发生变化的是存储方式。

实现方式

    采用range分区，根据数据的时间字段（collectTime）实现分区存储，以年份为基准，不同的区域存储的是不同年份的数据，可以采用合并语句进行分区的合并，分区操作由MySQL暗箱完成，从用户的角度看，data表不会改变，程序代码无需更改。

优缺点

   优点是range分区实现方便，没有破坏data表的结构，用户无需更改dao层代码和查询方式。而且可以提前预设分区，比如今年是2017年，用户可以将数据分区预设到2020年，方式灵活，便于扩充。

    缺点是数据存储依赖于分区的存储磁盘，一旦磁盘损坏，则会造成数据的丢失。

2.3 data表更换为Myisam搜索引擎

原理解释

MySQL提供Myisam和InnoDB类型的搜索引擎，两种搜索引擎侧重点不同，可以根据实际的需要搭配使用，以达到最优的相应效果。Myisam引擎可以平均分布I/O，获得更快的速度，InnoDB注重事务处理，适合高并发操作。

图1 BTREE存储结构

    从图中就可以看出，B+Tree的内部结点不存储数据，只存储指针，而叶子结点则只存储数据，不存储指针。并且在其每个叶子节点上增加了一个指向数据的指针。

    MyISAM引擎的索引结构为B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。

 实现方式

   修改data表的搜索引擎为Myisam，其它数据表不做更改。

优缺点

   Myisam优点数据文件和索引文件可以放置在不同的目录，平均分布I/O。缺点是不合适高并发操作，对事务处理(修改和删除操作)的支持较差。

    InnoDB优点是提供了具有提交、回滚和崩溃恢复能力的事务安全，适合高并发操作的事务处理。缺点是处理效率相对Myisam较差并且会占用更多的磁盘空间以保留数据和索引。

    由于data表主要涉及的是查询和插入操作，提高速度是第一需求，所以可以将data表的搜索引擎改为Myisam。

2.4 综合比较

实现方式

   分表后的数据实际存储在子表中，总表只是一个外壳，merge分表编码较少，更改了表的结构。 
   分区后的数据存储的文件分成了许多小块，不更改表的结构。

提高性能

    分表侧重点是数据分表存储，联表查询，注重提高MySQL的并发能力。

   分区侧重于突破磁盘的读写能力，从而达到提高MySQL性能的目的。

实现的难易度

    采用merge分表与range分区两种方式难易度差不多，如果是用其他分表方式就比分区更为复杂。

    range分区实现是比较简单的，建立分区的表和平常的表没有什么区。

    两种方式基本上对开发端代码都是透明的。

三、最终采用的优化思路

     故宫系统中的data表并发访问量不大，所以通过分表提高访问速度和并发效果不太显著，而且还可能破坏原有表的结构。而分区可以提高磁盘的读写能力，配合Myisam搜索引擎可以很大幅度提升查询速度。

    所以我们采用data表分区存储+Myisam搜索引擎+建立索引的方式来优化数据库的查询

四、实施步骤及相关操作简介

实施步骤

1、将data表的搜索引擎由InnoDB更改为MyISAM

ALTER TABLE `data` ENGINE=MyISAM;

2、建立以collectime、originAddr字段的BTREE索引

ALTER TABLE `data`

ADD INDEX `collectTime` (`collectTime`) USING BTREE ,

ADD INDEX `nodeId` (`originAddr`) USING BTREE ;

3、分区之前将collectTime由char类型改为datetime/date类型，才能进行分区操作(注:char类型不支持分区操作)。

ALTER TABLE `data`

MODIFY COLUMN `collectTime`  datetime NOT NULL AFTER `ID`;

4、采用range分区可以保证分区均匀

注:这里由于最早的数据从12年开始，我们采用了半年分一个区，预分区到2021年1月份，实际分区结合具体情况而定。

ALTER TABLE `data`

partition by range(to_days(collectTime))

(

partition P0 values less than (to_days('2012-01-01')),

partition P1 values less than (to_days('2012-07-01')),

partition P2 values less than (to_days('2013-01-01')),

partition P3 values less than (to_days('2013-07-01')),

partition P4 values less than (to_days('2014-01-01')),

partition P5 values less than (to_days('2014-07-01')),

partition P6 values less than (to_days('2015-01-01')),

partition P7 values less than (to_days('2015-07-01')),

partition P8 values less than (to_days('2016-01-01')),

partition P9 values less than (to_days('2016-07-01')),

partition P10 values less than (to_days('2017-01-01')),

partition P11 values less than (to_days('2017-07-01')),

partition P12 values less than (to_days('2018-01-01')),

partition P12 values less than (to_days('2018-07-01')),

partition P12 values less than (to_days('2019-01-01')),

partition P12 values less than (to_days('2019-07-01')),

partition P12 values less than (to_days('2020-01-01')),

partition P12 values less than (to_days('2020-07-01')),

partition P12 values less than (to_days('2021-01-01')),

)

5、分区情况查询

SELECT  *  FROM

  INFORMATION_SCHEMA.partitions

WHERE

  TABLE_SCHEMA = schema()

  AND TABLE_NAME='data'; 

图2 查看分区信息

相关操作简介

1、由于range分区函数无法识别char型字段，所以要在分区之前将collectTime由char类型改为datetime类型，才能进行range分区操作。

2、采用range分区时，要用to_days(collectime)的分区方式，采用这种方式，在查询的时候只会在相应的分区查找，而如果不加to_days(),在查询的时候，会对全表进行扫描。

3、分区优化后，查询速度提升主要体现在非跨区查询的时候，当查询条件均属于一个区域时，数据库可以快速定位到所查分区，而不会扫描全表。

4、每次插入数据的时候，数据库会判定对应的collectTime属于哪个分区，从而存储到对应的分区中，不会影响其它分区。

五、1500万数据测试对比

1.经过数十次的多条件跨区与不跨区查询测试，相比没有做优化的data表，优化后查询速度提升90%以上。

2、不跨区域查询响应速度<=0.5s，跨区查询在第一次比较慢，但之后在翻页查询的时候，相应速度<=1s。

3、查询的时候由于计算机性能差异，所以同样的查询在不同的机器上查询速度会有所不用，我们采用的测试环境为i7 4500U酷睿 2核 4线程处理器 8G RAM 普通硬盘。

表1 data表查询对比测试结果

编号	测试条件	collectTime字段	优化前 查询时间	优化后 查询时间
1	NodeID,data1,data2,collectTime	2017/1/19—2017/4/19	2.59s	0.74s
2	NodeID,data1,data2,collectTime	2017/1/19—2017/7/19	16.64s	0.96s
3	NodeID,data1,data2,collectTime	2016/8/19—2017/2/19	34.3s	2.49s
4	NodeID,data1,data2,collectTime	2016/1/19—2017/1/19	46.52s	2.50s
5	NodeID,data1,data2,collectTime	2015/2/19—2017/3/19	78.12s	3.73s
6	NodeID,data1,data2,collectTime	2015/3/19—2017/4/19	250.33s	4.42s
7	NodeID,data1,data2,collectTime	2015/1/19—2017/4/19	226.10s	4.39s
8	NodeID,data1,data2,collectTime	2014/4/19—2017/4/19	410.22s	5.50s
9	NodeID,data1,data2,collectTime	2014/2/19—2017/4/19	437.50s	5.50s
10	NodeID,data1,data2,collectTime	2014/1/19—2017/4/19	558.05s	5.70s
11	NodeID,data1,data2,collectTime	2012/4/19—2017/4/19	--（响应时间过长）	8.70s

六、测试查询数据部分截图

 

HBase三个重要机制

1、flush机制

1.（hbase.regionserver.global.memstore.size）默认;堆大小的40%

regionServer的全局memstore的大小，超过该大小会触发flush到磁盘的操作,默认是堆大小的40%,而且regionserver级别的flush会阻塞客户端读写

2.（hbase.hregion.memstore.flush.size）默认：128M

单个region里memstore的缓存大小，超过那么整个HRegion就会flush,

3.（hbase.regionserver.optionalcacheflushinterval）默认：1h

内存中的文件在自动刷新之前能够存活的最长时间

4.（hbase.regionserver.global.memstore.size.lower.limit）默认：堆大小 * 0.4 * 0.95

有时候集群的“写负载”非常高，写入量一直超过flush的量，这时，我们就希望memstore不要超过一定的安全设置。在这种情况下，写操作就要被阻塞一直到memstore恢复到一个“可管理”的大小, 这个大小就是默认值是堆大小 * 0.4 * 0.95，也就是当regionserver级别的flush操作发送后,会阻塞客户端写,一直阻塞到整个regionserver级别的memstore的大小为 堆大小 * 0.4 *0.95为止

5.（hbase.hregion.preclose.flush.size）默认为：5M

当一个 region 中的 memstore 的大小大于这个值的时候，我们又触发 了 close.会先运行“pre-flush”操作，清理这个需要关闭的memstore，然后 将这个 region 下线。当一个 region 下线了，我们无法再进行任何写操作。 如果一个 memstore 很大的时候，flush  操作会消耗很多时间。"pre-flush" 操作意味着在 region 下线之前，会先把 memstore 清空。这样在最终执行 close 操作的时候，flush 操作会很快。

6.（hbase.hstore.compactionThreshold）默认：超过3个

一个store里面允许存的hfile的个数，超过这个个数会被写到新的一个hfile里面 也即是每个region的每个列族对应的memstore在fulsh为hfile的时候，默认情况下当超过3个hfile的时候就会 对这些文件进行合并重写为一个新文件，设置个数越大可以减少触发合并的时间，但是每次合并的时间就会越长

 

2、compact机制

把小的storeFile文件合并成大的Storefile文件。

清理过期的数据，包括删除的数据

将数据的版本号保存为3个

3、split机制

当Region达到阈值，会把过大的Region一分为二。

默认一个HFile达到10Gb的时候就会进行切分

 

HBase的预分区

1、为何要预分区？

* 增加数据读写效率

* 负载均衡，防止数据倾斜

* 方便集群容灾调度region

* 优化Map数量

 

2、如何预分区？

每一个region维护着startRow与endRowKey，如果加入的数据符合某个region维护的rowKey范围，则该数据交给这个region维护。

 

3、如何设定预分区？

        1、手动指定预分区

create 'staff','info','partition1',SPLITS => ['1000','2000','3000','4000']​​​​​​​

      2、使用16进制算法生成预分区

create 'staff2','info','partition2',{NUMREGIONS => 15, SPLITALGO => 'HexStringSplit'}

      3、分区规则创建于文件中

cd /export/servers/
vim splits.txt

aaaa
bbbb
cccc
dddd

create 'staff3','partition2',SPLITS_FILE => '/export/servers/splits.txt'

 

HBase的rowKey设计技巧

HBase是三维有序存储的，通过rowkey（行键），column key（column family和qualifier）和TimeStamp（时间戳）这个三个维度可以对HBase中的数据进行快速定位。

HBase中rowkey可以唯一标识一行记录，在HBase查询的时候，有以下几种方式：

1. 通过get方式，指定rowkey获取唯一一条记录
2. 通过scan方式，设置startRow和stopRow参数进行范围匹配
3. 全表扫描，即直接扫描整张表中所有行记录

 

1 rowkey长度原则

rowkey是一个二进制码流，可以是任意字符串，最大长度64kb，实际应用中一般为10-100bytes，以byte[]形式保存，一般设计成定长。

建议越短越好，不要超过16个字节，原因如下：

1. 数据的持久化文件HFile中是按照KeyValue存储的，如果rowkey过长，比如超过100字节，1000w行数据，光rowkey就要占用100*1000w=10亿个字节，将近1G数据，这样会极大影响HFile的存储效率；
2. MemStore将缓存部分数据到内存，如果rowkey字段过长，内存的有效利用率就会降低，系统不能缓存更多的数据，这样会降低检索效率。

 

2 rowkey散列原则

如果rowkey按照时间戳的方式递增，不要将时间放在二进制码的前面，建议将rowkey的高位作为散列字段，由程序随机生成，低位放时间字段，这样将提高数据均衡分布在每个RegionServer，以实现负载均衡的几率。如果没有散列字段，首字段直接是时间信息，所有的数据都会集中在一个RegionServer上，这样在数据检索的时候负载会集中在个别的RegionServer上，造成热点问题，会降低查询效率。

 

3 rowkey唯一原则

必须在设计上保证其唯一性，rowkey是按照字典顺序排序存储的，因此，设计rowkey的时候，要充分利用这个排序的特点，将经常读取的数据存储到一块，将最近可能会被访问的数据放到一块。

 

4什么是热点

HBase中的行是按照rowkey的字典顺序排序的，这种设计优化了scan操作，可以将相关的行以及会被一起读取的行存取在临近位置，便于scan。然而糟糕的rowkey设计是热点的源头。

热点发生在大量的client直接访问集群的一个或极少数个节点（访问可能是读，写或者其他操作）。大量访问会使热点region所在的单个机器超出自身承受能力，引起性能下降甚至region不可用，这也会影响同一个RegionServer上的其他region，由于主机无法服务其他region的请求。

设计良好的数据访问模式以使集群被充分，均衡的利用。为了避免写热点，设计rowkey使得不同行在同一个region，但是在更多数据情况下，数据应该被写入集群的多个region，而不是一个。下面是一些常见的避免热点的方法以及它们的优缺点：

1加盐

这里所说的加盐不是密码学中的加盐，而是在rowkey的前面增加随机数，具体就是给rowkey分配一个随机前缀以使得它和之前的rowkey的开头不同。分配的前缀种类数量应该和你想使用数据分散到不同的region的数量一致。加盐之后的rowkey就会根据随机生成的前缀分散到各个region上，以避免热点。

2哈希

哈希会使同一行永远用一个前缀加盐。哈希也可以使负载分散到整个集群，但是读却是可以预测的。使用确定的哈希可以让客户端重构完整的rowkey，可以使用get操作准确获取某一个行数据。

3反转

第三种防止热点的方法时反转固定长度或者数字格式的rowkey。这样可以使得rowkey中经常改变的部分（最没有意义的部分）放在前面。这样可以有效的随机rowkey，但是牺牲了rowkey的有序性。

反转rowkey的例子以手机号为rowkey，可以将手机号反转后的字符串作为rowkey，这样的就避免了以手机号那样比较固定开头导致热点问题

4时间戳反转

一个常见的数据处理问题是快速获取数据的最近版本，使用反转的时间戳作为rowkey的一部分对这个问题十分有用，可以用 Long.Max_Value - timestamp 追加到key的末尾，例如 [key][reverse_timestamp] , [key] 的最新值可以通过scan [key]获得[key]的第一条记录，因为HBase中rowkey是有序的，第一条记录是最后录入的数据。

其他一些建议：

尽量减少行键和列族的大小在HBase中，value永远和它的key一起传输的。当具体的值在系统间传输时，它的rowkey，列名，时间戳也会一起传输。如果你的rowkey和列名很大，这个时候它们将会占用大量的存储空间。

列族尽可能越短越好，最好是一个字符。

冗长的属性名虽然可读性好，但是更短的属性名存储在HBase中会更好